Skip to content
Franklin Ajisogun
All projects

Case study · Healthcare Analytics / Data Engineering

Clinical Care Gap Explorer

A healthcare analytics application that uses synthetic patient data to identify potential gaps in diabetes A1C monitoring.

Status
Live demo · Portfolio project
Technologies
  • Python
  • DuckDB
  • Next.js
  • TypeScript
Links
Clinical Care Gap Explorer home screen showing a cohort of 116 patients, 25 open A1C gaps, 21 never tested, and a list of patients needing attention.

The Problem

Why A1C monitoring gaps matter

HbA1c, or A1C, is a blood test that reflects average blood glucose over roughly the past two to three months. Repeating it is how a care team follows a patient's diabetes over time, and widely used quality measures expect an A1C result at least once a year.

When no recent result is on file, that monitoring has lapsed: a care gap. Across a whole population those gaps are easy to miss, and the patients most likely to be missed are the ones with no result at all.

The question this project asks is simple: which patients with diabetes have no A1C result in the last 12 months? Answering it in a way that can be trusted is the harder part.

Clinical Care Gap Explorer is a portfolio demonstration built entirely on synthetic data. It is not a clinically validated product and is not intended for clinical decision-making.

The Solution

From raw records to a trustworthy list

  1. Processes synthetic healthcare data

    Synthea generates the synthetic patient records. A Python pipeline loads them into DuckDB and runs them through five stages: ingest, inject known defects, validate, build the gold table, and publish.

  2. Identifies the diabetes cohort

    A patient is in the cohort if any of eight diabetes SNOMED codes appears on their record and they are alive on the fixed data date. Prediabetes and hyperglycaemia are explicitly excluded.

  3. Evaluates A1C monitoring history

    For each patient in the cohort, the pipeline finds their numeric A1C results (LOINC 4548-4) on or before the data date and keeps every patient, including those with no result at all.

  4. Identifies potential monitoring gaps

    No A1C result in the 365 days before the data date is an open gap, and never tested counts as a gap. Each patient gets one status: current, overdue, or never tested.

  5. Presents descriptive analytics

    The web app shows the cohort, the care-gap work queue, patient-level evidence, and population analytics such as gap rates by age band, always with the denominator in view.

Architecture

How the pieces fit together

Every transform is SQL running in DuckDB, orchestrated by a Python command-line pipeline. The web app is a static export that reads the pipeline's output.

Data flow: Source (Synthea), then Bronze (Raw copy), then Silver (Validated), then Gold (care_gap_a1c), then Publish (JSON + Parquet), then Application (Next.js / TypeScript). Stages Bronze, Silver, Gold run in the DuckDB analytical database.

AI integration

The app's Ask AI page is rule-based, not a language model: preset questions with hand-written SQL, executed in the browser, with the SQL shown beside each answer. Unrecognized questions are refused. Separately, a notebook evaluated LLM-generated SQL (Claude) against the hand-written queries; the model was not shipped.

Role-based access model

The pipeline writes one data file per clinical role, built by queries that never select restricted columns. It models the minimum-necessary principle; there is no authentication, and it is not HIPAA compliance.

Data Quality

Earning trust in the numbers

  • Data validation

    Six classes of realistic defect were deliberately injected, including duplicate encounters, unit errors, future birth dates, and the same patient registered twice. Six checks (DQ1 to DQ6) are designed to catch them.

  • SQL checks and reconciliation

    On every table, Bronze must equal Silver plus quarantine, and the pipeline stops if it does not. The app also checks that its headline numbers reconcile, for example 116 = 91 current + 25 open gaps.

  • Cohort definition

    Anchoring on the type 2 diabetes code alone would have produced 88 patients instead of 116, because 73 patients carry a diabetes complication with no underlying diagnosis code.

  • A1C monitoring rules

    A numeric A1C result (LOINC 4548-4) within 365 days of the fixed data date. Exactly 365 days is not a gap; 366 is. Corrected values count as results.

  • Data transparency

    Rejected rows are quarantined with a reason, never silently dropped. Corrected values keep the original. Suspected duplicate patients are routed to human review and never auto-merged.

  • Limitations of the checks

    The checks are scored against defects the project injected itself, so the catch rate measures coverage of known problems, not of every way real data can fail.

Data & Quality page showing reconciliation checks: 116 equals 91 current plus 25 open gaps, and 25 equals 21 never tested plus 4 overdue, both marked pass, with a 365-day lookback timeline.
Data & Quality: reconciliation checks and the 365-day lookback.

Technical Decisions

Choices and their tradeoffs

  • Python for the pipeline, SQL for the transforms

    A Python package runs the stages and verifies each one; the transforms themselves are SQL.

    Tradeoff

    Keeping logic in SQL rather than pandas means moving to a cloud warehouse would be a connection string and a dialect pass, not a rewrite.

  • DuckDB for analytical queries

    Every query stays sub-second on a laptop, which matters when the pipeline runs dozens of times a day during development.

    Tradeoff

    DuckDB allows one writer at a time. That's an excellent analytical engine and a poor shared warehouse.

  • Next.js as a static export

    The site reads a build-time snapshot: JSON to render pages and Parquet for in-browser querying, deployable with no server runtime.

    Tradeoff

    Re-running the pipeline without re-exporting would leave the site stale, so export is the pipeline's final stage.

  • Synthetic data for the demonstration

    Synthea produces realistic but fictional patient records, so the project contains no protected health information.

    Tradeoff

    Synthetic patients receive more diligent care than real populations, and there is no orders table, so "ordered but not resulted" can't be distinguished from "never ordered".

  • No language model in the app

    A static site can't hold an API key safely, so the question page uses preset questions with real, visible SQL.

    Tradeoff

    Refusing unrecognized questions is a worse experience than a chatbot, but a safer failure than a confident wrong answer.

Results

What the pipeline found

Figures produced by the pipeline from the synthetic dataset.

Synthetic patients generated
1,000
Synthea population setting. Records for patients who died during the simulation are also loaded.
Patients in the diabetes cohort
116
Alive on the data date, with any of eight diabetes codes.
Patients with open A1C monitoring gaps
25
21.6% of the cohort. 21 of them have never been tested.

These numbers describe a synthetic dataset. They are not clinical performance measures, and the project makes no claim about patient outcomes.

Challenges & Lessons

What building it taught me

  • Data modeling

    An inner join from the cohort to lab results silently removes the 21 patients who have never been tested, the highest-risk people on the list, without raising an error. The cohort has to be the left side of a left join.

  • Data quality

    A catch rate scored against injected defects measures the imagination of whoever wrote the checks. Real data fails in ways nobody wrote a rule for, which is why distribution monitoring belongs alongside rule-based checks.

  • Healthcare terminology

    Diagnoses are coded, not written. Choosing a value set of SNOMED codes instead of a single code, and A1C instead of blood glucose, changed who counts and what the measure means.

  • Application development

    Testing caught bugs that reading did not: a SELECT-only SQL guard that let a WITH … DELETE statement through, and a question about insulin dosing that matched a keyword and returned a patient list.

  • Responsible AI

    In an offline evaluation, a language model answered a question about records awaiting review by querying the wrong field and returning zero rows: not an error, but an empty result that reads as "nothing needs review". Better grounding, not a better model, is what would change the decision.

Limitations & Future Work

What it can't do, and what's next

Limitations

  • Synthetic data only. The patients are fictional.
  • Not intended for clinical decision-making.
  • Descriptive, not prescriptive: it reports monitoring status and doesn't recommend treatment.
  • No claim of HIPAA compliance. The role model has no authentication.
  • Ordered-but-not-resulted tests and results held in other lab systems are invisible.

Future work

  • Orchestrate the pipeline with a tool like Dagster or Airflow, so failed stages re-run on their own.
  • Move checks next to the models they validate, without losing quarantine-with-a-reason.
  • Alert on drift in data distributions rather than fixed thresholds.
  • Make quarantined rows reprocessable once upstream issues are fixed.
  • Explore grounded natural-language querying, evaluated against the hand-written queries.
Ask AI page listing never-tested patients and explaining how the answer was calculated: population, filters, result, denominator, and source.
Ask AI: rule-based answers that explain their population and denominator.