Follow our setup guide to set up basic SQL transformations in Fivetran.
- A Fivetran account
- Sufficient permissions in your destination to create or manage transformations
Create a transformationlink
In your Fivetran dashboard, select Transformations in the left menu.
Click + Transformation.
On the New Transformation page, enter a name for your transformation. The name can contain only letters, numbers and underscores, and it can’t begin with a number.
Enter your SQL script. We have a built-in code highlighter to help you write correct SQL syntax. You can also use one of our sample queries.
Choose a schedule type: New Data or Time Schedule.
i. If you chose New Data, select the tables whose data changes will trigger a transformation.
ii. If you chose Time Schedule, select the frequency at which the transformation will run.
iii. If you chose 24 hours as a Transform Frequency interval value in the Time Schedule, you can also set the time of the day when the transformation will run.
Save your transformationlink
Depending on your destination, you may have several options for saving your transformation: Save & Test, Save & Run, and/or Save. To finish setting up your transformation, select your chosen save method.
- Save & Test performs a test of the transformation right after you save it. This method is the best way to save a transformation. Testing guarantees that the transformation will succeed when Fivetran runs it in your destination.
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 runs the transformation right after you save it. This method is only an option if your destination doesn’t support testing SQL scripts. This means that the destination doesn’t support transactions, dry-runs, or any other alternative that runs the script without data modification. Note that this method creates new transformed data in your destination.
- Save saves the transformation without testing. This method is useful when you have already tested a SQL script in your destination using its native warehouse UI.