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.