You want to transform only new data that Fivetran replicates to your destination, rather than the entire dataset.
- Instead of using functions like `CREATE OR REPLACE TABLE` you can leverage an `INSERT INTO` statement paired with a `WHERE` clause directed at the `_fivetran_synced` column
- The `_fivetran_synced` column is a Fivetran added column that indicates the start time of the job that synced the corresponding row
An example transformation in Snowflake would be:
CREATE TABLE IF NOT EXISTS “SCHEMA”.”TRANSFORMED_TABLE” AS
INSERT INTO “SCHEMA”.”TRANSFORMED_TABLE”
WHERE “SCHEMA”.”TABLE”._fivetran_synced >= (
- This transformation method will keep all changes made to the data since the transformation started.
- Keep in mind the transformation frequency vs the sync frequency of data sources. For example, if your transformation frequency is every 4 hours and sync frequency is every 2 hours, then you might miss records.
- When creating a Fivetran transformation, be sure to use the syntax of your corresponding destination.
- An alternative to this is creating or replacing a view which has benefits highlighted here. If you are set on creating a table, this is a good route to increase efficiency and only transform new data. Keep in mind that this transformation will still perform a full table scan.