Metric definitions as code

Ask three reports for the outpatient DNA rate and you can get three answers from the same appointments. In my outpatient analytics pipeline, built on synthetic data, the board pack says 8.2%, the specialty dashboard says 9.7%, and the agreed definition says 9.4%. Each one is correct arithmetic. What’s missing is a definition written down in one place, in a form the code has to use.

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.

This article covers why the rates drift apart, what a definition has to pin down, and how I keep one definition in a YAML file that compiles to SQL for every mart that reports it.

Why teams end up with three DNA rates

Nobody decides to have three DNA rates. A performance analyst writes the board pack query. A year later a specialty asks for a dashboard, and another analyst writes a new query, or copies the old one and changes a filter for a good reason. Someone else builds a spreadsheet from the raw extract because it was quicker. Each definition lives inside the thing that uses it: a SQL view, a DAX measure, a spreadsheet formula. Nothing forces them to agree, so they don’t.

The choices behind each version are all defensible, and they are not small. On one set of cleaned appointments, changing one choice at a time:

Data table

Definition	DNA rate
First appointments only	10.8%
Outcomes as they stood in the monthly extract	9.8%
Agreed: DNA over attended plus DNA	9.4%
Patient cancellations added to the denominator	8.6%
Follow-ups only	8.6%
All booked appointments as the denominator	8.2%

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

That’s a spread from 8.2% to 10.8%, about a third of the lower figure, with no data errors at all. Add the data handling problems (re-sent extract rows, legacy codes nobody mapped, outcomes corrected after the extract) and you get the dashboard’s 9.7%.

What a definition has to pin down

A metric name is not a definition. “DNA rate” leaves at least six questions open, and each of the variants above answers one of them differently.

Numerator. What counts as a DNA? Most cases are obvious. The edge cases are where reports split: a patient who arrived too late to be seen, or a same-day cancellation by phone. My definition counts arrived-late-not-seen as a DNA and says so.

Denominator. DNAs out of what? Out of appointments that went ahead as booked (attended plus DNA), or out of everything booked, cancellations included? The first answers “when we expected someone, how often did they not come?”. The second dilutes the rate with appointments that were never going to happen. In the project the difference is 9.4% against 8.2%.

Exclusions. What never enters either part: training patients booked on the live system, duplicate rows from re-sent extracts, appointments with no outcome recorded yet. Write them in words, next to the filter that implements them, so a reader doesn’t have to reverse-engineer a WHERE clause.

Grain. Which table is it counted on, and what is one row? DNA rate is counted on appointments. Slot utilisation is counted on clinic sessions, because its denominator is slots, which only exist at session level. Count a session metric on appointment rows and every appointment repeats its session’s slot count.

Time attribution. Which date puts a row in a month? The appointment date, the date the outcome was keyed, or the extract it arrived in? And as at when? Using outcomes as they stood in each monthly extract gives 9.8%, because DNAs later corrected to attended are still counted as DNAs. The agreed definition uses the appointment date and the latest known outcome, and every monthly figure is restated when late corrections arrive. That’s a choice too, and it should be written down.

Population. A DNA rate for first appointments only (10.8%) is a legitimate metric. It’s a different metric, and it needs a different name.

Writing it down as YAML

In the project, every ratio metric lives in metrics.yml. Each entry names its table, grain and time column, and defines the numerator and denominator as an aggregation with a filter:

dna_rate:
  label: DNA rate
  description: >
    Share of appointments that should have gone ahead where the patient did not
    attend and gave no notice.
  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.
    - Appointments with no outcome recorded yet are in neither part.
    - Arrived late and could not be seen (legacy code 7) counts as a DNA.
    - Training patients and re-sent extract rows never reach fct_appointments.

A metric on a different grain uses a sum instead of a count:

slot_utilisation:
  label: Slot utilisation
  model: fct_clinic_sessions
  grain: one planned clinic session
  time: session_date
  numerator:
    agg: sum
    column: attended
    where: session_status = 'HELD'
  denominator:
    agg: sum
    column: slots_available
    where: session_status = 'HELD'

The format is deliberately small. Five metrics fit on two screens, and a service manager can review a change to one in a pull request without reading SQL.

Compiling to SQL

A mart asks for a 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 replaces the placeholder with three columns:

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

The loader and compiler come to just over 100 lines of Python, worth packaging as a library once a second project needs them. Three details do most of the work.

Store the parts, not just the ratio. Every mart carries the numerator and the denominator. Any roll-up, from weekday to specialty or from clinic to trust, is the sum of numerators over the sum of denominators. An average of rates weights a clinic with 40 appointments the same as one with 4,000, and it’s the most common way a correct metric becomes a wrong total.

Take time from the definition. A monthly mart asks for {{ metric_time('dna_rate', 'month') }} rather than writing its own date logic, so the month always comes from the metric’s own time column. Nobody can quietly group DNAs by extract month.

Refuse the wrong table. The compiler checks that a model using a metric selects from the metric’s table. Asking for dna_rate in a model built on clinic sessions fails the build:

metric 'dna_rate' is defined on fct_appointments, which this model does not select from

Then test the result like any other model. The project reconciles the marts back to the fact table: DNAs summed across the specialty-by-weekday mart must equal the DNAs in fct_appointments. See data tests that catch real problems for the rest of that suite.

How this maps to semantic layers

A YAML file and a small compiler are a lightweight version of what a semantic layer does. The ideas carry over directly.

dbt’s Semantic Layer, built on MetricFlow, defines metrics in YAML alongside the models. It has a ratio metric type with a numerator and a denominator, and each side can carry its own filter. Metrics are computed at query time for whatever grouping the user asks for, so the parts are aggregated before the division without anyone having to remember to do it. If your warehouse and BI tools are already on dbt, that’s where these definitions belong.

A Power BI semantic model does the same job with DAX measures. Define the parts as measures and the rate as a division of them:

DNA Count :=
CALCULATE ( COUNTROWS ( Appointments ), Appointments[Attendance Outcome] = "DNA" )

Attended or DNA :=
CALCULATE (
    COUNTROWS ( Appointments ),
    Appointments[Attendance Outcome] IN { "ATTENDED", "DNA" }
)

DNA Rate := DIVIDE ( [DNA Count], [Attended or DNA] )

Because a measure is evaluated in the filter context of each visual, DNA Rate on a specialty total is recalculated from the two counts, not averaged from the rows below it. Publish one semantic model and point every report at it, and the definition exists once. Put the exclusions in the measure description, where report builders will see them. The failure mode is the same as with SQL: a report author writes a new DNA Rate v2 measure in a local model instead of using the shared one.

Whichever tool computes the numbers, I keep a plain-language spec like metrics.yml under version control: numerator, denominator, exclusions, grain, time. It is the thing people argue about and sign off, and the SQL or DAX is its implementation.

A checklist for a new metric

Before a new rate goes on a dashboard, I want answers to these, in writing:

1. What exactly is counted on top, including the edge cases?
2. What is it divided by, and why that denominator?
3. What is excluded from both parts, and why?
4. Which table is it counted on, and what is one row?
5. Which date puts a row in a period, and are past periods restated when late data arrives?
6. Is this the same population as the metric people will compare it with? If not, what’s its different name?
7. Where do the numerator and denominator live, so roll-ups are sums?
8. Which test proves the mart adds back to the fact table?

If a request for “the DNA rate” can’t answer all eight, it isn’t a definition yet. It’s a name.