How should effective_from and effective_to be set in a Type 2 dimension so every instant maps to one row?

## What the columns have to guarantee A Type 2 dimension replaces "one row per entity" with "one row per entity per period". For that to be usable, the periods belonging to a single business key must **tile time**: every instant from the first version's start to the far future is covered by exactly one row. Two properties follow, and both are testable: - **No overlaps** — two versions of the same entity must never both cover an instant, or a point-in-time lookup returns two rows and the fact fans out. - **No gaps** — no instant between the first and last version may be uncovered, or a lookup returns nothing and the fact silently loses its dimension. Everything below is machinery for keeping those two properties true. ## Half-open beats closed There are two conventions for expressing a period with two columns. **Closed** intervals: the row is valid from `effective_from` *through* `effective_to` inclusive, so end-dating sets `effective_to` to the instant before the successor starts — `2024-08-01` if the next version begins on `2024-08-02`. This reads naturally and matches `BETWEEN`, but it forces you to know the granularity in order to compute "the instant before". At day grain that is minus one day; at timestamp grain it is minus one microsecond, or whatever your engine's resolution happens to be, and if you guess wrong you leave a sliver of time uncovered or overlapped. Change the column from `DATE` to `TIMESTAMP` later and every end-date is subtly wrong. **Half-open** intervals: the row is valid from `effective_from` inclusive up to `effective_to` exclusive, so end-dating simply copies the successor's start date. Nothing has to be decremented, so nothing depends on granularity. The predicate is: ```sql WHERE event_ts >= d.effective_from AND event_ts < d.effective_to ``` This is the convention to default to. It is also why `BETWEEN` is a mild code smell in this context: `BETWEEN` is inclusive on both ends, so pairing it with half-open windows double-matches exactly on boundary instants — the rarest and therefore most expensive kind of bug to find. ## The open row: sentinel or NULL The current version has no known end. Two options: - **NULL** is semantically honest — the end is genuinely unknown — but it poisons the range predicate. Every query needs `AND (effective_to IS NULL OR event_ts < effective_to)`, and the day one analyst forgets, the current version drops out of their report and recent facts lose their dimension. - **A far-future sentinel** such as `9999-12-31` (or `9999-12-31 23:59:59` at timestamp grain) keeps a single uniform predicate everywhere and lets the column be `NOT NULL`. The cost is a magic value that must be used consistently and that looks odd to a newcomer. Most warehouse teams take the sentinel for exactly the reason above: correctness that depends on every query author remembering a NULL clause is not correctness. If you prefer NULL, hide the predicate behind a view so no one hand-writes it. ## Which timestamp starts the window The first version of an entity is usually effective from when the entity first appeared, and each subsequent version from when the change was observed. Two practical rules matter more than the philosophy: 1. **Be consistent about the clock.** Store windows in one timezone — UTC is the usual choice — and make sure the fact timestamps you compare against are in the same one. A window boundary that is off by the local offset misroutes every event near midnight. 2. **Make the first version reach back far enough.** If the earliest fact predates the earliest dimension version, that fact matches nothing. Many teams back-date the first version to a low sentinel (`1900-01-01`) so early facts still resolve. ## Testing the invariants These are cheap assertions and they belong in the load pipeline, not in an analyst's bug report: ```sql -- 1. no overlaps and no gaps: each row's end must equal the next row's start SELECT customer_id, effective_to FROM ( SELECT customer_id, effective_to, LEAD(effective_from) OVER (PARTITION BY customer_id ORDER BY effective_from) AS next_from FROM dim_customer ) t WHERE next_from IS NOT NULL AND next_from <> effective_to; -- 2. exactly one open row per business key SELECT customer_id FROM dim_customer WHERE effective_to = DATE '9999-12-31' GROUP BY customer_id HAVING COUNT(*) > 1; ``` Add a third: the surrogate key is unique. Together these three catch the overwhelming majority of broken Type 2 loads. ## Grain of the window vs grain of the fact One mismatch bites repeatedly: day-grain windows joined against timestamp-grain facts, or the reverse. If windows are `DATE` and the fact carries a timestamp, the comparison usually still works because the date is coerced to midnight — but two changes on the same day collapse into a zero-length window that matches nothing. If windows are timestamps and the fact carries only a date, every event is treated as midnight and a change made at 14:00 appears to have taken effect at the start of the day. Decide the grain deliberately and state it in the model's documentation. ## What good looks like One `NOT NULL` start, one `NOT NULL` end, half-open semantics, a sentinel for the open row, a single documented timezone, and three tests running on every load. That is the whole discipline, and it is what makes point-in-time joins trustworthy.