In a dbt snapshot table, what does the dbt_scd_id column identify?
answer
- unique_key alone is not enough here
- one row per version needs its own name
- a hash, and you never set it
- what dbt matches on when applying a run
- a good target for a unique test
basics
~20 sdbt_scd_id is a generated hash that uniquely identifies one version row in a dbt snapshot — the entity's unique_key combined with the timestamp that version starts at. It is dbt's internal row identity, not a business key.
solid answer
~40 sA snapshot table holds several rows per entity, one per version, so `unique_key` alone cannot identify a row. `dbt_scd_id` fills that gap: dbt generates it as a hash of the row's `unique_key` together with the timestamp it uses for that version — the source `updated_at` under the `timestamp` strategy, the run time under `check`. dbt uses it internally to tell which version rows already exist when applying a run's changes. Practically it gives you a natural target for a `unique` test on the snapshot and a stable handle for one version when debugging. Treat it as dbt's identity column rather than a key to publish: it is opaque, it is meaningful only inside dbt's own algorithm, and it says nothing that `unique_key` plus the validity window does not already say.
go deeper
Recognise it as one of the columns dbt adds to a snapshot, and know that it names a single version row rather than the entity itself.
Explain that it is a hash of unique_key plus the version's timestamp, that dbt matches on it when applying a run, and that a duplicate signals a non-unique source key.
Be ready to argue whether tool-internal metadata belongs in a published mart, and to use a unique test on it as an early warning for source-key or concurrency problems.
Own the boundary between tool-internal identifiers and keys your organisation publishes, so that replacing or rebuilding a snapshot later does not break downstream contracts.
## Why a snapshot needs its own identity column In the source table, `id` identifies a row. In the snapshot, `id` identifies an *entity* that may have twenty rows, one per version. Nothing in the business data uniquely names a single version, so dbt manufactures one: `dbt_scd_id`. It is generated by hashing the row's `unique_key` together with the timestamp dbt is using for that version. Under the `timestamp` strategy that is the source `updated_at`; under `check` it is the snapshot run time (which is also what lands in `dbt_updated_at` and `dbt_valid_from`). Two different versions of the same entity therefore hash differently, because their timestamps differ, while re-running the snapshot against unchanged data reproduces the same hash for the same version. ## What dbt uses it for That reproducibility is the point. A snapshot run builds a set of candidate rows and then has to apply them to the existing table without duplicating versions it already stored. `dbt_scd_id` is the key it matches on — it lets dbt recognise "this exact version already exists" deterministically, rather than re-comparing every column. It is an implementation detail of the snapshot materialization, and dbt maintains it for you; you never set it in your select. ## What it is useful for in your project Three honest uses: 1. **A test target.** Adding a `unique` generic test on `dbt_scd_id` in `schema.yml` is a cheap invariant. A duplicate there means something went badly wrong — typically a `unique_key` that is not actually unique in the source, or two snapshot processes writing concurrently. 2. **A debugging handle.** When you need to talk about one specific version row in a ticket or a query, `dbt_scd_id` names it unambiguously. 3. **A join key for version-level work** inside your own project, when a downstream model genuinely needs to pin to one version. ## What it is not It is not a business key and should not be exposed as one in a published mart. It is opaque, it carries no meaning to an analyst, and its value depends on dbt's internal hashing rather than on anything the business owns. If a downstream model needs a durable identifier for a dimension version, define one deliberately in your own modelling layer instead — that keeps you free to change or replace the snapshot later without breaking published keys. It is also not a change detector. Nothing about `dbt_scd_id` tells you *what* changed between two versions; that comes from comparing the versions' business columns, or from knowing which `check_cols` were being watched. ## A caveat on uniqueness Because the hash is built from `unique_key` plus a timestamp, two versions that share both would collide. That is not hypothetical. Under the `timestamp` strategy, a source that writes two distinct states carrying the same `updated_at` gives dbt no way to separate them. Under `check`, a `unique_key` that is not truly unique in the source produces multiple rows hashing identically within a single run, since they share the run timestamp. Both are source-data defects rather than dbt bugs, and both surface as a failing `unique` test on `dbt_scd_id` — which is the best argument for adding that test in the first place. ## Where it sits among the other metadata columns `dbt_valid_from` and `dbt_valid_to` describe *when* a version was true. `dbt_updated_at` records the timestamp dbt used when writing it. `dbt_scd_id` answers *which row is this*. dbt 1.9 lets you rename these metadata columns through configuration if a house standard requires different names, but the semantics are unchanged.
- Why is a unique test on dbt_scd_id worth adding to a dbt snapshot?The column should identify exactly one version row, so a duplicate is a real signal: usually a unique_key that is not unique in the source, or two snapshot processes writing concurrently. It costs one line of YAML and catches a class of corruption that otherwise surfaces much later as inflated counts in a downstream join.
- Should a published dimension expose dbt_scd_id as its surrogate key?Prefer not to. It is dbt's internal identity, opaque to analysts and tied to dbt's hashing rather than to anything the business owns. Adopting it as a published key couples downstream consumers to the snapshot implementation, so replacing or rebuilding the snapshot later breaks them. Define a key in your own modelling layer instead.
saying these in an interview costs you the question
- Calls dbt_scd_id a business key for the entity
- Thinks it identifies the entity rather than one version
- Believes dbt_scd_id records what changed
- Sets or overrides the column manually in the select