Skip to content

Discussion: FHIR-native data quality checks via SQLQuery Library #375

Open
Open
Discussion: FHIR-native data quality checks via SQLQuery Library#375

Description

@niquola
niquola
opened on Jul 7, 2026

FHIR-native Data Quality & Observability

A proposal sketch for the SQL on FHIR community — how to express data-quality checks on top of the primitives we already have (ViewDefinition, SQLQuery Library, and query-to-query dependencies).

Compiled by Claude, working with @niquola, as background for discussion. Feedback very welcome.

TL;DR

  1. A data-quality (DQ) check is just a query that returns the "bad" rows. 0 rows = pass, any rows = fail. That's the whole idea (this is exactly how dbt tests work).
  2. In SQL on FHIR that means: a ViewDefinition is the "table", and an SQLQuery Library is the "check" over it. No new resource, no new operation — reuse $sqlquery-run.
  3. The industry already agrees on a taxonomy (the Kahn framework: Conformance / Completeness / Plausibility) and even on a result format (OpenLineage's data-quality facets). We can reuse both instead of inventing our own.
  4. It's happening in FHIR already: the NCQA Bulk FHIR Quality Coalition grades Bulk FHIR data with the same three Kahn categories.
  5. A DQ check is best modelled as a thin profile of SQLQuery Library — a subtype, not a new thing. The only additions are a result contract and a little metadata (threshold, category, severity).

1. What is a data-quality check?

The simplest possible definition, borrowed from dbt:

A test is a query that returns the rows that break a rule. If it returns nothing, the data is fine. If it returns rows, those rows are the problem.

Everything else — categories, thresholds, dashboards — is just organizing and interpreting that one idea.

2. The Kahn framework, in plain words

Health-data quality has a widely-used taxonomy (Kahn et al.). It sorts every check into three questions and two ways of asking.

Three questions (categories):

  • Conformance — "is the data in the right shape?"
    Not is it true, but is it recorded correctly: right type, allowed value, valid reference, unique key.
    Example: Observation.status must be one of final / amended / …, never "closed".

  • Completeness — "is the data there at all?"
    The shape may be fine, but the value is simply missing.
    Example: 40% of lab observations have no numeric value — you can't analyze that column.

  • Plausibility — "can the data be believed?"
    Shape is fine, value is present — but is it realistic?
    Example: a body weight of 1000 kg, or a medication whose end date is before its start date.

Two ways of asking (contexts):

  • Verification — checked against internal rules / local knowledge, no outside reference ("field not null", "reference resolves", "date ≥ birth"). Most checks are here.
  • Validation — checked against an external benchmark: clinical expectations, other sites, reference distributions ("diabetes prevalence ≈ the known national rate").

3. Types of checks — a beginner's cheat-sheet

A "check type" is a reusable template, not a single query. One not_null template expands across every required field of every resource — that's how ~24 templates become thousands of concrete checks.

Check type Kahn What it asks Plain example
required / not_null Conformance field is filled in Observation.status is empty
data type Conformance value is the expected type "unknown" sits in a numeric field
allowed values Conformance value is from a permitted set status = "closed" (not in the list)
referential integrity Conformance a reference points to something real Observation → Patient/123 that doesn't exist
unique key Conformance an id doesn't repeat two patients share one id
valid concept Conformance code is standard / in the right domain a procedure code sits in a "diagnosis" field
value completeness Completeness how many rows are empty value null in 40% of rows
unmapped codes Completeness source codes fell through to "unmapped" 99% of source values map to 0
person completeness Completeness people with no records at all half the patients have no visits
value range Plausibility value within sane bounds systolic blood pressure = 4000
units Plausibility unit fits the measurement temperature recorded in kilograms
start ≤ end Plausibility dates are consistent prescription ends before it starts
after birth / before death Plausibility event within a lifetime a result dated before the patient was born
within visit dates Plausibility event inside its encounter a test dated outside the hospital stay
gender consistency Plausibility diagnosis doesn't contradict sex pregnancy recorded for a male patient
duplicates Plausibility one real entity appears once the same patient twice
distribution Plausibility statistics look expected diabetes prevalence of 0.1% (should be ~10%)
freshness / recency Timeliness* data is recent enough newest record is 3 weeks old
profiling metrics — (metrics) row count, null %, min/max, quantiles dataset shape numbers for a dashboard

*Timeliness and Accuracy are two dimensions that practice (NCQA, PhUSE) commonly adds on top of the three Kahn categories.

4. How this maps to SQL on FHIR

Two layers, both already in the spec:

DQ concept SQL on FHIR primitive
The flat table (source) ViewDefinition (flatten FHIR → columns)
One check SQLQuery Library over that view
A check template a parameterized / generated SQLQuery
Composing checks; a dashboard roll-up query query-to-query dependencies (relatedArtifact: depends-on)
Schema metadata (required, types, ranges, bindings) the FHIR profile (StructureDefinition)
Threshold / category / severity / result shape a thin DataQualityCheck profile on SQLQuery Library

The one thing worth standardizing early: the result contract

So that a single, generic dashboard query can consume any check, all checks should return a known shape. Two options:

  • dbt-style: the check returns the failing rows (0 = pass). Simplest, gives drill-down.
  • DQD-style: the check returns num_violated / num_denominator (for a percentage vs a threshold).

Suggestion: make the base contract dbt-style (failing rows), and compute the aggregate (num_violated / denominator) with a second query on top via query dependencies. Then the dashboard and the drill-down come from one source.

5. Repackaging Kahn for FHIR: two engines

The Kahn framework was born in the SQL/OMOP world, where everything is SQL over flat tables (so OHDSI's Data Quality Dashboard is one SQL engine). FHIR is different: most Conformance is already done by the validator, not by an analytical query.

Layer What it checks The FHIR engine Kahn
Schema is it valid FHIR at all? base spec (below Kahn)
Profile conformance cardinality, types, ValueSet bindings, invariants FHIR Validator + StructureDefinition Conformance
Completeness must-support present, data density, mapping completeness SQL on FHIR (validators can't count/aggregate) Completeness
Plausibility ranges, units, temporal, distributions, cross-resource SQL on FHIR (+ row-local invariants) Plausibility

So "repackaging Kahn for FHIR" = mapping each category to the engine that implements it best. A big slice of Conformance collapses into profile validation (declarative, already standard); SQL-on-FHIR checks then concentrate on Completeness + Plausibility, exactly where the validator can't help (aggregates, thresholds, cross-record, distributions).

The contexts repackage too: Verification = checks within one dataset (one server); Validation = across datasets / against a benchmark (the same ViewDefinition run on N servers, results compared).

Keep the outward category names (Conformance/Completeness/Plausibility) for interoperability with NCQA, OHDSI, and OpenLineage — the repackaging is about which engine runs what, not about renaming.

6. Worked examples: original → FHIR

A shared source table for all examples — ViewDefinition obs_flat (flatten Observation):

{ "resourceType":"ViewDefinition","name":"obs_flat","resource":"Observation",
  "select":[{"column":[
    {"name":"id",        "path":"getResourceKey()"},
    {"name":"status",    "path":"status"},
    {"name":"patient_id","path":"subject.getReferenceKey(Patient)"},
    {"name":"value",     "path":"value.ofType(Quantity).value"},
    {"name":"unit",      "path":"value.ofType(Quantity).code"},
    {"name":"effective", "path":"effective.ofType(dateTime)"}]}]}

Each check is one SQLQuery Library over that view, returning the failing rows (SQL shown decoded; in the resource it lives base64 in content).

Example 1 — required / not_null (Conformance)

dbt (original):

models:
  - name: obs_flat
    columns:
      - name: status
        data_tests: [not_null]

FHIR — SQLQuery Library:

{ "resourceType":"Library", "id":"dqc-obs-status-not-null",
  "meta":{"profile":["http://sql-on-fhir.org/StructureDefinition/DataQualityCheck"]},
  "type":{"coding":[{"code":"sql-query"}]},
  "extension":[
    {"url":".../dq-kahn-category","valueCode":"conformance"},
    {"url":".../dq-severity","valueCode":"error"},
    {"url":".../dq-threshold","valueDecimal":0.0}],
  "relatedArtifact":[
    {"type":"depends-on","resource":"ViewDefinition/obs_flat","label":"obs"}],
  "content":[{"contentType":"application/sql","data":"<base64>"}]}
-- decoded content: the failing rows
SELECT id, patient_id FROM obs WHERE status IS NULL

Example 2 — allowed values (Conformance)

dbt:

- name: status
  data_tests:
    - accepted_values:
        arguments: { values: ['final','amended','corrected','preliminary'] }

FHIR — decoded SQL:

SELECT id, status FROM obs
WHERE status NOT IN ('final','amended','corrected','preliminary')

FHIR bonus: the allowed list is a required ValueSet binding on Observation.status; ideally we generate the IN (...) from the ValueSet instead of hard-coding it.

Example 3 — referential integrity (Conformance)

Needs a second view patient_flat and a query-to-query dependency:

"relatedArtifact":[
  {"type":"depends-on","resource":"ViewDefinition/obs_flat",     "label":"obs"},
  {"type":"depends-on","resource":"ViewDefinition/patient_flat", "label":"pat"}]
SELECT o.id, o.patient_id
FROM obs o LEFT JOIN pat ON o.patient_id = pat.id
WHERE o.patient_id IS NOT NULL AND pat.id IS NULL

Example 4 — value completeness (Completeness)

SELECT id FROM obs WHERE value IS NULL

Example 5 — plausibility (range + temporal)

-- a human weight over 1000 kg is implausible
SELECT id, value, unit FROM obs WHERE unit = 'kg' AND value > 1000
-- on an Encounter view enc_flat(start, end): end before start
SELECT id FROM enc WHERE "end" < start

Example 6 — profiling metrics (not pass/fail)

Numbers for a dashboard (maps to OpenLineage's metrics facet):

SELECT count(*)                              AS "rowCount",
       count(*) FILTER (WHERE value IS NULL) AS "nullCount_value",
       count(DISTINCT patient_id)            AS "distinctCount_patient",
       min(value) AS "min_value", max(value) AS "max_value"
FROM obs

The check result (contract = OpenLineage assertion facet)

{ "assertion":"not_null", "column":"status",
  "success": false, "actualValue": 12, "expectedValue": 0, "severity":"error" }

The dashboard roll-up (via query dependencies)

"relatedArtifact":[
  {"type":"depends-on","resource":"Library/dqc-obs-status-not-null","label":"c1"},
  {"type":"depends-on","resource":"Library/dqc-obs-status-accepted","label":"c2"},
  {"type":"depends-on","resource":"Library/dqc-obs-value-complete", "label":"c3"}]
SELECT category, count(*) AS checks, sum(failed) AS failed
FROM ( SELECT 'conformance'  category, (SELECT count(*) FROM c1) > 0 failed
       UNION ALL SELECT 'conformance',  (SELECT count(*) FROM c2) > 0
       UNION ALL SELECT 'completeness', (SELECT count(*) FROM c3) > 0 ) t
GROUP BY category

7. What we can borrow (and from where)

From OHDSI Data Quality Dashboard (DQD)

  • The Kahn taxonomy and the "check type" idea (parameterized templates, not thousands of hand-written checks — the parameters come from FHIR profiles instead of CSV files).
  • A threshold → PASS/FAIL model.

From dbt (the closest design match)

  • The result contract: "a check is a query returning failing rows; 0 rows = pass."
  • Severity + two thresholds (severity: warn|error, warn_if / error_if).
  • store_failures — keep the failing rows for drill-down dashboards.
  • where — scoped / conditional checks (e.g. "active patients only").
  • Generic vs singular tests — a reusable template vs a one-off.
  • dbt-expectations — an off-the-shelf catalog of check types we can map directly:
    expect_column_values_to_be_between / _in_set / _match_regex, statistics
    (_mean_ / _stdev_ / _quantile_values_to_be_between), anomaly
    (_within_n_moving_stdevs), recency (expect_row_values_to_have_recent_data),
    multi-column (expect_compound_columns_to_be_unique). Two classes DQD lacks: anomaly
    detection     and recency (recency ties into the timeliness dimension).
  • Data tests vs unit tests — a crucial distinction: quality of the data (checks over real
    resources) vs quality of the view/mapping (sample resources → expected rows). The latter is the
    ViewDefinition test-suite pattern.

From OpenLineage (a ready result + lineage format)

OpenLineage already standardizes how to attach DQ results to a dataset in a lineage graph — and GX, Soda and dbt already emit into it:

  • DataQualityAssertionsDatasetFacet — assertion, column, success, plus actualValue, expectedValue, severity.
  • DataQualityMetricsInputDatasetFacet — rowCount, bytes, per-column nullCount, distinctCount, min, max, quantiles.

Lineage / observability

  • Declared lineage — the dependency graph is stated explicitly. We already get this from
    relatedArtifact: depends-on (ViewDefinition → SQLQuery → SQLQuery). Same as dbt ref().
  • Observed lineage — captured from actual runs (like Unity Catalog's column-level lineage). For us,
    that's what a server could log on each $run / $sqlquery-run (which ViewDefinitions and resources
    were read).

8. This is already happening in FHIR

The NCQA Bulk FHIR Quality Coalition grades Bulk FHIR data quality using exactly the Kahn categories — Conformance (does data match the required format & definitions?), Completeness (is the expected amount and type present?), Plausibility (is it accurate and does it mirror the source?). Their real-world use case is digital HEDIS measurement over US Core + CARIN Blue Button data, validated against NCQA's HEDIS FHIR IGs. Worth coordinating with — they're solving the same problem from the profiles/IG side, while SQL on FHIR could solve it from the query side.

Academic FHIR DQ work (JAMIA, the German MII initiative, a PhUSE FDA paper with 100+ checks) all use Kahn too — the taxonomy is settled.

9. Is a DQ check just a subtype of SQLQuery?

Yes — and that's the point. A DataQualityCheck is a thin profile (subtype) of SQLQuery Library, not a new resource and not a new operation. It runs on the same $sqlquery-run. The only things that turn a "query" into a "check" are:

  1. a result contract (it returns failing rows, or metrics), and
  2. a little metadata (threshold, Kahn category, severity).

That is precisely what a FHIR profile expresses: same base resource, a couple of constraints, a couple of extensions. dbt reached the same conclusion — a test there is literally a select, with no separate "test" type. If the base SQLQuery can declare its output columns (e.g. via Library.parameter with use = out), the check is almost defined by its output shape, and the profile stays very thin.

Recommendation: start with a convention (SQLQuery + an agreed output shape + a few extensions); promote it to a formal thin profile once the base SQLQuery settles. Don't make it a separate resource.

10. Open questions for the group

  1. Result contract — failing-rows (dbt) vs num_violated/denominator (DQD), or both levels?
  2. Adopt the OpenLineage DQ facet as the result shape (interop with GX / Soda / dbt / catalogs)?
  3. Coordinate with the NCQA Bulk FHIR Quality Coalition (same Kahn categories, profile side)?
  4. Extend Kahn with Accuracy + Timeliness? (Timeliness ties into materialization freshness.)
  5. Observed lineage — a standard log format for $run, or leave it vendor-specific?
  6. DataQualityCheck — a thin profile now, or a convention first?

Sources

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions