How to Build a Slowly Changing Dimension in SQL

Your customers table has a plan column. A customer upgrades from Starter to Pro on March 10th, and you run an UPDATE. Done. Except now every historical query that joins against that customer says they were always on Pro. Revenue by plan for Q1? Wrong. Churn analysis by original tier? Gone. The dimension changed, and you overwrote history.

SCD Type 2 fixes this by keeping every version of a record with date ranges. Instead of one row per customer, you get one row per customer per version. The current row stays active, and old rows become a historical archive you can query at any point in time.

It's one of those patterns that sounds academic until you need to answer "which plan was this customer on when they placed that order?" and realize your current schema can't tell you.

The table structure

CREATE TABLE customers_scd (
  id            SERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  name          TEXT NOT NULL,
  plan          TEXT NOT NULL,
  region        TEXT NOT NULL,
  valid_from    TIMESTAMP NOT NULL,
  valid_to      TIMESTAMP NOT NULL DEFAULT '9999-12-31',
  is_current    BOOLEAN NOT NULL DEFAULT TRUE
);
 
-- Initial load: seed from current customer records
INSERT INTO customers_scd (customer_id, name, plan, region, valid_from)
SELECT
  id,
  name,
  plan,
  region,
  created_at   -- first version starts at signup
FROM customers;
 
CREATE INDEX idx_scd_customer_current
  ON customers_scd (customer_id, is_current)
  WHERE is_current = TRUE;
 
CREATE INDEX idx_scd_customer_range
  ON customers_scd (customer_id, valid_from, valid_to);

A few things to call out. The valid_to default of 9999-12-31 is the "still active" sentinel. Every version has a closed valid_from and an open valid_to boundary. Open meaning exclusive: a record valid from January 1st to March 10th covers all timestamps where ts >= valid_from AND ts < valid_to. Use <, not <=. If you use <=, two adjacent versions both match at the boundary timestamp and your point-in-time joins return duplicate rows.

The is_current flag is technically redundant with valid_to = '9999-12-31', but it saves you from writing that sentinel check in every query. Partial index on is_current = TRUE makes current-state lookups fast.

Applying changes: the expire-and-insert pattern

When a customer changes plan, you do two things in one transaction: expire the current row by setting its valid_to to now, then insert a new row with valid_from = now. Postgres doesn't have a native MERGE that handles SCD neatly, so the standard pattern is a CTE that chains UPDATE and INSERT.

WITH changed AS (
  -- Find current records that differ from the incoming data
  SELECT s.id AS scd_id, s.customer_id
  FROM customers_scd s
  JOIN customers c ON c.id = s.customer_id
  WHERE s.is_current = TRUE
    AND (s.plan != c.plan OR s.region != c.region)
),
expire AS (
  -- Close out the old version
  UPDATE customers_scd
  SET valid_to   = NOW(),
      is_current = FALSE
  FROM changed
  WHERE customers_scd.id = changed.scd_id
  RETURNING changed.customer_id
)
-- Insert the new version
INSERT INTO customers_scd (customer_id, name, plan, region, valid_from)
SELECT c.id, c.name, c.plan, c.region, NOW()
FROM customers c
JOIN expire e ON c.id = e.customer_id;

This runs as a single statement. The CTE finds customers whose current SCD record doesn't match the source, expires those records, and inserts fresh ones. If nothing changed, no rows get touched.

One gotcha: this compares plan and region but not name. That's intentional. Name changes (typo fixes, company rebranding) usually aren't analytically interesting. You track what matters. Adding every column to the diff creates version churn that bloats the table without adding insight.

Run this on a schedule (hourly, daily, whatever matches your source's update frequency) and the SCD stays current. In production, wrap it in a transaction with an explicit lock on the customer_id range if you're worried about concurrent updates, though for a batch process that's rarely an issue.

Point-in-time queries

The payoff. "What plan was customer 4821 on during June 2025?"

SELECT customer_id, name, plan, region, valid_from, valid_to
FROM customers_scd
WHERE customer_id = 4821
  AND valid_from <= '2025-06-15'
  AND valid_to   >  '2025-06-15';

Notice the boundary operators: <= on valid_from, strict > on valid_to. This is the exclusive upper bound pattern. If a customer's plan changed at exactly 2025-06-15 00:00:00, the new version has valid_from = '2025-06-15' and matches. The old version has valid_to = '2025-06-15' and doesn't, because > excludes it. No overlap, no double-match.

If you used >= on valid_to instead, both the old and new version would match at the boundary, and your query returns two rows. I've debugged this exact issue three times in different codebases. Always exclusive on the upper bound.

Point-in-time joins against fact tables

The real power is joining a fact table (orders, events, invoices) to the dimension version that was active when each fact occurred.

SELECT
  o.id           AS order_id,
  o.created_at,
  o.amount_cents / 100.0 AS amount,
  c.plan         AS plan_at_order_time,
  c.region       AS region_at_order_time
FROM orders o
JOIN customers_scd c
  ON c.customer_id = o.customer_id
  AND o.created_at >= c.valid_from
  AND o.created_at <  c.valid_to
WHERE o.created_at BETWEEN '2025-01-01' AND '2025-06-30'
ORDER BY o.created_at;

Every order gets the plan and region that were active when the order was placed, not the customer's current plan. Revenue by plan is now historically accurate. You can break down Q1 revenue knowing that the 200 orders from a customer who was on Starter in January and Pro by March get attributed to the correct tier for each period.

Without SCD, that same customer's entire order history gets tagged with whatever plan they're on today. If they upgraded, Starter revenue gets inflated into Pro revenue. If they downgraded, the reverse. Either way, your plan-level revenue reporting is fiction.

The index on (customer_id, valid_from, valid_to) makes this join efficient. Postgres uses it for the range scan, and the join stays fast even with millions of orders against hundreds of thousands of SCD versions.

Building SCD from a changelog in DuckDB

If your source system keeps an audit log or changelog table (many CRMs and billing platforms do), you can build the SCD with window functions instead of the expire-and-insert loop. DuckDB is good at this.

Say you have a customer_changes table with one row per change event:

CREATE TABLE customers_scd AS
SELECT
  customer_id,
  name,
  plan,
  region,
  changed_at AS valid_from,
  COALESCE(
    LEAD(changed_at) OVER (PARTITION BY customer_id ORDER BY changed_at),
    TIMESTAMP '9999-12-31'
  ) AS valid_to,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY changed_at DESC) = 1
    AS is_current
FROM customer_changes
ORDER BY customer_id, valid_from;

LEAD() grabs the next change timestamp for the same customer, which becomes the current version's expiry. The last version (no next row) gets NULL from LEAD, and COALESCE fills in the 9999-12-31 sentinel. The ROW_NUMBER() = 1 on descending order marks only the latest version as current.

This is a single pass over the changelog. No loops, no multi-statement transactions. If you're pulling change data from Stripe, HubSpot, or Salesforce into Fastero's DuckDB store, you can rebuild the full SCD on every sync run in seconds. Cross-source too: join Stripe subscription changes with CRM account updates to build a unified customer dimension that tracks both billing tier and account owner over time. Lineage tracking shows you exactly which source feeds which SCD column.

Type 1 vs Type 2 vs Type 3

SCD Type 2 isn't always the right answer.

Type 1: overwrite. Just UPDATE the row. No history. Use it for corrections (fixing a misspelled name, merging duplicate records) and for attributes nobody will ever need historically. Simple, and the right default for most columns.

Type 2: versioned rows. What this entire post covers. Use it when historical accuracy matters for reporting: pricing tier, account owner, region, segment. The tradeoff is table size and join complexity. A customer with 15 plan changes over two years produces 15 rows.

Type 3: previous-value column. Add previous_plan and plan_changed_at columns to the existing row. Tracks exactly one prior state. Useful when you only care about "what was the value before the most recent change" and nothing deeper. Simpler than Type 2, but collapses if you need to go back more than one step.

My rule of thumb: start with Type 1 (overwrite) for everything. Promote individual columns to Type 2 when someone asks a question that requires historical state. Don't pre-build SCD Type 2 for columns that nobody queries historically. You'll create maintenance burden for zero analytical value.

Type 3 I almost never use. If I care about history, I usually care about the full history, not just the previous value. And once you have the Type 2 machinery running, adding another tracked column is trivial.

Try Fastero free — pull customer data from any source into DuckDB, build slowly changing dimensions with SQL, and query any point in time. No credit card required.