a hard delete orphans every historical fact row; last year's numbers change retroactively
Why
SCD2 is where warehouses go quietly wrong. Every failure above produces a dimension with a plausible row count, no error and no orphan — and a different answer to revenue by segment last year.
The as-of join is the amplifier. gold.fct_order_line resolves customer_sk by validity interval, so overlapping intervals return two dimension rows for one fact row and revenue doubles for exactly the customers who changed. A zero-length interval does the opposite: the join matches nothing and the line lands on the UNKNOWN member (rule 7), where most dashboards filter it out of sight.
How
pythontests/invariants/test_scd2_boundaries.py
import datetime as dt
import pytest
from dwh.common.scd2 import apply_scd2
KEYS = ["customer_id"]
SCHEMA = "customer_id STRING, customer_segment STRING, street STRING"
def T(day: int, hour: int) -> dt.datetime:
return dt.datetime(2026, 3, day, hour)
def cust(segment="A", street="Bahnhofstr. 1"):
return {"customer_id": "C1", "customer_segment": segment, "street": street}
CASES = [
# name (as_of, extract) in processing order versions open
("no-change", [(T(1, 6), [cust()]), (T(2, 6), [cust()])], 1, 1),
("segment-change", [(T(1, 6), [cust()]), (T(2, 6), [cust("B")])], 2, 1),
("address-only", [(T(1, 6), [cust()]),
(T(2, 6), [cust(street="Domplatz 4")])], 1, 1),
("reopen-A-B-A", [(T(1, 6), [cust()]), (T(2, 6), [cust("B")]),
(T(3, 6), [cust()])], 3, 1),
("twice-one-day", [(T(1, 6), [cust()]), (T(1, 18), [cust("B")])], 2, 1),
("late-arrival", [(T(2, 6), [cust("B")]), (T(1, 6), [cust()])], 2, 1),
("deleted", [(T(1, 6), [cust()]), (T(2, 6), [])], 1, 0),
]
@pytest.mark.parametrize("case,extracts,versions,open_rows", CASES)
def test_scd2_boundary(spark, case, extracts, versions, open_rows):
state = None
for as_of, extract in extracts:
state = apply_scd2(
state, spark.createDataFrame(extract, SCHEMA), KEYS, as_of=as_of
)
rows = sorted(
state.where("customer_id = 'C1'").collect(),
key=lambda r: r._tech_valid_from,
)
assert len(rows) == versions
assert sum(1 for r in rows if r._tech_valid_to is None) == open_rows
# the two assertions that catch every case above, whatever the counts say
assert all(r._tech_valid_from < (r._tech_valid_to or dt.datetime.max)
for r in rows) # no zero-length
assert all(a._tech_valid_to == b._tech_valid_from
for a, b in zip(rows, rows[1:])) # no gap, no overlap
The counts are the specification; the last two assertions are the safety net. Contiguity and non-zero length hold for every case in the table, so a boundary nobody wrote a case for still fails the suite.
Doing it
Write these seven cases before writing apply_scd2They are the specification, and the argument about case three is the modelling meeting.
Assert contiguity and non-zero length in every case, not only the version counts
Treat _tech_valid_to as exclusive, and say so in the column commentTest a fact timestamped exactly on the boundary — it belongs to the later version.
Inject the clock as as_ofA transform that calls current_timestamp() cannot be tested for any of these cases.
Close intervals on deletion; never hard-delete from a dimensionErasure requests are a different mechanism entirely.
Embedding it
Give them the table with the 'correct result' column blank and let them fill it inEvery disagreement is a decision the project had not made.
Ask which case their implementation does not handle before you look at itThey usually know, and saying it out loud is cheaper than you finding it.
Have them break it deliberately: change the interval predicate from < to <=Run the reconciliation, and watch revenue move. Nobody forgets that number.
Defaults
Seven cases, parametrised, one file, run on every commit
Exclusive end, NULL = open, asserted in every case
Clock injected as a parameter; current_timestamp() never called inside a transform
Deletion closes the interval and sets _tech_is_current = false
_tech_hash covers only the versioned attributesRule 4 is then enforced by the hash rather than by an if-statement someone can delete.
Gotchas
An inclusive _tech_valid_toThe fact row on the boundary matches two versions, the join fans out, and revenue for that customer doubles on exactly the day they changed segment. It is a small enough error to survive until year-end reconciliation.
Zero-length intervals from a DATE-granularity clock when two extracts land on the same dayRevenue does not disappear — it moves to the UNKNOWN member, and 'unknown' is the bar most dashboards hide.
Late extracts appended instead of splitting the intervalThis is the longest-lived defect of the seven because it needs an out-of-order load to appear at all, so it survives every test run in a well-behaved test environment.
Hashing all columns into _tech_hashAddress corrections then create versions, rule 4 is violated in the direction nobody checks, and the dimension grows without a single error being raised.