Articles

SCD Type 2 SQL Interview Exercise: Late Changes, Effective Dates, and History Repair

Practice SCD Type 2 SQL with late changes, correction precedence, half-open intervals, and fact-history repair using a worked exercise and expected results.

A customer moved from Basic to Pro on March 1. In April, the warehouse receives a missing change: the customer had actually moved to Plus on February 15. Closing the current Pro row and inserting Plus would make today's state wrong. Ignoring the late record would leave February's history wrong.

This SCD Type 2 SQL interview exercise asks you to repair that history, explain the boundaries, and identify the facts affected by the correction.

Evidence boundary: the fixture and interview follow-ups below are original practice material, not a candidate-reported question. Official PostgreSQL documentation supports the SQL semantics; Kimball Group supplies the dimensional-modeling guidance. Business rules and production recommendations are labeled as exercise assumptions or design judgment.

What should a correct SCD Type 2 repair preserve?

Authoritative modeling guidance: Kimball describes Type 2 changes as new dimension rows with new surrogate keys, effective dates, and expiration dates. Its late-arriving-dimension guidance also recognizes that retroactive changes can require restating associated facts. Type 2 dimensions and late-arriving dimensions.

For this exercise, produce the corrected business timeline for each customer. Preserve subsequent changes, collapse adjacent identical states, and make rerunning the same source data produce the same intervals. The output is a proposed dimension history; assigning persistent surrogate keys and publishing changes are separate steps.

Ask these questions before writing a window function:

Does effective_from mean business validity, while arrived_on records delivery?
Can a correction replace an earlier assertion at the same effective date?
Which source field establishes correction precedence?
Does an unknown attribute count as a real state?
Must reporting show corrected history, or what the warehouse believed previously?

Exercise contract: dates are calendar dates in an agreed business timezone. Intervals are half-open: [valid_from, valid_to). A null end means no known ending. A higher revision supersedes an older assertion for the same customer and effective date. Conflicting payloads with the same revision are rejected before this query; arbitrary row order cannot settle them.

The input and expected repaired history

Run this setup in a scratch database. revision is local to one effective-date assertion, not a global ordering across the customer's entire history.

CREATE TEMP TABLE changes (
  customer_id TEXT NOT NULL,
  effective_from DATE NOT NULL,
  revision INTEGER NOT NULL,
  plan TEXT,
  arrived_on DATE NOT NULL
);

INSERT INTO changes VALUES
 ('C42', DATE '2026-01-01', 1, 'Basic', DATE '2026-01-02'),
 ('C42', DATE '2026-03-01', 1, 'Pro',   DATE '2026-03-02'),
 ('C42', DATE '2026-02-15', 1, 'Plus',  DATE '2026-04-01'),
 ('C42', DATE '2026-02-15', 2, 'Team',  DATE '2026-04-02'),
 ('C42', DATE '2026-02-20', 1, 'Team',  DATE '2026-04-03'),
 ('C42', DATE '2026-02-15', 2, 'Team',  DATE '2026-04-04'),
 ('C99', DATE '2026-01-01', 1, NULL,    DATE '2026-01-02'),
 ('C99', DATE '2026-02-01', 1, 'Basic', DATE '2026-02-02');

The April 4 row is a redelivery. The February 20 assertion repeats the state already established on February 15. Neither should create another business version. For C99, unknown-to-Basic is a change under this contract.

Customer	Plan	Valid from	Valid to
C42	Basic	2026-01-01	2026-02-15
C42	Team	2026-02-15	2026-03-01
C42	Pro	2026-03-01	NULL
C99	NULL	2026-01-01	2026-02-01
C99	Basic	2026-02-01	NULL

The important result is Team, not Plus, for the middle interval: revision 2 corrects revision 1. Pro remains current because its business effective date is later. Arrival order does not decide either interval boundary.

Build change points first, then derive intervals

The query has four responsibilities: collapse identical business assertions, select the winning revision, remove consecutive no-op states, and derive end dates from the remaining starts.

CREATE TEMP VIEW repaired_history AS
WITH assertions AS (
  SELECT DISTINCT customer_id, effective_from,
                  revision, plan
  FROM changes
), ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer_id, effective_from
    ORDER BY revision DESC
  ) AS revision_rank
  FROM assertions
), canonical AS (
  SELECT customer_id, effective_from, plan
  FROM ranked
  WHERE revision_rank = 1
), compared AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY effective_from
    ) AS position_in_history,
    LAG(plan) OVER (
      PARTITION BY customer_id ORDER BY effective_from
    ) AS previous_plan
  FROM canonical
), change_points AS (
  SELECT customer_id, effective_from, plan
  FROM compared
  WHERE position_in_history = 1
     OR plan IS DISTINCT FROM previous_plan
)
SELECT customer_id, plan,
       effective_from AS valid_from,
       LEAD(effective_from) OVER (
         PARTITION BY customer_id ORDER BY effective_from
       ) AS valid_to
FROM change_points;

SELECT * FROM repaired_history
ORDER BY customer_id, valid_from;

Official SQL behavior: LAG reads a preceding row and LEAD reads a following row within the specified ordering. Here, LEAD runs after no-op change points have been removed, so Basic ends at the next actual state transition. PostgreSQL window functions.

The first-row check matters. For C99, both its initial plan and the missing predecessor are null; null-safe comparison alone would remove the first row. IS DISTINCT FROM treats null as a comparable value, unlike ordinary inequality. PostgreSQL comparison predicates.

This query assumes conflict validation has already passed. Check distinct payload counts per (customer_id, effective_from, revision) and stop the affected history if more than one remains. A larger UUID is not evidence that one contradictory business assertion is correct.

Observed local result: the setup and transformation produced the five expected rows in DuckDB 1.5.2. The syntax uses PostgreSQL-supported constructs; this local execution verifies fixture logic, not PostgreSQL locking behavior or warehouse performance.

Why the usual expire-and-insert answer fails here

A current-row update works only under additional arrival and business rules. In this fixture, expiring Pro on February 15 would give a March-starting row an earlier end. Expiring it on April 1 would instead confuse delivery time with effective time.

Likewise, deriving intervals before removing no-op states leaves an unnecessary February 20 boundary. Removing that row afterward, without recomputing the preceding end, creates a gap. The sequence of transformations is part of correctness.

Design judgment: rebuilding all change points for one affected customer is often easier to reason about than hand-editing several intervals. That is a correctness baseline, not a claim that a full-table rebuild is always economical. Keep an authoritative assertion log so a later correction can reconstruct earlier decisions.

If only a compacted dimension remains, some source assertions may already be lost. For example, yesterday's no-op can become meaningful after today's correction to its predecessor. Do not promise arbitrary historical repair from information the system discarded.

Repair fact references without changing unrelated history

Suppose orders already reference persistent dimension keys. Before the correction, C42's Basic row has key 101 and Pro has key 102. After repair, retain those keys for the retained versions, shorten Basic's end, and allocate a new key for Team. Do not generate all dimension keys from ROW_NUMBER: inserting a historical state would renumber later versions and break references.

Under this exercise's corrected-history reporting contract, orders from February 15 through the instant before March 1 must resolve to Team. Orders before February 15 remain Basic; orders on March 1 remain Pro. Recompute affected downstream aggregates if those aggregates store plan-level results.

Use an explicit interval predicate for resolution:

SELECT f.order_id, d.plan
FROM staged_orders AS f
LEFT JOIN repaired_history AS d
  ON d.customer_id = f.customer_id
 AND f.ordered_on >= d.valid_from
 AND (f.ordered_on < d.valid_to OR d.valid_to IS NULL);

BETWEEN would include both endpoints. Half-open intervals avoid assigning a boundary-date order twice. PostgreSQL's range documentation distinguishes inclusive and exclusive bounds and describes exclusion constraints for preventing overlaps. PostgreSQL range types.

For production, explicitly coordinate dimension publication and fact remapping. One option is a transaction covering an affected customer's repair; another is building a new consistent snapshot and switching readers after validation. Merely surrounding statements with BEGIN does not explain competing writers. State the locking or serialization strategy, and retry the whole operation if the database rejects a concurrent transaction. PostgreSQL transaction isolation.

Keep the old-to-new mapping, input revision set, affected date range, and repair identifier. These records make rollback and reconciliation possible without guessing which facts changed.

Tests that expose a plausible but incorrect answer

Start with the expected rows above, then mutate one input at a time. An assertion should describe the business result, not merely confirm that the query executed.

Mutation or probe	Required observation
Replay every input row	Identical five-row history; no extra versions
Shuffle delivery order	Identical effective timeline
Order on February 14	C42 maps once to Basic
Order on February 15	C42 maps once to Team
Order on March 1	C42 maps once to Pro
Repeated Team assertion on February 20	No additional interval
First C99 state is NULL	Initial unknown interval remains
Two different plans for one revision	Conflict gate rejects the affected history
Order before the first known start	Zero matches, handled explicitly

Check structural invariants too: every closed interval has a strictly later end, adjacent retained states differ, intervals do not overlap, and each customer's final interval is open under this fixture's active-customer assumption. If account closure is allowed, the final-open requirement must change.

An interval table can look correct while a fact join doubles revenue. Count matches per original fact identifier and require exactly one for accepted facts. Route zero-match facts according to an explicit unknown-member or quarantine policy; do not silently discard them with an inner join.

How to explain the solution in an interview

A concise explanation is: “I separate delivery time from effective time, resolve corrections at each change point, remove adjacent equal states, then derive half-open intervals. I preserve later states and identify the fact range affected by the repair.”

If asked about scale, discuss the affected-key set, long customer histories, sorting, and supporting access paths. An index beginning with customer and effective date may help relevant lookups, but verify the actual plan and workload. Avoid promising a runtime from row count alone.

If asked to reproduce last month's report as originally known, identify the additional requirement: effective history answers what was valid, while recorded history answers what was believed at a particular processing time. This single-timeline exercise does not implement a bitemporal model. Retaining revisions is useful evidence, but consumers still need explicit as-known query semantics.