# Periodic Snapshot Fact Tables

**URL:** <https://discourse.getdbt.com/t/periodic-snapshot-fact-tables/18412>\
**Category:** Help\
**Created:** [February 25, 2025, 1:43pm UTC](https://discourse.getdbt.com/t/periodic-snapshot-fact-tables/18412 "2025-02-25T13:43:17Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![dmg](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dmg/32/5385_2.png) [@dmg](https://discourse.getdbt.com/u/dmg)\
**Post date:** [February 25, 2025, 1:43pm UTC](https://discourse.getdbt.com/t/periodic-snapshot-fact-tables/18412/1 "2025-02-25T13:43:17Z")

</div>

## The problem I’m having

I’m trying to implement a table that snapshots metrics daily for a particular customer id. The idea is to run it once a day and store the totals for the day. I found a medium article that proposed something like this, but I can’t get it to work (dbt run failed).

## The context of why I’m trying to do this

This would be a fact table that I would join with dim tables (scd type 2). Having this at the daily grain allows me to plot measures over time, and having the date also allows me to use scd type 2 to get the values of attributes that where valid at the time of the snapshot.

## What I’ve already tried

I tried the method suggested in [this medium article](https://rasiksuhail.medium.com/explore-custom-snapshotting-in-dbt-a-guide-to-capture-historical-data-a58cc2c91fd0) article but it is failing for me.

## Some example code or error messages

```auto
{{ config(
   materialized='snapshot',
   unique_key= ['order_id', 'snapshot_timestamp']
) }}

select 
    GETDATE() snapshot_timestamp,
    coalesce(orders.order_id,0) order_id,
    orders.customer_id,
    sum (case when payments.status = 'success' then amount end) as amount
from {{ ref('stg_orders') }} orders
left join {{ ref('stg_stripe_payments') }} payments on orders.order_id = payments.order_id
group by 1,2,3

```

The code above results in

```auto
Compilation Error in model fct_orders_snapshot (models/fct_orders_snapshot.sql)
  argument of type 'NoneType' is not iterable
  
  > in macro materialization_snapshot_default (macros/materializations/snapshots/snapshot.sql)
  > called by model fct_orders_snapshot (models/fct_orders_snapshot.sql)

```

I’ve also tried passing the unique key as = ‘order\_id, snapshot\_timestamp’ and ‘order\_id’, ‘snapshot\_timestamp’.

**Note**  
I’m using the jaffle\_shop data to test if this is doable in dbt, I know snapshotting this model makes little/no sense.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [February 25, 2025, 2:02pm UTC](https://discourse.getdbt.com/t/periodic-snapshot-fact-tables/18412/2 "2025-02-25T14:02:28Z")

</div>

Don’t get confused by some of the overloaded terminology in this domain. dbt snapshots are a tool for turning SCD1 data into SCD2 data.

I don’t know if this article is out of date or what, but `snapshot` is also \<[Materializations | dbt Developer Hub](https://docs.getdbt.com/docs/build/materializations#overview%7Cnot) a materialisation option\>, it’s \<[Add snapshots to your DAG | dbt Developer Hub](https://docs.getdbt.com/docs/build/snapshots#configure-snapshots-in-versions-18-and-earlier%7Ca) whole separate thing\> and now as of 1.9 is available as \<[Add snapshots to your DAG | dbt Developer Hub](https://docs.getdbt.com/docs/build/snapshots#configuring-snapshots%7CYAML) configuration\> with no SQL code. Snapshots are not ordinary dbt models at all.

So, for your use case, to the extent that that article is correct at all, it’s a red herring anyway. If you want to store metrics every day for a particular customer ID, you should probably use an incremental model. Most of the code that you have in your example will work in an incremental context.

You don’t get the “point in time” aspect that the article is talking about, but you should probably have a better strategy for that anyway since in principle, dbt models should be able to be full-refreshed at any time and that would destroy any history in the table.

Note: `@Mike Stanley` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1740492103826639?thread_ts=1740491020.629389&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![a\_slack\_user](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/a_slack_user/32/1629_2.png) [@a\_slack\_user](https://discourse.getdbt.com/u/a_slack_user)\
**Post date:** [February 25, 2025, 2:07pm UTC](https://discourse.getdbt.com/t/periodic-snapshot-fact-tables/18412/3 "2025-02-25T14:07:31Z")

</div>

Thanks for the reply \<@U05HW5JBVK3\>. I’ll test the incremental model instead.

Note: `@diogomaiog` originally [posted this reply in Slack](https://getdbt.slack.com/archives/CBSQTAPLG/p1740492434117469?thread_ts=1740491020.629389&cid=CBSQTAPLG). It might not have transferred perfectly.

---

<div class="post-metadata">

**Author:** ![akshat](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/akshat/32/5409_2.png) [@akshat](https://discourse.getdbt.com/u/akshat)\
**Post date:** [March 6, 2025, 2:06pm UTC](https://discourse.getdbt.com/t/periodic-snapshot-fact-tables/18412/4 "2025-03-06T14:06:42Z")

</div>

Hey, were you able to resolve it? I’m facing the same issue.  
I created a snapshot as below:  
**snapshots:  
- name: division\_snapshot  
relation: ref(‘Divisions’)  
config:  
schema: NessieCatalog  
database: DDATest\_Curated  
unique\_key: division\_id  
strategy: timestamp  
updated\_at: airbyte\_run\_jobtime**

---

<div class="post-metadata">

**Author:** ![dmg](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/dmg/32/5385_2.png) [@dmg](https://discourse.getdbt.com/u/dmg)\
**Post date:** [March 7, 2025, 2:07pm UTC](https://discourse.getdbt.com/t/periodic-snapshot-fact-tables/18412/5 "2025-03-07T14:07:26Z")

</div>

Hi @akshat , I believe this worked in my tests:

```auto
{{ config(
   materialized='incremental',
   unique_key= ['customer_id', 'snapshot_timestamp']
) }}
select
   GETDATE() snapshot_timestamp,
   orders.customer_id,
   sum (case when payments.status = 'success' then amount end) as amount,
   count(distinct orders.order_id) total_orders
from {{ ref('stg_orders') }} orders
left join {{ ref('stg_stripe_payments') }} payments on orders.order_id = payments.order_id
group by 1,2

```

Hope it helps