Watch the Video
Snapshots in dbt: A Quick Overview
dbt snapshots allow you to “look back” at historical data by capturing changes in your database tables. This is achieved by implementing type-2 Slowly Changing Dimensions (SCDs), which track how a row has changed over time. For example, you can keep track of an order’s status as it moves from ‘pending’ to ‘shipped’ to ‘delivered’.
In this article:
How Snapshots Work
When you run dbt snapshot, it creates a new table with the same columns as your source table, plus additional metadata columns like dbt_valid_from and dbt_valid_to. On subsequent runs, dbt updates the dbt_valid_to column for changed records and adds new records with dbt_valid_to set to null.
dbt offers different strategies for detecting changes, including comparing timestamps, specific column values, or a unique key. You can also configure dbt to track deletions.
Snapshots Best Practices
- Store snapshots in a separate schema to keep your historical data organized.
- Snapshot raw data using the
sourcefunction and select all columns. - Avoid joins in your snapshot queries; instead, create separate snapshots and join them downstream.
- If you need to perform transformations, do so in ephemeral models before snapshotting.
- Schedule snapshots to run frequently to capture changes regularly.
By following these best practices, you can ensure that your dbt snapshots are accurate, efficient, and easy to maintain.
