It is currently not possible to modify the actual names of the snapshot metafields (
dbt_valid_from
and friends) even if you go down this path to customize the built in macros below - dbt-labs/dbt-core#7018
Customising dbt snapshots so that dbt_valid_from
dates can use a variable.
Macros that need to be overridden are in the materializations/snapshots
folder in dbt-core
.
- Setup initial snapshot:
-- snapshots/snappy.sql
{% snapshot snappy %}
{{
config(
target_database='development',
target_schema='dbt_jyeo',
unique_key='user_id',
strategy='check',
check_cols='all'
)
}}
SELECT *
FROM (VALUES (1, 'inactive', 123)
)
AS my_table(user_id, status, price)
{% endsnapshot %}
dbt snapshot --vars 'my_date: 1970-01-01'
USER_ID | STATUS | PRICE | DBT_SCD_ID | DBT_UPDATED_AT | DBT_VALID_FROM | DBT_VALID_TO |
---|---|---|---|---|---|---|
1 | inactive | 123 | 6fd297c5336d066f64983aca3899571a | 2021-11-22 01:02:20.909 | 1970-01-01 |
- Modify snapshot to simulate source data change and snapshot with new variable value:
-- snapshots/snappy.sql
{% snapshot snappy %}
{{
config(
target_database='development',
target_schema='dbt_jyeo',
unique_key='user_id',
strategy='check',
check_cols='all'
)
}}
SELECT *
FROM (VALUES (1, 'active', 123)
)
AS my_table(user_id, status, price)
{% endsnapshot %}
dbt snapshot --vars 'my_date: 1970-02-01'
USER_ID | STATUS | PRICE | DBT_SCD_ID | DBT_UPDATED_AT | DBT_VALID_FROM | DBT_VALID_TO |
---|---|---|---|---|---|---|
1 | active | 123 | 64297829e90911a07a647c9b8a0f193c | 2021-11-22 01:04:35.111 | 1970-02-01 | |
1 | inactive | 123 | 6fd297c5336d066f64983aca3899571a | 2021-11-22 01:02:20.909 | 1970-01-01 | 1970-02-01 00:00:00.000 |
BigQuery Testing (dbt 1.0.7)
Macro overrides:
Steps:
dbt snapshot --vars 'my_date: 1970-01-01'
and check the output:dbt snapshot --vars 'my_date: 1970-02-01'
and check the output: