Labels, snapshots, and backfills
A label snapshot records what was knowable by a date, not the database’s latest opinion.
Fraud labels arrive late and online features disagree with training data.
This chapter moves from declare maturity to backfill as a new version. We will make the idea concrete in a small code example before placing it back inside a production system with a declared latency, cost, and operator-capacity budget.
Recompute deterministically into a new snapshot ID, compare deltas, and never silently rewrite a model’s training evidence.
Verify the behavior illustrated in materialize_labels.sql, then measure decision utility, slice reliability, and system latency.
Joining only on customer ID attaches the present balance to every historical row. An unsafe release must degrade to a named baseline or the last known-good version.
Build the idea one layer at a time.
Labels arrive after predictions, sometimes late and sometimes corrected. Training remains auditable only when label maturity and snapshot identity are explicit.
A snapshot is a bounded statement of what outcomes were available by a fixed timestamp. It should not mutate when a correction arrives next week; instead, a later snapshot or versioned backfill records the new knowledge.
Backfills must be idempotent and derived from immutable inputs. Deduplicate outcome revisions deterministically, exclude immature examples, and key outputs by snapshot plus entity so a retry cannot create extra rows.
- watermarks
- label maturity
Follow the reasoning before touching the code.
- 01
Declare maturity
Set outcome horizon and reporting delay, then exclude decisions whose labels could still change routinely.
- 02
Freeze the evidence boundary
Filter every outcome by available_at no later than the snapshot cutoff and record source digests.
- 03
Backfill as a new version
Recompute deterministically into a new snapshot ID, compare deltas, and never silently rewrite a model’s training evidence.
WITH ranked AS (
SELECT
order_id,
promised_at,
delivered_at,
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY available_at DESC, revision DESC
) AS position
FROM delivery_outcomes
WHERE available_at <= TIMESTAMP '2026-08-29 00:00:00'
)
INSERT INTO label_snapshots (snapshot_id, order_id, was_late)
SELECT 'labels-2026-08-29', order_id, delivered_at > promised_at
FROM ranked
WHERE position = 1
AND promised_at <= TIMESTAMP '2026-08-22 00:00:00'
ON CONFLICT (snapshot_id, order_id) DO NOTHING;Create an idempotent, availability-bounded label snapshot.
What to notice
- The seven-day boundary removes recent promises whose final delivery outcome may not yet be mature.
- The uniqueness key makes an identical retry a no-op while preserving the original snapshot bytes.
YOUR TURN Pause and predict before revealing the answer.
A corrected delivered_at arrives on August 30 for an order in this snapshot. Where should it appear?
Need a hint?
The correction crossed the frozen availability boundary.
Check your reasoning
Keep the August 29 snapshot unchanged and include the correction in a new snapshot/backfill version with an explicit delta report.
Lessons 1, 2, 3, 4 in this course.
Connect the chapter to the course’s quantitative spine.
A label snapshot records what was knowable by a date, not the database’s latest opinion. The feature availability invariant below is the shared course-level lens that helps quantify the surrounding system. Name each symbol, check its units or shape, and use only values that were available at the production cutoff.
Feature availability invariant
| Symbol | Meaning / shape / unit |
|---|---|
t_available | time the value became knowable |
t_decision | prediction cutoff |
f_i | feature i |
Open derivation and numerical substitution
Start from the production quantity being optimized, substitute the observed values with their declared units, then isolate the model-controlled term. Preserve shape annotations at each step so broadcasting or aggregation cannot silently change the result.
- Write the named inputs: t_available, t_decision, f_i.
- Substitute one small, hand-checkable batch before vectorizing.
- Calculate an independent reference value and compare within a declared tolerance.
# equation → code contract
inputs = validate_shapes_and_units(batch)
value = compute_c03(inputs)
assert is_finite(value)Change one assumption and make the tradeoff visible.
Use the worked chapter as your prediction, then change one input in the lab and explain why the output moves. The lab runs predefined TypeScript and never executes learner code, so the focus stays on the concept rather than setup.
Point-in-time freshness lab
Delay feature arrival and see which historical rows remain eligible.
Assumption: Five events have fixed prediction cutoffs separated by six hours.
Open nonvisual data table
| Item | Computed state | Interpretation |
|---|---|---|
| row 1 | cutoff +0h | not yet knowable |
| row 2 | cutoff +6h | eligible |
| row 3 | cutoff +12h | eligible |
| row 4 | cutoff +18h | eligible |
| row 5 | cutoff +24h | eligible |
Trace the complete operating path.
Carry the chapter’s central idea into the system: A label snapshot records what was knowable by a date, not the database’s latest opinion. The path below shows where that reasoning needs an artifact, a test, an owner, or a fallback.
- 01
Turn “Declare maturity” into a written invariant and a failing test.
- 02
Promote materialize_labels.sql from illustration to a versioned implementation with representative fixtures.
- 03
Use “Backfill as a new version” as the release checkpoint: Recompute deterministically into a new snapshot ID, compare deltas, and never silently rewrite a model’s training evidence.
- 04
Serve or materialize behind explicit a declared latency, cost, and operator-capacity budget.
- 05
Join telemetry to mature outcomes and retain a rollback path.
Observability
Join service health, input quality, prediction distributions, slice behavior, and mature outcomes by exact version.
Cost
Measure storage, preprocessing, compute, queueing, and human review under a representative arrival pattern.
Failure modes
Joining only on customer ID attaches the present balance to every historical row. Add a detector, owner, mitigation, and stop condition for this class of failure.
Alternatives
Compare a rule, a simpler statistical baseline, and a different system boundary before adding model complexity.
Explain the contract, not just the vocabulary.
Point-in-time feature system
Design a versioned training set and online lookup from transaction, account, and outcome events.
- Contracts and lineage
- Temporal join specification
- Late-event/backfill policy
- Offline-online parity report
Read primary material with a purpose.
Return to the opening failure.
Version contracts, temporal joins, transform state, snapshots, backfills, and continuous offline/online parity checks.
For this lesson, the release evidence is the worked code example, a hand-checked formal result, deterministic simulation output, a ≥80% checkpoint, the production rubric, and a named fallback. The course resolves when the system can produce data contracts, point-in-time features, backfills, quality gates, and lineage.