Introduction

What this is, and why the middle part matters

Synthea · Massachusetts · as of 2026-08-23

Which diabetic patients have not had an A1c test in the last twelve months?

An measures average blood sugar over about three months. A diabetic patient is meant to have one roughly every six months. When twelve months pass with no result, that is a , and this project finds them.

New to clinical data? The Clinical concepts tab above explains every term, and any term with a dotted underline will define itself wherever you meet it.

See the numbers How the data gets there

Why it is really a data quality project

A care-gap list gets handed to a nurse who picks up a phone, so a wrong list costs something in both directions.

A false positive

A wasted call to someone who already had the test. Their result was filed under a duplicate record, or with a broken link back to the patient.

A false negative

Worse. The patient stays invisible, and everybody trusts the report that hid them.

Real clinical data arrives with duplicate rows, lab values in the wrong units, patients registered twice under different record numbers, and timestamps that contradict each other. So the build order is: load it, deliberately break it, catch the breakage, measure what fraction was caught, and only then report the gaps.

Two bars. LEFT JOIN keeps 116 patients. INNER JOIN keeps 95, with 21 patients shown as silently deleted. — Here is what that costs when it goes wrong. Joining the cohort to its lab results with INNER JOIN instead of LEFT JOIN deletes every patient who has no result, which is every patient the report exists to find. Both queries run, neither errors, and the shorter list looks entirely reasonable.(notebooks/01_profile.ipynb)

What was found

25 of 116

have an open A1c gap

21.6% of the diabetic cohort alive on the as-of date

have never been tested

Three separate ordinary mistakes would each have hidden exactly these people

6 of 6

defect types caught

249 of 249 injected rows, each by the check meant for it

A funnel from 161 patients carrying a diabetes code, to 116 alive on the as-of date, to 25 with an open A1c gap, to 21 never tested. — Every step from 1,151 patients to the 25 on the list is a decision somebody made and wrote down, not a filter chosen to make the number look a certain way. The two that move it most are excluding the deceased and counting a complication code as diabetes.(notebooks/01_profile.ipynb)

How the data gets here

1,226,674

Raw CSVs, every column text, nothing cleaned

1,226,451

Typed and validated; rejects quarantined with a reason

116

One row per diabetic patient. This is the care-gap list