Snapshot tables for historical tracking in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of Snapshot Tables in dbt

What is the main purpose of using snapshot tables in dbt for historical tracking?

A To store only the latest state of data without any history.

B To create temporary tables for intermediate calculations during transformations.

C To capture and store changes in source data over time for auditing and trend analysis.

D To optimize query performance by indexing source tables.

💡 Hint

Think about why you would want to keep past versions of data.

Snapshot tables in dbt are designed to capture changes in source data over time, allowing you to track historical states for auditing, trend analysis, and debugging.

❓ Predict Output

intermediate

Output of a dbt Snapshot with Unique Key Changes

Given the following snapshot configuration and source data changes, what will be the output in the snapshot table after the second run?

-- Snapshot config
unique_key: id
strategy: timestamp
updated_at: last_updated

-- Source data before first run:
id | value | last_updated
1  | 100   | 2024-01-01 10:00:00
2  | 200   | 2024-01-01 10:00:00

-- Source data before second run:
id | value | last_updated
1  | 110   | 2024-01-02 10:00:00
2  | 200   | 2024-01-01 10:00:00
3  | 300   | 2024-01-02 10:00:00

A Snapshot table will have 4 rows: id=1 with value=100 (current=false), id=1 with value=110 (current=true), id=2 with value=200 (current=true), id=3 with value=300 (current=true).

B Snapshot table will have 3 rows: id=1 with value=110, id=2 with value=200, id=3 with value=300, all with current=true.

C Snapshot table will have 2 rows: id=1 with value=110, id=3 with value=300, both current=true; id=2 is removed.

D Snapshot table will have 3 rows: id=1 with value=100, id=2 with value=200, id=3 with value=300, all current=false.

💡 Hint

Remember that snapshots keep history and mark old rows as current=false.

When a row changes (id=1), the old row is marked current=false and a new row with updated value and current=true is added. New rows (id=3) are added as current=true. Unchanged rows (id=2) remain current=true.

🔧 Debug

advanced

Identifying the Cause of Missing Historical Rows in Snapshot

A dbt snapshot is configured with the 'check_cols' strategy to track changes. However, after multiple runs, the snapshot table only contains the latest version of each row, with no historical versions. What is the most likely cause?

A The snapshot is configured with 'strategy: timestamp' instead of 'check_cols'.

B The source table has no updated rows, so no new snapshot rows are created.

C The 'unique_key' is not correctly set, causing rows to overwrite each other.

D The 'check_cols' parameter is set to an empty list, so no columns are checked for changes.

💡 Hint

Think about what happens if no columns are checked for changes.

If 'check_cols' is empty, dbt does not detect any changes in rows, so it never creates new historical rows, resulting in only the latest snapshot per unique key.

❓ data_output

advanced

Number of Rows in Snapshot Table After Multiple Runs

Consider a snapshot configured with 'unique_key' on 'customer_id' and 'strategy: timestamp' using 'updated_at' column. The source table has 3 customers initially. Over 3 snapshot runs, customer 1 updates twice, customer 2 updates once, and customer 3 never updates. How many rows will the snapshot table contain after the third run?

A 5 rows: 3 original + 2 updates for customer 1 + 1 update for customer 2 (duplicates counted).

B 6 rows: 3 original + 2 updates for customer 1 + 1 update for customer 2.

C 7 rows: 3 original + 2 updates for customer 1 + 1 update for customer 2 + 1 original for customer 3.

D 6 rows: 1 original + 2 updates for customer 1 + 1 update for customer 2 + 2 for customer 3.

💡 Hint

Remember that unchanged rows do not create new snapshot rows.

Initial 3 rows exist. Customer 1 updates twice, so 2 new rows added. Customer 2 updates once, so 1 new row added. Customer 3 never updates, so no new rows. Total = 3 + 2 + 1 = 6 rows.

🚀 Application

expert

Designing a Snapshot for Slowly Changing Dimensions

You need to track changes in a customer dimension table where some columns rarely change (e.g., phone number) and others change frequently (e.g., last login). You want to keep historical versions only when rare-change columns update, ignoring frequent changes. Which snapshot configuration best achieves this?

A Use 'strategy: check' with 'check_cols' set to only the rare-change columns.

B Use 'strategy: timestamp' with 'updated_at' column that updates on any change.

C Use 'strategy: check' with 'check_cols' set to all columns including frequent-change columns.

D Use 'strategy: timestamp' with a custom 'updated_at' column that updates on any change.

💡 Hint

Think about how to ignore frequent changes in snapshot history.

Using 'strategy: check' with 'check_cols' limited to rare-change columns ensures snapshots only create new versions when those columns change, ignoring frequent updates like last login.

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