Skip to main content

snapshot_meta_column_names

Starting in 1.9 or with versionless dbt Cloud.

snapshots/schema.yml
snapshots:
- name: <snapshot_name>
config:
snapshot_meta_column_names:
dbt_valid_from: <string>
dbt_valid_to: <string>
dbt_scd_id: <string>
dbt_updated_at: <string>
dbt_is_deleted: <boolean>

snapshots/<filename>.sql
{{
config(
snapshot_meta_column_names={
"dbt_valid_from": "<string>",
"dbt_valid_to": "<string>",
"dbt_scd_id": "<string>",
"dbt_updated_at": "<string>",
"dbt_is_deleted": "<boolean>",
}
)
}}

dbt_project.yml
snapshots:
<resource-path>:
+snapshot_meta_column_names:
dbt_valid_from: <string>
dbt_valid_to: <string>
dbt_scd_id: <string>
dbt_updated_at: <string>
dbt_is_deleted: <boolean>

Description

In order to align with an organization's naming conventions, the snapshot_meta_column_names config can be used to customize the names of the metadata columns within each snapshot.

Default

By default, dbt snapshots use the following column names to track change history using Type 2 slowly changing dimension records:

FieldMeaningNotes
dbt_valid_fromThe timestamp when this snapshot row was first inserted and became valid.The value is affected by the strategy.
dbt_valid_toThe timestamp when this row is no longer valid.
dbt_scd_idA unique key generated for each snapshot row.This is used internally by dbt.
dbt_updated_atThe updated_at timestamp of the source record when this snapshot row was inserted.This is used internally by dbt.
dbt_is_deletedA boolean value indicating if the record has been deleted. True if deleted, False otherwise.Added when hard_deletes='new_record' is configured.

However, these column names can be customized using the snapshot_meta_column_names config.

warning

To avoid any unintentional data modification, dbt will not automatically apply any column renames. So if a user applies snapshot_meta_column_names config for a snapshot without updating the pre-existing table, they will get an error. We recommend either only using these settings for net-new snapshots, or arranging an update of pre-existing tables prior to committing a column name change.

Example

snapshots/schema.yml
snapshots:
- name: orders_snapshot
relation: ref("orders")
config:
unique_key: id
strategy: check
check_cols: all
hard_deletes: new_record
snapshot_meta_column_names:
dbt_valid_from: start_date
dbt_valid_to: end_date
dbt_scd_id: scd_id
dbt_updated_at: modified_date
dbt_is_deleted: is_deleted

The resulting snapshot table contains the configured meta column names:

idscd_idmodified_datestart_dateend_dateis_deleted
160a1f1dbdf899a4dd...2024-10-02 ...2024-10-02 ...2024-10-03 ...False
160a1f1dbdf899a4dd...2024-10-03 ...2024-10-03 ...True
2b1885d098f8bcff51...2024-10-02 ...2024-10-02 ...False
0