Snapshot tables for historical tracking in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a snapshot table in dbt?

A snapshot table in dbt is a special table that records changes in source data over time, allowing you to track historical versions of records.

beginner

Why do we use snapshot tables for historical tracking?

We use snapshot tables to keep a history of data changes, so we can see how data looked at different points in time, which helps in auditing and analysis.

intermediate

What are the two main strategies for snapshots in dbt?

The two main strategies are 'timestamp' and 'check'. 'Timestamp' tracks changes based on a timestamp column, while 'check' compares specified columns for changes.

intermediate

How does the 'check' strategy detect changes in a snapshot?

The 'check' strategy compares specified columns in the source data to detect any changes. If any column value changes, dbt records a new version in the snapshot table.

beginner

What columns does dbt add automatically to a snapshot table?

dbt adds columns like 'dbt_valid_from' and 'dbt_valid_to' to mark the time range when a record version was valid, helping track the history clearly.

What is the main purpose of a snapshot table in dbt?

A To create backups of the database

B To track historical changes in data over time

C To delete old data automatically

D To speed up query performance

Snapshot tables are designed to keep historical versions of data to track changes over time.

Which snapshot strategy in dbt uses a timestamp column to detect changes?

A Check

B Incremental

C Merge

D Timestamp

The 'timestamp' strategy uses a timestamp column to identify when data has changed.

What does the 'check' strategy compare to detect changes?

A Only timestamp columns

B Only primary key columns

C All specified columns for any difference

D Row counts

The 'check' strategy compares specified columns to find any changes in their values.

Which columns does dbt add to snapshot tables to track validity periods?

A dbt_valid_from and dbt_valid_to

B created_at and updated_at

C start_date and end_date

D valid_from and valid_until

dbt adds 'dbt_valid_from' and 'dbt_valid_to' columns to mark when each record version is valid.

When should you use snapshot tables in your data project?

A When you want to track how data changes over time

B When you want to delete old data

C When you want to speed up data loading

D When you want to create indexes

Snapshot tables are useful to keep a history of data changes for analysis or auditing.

Explain what a snapshot table is and why it is useful for historical tracking in data projects.

Think about how you can see past versions of data.

Describe the difference between the 'timestamp' and 'check' snapshot strategies in dbt.

Consider how each detects changes in data.

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