History mode is a feature that can be turned on for any table where a data analyst may want to look at how data has changed over time. The feature achieves Type 2 Slowly Changing Dimensions (Type 2 SCD), meaning a new timestamped row is added for every change made to a column.

In a previous blog post, we shared some history mode use cases from our customers. Here we compare Fivetran history mode to alternative methods for building historical analysis.

Build and maintain vs. “flip a switch”

You may be asking, “Why can’t I just build the historical analysis myself?” Well, you could, but you'd have to dedicate time and resources to building and maintaining the solution. At Fivetran, automation is at the core of our product, and we believe that businesses should eliminate manual work where possible, putting people power towards projects and challenges unique to their business.

You may have come across other solutions to access historical data. While these will admittedly get the job done, Fivetran offers a richer set of capabilities. Below are some benefits and drawbacks for two alternatives.

Salesforce native field history tracking

Salesforce has native functionality that allows users to define specific fields for which they want to track history. This works great for some basic use cases, but there are limitations, including:

• 20 fields per object limit. Native field history tracking in Salesforce is limited to 20 fields per object. Fivetran history mode allows you to track every field on the tables you choose, with no limit.
• Field history tracked in a separate object. With Salesforce, field history is tracked in a separate object, so you have to join non-history and history objects in the warehouse via transformation. With Fivetran, your historical data is stored within the same table, so you have fewer joins and a simpler data model.

Designing SQL transformations to build table history

You can design SQL transformations to build table history yourself with a tool like dbt. We love dbt, and that’s why we offer an integration with dbt within our dashboard and deliver dbt packages for many of our connectors. These packages handle basic table standardization and table joins and aggregations to provide clean, analysis-ready models.

dbt is powerful and can even be used to create snapshots of your data, essentially building out a full table history over time. This is a great option when a simpler way of tracking history is not available, but there are a few drawbacks:

• More complex set-up. dbt requires you to set up and orchestrate complex SQL scripts. With Fivetran, you can enable history mode with just two clicks in the Fivetran UI.
• More complex queries. Similar to Salesforce field history tracking, the dbt snapshot method creates a separate table that must be queried separately and requires the analyst to handle NULL timestamps. Fivetran history mode keeps all the historical data in one table.
• Requires workarounds to support deleted data. For dbt snapshots, tracking of deleted data is not supported out-of-the-box and requires workarounds or advanced usage of dbt. History mode supports tracking of deleted data out-of-the-box.

Learn more about Fivetran history mode

At Fivetran, we pride ourselves on ease-of-use. With history mode, just toggle a button to start tracking history on a table. Unlike other solutions, Fivetran history mode keeps all historical data in one table, supports tracking of deleted data out-of-the-box and allows you to track every field on the tables you choose, with no limit.

There are a number of ways to learn more about our history mode feature:

• Watch the Getting Started With Salesforce History Mode webinar recording.
• See it in action with the Fivetran history mode demo for Snowflake or for Databricks
• Review the history mode documentation
• Use our free 14-Day trial to sync your Salesforce data, enable history mode and get a sense of the impact on monthly active rows for your use case