snapshot_meta_column_names

💡Did you know...

Available from dbt v1.9 or with the dbt "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: <string>

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": "<string>",
      }
    )
}}

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: <string>

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	Example
dbt_valid_from	The timestamp when this snapshot row was first inserted and became valid.	The value is affected by the strategy.	snapshot_meta_column_names: {dbt_valid_from: start_date}
dbt_valid_to	The timestamp when this row is no longer valid.		snapshot_meta_column_names: {dbt_valid_to: end_date}
dbt_scd_id	A unique key generated for each snapshot row.	This is used internally by dbt.	snapshot_meta_column_names: {dbt_scd_id: scd_id}
dbt_updated_at	The updated_at timestamp of the source record when this snapshot row was inserted.	This is used internally by dbt.	snapshot_meta_column_names: {dbt_updated_at: modified_date}
dbt_is_deleted	A string value indicating if the record has been deleted. (True if deleted, False if not deleted).	Added when hard_deletes='new_record' is configured.	snapshot_meta_column_names: {dbt_is_deleted: is_deleted}

All of these column names can be customized using the snapshot_meta_column_names config. Refer to the Example for more details.

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.

How dbt_scd_id is calculated

dbt_scd_id is a unique identifier generated for each row in a snapshot. dbt uses this identifier to detect changes in source records and manage versioning in slowly changing dimension (SCD) snapshots.

dbt's snapshot macro handles dbt_scd_id in the dbt-adapters repository.

The hash is computed by concatenating values of the snapshot's unique_key and either the updated_at timestamp (for the timestamp strategy) or the values in check_cols (for the check strategy), and then hashing the resulting string using the md5 function. This enables dbt to track whether the contents of a row have changed between runs.

Here's an example of a custom hash calculation that combines multiple fields into a single string and hashes the result using md5.

md5(
 coalesce(cast(unique_key1 as string), '') || '|' ||
 coalesce(cast(unique_key2 as string), '') || '|' ||
 coalesce(cast(updated_at as string), '')
)

The exact fields included in the hash depend on the snapshot strategy:

• timestamp strategy: The hash typically combines the unique_key columns and the updated_at value.
• check strategy: The hash combines the unique_key columns and the values of the columns listed in check_cols.

If you don’t want to use md5, you can customize the dispatched macro.

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