Outpatient analytics pipeline

An outpatient department asks one question, “what is our DNA rate?”, and gets three answers. This project builds the pipeline that makes the answer boring: staged, tested SQL models over a messy extract, and one metric definition that every mart compiles from.

It is plain SQL on SQLite with a small Python runner in the style of dbt, so anyone can run it with one command and no warehouse. The data is synthetic: two years of outpatient appointments shaped like a hospital patient administration system (PAS) feed, with problems planted on purpose so the tests have something real to find.

uv run python projects/outpatient-analytics-pipeline/run.py

That generates the extracts, builds a naive first version and the fixed pipeline, runs the same 28 data tests against both, and writes every chart and number on this page. It takes about 15 seconds.

The problem: three reports, three DNA rates

Three reports, all built from the same appointments, all labelled “DNA rate”:

None of them is wrong arithmetic. The gap between A and C is a definition: whether a cancelled appointment belongs in the denominator. The gap between B and C is data handling: B counts re-sent rows twice, misses numeric legacy codes, and never sees the outcomes corrected after the extract. The three lines move together, and in none of the 24 months do they agree.

Data table

Month	A: DNAs over all booked	B: raw extract as received	C: agreed definition
2024-09-01	10.2%	12.3%	11.5%
2024-10-01	8.2%	9.8%	9.3%
2024-11-01	9.2%	11.4%	10.5%
2024-12-01	10.1%	11.9%	12.1%
2025-01-01	9.9%	11.5%	11.2%
2025-02-01	9.4%	11.0%	10.7%
2025-03-01	9.7%	11.2%	10.9%
2025-04-01	9.0%	10.4%	10.3%
2025-05-01	9.4%	11.2%	10.7%
2025-06-01	7.2%	8.6%	8.3%
2025-07-01	7.7%	9.2%	8.8%
2025-08-01	7.9%	9.6%	9.3%
2025-09-01	7.1%	8.5%	8.0%
2025-10-01	7.8%	9.3%	8.7%
2025-11-01	7.2%	8.7%	8.3%
2025-12-01	8.2%	10.2%	9.9%
2026-01-01	7.9%	9.3%	8.9%
2026-02-01	7.4%	9.0%	8.3%
2026-03-01	7.5%	8.9%	8.6%
2026-04-01	7.5%	8.9%	8.4%
2026-05-01	7.1%	8.5%	8.0%
2026-06-01	7.3%	8.7%	8.2%
2026-07-01	7.3%	8.8%	8.3%
2026-08-01	7.7%	9.7%	9.4%

Synthetic data. Source: projects/outpatient-analytics-pipeline.

Arguing about which report is right misses the point. The fix is a pipeline where the data problems are caught by tests and the definition lives in one place.

The source mess

The generator writes five files: a monthly appointment extract, a feed of outcome changes keyed after the extract, a clinic lookup, a specialty lookup and a list of planned clinic sessions with slot counts. The extract has 156,369 rows covering 148,763 appointments between September 2024 and August 2026, across ten specialties and 40 clinic codes.

What is planted in it:

On top of that, outcome codes come from three screens (Did Not Attend, dna, 3), and first and follow-up flags come in three schemes (N/F, New/FU/F/U in any case, 1/2). The README in the project code lists every planted pattern.

Pipeline design and lineage

The layers follow the usual convention. Staging models have one job each: make one source trustworthy, with types, codes and duplicates handled. Their only joins are to the code-mapping seeds. Intermediate models combine sources. Marts hold two fact tables at a stated grain and the reporting tables built on them.

Each model is one SELECT in a .sql file. A model declares its inputs with placeholders, {{ source('appointments') }} and {{ ref('stg_appointments') }}, and the runner reads those to build the dependency graph, then materialises each model as a table in topological order using Python’s graphlib. A cycle stops the build with the path:

pipeline error: dependency cycle: fct_appointments -> int_appointments_enriched -> fct_appointments

The lineage of the fixed pipeline:

raw_appointments ─────► stg_appointments ───┬──► int_appointment_status ──┐
raw_status_updates ───► stg_status_updates ─┘                             ├──► int_appointments_enriched
raw_clinics ──────────► stg_clinics ────────┬──► int_clinics ─────────────┘
                        stg_appointments ───┘

int_appointments_enriched ──┬──► fct_appointments ─────┬──► mart_dna_by_specialty_weekday
stg_specialties ────────────┘                          ├──► mart_dna_by_month
                                                       ├──► mart_new_to_follow_up_by_specialty
                                                       ├──► mart_lead_time_distribution
                                                       └──► mart_lead_time_by_specialty

raw_clinic_sessions ──► stg_clinic_sessions ──┐
fct_appointments ─────────────────────────────┤
int_clinics ──────────────────────────────────┼──► fct_clinic_sessions ──┬──► mart_utilisation_by_clinic
stg_specialties ──────────────────────────────┘                          └──► mart_utilisation_by_specialty

Seeds: attendance_status_map feeds both staging models of outcomes; visit_type_map feeds stg_appointments.

Two models carry most of the fixes. Staging keeps the latest copy of each appointment and maps codes through seed files rather than CASE statements, so a new code is a one-line change a service manager can review:

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY appointment_id
            ORDER BY extracted_at DESC
        ) AS copy_rank
    FROM {{ source('appointments') }}
)

SELECT
    r.appointment_id,
    UPPER(TRIM(r.clinic_code)) AS clinic_code,
    NULLIF(TRIM(r.specialty_code), '') AS specialty_code,
    CASE
        WHEN TRIM(r.attendance_status) = '' THEN 'NOT_RECORDED'
        ELSE COALESCE(s.attendance_outcome, 'UNMAPPED: ' || r.attendance_status)
    END AS attendance_outcome,
    r.extracted_at AS status_as_at
    -- other columns omitted
FROM ranked AS r
LEFT JOIN {{ ref('attendance_status_map') }} AS s
    ON s.raw_code = UPPER(TRIM(r.attendance_status))
WHERE r.copy_rank = 1
  AND r.patient_id NOT LIKE 'ZZ%'  -- training patients

An unknown code becomes UNMAPPED: <code> rather than NULL, so the accepted-values test names it instead of the value quietly vanishing. The intermediate model int_appointment_status then stacks the extract row and every later update and keeps whichever came last, so a DNA corrected to attended a fortnight later counts as attended. SQL window functions for patient pathways works through this keep-the-latest pattern with ROW_NUMBER, and the ways it goes wrong.

Tests and what they caught

The tests live in tests.yml, laid out like a dbt properties file: not_null, unique, accepted_values and relationships on columns, an expression_is_true check that bookings fit in the slots available, a freshness check on the extract, two singular SQL tests, and three reconciliations. Severity is error unless marked warn.

- name: fct_appointments
  columns:
    - name: clinic_code
      data_tests:
        - relationships:
            arguments: {to: ref('stg_clinics'), field: clinic_code}
            config: {severity: warn}  # the lookup owner's to-do list, not a pipeline fault

I wrote the naive version first, the way a first pass usually goes: read the extract as if it were clean, map the codes the main screen uses, inner join to the clinic lookup. Then I ran the same 28 tests against both builds.

The tests that reported on either build (the other 16 pass on both):

Numbers are failing rows (months, for the volume check), except the reconciliation, where it is the size of the gap. What each failure meant, and the fix:

• Re-sent extracts broke uniqueness in staging (7,606 appointment IDs on 15,212 rows), pushed 424 clinic sessions over their slot count, and tripped the monthly volume check for March 2025, the month re-sent in full, and for the three months after it, whose baseline March had inflated. Fix in staging: keep the latest copy by extracted_at. The source-level unique test stays at warn on both builds, because re-sends are normal and staging is where they are resolved.
• Outcome codes from three screens left 11 unexpected values on 24,393 rows, led by the legacy code 5 on 11,137 rows. Fix in staging: map through a seed.
• Visit flags were NULL on 58,642 rows. accepted_values passed, because like dbt’s version it ignores NULLs; not_null caught it. Fix in staging: a second seed.
• Blank specialty codes passed not_null, because an empty string is not NULL, and failed relationships on 3,852 rows. Fix: NULLIF(TRIM(...), '') in staging, then fill from the clinic lookup.
• Late and corrected outcomes. Blanks showed up among the unexpected values above, but outcomes later corrected were invisible to every column test: a stale DNA is a valid code. The singular test that compares the fact table with the status feed found 3,383 appointments behind their latest update. Fix in intermediate: latest event wins.
• The inner join to the clinic lookup dropped 6,027 rows, and the relationships test on clinic code passed, because the orphans were already gone. Only the reconciliation noticed, and even then the signal was muted. The build should have dropped 7,766 rows (re-sent copies and training patients). The join dropped 6,027, of which only 227 were among them; the other 5,800 were real appointments at clinics with badly typed or missing codes. Extra rows and lost rows largely cancelled, leaving a net gap of 1,739, about 1%.

That last one is the argument for reconciliations. The fixed build accounts for every row:

The fixed build still reports three warnings, and they are correct. The source still contains re-sent rows, which staging resolves. Three clinics (ORTH05, DERM05, RESP04) are missing from the lookup, so the pipeline keeps their 4,094 appointments, takes each clinic’s specialty from its own appointment rows, and flags them for the lookup owner. And 201 appointments still have no outcome recorded, which is a job for the clinics rather than the pipeline.

Metric definitions

Every rate is defined once in metrics.yml: the table it is counted on, the grain, the date it is attributed to, and a numerator and denominator, each an aggregation with a filter. Exclusions are written down in words next to the filters that implement them.

dna_rate:
  label: DNA rate
  model: fct_appointments
  grain: one appointment
  time: appointment_date
  numerator:
    agg: count
    where: attendance_outcome = 'DNA'
  denominator:
    agg: count
    where: attendance_outcome IN ('ATTENDED', 'DNA')
  exclusions:
    - Cancellations by the patient or the hospital are in neither part.
    - Arrived late and could not be seen (legacy code 7) counts as a DNA.

A mart asks for the metric by name and chooses only the grouping:

SELECT
    specialty_name,
    weekday_name,
    visit_type,
    {{ metric('dna_rate') }}
FROM {{ ref('fct_appointments') }}
GROUP BY specialty_name, weekday_name, visit_type

The runner compiles the placeholder into three columns, the numerator, the denominator and the ratio:

SUM(CASE WHEN attendance_outcome = 'DNA' THEN 1 ELSE 0 END) AS dna_rate_numerator,
SUM(CASE WHEN attendance_outcome IN ('ATTENDED', 'DNA') THEN 1 ELSE 0 END) AS dna_rate_denominator,
1.0 * SUM(CASE WHEN attendance_outcome = 'DNA' THEN 1 ELSE 0 END)
    / NULLIF(SUM(CASE WHEN attendance_outcome IN ('ATTENDED', 'DNA') THEN 1 ELSE 0 END), 0) AS dna_rate

Keeping the parts is what makes roll-ups safe: a specialty total is the sum of numerators over the sum of denominators, never an average of weekday rates. The compiler also refuses a metric on the wrong table. Asking for dna_rate in a model that selects from fct_clinic_sessions fails with metric 'dna_rate' is defined on fct_appointments, which this model does not select from.

The five metrics are DNA rate, hospital and patient cancellation rates, slot utilisation (attended appointments over slots in sessions that went ahead) and the new-to-follow-up ratio (follow-up attendances per first attendance).

Results

Over the two years, 12,188 of 129,670 appointments that went ahead as booked were DNAs: 9.4%. First appointments ran higher than follow-ups, 10.8% against 8.6%. Mondays (10.2%) and Fridays (10.7%) were worse than midweek (8.7% to 8.9%).

Data table

Weekday	All appointments	First appointments	Follow-ups
Monday	10.2%	11.6%	9.4%
Tuesday	8.7%	10.0%	7.9%
Wednesday	8.9%	10.6%	7.9%
Thursday	8.8%	10.0%	8.1%
Friday	10.7%	12.3%	9.7%

Synthetic data. Source: projects/outpatient-analytics-pipeline.

By month, the rate peaks in December and falls from 10.8% before June 2025 to 8.6% after it. That step is planted (the generator cuts DNA odds by about a fifth when text reminders start), so treat it as a check that the marts can show a real change, not as evidence about reminders. That evidence needs a randomised trial, like the appointment reminder experiment.

Data table

Month	All appointments	First appointments	Follow-ups
2024-09-01	11.5%	12.9%	10.8%
2024-10-01	9.3%	10.7%	8.6%
2024-11-01	10.5%	12.0%	9.6%
2024-12-01	12.1%	14.4%	10.7%
2025-01-01	11.2%	12.4%	10.5%
2025-02-01	10.7%	12.0%	10.0%
2025-03-01	10.9%	11.9%	10.2%
2025-04-01	10.3%	11.7%	9.5%
2025-05-01	10.7%	11.5%	10.2%
2025-06-01	8.3%	9.5%	7.5%
2025-07-01	8.8%	10.8%	7.7%
2025-08-01	9.3%	10.9%	8.4%
2025-09-01	8.0%	9.1%	7.4%
2025-10-01	8.7%	10.4%	7.8%
2025-11-01	8.3%	9.8%	7.4%
2025-12-01	9.9%	10.8%	9.4%
2026-01-01	8.9%	10.2%	8.1%
2026-02-01	8.3%	10.5%	6.9%
2026-03-01	8.6%	10.1%	7.7%
2026-04-01	8.4%	9.9%	7.6%
2026-05-01	8.0%	10.0%	6.8%
2026-06-01	8.2%	9.1%	7.7%
2026-07-01	8.3%	9.5%	7.6%
2026-08-01	9.4%	11.0%	8.5%

Synthetic data. Source: projects/outpatient-analytics-pipeline.

Slot utilisation was 82.3% across 40 clinics: of the slots in sessions that went ahead, that share went to a patient who was seen. The hospital cancelled 418 of 10,667 planned sessions. Hospital cancellations were 5.0% of booked appointments and patient cancellations 7.6%.

Data table

Specialty	Slot utilisation
Ophthalmology	87.8%
Dermatology	86.4%
Trauma and orthopaedics	85.4%
Cardiology	84.2%
Urology	82.3%
Ear, nose and throat	82.0%
Gynaecology	80.6%
General surgery	80.1%
Gastroenterology	77.8%
Respiratory medicine	77.6%

Synthetic data. Source: projects/outpatient-analytics-pipeline.

The median wait from referral to an attended first appointment was 11.7 weeks, with a long right tail: one in ten waited more than 23.9 weeks.

Data table

Weeks from referral	All specialties
0-2	0.0%
2-4	2.3%
4-6	8.4%
6-8	13.4%
8-10	14.2%
10-12	12.9%
12-14	11.1%
14-16	8.8%
16-18	6.8%
18-20	5.1%
20-22	4.0%
22-24	3.0%
24-26	2.3%
26-28	1.8%
28-30	1.3%
30+	4.4%

Synthetic data. Source: projects/outpatient-analytics-pipeline.

The two specialties with the highest DNA rates, gastroenterology and respiratory medicine, also have the lowest utilisation. That follows from the definition: a DNA is a booked slot nobody used.

Limits

• The data is synthetic and the patterns are planted. The weekday, seasonal, specialty and reminder effects are there because the generator put them there. The pipeline shows them faithfully; it does not discover them.
• Tests check shape, not truth. No column test caught the stale outcomes. The singular test exists because I knew the status feed existed. A pipeline test suite is only as good as the analyst’s knowledge of the source.
• The runner is a teaching tool. It rebuilds everything each run, has no incremental models, snapshots, parallelism or documentation site, and SQLite has none of a warehouse’s access control.
• Some choices are local rules, written down. Code 7 (arrived late, not seen) counts as a DNA; utilisation counts attended slots, not booked ones; a clinic missing from the lookup takes the specialty most often recorded on its own appointments until someone fixes the lookup.
• Lead time is referral to first attended appointment. It is not a referral-to-treatment wait and should not be compared with one.
• Freshness is checked against a fixed date (14 September 2026) so the run is reproducible. In production it would be checked against the time of the run.

What next

• Move the same SQL to dbt on a warehouse. The models already use ref() and source(), though dbt’s source() takes a source name and a table name. Seeds move to seeds/ and the singular tests to tests/. Most column tests carry over as written; freshness becomes a source setting, and expression_is_true comes from the dbt_utils package. metrics.yml would be rewritten for whichever semantic layer the reporting tool reads.
• Load the extract incrementally and snapshot the clinic lookup, so a clinic that changes specialty keeps its history.
• Store test failures as tables and send the warnings to the people who can act on them: unmapped clinics to the booking team, unrecorded outcomes to the clinics.
• Add distribution tests: monthly volumes and DNA rates checked against their own recent history, so a partial extract or a new code that maps to the wrong outcome shows up as a shift before anyone reads the dashboard.