High-Performance Vehicle Analytics

Process millions of vehicle listings — pricing, mileage, depreciation, lifecycle — on a single machine, with no always-on database cluster and £0 cloud-compute cost.

This is a small, runnable blueprint for the kind of data platform that powers a vehicle marketplace, built the lean way: DuckDB and Polars over partitioned Parquet, a query-time delta overlay instead of nightly full rebuilds, and guardrails so the pipeline can't silently rot. Everything here runs on 100% synthetic data (no DVLA or CarHunch data — see About the data) and a laptop.

It's the open, reproducible version of an architecture I run in production on CarHunch — see the case study below. The marketplace-style features shown here (pricing, depreciation, volatility) are a generic illustration; they are not CarHunch features — CarHunch does MOT history and risk insights, not valuations.

---

The problem

A lot of data platforms still reach for an always-on warehouse or a multi-node cluster (Oracle/Exadata, a standing Spark/EMR cluster, a big Snowflake/BigQuery footprint) for workloads that comfortably fit on one modern machine. That choice quietly costs a fortune — in licensing, in always-on compute you pay for 24/7 but use in bursts, and in the human overhead of keeping a distributed system fed and healthy.

Hardware and single-node engines have moved on. A 2-core container with DuckDB or Polars will chew through millions of rows of vehicle data in milliseconds. For a very large class of analytical workloads, the cluster is no longer necessary — and this repo is meant to let you prove that to yourself with make bench.

The architecture

No server process. No cluster. Python opens DuckDB in-process, reads Parquet files, and returns. That's the whole runtime.

Text version of the diagram

   raw daily scrapings (CSV/JSON)
              │
              ▼
   ┌─────────────────────┐      validate + filter, write columnar
   │  ingest (DuckDB /    │ ───► data/parquet/listings/   (base, partitioned by make)
   │  Polars)            │ ───► data/parquet/delta/      (today's changes)
   └─────────────────────┘
              │
              ▼
   ┌─────────────────────┐      base + delta merged AT QUERY TIME (no nightly rebuild)
   │  query-time overlay  │ ───► "current state" view
   └─────────────────────┘
              │
     ┌────────┴─────────┬──────────────────┐
     ▼                  ▼                  ▼
  analytics        lifecycle           guardrails
  (velocity,       state machine       (sanity + canary
   depreciation,   (Active→PriceDrop    after every load)
   volatility)      →Delisted→…)

Benchmarks

One run on an ordinary multi-core machine, 5,000,000 synthetic listings, DuckDB 1.5.3. Run make bench for your own numbers — they'll vary by hardware, but the shape holds.

Step	Result
Ingest 5M rows — raw CSV → partitioned ZSTD Parquet	~0.5 s
On-disk — 334 MB CSV → Parquet	43 MB · ~7.8× smaller
Point lookup — 100 listings across 5M rows	~8 ms
Current state — query-time overlay (read 100)	~21 ms
Current state — full rebuild ("nightly merge")	~227 ms
Analytics — group-by over all 5M rows	~18 ms
Peak memory · cloud cost	~1.7 GB · £0

The headline is the overlay — reading current state by merging base + delta on the fly is ~10× cheaper than re-materialising the whole dataset, and that gap widens with scale (the overlay only touches what you read; a rebuild rewrites everything). It's what turned CarHunch's daily pipeline from 2+ hours into ~70 seconds (below). make bench prints this live:

  overlay (read 100)     ████                                       21 ms
  rebuild full snapshot  ████████████████████████████████████████  227 ms   ← 10× slower

An honest note on derived tables. The repo also builds a sorted, narrow "lookup-by-id" derived table (pipeline/derived.py). At 5M rows on a laptop it's no faster than scanning the base — DuckDB's columnar reader is so quick that a full scan is already ~8 ms. That's the point: the optimisation only earns its keep at scale. In CarHunch production it replaced a 56 GB canonical scan with a 14 GB derived table and saved ~9.6 s per request. Knowing when an optimisation matters is as important as knowing how to do it.

Quickstart

Fastest path — install, generate data, run the narrated demo (~30 s on a laptop):

pip install -e .          # DuckDB + numpy  (add ".[polars]" for the Polars path)
make data ingest          # synthetic listings + daily delta -> Parquet
make demo                 # narrated walkthrough (overlay, analytics, lifecycle, guardrails)

No install needed, really: the scripts add the repo root to sys.path themselves, so if pip install -e . is awkward on your machine you can just pip install duckdb numpy (optionally polars) and run the same make targets.

Full tour — benchmarks, derived table, and standalone guardrail check:

pip install -e .
make data                 # generate synthetic listings + a daily delta (seeded, 5M rows)
make ingest               # raw CSV -> partitioned Parquet (+ delta)
make derived              # build the access-optimised derived table (needed for bench)
make bench                # timings + overlay-vs-rebuild bar chart
make demo                 # narrated walkthrough (includes guardrails)
make check                # same guardrails as the end of `make demo`, exit-code only

Or one shot: make all then make demo (runs data → ingest → derived → check → bench).

Requires Python 3.10+. No services, no containers, no cloud account.

Re-running cleanly

You usually don't need to uninstall anything or wipe the repo — just regenerate:

make data ingest          # overwrites CSV + Parquet; pick up code/data generator changes
make derived              # only if you ran `make bench` or care about the derived-table row
make bench demo           # re-run the show

To wipe all generated artefacts and start completely fresh:

make clean                # removes data/raw/ and data/parquet/ only
make data ingest derived bench demo

make clean does not touch your Python install (pip install -e . is one-time unless dependencies change). Re-run pip install -e . only after pulling changes to pyproject.toml.

Scale or re-seed the dataset: make data ROWS=50_000_000 SEED=7 then make ingest.

Pacing (recordings & walkthroughs)

make demo runs fast by default. To give viewers time to read each chart:

make demo-slow          # 1.5s pause between sections (and between charts)
make demo-pause         # press Enter when you're ready for the next beat
make record             # paced bench + demo (~2s) — handy for asciinema

# or tune the delay yourself:
python3 demo/run.py --delay 2
python3 benchmarks/run.py --delay 2.5

--pause is best when you're presenting live; --delay is best for unattended recordings.

What's in here

Path	What it shows
data/generate.py	Seeded synthetic dataset (no real data — see docs/DATA.md). Scale with --rows.
pipeline/ingest.py	DuckDB and Polars ingest → compressed, partitioned Parquet.
pipeline/overlay.py	The query-time base+delta overlay — the key idea.
pipeline/derived.py	Access-pattern-optimised derived table (see note above).
pipeline/analytics.py	Market velocity, depreciation curves, volatility — pushed into SQL.
automation/lifecycle.py	A small, testable listing-lifecycle state machine.
automation/guardrails.py	Sanity + canary checks so a delta load can't silently fail.
benchmarks/run.py	The reproducible benchmark harness (+ overlay vs rebuild chart).
demo/run.py	Narrated end-to-end demo (make demo).
demo/display.py	Terminal formatting (sparklines, bar charts) — stdlib only.

About the data

It's all synthetic — generated locally, seeded, reproducible. No DVLA data, no CarHunch data, nothing scraped; clone and run with no credentials or network. Every pattern the demo shows (depreciation, faster-selling hatchbacks, premium price drops) is engineered into the generator on purpose, so the charts illustrate the kind of analytics a marketplace runs rather than any real finding. Full breakdown — every field and how it's produced — in docs/DATA.md.

What this would take the old way

Illustrative, not benchmarked — but the orders of magnitude are real and familiar to anyone who's run these systems:

	This blueprint	Always-on cluster / legacy warehouse
Compute	One node, in-process, on-demand	Cluster running 24/7 (or large warehouse credits)
Licensing	None — open source	Per-core / per-credit (Oracle, Exadata, …)
Ongoing ops	Run a script; guardrails self-check	DBAs / platform team to feed, patch, tune, scale
To go faster	Bigger single box — cheap, linear	Add & rebalance nodes — complex, step-change cost
Monthly cost	A modest dedicated box	Often 10–100× more, before headcount

The argument isn't "clusters are bad" — it's that for workloads that fit on one node, the cluster is pure cost and overhead with no benefit. This repo lets you check whether yours fits.

Production case study: CarHunch

CarHunch is a live UK vehicle-insights platform — MOT history, risk insights, "hunches" and comparisons over the full DVLA MOT dataset (~1.7 billion rows). (It doesn't do pricing or valuations; the pricing analytics in this demo are a generic marketplace illustration, not a CarHunch feature.) What it shares with this blueprint is the architecture, and the real results behind it:

• Migrated the analytical read path off ClickHouse onto DuckDB + Parquet — no standing database server. I wrote it up here: Why I dropped ClickHouse for DuckDB.
• Daily ingest: 2+ hours (flaky, OOM, manual intervention) → ~70 seconds (~100× faster), by using the query-time delta overlay instead of rebuilding ~1.7B rows nightly.
• Derived tables replaced full canonical scans on hot paths (e.g. a 56 GB scan → a 14 GB table, ~9.6 s saved per request).
• Consolidated onto a single modest dedicated server (everything — API, DuckDB/Parquet, Redis, workers, WordPress — on one box) and put Cloudflare in front. It now serves real production traffic every day and is essentially hands-off: no cluster to babysit, no nightly firefighting. Low operational cost and low operational attention.

More engineering write-ups: https://engineering.carhunch.com

Scaling up

This runs at 5M rows on a laptop; the same code runs at hundreds of millions on a single dedicated box. To push it:

• python3 data/generate.py --rows 50_000_000 and re-run make bench.
• Swap the DuckDB ingest for the Polars path (--engine polars) to compare engines.
• Add more partition keys, or shrink row-group sizes, as your access pattern demands.
• For richer, stateful automation (fan-out, retries, human/LLM steps), grow the automation/ state machine toward something like LangGraph.

Tech

Python 3.10+ · DuckDB · Polars (optional) · Apache Parquet (ZSTD) · NumPy. No cloud dependencies; runs entirely locally.

Author

Donald Simpson — independent DevOps / platform / data engineer, working remotely on a project basis through my own consultancy. donaldsimpson.co.uk · github.com/DonaldSimpson

Related

• Why I dropped ClickHouse for DuckDB
• CarHunch engineering blog
• MOT embeddings demo

License

MIT — see LICENSE. Data is synthetic and generated locally; no DVLA or CarHunch data is included.