My Analytics Were Lying About Where My Customers Lived — Here’s How I Fixed It With SCD Type 2

When a simple UPDATE in your source system silently rewrites history — and how dbt snapshots make your data warehouse tell the truth.

If you’ve been following this series, you know what FinSight Analytics is by now: a production-grade ELT pipeline I’ve been building brick by brick on dbt Core + Snowflake. We’ve handled schema drift, data corruption, late arriving data, and semi-structured JSON.

Each milestone solved a real production failure mode. This one is different.

This one isn’t about a pipeline breaking. It’s about a pipeline that runs perfectly — and still gives you wrong answers.

The Problem Nobody Talks About in Tutorials

Here’s the scenario. Alice Johnson is a customer. She lives in Seattle. She makes 10 transactions between January and April 2024. All good.

Then Alice moves to Austin.

Your upstream team runs:

UPDATE raw.customers SET city = 'Austin' WHERE customer_id = 'C001';

One line. One second. And just like that, every historical analysis you run on Alice is now wrong.

Your risk team asks: “Show me high-value transactions from Seattle customers in Q1 2024.”

Alice’s $250 transaction from March? Gone from the results. She lives in Austin now — retroactively, permanently, including all of 2024.

Your fraud team asks: “Did any customers change cities right before a large transaction?”

You can’t answer that. The old city is gone. There’s no record it ever existed.

This is the Slowly Changing Dimension problem. And in finance, it doesn’t just produce wrong dashboards — it produces indefensible audit trails.

What SCD Type 2 Actually Does

Most tutorials describe SCD Type 2 as “keeping history.” That’s technically correct but misses the point.

SCD Type 2 answers the question: “What did we know at the time?”

Instead of overwriting Alice’s city, you:

• Close the old record: Alice was in Seattle from snapshot creation → change timestamp
• Insert a new record: Alice is in Austin from change timestamp → present

Now when someone queries “where was Alice on March 15, 2024?” — you join to the record that was valid on that date. Seattle. Correct. Defensible.

In banking specifically, this isn’t a nice-to-have. It’s a legal requirement. Regulators don’t accept “the data got overwritten” as an explanation for why your audit trail doesn’t match what actually happened.

Why dbt Handles This Better Than Everything Else

In most tools, SCD Type 2 means writing a MERGE statement yourself. That’s 60+ lines of SQL per dimension, with valid_from/valid_to logic, surrogate key generation, "close the old row" updates — all of it error-prone, all of it yours to maintain.

dbt gives you a first-class snapshot block that handles all of it. You declare:

• Which table to watch
• Which column is the unique key
• Which columns, when changed, trigger a new version

dbt handles the rest. Every run it compares source vs snapshot, closes changed rows, and inserts new ones. The timeline is maintained automatically.

Building It — Step by Step

First: Understanding the Source Schema

Before writing a single snapshot, I ran DESCRIBE on both dimension tables in Snowflake:

**raw.customers** → CUSTOMER_ID, FULL_NAME, EMAIL, CITY, SIGNUP_DATE

**raw.merchants** → MERCHANT_ID, MERCHANT_NAME, CATEGORY, CITY

This matters because the check_cols in your snapshot config must match actual column names — and you only want to track columns that represent meaningful business changes, not noise.

The Snapshots

I created a top-level snapshots/ folder — not inside models/. This is important. dbt only recognises the {% snapshot %} tag from the dedicated snapshots directory.

(First attempt I put them inside models/snapshots/ — dbt threw: *Encountered unknown tag 'snapshot'. Classic gotcha.)*

**snapshots/scd_customers.sql:**

{% snapshot scd_customers %}

{{
    config(
        target_schema='snapshots',
        target_database='finsight_db',
        unique_key='customer_id',
        strategy='check',
        check_cols=['email', 'city'],
        invalidate_hard_deletes=True
    )
}}

SELECT
    customer_id,
    full_name,
    email,
    city,
    signup_date
FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

**snapshots/scd_merchants.sql:**

{% snapshot scd_merchants %}

{{
    config(
        target_schema='snapshots',
        target_database='finsight_db',
        unique_key='merchant_id',
        strategy='check',
        check_cols=['category', 'city'],
        invalidate_hard_deletes=True
    )
}}

SELECT
    merchant_id,
    merchant_name,
    category,
    city
FROM {{ source('raw', 'merchants') }}

{% endsnapshot %}

Three config decisions worth explaining:

check_cols — I'm only watching email and city for customers, category and city for merchants. full_name and signup_date are excluded intentionally — name corrections are noise, signup date never changes. You don't want every minor data fix creating a new "version" of a customer.

strategy='check' — the alternative is timestamp, which relies on an updated_at column in the source. My raw tables don't have a reliable one, so check is correct here. dbt compares column values directly.

invalidate_hard_deletes=True — if a customer is deleted from source, dbt closes their snapshot row. Without this, deleted customers appear "current" forever. In finance, knowing a customer was deactivated matters.

First Run

dbt snapshot

1 of 2 OK snapshotted snapshots.scd_customers  [SUCCESS 5 in 3.64s]
2 of 2 OK snapshotted snapshots.scd_merchants  [SUCCESS 7 in 3.67s]

5 customers, 7 merchants. All with dbt_valid_to = NULL — meaning "this is the current version, no history yet." That's correct. History only builds when data changes and you re-run.

Querying the snapshot table immediately after:

scd_customers_initial_state

Every row has the same dbt_valid_from timestamp — the moment dbt first saw them — and dbt_valid_to = null. The dbt_scd_id column is a hash dbt generates as a surrogate key for each version row.

Simulating Real Change — Customers

Now the proof. Alice Johnson moves from Seattle to Austin:

UPDATE finsight_db.raw.customers
SET city = 'Austin'
WHERE customer_id = 'C001';

Re-run snapshot:

dbt snapshot

scd_customers  [SUCCESS 1]   ← exactly 1 change detected
scd_merchants  [SUCCESS 0]   ← nothing changed in merchants

dbt_snapshot_alice_city_change

Notice the precision: dbt didn’t reprocess all 5 customers. It detected exactly 1 change and processed only that. That’s the efficiency of check strategy.

Query the proof:

SELECT customer_id, full_name, city, dbt_valid_from, dbt_valid_to
FROM finsight_db.snapshots.scd_customers
WHERE customer_id = 'C001'
ORDER BY dbt_valid_from;

proof_dbt_snapshots_success_customers

Two rows for Alice:

Row City Valid From Valid To 1 Seattle 18:11:28 18:18:53 ← closed 2 Austin 18:18:53 null ← current

The dbt_valid_to on Row 1 and dbt_valid_from on Row 2 are the exact same timestamp. No gap. No overlap. The timeline is continuous and airtight.

This is your audit trail. Alice lived in Seattle. Then she moved. Both facts are preserved, timestamped, immutable.

Simulating Real Change — Merchants

Same exercise for merchants. Whole Foods recategorizes from Groceries to Crypto:

UPDATE finsight_db.raw.merchants
SET category = 'Crypto'
WHERE merchant_id = 'M001';

scd_customers  [SUCCESS 0]  ← untouched
scd_merchants  [SUCCESS 1]  ← M001 detected

dbt_snapshot_merchant_category_change

And why does this matter in finance specifically? A grocery store becoming Crypto mid-year is exactly the kind of merchant recategorization that fraud teams care about. Without SCD2, every historical transaction at Whole Foods would now show as a Crypto transaction. Your mart_category_spend would be silently lying about the entire history of that merchant.

The Point-in-Time Mart — Where It Becomes Business Value

Snapshots alone aren’t the payoff. The payoff is what you can query once you have them.

I built mart_customer_spend_history.sql — a mart that joins each transaction to the customer version that was valid at the time of the transaction:

LEFT JOIN {{ ref('scd_customers') }} c
    ON t.customer_id = c.customer_id
    AND t.transaction_date >= c.dbt_valid_from
    AND (
        t.transaction_date < c.dbt_valid_to
        OR c.dbt_valid_to IS NULL
    )

In plain English: “Give me the customer record where the transaction date falls inside that version’s valid window.”

Alice’s transactions from January 2024 → join to the Seattle row. Any future transactions she makes → join to the Austin row.

Historically accurate. Always.

The Troubleshooting That Made This Real

This is the part tutorials skip. Here’s what actually happened.

Bug 1— The Mart Returned 0 Rows

After building mart_customer_spend_history, querying it returned nothing. COUNT(*) = 0.

Two separate root causes, discovered by debugging from the inside out:

Root cause A — Case-sensitive status filter:

The mart had WHERE status = 'completed' but Snowflake stores it as 'COMPLETED'. Snowflake string comparisons are case-sensitive. The entire transactions CTE returned 0 rows before the join even ran.

-- diagnosis
SELECT DISTINCT status FROM finsight_db.staging.stg_transactions;
-- result: COMPLETED

Fix: WHERE status = 'COMPLETED'

Root cause B — Historical data predating the snapshot:

After fixing the case issue, still 0 rows for customer_id = 'C001'.

The join condition was:

t.transaction_date >= c.dbt_valid_from

Alice’s transactions are from 2024-01-05, 2024-03-15, 2024-04-02. The snapshot dbt_valid_from was 2026-05-26 18:11:28. So 2024 >= 2026 is always false.

This is not a bug — it’s a production reality. In a live system, snapshots run from day one alongside data ingestion. In my case, I loaded 2 years of historical data first and created snapshots today. The timelines don’t overlap.

Fix: Add a fallback condition for historical data that predates the snapshot:

LEFT JOIN {{ ref('scd_customers') }} c
    ON t.customer_id = c.customer_id
    AND (
        -- normal point-in-time join
        (
            t.transaction_date >= c.dbt_valid_from
            AND (t.transaction_date < c.dbt_valid_to OR c.dbt_valid_to IS NULL)
        )
        -- fallback: historical transactions use earliest known customer version
        OR (
            t.transaction_date < c.dbt_valid_from
            AND c.dbt_valid_from = (
                SELECT MIN(dbt_valid_from)
                FROM {{ ref('scd_customers') }}
                WHERE customer_id = t.customer_id
            )
        )
    )

After both fixes:

dbt run --select mart_customer_spend_history
SUCCESS 35

35 rows. All of Alice’s transactions correctly showing Seattle as customer_city_at_txn_time — because all her transactions happened in 2024, before she moved to Austin in 2026.

mart_point_in_time_proof

The point-in-time join works. History is preserved and queryable.

The Debugging Framework That Saved Me

When the mart returned 0 rows, here’s the exact order I diagnosed it:

Mart returns 0 rows
  → Is it missing one customer, or is the whole mart empty?
  → COUNT(*) = 0 → entire mart is empty
  → Problem is before the join, in the transactions CTE
  → Check the filter → DISTINCT status → found 'COMPLETED' vs 'completed'
  → Fix case → mart now has 35 rows
  → C001 still 0 → join condition problem
  → Compare transaction_date vs dbt_valid_from → 2024 vs 2026
  → Add historical fallback → 35 rows including C001

The lesson: always debug from the innermost CTE outward. Confirm each layer has data before blaming the join. Most “join problems” are actually “empty input problems.”

What This Milestone Proved

After 7 milestones, the FinSight pipeline now handles:

Problem Solution Schema drift on_schema_change + assert_no_schema_drift Data corruption force_replace surgical overwrite Late arriving data loaded_at system clock watermark Historical dimension changes SCD Type 2 snapshots Point-in-time accuracy Snapshot join pattern in marts

These aren’t tutorial exercises. Each one maps to a failure mode that breaks real production pipelines at real companies. The difference between a junior engineer and a production-ready one isn’t knowing the happy path — it’s knowing what breaks and having the patterns ready when it does.

What’s Next

Milestone 8 is Advanced Testing + Observability — dbt-expectations for Great Expectations-style tests, elementary-data for an observability dashboard, anomaly detection on transaction amounts, source freshness checks, and email alerting on test failures.

Because a pipeline that runs without breaking isn’t enough. You need to know it’s running correctly, before someone finds a wrong dashboard.

The full project is on GitHub: github.com/SriGayathri06/finsight-dbt

Let’s Talk

SCD Type 2 is one of those concepts that sounds academic until you’re staring at a compliance audit asking why your customer’s historical city doesn’t match the transaction records. The point-in-time join pattern isn’t glamorous — but it’s the difference between a data warehouse that tells the truth and one that tells a convenient story.

If you’ve implemented SCD2 differently — a timestamp strategy instead of check, a different fallback pattern for historical data, a DBT macro that generates the point-in-time join automatically — drop it in the comments. There’s more than one way to solve this, and the tradeoffs matter.

If this saved you debugging time or gave you a pattern worth using, a clap goes a long way 👏

Built with dbt Core 1.11.9, Snowflake Standard Edition, Windows 11 CMD. Full project: github.com/SriGayathri06/finsight-dbt