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.

Pipeline stages
Explore each stage to see its transformations, validation, and outputs.
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.
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.
ReconciliationClean / 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 QuarantineCohortDiabetes 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.
PatientsGoldPatient-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
Do the numbers reconcile?
Computed here from the same patient rows and functions every page uses, not typed in.
These counts reconcile across Home, Care Gaps, Patients, Analytics and Data & Quality.
View Care Gaps View Patients View AnalyticsA1C 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
- Current91 patients current
An A1C result on or after Aug 23, 2025.
- Overdue4 patients overdue
A result exists, but the latest is before Aug 23, 2025.
- Never tested21 patients never tested
No A1C result anywhere in the available data.
- 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.

- 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.
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.
Use a fixed data date, not todayMeasure every window from Aug 23, 2026, the day the source data ends.
| Data date | Open gaps | Of cohort |
|---|---|---|
| Aug 23, 2026 (fixed) | 25 | 116 |
| +1 month · Sep 23, 2026 | 29 | 116 |
| +3 months · Nov 23, 2026 | 37 | 116 |
| +6 months · Feb 23, 2027 | 50 | 116 |
| +9 months · May 23, 2027 | 74 | 116 |
| +12 months · Aug 23, 2027 | 116 | 116 |
- 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.
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.
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
- ReconciliationBronze equals Silver plus quarantine, for every table, on every run.
- The six checksWhat each data quality check catches, and the operational cause behind it.
- The A1C floorWhy the plausibility range nearly threw away 951 good results.
- QuarantineEvery row held back from the report, with the reason.
- Identity reviewRecords that look like the same person, waiting for a human decision.
- RemediationValues corrected rather than rejected, with the original kept.
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.