--- title: "Vehicle data normalization: turning seven auction feeds into one schema" description: "How to normalize make, model, trim, fuel, gearbox and damage across auction platforms that each invented their own taxonomy — and why keeping the raw payload matters more than getting the mapping right." slug: vehicle-data-normalization canonical: https://thecarapi.com/blog/vehicle-data-normalization category: Data engineering author: TheCarApi Engineering published: 2026-03-14 updated: 2026-08-14 tags: [data normalization, schema design, vehicle taxonomy, ETL] --- # Vehicle data normalization: turning seven auction feeds into one schema Normalization is where multi-source vehicle projects succeed or quietly fail. The failure mode is not a crash; it is a filter that returns the wrong cars and nobody can tell. 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: ```text 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. > **Decide once, write it down, expose both** — Whatever you choose, the resolution is the same: keep a canonical `fuel_group` for filtering and retain the original source string alongside it. Users filter on the group; anyone who needs the distinction can read the raw value. Never overwrite the source value in place. ## 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. | Source string | Model | Trim / variant | Note | | --- | --- | --- | --- | | `BMW 320d Touring xDrive M Sport` | 3 Series | 320d Touring xDrive M Sport | Engine code is load-bearing — 320d ≠ 320i | | `VW Golf VII 1.6 TDI Comfortline` | Golf | 1.6 TDI Comfortline | Generation number matters for valuation | | `Mercedes C 220 d T-Modell AMG Line` | C-Class | C 220 d Estate AMG Line | `T-Modell` is German for estate, not a trim | | `Peugeot 3008 1.5 BlueHDi Allure Pack` | 3008 | 1.5 BlueHDi Allure Pack | Numeric model names collide with engine sizes | | `Kia Niro 1.6 GDi HEV` | Niro | 1.6 GDi HEV | Same nameplate exists as HEV, PHEV and BEV | 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. `clean_make` — matched against a curated marque list with aliases. High confidence, near-total coverage. 2. `clean_model` — matched against a per-make model catalog. Good confidence; needs manual curation for new nameplates. 3. **Raw 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: ```json { "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 } ``` _Filter and sort on the comparable field. Display the source figure when the user is looking at the lot on its own. Never sort on a mixed-basis column._ > **The cheapest car in your search is probably a bug** — When a price-ascending search returns something implausible, the cause is almost always basis mixing — a net-of-VAT figure sorted against gross ones, or a stale FX rate on a non-euro source. Check the basis before you check the vehicle. ## 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. A **boolean** that is reliably populated for every source — damaged or not. Coarse, but it is the filter 90% of users actually want. 2. A **severity band** where the source supports it — light, structural, total loss. 3. The **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](/blog/salvage-damaged-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. ```bash curl -s "https://api.thecarapi.com/api/auction/schadeautos/1775437" \ -H "X-API-Key: $API_KEY" | jq '.car_identification' ``` _Every normalized vehicle keeps `car_identification` — the source payload exactly as published, so a disputed field can be traced to what the source actually said._ 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. > **Build the vocabulary endpoints first** — Expose the canonical values as data — brands, models, fuels, gearboxes, countries — rather than baking them into your frontend. It makes the vocabulary auditable, lets clients discover new values automatically, and means a new source adds options instead of breaking filters. See [the catalog endpoints](/docs/catalog). ## The order to build in If you are starting this from scratch, the sequence that avoids the most rework: 1. Store raw payloads first, before you normalize anything. Everything else can be recomputed from them; nothing can be recovered without them. 2. Normalize make, then fuel, then gearbox. Highest value, lowest ambiguity. 3. Build the comparable price field early — it gates every meaningful filter and sort. 4. Model matching next, accepting that it needs ongoing curation forever. 5. Damage last, and layered rather than unified. 6. Add 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](/blog/scraping-car-auction-sites-vs-api), and [the data dictionary](/docs/schema) documents the schema this article describes as it actually ships. ## Frequently asked questions ### What does vehicle data normalization mean? Mapping the varied, source-specific ways different platforms describe a vehicle — make, model, fuel, gearbox, price basis, damage — onto one consistent schema, so that data from multiple sources can be filtered, sorted and compared together. The defining constraint is that it must be lossy without being destructive: the canonical values sit alongside the original source values, never replacing them. ### Should mild hybrids be classified as hybrid or petrol? There is no universally correct answer — it is a product decision. A 48V mild hybrid drives and is taxed like a petrol car, but users filtering for "hybrid" may expect to see it. Whichever you choose, keep the original source string so the distinction is recoverable, and document the decision where your API consumers will read it. ### Why can I not just use VIN to match vehicles across sources? VIN is the ideal key but is sparsely published in wholesale auction listings — many platforms reveal it only to logged-in dealers, and fill rates vary widely by source and country. An architecture that assumes VIN as the join key works on test data and fails in production. Use it when present, and have a normalized-attribute fallback for when it is not. ### How do you compare prices from sources with different VAT treatment? Compute a single comparable field on one consistent basis — a common currency, a defined VAT treatment, and a stated fee inclusion policy — and store it alongside the untouched source figure. Filter and sort on the comparable field only. Sorting a column that mixes net and gross figures is the most common cause of implausible results in price-ascending search. ### How do you detect that normalization has silently broken? Distributional monitoring rather than schema validation. Watch per-field fill rates by source, alert on raw values that fail to map instead of bucketing them into "Other", flag sudden shifts in a source's median price, and check that the same make and model has overlapping price ranges across sources. Type validation passes while all four of these are failing.