dbt snapshotのHard deletesの挙動を確認する

概要

dbt snapshotのhard deletesってどんな感じで振る舞うんだろうってのを確かめている記事です

https://docs.getdbt.com/docs/building-a-dbt-project/snapshots#hard-deletes-opt-in

通常のsnapshot

データの準備

create or replace table lake.member
as
select
    member_id,
    age
from
    (
        select "hoge" as member_id, 12 age union all
        select "fuga" as member_id, 10 age
    )

snapshotをとる

{% snapshot member %}

{{
    config(
        target_schema="snapshots",
        unique_key="member_id",
        strategy="check",
        check_cols=[
            "age"
        ]
    )
}}

    select
        member_id,
        age
    from lake.member

{% endsnapshot %}

データを消す

create or replace table lake.member
as
select
    member_id,
    age
from
    (
        select "fuga" as member_id, 10 age
    )

再度snapshotをとる

変わらない

hard deleted

データの準備

create or replace table lake.member
as
select
    member_id,
    age
from
    (
        select "hoge" as member_id, 12 age union all
        select "fuga" as member_id, 10 age
    )

snapshotをとる

{% snapshot member %}

{{
    config(
        target_schema="snapshots",
        unique_key="member_id",
        strategy="check",
        check_cols=[
            "age"
        ],
        invalidate_hard_deletes=True,
    )
}}

    select
        member_id,
        age
    from lake.member

{% endsnapshot %}

データを消す

create or replace table lake.member
as
select
    member_id,
    age
from
    (
        select "fuga" as member_id, 10 age
    )

再度snapshotをとる

dbt_valid_toにタイムスタンプが入るんですねー
なるほど