Override Snapshot meta columns dbt_updated_at, dbt_valid_from, dbt_valid_to, dbt_scd_id

ashishkumar0705 · June 29, 2023, 11:16pm

Hello All,

While implementation of SCD Type 2 using dbt Timestamp & Check strategy .
Below Columns naming convention is getting populated .
dbt_updated_at, dbt_valid_from, dbt_valid_to, dbt_scd_id

Tried changing the macro for first time it works , second run it fails stating column missing .

Could you please share the link or guide how to override these naming convention in snapshots strategy .

joellabes · June 29, 2023, 11:48pm

I generally recommend that people treat their snapshots as sources with fixed column names, and then build downstream models on top of them where you can rename the columns as you see fit. Is that an option for your use case?

ashishkumar0705 · June 29, 2023, 11:51pm

Nope , we have strict standard to follow with naming convention , staging load with scd type or any other strategy , should be in-line with organisation standards like cloud_load_timestamp etc…

joellabes · June 29, 2023, 11:59pm

This gist from my colleague @jeremyyeo demonstrates overriding the snapshot strategy code to have a custom dbt_valid_from date, but you can use it to see which macros you need to modify (build_snapshot_table and snapshot_staging_table) to have different column names:

gist.github.com

https://gist.github.com/jeremyyeo/7da6a6a4fd6dba598c04c431f74e91c0

README.md

## Customising dbt snapshots

Customising dbt snapshots so that `dbt_valid_from` dates can use a variable.

Macros that need to be overridden are in the [`materializations/snapshots`](https://github.com/dbt-labs/dbt-core/tree/main/core/dbt/include/global_project/macros/materializations/snapshots) folder in `dbt-core`.

1. Setup initial snapshot:

```sql
-- snapshots/snappy.sql

This file has been truncated. show original

build_snapshot_table.sql

{% macro build_snapshot_table(strategy, sql) %}

    select *,
        {{ strategy.scd_id }} as dbt_scd_id,
        {{ strategy.updated_at }} as dbt_updated_at,
        -- {{ strategy.updated_at }} as dbt_valid_from,
        '{{ var("my_date") }}'::date as dbt_valid_from,
        nullif({{ strategy.updated_at }}, {{ strategy.updated_at }}) as dbt_valid_to
    from (
        {{ sql }}

This file has been truncated. show original

snapshot_staging_table.sql


{% macro snapshot_staging_table(strategy, source_sql, target_relation) %}

    with snapshot_query as (

        {{ source_sql }}

    ),

    snapshotted_data as (

This file has been truncated. show original

ashishkumar0705 · June 30, 2023, 12:18am

No Luck , Same Error …!!!

First Run it works and second Run fails with missing columns.

FIRST RUN : -------
$ dbt snapshot -select scd_test_snapshot
00:09:41 Running with dbt=1.5.2
00:09:42 Registered adapter: bigquery=1.5.3
00:09:42 Found 0 models, 0 tests, 1 snapshot, 0 analyses, 359 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
00:09:42 The selection criterion ‘elect’ does not match any nodes
00:09:42
00:09:43 Concurrency: 8 threads (target=‘sandbox’)
00:09:43
00:09:43 1 of 1 START snapshot dbt_cif_staging.scd_test_snapshot … [RUN]
00:09:45 1 of 1 OK snapshotted dbt_cif_staging.scd_test_snapshot … [success in 2.09s]
00:09:45
00:09:45 Finished running 1 snapshot in 0 hours 0 minutes and 2.98 seconds (2.98s).
**00:09:45 **
00:09:45 Completed successfully
**00:09:45 **
00:09:45 Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

ERROR : -----

SECOND RUN : -------

$ dbt snapshot -select scd_test_snapshot
00:09:59 Running with dbt=1.5.2
00:10:00 Registered adapter: bigquery=1.5.3
00:10:00 Found 0 models, 0 tests, 1 snapshot, 0 analyses, 359 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
00:10:00 The selection criterion ‘elect’ does not match any nodes
00:10:00
00:10:01 Concurrency: 8 threads (target=‘sandbox’)
00:10:01
00:10:01 1 of 1 START snapshot dbt_cif_staging.scd_test_snapshot … [RUN]
00:10:01 1 of 1 ERROR snapshotting dbt_cif_staging.scd_test_snapshot … [ERROR in 0.38s]
00:10:01
00:10:01 Finished running 1 snapshot in 0 hours 0 minutes and 1.29 seconds (1.29s).
00:10:01
00:10:01 Completed with 1 error and 0 warnings:
00:10:01
00:10:01 Compilation Error in snapshot scd_test_snapshot (snapshots/scd_test_snapshot.sql)
00:10:01 Snapshot target is not a snapshot table (missing “dbt_scd_id”, “dbt_valid_from”, “dbt_valid_to”)
**00:10:01 **
00:10:01 > in macro materialization_snapshot_default (macros/materializations/snapshots/snapshot.sql)
00:10:01 > called by snapshot scd_test_snapshot (snapshots/scd_test_snapshot.sql)
00:10:01
00:10:01 Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Topic		Replies	Views
Customising Macros in DBT Help	1	1633	December 7, 2022
Custom strategy - macro Help	3	1542	December 8, 2022
Recreate SCD2 table with snapshots Help snapshots	3	115	September 17, 2024
Change default snapshot meta column names and values Archive	1	3365	January 6, 2021
Using snapshots while avoiding the meta-fields "dbt_scd_id", "dbt_valid_from", "dbt_valid_to" Help snapshots	1	1573	September 10, 2022

Override Snapshot meta columns dbt_updated_at, dbt_valid_from, dbt_valid_to, dbt_scd_id

Related topics