Basic SQL Transformations
Orchestrate simple, in-destination SQL transformations from your Fivetran dashboard.
Overview
Fivetran's basic SQL transformations allow you to execute transformations in your destination from your Fivetran dashboard. With basic SQL transformations, you can:
- Type or paste SQL scripts into the Fivetran dashboard's web-based editor
- Trigger transformations to run on a periodic schedule or when new data is loaded into your destination
Note: Basic SQL transformations do not support version control. If you need version control, use dbt transformations instead.
How it works
You can write your own SQL transformations or use our sample queries. Once you've written your SQL scripts, you can either schedule or trigger your transformations to run:
- Scheduled transformations run on a regular interval that you choose.
- Triggered transformations run when your connector adds new data to your destination.
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 can set a frequency between 5 minutes and 24 hours.
Triggered transformations
Triggered transformations run when your connector loads new data into your destination. When you create a triggered transformation, you select the tables whose changes will trigger the transformation. If new data arrives in the selected tables, we run the transformation at the end of the sync.
Fivetran only runs transformations once the connector sync is finished. By waiting until the sync is finished, we avoid running a transformation multiple times while syncing a single connector.
Multiple statements
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:
Supported destinations
Fivetran supports basic SQL transformations for all destinations.
Setup guide
Follow our step-by-step setup guide to learn how to use basic SQL transformations in Fivetran.
Sample queries
Fivetran has created sample queries that you can use to transform data in your destination.
Manage basic SQL transformations
Once you have set up your basic SQL transformations, you can manage them on the Transformations page of your Fivetran dashboard. The Transformations page contains a list of all your transformations, sorted in alphabetical order. Each transformation shows when it was most recently run and whether the last run was successful.
From the Transformations page, you can add, view, run, pause, edit, and delete a transformation.
Add a transformation
Click + Transformation in the top right corner of the dashboard. You'll be redirected to the transformation setup page. Follow our setup instructions to add a transformation.
View a transformation
Click on the transformation to go to its Transformation Details page. The Transformation Details page shows information about the transformation along with its run history. The run history shows information about that transformation's runs, including detailed messages for the last 7 days.
Run a transformation
Click on the transformation you want to run. In the top right corner of the Transformation Details page, click the Run now button.
Pause a transformation
Click on the transformation you want to pause. In the top right corner of the Transformation Details page, set the ENABLED toggle to PAUSED.
Edit a transformation
Click on the transformation you want to edit. On the Transformation Details page, select Edit transformation details. You'll be redirected to the transformation setup page. Follow our setup instructions to edit your transformation.
Delete a transformation
Click on the transformation you want to delete. On the Transformation Details page, select Delete transformation. In the pop-up window that opens, confirm that you want to delete this transformation.
Writing transformed data to a destination-added schema
If your transformation will write to new tables or outside schema, you may need to grant your destination user permission to access them. To check your user permissions, run the following query in your destination:
SELECT
u.username,
s.schemaname,
has_schema_privilege(u.username,s.schemaname,'create') AS user_has_select_permission,
has_schema_privilege(u.username,s.schemaname,'usage') AS user_has_usage_permission
FROM
pg_user u
CROSS JOIN
(SELECT DISTINCT schemaname FROM pg_tables) s
WHERE
u.username = 'usernameToCheck'
AND s.schemaname = 'schemaToCheck'
Your destination user should have the same permissions for your new schema as it does for the schemas that Fivetran creates during your syncs. If the permissions are not the same, you must grant the missing permissions to the destination user.
Alerts
To see alerts about your transformations, select Alerts in the left menu of your Fivetran dashboard. 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 transformations.
