Diagnostics · Data · Foundations
Every fleet method assumes a clean, joined, trustworthy table. Here is how that table is actually built — joining records by VIN, aligning clocks, and surviving missing data.
The fleet posts assumed a tidy table: one row per car, with build attributes, a telemetry summary, and a warranty outcome. That table does not arrive by post — it is assembled from messy raw sources that disagree about identity, time, and meaning. This piece is the unglamorous foundation under the fleet method. We join build records, telemetry, warranty claims, service notes, and DTC logs by VIN into one row per car; survive the record-linkage pitfalls that corrupt the join (typos, duplicate claims, many-to-one rows, remanufactured parts); align events that live on four different clocks to a common time-in-service reference so onset curves and survival models are valid; map and respect missingness instead of imputing over it; harmonize units and drifting DTC dictionaries into one shared data model; and wire it all into an ingestion → canonical store → per-vehicle summary → analysis pipeline with lineage, so any diagnosis can be re-run. The running example is the 6,000-car EV fleet with the P0AE0 charging code — assembled, this time, from the raw.
Every method in the fleet series — de-mixing the mixture, drawing the cohort boundary, fitting the Weibull, running the CUSUM — opens with the same silent assumption: that you already have a clean, joined, trustworthy table, one row per car.
You almost never do. That table is the output of a long, dull pipeline that nobody writes blog posts about, and when the pipeline is wrong the analysis on top of it is wrong in a particularly dangerous way: confidently wrong. A bad join inflates a failure rate; a clock misalignment bends an onset curve that was actually flat; a silently dropped cohort hides the very cluster you are hunting. The math downstream cannot tell the difference between a real signal and a plumbing artifact — it just computes.
This is the post that sits under posts four through six. The fleet method told you what to do with the table. This one tells you how the table gets built from the raw — and how each step can quietly poison everything above it. We keep the same 6,000-car EV fleet throwing the P0AE0 charging code, with its two real causes: a firmware regression and a bad connector lot.
A diagnosis is only as trustworthy as the table it ran on. Every figure in the fleet posts inherits the assumptions made here — so a mistake in the plumbing is not a small mistake. It is a mistake that wears the costume of a finding.
Part A — Joining the Sources
The car’s story is scattered across systems that were never meant to be read together. The first job is to pull them onto a single spine — and to survive the ways that join goes wrong.
A car’s life is recorded in five places that rarely talk to each other. Each holds a piece of the truth; none holds it all. The spine that pulls them together is the one identifier they share: the VIN.
The sources for our fleet, and what each one knows:
P0AE0 lives here, with a timestamp and the conditions around it.The goal is a single table where every car is exactly one row, carrying its build attributes, a summary of its telemetry, its warranty and service outcome, and its DTC history flattened into features. The VIN is what makes the join possible — and, as the next section shows, the VIN is also where the join breaks.
input build · telemetry · warranty · service · dtc # keyed, dirtily, by VIN
vins ← canonical_vin_set(build) # build is the spine: one row per real car
table ← { }
for v in vins:
row ← build[v] # plant, build week, fw, connector lot, trim
tel ← telemetry.where(VIN = v) # many rows → reduce to features
row.cold_charge_cycles ← count(tel.cold_fast_charge)
row.connector_temp_p95 ← pctl(tel.conn_temp, 95) # may be MISSING
row.dtc_P0AE0 ← any(dtc.where(VIN = v).code = "P0AE0")
row.warranty ← dedup(warranty.where(VIN = v)) # collapse duplicate claims
row.in_service_date ← earliest(warranty, dtc, telemetry, sale)
table[v] ← row
return table # one row per car — the contract for §§4–6
# the reduce step is where bias enters: every summary throws information away.
The whole pipeline rests on one assumption: that a VIN means the same car everywhere it appears. In practice the key is dirty, and every kind of dirt has its own way of corrupting the table.
The failure modes, and what each one does to a rate:
O for 0 or I for 1. The warranty claim now fails to join — the car looks like it never failed, and your failure rate drops below the truth.When the key is too dirty to match exactly, you fall back to fuzzy matching: decide whether two records refer to the same car by scoring how well their fields agree. The classic framework, from Fellegi and Sunter, puts this on a footing — in plain words, each agreeing field (same plant, same build week, near-identical VIN) is evidence the records are the same car, and each disagreeing field is evidence against. You add up the weighted evidence and set two thresholds: above the upper one, declare a match; below the lower one, declare a non-match; in between, send it to a human. It is the same weigh-the-evidence logic as the diagnosis posts, applied to identity instead of fault.
Deciding “are these two records the same car?” is the same evidence-weighing problem as “is this the root cause?” — agreeing fields raise the odds, disagreeing fields lower them, and an uncertain middle band goes to a human. Get identity wrong and every downstream rate is built on sand.
Part B — Making It Analyzable
A joined table is not yet an analyzable one. Three more things have to be true: the events must share a clock, the gaps must be understood rather than filled, and the columns must mean what they say.
Onset curves and survival models all answer “how long after what?” If the events live on different clocks, the answer is meaningless no matter how careful the statistics on top.
A single fault event drags four different timestamps behind it, and they rarely agree:
Mix these and you get nonsense. Measure failure age from build date and a car that sat unsold for six months looks like it failed late; measure from report date and a slow data pipeline makes the whole fleet look like it failed on the same day the batch landed. The fix is to pick one common reference — almost always time-in-service — and express every event as an offset from it. Only then are two cars comparable, and only then is the onset curve of the fleet post measuring the thing it claims to measure.
input car row · its events (dtc · claims · charge sessions)
t0 ← row.in_service_date # common reference — NOT build, NOT report
for e in events:
ts ← to_utc(e.timestamp, e.timezone) # normalize zone; correct onboard-clock drift
if ts < row.build_date: flag("impossible time") # validation rule
e.t_in_service ← ts − t0 # offset from in-service, in days
return events sorted by t_in_service # now two cars are comparable
# onset curves & Weibull fits are only valid on this aligned clock.
The most dangerous cells in the table are the empty ones — not because they are missing, but because why they are missing usually correlates with the very thing you are studying.
Missing data comes in kinds, and they are not interchangeable:
The trap is that missingness is almost never neutral. It ties straight to the selection and survivorship problems from the diagnosis series: the cars that stop reporting are disproportionately the ones in trouble, so dropping them quietly biases every rate downward. The discipline is threefold — classify each missing field by its mechanism, attach provenance to every value (which source, which version, how trusted), and run validation rules that flag the impossible (a charge temperature of 900°C, an in-service date before the build date) rather than letting it flow into the model.
A joined, time-aligned, gap-aware table can still be quietly wrong if two columns that share a name don’t share a meaning. Harmonizing semantics is the least visible and most violated step.
The fault lines:
P0AE0 may denote a slightly different condition on a 2024 model year than on a 2026 one after a diagnostic revision. Treat the string as a constant and you merge two different faults — or split one.The remedy is a canonical data model: one definition per concept, written down, with every source mapped into it. A canonical connector_temp_c column with an explicit unit, a versioned DTC dictionary keyed by model year, a supplier-lot crosswalk. The payoff is exactly what the fleet method assumes without saying so — that when it reads a column, the column means one thing across all 6,000 cars.
| Concept | Raw sources disagree on | Canonical definition |
|---|---|---|
| connector temp | °C vs °F; sensor vs inferred | connector_temp_c · °C · measured only |
| distance | km vs miles | odometer_km · kilometers |
| fault code | P0AE0 meaning drifts by MY | dtc + dtc_dict_version keyed by model year |
| connector lot | per-supplier numbering | lot_id via supplier crosswalk |
| in-service | build vs sale vs first-drive | in_service_date · first telematics heartbeat |
Part C — The Platform
The steps so far are logic. A platform makes them repeatable: raw in, canonical store, per-vehicle summary, analysis — with lineage so any finding can be reproduced.
The pipeline is four layers, each with one job, each insulating the next from the mess below it. The shape is conventional on purpose — boring infrastructure is reproducible infrastructure.
The layers:
build_vehicle_summary: reduce the many-row telemetry and DTC history into one row per car. This is the table the fleet method consumes.Two cross-cutting choices matter. Batch vs streaming: the per-vehicle summary can be rebuilt nightly in batch for the retrospective analyses, but the surveillance from the rigor post — the CUSUM that catches a forming cluster at fifty cars — needs a streaming path that updates as telemetry lands. And lineage: every value in the summary carries a pointer back to the raw records and the code version that produced it, so a diagnosis is not a one-time artifact but something a regulator or a future engineer can re-run and get the same answer.
Put it together end to end. Five messy feeds go in; the one-row-per-car table the fleet de-mix consumes comes out. Here is each decision, in order.
Build is the spine — exactly 6,000 cars, one row each. Telemetry and DTC logs are reduced to per-car features before joining (never joined raw, or §03’s many-to-one explosion ruins the counts). Warranty and service attach where present.
23 rows are re-bills or double entries on the same VIN, same part, same week. Collapse them or the P0AE0 failure rate inflates by ~6%. Fuzzy-match the 4 typo-broken VINs back to their cars so their failures are not silently lost.
Re-zero every event — DTC, claim, charge session — to each car’s in-service date, not build or report date. Now the onset curve measures months-in-service, and the firmware and connector clusters separate cleanly in time.
The gap is biased — it lines up with old firmware and sensor-less trims (§05). Do not impute a plausible value; that would invent signal exactly where a cause lives. Flag the field as missing with its mechanism and let the model treat it as its own category.
Build attributes, telemetry summary features, dedup’d warranty outcome, flattened DTC history, missingness flags, provenance. This is the table §3 of the fleet post calls “the data” — now actually built, and defensible.
| VIN | fw | lot | conn_temp_c | P0AE0 | mo-in-svc | outcome |
|---|---|---|---|---|---|---|
| 5YJ…0042 | v5.1 | L-220 | 91 (p95) | yes | 7.2 | warranty repair |
| 5YJ…0317 | v5.1 | L-220 | missing | yes | 6.8 | warranty repair |
| 5YJ…1185 | v4.9 | L-204 | missing | no | 11.4 | healthy |
| 5YJ…2630 | v5.1 | L-204 | 58 (p95) | no | 9.1 | healthy |
That table — VIN-joined, deduped, clock-aligned, missingness-flagged, semantically harmonized — is what every figure in the fleet posts silently assumed it was handed. The difference now is that you could re-run it from the raw and get the same rows.
One row per car comes from the source that is one-per-car by nature. Everything else attaches to that spine; nothing else gets to invent or multiply cars.
Telemetry and DTC logs are many-per-car. Reduce them to features before joining, or a single car’s attributes get counted thousands of times and every rate goes to garbage.
Re-bills and double entries inflate failure rates. Collapse duplicate claims on VIN + part + window before you count anything.
A typo’d VIN is a lost failure, not a missing car. Use Fellegi–Sunter scoring to recover broken matches; send the uncertain middle to a human.
Build, sale, event, and report dates disagree. Pick time-in-service as the origin so onset curves and survival models measure what they claim — and mind timezones.
Gaps line up with causes. Classify each missing field by mechanism and flag it; never impute over a gap that sits exactly where a cause lives.
Celsius and Fahrenheit in one column is a fake second peak; a drifting DTC dictionary merges two faults. Map every source into a versioned canonical model.
Every value should point back to its raw record and the code version that made it. A finding you cannot re-run is an assertion, not a result.
This post leans on databases, data warehousing, record linkage, and data quality. Entry points, grouped by the question they answer.
E. F. Codd, “A Relational Model of Data for Large Shared Data Banks” (1970). The foundation of the join itself — why a shared key like the VIN lets independent tables be combined.
R. Kimball & M. Ross, The Data Warehouse Toolkit (3rd ed., 2013). Dimensional modeling and conformed dimensions — the discipline behind “one row per car” and a shared canonical model.
M. Kleppmann, Designing Data-Intensive Applications (2017). Batch vs streaming, ingestion, and the architecture of pipelines that stay reproducible at scale.
I. Fellegi & A. Sunter, “A Theory for Record Linkage” (1969). The evidence-weighing framework for deciding whether two records are the same entity when the key is dirty.
C. Batini & M. Scannapieco, Data Quality: Concepts, Methodologies and Techniques (2006). A systematic treatment of completeness, accuracy, and the dimensions of quality behind validation rules.
T. Redman, Data Quality: The Field Guide (2001). The practical cost of bad data and how it propagates into confident-but-wrong conclusions.
DAMA International, DAMA-DMBOK: Data Management Body of Knowledge. Provenance, lineage, master data, and the governance vocabulary for a trustworthy canonical store.
These are orientation points into large literatures, cited to show the lineage of the method rather than as the source of any single claim here. Titles and years are given so you can find them.