SQL · Guides · October 29, 2026 · 14 min read

Slowly changing dimension type 2 in SQL: as-of queries and checks

A slowly changing dimension of type 2 (SCD2) is a table that keeps the full history of an attribute instead of overwriting it. When a customer’s tier, a product’s price or an employee’s department changes, the current row is closed and a new row is opened. Each row carries the period when it was true: valid_from (when the version started) and valid_to (when it stopped; NULL or a far-future date for the current version).

With that structure you can answer two kinds of questions that a plain “current value” table cannot:

• Point-in-time: what was the value on 1 August? WHERE valid_from <= t AND (valid_to > t OR valid_to IS NULL).
• As-of join: what was the value at the moment each event happened? Join on the key and on event_time falling inside the row’s period.

The rest of this guide covers both, plus the checks every SCD2 table needs: overlapping versions, gaps and missing ends. The examples use two real SCD2 tables from a training airline database: loyalty_tier_history (a member’s tier: basic, silver, gold, platinum) and fare_price_history (the fare for a route and cabin class). Every query was run on that data and the tables show the real output, so you can run each one yourself in the Nomto SQL room.

What an SCD2 table looks like

One member’s tier history:

SELECT member_id, tier, valid_from, valid_to
FROM loyalty_tier_history
WHERE member_id = 143
ORDER BY valid_from;

Three rules make this a well-formed history:

1. Half-open intervals. A row is valid from valid_from inclusive to valid_to exclusive. The basic row ends at exactly the moment the silver row starts, and 24 July 00:00 belongs only to silver.
2. No overlaps, no gaps. For each key, every moment is covered by exactly one row, from the first version on.
3. One current row. The latest version has an open end (valid_to IS NULL).

Some warehouses use 9999-12-31 instead of NULL for the open end, and sometimes an is_current flag. The logic below is the same; only the open-end condition changes.

Compared with the other common types: type 1 overwrites the value and keeps no history; type 2 adds a row per change (this guide); type 3 keeps a “previous value” column, so only one step of history.

The value at a point in time

To get every member’s tier as of 1 August, keep the rows whose period contains that moment:

SELECT tier, COUNT(*) AS members
FROM loyalty_tier_history
WHERE valid_from <= TIMESTAMP '2026-08-01 00:00'
  AND (valid_to > TIMESTAMP '2026-08-01 00:00' OR valid_to IS NULL)
GROUP BY tier
ORDER BY members DESC;

The two comparisons mirror the half-open rule: <= on the start, strict > on the end. Writing the end condition as OR valid_to IS NULL keeps current rows; without it every open version would be silently lost, because a comparison with NULL is never true.

Why BETWEEN is the wrong tool

BETWEEN includes both ends. On a half-open history that means a moment exactly on a boundary matches two rows:

SELECT member_id, tier, valid_from, valid_to
FROM loyalty_tier_history
WHERE member_id = 143
  AND TIMESTAMP '2026-07-24 00:00' BETWEEN valid_from
      AND COALESCE(valid_to, TIMESTAMP '9999-12-31');

Tier upgrades here take effect at midnight, so boundaries land exactly on midnight. Asking “who held which tier on 24 July at 00:00” with BETWEEN returns 47,770 rows for 47,648 members; the half-open condition returns 47,658. The remaining ten extra rows are not a query problem but a data problem, which brings us to checks.

Checking an SCD2 table: overlaps, gaps, missing ends

Before trusting a history, test it. LEAD() puts the start of the next version next to each row, and comparing it with the row’s own end classifies every row:

WITH h AS (
  SELECT member_id, tier, valid_from, valid_to,
    LEAD(valid_from) OVER (PARTITION BY member_id ORDER BY valid_from) AS next_from
  FROM loyalty_tier_history
)
SELECT
  CASE WHEN next_from IS NULL AND valid_to IS NULL THEN 'current, ok'
       WHEN next_from IS NULL THEN 'last row closed'
       WHEN valid_to IS NULL THEN 'open but has successor'
       WHEN valid_to = next_from THEN 'touches next'
       WHEN valid_to > next_from THEN 'overlaps next'
       ELSE 'gap before next' END AS status,
  COUNT(*) AS row_count
FROM h
GROUP BY 1
ORDER BY 2 DESC;

What each status means:

• touches next: the healthy case, end equals the next start.
• open but has successor: the old version was never closed. The member now has two “current” tiers. 110 rows.
• overlaps next: the old version was closed, but later than the new one started. For a few days the member held two tiers. 95 rows.
• gap before next: the end is earlier than the next start, so for a while the member had no tier at all. None here.
• last row closed: the latest version has an end. Legitimate if the entity was deleted; suspicious otherwise. None here.

The same check on the fare table, keyed by route and class, comes back clean:

WITH h AS (
  SELECT departure_airport, arrival_airport, fare_conditions, price, valid_from, valid_to,
    LEAD(valid_from) OVER (
      PARTITION BY departure_airport, arrival_airport, fare_conditions
      ORDER BY valid_from) AS next_from
  FROM fare_price_history
)
SELECT
  CASE WHEN next_from IS NULL AND valid_to IS NULL THEN 'current, ok'
       WHEN next_from IS NULL THEN 'last row closed'
       WHEN valid_to IS NULL THEN 'open but has successor'
       WHEN valid_to = next_from THEN 'touches next'
       WHEN valid_to > next_from THEN 'overlaps next'
       ELSE 'gap before next' END AS status,
  COUNT(*) AS row_count
FROM h
GROUP BY 1
ORDER BY 2 DESC;

Two smaller checks are worth adding: no row with valid_to <= valid_from (zero-length or inverted periods; there are none in either table), and no two rows with the same key and the same valid_from (also none).

Listing the overlapping pairs

The LEAD check only compares neighbours. To list every pair of versions that overlap, including a long-open row that overlaps several later ones, self-join the table:

SELECT a.member_id,
  a.tier AS tier_a, a.valid_from AS from_a, a.valid_to AS to_a,
  b.tier AS tier_b, b.valid_from AS from_b, b.valid_to AS to_b
FROM loyalty_tier_history a
JOIN loyalty_tier_history b
  ON b.member_id = a.member_id
 AND b.valid_from > a.valid_from
 AND b.valid_from < COALESCE(a.valid_to, TIMESTAMP '9999-12-31')
ORDER BY a.member_id
LIMIT 4;

The condition reads: “version b starts after version a starts and before a ends”. Because no two versions of one member share a start, this finds each overlapping pair exactly once. Across the table there are 214 such pairs for 205 members, and in 119 of them the older row was never closed. The general overlap test for two half-open intervals is a.start < b.end AND b.start < a.end; the version above is that test with the pair ordered by start to avoid listing it twice.

Repairing a history with LEAD

If the rule is “a version lasts until the next one starts”, you can rebuild every end from the starts alone. That is the standard repair for overlaps and unclosed rows:

SELECT member_id, tier, valid_from, valid_to,
  LEAD(valid_from) OVER (PARTITION BY member_id ORDER BY valid_from) AS valid_to_fixed
FROM loyalty_tier_history
WHERE member_id IN (150, 920)
ORDER BY member_id, valid_from;

After the repair the point-in-time query on 1 August returns 50,040 rows for 50,040 members, exactly one per member, instead of 50,082 rows. Use this only when “next start ends the previous version” is really the business rule. If gaps are legitimate (a membership was paused, a product was off sale), deriving ends with LEAD would erase them.

As-of joins: the value when the event happened

The most common use of SCD2 is attaching the historical value to facts. Each member ticket was booked at book_date; which tier did the member hold at that moment? Compare that with the naive version that just takes the current tier:

WITH mt AS (
  SELECT tm.ticket_no, tm.member_id, b.book_date
  FROM ticket_members tm
  JOIN tickets t ON t.ticket_no = tm.ticket_no
  JOIN bookings b ON b.book_ref = t.book_ref
  WHERE b.book_date >= '2026-07-01'
),
matched AS (
  SELECT mt.ticket_no, h.tier,
    ROW_NUMBER() OVER (PARTITION BY mt.ticket_no ORDER BY h.valid_from DESC) AS rn
  FROM mt
  JOIN loyalty_tier_history h
    ON h.member_id = mt.member_id
   AND mt.book_date >= h.valid_from
   AND (mt.book_date < h.valid_to OR h.valid_to IS NULL)
),
at_booking AS (
  SELECT ticket_no, tier FROM matched WHERE rn = 1
),
cur_m AS (
  SELECT mt.ticket_no, h.tier,
    ROW_NUMBER() OVER (PARTITION BY mt.ticket_no ORDER BY h.valid_from DESC) AS rn
  FROM mt
  JOIN loyalty_tier_history h ON h.member_id = mt.member_id AND h.valid_to IS NULL
),
cur AS (
  SELECT ticket_no, tier FROM cur_m WHERE rn = 1
)
SELECT 'tier at booking' AS version, COUNT(*) AS tickets,
  COUNT(*) FILTER (WHERE tier = 'basic') AS basic,
  COUNT(*) FILTER (WHERE tier = 'silver') AS silver,
  COUNT(*) FILTER (WHERE tier = 'gold') AS gold,
  COUNT(*) FILTER (WHERE tier = 'platinum') AS platinum
FROM at_booking
UNION ALL
SELECT 'current tier', COUNT(*),
  COUNT(*) FILTER (WHERE tier = 'basic'),
  COUNT(*) FILTER (WHERE tier = 'silver'),
  COUNT(*) FILTER (WHERE tier = 'gold'),
  COUNT(*) FILTER (WHERE tier = 'platinum')
FROM cur;

The difference is dramatic. Tiers were introduced on 1 July and members climb them by flying, so most tickets were bought while their owners were still basic. Credit those sales to the current tier and gold members appear to buy about 14 times and platinum members about 20 times more tickets than they actually bought while holding those tiers. Any analysis of “how does tier affect behaviour” has to use the as-of value.

Two details in the query:

• The join condition is the half-open rule again, with the event time in the place of the fixed moment.
• ROW_NUMBER() ... rn = 1 protects against overlaps. A plain join on this table produces 70,258 rows for 70,205 tickets, because tickets booked during an overlap match two versions and would be counted twice. Keeping the latest-starting version is one reasonable tie-break; repairing the table first with LEAD is the cleaner one.

Which moment to join on

An as-of join is only as good as the timestamp you choose. Fares are a good illustration: the price on a ticket was fixed when it was booked, not when the plane departed. Join the fare history to segments on September flights both ways and count how often the fare matches the amount paid:

SELECT tf.fare_conditions, COUNT(*) AS segments,
  COUNT(*) FILTER (WHERE tf.amount = p.price) AS at_booking_fare,
  COUNT(*) FILTER (WHERE tf.amount = c.price) AS at_departure_fare
FROM ticket_flights tf
JOIN tickets t ON t.ticket_no = tf.ticket_no
JOIN bookings b ON b.book_ref = t.book_ref
JOIN flights f ON f.flight_id = tf.flight_id
LEFT JOIN fare_price_history p
  ON p.departure_airport = f.departure_airport
 AND p.arrival_airport = f.arrival_airport
 AND p.fare_conditions = tf.fare_conditions
 AND b.book_date >= p.valid_from
 AND (b.book_date < p.valid_to OR p.valid_to IS NULL)
LEFT JOIN fare_price_history c
  ON c.departure_airport = f.departure_airport
 AND c.arrival_airport = f.arrival_airport
 AND c.fare_conditions = tf.fare_conditions
 AND f.scheduled_departure >= c.valid_from
 AND (f.scheduled_departure < c.valid_to OR c.valid_to IS NULL)
WHERE f.scheduled_departure >= '2026-09-01'
GROUP BY tf.fare_conditions
ORDER BY tf.fare_conditions;

Joined on the booking time, every Business and Comfort segment matches its fare. Joined on the departure time, between a fifth and a third do not, because many routes got a new fare on 1 September, after these tickets had been sold. (The Economy segments that do not match even at booking time are seats in certain cabin rows sold above the fare; the dataset documents this.) The SQL is identical apart from one column; the right column is a business question, not a technical one.

Building an SCD2 table from snapshots

Histories are often built from daily snapshots: a table with one row per key per day and the value on that day. Collapsing snapshots into versions is a gaps-and-islands problem: flag the days where the value changes, number the versions with a running sum, and take the first and last day of each. The query below creates daily snapshots for member 1243 from the history and collapses them back, which shows both directions:

WITH snapshots AS (
  SELECT CAST(d.dt AS DATE) AS snapshot_day, h.member_id, h.tier
  FROM generate_series(DATE '2026-07-01', DATE '2026-08-11', INTERVAL '1 day') AS d(dt)
  JOIN loyalty_tier_history h
    ON h.member_id = 1243
   AND h.valid_from <= d.dt
   AND (h.valid_to > d.dt OR h.valid_to IS NULL)
),
marked AS (
  SELECT snapshot_day, member_id, tier,
    CASE WHEN tier = LAG(tier) OVER (PARTITION BY member_id ORDER BY snapshot_day)
         THEN 0 ELSE 1 END AS changed
  FROM snapshots
),
versioned AS (
  SELECT snapshot_day, member_id, tier,
    SUM(changed) OVER (PARTITION BY member_id ORDER BY snapshot_day) AS version
  FROM marked
)
SELECT member_id, tier,
  MIN(snapshot_day) AS valid_from,
  CASE WHEN MAX(snapshot_day) = DATE '2026-08-11' THEN NULL
       ELSE MAX(snapshot_day) + 1 END AS valid_to
FROM versioned
GROUP BY member_id, tier, version
ORDER BY valid_from;

Grouping by version rather than by tier alone matters: if a member goes gold → silver → gold, the two gold periods must stay separate rows. The end is the day after the last snapshot (half-open again), and the version still present in the latest snapshot gets an open end. Snapshot-based histories have day precision: a change at 14:00 shows up at the next snapshot. For more on the flag-and-sum technique, see our guide to gaps and islands in SQL.

Checklist for working with SCD2

• Point-in-time: valid_from <= t AND (valid_to > t OR valid_to IS NULL); never BETWEEN.
• As-of join: the same condition with the event’s timestamp, and choose that timestamp deliberately.
• Count rows before and after an as-of join; if the count grows, the history has overlaps.
• Check every new history with LEAD: overlaps, gaps, unclosed versions, inverted periods.
• Repair with LEAD(valid_from) only if “the next version ends the previous one” is the real rule.
• When building from snapshots, group by a version number, not by the value.

Both history tables in this guide are part of the Nomto SQL course dataset, so every check and join here can be run and modified in the SQL room.

FAQ

What is a slowly changing dimension type 2?

A table that stores each change of an attribute as a new row with a validity period (valid_from, valid_to) instead of overwriting the old value, so you can reconstruct the value at any moment in the past.

How do I query the current row in an SCD2 table?

Filter on the open end: WHERE valid_to IS NULL (or = '9999-12-31', or is_current = true, depending on the convention). If that returns more than one row per key, the history has unclosed versions.

How do I join facts to an SCD2 dimension?

Join on the business key and on the event timestamp falling inside the version’s period: ON d.key = f.key AND f.event_time >= d.valid_from AND (f.event_time < d.valid_to OR d.valid_to IS NULL).

How do I find overlapping records in an SCD2 table?

Use LEAD(valid_from) OVER (PARTITION BY key ORDER BY valid_from) and flag rows where valid_to is NULL or greater than the next start, or self-join the table on a.valid_from < b.valid_to AND b.valid_from < a.valid_to for the same key.

Should valid_to be inclusive or exclusive?

Exclusive (half-open intervals) is the safer convention: the end of one version equals the start of the next, there is no need for “one second before” arithmetic, and the point-in-time condition never matches two rows of a correct history.