Pitwall

Formula 1 OLAP analytics engine built with DuckDB and NestJS.

Core complete — ingest, Kimball star Parquet lake, concept SQL, NestJS analytics API, materialized summaries, and benchmarks. Open to extensions (more seasons, auto-refresh, Iceberg later).

Pitwall is a self-contained OLAP environment: ingest real Formula 1 data, store it as partitioned Parquet, query it with DuckDB, and expose analytics through a NestJS API. The F1 domain keeps questions concrete (lap times, results, pit stops, teammates). The deeper goal is to show how analytical systems think differently from Postgres-style OLTP — and to demonstrate the DuckDB skills behind that shift.

Quick path

npm install
npm run ingest -- --seasons 2025          # Jolpica → Parquet lake (~50–70 min cold)
npm run partition                         # layout check
npm run query -- queries/materialized_aggregates.sql
npm run benchmark                         # pruning + raw vs summary
npm run start:dev                         # API on :3000

Import postman/Pitwall.postman_collection.json to explore the five analytics endpoints.

Lessons learned

Write-up of the Postgres → OLAP shift, star schema / grain, Parquet + Hive layout, ETL, materialization, and what I’d do differently:

→ From Postgres to OLAP: What I Learned Building an F1 Analytics Engine with DuckDB

Why this exists

Most backend engineers live in OLTP: normalized schemas, B-tree indexes, transactions, row-oriented storage. Analytical workloads are different. They scan large slices of history, aggregate heavily, and care more about layout and compression than about point lookups.

Pitwall makes that difference tangible. You build a pipeline, run queries that would hurt a busy Postgres instance, read the plans, and see why columnar engines win — not only that they are faster.

Technical skills — DuckDB & OLAP

This section is the skill map the project is designed around. Each skill is something you should be able to explain and demonstrate after working through the repo.

Engine & execution

Columnar storage. DuckDB stores values by column, not by row. Aggregations that touch a few columns (average lap time, points totals) read far less data than a row store that must load entire tuples. Contrast this with Postgres heap pages when you SELECT avg(time_ms) over millions of rows.

Vectorized execution. Instead of processing one tuple at a time (volcano / iterator style), DuckDB works on batches of values (vectors). That improves CPU cache use and enables SIMD-friendly loops — one reason heavy GROUP BY work stays responsive locally.

Push-based pipelines. DuckDB plans are better read as fused forward pipelines than as a classic pull-based iterator tree. Operators push batches downstream; understanding that shape is the key to reading DuckDB EXPLAIN output.

EXPLAIN / EXPLAIN ANALYZE. Learn to spot which row groups were scanned vs skipped, where filters were pushed into the scan, and how operators fuse. Compare the same SQL on Postgres: one plan is an iterator tree; the other is a pipeline with scan statistics.

Storage & layout

Apache Parquet. The on-disk format for the data lake. DuckDB can query Parquet files directly — no “load into a warehouse first” step. Schema, compression, and column statistics live in the file footer.

Hive-style partitioning. Data lands under paths like season=2024/laps.parquet. The folder name is a column. Filters on season become opportunities to skip entire directories.

Partition pruning. With a good layout, DuckDB never opens seasons you did not ask for. There is no B-tree here — the directory tree is the index. This is the same idea behind large S3 lakes.

Compression encodings. Low-cardinality columns (status, constructor_id) compress aggressively with dictionary encoding; runs of repeats suit RLE; ordered numerics suit delta. Export the same season to CSV and compare size — the difference is the skill, not the file extension.

Analytical patterns

Window functions at scale. Rolling averages, deltas from race 1, pace ranks per round — answered in one pass across seasons. These queries are legal in Postgres; at lake scale they are where OLAP engines earn their keep.

Pre-aggregation / materialization. Build driver and constructor season summaries (points, wins, podiums, DNFs, average finish). Same answers as a raw scan, much lower latency. Trade-off: freshness equals last rebuild.

OLTP vs OLAP boundary. Decide when analytics should leave the primary transactional database. Pitwall’s stance: keep writes and serving in OLTP systems; run heavy historical analytics in an embedded columnar engine over Parquet.

Embedded analytics in NestJS. A shared DuckDB connection behind typed controllers/services — analytics as an application capability, not a separate BI stack.

Features

Ingest ETL from Jolpica (REST → stage → grain checks → Parquet), with throttle, retries, and URL cache
Kimball star lake — shared dims + independent facts under facts/season=YYYY/
DuckDB in place — aggregations, windows, EXPLAIN ANALYZE, Hive partition pruning
NestJS analytics API — standings, driver form, constructor pace, pit stops, teammates
Materialized summaries — driver_season_summary / constructor_season_summary; standings prefer the summary when present
Concept SQL under queries/ mapped to the OLAP skill map above
Benchmark — median timings for pruning and raw GROUP BY vs summary (scripts/benchmark.ts)
Postman collection for the HTTP surface

Tech stack

Analytical database: DuckDB — embedded columnar OLAP engine
Storage: Apache Parquet, Hive-style partitions by season
Data source: Jolpica F1 API — open Ergast replacement
API: NestJS (TypeScript) — typed HTTP surface over DuckDB

Architecture

Jolpica REST API
        │
        ▼
 Ingest script (NestJS / TypeScript)
        │
        ▼
 DuckDB staging tables
        │
        ▼
 Partitioned Parquet lake
 dims/ + facts/season=YYYY/
        │
        ▼
 DuckDB (reads Parquet directly)
        │
        ▼
 NestJS analytics API

Design rationale

Jolpica instead of FastF1. FastF1 is Python-centric. Jolpica is a plain REST API, so the whole stack stays in TypeScript without a sidecar process.

Parquet + season= partitions. Layout enables pruning. Queries for one season should not pay for every season on disk.

Embedded DuckDB. Analytical performance without operating a separate warehouse. One process, file-backed lake, API in front.

Stage then write. Ingest lands in DuckDB staging tables first so types, nulls, and duplicates can be checked before Parquet is written. Re-runs should be safe to repeat for the same seasons.

Dataset

Source: Jolpica-F1 API — free, no registration, no API key. Actively maintained replacement for the defunct Ergast API.

Coverage: 1950 → current season, updated after race weekends.

Entities

f1_lap_times — per-lap timing and position (season, round, driver_id, lap, position, time_ms)
f1_results — race outcomes (grid, finish_position, points, status, fastest_lap_ms, constructor_id)
f1_pit_stops — stop number, lap, and duration_ms per driver
f1_qualifying — q1_ms, q2_ms, q3_ms, and grid position

Grain matters: one row in f1_lap_times is one driver on one lap of one race; one row in f1_results is one driver in one race. Mixing grains is the fastest way to get wrong analytics.

Questions this data can answer

Driver performance

Is average lap time genuinely improving across a season, or is it variance?
Rolling 5-race average finish — real form or noise?
Which driver closed the teammate gap the most from round 1 to the finale?

Constructor analysis

Which constructor improved pit-stop duration the most across a season?
Qualifying pace vs race pace correlation by constructor
Which car degrades least (lap 1 vs late-race lap time delta)?

Historical patterns

Which circuits produce the most position changes?
Points-per-race consistency across champions
DNF rates by constructor in the turbo-hybrid era (2014–present)

Race strategy

Pace drop across a stint on aging tires
Undercut vs overcut success by circuit (where the data supports it)
Drivers who consistently finish ahead of their grid position

Project structure

pitwall/
├── data/parquet/f1/           # Generated locally (gitignored Parquet)
│   ├── dims/                  # drivers, constructors, circuits, races
│   ├── facts/season=YYYY/     # results, laps, pit_stops, qualifying
│   └── summaries/             # optional materialized aggregates
├── docs/
│   ├── SCHEMA.md
│   ├── INGEST.md              # How to run ingest under Jolpica limits
│   └── ETL-LESSON.md          # ETL flow + developer takeaways
├── postman/                   # API collection
├── src/
│   ├── app.module.ts
│   ├── database/              # Shared DuckDB connection
│   └── f1/                    # Analytics controllers, service, repository
├── scripts/
│   ├── ingest.ts
│   ├── partition.ts
│   ├── benchmark.ts
│   ├── query.ts
│   ├── schema.ts
│   └── lib/                   # Jolpica client + time parsing
├── queries/                   # Training SQL (not the API)
├── CONTRIBUTING.md
├── SECURITY.md
├── LICENSE
└── README.md

Generated DuckDB files (*.duckdb), Parquet, and data/cache/ are not committed. See docs/INGEST.md.

Getting started

Prerequisites

Node.js 20+
npm, pnpm, or yarn
Network access to Jolpica for ingest

Install

git clone https://github.com/iikareem/pitwall.git
cd pitwall
npm install

Ingest (2025 star schema)

Full guide: docs/INGEST.md.

# First run: ~50–70 min (Jolpica 500 req/hour; laps dominate)
npm run ingest -- --seasons 2025

# Validate dims + facts exist
npm run partition

If you hit HTTP 429, wait and re-run the same command — responses are cached under data/cache/jolpica/.

Run the API

npm run start:dev

Default base URL: http://localhost:3000

Run concept SQL

npm run query -- queries/vectorized_aggregation.sql

Benchmark

npm run benchmark measures two OLAP ideas on your local lake:

Check	What it compares	Why it matters
Partition pruning	`COUNT(*)` all seasons vs `WHERE season = N`	Hive folders act like an index — skip unused years
Materialization	Live standings `GROUP BY` on results vs read `driver_season_summary`	Pay once at rebuild; serve cheap reads (same pattern as the API)

# After ingest, build summaries then bench
npm run query -- queries/materialized_aggregates.sql
npm run benchmark

# Optional knobs
BENCH_SEASON=2024 BENCH_RUNS=9 npm run benchmark

Example output shape:

— Partition pruning —
full scan  COUNT(*) all seasons: median … ms
pruned scan COUNT(*) season=2025: median … ms
  → pruning: N× faster than raw

— Standings (raw GROUP BY vs summary) —
Correctness — top driver match: OK
raw       GROUP BY …: median … ms
summary   read …:     median … ms
  → materialization: N× faster than raw

On a small laptop lake the millisecond gap can be tiny; at warehouse scale the same design saves real latency and cost. Details live in the header of scripts/benchmark.ts.

API

Analytics surface (standings prefer summaries/driver_season_summary.parquet when present):

GET /analytics/f1/:season/standings — championship picture: points, wins, podiums, DNFs
GET /analytics/f1/:season/driver/:id/form — rolling lap-time / finish form for one driver
GET /analytics/f1/:season/constructors/pace — constructor pace and points comparison
GET /analytics/f1/:season/constructors/pitstops — pit-stop duration trends by team
GET /analytics/f1/:season/teammates — head-to-head teammate comparison within each constructor

Import postman/Pitwall.postman_collection.json or use curl:

curl http://localhost:3000/analytics/f1/2025/standings
curl http://localhost:3000/analytics/f1/2025/driver/norris/form
curl http://localhost:3000/analytics/f1/2025/teammates

Rebuild standings summaries after ingest with:

npm run query -- queries/materialized_aggregates.sql

Concept queries

Hands-on SQL lives under queries/. Each file starts with a header comment: what it applies to, when to use it, and how to run it. Work through them in order after ingest.

1. Vectorized aggregation — `vectorized_aggregation.sql`

Run heavy aggregations across seasons: average lap time, fastest lap, laps completed per driver. Measure wall time. Optionally compare the same shape of query on Postgres. DuckDB should feel dramatically faster because it only reads needed columns.

2. Parquet compression — `parquet_compression.sql`

Export one season’s results to CSV and compare size with Parquet. Inspect low-cardinality columns such as status (Finished / DNF / DNS) and constructor_id. That cardinality is why dictionary encoding compresses so well.

3. Plan reading — `explain_analyze.sql`

Run EXPLAIN ANALYZE on a filtered query. Find pushed-down filters, skipped row groups, and fused pipelines. Compare with Postgres EXPLAIN ANALYZE on the same predicate.

4. Window functions — `window_functions.sql`

Answer: is this driver improving, or is it variance? Build rolling 5-race averages, deltas from race 1, and pace ranks per round in one query across seasons. Imagine the same load on a busy OLTP database at peak traffic.

5. Partition pruning — `partition_pruning.sql`

Compare a full multi-season scan with a single-season filter. Confirm unused season= folders are never opened. Layout is the index.

6. Materialized aggregates — `materialized_aggregates.sql`

Build driver and constructor season summaries, then compare latency with npm run benchmark (raw GROUP BY vs reading the summary Parquet). Same answers, different cost. Remember the freshness trade-off: rebuild summaries after ingest.

Suggested path: ingest → queries 1–6 → npm run benchmark → NestJS endpoints → reconcile API output with raw SQL.

What you should be able to explain afterward

Why columnar storage exists and when you would choose it over a row store
How to read a DuckDB EXPLAIN ANALYZE plan and talk about push-based pipelines
What partition pruning is and how to design folder layout to exploit it
Where the OLTP / OLAP boundary sits in a real backend architecture
Why Parquet is the default format for analytical lakes
Why pre-aggregation is a design pattern, not a one-off trick (and how to prove it with npm run benchmark)
How to embed DuckDB in a NestJS service and expose typed analytics over REST

Contributing

See CONTRIBUTING.md. Issues and design feedback are welcome.

For security reports, see SECURITY.md.

Resources

From Postgres to OLAP — lessons from building Pitwall (Hashnode)
DuckDB documentation
DuckDB blog / internals
Jolpica F1 API
NestJS documentation
How Query Engines Work (free book)

License

Released under the MIT License.

Name		Name	Last commit message	Last commit date
data/parquet/f1		data/parquet/f1
docs		docs
postman		postman
queries		queries
scripts		scripts
src		src
.env.example		.env.example
.gitignore		.gitignore
CONTRIBUTING.md		CONTRIBUTING.md
LICENSE		LICENSE
README.md		README.md
SECURITY.md		SECURITY.md
nest-cli.json		nest-cli.json
package-lock.json		package-lock.json
package.json		package.json
tsconfig.json		tsconfig.json

Latest commit

History

Folders and files

Repository files navigation

Pitwall

Quick path

Lessons learned

Why this exists

Technical skills — DuckDB & OLAP

Engine & execution

Storage & layout

Analytical patterns

Features

Tech stack

Architecture

Design rationale

Dataset

Entities

Questions this data can answer

Project structure

Getting started

Prerequisites

Install

Ingest (2025 star schema)

Run the API

Run concept SQL

Benchmark

API

Concept queries

1. Vectorized aggregation — vectorized_aggregation.sql

2. Parquet compression — parquet_compression.sql

3. Plan reading — explain_analyze.sql

4. Window functions — window_functions.sql

5. Partition pruning — partition_pruning.sql

6. Materialized aggregates — materialized_aggregates.sql

What you should be able to explain afterward

Contributing

Resources

License

About

Topics

Resources

Contributing

Security policy

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

1. Vectorized aggregation — `vectorized_aggregation.sql`

2. Parquet compression — `parquet_compression.sql`

3. Plan reading — `explain_analyze.sql`

4. Window functions — `window_functions.sql`

5. Partition pruning — `partition_pruning.sql`

6. Materialized aggregates — `materialized_aggregates.sql`