A tested dbt + DuckDB pipeline that turns nine authoritative/open-data sources into explainable, documented neighbourhood indicators — with published lineage, 228 data tests + 2 dbt unit tests, and a reproducible fixture-to-full build.
The engine rolls fragmented public housing data up to a consistent MSOA grain (7,264 England & Wales neighbourhoods) and derives five transparent 0–100 indicators: affordability, recorded crime, energy, flood resilience and convenience. Each is kept beside the raw figure it came from, with per-area evidence quality driven by coverage, provenance and source grain. Missing data lowers evidence quality; it never silently becomes a zero. The "where to live" framing is the vehicle. The substance is the pipeline, the dimensional and decision modelling, the tests, and the explainability layer.
Scope. A reference analytics-engineering project, not a product — the UK area-data space is already well served (CrystalRoof, Plumplot, PostcodeCheck, …). It's an end-to-end data stack built over official open data.
This is not positioned on transparency of scoring. Transparent 0–100 neighbourhood composites already exist, and at least one publishes its full component weights and gives the report away free. What is uncommon here is narrower and purely a matter of format: a multi-domain, small-area-grain, open-code, reproducible composite — the whole thing rebuilds from source with one command and is tested at every layer. The closest open comparator, AHAH (v5.1, OGL, open code, 43,064 small areas across Great Britain), is scoped to health. That is an absence of a format, not of a served need, and it is not a claim that anyone is underserved.
The website and repository now share one descriptive identity: England & Wales Housing Decision Support. The repository slug is
england-wales-housing-decision-support.
One of three UK open-data builds on my profile — siblings tfl-data-engineering (Spark/Airflow/MCP at scale) and community-energy-flex (a decision system with LP/MILP optimisation and a forecast-vs-actual retro). Full project map → profile.
| Surface | URL |
|---|---|
| 🌐 Housing decision-support website (Next.js / Vercel) | https://uk-housing-decision-support.vercel.app |
| ⚙️ API (FastAPI / Fly.io) — OpenAPI docs | https://uk-housing-decision-support-api.fly.dev/docs |
| 📊 dbt docs (lineage + column catalogue) | https://rosscyking1115.github.io/england-wales-housing-decision-support/ |
The dbt + DuckDB warehouse is the centre of gravity. It builds a small
read-only decision.duckdb extract that a thin FastAPI service serves; a Next.js
website is one HTTP client of it. The clients exist to show the modelling is
frontend-agnostic and consumable; they are not the point.
dbt + DuckDB engine ──► data/decision.duckdb ──► API (FastAPI, api/)
(9 source families) (slim extract) │ /v2 + OpenAPI
│
Website (web/, Next.js)
— a thin demo client
The transformation is specified by a versioned machine-readable contract and
golden cases under contracts/. Four runtimes compute the overall
score — the dbt mart, api/scoring.py, the website's web/src/lib/reweight.ts,
and scripts/rescore_extract.py — and each is bound to
contracts/neighbourhood-scoring-v2.json: three read that file directly, and the
dbt weight bounds are tied to it by a parity test rather than retyped. A fifth
copy that read neither the contract nor the golden cases would be a silent
divergence risk, so
tests/test_scoring_single_definition.py
fails the build if the formula turns up in an undeclared file.
(A Streamlit MVP and an Expo mobile client were also built and are now parked;
the maintenance policy closes the feature roadmap.)
| Metric | Declared owner and grain | Meaning and caveat |
|---|---|---|
| Area sale context | rpt_area_profile_mvp — one MSOA area_id |
Latest configured-year matched-sale median, count, year and confidence state. It is area context, not valuation. |
| Regional price change | rpt_price_yoy_by_region — region × transferred_year |
Same-region change between consecutive calendar-year medians; absent prior year remains null. Analytical reporting only. |
| Regional new-build premium | rpt_new_build_premium — region × transferred_year |
New-build versus existing median for the same regional year; null when the existing-sale denominator is missing or zero. Analytical reporting only. |
The metrics intentionally stay at their source-supported grains. The reference build does not infer an MSOA YoY rate or a property price from regional figures. The grains, denominators, null semantics and permitted consumers are fixed in ADR-001: Area-market metric contract.
That ADR also records the project's most deliberate omission. Its "SCD2
decision: rejected" section refuses to put a slowly-changing dimension over
dim_area: the geography loader recreates the postcode table from one
normalised source file and retains no prior releases, so no real geography
history exists in this repository to record. Deriving valid-from/valid-to rows
from repeated builds, file timestamps or the CI fixture would manufacture
history rather than record it, and the ADR forbids it. It then names the five
conditions that would have to hold before SCD2 is reconsidered — retained
authoritative releases, an attributable effective date per release with a
documented source-effective vs ingestion-effective choice, a stable business key
or official crosswalk policy, a change-bearing attribute with a documented
"as-of" use, and an ingestion path that appends releases instead of replacing
the current table. Until then: current-state geography plus source-snapshot
metadata.
Worked area-contract example. The API golden case for MSOA E02006959
contains a matched-sale median of £267,295, from 417 matched sales in
the configured 2025 reference year, with a reliable confidence state.
That is evidence about the area's recorded-sale context in the test fixture —
not a valuation of a home, forecast, or promise about a live extract refresh.
| Path | What |
|---|---|
models/ |
dbt models: sources → staging → intermediate → marts (the engine). |
seeds/, macros/, analyses/ |
dbt seeds (fixtures + name lookups), reusable macros (haversine_km, median_anchored), ad-hoc analyses. |
scripts/ |
Data prep/load scripts for the real (non-fixture) sources. |
orchestration/ |
Dagster asset graph over the monthly refresh: ingestion (+ a pre-dbt data-quality gate) → dbt → decision extract. |
tests/ |
29 singular dbt tests + Python/API regression suites. The other 199 data tests are generic tests declared in the models/ and seeds/ schema files; 228 in total. |
api/ |
FastAPI service over the decision marts (resolve / search / listing-check / areas index / meta). Dockerfile + fly.toml. |
web/ |
Housing decision-support website — Next.js. Search, compare, listing checker, and ~7k programmatic area/town/region/rent pages. See web/README.md and web/DESIGN_BRIEF.md. |
data/ |
Local DuckDB warehouse + the committed decision.duckdb extract the API ships. |
docs/ |
Architecture decision records (docs/adr/) and the evidence inventories behind them. |
DEPLOY.md |
Runbook for deploying the API (Fly.io) and website (Vercel). |
The website's search map uses MapLibre GL JS with the keyless OpenFreeMap tile service, so it has no usage-based map API bill. The provider is free and open-source but offers no availability SLA; all search results remain usable as an ordinary list if map tiles are unavailable.
Two reference docs at the repo root carry the modelling detail:
HOUSING_AREA_PROFILE_CONTRACT.md (the
per-area output contract) and
HOUSING_DECISION_SUPPORT_DATA_SOURCES.md
(every source, its licence, and its coverage).
A complete, tested analytics-engineering pipeline is the project's credibility: sources → staging → intermediate → marts (dimensions / facts / reporting), tested at every layer, with lineage and column-level docs published to GitHub Pages on every push. Nine authoritative/open-data source families support the output.
| Signal | Coverage | Notes |
|---|---|---|
| Sale-market context (HM Land Registry) | 4.99M transactions, 2021–2025 | Long-term market layer; median sale price per area. |
| Geography (ONSPD) | 2.73M postcodes → 7,264 E&W MSOAs | 99.999% Land Registry coverage; readable area/LA/region names. |
| Rent + affordability (ONS PIPR) | 100% of MSOAs | Local-authority rent incl. per-bedroom (1/2/3/4+). |
| Energy (EPC) | 100% (23.5M certificates) | Per-area median EPC band. |
| Crime (Police API) | 99.6% (17.1M crimes) | Approx. monthly rate per 1,000 — indicator only; locations are snapped, see below. |
| Population (ONS mid-year estimates) | 7,264 MSOA 2021 areas | Compatible mid-2024 denominator for the monthly crime rate. |
| Planning constraints (Planning Data Platform) | England only | Wales is explicitly not covered; no favourable zero default. |
| Flood (Environment Agency zones via Planning Data Platform) | England only | Share of postcodes intersecting a zone; Wales is explicitly not covered. |
| Convenience (OpenStreetMap) | 100% (437k amenities) | Nearest supermarket/school/GP/park/station + walkable count. |
Why the crime figure is an indicator and not a measurement. Police.uk does not publish where a crime happened. Before publication each crime is moved to the nearest point on a fixed anonymisation grid — a master list of roughly 779,000 points built in 2012 and refreshed in 2022, each covering a catchment of at least eight postal addresses. Police.uk's own documentation states that inconsistent force geocoding policies mean the published location cannot be relied on as fully accurate or consistent, and puts geocoding accuracy across forces in a range from 60% to 97%. An area rate built from snapped points therefore carries locational error that varies by force and cannot be corrected downstream. This is why the indicator is published as a rate beside its period and denominator, and never as a safety judgement — and it is why that caveat is not softened anywhere in this project.
Each external source is fixture-default for fast, reproducible CI, with a
real-data toggle (--vars '<source>: …') for production builds. Explainable
scoring (rpt_neighbourhood_score) turns these into five 0–100 component scores,
a weighted overall, per-area evidence quality, and a "why this area" line.
Potential additions such as door-to-door commute time are deliberately outside
the active maintenance scope; station proximity remains the published transport indicator.
Geography, the source toggles, and the full per-source prep commands are detailed
in HOUSING_DECISION_SUPPORT_DATA_SOURCES.md
and the Source attribution section.
The score is a transformation you can read top to bottom, not a black box —
implemented in rpt_neighbourhood_score:
- Per-indicator normalisation → 0–100. Continuous indicators (rent-to-income
ratio, crime rate, station distance) use a median-anchored, winsorised
min-max via the
median_anchoredmacro: clip to the 2nd/98th percentile, then map p2→0, median→50, p98→100. This keeps magnitude (unlike a pure percentile rank, which forces a uniform spread and makes every area look extreme). Categorical/absolute indicators use fixed anchors — EPC band (A=100 … G=0), flood = share of postcodes in a flood zone. - Overall = weighted geometric mean of the indicators an area actually has
(floored at 1), so one excellent pillar can't mask a poor one. Weights are
configurable via dbt
vars; a client can re-weight from the stored component scores without recomputing the marts. - Missing indicators are dropped, never zeroed — an absent signal lowers the
area's evidence-quality level (
strong/mixed/limited) instead of silently penalising it. - Every score ships beside its raw figure (rent, crime rate, EPC band) and a
generated
why_this_areasentence, so the output is auditable.
Because it's one SQL transformation, the logic is covered by the same data tests as everything else (score bounds 0–100, coherence, and coverage/evidence rules).
Land Registry data refreshes monthly, so the refresh is modelled as a
Dagster asset graph (orchestration/) rather than a
sequence of hand-run scripts: six ingestion assets (the Land Registry spine is
downloaded automatically; five reference sources load from locally prepared
files) feed the whole dbt project and end at decision_extract, the slim
DuckDB file the API ships. The dbt project loads via dagster-dbt, so every
model is an asset and every dbt test an asset check in the same lineage.
Two design points worth reading the code for:
- Data-quality gates before dbt, with named thresholds. dbt tests run
after load; every ingestion asset is gated before it. The raw Land Registry
parquet must clear three assertions in
orchestration/checks.py: a row-count floor of 3,000,000 (a full 2021–2025 drop is around 5M, so anything far below it is a truncated or partial file), zero null-or-empty prices and zero null transfer dates, and a malformed-postcode rate of ≤ 1% — non-empty postcodes failing a UK postcode pattern, as a share of all rows, since genuinely empty postcodes are normal in older records. Each of the five reference sources then carries its ownprepared_file_is_sanecheck (row-count floor, required columns, non-null business key) evaluated before its drop-and-recreate load, so a truncated prepared file cannot replace a good warehouse table. All of these are Dagster asset checks withblocking=True: a failed gate halts the graph at the front door instead of propagating into the marts. - The orchestrated build is the real refresh. It parses and builds dbt
with the real-source vars, while plain
dbt buildkeeps the fixture-seed default for fast, reproducible CI. Onefull_refreshjob runs the whole pipeline (steps serialized — DuckDB is a single-writer file on Windows).
Why Dagster and not Airflow: this is a set of data assets with lineage, not a
task DAG — the asset/materialization model fits, and dbt lineage flows into the
same graph. Freshness is declared (35-day warn on the warehouse spine and the
extract, mirroring dbt's source freshness) and the monthly cadence is defined in
code — cron_schedule="0 9 28 * *", aligned to Land Registry's publication
around the 20th working day — but it ships with
default_status=DefaultScheduleStatus.STOPPED, and the code comment beside it
says why: the reference-source archives are large, licensed and fetched by hand,
so pretending an unattended cron runs in production "would be theatre". The
schedule documents the intended SLA; the job runs on demand
(dagster dev -m orchestration.definitions). Details and trade-offs in
orchestration/README.md.
git clone https://github.com/rosscyking1115/england-wales-housing-decision-support.git
cd england-wales-housing-decision-support
python -m venv .venv
# Windows: .\.venv\Scripts\Activate.ps1 | macOS/Linux: source .venv/bin/activate
python -m pip install --upgrade pip && pip install -r requirements.txt
dbt deps
mkdir -p ~/.dbt && cp profiles.yml.example ~/.dbt/profiles.yml # one-time
python scripts/download_raw.py # --sample for a faster 2-year run
python scripts/load_to_duckdb.py
dbt seed
dbt build --threads 1 # full warehouse + 228 data tests, < 5 min on a laptop.venv/Scripts/python -m uvicorn api.main:app --reload # http://127.0.0.1:8000/docscd web
cp .env.example .env.local # points at http://127.0.0.1:8000
npm install && npm run dev # http://localhost:3000The website needs the API running. Full web docs in web/README.md;
deploying both services is covered in DEPLOY.md.
ci.yml runs on every PR and gates main via branch protection: Python unit tests
(incl. the API suite), dbt build --threads 1 with 228 data tests + 2 unit tests,
source-freshness, sqlfluff, and the web lint/test/build checks.
| Layer | Count | What it catches |
|---|---|---|
| Source freshness | 1 | Stale upstream data (warn if nothing newer than 35 days). |
| Built-in row-shape | 163 | Schema bugs, FK orphans, enum drift, contract violations. |
dbt-utils |
22 | Sign/range invariants, multi-column uniqueness, score bounds. |
dbt-expectations |
14 | Type-cast bugs, statistical drift, format regressions. |
Singular (tests/assert_*.sql) |
29 | Domain anomalies, coverage, coherence, and cross-runtime golden cases. |
| dbt data-test total | 228 | All passing on every dbt build. |
| dbt unit tests | 2 | Model logic on mock inputs: enrichment (postcode parse + region join + filter) and the scoring maths (median-anchored min-max, geometric-mean floor, missing-component rule). |
API (tests/test_api.py) |
14 | Versioned OpenAPI contract, neutral comparison fields, search validation/re-rank, missing-data and jurisdiction coverage, mocked postcodes.io. |
Two CI steps are gates rather than counts, and they are the two worth reading
ci.yml for:
- A negative test that proves the scoring contract still refuses bad input.
One step runs
dbt compile --select rpt_neighbourhood_score --vars '{score_weight_affordability: 6}'and fails the build if that command succeeds. Scoring weights are bounded 0.0–5.0 byallowed_weightincontracts/neighbourhood-scoring-v2.json, enforced at compile time by thevalidated_score_weightmacro, so a weight of 6 must raise a compilation error. A green test suite only proves valid weights work; this step proves invalid ones are rejected, so the guard cannot rot unnoticed. - sqlfluff lint over
models/is a hard merge gate, not an advisory job. A style violation in model SQL fails the same requiredbuild-and-testcheck as a failing data test.
- Explain trade-offs; never hide behind one opaque score.
- No "safe"/"unsafe" labels — measured indicators and caveats only.
- No red-to-green good/bad colouring of scores; the EPC A–G bands are the one official exception.
- Official and open data first; no portal scraping. Listing comparison is user-entered.
- Area-level guidance over individual-property claims unless the source supports it.
- Missing data lowers evidence quality — it never silently becomes a zero.
The project is complete as a portfolio reference implementation and has no active
feature roadmap. Maintenance is limited to source breakages, security and
dependency updates, correctness defects, and documentation that keeps published
claims aligned with the implementation. The Streamlit MVP and Expo mobile client
remain parked. See MAINTENANCE.md for the acceptance policy.
All sources are public and used under the
Open Government Licence v3.0
(OpenStreetMap under the Open Database Licence). Per-source access and prep
commands are catalogued in
HOUSING_DECISION_SUPPORT_DATA_SOURCES.md.
- HM Land Registry Price Paid Data — sale-market context. Contains HM Land Registry data © Crown copyright and database right.
- ONS Postcode Directory — postcode → MSOA/LSOA/LA/region geography and name lookups. Contains OS, ONS, Royal Mail and NRS data © Crown copyright and database right.
- ONS Price Index of Private Rents — local-authority rent (incl. per bedroom). Values may be provisional and revised.
- Energy Performance Certificates — per-area median EPC band (23.5M certificates). Certificates may be expired or superseded.
- Police street-level crime — approximate monthly crime rate per 1,000 (LSOA→MSOA), as an indicator only. Locations are snapped to an anonymisation grid before publication and force geocoding accuracy is stated by the publisher as ranging from 60% to 97%.
- ONS mid-year population estimates — compatible mid-2024 MSOA population denominator for the recorded-crime rate.
- Planning Data Platform — per-MSOA planning-constraint count via spatial point-in-polygon.
- Environment Agency flood-risk zones — per-MSOA flood-risk band from postcode intersections, distributed through the Planning Data Platform.
- OpenStreetMap (via Geofabrik) — nearest-amenity + walkable count. © OpenStreetMap contributors, Open Database Licence.
MIT.