Five steps, about six minutes

The same sandbox as the live demo, in the order a Trust data team usually wants to see it. Prefer to poke at it yourself? Open the free-form sandbox.

Canonical schema, generated as code

The physical schema is not hand-modelled. DDL is generated from the published NHS Canonical Data Model definition, so table names, column types, keys and value sets are deterministic and reproducible across every deployment.

Generated PostgreSQL DDL: 3NF core, conformed dimensions, slowly-changing type-2 history on reference entities
Deterministic surrogate keys plus natural keys (NHS number, MRN, ODS code) retained on every fact table
Domain packages are additive — a new domain ships as new models and tests, no destructive migration to live tables
Migrations are forward-only and version-pinned to a CDM release tag

Store: PostgreSQL 16 (Aurora-compatible)
Modelling: dbt Core, SQL only
Schema source: NHS CDM definition → generated DDL
Versioning: Git tag per CDM release

1 / 5

Reference artefactstep 01

-- generated, do not edit by hand
create table cdm.encounter (
  encounter_sk      bigint primary key,
  encounter_id      text not null,          -- natural key from PAS
  patient_sk        bigint not null references cdm.patient,
  ods_site_code     text not null,
  admission_dttm    timestamptz not null,
  discharge_dttm    timestamptz,
  admission_method  text not null,          -- value set: NHSDD ADMISSION METHOD
  _loaded_at        timestamptz not null default now(),
  _source_system    text not null
);

Tables in the model

Columns generated

~1,600

Data-quality tests

400

Clinical domains

patient

Patient index

Identifiable

1.02m rows

Master patient index conformed from PAS. Demographics, registration and death notification, with a stable surrogate key used across every clinical domain.

episode_of_care · outpatient_appointment · emergency_care_attendance · rtt_pathway

Column	Type	Key	Notes
patient_sk	bigint	PK
nhs_number	char(10)		restricted to the identifiable visibility group
local_patient_identifier	varchar(20)
birth_date	date
death_date	date		nullable
gender_code	varchar(2)	FK
ethnic_category_code	varchar(2)	FK
lsoa_2021_code	varchar(9)	FK
registered_gp_practice_code	varchar(6)	FK
record_effective_from	timestamptz
record_effective_to	timestamptz		nullable

A documentation-grade subset of the canonical model is shown here. The generator, the full 72-table DDL and the dbt test project are released to authorised evaluators only.

Next step

See it before you commit to anything

Open the synthetic sandbox to see the canonical model, the FHIR API and the pathway analytics working — or request evaluation access to the reference build itself.

Open the live sandbox Request evaluation access