Snapshot tables for historical tracking in dbt - Step-by-Step Execution

Concept Flow - Snapshot tables for historical tracking

Start: Define source data

↓

Create snapshot config

↓

Run snapshot command

↓

dbt compares current source data with snapshot

↓

If changes detected

↓

Insert new record

↓

Snapshot table updated with history

↓

End

This flow shows how dbt creates and updates snapshot tables by comparing source data and storing changes over time.

Execution Sample

dbt

{{ config(
  strategy = 'timestamp',
  updated_at = 'last_updated',
  unique_key = 'customer_id',
  check_cols = ['name', 'email']
)}}

select * from raw.customers

This snapshot config tracks changes in the customers table using the last_updated timestamp.

Execution Table

Step	Action	Source Data Row	Snapshot Table State	Change Detected	Result
1	Initial snapshot run	{customer_id: 1, name: 'Alice', email: '[e-mail]', last_updated: '2024-01-01'}	Empty	Yes (new record)	Insert record into snapshot
2	Initial snapshot run	{customer_id: 2, name: 'Bob', email: '[e-mail]', last_updated: '2024-01-01'}	Contains Alice	Yes (new record)	Insert record into snapshot
3	Second run with no changes	{customer_id: 1, name: 'Alice', email: '[e-mail]', last_updated: '2024-01-01'}	Contains Alice, Bob	No	No insert
4	Second run with change	{customer_id: 1, name: 'Alice', email: '[e-mail]', last_updated: '2024-02-01'}	Contains Alice, Bob	Yes (email changed)	Insert new version of Alice
5	Second run with unchanged Bob	{customer_id: 2, name: 'Bob', email: '[e-mail]', last_updated: '2024-01-01'}	Contains Alice (2 versions), Bob	No	No insert

💡 All source rows processed; snapshot table updated with new versions only when changes detected.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5
Snapshot Table Rows	Empty	Alice v1	Alice v1, Bob v1	Alice v1, Bob v1	Alice v1, Alice v2, Bob v1	Alice v1, Alice v2, Bob v1

Key Moments - 2 Insights

Why does the snapshot insert a new record for Alice in step 4 even though customer_id is the same?

Because the email changed, dbt detects a difference in the tracked columns and inserts a new version to keep history, as shown in execution_table step 4.

Why is there no insert for Bob in step 5?

Bob's data did not change since last snapshot, so dbt skips inserting a new record, as shown in execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution table, what is the state of the snapshot table after step 2?

A Contains only Alice's first version

B Contains Alice and Bob's first versions

C Empty

D Contains Alice's second version

💡 Hint

Check the Snapshot Table State column in row for step 2.

At which step does dbt detect a change in Alice's data?

A Step 4

B Step 3

C Step 1

D Step 5

💡 Hint

Look for 'Change Detected' column showing 'email changed' in execution_table.

If the 'check_cols' included 'last_updated' only, what would happen at step 4?

A No new version inserted because email changed but not last_updated

B Snapshot table would be emptied

C New version inserted because last_updated changed

D All records would be duplicated

💡 Hint

Consider how 'check_cols' controls which columns trigger snapshot inserts, see execution_table step 4.

Concept Snapshot

Snapshot tables track changes in source data over time.
Use dbt snapshot with unique_key and strategy.
Changes in tracked columns insert new records.
Unchanged rows are skipped to save space.
Snapshot tables keep full history for analysis.

Full Transcript

This visual execution shows how dbt snapshot tables work for historical tracking. First, source data is defined. Then a snapshot configuration specifies the unique key, strategy, and columns to check for changes. When the snapshot runs, dbt compares current source rows with existing snapshot records. If a row is new or changed, dbt inserts a new record version into the snapshot table. If no changes are detected, no insert happens. The execution table traces each step with source data, snapshot state, and actions. Variable tracking shows how snapshot table rows grow only when changes occur. Key moments clarify why new versions are inserted or skipped. The quiz tests understanding of snapshot state and change detection. The snapshot table approach helps keep a full history of data changes for analysis and auditing.

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