SQL Transformations

SQL Transformations is a powerful and efficient tool you can use to apply transformations to large volumes of data using structured query language (SQL) queries.

It is an in-house utility that provides you with the necessary tools to perform large-volume and complex transformations on data extracted from multiple different sources. This utility enables you to use simple SQL SELECT statements to bind the various large-volume data sets and transform them to get the data you need.

Key features

• Query data coming in from any number of data sources.
• No limitation on the volume of data you can fetch and transform, or on the data output produced. This means you can handle millions of records with ease.
• High performance in running queries and producing output data sets. SQL Transformations can perform complete transformation in seconds.
• Supports the use of complex select queries that involve joins and other SQL functions.

---

Usage inspiration

Sample use cases

See our guides for step-by-step instructions on how to leverage SQL Transformations for the following use cases:

• Change data capture
• Data enrichment
• Data validation and cleaning

Additionally, SQL Transformations enables users to run complex queries to handle the following situations:

• Periodically fetch new accounts information from Dynamics CRM and merge it with additional lookup data stored as a file in Workato FileStorage and load the output directly to a BigQuery data warehouse table.

• Export large volume sales details from Salesforce, enrich this data by joining it with product details available as a file within Workato FileStorage, filter and fetch only opportunities that are above a specific value as output data set, and load it to an on-prem system.

• Fetch complete employee data extract from the on-prem system, filter and get details of employees with a specific job title and store the output data set in Workato FileStorage as a file for future report generation purposes.

---

How does it work?

SQL Transformations utilizes the Workato FileStorage system under the hood for temporary data storage of incoming large volume content stream before running transformations on it. Since it is deeply integrated with Workato FileStorage, it can scale to store and process any volume of data in a fast and efficient manner.

---

Who can use this feature?

Once the connector is provisioned, it is available to all roles within the same workspace. Do note that at present, all files that are queried from, or created from the query output and stored within Workato FileStorage at a workspace level, can be accessed by all roles within the specific workspace/tenant as well.

Feature availability

SQL Transformations is currently available by invite only. Contact your customer success manager to indicate your interest.

SQL Transformations utility connector

---

Encryption

SQL Transformations is built as a service to Workato FileStorage system, hence it follows same encryption principles as FileStorage. Additional details about FileStorage encryption is available here.

---

Limitations

In the private preview release, SQL Transformations has the following limits:

• Each data source used in the query has a maximum size of GB
• If you are storing the output data in Workato FileStorage, storage limits apply.

---

Data retention

In SQL Transformations, the following data are temporarily retained

• The incoming data from different data sources other than FileStorage
• The transformed query output data

These data are all automatically purged from primary and backup drives within 4 days. To persist this data longer, you can store it in FileStorage and make use of it in SQL Transformations utility.