Make every label and feature earn its place

A table is a history of measurements

A CSV looks simultaneous: every cell is visible when you open it. Operational data are not simultaneous. A job is created, its queue is measured, its promise may be amended, it completes, and its outcome may be confirmed later. A predictive table must reconstruct what was knowable at a specific moment. This is the practical continuation of the SQL course's cutoff and feature-window lesson.

Data provenance means being able to explain where a value came from and how it was produced. For each predictor, record its source, unit, valid range, availability timestamp and update behavior. queued_jobs should describe the queue at this job's intake, not today's queue or the largest queue encountered later. priority should be the intake value, not a final escalation status. A familiar column name is not evidence that the value existed in time.

Our approved inputs are distance_km, queued_jobs, item_count, priority, zone, depot_id, weekday and the intake value of promised_hours. job_id and timestamps remain useful for auditing and joining; they are not automatically predictors. label_available_at, final_delay_reason and close_cost are explicitly forbidden predictors because they become available after the outcome. In this synthetic dataset, label_available_at also records completion; a separate completion_timestamp in a real export would be forbidden for the same reason. The raw dataset retains traps so you can demonstrate exclusion rather than merely trust a conveniently cleaned table.

Define the target precisely

Duration is elapsed time from intake to completion, expressed in hours. If timestamps carry time zones, normalize deliberately before subtraction. A negative elapsed time is an integrity problem, not a difficult prediction. The label late equals 1 when duration exceeds the intake promise and 0 otherwise. Equality is on time. A missing duration means the outcome is not yet known; it does not mean the job was on time.

Two times matter for a label. The event time says when the outcome happened. The label-availability timestamp says when the system could actually retrieve a trustworthy label. A completion can occur at 16:00 but only be confirmed at 18:00. A model fitted at 17:00 cannot use that label. A realistic training snapshot therefore checks label availability against its fit cutoff. A gap in calendar days is only a safeguard; the timestamp check is the evidence.

Unfinished jobs create a further problem. An export containing only completed jobs can preferentially omit long jobs near its endpoint. That selection makes the observed sample look faster. The synthetic lab provides completed outcomes, but a real system must define a maturity window or a method for censored observations. We do not turn unresolved jobs into zeros or claim to teach survival analysis in this course.

Worked miniature: audit the timeline

Consider three hypothetical jobs. A arrives Monday 09:00, finishes 13:00 and has a confirmed label at 14:00. B arrives Monday 10:00, finishes Tuesday 10:00 and is confirmed Tuesday 12:00. C arrives Monday 11:00 and remains unfinished at the extraction cutoff. A model will be fitted Monday 18:00.

A's duration of 4 hours is available for that fit. B's eventual 24-hour duration is not, even if a later export contains it. C has no observed duration at the cutoff. Including B uses information from the future; labeling C as zero fabricates an outcome. If a prediction is made for B at Monday 10:00, its completion time is also forbidden as a feature. The same field can be appropriate for constructing a historical label while being inappropriate for constructing historical inputs.

Now suppose A's intake promise is 4 hours and B's is 20. A is on time because 4 is not greater than 4. B is late once its label becomes available. If someone changes A's promise to 3 hours after completion, constructing the target from that amended promise would change the question. Preserve the version used by the contract.

Leakage has several routes

Target leakage occurs when a predictor directly or indirectly contains the answer. A final delay reason is nearly a label in another form. Temporal leakage occurs when a value was unavailable at the prediction moment. Train–evaluation leakage occurs when held-out observations influence preprocessing, feature selection, model fitting or repeated decisions. A perfectly timed feature can still participate in the last kind if its imputer was fitted on the entire table.

Duplicates can also leak. If the same job appears in two exports and one copy reaches training while the other reaches evaluation, the score partly measures memory. Grouping by job_id, checking uniqueness, and reconciling row counts matter before fitting. Do not remove duplicates by arbitrary first occurrence when records contain conflicting updates: establish which version was available at intake and preserve an issue log.

SQL join discipline carries over to pandas. Joining one intake row to three status rows creates three apparent jobs unless you select the valid status snapshot first. Use a declared key relationship and inspect unmatched rows. pandas provides merge validation and an indicator column for these audits; its 2.2 merge reference also warns that null keys can match each other, unlike usual SQL behavior. Do not casually equate pandas and SQL joins.

Missingness and measurement are part of the model

A missing distance could mean an intake failure, an unmeasured route or a newly introduced zone. Imputation replaces a numerical gap for computation; it does not resolve which explanation is true. Record missing rates by time and source. A feature that is usually missing for express work can accidentally act as a priority proxy, and its meaning may change when a logging system is repaired.

Run python examples/02_provenance.py. Inspect the allowed-feature check, target boundary checks, row identities and timestamp assertions. State what each check establishes and what it cannot establish. A script can verify that a timestamp precedes another timestamp; it cannot establish that a source system's clock or business definition was truthful without outside evidence. Preserve the raw file unchanged and make accepted data a derived artifact.

Independent practice

1. 02.1 · Write an availability ledger. For queued_jobs, label_available_at, priority, close_cost and job_id, specify source role, earliest legitimate availability and whether it belongs in X. Give a concrete leakage route for one forbidden field.
2. 02.2 · Audit a fit cutoff. A fit happens Wednesday 09:00. Job P completes Tuesday 18:00 and is confirmed Wednesday 10:00. Q completes Tuesday 20:00 and is confirmed Tuesday 21:00. R is still open. Decide which labels may train the model and explain each decision without inventing missing labels.
3. 02.3 · Repair a multiplied table. An intake table has 100 unique jobs. Joining to status history yields 260 rows. Describe an audit and reconstruction plan that preserves the intake grain and history, including a check for unmatched jobs and conflicting records.

Exit checks

• 02.A: Why is a post-outcome field allowed in target construction but forbidden in intake predictors?
• 02.B: Why does an embargo not remove the need to check label-availability timestamps?