DBT Snapshots & Incremental Models Flashcards

What does the `invalidate_hard_deletes` config on a dbt snapshot do?

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.

Which `incremental_strategy` inserts all new rows without checking for duplicates, suitable for append-only event streams?

Answer: append

The `append` strategy simply inserts new rows without any deduplication or updating, ideal for immutable event logs.

What happens to an incremental model on its very first run when the target table does not yet exist?

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.

In a dbt snapshot using the `check` strategy, what must you specify under `check_cols`?

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.

Which column added by dbt snapshots holds a hash of all snapshot-tracked columns for change detection?

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.

What `on_schema_change` config value causes dbt to add new source columns to an existing incremental table automatically?

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.