Worked example · Independent demo · Designed for Microsoft Fabric
Five public sources, one checked view of a healthcare charity
A healthcare provider's ratings sit with CQC, its NHS waiting lists with NHS England, its sites in the NHS Organisation Data Service and, because it is a registered charity, its accounts with the Charity Commission. Each keys its records differently: a charity number, a CQC location ID, an NHS site code, a postcode. We built a lakehouse, designed for Microsoft Fabric, that joins them, tests itself on every run, and answers the kind of questions a board might ask. The organisation is not named: its identifiers are masked, hospitals are given letters, money is shown as an index or a share of income, and NHS volumes are rounded.
The problem
The hard part isn't the chart. It's knowing which records are the same hospital.
A board, a commissioner or a funder asks simple questions. The answers sit with different public bodies, in different formats, on different schedules, keyed in different ways.
One hospital, five keys
One of the charity's hospitals
Values masked; each source's format shown instead.
Almost a third of the CQC hospital and diagnostic records carry no NHS site code, so the lakehouse falls back to a postcode that is unique on both sides, accepts it only if the two names share a distinctive word, and records the method on every match.
How much NHS work does the charity do, and how long are those patients waiting?
NHS England publishes one national file a month covering every provider. Thirteen months is 2.4 million rows.
NHS England RTT · keyed by site codeAre its hospitals rated well, and how current are those ratings?
Two of CQC's large spreadsheets cover every active regulated service in England.
CQC care directory · keyed by location IDHow have its income, spending and staffing moved?
Annual returns and register details for every registered charity, from 7 of the register extract's 13 tables.
Charity Commission · keyed by charity numberWhere are its sites, and who lives around them?
Site lists, postcode lookups and the 2025 deprivation index for 33,755 neighbourhoods.
NHS ODS, postcodes.io, IMD 2025 · keyed by postcodeJoining these sources by hand is slow and hard to repeat. The aim here was a tested refresh that anyone can rerun and audit from the landed files, built to be scheduled once the download step is added.
Architecture
A medallion lakehouse, sized for the smallest Fabric capacity
Microsoft recommends the medallion design for Fabric. This build uses its first pattern, one lakehouse per layer, with Delta tables throughout and a Direct Lake semantic model designed on gold. Each processing step below is a notebook in the handover pack.
Sources
5 public sources
- Charity Commission register extract, 7 of 13 tables
- CQC ratings and active locations
- NHS England RTT, 13 monthly files
- NHS ODS provider and site lists
- IMD 2025 and postcodes.io
Ingest
Data pipeline
- Copy activities over HTTP into a dated landing folder
- A manifest records each file's URL, time, bytes and SHA-256
- nb_01 stops the run if any file fails its checksum
- In this demo a download script did this step and wrote the manifest; the pipeline is a draft outline
OneLake (local folders in this demo)
Three lakehouses
Serve
Direct Lake model
- Star schema: 4 dimensions, 7 facts, plus check results
- Medians computed from week bands in DAX
- Report pages for each finding, plus a "can I trust this?" page
- Specified in the pack; not yet built in a tenant
Python notebooks, not Spark
The download is about 220 MB (about 1.9 GB unzipped). Fabric's Python notebooks run on a single node (2 vCores and 16 GB by default) with DuckDB, Polars and delta-rs preinstalled. We expect that to be enough for this data, but have not yet measured it in Fabric.
Gold well inside F2 limits
The largest gold table is under a quarter of a million rows. Direct Lake on the smallest capacity, F2, allows 300 million rows per table.
Join on registered numbers, not names
CQC records carry the charity number, so the link to the register is exact.
Keep closed sites, flag them
Fifteen of the charity's site codes are marked closed in ODS. They stay in gold for history and are filtered out of current views.
Findings
What the joined-up view shows
Every number below is generated by the notebooks from the landed public files, through the gold tables unless the text names another source. Headings and comparisons were written from those numbers. In a Fabric deployment these charts would be pages of the Power BI report.
Income up by half in four years, with spending slightly ahead
Incomplete RTT pathways at month end, as reported. England is rebuilt from the provider files and matches NHS England's published series (excluding its estimates for trusts that did not submit) within 0.5% (check SV-06). Sites with missing or partial months are listed in check GD-08.
The headline matches England. The specialties don't.
A comparison like this starts a conversation rather than settling one: referral mix and case complexity differ between providers. A report built from the model spec would let you drill into each hospital.
None of the charity's hospitals has an overall CQC rating below Good
Hospitals are mostly in less deprived areas. Outreach sites are more mixed.
Deprivation is for the neighbourhood (LSOA) each site is in, not for the people it serves.
English Indices of Deprivation 2025, decile 1 = most deprived 10% of neighbourhoods. One dot per site.
Data quality
Names are not keys
Filed answers can disagree
Gaps look like trends
| Check | Rule | Result | Rows | Status |
|---|
What's handed over
Designed to run in your tenant, and yours to keep
This is how KYLANTIC would hand over a client build: in your Fabric workspace, on your data, with documented code and an exit that doesn't depend on us. Below is what exists for this demo today.
The pack
- Six notebooks: config, bronze, silver, gold, quality, export. Plain Python and SQL, commented rule by rule, with .ipynb copies for import (not yet run in Fabric).
- Run script that rebuilds everything from the landed files, times each notebook and stops on a failed check.
- Pipeline outline (draft) for the scheduled downloads and notebook runs, with its source list.
- Semantic model spec (draft): tables, relationships and DAX measures, including medians from week bands.
- Check catalogue: what each rule tests, what it found on this run, and whether it blocks the refresh.
- Runbook: how to rerun a past month, add a source, or retire one.
Status of this demo
The charity has not seen or reviewed this work. Figures are calculated from each source's published data and have not been audited.
Questions about the method or the code: info@kylantic.co.uk
Sources and licences
Open data, credited
| Dataset | Publisher | Edition used | Licence |
|---|---|---|---|
| Register of charities, full extract (7 tables) | Charity Commission for England and Wales | Open Government Licence v3.0 | |
| Care directory with ratings; care directory with filters | Care Quality Commission | Open Government Licence v3.0. Contains CQC information. | |
| Consultant-led referral to treatment waiting times, full CSV extracts and overview time series | NHS England | Open Government Licence v3.0 (RTT data page footer) | |
| Organisation Data Service: ORD API, independent sector providers and sites | NHS England (ODS) | No licence wording on the pages or API responses used; the site links to NHS England terms and conditions | |
| English Indices of Deprivation 2025, File 7 (LSOA level) | Ministry of Housing, Communities and Local Government | Published 30 Oct 2025, updated 17 Nov 2025 | Open Government Licence v3.0 |
| Postcode lookups (51 postcodes) | postcodes.io (ONS Postcode Directory and OS data) | Service under MIT Licence; data under the OS OpenData licence (postcodes.io licences page) |
Contains public sector information licensed under the Open Government Licence v3.0. Contains CQC information. Contains Ordnance Survey data © Crown copyright and database right 2025. Contains Royal Mail data © Royal Mail copyright and database right 2025. Contains National Statistics data © Crown copyright and database right 2025.
The organisation the public data describes is not named on this page. Its identifiers are masked, hospitals the text refers to are given letters, money is shown as an index or a share of income, and NHS pathway and waiting-list volumes are rounded to the nearest hundred. No content, logos or non-public data from the organisation are used. Every file used is recorded with its download URL and SHA-256 in a run manifest kept with the code.