Snapshot tables for historical tracking in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a snapshot in dbt.

dbt

snapshot my_snapshot {
  [1]: 'my_source_table'
  strategy: timestamp
  updated_at: updated_at_column
}

Drag options to blanks, or click blank then click option'

Atable

Btarget

Csource

Ddatabase

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'target' instead of 'source' causes dbt to fail to find the input table.

Confusing 'table' or 'database' as the keyword.

In dbt snapshots, the 'source' keyword specifies the source table to track changes from.

2fill in blank

medium

Complete the code to specify the snapshot strategy in dbt.

dbt

snapshot my_snapshot {
  source: 'my_source_table'
  strategy: [1]
  updated_at: updated_at_column
}

Drag options to blanks, or click blank then click option'

Atimestamp

Bmerge

Cincremental

Dappend

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'merge' which is not a valid snapshot strategy in dbt.

Confusing 'incremental' with snapshot strategies.

The 'timestamp' strategy tracks changes based on a timestamp column indicating last update time.

3fill in blank

hard

Fix the error in the snapshot config to correctly track changes by unique key.

dbt

snapshot my_snapshot {
  source: 'my_source_table'
  strategy: check
  unique_key: [1]
}

Drag options to blanks, or click blank then click option'

Aupdated_at_column

Bid_column

Cmerge_key

Dtimestamp_column

Attempts:

3 left

💡 Hint

Common Mistakes

Using a timestamp column as unique_key causes incorrect snapshot behavior.

Using a non-unique column leads to duplicate records.

The 'unique_key' should be the column that uniquely identifies each record, often an ID column.

4fill in blank

hard

Fill both blanks to create a snapshot that uses the 'check' strategy and tracks changes by 'email' column.

dbt

snapshot user_email_snapshot {
  source: 'users'
  strategy: [1]
  unique_key: [2]
}

Drag options to blanks, or click blank then click option'

Acheck

Btimestamp

Cemail

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'timestamp' strategy with 'email' unique key is inconsistent.

Using 'id' instead of 'email' when the task specifies email.

The 'check' strategy compares specified columns for changes, and 'email' is used here as the unique key.

5fill in blank

hard

Fill all three blanks to define a snapshot with 'timestamp' strategy, track by 'last_modified', and use 'user_id' as unique key.

dbt

snapshot user_activity_snapshot {
  source: [1]
  strategy: [2]
  unique_key: [3]
  updated_at: last_modified
}

Drag options to blanks, or click blank then click option'

A'user_activity'

Btimestamp

Cuser_id

D'users'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong table name for source.

Confusing unique_key with updated_at column.

The source table is 'users', strategy is 'timestamp', and unique_key is 'user_id' to track user activity changes.

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