# Snapshot behavior

**URL:** <https://discourse.getdbt.com/t/snapshot-behavior/9034>\
**Category:** Help\
**Created:** [July 6, 2023, 8:03pm UTC](https://discourse.getdbt.com/t/snapshot-behavior/9034 "2023-07-06T20:03:04Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![jeffsk](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/jeffsk/32/1250_2.png) [@jeffsk](https://discourse.getdbt.com/u/jeffsk)\
**Post date:** [July 6, 2023, 8:03pm UTC](https://discourse.getdbt.com/t/snapshot-behavior/9034/1 "2023-07-06T20:03:04Z")

</div>

Today we discovered an issue with the behavior of `dbt snapshot` that I believe is not unique to my use case, but could affect everyone who uses dbt. Before posting an issue on Github, I wanted to discuss it here to see if folks agree that this could be improved.

**Scenario:**  
invalidate hard deletes = True  
A record is snapshotted.  
Then the record is no longer in source, so it gets “ended” (`dbt_valid_to` is populated).  
Then later the record appears in the source again.  
I would expect it to be snapshotted again, but it does not.

More detail:  
When dbt creates the temp table that is used as the basis for the `merge` statement, flagging each record with “insertion” or “update”… my new record (the one that went away and came back) IS flagged with “insertion”. Yay! So I would guess in the next step, the record would be inserted into the snapshot table.

To understand why it is not re-inserted, we need to look at the merge statement:

```auto
    merge into {{ target }} as DBT_INTERNAL_DEST
    using {{ source }} as DBT_INTERNAL_SOURCE
    on DBT_INTERNAL_SOURCE.dbt_scd_id = DBT_INTERNAL_DEST.dbt_scd_id

    when matched
     and DBT_INTERNAL_DEST.dbt_valid_to is null
     and DBT_INTERNAL_SOURCE.dbt_change_type in ('update', 'delete')
        then update
        set dbt_valid_to = DBT_INTERNAL_SOURCE.dbt_valid_to

    when not matched
     and DBT_INTERNAL_SOURCE.dbt_change_type = 'insert'
        then insert ({{ insert_cols_csv }})
        values ({{ insert_cols_csv }})

```

Because we are merging `on DBT_INTERNAL_SOURCE.dbt_scd_id = DBT_INTERNAL_DEST.dbt_scd_id` regardless of `dbt_valid_to`, this record is considered `matched`. We know that `matched` records don’t get inserted.

This leaves me with a temp table that says “go ahead and insert this” and nothing gets inserted!

I considered modifying the macro, but it causes a lot of unintended complexity when you get really into it.

Question for the community: Do you see this as a bug? If not, why? Looking forward to hearing your thoughts!

---

<div class="post-metadata">

**Author:** ![DrFred59](https://avatars.discourse-cdn.com/v4/letter/d/51bf81/32.png) [@DrFred59](https://discourse.getdbt.com/u/DrFred59)\
**Post date:** [October 10, 2023, 1:31pm UTC](https://discourse.getdbt.com/t/snapshot-behavior/9034/2 "2023-10-10T13:31:18Z")

</div>

I think it works, but only if your “update\_at” field has evolved.  
Indeed, the field used for the merge (dbt\_scd\_id) corresponds to the combination of the PK and the “update\_at” field. If the latter has evolved, the result is “not matched”.

_md5(coalesce(cast(pk\_key as varchar ), ‘’)_  
_|| ‘|’ || coalesce(cast(DATE\_MAJ as varchar ), ‘’)_  
_) as dbt\_scd\_id_