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

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

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

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

Hint

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

Practice

1. What is the main purpose of using snapshot tables in dbt?

easy

A. To save historical versions of data automatically

B. To speed up query performance by caching results

C. To create temporary tables for intermediate calculations

D. To delete old data from the database

Solution

1. Step 1: Understand snapshot tables

   Snapshot tables store copies of data at different points in time to track changes.

2. Step 2: Identify the main use

   They are used to save historical versions automatically, not for caching or deleting data.

3. Final Answer:

   To save historical versions of data automatically -> Option A

4. Quick Check:

   Snapshot tables = historical data tracking [OK]

Hint: Snapshots track data changes over time automatically [OK]

Common Mistakes:

• Confusing snapshots with caching mechanisms
• Thinking snapshots delete old data
• Assuming snapshots create temporary tables

2. Which of the following is the correct way to define a snapshot strategy in dbt?

easy

A. strategy: timestamp

B. method: snapshot

C. type: incremental

D. mode: historical

Solution

1. Step 1: Recall snapshot config syntax

   In dbt snapshots, the strategy is set using strategy: timestamp or strategy: check.

2. Step 2: Identify correct keyword

   Only strategy: timestamp is valid; others like method, type, or mode are incorrect.

3. Final Answer:

   strategy: timestamp -> Option A

4. Quick Check:

   Snapshot strategy keyword = strategy [OK]

Hint: Use 'strategy' keyword to set snapshot method [OK]

Common Mistakes:

• Using wrong keywords like 'method' or 'type'
• Confusing snapshot strategy with model materialization
• Misspelling 'strategy'

3. Given this snapshot config snippet:

snapshots:
  - name: customer_snapshot
    strategy: check
    check_cols: ['email', 'status']

What happens when the email or status changes in the source data?

medium

A. The snapshot process ignores changes in these columns

B. The existing record is overwritten without history

C. A new record version is created in the snapshot table

D. An error occurs because check_cols is invalid

Solution

1. Step 1: Understand 'check' strategy with check_cols

   The 'check' strategy tracks changes in specified columns and creates new versions when they change.

2. Step 2: Apply to given columns

   If 'email' or 'status' changes, dbt adds a new record version to keep history.

3. Final Answer:

   A new record version is created in the snapshot table -> Option C

4. Quick Check:

   Check strategy tracks column changes = new version [OK]

Hint: Check strategy creates new versions on column changes [OK]

Common Mistakes:

• Thinking existing records get overwritten
• Believing changes are ignored
• Assuming check_cols is not valid syntax

4. You wrote this snapshot config:

snapshots:
  - name: order_snapshot
    strategy: timestamp
    updated_at: updated_at_column

But dbt throws an error. What is the likely cause?

medium

A. The 'updated_at' key should be inside 'config' block

B. The 'strategy' value 'timestamp' is invalid

C. The 'updated_at' key is missing quotes

D. The 'updated_at' key should be 'updated_at_column'

Solution

1. Step 1: Check snapshot config keys for timestamp strategy

   For 'timestamp' strategy, the correct key is updated_at_column, not updated_at.

2. Step 2: Identify the error cause

   Using updated_at causes dbt to error because it expects updated_at_column.

3. Final Answer:

   The 'updated_at' key should be 'updated_at_column' -> Option D

4. Quick Check:

   Timestamp strategy key = updated_at_column [OK]

Hint: Use 'updated_at_column' key for timestamp strategy [OK]

Common Mistakes:

• Using 'updated_at' instead of 'updated_at_column'
• Misplacing keys outside config block
• Assuming strategy value is wrong

5. You want to track changes in a product price over time using dbt snapshots. The source table has columns product_id, price, and last_updated. Which snapshot config is best to capture price changes accurately?

hard

A. strategy: timestamp with updated_at_column: last_updated

B. strategy: check with check_cols: ['price']

C. strategy: timestamp with updated_at_column: price

D. strategy: check with check_cols: ['last_updated']

Solution

1. Step 1: Understand the difference between timestamp and check strategies

   Timestamp strategy uses a datetime column to detect changes; check strategy compares specified columns directly.

2. Step 2: Analyze columns for tracking price changes

   Price changes may not always update 'last_updated' reliably, so checking 'price' column directly is safer.

3. Step 3: Choose the best config

   Using strategy: check with check_cols: ['price'] ensures any price change triggers a new snapshot version.

4. Final Answer:

   strategy: check with check_cols: ['price'] -> Option B

5. Quick Check:

   Check strategy on price column = accurate price tracking [OK]

Hint: Use check strategy on price column for accurate tracking [OK]

Common Mistakes:

• Using timestamp on a non-datetime column
• Relying on last_updated which may be unreliable
• Checking wrong columns for changes