Skip to main content

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 NULL edge 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 JOIN on 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 AND ensure the event date falls between dbt_valid_from and dbt_valid_to.
  • Always use COALESCE(dbt_valid_to, '2099-12-31') to ensure currently active rows don't break the join logic.