# Snapshot timestamp strategy clarification

**URL:** <https://discourse.getdbt.com/t/snapshot-timestamp-strategy-clarification/4569>\
**Category:** Archive\
**Created:** [July 5, 2022, 10:24pm UTC](https://discourse.getdbt.com/t/snapshot-timestamp-strategy-clarification/4569 "2022-07-05T22:24:35Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![SignalRunner](https://avatars.discourse-cdn.com/v4/letter/s/d2c977/32.png) [@SignalRunner](https://discourse.getdbt.com/u/SignalRunner)\
**Post date:** [July 5, 2022, 10:24pm UTC](https://discourse.getdbt.com/t/snapshot-timestamp-strategy-clarification/4569/1 "2022-07-05T22:24:35Z")

</div>

Hi DBT helpfuls,  
I have a 30 million row snapshot with strategy timestamp. I was expecting the DBT logic under the hood would be similar to having:  
…  
where updated\_at \> (select max(updated\_at) from {{ this }})  
…  
which executes within 2 seconds.  
But it clearly doesn’t as it takes 20 minutes+, so I assume it’s still looking up every PK or doing something else?

Is there a way to configure the snapshot to evaluate the latest updated\_at ONLY, as per the above? Or what is going on here?

Thanks for any help

---

<div class="post-metadata">

**Author:** ![smartinez](https://avatars.discourse-cdn.com/v4/letter/s/e68b1a/32.png) [@smartinez](https://discourse.getdbt.com/u/smartinez)\
**Post date:** [July 7, 2022, 1:37am UTC](https://discourse.getdbt.com/t/snapshot-timestamp-strategy-clarification/4569/2 "2022-07-07T01:37:38Z")

</div>

We had a similar issue at my company where we had some tables as large as 16B rows.

Ultimately our solution was to add `where updated_at > (select date_add(updated_at, -7) from {{ this }})`

to the snapshot definition to have a rolling 7-day window. While we may miss some late arriving facts, this was a reasonable compromise since before the query would run over two hours, or time out.

You can see the relevant code [here](https://sourcegraph.com/github.com/dbt-labs/dbt-core/-/blob/core/dbt/include/global_project/macros/materializations/snapshots/helpers.sql?L37)

---

<div class="post-metadata">

**Author:** ![SignalRunner](https://avatars.discourse-cdn.com/v4/letter/s/d2c977/32.png) [@SignalRunner](https://discourse.getdbt.com/u/SignalRunner)\
**Post date:** [July 7, 2022, 10:21pm UTC](https://discourse.getdbt.com/t/snapshot-timestamp-strategy-clarification/4569/3 "2022-07-07T22:21:32Z")

</div>

Yes great - thanks smartinez.

The only problem with that is if the snapshot table does not already exist, the reference to {{ this }} causes an error. So for new deployments this is a problem.  
I don’t suppose you know if there is a way to detect if the snapshot table already exists? I’ve tried to build this condition into the where clause as well - interrogating the infomation\_schema for the table, but it evaluates all conditions, so still generates an error when the snapshot table is not present.

---

<div class="post-metadata">

**Author:** ![smartinez](https://avatars.discourse-cdn.com/v4/letter/s/e68b1a/32.png) [@smartinez](https://discourse.getdbt.com/u/smartinez)\
**Post date:** [July 8, 2022, 5:08pm UTC](https://discourse.getdbt.com/t/snapshot-timestamp-strategy-clarification/4569/4 "2022-07-08T17:08:52Z")

</div>

IIRC, the first time a snapshot runs, it generates the initial build code. You can probably try a `ref()` in the subselect instead and see if that works.

---

<div class="post-metadata">

**Author:** ![smartinez](https://avatars.discourse-cdn.com/v4/letter/s/e68b1a/32.png) [@smartinez](https://discourse.getdbt.com/u/smartinez)\
**Post date:** [July 15, 2022, 10:37pm UTC](https://discourse.getdbt.com/t/snapshot-timestamp-strategy-clarification/4569/5 "2022-07-15T22:37:19Z")

</div>

I want to amend my statement here and open this up for a larger discussion.

Adding a time window to the snapshot definition will add it to the first query in the MERGE cte set that makes up a snapshot.

```auto
    with snapshot_query as (

        {{ source_sql }}

    ), ...

```

If you include the `invalidate_hard_deletes` flag, then your snapshotted query will surely include records that are not present in your source query, which looks like dbt will expire those perfectly valid records:

```auto
        select
            'delete' as dbt_change_type,
            source_data.*,
            {{ snapshot_get_time() }} as dbt_valid_from,
            {{ snapshot_get_time() }} as dbt_updated_at,
            {{ snapshot_get_time() }} as dbt_valid_to,
            snapshotted_data.dbt_scd_id

        from snapshotted_data
        left join deletes_source_data as source_data on snapshotted_data.dbt_unique_key = source_data.dbt_unique_key
        where source_data.dbt_unique_key is null

```

Right now some of our larger tables (i.e. transactions) take in excess of 30 minutes to run. Even larger ones have timed out.