Querying Snapshot Tables
You have successfully run dbt snapshot and built a beautiful Slowly Changing Dimension (SCD Type 2) table in Snowflake.
Now, how do you actually use it?
If you try to write a standard JOIN between your fct_orders table and your new snp_customers table using the customer_id, your query will explode. Because the snapshot table has multiple rows for a single customer (one for each historical state), a standard join will cause a massive fan-out, duplicating your revenue numbers.
You must join using a Validity Window.
Topics Covered
- Getting the Current State
- Getting the Historical State (The Validity Window)
- Handling the
NULLedge case
1. Getting the Current State
If your Tableau dashboard only cares about where the customer lives today, querying a snapshot table is incredibly easy.
You simply filter for the row where dbt_valid_to is NULL. By definition, the active record has no expiration date.
-- models/marts/dim_active_customers.sql
SELECT
customer_id,
customer_name,
city
FROM {{ ref('snp_customers') }}
WHERE dbt_valid_to IS NULL
Note: You can safely perform a standard JOIN against this model, because filtering out the historical rows guarantees customer_id is unique again!
2. Getting the Historical State (The Validity Window)
This is the most powerful use case for Snapshots.
Imagine Arjun lived in Mumbai in 2022, but moved to Delhi in 2023.
He placed an order on 2022-10-15. We want to join that order to the snp_customers table to see exactly which city he lived in at the time he placed the order.
We do this using a Between Join.
-- models/marts/fct_orders_with_historical_city.sql
SELECT
o.order_id,
o.order_amount,
o.order_date,
c.customer_name,
c.city as city_at_time_of_order
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('snp_customers') }} c
ON o.customer_id = c.customer_id
-- THE MAGIC HAPPENS HERE:
AND o.order_date >= c.dbt_valid_from
AND o.order_date < c.dbt_valid_to
How it works:
The JOIN looks at Arjun's order on 2022-10-15. It scans Arjun's rows in the snapshot table. It finds the "Mumbai" row (valid from 2022-01-01 to 2023-01-01), and it finds the "Delhi" row (valid from 2023-01-01 to NULL).
Because the order date falls between the timestamps of the Mumbai row, it joins correctly and ignores the Delhi row!
3. Handling the NULL Edge Case
There is a subtle bug in the SQL query we just wrote.
If Arjun placed an order today, the query would look for a row where today's date is < c.dbt_valid_to.
But wait... Arjun's currently active row has a dbt_valid_to of NULL!
In SQL, any comparison against NULL (like order_date < NULL) evaluates to FALSE. The JOIN will fail, and today's orders will have a missing city.
To fix this, we must use a COALESCE function to pretend the active row expires in the distant future.
The Perfect Historical Join:
-- models/marts/fct_orders_with_historical_city.sql
SELECT
o.order_id,
o.order_amount,
o.order_date,
c.customer_name,
c.city as city_at_time_of_order
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('snp_customers') }} c
ON o.customer_id = c.customer_id
-- The fixed validity window:
AND o.order_date >= c.dbt_valid_from
AND o.order_date < COALESCE(c.dbt_valid_to, '2099-12-31'::timestamp_ntz)
Summary
- A standard
JOINon a snapshot table will cause a fan-out (duplicate data). - To get the current state, add
WHERE dbt_valid_to IS NULL. - To get the historical state, join on the primary key
ANDensure the event date falls betweendbt_valid_fromanddbt_valid_to. - Always use
COALESCE(dbt_valid_to, '2099-12-31')to ensure currently active rows don't break the join logic.