SQL slowly changing dimensions in a historical customer report

A customer can change region while their earlier purchases remain unchanged. Joining every purchase to today's customer record reallocates historical sales to the current region. If the report asks where the customer belonged when the purchase occurred, join against dated dimension versions instead.

A type-2-style dimension keeps successive versions with validity intervals. The reporting contract must define the interval boundaries, the handling of unknown history and whether the report uses historical or current attributes.

Read the dimension's time contract

In the advanced SQL lab, customer A belongs to North before March 1, 2026 and West from March 1 onward. valid_from is inclusive, valid_to exclusive, and a NULL valid_to represents an open-ended version.

A's January and February purchases total 30,000 paise. Its April purchase contributes 5,000. Historical attribution therefore sends 30,000 to North and 5,000 to West. Current-region attribution sends all 35,000 to West. Both views can be useful, but their labels must identify their different meanings.

Join using the purchase date

SELECT h.region, COUNT(*) AS purchase_count,
       SUM(p.amount_paise) AS historical_value_paise
FROM purchases AS p
JOIN customer_history AS h
  ON h.customer_id = p.customer_id
 AND p.purchased_at >= h.valid_from
 AND (h.valid_to IS NULL OR p.purchased_at < h.valid_to)
GROUP BY h.region
ORDER BY h.region;

The historical totals are North 70,000, South 35,000 and West 30,000 paise. They sum to 135,000 across eleven purchases. The predicates belong together: joining only on customer_id multiplies A's purchases because A has two dimension versions.

An inner join is safe for the final aggregation only after proving that every eligible purchase matches exactly one version. Otherwise it silently drops purchases with missing history. The next check tests that assumption.

Require exactly one matching version

SELECT p.purchase_id, COUNT(h.customer_id) AS matching_versions
FROM purchases AS p
LEFT JOIN customer_history AS h
  ON h.customer_id = p.customer_id
 AND p.purchased_at >= h.valid_from
 AND (h.valid_to IS NULL OR p.purchased_at < h.valid_to)
GROUP BY p.purchase_id
HAVING COUNT(h.customer_id) <> 1;

The fixture produces no exceptions. Zero matches indicate a gap or missing customer; more than one indicates overlapping versions. Count the matched dimension key rather than COUNT(*), because a left join still produces an output row when no dimension version matches.

For a production dimension, also validate unique version identifiers, nonempty intervals and no overlapping intervals per business key. Detecting overlap even when no transaction happens to fall inside it prevents a latent defect from appearing later.

The join behavior follows the table-expression rules described in the PostgreSQL documentation; this fixture executes the equivalent predicates in SQLite.

Contrast with a current-region report

SELECT h.region, SUM(p.amount_paise) AS current_region_value_paise
FROM purchases AS p
JOIN customer_history AS h ON h.customer_id = p.customer_id
WHERE h.valid_to IS NULL
GROUP BY h.region ORDER BY h.region;

This produces North 40,000, South 35,000 and West 60,000. The grand total stays 135,000 while regional attribution changes. Reconciliation of the grand total alone therefore cannot detect an incorrect time interpretation.

For a dated current-state snapshot, an as-of join is more precise than assuming every open-ended version is applicable. Future scheduled versions and malformed histories can break that shortcut. The fixture contains neither, but production data may.

Decide how corrections affect history

A late discovery that A moved on February 20 can require correcting historical intervals and recomputing affected reports. Record when the business change was effective separately from when the warehouse learned about it. If you need both “what was true then” and “what we believed then,” simple validity intervals alone are insufficient; preserve additional system-time history.

Exercise: change North's valid_to to March 2 while keeping West's valid_from at March 1, then insert a March 1 purchase. The exactly-one-match check should expose the overlap. Next create a one-day gap and confirm that it produces a zero-match exception.

NeuraPath's Data Analytics with Generative AI course connects SQL joins with reliable reporting models. A historical reporting project should demonstrate interval tests and explain why a correct grand total can coexist with incorrect regional results.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Compare month-to-date sales fairly across unequal months.
• Continue with Reconcile two systems with a full outer join.