snapshot_meta_column_names

💡Did you know...

Available from dbt v1.9 or with the dbt Cloud "Latest" release track.

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:

Field	Meaning	Notes
dbt_valid_from	The timestamp when this snapshot row was first inserted and became valid.	The value is affected by the strategy.
dbt_valid_to	The timestamp when this row is no longer valid.
dbt_scd_id	A unique key generated for each snapshot row.	This is used internally by dbt.
dbt_updated_at	The updated_at timestamp of the source record when this snapshot row was inserted.	This is used internally by dbt.
dbt_is_deleted	A 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:

id	scd_id	modified_date	start_date	end_date	is_deleted
1	60a1f1dbdf899a4dd...	2024-10-02 ...	2024-10-02 ...	2024-10-03 ...	False
1	60a1f1dbdf899a4dd...	2024-10-03 ...	2024-10-03 ...		True
2	b1885d098f8bcff51...	2024-10-02 ...	2024-10-02 ...		False