// personal project · write-up

Write it down first: expectation-driven data engineering

Bobby Palmer · November 12, 2026

In one sentence. Expectation-driven data engineering means recording what a metric is expected to do before the data arrives, storing that record as versioned data inside the pipeline, and scoring it automatically so that every verdict can be traced to its evidence.

What I built to show it. A PySpark, Iceberg and Airflow pipeline over 1.07 billion New York taxi and ride-hail trips. It holds 13 written expectations about congestion pricing, 12 of them from an AI agent acting as a domain expert, each labeled, frozen and scored as its data arrives. It reproduces three figures from the MTA's own evaluation report to within 0.2 percentage points.

Two expectations from the project, as the pipeline reports them

HeldRetrospective

The pipeline reproduces the MTA's figure that yellow and ride-hail trips starting or ending in the zone rose about 0.8% from January to September 2024 to the same months of 2025.

Observed +0.79%, expected +0.30% to +1.40%

PendingProspective

In the week of November 8 to 14, 2026, about 4% fewer vehicles will enter the Congestion Relief Zone than in the matching week of 2025.

Expected −7.50% to −1.00%. Recorded November 11, before the data was published.

The range is written first. The dark marker is added when the data lands. Both are shown as of November 12, 2026, and the status page has all 13.

Counting situations

Football's analytics revolution did not start with clever play-calling models. It started with counting. Coaching staffs worked out how many punts, third downs and red-zone trips a team should expect in a game, then spent their limited practice time on the situations they would actually face.

A data program can skip that step. It builds dashboards that show what happened, and everyone decides afterwards whether it was good.

A number can't surprise you if you never said what you expected it to be.

Understanding is expectation

People understood that the sun would rise long before anyone knew the Earth turns. David Hume made the point in 1748: nothing in logic rules out a morning with no sunrise, so our confidence comes from custom, an expectation built from repeated observation. To understand something, in the plainest sense, is to hold an expectation about it that could turn out wrong.

Bertrand Russell added the warning. A chicken fed every morning expects to be fed, until the morning the farmer wrings its neck. Expectations built from history fail when someone changes the system. A toll, a new policy or a large investment is that kind of change. Its effect can't be read from the past, so someone has to state it.

The idea: write it down first

The method rests on one habit. Before an outcome is known, write down what you expect it to be, and make that statement part of the pipeline.

An expectation names a metric, a window of time, what the window is compared with, the expected result as a range, a confidence, the reasoning, who said it, and when. It is stored as versioned data, and once recorded it never changes. A revision is a new entry that names the one it replaces.

An expectation is a record

- id: prospective-crz-entries-2026-11-08-week
  statement: In the week of November 8 to 14, 2026, about 4% fewer
    vehicles will enter the Congestion Relief Zone than in the
    matching week of 2025.
  # recorded before the window's data was published
  origin: prospective
  # an AI agent acting as subject matter expert
  author: ai_sme
  recorded: 2026-11-11
  metric: crz_entries
  comparison: change_vs_period
  window: {start: 2026-11-08, end: 2026-11-14}
  comparison_window: {start: 2025-11-09, end: 2025-11-15}
  expected: {low: -7.5, high: -1.0, unit: percent}
  # compared later with the share of forecasts that held
  confidence: 80
  drivers: [vehicle_class, detection_group, time_period]
  # rationale, sources and approval fields left out here
A real entry from the registry, shortened and rewrapped, with my comments added. It is the pending expectation shown above.

The pipeline then does three jobs:

  1. It measures normal. A baseline for each metric says what an ordinary day looks like.
  2. It scores. When a window's data arrives, each expectation gets a verdict: held, violated low, violated high, pending, not scoreable, or blocked by a data problem.
  3. It locates. When a result misses, the pipeline shows where in the data the gap sits.

The name borrows from test-driven development, where you write down what should happen before you run the code. None of the parts is new. Process control has baselines, science has pre-registration, and data teams have contracts and observability. What I have not seen elsewhere is the combination.

The build

Most of my work can't be published, so I rebuilt the approach on public data. New York's congestion pricing program has a known start date, published projections, and data fine enough to locate a gap.

An expectation is scored only after its data passes the checks

BronzeSilverGoldData checks RegistryScoreDriversStatus page Trip files and entrycounts as received One schema persource and year Daily totals by sliceand baselines Is the data right?Failures block scoring 13 expectations,written first One verdict perexpectation Where each gapcomes from Verdicts, drivers,data health metrics and baselines gates the verdict
Data moves up through three layers. Expectations meet it only at scoring, and only after the checks on that data pass.

The data is the city's taxi and ride-hail trip records from January 2023 to September 2026, plus the MTA's counts of vehicles entering the toll zone. PySpark builds three layers of Iceberg tables: source files as received, one schema per source, then daily totals and baselines. Every job overwrites only one month of one source, so any month can be rerun. Two Airflow DAGs follow the sources' monthly and weekly schedules. Eleven data checks have produced 567 results across 45 months, with one warning and no failures.

The registry is a version-controlled file of 13 expectations, and a CI rule rejects any pull request that edits a recorded entry. Each verdict is stored with the registry commit it was scored against and any failing checks that blocked it. So a verdict on the status page can be traced through the registry version, the checks and the tables to the source files.

Three entries test the pipeline itself by re-deriving figures the MTA published. All three held:

Trips starting or ending in the zone, January to September 2025 against 2024

MTA reportThis pipeline
Yellow taxi and ride-hail+0.82%+0.79%
Yellow taxi+16.87%+16.71%
Ride-hail−4.86%−4.86%

What building it taught

The expectation decides the grain. I expected zero congestion-fee trips before tolling began, and found 401 on January 4. Of those, 397 had been picked up before midnight and dropped off after the toll started. The data was right and my expectation was at the wrong grain, so the trip table now keeps drop-off time.

Ask whether the data is wrong before saying the world changed. Data quality tools use "expectation" for rules such as "this column is never null". Those are claims about the data. Mine are claims about the world, and they wait on the first kind. If a check on an expectation's inputs fails, the verdict is "data issue", never "violated".

Two true numbers can be impossible to compare. The MTA projected a 13.4% drop in vehicles entering the zone, against a modeled scenario with no toll. The state later reported 11% without naming its baseline. The registry keeps the projection with the verdict "not scoreable" and the reason, which is more useful than quoting the two figures side by side.

Locating is not explaining. In tolling's first year, taxi and ride-hail trips touching the zone rose 1.7%, which looks like nothing happened. The driver breakdown shows yellow taxis about 4.6 million trips above an even share of that change, and ride-hail about 3.5 million below. The pipeline shows where a gap sits. Saying why takes domain knowledge.

An AI expert on the record

The engineer is usually the wrong person to set the expected values. They come from stakeholders, and the hard part is getting someone to commit to a number.

For this project I simulated the whole exchange. One Claude session played a transportation analyst. A second, acting as the engineer on my behalf, briefed it on the available metrics and the published data, and challenged each draft on measurability, time windows and arithmetic. I reviewed the resulting entries and approved them by committing the registry. The exchange caught one error, and it was the engineer's: its target for reproducing an MTA figure counted vehicle types the pipeline doesn't load. The expert pointed that out, and the target was corrected from +1.03% to +0.82%.

The pipeline treats an AI author as it should treat any author. Each of the twelve entries is labeled as an AI expert's and links to the unedited interview. Seven are forecasts, recorded at 80% confidence before their data was published. Some of their windows had already begun or ended when they were recorded, but none of that data was out yet. The status page will report how many held against that confidence. The registry also draws a line that matters for AI in data systems. A model that turns a sentence into structured fields is a clerk. A model that supplies the number is an author, and the record should say so.

The project was built with Claude Code, working from a spec I developed with it. I approved each phase plan and merged each pull request, and the implementer was never allowed to write or change an expectation's values. The README has the details.

The dog that didn't bark

In one Sherlock Holmes story the clue is a dog that did nothing in the night. Its silence meant something only because it was expected to bark.

Data timeliness has the same shape. An ingestion that should have run yesterday is found, today, not to have run. A pipeline that fails completely can't report its own failure, because the run that would have written the report row never happened. So the timeliness view has to know that a load was due, and read the absence of a row as a probable failure.

A missing load is visible only against a table of what was due

SourceMonTueWedThuFriSatSun
Orders feeddue daily✓✓✓✓✓✓✓
Inventory snapshotdue daily✓✓✓no row✓✓✓
Supplier filedue Wednesdays··✓····
An illustration with made-up sources, not output from the project. ✓ is a load report row. A dot means nothing was due. Thursday's failed run wrote nothing, so only the due dates reveal it.

That is an expectation, and it belongs in data like the others: each source with its cadence, its due time and a grace period. Three rules follow.

Heartbeat monitors and dbt's source freshness do versions of this, with the expectation held in tool configuration. I'd keep it in a table that anyone reading the timeliness view can also read. It is a pain to build, because the schedule now lives in two places and they drift.

This project gets part of the way. Its freshness check compares what is loaded with what each source has published, which separates late from failed. It has no deadline, though. If nothing ran at all, the status page would keep its last state and nothing would report it. That is the next thing I would build.

Limits

What it asks, and where it carries

The method is mostly discipline. Someone has to commit to a number before the result is known, and the pipeline has to keep the detail needed to locate a miss. In return, every number arrives with a question already attached: is this what we expected? A miss becomes the start of an investigation, not the start of an argument.

None of this is specific to traffic. It applies wherever a change has a start date and someone predicted its effect: a sourcing change projected to cut spend, or a new supplier expected to shorten lead times. The registry, the scoring and the drivers carry over. Only the metrics change.