Snapshot tables for historical tracking in dbt - Mini Project: Build & Apply

Snapshot tables for historical tracking

📖 Scenario: Imagine you work for an online store. You want to keep track of how product prices change over time. This helps you see price trends and understand when prices go up or down.

🎯 Goal: You will create a dbt snapshot to track changes in product prices over time. This snapshot will store historical versions of product data so you can analyze price changes later.

📋 What You'll Learn

Create a source table with product data

Define a snapshot configuration with unique key and strategy

Write the snapshot SQL to track price changes

Run the snapshot and display the historical data

💡 Why This Matters

🌍 Real World

Snapshot tables help businesses track how data changes over time, like price changes, customer info updates, or inventory levels.

💼 Career

Data analysts and engineers use snapshots to build historical datasets for reporting, auditing, and trend analysis.

1

Create the source table with product data

Create a source table called products with these exact columns and values: product_id (1, 2, 3), product_name ('T-shirt', 'Jeans', 'Sneakers'), and price (20, 40, 60). Use a dbt model SQL file named products.sql with a select statement returning these rows.

dbt

-- Create a source table called products with product_id, product_name, and price
-- Your code here

Need a hint?

Use a select * from (values ...) statement to create the table with exact rows.

2

Configure the snapshot settings

Create a dbt snapshot configuration file named product_price_snapshot.sql. Add a snapshot block with unique_key = 'product_id' and strategy = 'check'. Also, set check_cols = ['price'] to track changes only in the price column.

dbt

select * from (values
  (1, 'T-shirt', 20),
  (2, 'Jeans', 40),
  (3, 'Sneakers', 60)
) as products(product_id, product_name, price)

-- Add snapshot configuration below
-- Your code here

Need a hint?

Use {% snapshot %} and {{ config(...) }} to set snapshot options.

3

Run the snapshot to track price changes

Run the dbt snapshot command to create the snapshot table and track historical price changes for products.

dbt

{% snapshot product_price_snapshot %}
  {{ config(
    target_schema='snapshots',
    unique_key='product_id',
    strategy='check',
    check_cols=['price']
  ) }}

  select * from {{ ref('products') }}

{% endsnapshot %}

-- Run the snapshot command in your terminal
-- Your code here

Need a hint?

Use the terminal command dbt snapshot to run the snapshot.

4

Display the snapshot historical data

Write a select query to display all rows from the snapshot table named snapshots.product_price_snapshot. Use order by product_id, dbt_valid_from to see the history in order.

dbt

{% snapshot product_price_snapshot %}
  {{ config(
    target_schema='snapshots',
    unique_key='product_id',
    strategy='check',
    check_cols=['price']
  ) }}

  select * from {{ ref('products') }}

{% endsnapshot %}

-- Write a SQL query to select all from snapshots.product_price_snapshot ordered by product_id and dbt_valid_from
-- Your code here

Need a hint?

Use select * from snapshots.product_price_snapshot order by product_id, dbt_valid_from to see the full history.