Snapshot tables for historical tracking in dbt - Time & Space Complexity

Time Complexity: Snapshot tables for historical tracking

O(n)

Understanding Time Complexity

When using snapshot tables in dbt, we want to know how the time to update snapshots changes as data grows.

We ask: How does the work grow when there are more records to track?

Scenario Under Consideration

Analyze the time complexity of the following dbt snapshot configuration.

snapshot:
  name: customer_snapshot
  strategy: timestamp
  updated_at: updated_at
  unique_key: customer_id

select * from source.customers

This snapshot tracks changes in the customers table by checking the updated_at timestamp for each record.

Identify Repeating Operations

Look for repeated work done during snapshot updates.

• Primary operation: Comparing each source record's timestamp to the snapshot's stored timestamp.
• How many times: Once for every record in the source table each time the snapshot runs.

How Execution Grows With Input

As the number of records grows, the snapshot process checks each record's timestamp.

Input Size (n)	Approx. Operations
10	10 timestamp checks
100	100 timestamp checks
1000	1000 timestamp checks

Pattern observation: The work grows directly with the number of records.

Final Time Complexity

Time Complexity: O(n)

This means the time to update the snapshot grows in a straight line as the number of records increases.

Common Mistake

[X] Wrong: "Snapshot updates only check changed records, so time stays constant no matter how many records exist."

[OK] Correct: The snapshot must scan all records to find which ones changed, so time grows with total records, not just changed ones.

Interview Connect

Understanding how snapshot updates scale helps you explain data freshness and performance in real projects.

Self-Check

What if we changed the snapshot strategy from timestamp to check columns? How would the time complexity change?

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