Why Snapshot tables for historical tracking in dbt? - Purpose & Use Cases

The Big Idea

What if you could see every change in your data history without lifting a finger?

The Scenario

Imagine you have a spreadsheet tracking customer data every day. To see how a customer's info changed over time, you copy and paste the entire sheet daily into new files. It quickly becomes messy and hard to find past details.

The Problem

Manually copying data daily is slow and easy to mess up. You might miss changes or overwrite old info. Finding what changed and when becomes a frustrating hunt through many files.

The Solution

Snapshot tables automatically save each change in your data with timestamps. This means you keep a clean, organized history of every update without extra work. You can easily see what changed and when.

Before vs After

✗ Before

Copy entire table daily to new file
Compare files manually for changes

✓ After

Create snapshot in dbt
Dbt tracks changes and stores history automatically

What It Enables

Snapshot tables let you track data changes over time effortlessly, unlocking powerful historical analysis and auditing.

Real Life Example

A company tracks customer address changes over years to understand migration patterns and update marketing strategies based on accurate historical data.

Key Takeaways

Manual tracking of data changes is slow and error-prone.

Snapshot tables automate historical tracking with timestamps.

This enables easy analysis of how data evolves over time.

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