Data transformation is a key aspect of any integration platform, as we often need to transform the data from one format to another while exchanging the data between one or more systems involved in the integration. The different platforms have their own transformation engines built using different tech stacks.
Similarly, Workato has a very powerful SQL Transformations component to process huge volumes of data with ease. It offers an intuitive interface to define multiple data sources and extract and transform the data using SQL queries. Although it can be utilised in a variety of use cases, this blog primarily focusses on comparing and transforming the bulk data extracted from the source system.
Use case
The requirement involves the processing of the incremental data from the source to the target system through a CSV file. The source system is not capable of extracting the incremental data; however, the target system requires only the incremental changes. We can leverage Workato’s SQL transformations to compare the old file extract with the latest one to identify the incremental changes and process them either via streaming the query response or writing the results directly to the file on Workato storage.
Steps to configure the SQL transformation action
1. Add the SQL transformation action into your recipe flow.
2. Configure data source 1 by defining the data source, data source type, and data source schema. The data source type can be file storage, content stream, or data table. The below configuration is configured considering the data source type as Workato file storage, and it refers to the latest snapshot from the source system.
3. Similarly, configure data source 2 for the old data snapshot from the source system.
4. Write the SQL query, considering the above two configured data source names, to extract the incremental data. The below query combines the results from two different SQL queries using the union clause. The first query is to identify the new records or updated records by comparing the latest and old file data. The second one returns the deleted records in the new file. The outer SELECT statement orders the result by the disabled column to format the output and place the deleted records first in the output file.
5. Lastly, we need to configure the output step of the component. The below configuration is to write the query results to a CSV file on the Workato storage.
The below screenshot shows the content of the latest file, old file and output file created by the transformation action.
We have learnt how easy it is to transform the data efficiently using Workato’s SQL transformations. We can even write more complex SQL queries to filter the records and perform data cleansing using this technique. It also scales up to millions of records as it is backed by Workato’s file storage.
References
https://docs.workato.com/features/sql-transformations.html
Happy Learning 🙂