Vehicle data normalization: turning seven auction feeds into one schema

Seven auction platforms describing the same car will produce seven different strings, three different fuel vocabularies, two currencies, and at least one field where the same name means something different from everywhere else. Getting them into one schema is the work. It is also the work that gets underestimated by roughly an order of magnitude, because every individual mapping looks trivial and there are several thousand of them.

This is a practical account of the problems, in the order you will hit them.

Start with the fields that look easy

Fuel type is the canonical example of a field everyone assumes is a three-value enum. In practice, across European wholesale sources, you will encounter all of the following as distinct raw values meaning overlapping things:

Petrol · Gasoline · Benzin · Essence · Benzina · Gasolina
Diesel · Gasoil · Gazole · Nafta
Hybrid · Hybrid (Petrol) · HEV · Full Hybrid · Mild Hybrid · MHEV
Plug-in Hybrid · PHEV · Plug-in Hybrid (Diesel) · Hybrid Rechargeable
Electric · BEV · Elektro · Électrique · Elettrica
LPG · GPL · Autogas · Petrol/LPG · Bi-fuel
CNG · Erdgas · Methane · Petrol/CNG

The mapping itself is straightforward. The decisions inside it are not, and they are product decisions rather than engineering ones:

• Does a mild hybrid belong in Hybrid or in Petrol? A 48V MHEV drives like a petrol car and is taxed like one in most markets, but a buyer filtering for "Hybrid" may well expect to see it.
• Does plug-in hybrid collapse into Hybrid, or is it its own group? Buyers who want a PHEV specifically do not want an HEV, and the price difference is substantial.
• Is bi-fuel petrol/LPG filed under Petrol or LPG? It is genuinely both.

Make, model and the trim problem

Make is nearly tractable — a few hundred values, mostly with obvious aliases (VW / Volkswagen, Mercedes / Mercedes-Benz / MB, Alfa / Alfa Romeo). Even here there are traps: DS was a Citroën trim before it became a marque, and listings from before the split still file DS cars under Citroën.

Model is where it becomes genuinely hard, because sources disagree about where the model ends and the trim begins.

The Kia Niro row is the instructive one. Niro is a single nameplate covering a hybrid, a plug-in hybrid and a full electric car with completely different powertrains, weights and prices. Normalize to the nameplate alone and you produce a "model" whose price distribution is trimodal and whose average is meaningless.

The workable structure

Three fields rather than two, populated with decreasing confidence:

1. 1clean_make — matched against a curated marque list with aliases. High confidence, near-total coverage.
2. 2clean_model — matched against a per-make model catalog. Good confidence; needs manual curation for new nameplates.
3. 3Raw variant string — preserved verbatim. Do not attempt to parse trim into structured fields unless a customer is paying for it; the combinatorics are brutal and the payoff is small.

Prices are not numbers

A price field is the most dangerous field in the payload, because it is a number and numbers look trustworthy. Before comparing two prices from different sources, you need to know six things about each:

• Currency, and for non-euro sources, the FX rate and when it was applied. A Korean won price converted at ingest is not the same figure as one converted at read time.
• VAT treatment — inclusive, exclusive, or margin-scheme. This is a 20%+ swing and sources genuinely differ.
• Fee inclusion — does the displayed figure include the platform's buyer fee, which is often a non-linear function of the hammer price?
• Price type — current bid, buy-now, reserve, or estimate. These are not interchangeable and a single price column that mixes them is unusable.
• Reserve state — a current bid below an unmet reserve is not a price at which the car is available.
• Observation time — on a live lot, a price without a timestamp is not a fact.

The resolution is a comparable field computed on one consistent basis, sitting next to the untouched source figure:

{
  "current_price": 9800,          // source figure, source currency, source basis
  "currency": "EUR",
  "public_price_eur": 11466.00,   // one consistent basis, comparable across sources
  "buy_now_price": 12400,
  "buy_now": true
}

Mileage, and the units trap

Kilometres nearly everywhere in Europe, miles for UK and Irish stock, and occasionally an unlabelled integer whose unit you infer from the source. Two additional wrinkles worth handling explicitly:

• Some sources publish mileage in thousands. A 45 in a field that usually holds 45000 is not a 45 km car; it is a units bug waiting to reach a customer.
• Zero and null are different. Zero means the odometer read zero — plausible on a delivery-mileage car, suspicious otherwise. Null means the source did not say. Collapsing them makes every unknown-mileage car look like a new one.

Damage: the field with no shared vocabulary

There is no standard European damage taxonomy. A salvage-focused source may publish structured per-panel damage; a general wholesale source may publish a single free-text sentence; another may publish nothing but a boolean. Attempting to unify them into one rich schema produces a schema that is mostly null.

The pragmatic layering that survives contact with real data:

1. 1A boolean that is reliably populated for every source — damaged or not. Coarse, but it is the filter 90% of users actually want.
2. 2A severity band where the source supports it — light, structural, total loss.
3. 3The raw damage payload, unmodified, for the sources that publish detail. Anyone doing repair estimation reads this and nothing else.

Resist the urge to synthesise severity from free text with a heuristic. It will be wrong on the edge cases, and the edge cases are exactly the expensive cars. Damaged and salvage vehicle data goes into this in more depth.

Keep the raw payload. Always.

If there is one thing to take from this article: normalization is lossy, your mapping will be wrong somewhere, and you will find out from a customer. Retaining the untouched source payload turns that from an outage into a lookup.

curl -s "https://api.thecarapi.com/api/auction/schadeautos/1775437" \
  -H "X-API-Key: $API_KEY" | jq '.car_identification'

It also makes normalization improvable. When you fix a mapping, you can reprocess history from the raw payloads instead of waiting for the corrected data to accrue going forward.

Validate continuously, not at ingest

Schema validation at ingest catches type errors. It does not catch the failures that matter, which are distributional. Four monitors worth running on a schedule:

• Fill rate per field per source. A field that was 95% populated and drops to 60% overnight is a broken extractor, whatever the logs say.
• Unmapped-value alerts. Any raw fuel, gearbox or body value that fails to map should raise, not silently fall into an Other bucket where it disappears.
• Price distribution shift. A source whose median price moves 30% in a day changed its VAT basis or its currency. It did not suddenly acquire cheaper cars.
• Cross-source sanity. The same make and model should have overlapping price ranges across sources. Non-overlap is a normalization bug, not a market insight.

The order to build in

If you are starting this from scratch, the sequence that avoids the most rework:

1. 1Store raw payloads first, before you normalize anything. Everything else can be recomputed from them; nothing can be recovered without them.
2. 2Normalize make, then fuel, then gearbox. Highest value, lowest ambiguity.
3. 3Build the comparable price field early — it gates every meaningful filter and sort.
4. 4Model matching next, accepting that it needs ongoing curation forever.
5. 5Damage last, and layered rather than unified.
6. 6Add the distributional monitors before you have customers, not after your first bad-data incident.

If this reads like more work than you want to own, that is a reasonable conclusion — it is roughly the argument in the build-versus-buy breakdown, and the data dictionary documents the schema this article describes as it actually ships.