Fivetran culture flows from a relentless determination to solve a critical data engineering problem: Automating data pipelines for every data source. Analytics workflows can run uninterrupted from the initial setup through ongoing updates to ensure that your data remains fresh. We are intent on developing features that support your desired business outcomes.

We have built many new features for your database connectors such as:

• Automated schema drift handling. Schema changes such as table and column additions or data type changes commonly break data pipelines or require maintenance and upkeep to accommodate the changes. With Fivetran, these changes are automatically pushed to your data warehouse so your teams can stay up to date with the latest business analytics and tracked metrics.
  
• Log-free replication. For use cases where you do not have access to your database logs, we have developed teleport sync to replicate data log-free. Teleport sync uses compressed representations of your dataset to determine incremental updates and quickly update your destination.
  

Our latest feature for your database connectors is history mode. History mode is currently enabled for: DynamoDB, MariaDB, MongoDB, MySQL, Oracle, PostgreSQL and SQL Server.

What is history mode?

History mode is our solution for unlocking historical analysis within your data sources. We create a record of every change in your database transaction logs and append those records to the tables you specify so you can track changes made to your data over time.

“[Fivetran’s] history mode can allow our software developers to focus on what they do best: Engineering fast transactions on our platform without having to worry about how to capture data changes that occur within those transactions. The business systems team now has access to ephemeral, changing datasets effortlessly. We're incredibly pleased with this new option.”
Beth Mattson, data engineer, Field Nation

Type 2 slowly changing dimensions (SCD)

The common term used to describe history mode tracking is Type 2 Slowly Changing Dimensions (SCD), which must be built out alongside your data integration tool, and history mode accomplishes this natively. In a previous post, we dove into considerations when building a historical analysis yourself, but the two primary issues that you’ll come across when scripting Type 2 SCD yourself involve:

• Manual data integrity checks to ensure that you’re capturing every transaction made to your table, along with timestamps, to have a reliable history of data to draw upon for insights
  
• Additional checks to capture deleted data and determine when values are no longer valid
  

“[Fivetran's history mode] is going to save us a bunch of time in regards to reporting, lookbacks, debugging product problems... [related to] patient-provider relations."
- Brian Moore, lead data engineer, CirrusMD

Type 2 SCD implementation via history mode

In the UI, history mode can be enabled by hovering over the table and selecting “history mode” within the dropdown on the right side. That is all you need to do to immediately start capturing the change record of your data.

Fivetran extracts the timestamp associated with changes in your database to create additional rows that denote the values associated with each change during the row’s lifecycle. 

Customer journey analytics, order funnel analytics and more

In this example, we look at a table that captures how an order has changed over time. The order remains open until the purchase has gone through. We have an indecisive customer who continues to add to their cart and we want to see how their order changes.

History mode shows us data value changes overtime and appends 3 columns to the table to track the lifecycle of each row. “fivetran_start” and “fivetran_end” denote when a row was current. The last column is “fivetran_active” where true is the current row version and false is an older row version. Timestamps and changed records are extracted from the logs and are not impacted by a connector’s update frequency.

There are a few universally shared use cases for historical analysis of Salesforce data. The primary difference in database historical analysis is the tracked data comes from individual business models. Some use cases for historical tracking on databases include:

• User journeys through product line and feature usage for future product roadmaps or customer health indicators
  
• Order progression including order changes over time and logistics involved with manufacturing and distribution
  

How to get started with Fivetran history mode

If you have an existing database connector, you can log into your dashboard, select the connector, navigate to the tables list and use the dropdown menu to the right of each table name to enable history mode. If not, you can start by creating a new database connector and then following the instructions above.

If you’re considering Fivetran for your database replication needs, we offer a free 14 day trial for our database connectors. This trial doesn’t begin until after you’ve already finished your initial, historical sync of a chosen data source. There’s no better time to sign up and experience seamless integration built to power analytics.

Download this Datasheet: History mode for databases

DOWNLOAD