Why do pipeline data intervals use half-open bounds that include the start but exclude the end?

## What half-open means An interval written `[start, end)` includes its lower bound and excludes its upper bound. For a daily pipeline the run covering 11 March owns `[2026-03-11 00:00:00, 2026-03-12 00:00:00)`, and the run covering 12 March owns `[2026-03-12 00:00:00, 2026-03-13 00:00:00)`. The instant `2026-03-12 00:00:00.000` belongs to the second interval and only to the second interval. This is not a stylistic preference. It is the only convention under which consecutive intervals **tile** the timeline: every instant belongs to exactly one window, with no gaps and no overlaps. Two closed intervals would share their endpoint (overlap); two open intervals would leave the endpoint homeless (gap). ## The duplication bug The most common way this goes wrong is a source filter written with an inclusive upper bound: ```sql -- WRONG: claims midnight of the next day as well SELECT SUM(amount) FROM payments WHERE created_at BETWEEN '2026-03-11 00:00:00' AND '2026-03-12 00:00:00'; -- RIGHT: half-open, tiles cleanly with the next run SELECT SUM(amount) FROM payments WHERE created_at >= '2026-03-11 00:00:00' AND created_at < '2026-03-12 00:00:00'; ``` `BETWEEN` in SQL is inclusive on both sides. Any row stamped exactly at midnight is summed into 11 March by one run and into 12 March by the next. The failure mode is nasty because it is *small and silent*: on a table with millisecond-resolution timestamps only a handful of rows land exactly on the boundary, so the totals are wrong by a rounding-error amount that nobody notices until someone reconciles against the source. Systems that generate timestamps at second granularity, or that batch-insert with a truncated `created_date`, make it much larger. ## Why subtracting a second is not the fix The reflex repair is to close the window just short of the boundary: ```sql -- fragile: assumes second precision forever WHERE created_at BETWEEN '2026-03-11 00:00:00' AND '2026-03-11 23:59:59'; ``` This silently drops everything in `23:59:59.001` to `23:59:59.999` as soon as the source starts emitting sub-second timestamps — which happens when someone upgrades a connector, changes a column type, or switches from a batch export to a streaming one. Now you have the opposite bug, data loss, and it is even harder to spot because the missing rows are never anywhere. Half-open bounds are precision-independent: `< end` is correct at second, millisecond, microsecond or nanosecond resolution, and correct for date types as well. ## Where the same rule shows up elsewhere **Partition naming.** If the output partition is `dt=2026-03-11`, the rows in it must be exactly the rows matched by the half-open filter. Any mismatch between the filter and the partition label means the partition does not mean what its name says, and every downstream consumer inherits the error. **Overwrite semantics.** Idempotent reprocessing works by deleting and rewriting one partition. That is only safe if the partition and the interval are the same set of rows; overlapping intervals would have one run deleting rows another run wrote. **Reconciliation.** "Sum the daily partitions and compare to the source total for the month" only balances if the daily windows tile. Overlaps inflate the sum, gaps deflate it, and either way the reconciliation cannot tell you which day is at fault. **Event time versus ingestion time.** The half-open rule tells you how to slice a timestamp column, not *which* timestamp column to slice. Filtering ingestion time gives windows that always balance but assign records to the day they arrived; filtering event time gives business-correct assignment but exposes you to late arrivals landing after their window closed. Pick deliberately and write it down, because switching later silently reshuffles history. ## How to answer State the tiling property first, then give the concrete failure: `BETWEEN` double-counts the boundary row, minus-one-second drops sub-second rows, `>= start AND < end` is right at every precision. Finish by connecting it to partition overwrite: intervals that tile are what let a rerun replace exactly one partition without touching its neighbours.