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 |
Hi @jeremyyeo
I have a question about snapshot in dimansion scd type 2. The first data load it works. But the problem is caused by the second loading. that has changed the data. For example, I changed the data. localfirstname and modifiendat where _id = '5f2965bd8fac2ee61b6d6cac' data is valid according to scd type2 principle, but id = '5f2965ba8fac2ee61b69e3c0' is also valid_to, can you suggest a fix?