Pipeline

Four stages, and the checks between them

6 of 6 caught

The stages

Each is re-runnable and verifies its own work. A stage that cannot prove what it did raises rather than printing a warning nobody reads.

Ingest

Raw CSVs into Bronze

1,226,674 rows

Every column loaded as text. Nothing cast, deduped or filtered — the only additions are a load timestamp and the source filename.

Inject defects

249 rows damaged on purpose

249 rows

Six realistic failure modes, each one logged. Without that log a catch rate is a claim rather than a measurement.

Validate

Six checks into Silver

1,226,451 rows

Surviving rows are typed; rejected rows land in quarantine with a reason. Never dropped.

Gold

One row per diabetic patient

116 patients

Sourced from Silver only. If Gold ever read Bronze, the whole validation layer would be decorative.

Nothing is silently dropped

For every table, rows must equal rows plus rows. The pipeline asserts this on every run and stops when it fails — which is what turns the claim into a property somebody can check.

Table	Bronze	Silver	Quarantined
patients	1,159	1,151	8
encounters	67,795	67,730	65
conditions	40,811	40,811	—
observations	870,510	870,360	150
medications	59,273	59,273	—
procedures	187,126	187,126	—

The six checks

Each check cleans one failure mode, and each has a real operational cause — knowing the cause is the difference between “duplicate rows” and “an interface replayed the message”, which is what tells you whether to fix the data or the interface. Every row a check catches is quarantined with its reason attached, never dropped.

Check	Rule	What causes it in a real system	Rows cleaned
DQ1 Encounter uniqueness	One row per patient and encounter	An interface replayed the message	40
DQ2 Referential integrity	Every observation points at a patient that exists	A result arrived with an unresolvable patient id	150
DQ3 A1c plausibility	A1c between 2.0 and 20.0 percent	A glucose in mg/dL keyed into a percent field	20
DQ4 Birth date sanity	In the past, implied age 120 or less	A registration typo in the year	8
DQ5 Encounter chronology	Discharge is not before admission	Clock drift between two systems	25
DQ6 Patient identity	No two patients share name and birth date	The same person registered twice	6

Quarantine

223 rows held back, 4 distinct reasons. Showing the rows is the argument — a count would be the claim, not the evidence. Somebody will eventually ask why a patient is missing from a report, and this is how that gets answered.

Check	From	Row	Why it was held back
DQ1	encounters	01e52f9d-3872-9a…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	09527386-1d9c-f6…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	12c65474-ae7c-3a…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	18f1d934-bd81-79…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	1c0fa1bd-7f20-9e…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	1e528593-92f0-e5…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	1f15f811-0033-bd…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	2a418d80-75d4-cb…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	330e68f1-aa53-9d…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	3b15ee15-0f2a-91…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	4528f806-07dd-7a…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	4941744a-e120-ed…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	4dd9594e-3d10-6f…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	53b237aa-9ddf-40…	Duplicate encounter row for the same patient and encounter id; earliest copy kept
DQ1	encounters	546eccd8-ce77-14…	Duplicate encounter row for the same patient and encounter id; earliest copy kept

Suspected duplicate patients

These records look like the same human registered twice. Nothing downstream merges them, and both patients continue to exist separately in Silver. That restraint is a choice, not a missing feature — a wrong merge combines two people's medication lists, which is a patient safety event rather than a data bug.

Record A	Record B	Fields that match	Confidence	Status
1782a49d	ee2fdada	first name, last name, birth date, ssn, address	1.00	awaiting review
3c9cff8e	5c92cf2b	first name, last name, birth date, ssn, address	1.00	awaiting review
4917c584	b5c36d2a	first name, last name, birth date, ssn, address	1.00	awaiting review
93a5bbce	ab5a9b62	first name, last name, birth date, ssn, address	1.00	awaiting review
9c5895b3	aa081e8d	first name, last name, birth date, ssn, address	1.00	awaiting review
bb610fec	d119c336	first name, last name, birth date, ssn, address	1.00	awaiting review

The range nearly threw away 951 good results

The plausibility check needs a floor. Picking one by eye would have made the check itself the biggest source of error on the page.

A histogram of clean A1c values, with 951 of 8,941 results falling below a proposed floor of 3.0 percent. — The specification proposed rejecting any A1c below 3.0%. Run against the clean data first, that floor would have quarantined 951 of 8,941 real results, an 11% false-positive rate on a check meant to catch 20 injected errors. The floor shipped at 2.0% because the data was checked before the rule was written, not after.(notebooks/01_profile.ipynb)

One correction, in full

Not every bad value is thrown away. An is a percentage, so 250 is impossible — but blood glucose in mg/dL lands there routinely, which makes this a unit error rather than nonsense.

As it arrived

250

percent — biologically impossible

After correction

10.3

percent — a poorly controlled diabetic

Rule applied

A1c = (value + 46.7) / 28.7

The ADA mapping between A1c and estimated average glucose. The original is kept, so a reviewer who disagrees excludes every corrected row with one filter.