Skip to content
Back to projects
Own project

Open data warehouse: a Kimball star schema on RDW and CBS

An end-to-end data warehouse on Dutch open data — from API to Power BI, with a star schema that can carry the questions.

Two public sources, one data warehouse. Vehicle data from the RDW (the Dutch vehicle authority) supplies record-level facts; CBS StatLine (Statistics Netherlands) supplies a periodic snapshot per municipality. Bringing those two together in one dimensional model sounds simple, until you notice that they have a different grain, that the Netherlands redraws municipal boundaries almost every year, and that the source doesn't stick to its own schema.

This project works that out: Python ingestion, a dbt project with staging, star schema and marts, data-quality tests that run on every pull request, and a Power BI report that sits directly on the schema. Built the way a platform team would deliver it — with version control, code review and a lineage that is correct.

dbt
SQL
Python
DuckDB
Data Engineering
Star schema

2 facts · 5 dimensions · 1 bridge

Default run

2.8 million rows

CI on every PR

Lint + build + tests

From zero

One command

Python
dbt
DuckDB
Power BI
GitHub Actions
ruff · sqlfluff
Make
Architecture of nl-vehicle-warehouse in five columns (labels in Dutch): sources (RDW open data via the Socrata API, four datasets: vehicles, fuel, defects, defect codes; CBS StatLine via odata: 70072ned, 85237NED and Gebieden 2015-2026), ingestion in Python (rdw.py with offset pagination of 50,000 per page, cbs.py with pagination via @odata.nextLink, common.py with retries, batches, everything as string and the column list from the dataset metadata; one licence-plate range for all RDW sets), data/raw/ (23 parquet files, 2.8 million rows, about 47 MB zstd, a literal copy of the source, not in git; _manifest.json with snapshot date, selection query, plate range and row counts, which is in git), dbt on DuckDB (staging as views 1:1 with the source; warehouse as tables with 2 facts, 5 dimensions and 1 bridge; marts as tables) and use (Power BI on the star schema, dbt docs as a CI artifact, exports as parquet). At the bottom the quality and CI strip: ruff, sqlfluff, dbt build, data-quality tests and dbt docs generate, on every pull request against small fixtures.
From two open sources to a star schema and a report — every step in version control and tested in CI.

Why this project

Portfolio projects with data often stop at “read a CSV and make a chart”. What makes a data warehouse hard sits elsewhere: in the choices you make before there is a dashboard. What grain does a fact sit on. What do you do with a dimension that changes. How do you make sure a colleague gets the same numbers you do.

I wanted a project in which those choices are visible and defended, not polished away. Every design choice is documented in the repository, including the things that couldn't be done.

The sources

The combination is deliberate. Two facts with a different grain in one schema is exactly what dimensional modelling is about — and where it goes wrong if you don't make the grain explicit.

Both sources can be queried without an API key; anyone can check every number on this page themselves.

SourceWhatRole in the model
RDW open data (Socrata)registered vehicles, fuel, observed defects, defect codesrecord-level transaction facts
CBS StatLine (odata)motor-vehicle fleet per municipality and the municipal boundaries per yearsnapshot fact + municipality dimension

Architecture

The chain is deliberately boring and linear. Python fetches the sources and writes them as parquet into data/raw/, without changing anything at all. dbt reads that as a source, types and renames in staging/, builds the star schema from it in warehouse/, and answers the business questions in marts/. Power BI sits on the star schema, not on the source.

That separation isn't cosmetic. Because data/raw/ is a literal copy of the source, a mistake in the transformation can never quietly disappear into the ingestion — it can always be traced to a dbt model that is in git and went through code review.

Three choices that shape the chain

01

Raw is a literal copy

Ingestion changes nothing: no typing, no renaming, no filter. Everything that makes the warehouse differ from the source lives in a dbt model — and so can be read, reviewed and reverted.

02

Three dbt layers, one job each

staging/ is 1:1 with the source: type, rename, deduplicate, pivot CBS. warehouse/ is the Kimball star schema. marts/ answers the business questions. A model belongs in exactly one layer, and the lineage shows that it does.

03

DuckDB, but not in the models

DuckDB as the warehouse: zero cost, runs in CI, and one file to review. The same dbt project targets Snowflake or Databricks with a different profile; outside staging/ there is no DuckDB-specific SQL. The choice of warehouse isn't part of the modelling, and that is exactly the point.

The hardest part was the ingestion

The three large RDW sets together hold about 58 million rows. Taking one snapshot out of that is no problem. Taking three is.

The sets are all keyed on licence plate. Taking the first 500,000 rows of each set yields three different vehicle populations: the defects set has several rows per vehicle and so covers a much narrower plate range than the vehicles set. The result is facts pointing at dimension rows that aren't in the snapshot, and not a single relationships test holding up. You end up with a dataset that looks fine and is wrong.

The fix is to determine one range on the vehicles set — sorted by plate, position 0 and position 499,999 — and fetch every other set on that same range. The range is deterministic, so the run is reproducible as long as the source doesn't change, and the chosen range is recorded in a manifest that is in git. Scaling up to the full set is one variable.

58M

rows in the three large RDW sets together; the default run samples from them

1

licence-plate range, determined on the vehicles set and reused for every other set

0

orphan rows in fuel and defects after ingestion — verifiable with the relationships tests

The result can be checked

  • Zero orphan rows in fuel: every fuel row points at a vehicle in the snapshot.

  • Zero orphan rows in defects: every observed-defect row points at a vehicle in the snapshot.

  • All 622 defect codes in use are present in the code list.

These are exactly the relationships tests that failed with three separate snapshots.

Two smaller choices that kept the schema stable

01

Everything is read as a string

The sources deliver JSON without reliable typing. Typing belongs in dbt, where it is tested — not in an ingestion script where one unexpected value topples the whole batch.

02

The column list comes from the metadata, not the response

Socrata simply omits empty fields, so different pages yield different keys and merging batches falls over on schema differences. The column list is therefore taken from the dataset metadata up front. The vehicles set appears to have 52 columns and has 98.

data/raw/_manifest.json — snapshot date · selection query · plate range · row counts

The parquet files aren't in git; the manifest is. That makes every run traceable: which source, which range, how many rows, on which day.

The model

For every fact table the grain is stated explicitly in the documentation — one sentence, no interpretation possible. Every dimension hangs off the facts through a surrogate key; degenerate dimensions (plate, report date, defect id, vehicle type) stay in the fact table, so the grain test can run on them.

The star schema (labels in Dutch): two facts with a different grain in one model. fct_gebrek_constatering is a transaction fact with grain one observed defect per inspection per vehicle and measure aantal_gebreken = 1; attached are dim_voertuig (SCD type 1, plate and type attributes), dim_gebrek (SCD type 1, RDW defect code, description and validity period) and dim_datum via meld_datum_key. dim_voertuig connects through bridge_voertuig_brandstof (12,671 vehicles have more than one fuel — prevents double counting) to dim_brandstof. fct_voertuigpark_gemeente is a periodic snapshot with grain one municipality per reference year per vehicle type and measure aantal; attached are dim_gemeente (SCD type 2 with geldig_van and geldig_tot, via a temporal join on gemeente_key) and dim_datum via peildatum_key. dim_datum is generated, holds Dutch public holidays and quarter and week numbers, and is shared by both facts as a conformed dimension.
Two facts with a different grain, joined by dim_datum as the shared dimension.

Four tables that carry the model

fct_gebrek_constatering

One observed defect per inspection per vehicle

So counting defects is simply count(*). The source column with the inspection total is deliberately left out: it repeats the same number on every row of the same inspection, and a sum() over it double counts.

fct_voertuigpark_gemeente

One municipality per reference year per vehicle type

A periodic snapshot next to a transaction fact, with dim_datum as the shared dimension. The municipality key comes from a temporal join on the SCD2 dimension: the number hangs off the municipality as it was called then.

bridge_voertuig_brandstof

12,671 vehicles with more than one fuel

Without a bridge table you count those twice as soon as someone filters on energy source. In Power BI the same bridge is set up as many-to-many with one bidirectional cross filter, so the report behaves the same as the warehouse.

dim_datum

Generated, with Dutch public holidays

Quarter and week numbers, Dutch public holidays, no source needed. As the conformed dimension it is the only table both facts share — and so the axis along which you put them side by side.

Repeating the inspection total on every row and then summing it — that's the kind of mistake nobody notices until a steering committee asks why a number doesn't add up.

Why the municipality dimension is SCD type 2

The Netherlands went from 393 municipalities in 2015 to 342 in 2026. The difference between two CBS boundary files is the redivision you have to record.

Without a dimension with validity periods, the 2016 number hangs off the municipality as it is called today. Historical management information then shifts retroactively, and there is no error — the query just runs. That is the silent kind of data error, and the most annoying one.

The dimension is built with a lag() over reference year per municipality code: every name change marks a new version, and the running sum over those markers is the version number. The fact doesn't join on code alone, but on code and reference date within the validity period.

393 → 342

municipalities between 2015 and 2026; every difference between two years is a redivision

12

CBS boundary files, one per reference year, from which the versions are derived

3

custom tests that guard the validity periods

SCD type 2: the number hangs off the municipality of that time (labels in Dutch). Left the source, CBS Gebieden in Nederland, one file per reference year from 2015 to 2026: 2015 393 municipalities, 2018 380 (+3/−11), 2019 355 (+9/−34), 2023 342 (+1/−4); the difference between two years is the redivision. In the middle dim_gemeente as SCD type 2 with gemeente_key, name, geldig_van and geldig_tot: version 1 Haaren 2015-01-01 to 2020-12-31, version 2 Oisterwijk 2021-01-01 to 9999-12-31; built with lag() over reference year per gemeente_code, with custom tests for contiguous, non-overlapping periods and at most one current version. Right fct_voertuigpark_gemeente with reference year 2016 (falls in version 1) and 2023 (falls in version 2): every fact row gets the surrogate key of the version valid on 1 January of that year. At the bottom the temporal join: on gemeente_code = gemeente_code and make_date(peiljaar,1,1) between geldig_van and geldig_tot. Without type 2 the 2016 number would hang off the current municipality.
Haaren merged into Oisterwijk in 2021: the 2016 number stays on version 1, the 2023 number on version 2.

Three custom tests guard the dimension

  1. 01

    The periods per municipality code are contiguous: geldig_tot of one version meets geldig_van of the next.

  2. 02

    The periods don't overlap: a reference date falls in at most one version.

  3. 03

    There is at most one current version per municipality.

The temporal join is the only place where the fact touches the dimension; when these three tests are green, that join is unambiguous.

What couldn't be done, and why that is in the documentation

The intended grain for the snapshot fact was municipality per reference year per fuel type. That doesn't exist at CBS. The table with the fuel split doesn't go below province; the table with 728 municipalities only splits by vehicle type. Municipality × fuel is only possible by making numbers up.

Both tables were fetched and the choice is written out in the repository: it became municipality × vehicle type, because the temporal link to the SCD2 dimension is the point of this fact. The unused table is a deliberate choice, not a forgotten file.

A model whose limitations are written down is usable. A model whose limitations aren't written down will sooner or later be used wrongly.

Quality and CI

On every pull request GitHub Actions runs the same five steps. The lineage below isn't a drawing next to the code but what dbt derives from the source() and ref() calls: every arrow is an explicit reference in the SQL.

Data lineage from source to mart in four lanes (labels in Dutch). Sources (data/raw/*.parquet): rdw.gekentekende_voertuigen, rdw.brandstof, rdw.gebreken (code list), rdw.geconstateerde_gebreken, cbs.gebieden (one file per reference year 2015-2026), cbs.70072ned (motor vehicles per municipality). Staging (views, 1:1 with the source): stg_rdw__voertuigen, stg_rdw__brandstof, stg_rdw__gebreken, stg_rdw__gebrek_constateringen (this is where the source duplication is deduplicated), stg_cbs__gemeenten, stg_cbs__voertuigpark_gemeente (long format pivoted and labelled). Warehouse (tables, star schema): dim_voertuig, bridge_voertuig_brandstof, dim_brandstof, dim_gebrek, fct_gebrek_constatering, dim_datum (generated, no source), dim_gemeente (SCD type 2, temporal join), fct_voertuigpark_gemeente. Marts: mart_gebrek_top (top defects per vehicle type and build year), mart_rdw_cbs_aansluiting (sample next to the CBS fleet), mart_voertuigpark_herindeling (fleet per municipality and reference year, with redivisions). Every arrow is a source() or ref() in the SQL.
Every arrow is a source() or ref() in the SQL; dbt docs generates this lineage on every pull request.
StepToolWhat it guards
Python lintruffthe ingestion scripts
SQL lintsqlfluffevery dbt model compiles and follows the style rules
Builddbt buildmodels and tests, in dependency order
Data qualitydbt testsunique and not_null on every key, relationships from every foreign key to its dimension, accepted_values on the reference columns, plus custom tests on the grain: no duplicate rows per grain definition
Documentationdbt docs generatelineage and column documentation, kept as a CI artifact

CI doesn't run on the full snapshot but on small fixtures that are in git, so a pull request is green or red within a few minutes. The same command runs locally with a single make ci: CI can't do anything you can't.

Power BI

The report answers three questions directly on the star schema.

01

The vehicle fleet per municipality and reference year

Including redivided municipalities: a 2016 number sits with the municipality as it was called then, thanks to the SCD2 dimension.

02

The top defects per vehicle type and build year

With a slicer on energy source that, thanks to the bridge, doesn't double count vehicles with more than one fuel.

03

How the RDW sample lines up with the CBS fleet

How the sample from the RDW sets relates to the full fleet that CBS reports per municipality.

The semantic model mirrors the schema and doesn't repair it. Relationships run 1:* from dimension to fact with a single-direction cross filter, the SCD2 dimension hangs off the fact via the surrogate key so the temporal join from the warehouse is preserved, and the DAX measures are cross-checked with dbt show. A report that bypasses the model is a second model — and then you have two truths.

What this project shows

Dimensional modelling the Kimball way
ELT with dbt: staging, star schema, marts
Reproducible ingestion in Python
SCD type 2 with temporal joins
Data-quality tests on keys and grain
CI/CD and PR workflow with GitHub Actions
A warehouse setup that ports to Snowflake or Databricks
Power BI on the star schema, not on the source

The numbers are traceable, the choices are written down, and someone else can run it.

Sources: RDW open data (opendata.rdw.nl) and CBS StatLine (opendata.cbs.nl). Both can be queried without an API key.

The documentation of the data and the sampling approach lives in docs/data.md in the repository.