BETWEEN can double-count a fact at an SCD2 version boundary

A customer changes tier at midnight. A payment happens at exactly that instant. A historical join returns the payment twice: once under the old tier and once under the new one.

This can happen even when the history has no duplicate change records. An inclusive upper boundary is enough.

Put a fact on the boundary

Assume the following effective changes for customer c. They arrive in this order:

1. basic, effective January 1
2. premium, effective January 3
3. plus, effective January 2

The third change arrives late. Once all three changes are known, the valid-time history is:

Tier	Valid from, inclusive	Valid to, exclusive
basic	January 1	January 2
plus	January 2	January 3
premium	January 3	open

A fact at January 2 belongs to plus. A fact at January 3 belongs to premium.

SQLite’s BETWEEN documentation defines it using an inclusive lower and upper comparison. So joining a fact timestamp between valid_from and valid_to includes both adjoining intervals at their shared boundary.

For these half-open intervals, use:

f.at >= h.valid_from
AND (h.valid_to IS NULL OR f.at < h.valid_to)

The business contract chooses the interval convention. The SQL must implement that convention consistently.

A complete local experiment

This Python script creates a SQLite database in memory. It compares an inclusive join with a half-open join. The amounts are synthetic minor units in one currency.

Save it as temporal_join_demo.py and run it with Python.

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE changes(customer TEXT, valid_from TEXT, tier TEXT);
CREATE TABLE facts(id TEXT, customer TEXT, at TEXT, amount_minor INTEGER);
INSERT INTO changes VALUES
 ('c', '2026-01-01T00:00:00Z', 'basic'),
 ('c', '2026-01-03T00:00:00Z', 'premium'),
 ('c', '2026-01-02T00:00:00Z', 'plus');
INSERT INTO facts VALUES
 ('f1', 'c', '2026-01-01T00:00:00Z', 100),
 ('f2', 'c', '2026-01-02T00:00:00Z', 200),
 ('f3', 'c', '2026-01-03T00:00:00Z', 300),
 ('f4', 'missing-customer', '2026-01-02T00:00:00Z', 400);
""")

history = """
WITH history AS (
 SELECT customer, tier, valid_from,
        LEAD(valid_from) OVER (PARTITION BY customer ORDER BY valid_from) AS valid_to
 FROM changes
)
"""
select = """
SELECT f.id, COALESCE(h.tier, 'UNKNOWN'), f.amount_minor
FROM facts AS f LEFT JOIN history AS h
 ON f.customer = h.customer AND %s
ORDER BY f.id, h.valid_from
"""
bad = db.execute(history + select % """
 f.at BETWEEN h.valid_from AND COALESCE(h.valid_to, '9999-12-31T23:59:59Z')
""").fetchall()
good = db.execute(history + select % """
 f.at >= h.valid_from AND (h.valid_to IS NULL OR f.at < h.valid_to)
""").fetchall()
assert good == [("f1", "basic", 100), ("f2", "plus", 200),
                ("f3", "premium", 300), ("f4", "UNKNOWN", 400)]
assert len(bad) == 6 and sum(row[2] for row in bad) == 1500
assert len(good) == 4 and sum(row[2] for row in good) == 1000
assert len({row[0] for row in good}) == 4
print("inclusive boundaries:", len(bad), "rows; total", sum(row[2] for row in bad))
print("half-open intervals:", len(good), "rows; total", sum(row[2] for row in good))
print("historical result:", good)

The local run produced:

inclusive boundaries: 6 rows; total 1500
half-open intervals: 4 rows; total 1000
historical result: [('f1', 'basic', 100), ('f2', 'plus', 200), ('f3', 'premium', 300), ('f4', 'UNKNOWN', 400)]

The inclusive join duplicates f2 and f3. Their amounts add another 200+300 to the output. A report over the joined rows reaches 1,500 even though the input contains only 1,000.

The corrected join keeps all four fact IDs exactly once and assigns the expected tier to each known customer fact.

Sort the history by effective time

The history CTE uses LEAD over valid_from within each customer. The next effective timestamp becomes the current interval’s end. SQLite documents this behavior in its window-function reference.

That is why the late January 2 change splits the earlier basic-to-premium history correctly in this fixture. Inserting history rows in arrival order and treating that order as effective time would assign the wrong tier even after fixing the boundary predicate.

This is a small batch reconstruction with one non-conflicting change per customer and effective instant. It does not implement an incremental dimension loader, conflict detection, or duplicate normalization. Those need explicit rules before LEAD is applied.

Missing dimensions must remain visible

The fourth fact references a customer missing from the dimension. The LEFT JOIN preserves it, and the output labels the tier UNKNOWN.

An INNER JOIN would drop its 400 minor units from the report. A right-side filter placed in WHERE can also eliminate unmatched facts after a LEFT JOIN, depending on the predicate. Keep the intended population explicit when reviewing the query.

There are two separate checks here:

• Every input fact remains represented exactly once.
• Every represented fact receives the correct historical classification.

A row-count assertion alone cannot establish the second. A total alone cannot establish either if errors cancel each other out. The script checks the full expected result as well as count, distinct IDs, and sum.

State the time model

Every timestamp in this script is a fixed-width UTC string ending in Z. Text ordering works for that fixture. Mixed offsets or inconsistent formats must be normalized to comparable instants first.

This query answers “which tier was valid when the fact happened, using the history we now have?” It does not answer “which tier did the system know about at that time?” Reconstructing historical knowledge requires an additional time/version model. A late effective change can alter the first answer without having been available to the original decision.

Extend the exercise

I maintain a free fintech data engineering roadmap. C02: late dimensions and a historical SQL join adds UTC normalization, exact-repeat handling, same-instant conflicts, and totals grouped by tier and currency.

The exercise asks for a temporal join, its query plan, and evidence that missing dimensions do not lose money. This article isolates the join-boundary failure; it is not a complete C02 submission.

A useful next test is a fact before the first known dimension version. Decide whether it should remain UNKNOWN, then verify that a convenience filter has not silently removed it.