Allow customizing names of metadata fields in snapshots

[gshank]

From #9848

Description

There are a handful of Snapshot meta fields:

• dbt_valid_from
• dbt_valid_to
• dbt_scd_id
• dbt_updated_at

These are fields dbt creates when creating the snapshot.

Folks want the ability to rename these meta fields - “I want to rename dbt_valid_from to grace_valid_from”- because of some corporate standard

Customers currently have to create a view on top of the snapshot

select 
    dbt_valid_to as grace_valid_to,
    ...
from {{ ref('my_snapshot') }}

We should instead provide the ability to rename these meta fields within the actual snapshot configuration.

draft of YML config:

{{
    config(
      target_database='analytics',
      target_schema='snapshots',
      unique_key='id',

      strategy='timestamp',
      updated_at='updated_at',
      
      snapshot_meta_column_names={
              dbt_valid_from: grace_valid_from,
              dbt_valid_to: grace_valid_to
      }
    )
}}

Open questions/thoughts:

• You should also be able to set a default for this at the project level

snapshots:
  my_project:
    +snapshot_meta_column_names={
      dbt_valid_from: grace_valid_from,
      dbt_valid_to: grace_valid_to
    }

• If I have an existing snapshot, and I want to rename the fields (add this config)… what should happen?
  • [let's go with this to start] option 1: throw an error
    • the error message should indicate that they’ve have to go in and manually update their existing snapshots with the new column names
  • option 2: map old names → new names
  • option 3: don’t touch the snapshots, automatically create a view on top of it
    • this breaks our "1:1" relationship from snapshot to warehouse object :((
  • option 4: some sort of codegen that codegens the view for them
  • option 5: execute the rename for them
    • but we’d need both the old name (old config) and the new name
    • not possible :(
  • option 6: we create new fields, and coalesce with old?
    • same problems as option 5

Acceptance Critera

• new config for snapshots to alias / override the names of the metadata fields dbt_field_name_overrides
• can set that config for individual snapshot, or project default
• throw an error if they have an existing snapshot and they try to add/change this config - this should error on insert if you change the meta field names, but [one idea] we could try to throw an error before running the snapshot DML by running adapters.get_columns_in_relation() on the existing snapshot and comparing the meta fields that exist in the current config to what exists in the warehouse
  • Create a follow-up issue for migration.

[lukzard]

up vote!

this should have been considered already at implementation...

[ChenyuLInx]

We need to think a bit about migration:

• we need to rename the column when user change these configs
• we only do it the first time when user does the change(maybe detect whether the snapshot query would fail).

Idea from @aranke : add comment to the dbt_valid_from field and leverage that information during migration.
https://docs.getdbt.com/reference/resource-configs/persist_docs

[graciegoheen]

Migration Example:

1. I update my config to:

snapshots:
  my_project:
    +snapshot_meta_column_names ={
      dbt_valid_from: grace_valid_from,
      dbt_valid_to: grace_valid_to
    }

2. I run my snapshots, dbt handles my renames from dbt_valid_from -> grace_valid_from and so on for me.
3. I update my config to:

snapshots:
  my_project:
    +snapshot_meta_column_names ={
      dbt_valid_from: gerda_valid_from,
      dbt_valid_to: gerda_valid_to
    }

4. I run my snapshots, dbt handles my renames from grace_valid_from -> gerda_valid_from and so on for me.

[FishtownBuildBot]

Opened a new issue in dbt-labs/docs.getdbt.com: dbt-labs/docs.getdbt.com#6112

[GRVsati]

{{
config(
target_database='ABC',
target_schema='snapshots',
unique_key='id',

  strategy='timestamp',
  updated_at='updated_at',
  
  snapshot_meta_column_names={
          'dbt_valid_from': 'date_eff_from',
          'dbt_valid_to': 'date_eff_to'
  }
)

}}

Running dbt-core = 1.7.18 with snowflake = 1.7.5 snapshot completed with no error but column didn't changed.

[graciegoheen]

@GRVsati Hi! 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.