Skip to content

Data & Quality

Understand how source data becomes the patient-level measures used throughout Care Gap Explorer.

This page describes the data pipeline and application measure definitions used in this portfolio demonstration.

Pipeline and lineage

Where the data comes from, and each step it takes before it reaches a page.

Open the full pipeline
Data pipeline and lineage: synthetic source records, raw Bronze, clean Silver, the diabetes cohort, the patient-level A1C measure, and Care Gap Explorer.
High-level overview of the data flow.

Pipeline stages

Explore each stage to see its transformations, validation, and outputs.

  1. SourceSynthetic healthcare records1,159patientsSynthea CSV exports: patients, conditions, encounters, observations, medications, procedures.

    Synthetic data, generated for this portfolio demonstration from a pinned seed so every number reproduces.

    870,510 observations, 67,795 encounters, 40,811 conditions, 59,273 medication rows and 187,126 procedures.

  2. Raw / BronzeFaithful copy6tablesLoaded as text, exactly as exported. Only a load time and source file are added.

    Every column is read as text, so no row is silently nulled by type inference. Ingestion is kept apart from transformation, and every Silver row can be traced back.

    Reconciliation
  3. Clean / SilverTyped and checked223rows quarantinedColumns typed and renamed; six checks run; failing rows quarantined with a reason.

    Columns are cast to real types and renamed to the data dictionary. Numeric results are typed while the original text is kept beside them.

    Six checks: encounter duplicates, results with no patient, implausible A1C, impossible birth dates, discharge before admission, and possible duplicate patients. 20 A1C unit errors are corrected rather than rejected, and 6 possible duplicate registrations are held for review, never merged automatically.

    Deduplication applies to encounters only, through quarantine; there is no general deduplication step.

    The six checks Quarantine
  4. CohortDiabetes cohort116patientsAny of 8 diabetes codes ever recorded, and alive on Aug 23, 2026.

    161 patients carry a diabetes code; 45 died before the data date and are excluded, because a care-gap list is a call list. 116 remain.

    Quarantined patients cannot enter: the cohort joins through Silver's patients.

    Patients
  5. GoldPatient-level A1C measure116rows, one per patientLatest A1C, its date, status, open-gap flag, days overdue, next due date.

    A1C observations are reduced to each patient's latest numeric result on or before the data date, then joined to the cohort, keeping patients with none.

    91 current, 4 overdue, 21 never tested. Ten assertions run on this table every time the pipeline does; any failure stops it.

    Care Gaps
  6. ApplicationCare Gap Explorer6areasHome, Care Gaps, Patients, Tasks, Analytics and Ask AI all read the same rows.

    Exported as static files and read in the browser. Every page counts with the same functions, so they cannot disagree.

    Home Analytics Ask AI

Do the numbers reconcile?

Computed here from the same patient rows and functions every page uses, not typed in.

116 = 91 + 25. 78.4% current, 21.6% with an open gap.PASS
25 = 21 + 4. 84.0% of open gaps never tested, 16.0% overdue.PASS

These counts reconcile across Home, Care Gaps, Patients, Analytics and Data & Quality.

View Care Gaps View Patients View Analytics

A1C monitoring measure

One status per patient, from one rule, against one fixed date.

  • Fixed data dateAug 23, 2026
  • Lookback365 daysFrom Aug 23, 2025
  • Qualifying resultA numeric A1C (LOINC 4548-4) on or before the data date
The 365-day lookbackEvery patient is checked against the same window: the 365 days up to the data date, Aug 23, 2026.
  • Current

    A qualifying A1C result within the previous 365 days.

    Boundary: exactly 365 days old is current.

  • Overdue

    An earlier A1C result exists, but none within the previous 365 days.

    Boundary: 366 days old is overdue.

  • Never tested

    No qualifying A1C result anywhere in the available data.

    No days-overdue figure: there is no earlier result to be late against.

Never tested describes the available dataset. It does not establish that a patient has never received an A1C outside the available records.

  • Monitoring status is not a diagnosis.
  • It does not say whether diabetes is controlled, and does not recommend treatment.
  • Completing a workflow task does not close a monitoring gap. Only qualifying clinical data changes the status.

Data quality checks

Each check names what it covers, what it found and why it matters. Open a row for the evidence and where the result comes from.

  • Checks evaluated28of 29 listed
  • Passed22
  • Warnings3plus 3 informational
  • Not evaluated1

A warning is something worth knowing, not a broken pipeline: small groups, synthetic values, or a gap in coverage. No score is computed from these, because a percentage of checks passed says nothing about which ones.

Identity & grain

  • One row per patientPatient-level care-gap dataset116 rows, 116 distinct patientsPassed

    Why it matters. Every count on every page assumes one row is one patient. A join that multiplied rows would inflate them all.

    Evidence. Also asserted in the pipeline on every run (Gold check V4.1).

    G1 · Live: recomputed from the patient data

  • No missing patient identifiersPatient-level care-gap datasetNone missingPassed

    Why it matters. A row without an identifier cannot be reviewed, linked to its history, or followed up.

    G2 · Live: recomputed from the patient data

  • Duplicate encountersAll encounters (DQ1: one row per patient and encounter)No duplicates in Silver; 40 repeated rows quarantinedPassed

    Why it matters. A repeated encounter would inflate visit counts and could move a patient's last-seen date. Repeats are quarantined, not merged.

    G4 · Pipeline: from the run's own report

  • Possible duplicate registrationsAll patients (DQ6: same name and birth date)6 pairs held for review; 0 involve cohort patientsInformational

    Why it matters. Two records for one person would double-count them. They are flagged for a human decision, never merged automatically.

    G3 · Pipeline: from the run's own report

A1C observations

  • Plausible A1C valuesLatest A1C of 95 tested patientsAll within 2.0–20.0%Passed

    Why it matters. A value outside any plausible range is a data error, such as a glucose keyed into a percent field, not a result.

    Evidence. The pipeline's DQ3 applies the same 2.0–20.0% range to every A1C. 20 values recorded in mg/dL rather than percent were converted, with the original kept, rather than discarded.

    A1 · Live: recomputed from the patient data

  • Unusually low A1C valuesLatest A1C of 95 tested patients13 below 3.0%Warning

    Why it matters. Values this low are rare in real care. They come from the synthetic generator and are kept as recorded; they pass the plausibility range but are worth knowing about.

    A2 · Live: recomputed from the patient data

  • Every result has a value95 patients with a latest A1CNo dated result is missing its valuePassed

    Why it matters. Gold only counts numeric results, so a date without a value would mean the selection let through something it should not.

    A3 · Live: recomputed from the patient data

  • Duplicate A1C observationsAll A1C results in Silver0 duplicates (same patient, time and value)Passed

    Why it matters. Duplicates would inflate testing counts per year, though not a patient's status.

    A4 · Audit: one-time warehouse query

  • Latest result is unambiguousAll A1C results in Silver0 patients with two results at the same latest timestampPassed

    Why it matters. Gold picks the latest result with row_number() ordered by time. A tie would make the chosen value arbitrary.

    A5 · Audit: one-time warehouse query

Dates

  • No results after the data dateLatest A1C datesNone after 2026-08-23Passed

    Why it matters. A result from after the data date would make a patient current on information that did not yet exist.

    Evidence. The audit also found 0 A1C results after the data date anywhere in Silver.

    D1 · Live: recomputed from the patient data

  • No encounters after the data dateLast encounter datesNone after 2026-08-23Passed

    Why it matters. "Last seen" must be on or before the data date, or recency filters would include visits that had not happened.

    D2 · Live: recomputed from the patient data

  • Encounter chronologyAll encounters (DQ5)No discharge before admission in Silver; 25 encounters quarantinedPassed

    Why it matters. Validates that discharge dates do not occur before admission dates. An encounter that ends before it starts is a clock or interface error, so it is quarantined with a reason rather than kept.

    D3 · Pipeline: from the run's own report

  • Plausible birth datesAll patients (DQ4: in the past, implied age 120 or less)8 patients quarantinedPassed

    Why it matters. An impossible birth date makes every age, and so every age band, wrong for that patient. Quarantined patients cannot enter the cohort.

    D5 · Pipeline: from the run's own report

  • Observations missing a dateAll observations in Silver0 missingPassed

    Why it matters. An undated result cannot be placed in the 365-day window.

    D4 · Audit: one-time warehouse query

Cohort

  • Deceased patients excludedPatients with any of the 8 diabetes codes161 coded, 45 died by the data date, 116 in the cohortPassed

    Why it matters. A care-gap list is a call list. Patients who died before the data date are not on it.

    Evidence. Asserted on every run as Gold check V4.5: the cohort equals the written definition.

    C1 · Audit: one-time warehouse query

  • Cohort matches the pipeline's reportPatient-level dataset vs gold_report.json116 rows; the pipeline reported 116Passed

    Why it matters. The browser and the warehouse must describe the same people.

    C2 · Live: recomputed from the patient data

  • Small groupsAge bands and care settings5 groups under 10 patientsWarning

    Why it matters. With so few patients a group's gap rate swings on one person. Pages show these groups but do not rank them.

    Evidence. 18-44 (5), urgentcare (5), emergency (3), hospice (1), snf (1)

    C3 · Live: recomputed from the patient data

Relationships

  • Results point at a known patientAll observations (DQ2)No unattributed results in Silver; 150 observations quarantinedPassed

    Why it matters. Validates referential integrity between observations and patients. A result for a patient who does not exist cannot be attributed, so it is quarantined rather than dropped silently.

    R1 · Pipeline: from the run's own report

  • Child rows of quarantined patientsObservations, encounters and medications in Silver2,646 rows belong to the 8 quarantined patientsWarning

    Why it matters. When a patient is quarantined (impossible birth date), their observations, encounters and medications stay in Silver without a patient row. None can reach the cohort, which joins through Silver's patients, so no number on any page is affected - but they should be quarantined with the patient.

    Evidence. 2,314 observations, 281 encounters, 51 medications; 0 such rows for any other patient.

    R2 · Audit: one-time warehouse query

  • Procedures without a patientAll proceduresNot checked by the pipeline or the auditNot evaluated

    Why it matters. No check covers procedures. They appear only in a patient's history, never in a count, so the risk is low; it should be added alongside R2's fix.

    R3 · Pipeline: from the run's own report

Measure

  • Current + open gaps = cohortPatient-level dataset91 + 25 = 116 (cohort 116)Passed

    Why it matters. Every patient is in exactly one of the two groups, or every page's percentages are wrong.

    M1 · Live: recomputed from the patient data

  • Never tested + overdue = open gapsOpen gaps21 + 4 = 25 (open gaps 25)Passed

    Why it matters. The two kinds of gap must partition the gaps, or a patient is counted twice or not at all.

    M2 · Live: recomputed from the patient data

  • Patients without an A1C are keptCohort vs patients with any A1C result21 kept with no result (116 in the cohort, 95 with a result)Passed

    Why it matters. These patients are the never-tested gaps. A join that required a result would drop all of them without an error.

    Evidence. Asserted on every run as Gold check V4.2.

    M3 · Live: recomputed from the patient data

  • Gap flag matches the written rule116 patients, re-derived from datesEvery flag agrees with the 365-day rulePassed

    Why it matters. The exported flag is tested against the definition rather than trusted.

    Evidence. Also Gold check V4.4.

    M4 · Live: recomputed from the patient data

  • The 365-day boundaryResults within 7 days of the boundaryNo patient's latest result is within 7 days of itInformational

    Why it matters. Exactly 365 days old is current; 366 is overdue. No patient sits near the line in this data, so the boundary is held by the Gold SQL and by unit tests rather than by an example.

    M5 · Live: recomputed from the patient data

  • Days overdue and next due are consistentPatient-level datasetConsistent for every patientPassed

    Why it matters. Never-tested patients must have neither figure; overdue patients must have days overdue.

    Evidence. Also Gold check V4.12.

    M6 · Live: recomputed from the patient data

  • Care Gaps order set only for gapsPatient-level datasetSet for every gap and no current patientPassed

    Why it matters. The worklist rank must cover exactly the patients on the worklist.

    Evidence. Also Gold check V4.11.

    M7 · Live: recomputed from the patient data

Pipeline

  • Source-to-Silver reconciliation6 source tables6 of 6 tables reconcile: source = Silver + quarantinePassed

    Why it matters. A row can leave the pipeline only by reaching Silver or quarantine, with a reason. Nothing disappears.

    Evidence. patients 1,159 = 1,151 + 8; encounters 67,795 = 67,730 + 65; conditions 40,811 = 40,811 + 0; observations 870,510 = 870,360 + 150; medications 59,273 = 59,273 + 0; procedures 187,126 = 187,126 + 0

    P1 · Pipeline: from the run's own report

  • Quarantined and corrected rowsRows rejected or corrected in Silver223 rows quarantined with a reason; 20 A1C values correctedInformational

    Why it matters. A row that fails a check is kept in quarantine with the check and the reason, never deleted, so every rejection can be reviewed and reversed.

    Evidence. DQ1: 40, DQ2: 150, DQ4: 8, DQ5: 25

    P2 · Pipeline: from the run's own report

Engineering decisions

The choices that most affect whether the numbers can be trusted.

Preserve patients with no A1C resultJoin the cohort to its A1C results with a LEFT JOIN, never an INNER JOIN.
LEFT JOIN against INNER JOIN. LEFT JOIN: 116 patients retained, 21 never-tested patients preserved. INNER JOIN: 95 patients retained, 21 never-tested patients lost.
Why
Of 116 cohort patients, 95 have a recorded A1C and 21 have none. Those 21 are the never-tested patients. The missing value is part of the signal.
Alternative
An INNER JOIN would return 95 rows, run without an error, and look plausible.
Risk avoided
Losing all 21 never-tested patients, and with them 21 of the 25 open gaps, silently.
The Gold SQL
One row per patientReduce each event table to one row per patient before joining it to the cohort.
cohort  ─┬─ LEFT JOIN latest_a1c    (1 row per patient)
         ├─ LEFT JOIN a1c_2y        (1 row per patient)
         ├─ LEFT JOIN first_dx      (1 row per patient)
         ├─ LEFT JOIN last_enc      (1 row per patient)
         └─ LEFT JOIN active_meds   (1 row per patient)
Why
A patient has many A1C results, encounters, prescriptions and procedures. MRN 64b7b8b0, for example, has 275 A1C results and 967 prescriptions: joined directly, that one patient becomes 265,925 rows before procedures (1,605) are added. Gold reduces each table first - latest A1C, last encounter, count of A1C in two years, active medications - then joins.
Alternative
Join every event table to the cohort and aggregate at the end.
Risk avoided
Row multiplication: counts, rates and ranks inflated by however many events a patient happens to have. Gold asserts one row per patient on every run (V4.1).
Select the latest A1C as a whole rowRank each patient's results by time and keep the first, so the date and value come from the same result.
latest_a1c AS (
    SELECT patient_id, observed_at::DATE AS last_a1c_date, value AS last_a1c_value
    FROM a1c QUALIFY row_number() OVER (PARTITION BY patient_id ORDER BY observed_at DESC) = 1
)
Why
The real Gold query keeps the latest numeric result on or before the data date with a window function. The audit found 0 patients with two results at the same latest time, so the choice is never arbitrary.
Alternative
MAX(observed_at) with the value fetched separately, or MAX of each column independently.
Risk avoided
A date from one result paired with the value of another - MAX(value) is the highest A1C ever, not the latest.
The Gold SQL
Use a fixed data date, not todayMeasure every window from Aug 23, 2026, the day the source data ends.
Open gaps if the data date moved forward and the data stayed the same, computed from the patient rows with the Gold rule.
Data dateOpen gapsOf cohort
Aug 23, 2026 (fixed)25116
+1 month · Sep 23, 202629116
+3 months · Nov 23, 202637116
+6 months · Feb 23, 202750116
+9 months · May 23, 202774116
+12 months · Aug 23, 2027116116
Why
The source stops at a known date. Measured from the calendar instead, the same data would produce more gaps every day simply because time passed. A fixed reporting date makes the result reproducible.
Alternative
CURRENT_DATE.
Risk avoided
Identical data giving different answers on different days; within a year, every patient would be a gap.
ADR 0007: fixed as-of date
Calibrate the A1C plausibility rule to the dataSet the lower plausibility bound at 2.0%, not 3.0%, after measuring what each would reject.

A plausibility check decides whether a value could be a real measurement. It is a data-quality rule, not a clinical target, and says nothing about whether a result is good or bad for a patient.

Why
In the untouched source, 8,941 A1C results: a 3.0% floor would have rejected 951 of them and passed 7,990. Those 951 were not errors - they are how the synthetic generator writes A1C - so the floor moved to 2.0%, and the range became 2.0–20.0%.
Alternative
A floor chosen from clinical intuition.
Risk avoided
Quarantining about one result in nine as 'implausible' and quietly turning tested patients into never-tested gaps.
Data quality reference

Source, derived and workflow data

Three kinds of information, kept apart on every page.

  • Clinical source data

    From the synthetic healthcare source, through the pipeline.

    • Patient demographics
    • Encounters and their care settings
    • Diagnoses
    • A1C observations, with dates and values
    • Medications and their fill counts
    • Procedures performed
  • Derived application data

    Calculated by Care Gap Explorer from the source.

    • Diabetes cohort membership
    • Latest A1C and its date
    • Monitoring status: current, overdue, never tested
    • Open-gap indicator, days overdue, next due date
    • Population metrics
  • Demo workflow data

    Saved in this browser only. Not part of the clinical source.

    • Task status and assignee
    • Due dates and workflow notes
    • Workflow activity history
    • Ask AI conversations

Workflow data is not part of the clinical source. Changing a task's status never changes a patient's A1C gap status.

Reproducibility

What anyone needs to rebuild the measure and get the same answer.

Data date
Aug 23, 2026
Lookback
365 days
Population
Diabetes cohort, alive on the data date (116)
Final grain
One row per patient
Primary measure
A1C monitoring status
Statuses
Current, Overdue, Never tested
Source data
Synthea, pinned seed
  • Fixed data date: met
  • Defined denominator: met
  • Explicit lookback: met
  • Deterministic status logic: met
  • Patient-level grain: met
  • Missing A1C preserved: met
  • Counts reconcile: met
The full pipeline, section by section

Data limitations

What the available data cannot tell you, whatever page you read it on.

  • Synthetic data

    Every patient is generated by Synthea. Some values, such as A1C results below 3%, are rare in real care and are kept as recorded.

  • Incomplete history

    The available records may not hold a patient's complete history. Never tested means no A1C in this data, not never tested anywhere.

  • No orders

    The source has no orders table. A test that was ordered and missed looks the same as one that was never requested.

  • Fills are not doses

    Medication records carry dispense counts, not doses, and do not show adherence or confirmed use.

  • Demonstration definitions

    The measure follows the HEDIS idea of one A1C a year but is a portfolio definition, not a certified implementation. Results are not clinical recommendations.

  • Workflow and roles

    Task data is saved in the browser only. The pipeline writes role-scoped exports for three roles with a tested access matrix; the web application shows the whole record and does not enforce them.