Transformations

Fivetran Transformations provide everything data teams need to orchestrate SQL-based transformations in the destination. Once a data source connection is initiated within Fivetran, analysts can use a trigger or time-based schedule to automatically update transformation tables whenever new data is loaded into the destination.

Fivetran Transformations follow an ELT (extract, load, transform) model. Because transformations happen in the destination, your raw data is always available along with the transformed data. If a transformation fails, you will not lose data. If your organization's analytical needs change, you can edit your transformations and run them again on the raw data.

Transformations are SQL scripts that are executed in the destination based on specific events or conditions. The main purpose of transformations is to map data into a specific shape that will be easier or faster to use in the next pipeline elements.

---

Run conditions

Fivetran transformations allow you to execute SQL scripts based on a chosen run condition.

We support the following run conditions:

• Scheduling (running a transformation periodically)
• Triggering (running a transformation when new data is pushed by a connector)

If a transformation is already running or is being tested, it cannot be run or tested again. Another attempt to run or test the transformation will fail immediately. After the existing transformation completes, it can be run again.

Scheduled transformations

Scheduled transformations run periodically based on the frequency you've selected. You may set a frequency between 5 minutes and 24 hours.

Triggered transformations

Triggered transformations run when the connector pushes new data into your destination. When you create a triggered transformation, you select which tables trigger the transformation. We run the transformations at the end of the sync if new data arrives in the selected tables.

Fivetran runs transformations only when the connector sync is finished. By waiting until the sync is finished, we avoid running a transformation multiple times while syncing a single connector.

---

Supported data destinations

We support transformations in the following data destinations:

• Azure Data Explorer
• Azure Synapse
• BigQuery
• Databricks
• MySQL
• Panoply
• Periscope
• PostgreSQL
• Redshift
• Snowflake
• SQL Server

Multiple statement support

Fivetran supports multi-statement SQL scripts for all destinations. However, some destinations don't support executing multiple statements within a single query execution. For these destinations, we split the script by statements and execute the statements sequentially. To allow us to reliably and predictably split the script into statements, format it so that each statement ends with ; and is followed by an empty line.

Correct script – will be split into two statements that will be executed in sequence:

SELECT 1;

SELECT 2;

Incorrect script – will be executed as one statement which will result in a destination error:

SELECT 1;
SELECT 2;

This workaround is necessary for the following destinations:

• Azure Data Explorer
• Databricks

Required permissions

You must have analyst or admin permissions in the destination to create or manage transformations. See Snowflake's documentation on setting roles.

---

Create transformations

1. Name your transformation. The name can contain only letters, numbers and underscores, and can't begin with a number.

2. Enter your SQL script. We have a built-in SQL code highlighter to help you write correct SQL syntax.

   Refer to our list of sample queries.

3. Select schedule type: Triggered or scheduled.

• If you select triggered, select the tables whose data changes you wish to trigger a transformation.
• If you select scheduled, select the frequency at which you'd like the transform to run.

Save the transformation

Depending on your destination, you may have several options for saving your transformation.

Save & Test

The best way to save a transformation is to perform a test of the transformation right after you save it. Testing the transformation will guarantee that when Fivetran runs the transformation on your destination, it will succeed.

Select Save & Test to save and immediately test your transformation.

Note: BigQuery doesn't support test runs of transformations. Databricks supports testing transformations, but only tests the syntax and compilation of the transformation script. It does not test the actual execution of the transformation.

Save & Run

Not all destinations support testing SQL scripts. This means that the destination doesn't support transactions, dry-runs or any other alternative that will run the script without data modification. For such destinations, Fivetran runs the transformation right after saving. This means that the transformation will create new data in your destination.

If your destination doesn't support testing, you have the option to Save & Run the script immediately.

Save

For your convenience we also allow saving transformation without testing. It may be useful in situations when you have already tested a SQL script in your destination using the native warehouse UI.

---

Manage transformations

To manage transformations, go to your Fivetran dashboard and select Transformations in the left menu.

In the transformations section of the Fivetran dashboard, you can see a list of transformations. Transformations are sorted in alphabetical order. Each transformation shows when it was most recently run and whether the last run was successful.

From the transformation section of the dashboard, you can add, view, run, pause, edit, and delete a transformation.

Add a transformation

To add a transformation, click +Transformation in the top right corner of the dashboard. It will open the transformation setup page. Follow the instructions in the Create transformations section to finish adding a transformation.

View a transformation

In the Transformations tab of the dashboard, select the transformation you want to view and go to its Transformation Details page.

The view shows detailed information about the transformation along with the transformation run history. The run history shows information about transformation runs including detailed messages for the last 7 days.

Run a transformation

1. In the Transformations tab of the dashboard, select the transformation you want to run and go to its Transformation Details page.
2. On the Transformation Details page for your chosen transformation, select the Run now button in the top right corner of the dashboard.

Pause a transformation

1. In the Transformations tab of the dashboard, select the transformation you want to pause and go to its Transformation Details page.
2. On the Transformation Details page for your chosen transformation, select the toggle in the top right corner of the dashboard to toggle between Enabled and Paused.

Edit a transformation

1. In the Transformations tab of the dashboard, select the transformation you want to edit and go to its Transformation Details page.
2. On the Transformation Details page for your chosen transformation, select Edit transformation details.
3. Follow the instructions in the Create transformations section to finish editing the transformation.

Delete a transformation

1. In the Transformations tab of the dashboard, select the transformation you want to delete and go to its Transformation Details page.
2. On the Transformation Details page for your chosen transformation, select Delete transformation.
3. In the confirmation dialogue that opens, confirm that you wish to delete.

---

Alerts

To see alerts about your transformations, go to your Fivetran dashboard and select Alerts.

Transformation alerts are listed here along with all your other Fivetran alerts.

Email notifications

Manage email notifications related to your alerts in the notifications section of the dashboard. Here you can granularly control notifications for transforms.