With the release of Fivetran Transformations, Fivetran users are enjoying newfound utility in centralizing data to a relational warehouse. In addition to aggregation and clean up, Transformations can make your data more extendable in another way: table history.

History tables

Some data platforms, such as Salesforce, allow you to keep an incremental history of record states over time. With Fivetran Transformations, you can embed this same functionality into your warehouse when your data source doesn’t offer any visibility into record history.

The recipe

The table history transformation takes advantage of the _fivetran_synced column, which is written to every table that Fivetran creates in the warehouse. Each time Fivetran updates the table in the warehouse, the transformation script will append the changed version of the records to a _history-labeled version of the table.

The following sample will create and continue to update table history for the `LEAD` table in a schema titled `MARKETO`. Note that the table history will be written to the same base schema as the base table, but you may like to dedicate a schema for transformation-created tables. The script uses Snowflake dialect.

1. From your Fivetran Dashboard, click Transformations

2. Create a new transformation by clicking + Transformation

3. Name your transformation

4. Paste the following in the SQL script editor, making sure to use your own database, schema and table names:

CREATE TABLE IF NOT EXISTS "DATABASE_NAME"."MARKETO"."LEAD_HISTORY" ASSELECT *FROM "DATABASE_NAME"."MARKETO"."LEAD";INSERT INTO "DATABASE_NAME"."MARKETO"."LEAD_HISTORY"SELECT *FROM "DATABASE_NAME"."MARKETO"."LEAD"WHERE "DATABASE_NAME"."MARKETO"."LEAD"._fivetran_synced > ( SELECT Max(_fivetran_synced) FROM "DATABASE_NAME"."MARKETO"."LEAD_HISTORY");

5. For schedule type, select the New Data option. The trigger table should be the name of the table that will be used to generate the table history.

That’s it! Going forward, Fivetran will automatically add to this *_history table to reflect every record change relevant to this table. You can use this history table to view how your data looked at a point in time and analyze trends, while still using the base table for your daily analytics.

This is only one example of how you can extend your data capability with Transformations. Have you written a useful Transformation? We'd love to see it! Email me at stephen@fivetran.com.

Happy transforming!