15 Oct 2019 | Analyst Recipe

Table History With Fivetran Transformations

Stephen Young
Stephen Young
Table History With Fivetran Transformations
Use Fivetran Transformations to take ongoing snapshots of your data so you can analyze trends over time.

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" AS
SELECT *
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!

Are You A Data Expert?

Start a free trial today.

Discover the smartest solution for data-driven results.
We have detected that you are using an adblocking plugin in your browser. We don't show ads, but we rely on advertising services, so it might restrict you from completing important functions or seeing important content. Please make sure you whitelist our website in your adblocking plugin.
Fivetran uses cookies to enhance your user experience and improve the quality of our website. Unless you disable cookies, you consent to the placement and use of cookies as described in our Privacy Policy by continuing to use this website.
Adblock Detection