This is the foundation beneath Fleet-Scale Root-Cause Analysis and the posts that follow it. Those posts built the method of diagnosing a population — de-mix the mixture, draw the cohort boundary, fit the hazard, run the surveillance. Every one of them opens with the same silent assumption: that you already hold a clean, joined, trustworthy table, one row per car. You almost never do. That table is the output of a long, dull pipeline nobody writes about, and when the pipeline is wrong the analysis on top is confidently wrong — a plumbing artifact wearing the costume of a finding.

This post is how the table actually gets built from the raw. It takes the same 6,000-car EV fleet throwing the P0AE0 charging code — firmware regression and bad connector lot — and assembles its one-row-per-car table from five messy, disagreeing sources. The theme throughout: garbage in, confident-garbage out.

What it covers

Ten sections, about twenty-one minutes, in plain language with the arithmetic and decisions shown.

Part A · Joining the sources

§ 2 — The entities and the spine. VIN as the join key; the five sources — build records, telemetry, warranty claims, service notes, DTC logs — reduced to one row per car, with the build_vehicle_summary algorithm.

§ 3 — Record-linkage pitfalls. VIN typos and OCR errors, duplicate claims, many-to-one rows, remanufactured parts, and fuzzy matching in the Fellegi–Sunter sense when the key is dirty.

Part B · Making it analyzable

§ 4 — Time alignment. Build, in-service, event, and report dates disagree; align everything to time-in-service so onset curves and survival models are valid.

§ 5 — Missingness & data quality. Why the gaps are not random, how missingness ties to selection and survivorship, validation rules, and provenance per field.

§ 6 — Schema & semantics. Harmonizing units, drifting DTC dictionaries, and supplier-lot encodings into one canonical data model.

Part C · The platform

§ 7 — Architecture. Ingestion → canonical store → per-vehicle summary → analysis; batch vs streaming; lineage and reproducibility.

§ 8 — Worked example. Assembling the 6,000-car table: join by VIN, dedupe claims, align to in-service date, flag the 4% missing connector-temperature, and emit the table the fleet de-mix consumes.

§ 9 — Practical notes. Field rules for the data plumbing.

§ 10 — References & further reading. Databases, warehousing, record linkage, and data quality, with pointers.

Read it

Open the monograph →

The monograph lives at its own URL in the warm-paper layout of the series, with an entity/spine diagram, a record-linkage pitfalls panel, a multi-clock alignment timeline, a cars-by-fields missingness map, a canonical-semantics table, a pipeline architecture diagram, two algorithms, a worked assembly of the 6,000-car table, practical notes, and references.

Where it sits

The diagnosis series, in order:

I · The core method

  1. Root-Cause Analysis Under Uncertain, Heterogeneous Evidence
  2. When Is the Evidence Enough?
  3. Harder Cases in Diagnosis

II · At fleet scale

  1. Fleet-Scale Root-Cause Analysis
  2. Fleet RCA, Made Rigorous

III · Proof, foundations & prevention

  1. Proving Cause with Experiments
  2. Diagnosis on the Vehicle
  3. The Data Plumbing Behind Fleet Diagnosis — (this post)
  4. From Root Cause to No Cause

Full map: The Diagnosis Series index.

← Back to Autonomy