DBT DBT Snapshots & Incremental Models 3 — Questions and Answers
Question 1: What does the `invalidate_hard_deletes` config on a dbt snapshot do?
- Physically deletes rows from the snapshot table
- Sets dbt_valid_to on snapshot rows whose source rows have been deleted (Correct answer)
- Prevents any deletions from the source from being tracked
- Validates that no rows have been deleted before running
Correct answer: Sets dbt_valid_to on snapshot rows whose source rows have been deleted
When `invalidate_hard_deletes: true`, dbt closes out snapshot rows for source records that no longer exist by setting `dbt_valid_to` to the current timestamp.
Question 2: Which `incremental_strategy` inserts all new rows without checking for duplicates, suitable for append-only event streams?
- merge
- delete+insert
- append (Correct answer)
- upsert
Correct answer: append
The `append` strategy simply inserts new rows without any deduplication or updating, ideal for immutable event logs.
Question 3: What happens to an incremental model on its very first run when the target table does not yet exist?
- dbt raises an error
- It runs as a full table build, ignoring the is_incremental() filter (Correct answer)
- It creates an empty table and exits
- It prompts the user to run --full-refresh first
Correct answer: It runs as a full table build, ignoring the is_incremental() filter
On the first run, `is_incremental()` returns false because the table doesn't exist, so dbt executes the full query and creates the table.
Question 4: In a dbt snapshot using the `check` strategy, what must you specify under `check_cols`?
- The primary key column only
- The list of columns whose changes should be tracked (Correct answer)
- All columns in the source table
- The timestamp column to compare
Correct answer: The list of columns whose changes should be tracked
`check_cols` lists the specific columns dbt should hash and compare; if any of them change, a new snapshot row is created.
Question 5: Which column added by dbt snapshots holds a hash of all snapshot-tracked columns for change detection?
- dbt_row_hash
- dbt_scd_id (Correct answer)
- dbt_unique_key
- dbt_checksum
Correct answer: dbt_scd_id
`dbt_scd_id` is a hash-based unique identifier generated by dbt for each snapshot row, combining the unique key and `dbt_updated_at` to ensure row-level uniqueness.
Question 6: What `on_schema_change` config value causes dbt to add new source columns to an existing incremental table automatically?
- ignore
- fail
- append_new_columns (Correct answer)
- sync_all_columns
Correct answer: append_new_columns
`append_new_columns` instructs dbt to ALTER the incremental table to add any new columns found in the source query without rebuilding the table.
DBT DBT Snapshots & Incremental Models 3
What does the `invalidate_hard_deletes` config on a dbt snapshot do?