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.
2 facts · 5 dimensions · 1 bridge
2.8 million rows
Lint + build + tests
One command
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.
| Source | What | Role in the model |
|---|---|---|
| RDW open data (Socrata) | registered vehicles, fuel, observed defects, defect codes | record-level transaction facts |
| CBS StatLine (odata) | motor-vehicle fleet per municipality and the municipal boundaries per year | snapshot 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 countsThe 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.
Four tables that carry the model
fct_gebrek_constateringOne 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_gemeenteOne 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_brandstof12,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_datumGenerated, 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
Three custom tests guard the dimension
01
The periods per municipality code are contiguous: geldig_tot of one version meets geldig_van of the next.
02
The periods don't overlap: a reference date falls in at most one version.
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.
| Step | Tool | What it guards |
|---|---|---|
| Python lint | ruff | the ingestion scripts |
| SQL lint | sqlfluff | every dbt model compiles and follows the style rules |
| Build | dbt build | models and tests, in dependency order |
| Data quality | dbt tests | unique 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 |
| Documentation | dbt docs generate | lineage 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
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.