Label Construction
Labels are built, not found. A versioned, tested query over raw events, parameterised by the snapshot moment and the horizon, is the difference between a label and a column that happened to be there.
The problem, the obvious approach, and why it breaks
Every lesson starts where the work starts: someone has a problem, and the first model that comes to mind looks fine offline.
How do you turn a target definition into a label table that is correct for every (entity, snapshot) pair, reproducible later, and provably free of information from after the snapshot?
The data scientist: "I rebuilt the training set from the same query and the metrics changed. Then I found that half the positive labels for last quarter had disappeared. What happened?"
Write one query that joins subscribers to cancelled_at, compare it to the snapshot date, and save the result as labels.csv. Rerun it when you need more data.
The subscriptions table is mutable. A subscriber who cancelled and then reactivated has cancelled_at cleared; the label computed three months ago was positive and the rebuilt one is negative. The query is deterministic; its input is not.
- The subscriptions table is mutable. A subscriber who cancelled and then reactivated has
cancelled_atcleared; the label computed three months ago was positive and the rebuilt one is negative. The query is deterministic; its input is not. - The query has no horizon, so a subscriber's label for the January snapshot depends on whether they cancelled by the day the query ran — in the first run, a two-month window; in the rebuild, a five-month window. Same query, different targets.
- Labels for the most recent snapshot were computed before the horizon had elapsed. Those subscribers had not had thirty days to cancel; they are labelled negative because the future had not happened yet, and the model learned that recent cohorts are loyal.
- The CSV is the only record. Nobody can say which rule, which table state or which run produced the labels the deployed model was trained on.
What is being predicted, and from what data
This domain leads with these two. A target nobody defined precisely is a label nobody can trust, and a dataset nobody can describe is a model nobody can debug.
- For each subscriber active on a monthly snapshot date, whether paid access ended — by cancellation click, lapse at term end, or downgrade — within thirty days of that date, as agreed in Target Definition.
- The label table is one row per (subscriber, snapshot date), with the label, the label rule version, and the date the label became observable.
- A subscriptions table that is updated in place:
cancelled_at,plan, andterm_endreflect the current state, not the state at any past date. A separate events table records every plan change and cancellation with a timestamp. - The first query was written against the subscriptions table and run once; the rebuild ran against the same table three months later, when many rows had changed.
How it actually works
Precisely enough to predict its behaviour — not a framework API.
- A correct label is a function of immutable events, the snapshot timestamp and the horizon: label(u, t) = ∃ event e for u with type in the target's set and t < e.time ≤ t + H. Because the inputs are events with timestamps rather than current state, the function returns the same answer whenever it is run — provided events are never deleted or rewritten.
- The label is observable only once t + H has passed. Before that it is undefined, not negative. A label table must therefore carry the observable-from date, and a training set built at time now may only include rows with t + H ≤ now — which is why the dataset is always at least one horizon behind the present (Ground-Truth Delay).
- Every label is a small program. It has a version, it has edge cases — reactivation inside the horizon, a downgrade followed by an upgrade, a pause — and it has tests, because a wrong branch in the rule is a systematic bias in every model trained on it.
The label as a query over events
The query below is the label rule for the agreed target. Read the three things it does that the naive query did not: it reads the append-only events table rather than the current state; it takes the snapshot date and horizon as parameters and compares event time to both; and it refuses to produce a label for any snapshot whose horizon has not elapsed.
The label_rule_version column is not decoration. It is the join key between a training set and the rule that produced it, and it is what lets a model say which definition of churn it learned.
1-- One row per (subscriber, snapshot). Only snapshots whose horizon has elapsed.2WITH snapshots AS (3 SELECT s.subscriber_id, d.snapshot_date4 FROM monthly_snapshot_dates d5 JOIN subscription_events s6 ON s.event_time <= d.snapshot_date -- state as of the snapshot7 WHERE d.snapshot_date + INTERVAL '30 days' + INTERVAL '7 days' <= CURRENT_DATE8 GROUP BY s.subscriber_id, d.snapshot_date9 HAVING BOOL_OR(s.event_type = 'activated')10 AND NOT BOOL_OR(s.event_type IN ('cancelled','lapsed') AND s.event_time <= d.snapshot_date11 AND NOT EXISTS (SELECT 1 FROM subscription_events r12 WHERE r.subscriber_id = s.subscriber_id13 AND r.event_type = 'reactivated'14 AND r.event_time > s.event_time15 AND r.event_time <= d.snapshot_date))16),17ending AS (18 SELECT e.subscriber_id, e.event_time19 FROM subscription_events e20 WHERE e.event_type IN ('cancelled', 'lapsed', 'downgraded')21)22SELECT sn.subscriber_id,23 sn.snapshot_date,24 EXISTS (SELECT 1 FROM ending en25 WHERE en.subscriber_id = sn.subscriber_id26 AND en.event_time > sn.snapshot_date27 AND en.event_time <= sn.snapshot_date + INTERVAL '30 days') AS churned,28 'v3' AS label_rule_version,29 sn.snapshot_date + INTERVAL '37 days' AS observable_from30FROM snapshots sn;The seven-day grace on top of the horizon is for late-arriving lapse events. The population rule — activated, and not cancelled or lapsed without a later reactivation as of the snapshot — is the part that most needs tests, because it is the part most likely to have a branch nobody thought of.
Why the labels changed
The data scientist's two mysteries have one cause each. The metrics changed because the naive query read mutable state, so the rebuild computed labels from a table that had been updated in the meantime. The positives disappeared because subscribers who cancelled and later reactivated had cancelled_at cleared — they were churners in the first run and loyal in the second.
Neither is a bug in the query text. Both are the query's inputs being a different thing on a different day, which is what "labels are built, not found" means in practice.
`SELECT id, cancelled_at IS NOT NULL AS churned FROM subscriptions` — saved as a CSV, rerun when more data is needed.
A query over append-only events with snapshot date and horizon as parameters, refusing unobservable rows, stamping each row with a rule version, stored as a versioned snapshot with tests over synthetic histories.
The first is a different function every day because its input mutates; the second returns the same label for the same (subscriber, snapshot) whenever it runs, and can say which definition of churn it encodes. The evaluation can only be as reproducible as the labels.
The unobserved are not negatives
The subtlest error in the naive pipeline is the newest cohort. Subscribers snapshotted last week have not had thirty days to cancel. The naive query gives them churned = false, and the model learns that the most recent subscribers are the safest — which is exactly backwards for a product whose trial users cancel early.
The fix is a refusal, not a correction: rows whose horizon has not elapsed are not in the label table at all. The consequence is that the training set is always at least a horizon behind, and that gap is a permanent property of the system that monitoring and retraining have to be designed around.
For every (subscriber, snapshot) row used in training, the horizon plus the grace period had elapsed before the label was computed, so a negative means "did not churn" and not "has not yet had time to".
holds when The label query enforces the observability cut-off, the grace period covers the latest late-arriving event type, and the training set is built from the versioned label table rather than a fresh query.
breaks when Someone builds a training set by running the label rule directly with the cut-off relaxed "to get more recent data"; a new event type arrives later than the grace period; a backfill re-timestamps events.
observable_from <= build_time for every row; label rate by snapshot month, which drops sharply for the newest months when unobserved rows leak in.respond Drop the unobservable rows and retrain; if the grace period was too short, extend it and rebuild the affected months' labels under a new rule version.
How to build it
Most important first.
- Build labels from an append-only events table, never from a mutable state table, and parameterise the query by snapshot date and horizon (The Event Log, Keeping Raw History: The Recovery Position and the Liability).
- Include only rows whose horizon has fully elapsed at build time, and record the build time and the label rule version on every row (Dataset Versioning).
- Write the rule as a versioned SQL model with unit tests over synthetic event histories that pin each edge case; a change to the rule is a new version, and artifacts record which version they were trained on (Model Lineage).
- Store the label table as a versioned snapshot, not a regenerated CSV, so the exact rows a model was trained on can be retrieved even after the rule changes (Reproducibility).
What to measure
Which number actually maps to the decision — and which numbers look relevant and are not.
- Label rate per snapshot month, per plan, over time. A step change at a rule version boundary is expected; a step change without one is an events-table problem.
- The share of rows in the training set whose horizon had not elapsed at build time — which must be zero — and the age of the newest usable snapshot, which is the label delay.
- Do not measure label correctness by whether the query runs. Every wrong version of this query ran successfully.
What must stay true after deployment
The field this whole domain exists for. A model is a set of assumptions with weights attached; these are the ones a monitor or a test should be checking.
- The events the label rule reads are still append-only, complete, and timestamped at event time rather than at ingestion time.
- The horizon has fully elapsed, plus a grace period for late-arriving events, for every row in any training set.
- The label rule version recorded on each row is the version that actually produced it, and every deployed artifact can name the version it was trained on.
- Every subscription state the product can produce has a branch in the rule and a test.
- Offline: unit tests of the rule over synthetic histories — cancel, reactivate, downgrade, pause, lapse, late-recorded lapse — at several snapshot dates; a rebuild of last quarter's labels must match the stored snapshot row for row.
- Online: a daily check that the newest snapshot used for training has t + H + grace ≤ now, and a row-level assertion that no label was computed before its horizon elapsed.
- Over time: label rate per version and per month, alerting on a step without a version change; a check that every artifact in the registry names a label rule version that still exists.
What can go wrong
- The events table is append-only and a backfill rewrites it — a migration re-emits cancellation events with corrected timestamps — and every label shifts without the rule version changing (What Backfills Break).
- Late-arriving events: a lapse is recorded by the billing system days after term end, so a label built exactly at t + H misses it and a rebuild a week later finds it (Late-Arriving Data).
- The tests pin the edge cases the author imagined, and the product adds a new subscription state — a gifted subscription with no term — that falls into no branch and is silently negative.
- Event-sourced labels need an events table that Data Engineering must keep append-only, and a grace period that pushes the training data further behind the present.
- Versioned label snapshots are storage and a retention policy; the CSV was free.
- Tests over synthetic histories must be written by someone who knows the subscription lifecycle, which is domain work the model was supposed to save.
- "The query is deterministic, so the labels are reproducible." A deterministic query over a mutable table is a different function every day. Reproducibility is a property of the inputs as much as the code.
- "Recent subscribers who have not cancelled are negatives." They are unobserved. Labelling them negative teaches the model that the newest cohort never churns, which is the cohort it will be asked about first.
- "Label construction is a data engineering task." The rule encodes what the model predicts. Data Engineering owns the events table; the rule, its tests and its version are the model's, because a change to it is a change to the model.
Where this applies
ML advice is stated as universal far more often than it is. These labels say what each claim is specific to — and where CONTESTED appears, the note gives the strongest form of the opposing view.
- GENERALThat labels must be a function of immutable events at a moment over a horizon holds for every supervised target with a delayed outcome; for targets observed instantly — an image's class — the horizon collapses but the versioning and tests remain.
- DATA-SPECIFICThe event-sourced approach assumes an append-only events table exists; where only mutable state tables are available, daily snapshots of them are the fallback, and the label is then only as fine-grained as the snapshot cadence.
Where the depth lives
This domain teaches the model and hands the rest off by name.