Diabetic Care Gap Analysis

Query synthetic EHR data in DuckDB to find diabetic patients overdue for care.

Introduction

30 Second Summary

Every year, thousands of diabetic patients miss critical lab tests because their health center has no way to flag who is overdue. The data exists in the system, but nobody is querying it.

In this project, you will play the role of an analytics consultant hired by a community health center to find which diabetic patients are falling through the cracks. You will load raw patient data into DuckDB, write SQL queries to identify care gaps, and present your findings in a Power BI dashboard.

What You'll Build

You will open your finished dashboard and show a health center executive exactly which patients need outreach, how overdue they are, and where demographic disparities exist.

By the end of this project, you'll have:

  • A DuckDB database loaded with synthetic EHR data (patients, conditions, observations, medications) that you can query to explore real-world clinical record structures.
  • SQL queries using CTEs, JOINs, and window functions that identify diabetic patients overdue for their HbA1c lab test.
  • A Power BI dashboard with visuals showing care gap urgency categories, demographic breakdowns, and executive-ready recommendations that a health center leader could act on today.
  • Secret Mission: A provider-level performance comparison showing which providers have the highest rates of overdue patients.

Are there any prerequisites?

None. This guide walks you through installing DuckDB and downloading the free synthetic dataset. You will need Power BI Desktop installed on Windows, which is a free download from Microsoft.

Before We Start

Before we dive in, let's lock in what you're about to build and why it matters.

You are an analytics consultant who just received a raw EHR data export from a community health center. Your job is to find which diabetic patients are overdue for care.

Set Up DuckDB and Load the Data

Your community health center client just handed you a raw EHR data export. Before you can find which diabetic patients are falling through the cracks, you need a way to query this data with SQL.

In this step, you'll install DuckDB (a lightweight SQL database that runs locally with zero server setup), load the health center's patient data into it, and confirm you can query it.

In this step, get ready to:
  • Install the DuckDB CLI on your machine.
  • Download and extract the synthetic patient data.
  • Load the CSV files into a persistent DuckDB database.
Install the DuckDB CLI

DuckDB is a free, open-source SQL database that reads CSV files directly. It installs in seconds and needs no server configuration.

  • Open PowerShell by pressing the Windows key, typing PowerShell, and pressing Enter.
  • Install DuckDB by running this command:
winget install DuckDB.cli

What does this command do?

winget is the Windows package manager that comes built into Windows 10 and 11. It downloads and installs DuckDB's command-line interface for you automatically.

  • Close your PowerShell window completely, then reopen it the same way (Windows key, type PowerShell, press Enter).

Why do I need to reopen PowerShell?

The installer adds DuckDB to your system PATH, but that change only takes effect in new terminal sessions. Your current window still has the old PATH.

  • Verify DuckDB installed correctly by running:
duckdb --version

Check what your terminal displays:

✔️ I see a version number

You should see something like v1.5.5 followed by a codename and hash. DuckDB is ready to go.

ⓧ Command not found

If PowerShell says it cannot find duckdb, try these fixes:

  • Confirm you closed and reopened PowerShell after the install.
  • Run winget install DuckDB.cli again to check for errors. If you see a message that the Microsoft Visual C++ Redistributable is required, install it from the Microsoft website first, then retry.
  • If winget itself is not found, make sure you are running Windows 10 (version 1809 or later) or Windows 11. Update Windows if needed.
Download and extract the Synthea data

Your health center client's data comes from Synthea, a tool that generates realistic synthetic patient records. The data is structured exactly like a real EHR export but contains no actual patient information.

  • Open your browser and go to synthea.mitre.org/downloads.
  • Find the download labeled 1K Sample Synthetic Patient Records, CSV (approximately 9 MB).
  • Click the download link and save the ZIP file.

Now create your project folder and extract the data into it:

  • In your reopened PowerShell, move to your Desktop by running this command:
cd ~\Desktop
  • Create your project folder and a data subfolder by running these commands:
mkdir health-center-project
mkdir health-center-project\data
  • Open File Explorer and navigate to your Desktop, then into the health-center-project\data folder.
  • Extract the contents of the downloaded ZIP file into this data folder. Right-click the ZIP, select Extract All, and set the destination to your data folder.

Check the folder structure

After extraction, make sure the CSV files (patients.csv, conditions.csv, etc.) are directly inside data\, not nested in an extra subfolder. If they are nested, move them up one level.

  • Confirm the files are in place by running:
ls ~\Desktop\health-center-project\data\*.csv

You should see a list of CSV files including patients.csv, conditions.csv, observations.csv, and medications.csv.

Don't see any CSV files?

The ZIP may have extracted into a nested subfolder. Open File Explorer, navigate into health-center-project\data, and check if there is another folder inside. If so, move the CSV files up into data directly.

help me find and organize my Synthea CSV files in the correct folder

Load the CSV files into DuckDB

With your data in place, you can now create a persistent DuckDB database and load the four tables you need for your care gap analysis.

  • Move into your project folder by running:
cd ~\Desktop\health-center-project
  • Open DuckDB and create a persistent database file by running:
duckdb health_center.duckdb

What does this command do?

Passing a filename to duckdb creates a persistent database file. Everything you create inside this session (tables, views) is saved to health_center.duckdb on disk. Without a filename, DuckDB runs in-memory and your work disappears when you exit.

You should now see the DuckDB interactive prompt (a D followed by a cursor). You are inside the database.

  • Load the four key CSV files into tables by running these SQL statements one at a time:
CREATE TABLE patients AS
SELECT * FROM read_csv_auto('data/patients.csv');
CREATE TABLE conditions AS
SELECT * FROM read_csv_auto('data/conditions.csv');
CREATE TABLE observations AS
SELECT * FROM read_csv_auto('data/observations.csv');
CREATE TABLE medications AS
SELECT * FROM read_csv_auto('data/medications.csv');

What do these statements do?

Each CREATE TABLE ... AS SELECT * FROM read_csv_auto(...) statement tells DuckDB to read a CSV file and create a permanent table from its contents.

The read_csv_auto function automatically detects column names, data types, and delimiters. You end up with four queryable tables: patients, conditions, observations, and medications.

Before you run the next command, how many rows do you think the patients table contains? Remember the dataset name.

  • Verify the data loaded correctly by running:
SELECT count(*) FROM patients;

You should see approximately 1,000 rows returned. That confirms all your patient records loaded successfully into DuckDB.

Seeing an error about the file path?

If DuckDB reports that it cannot find the CSV file, check two things:

  • Make sure you launched duckdb health_center.duckdb from inside the health-center-project folder (not from Desktop or another location).
  • Confirm that your CSV files are directly inside the data subfolder, not nested in an extra directory from the ZIP extraction.

help me fix my DuckDB file path error when loading CSVs

✔️ Awesome, I've got everything!

Great. Double check you see the D prompt and the count returned approximately 1,000.

ⓧ I'd like to double check the full code

Here is the complete set of SQL statements you should have run inside DuckDB. Compare against what you entered:

-- Create a persistent database and load Synthea CSVs
-- Run from DuckDB CLI: duckdb health_center.duckdb

CREATE TABLE patients AS
SELECT * FROM read_csv_auto('data/patients.csv');

CREATE TABLE conditions AS
SELECT * FROM read_csv_auto('data/conditions.csv');

CREATE TABLE observations AS
SELECT * FROM read_csv_auto('data/observations.csv');

CREATE TABLE medications AS
SELECT * FROM read_csv_auto('data/medications.csv');

Your SQL environment is ready and loaded with real-world-structured clinical data. Next up, you'll explore what this data actually looks like and identify the diabetic patient cohort.

Explore the Schema and Find the Diabetics

You have four tables loaded into your database, but you have no idea what columns they contain or how they connect to each other. Before you can identify care gaps, you need to understand the data landscape.

A senior consultant always assesses what they're working with before writing analysis. In this step, you'll map the table schemas, spot the messiness of real-world EHR data, and write your first analytical query to isolate the diabetic patient cohort.

In this step, get ready to:
  • Explore table schemas and map the relationships between tables.
  • Write a CTE query to identify the diabetic patient cohort with demographics.
  • Review the cohort size and demographic breakdown as your analysis baseline.
Explore the table schemas

Your four tables are loaded, but you need to see what columns each one has and how they relate. The Patient column is the key that links everything together.

  • In the DuckDB CLI (which is already open with health_center.duckdb), run these DESCRIBE commands to see each table's columns:
DESCRIBE patients;
DESCRIBE conditions;
DESCRIBE observations;

You'll see a table listing each column's name, type, and constraints for each DESCRIBE statement.

What should I notice?

Look at how the tables connect. The patients table has an Id column (a UUID). The conditions, observations, and medications tables each have a Patient column that references that UUID.

Notice the messiness typical of real EHR exports. Conditions uses SNOMED-CT codes (numeric codes without friendly names). Observations mixes all test types (labs, vitals, surveys) into one table with LOINC codes.

  • Confirm your total patient population by running:
SELECT count(*) AS total_patients FROM patients;

You should see approximately 1,000 rows returned. This is your full population, and your next task is to find the diabetics within it.

Not seeing any results?

Make sure you're running the query inside the DuckDB CLI with health_center.duckdb open. If you see an error about the table not existing, re-run your load commands from Step 1.

Help me troubleshoot my DuckDB query

Identify the diabetic population

Now that you understand the schema, you need to isolate the diabetic patients as your analysis cohort. You'll use a CTE (Common Table Expression) to filter the conditions table, then JOIN the results to patients for demographics.

What is a CTE?

A CTE is a temporary named result set defined with the WITH keyword. It lets you break complex queries into readable steps. Think of it as creating a mini-table that only exists for the duration of your query.

Before you run this query, think about it: out of approximately 1,000 patients in this community health center, how many do you expect to have an active diabetes diagnosis?

  • Find the diabetic patients with their demographics by running this query:
-- Find diabetic patients with demographics
WITH diabetic_patients AS (
    SELECT DISTINCT c.Patient
    FROM conditions c
    WHERE c.Description LIKE '%Diabetes%'
      AND c.Stop IS NULL
)
SELECT
    p.Id,
    p.First,
    p.Last,
    p.BirthDate,
    p.Race,
    p.Ethnicity,
    p.Gender,
    date_diff('year', p.BirthDate::DATE, CURRENT_DATE) AS age
FROM diabetic_patients dp
JOIN patients p ON dp.Patient = p.Id;

What does this query do?

  • The CTE filters conditions for rows where Description contains 'Diabetes' and Stop IS NULL (meaning the condition is still active, not resolved).
  • DISTINCT avoids duplicate patient IDs, since a patient could have multiple diabetes-related condition records.
  • The JOIN brings in demographic columns from the patients table using the Patient UUID as the link.
  • date_diff('year', p.BirthDate::DATE, CURRENT_DATE) calculates each patient's current age from their birth date.

You'll see a result set showing the diabetic patients with their Id, name, birth date, race, ethnicity, gender, and calculated age. This is a subset of your full 1,000-patient population.

Seeing zero rows?

Check that '%Diabetes%' uses percent signs on both sides. The LIKE operator is case-sensitive in DuckDB, and Synthea capitalizes 'Diabetes' in the Description field.

Help me debug my diabetic patient query

Save your exploration queries

You've mapped the schema and identified your cohort. Now save all your exploration work to a .sql file so you have a record of your methodology.

  • Create a new file called 02_explore_and_find_diabetics.sql in your project folder.
  • Paste the following into it:
-- Explore the schema
DESCRIBE patients;
DESCRIBE conditions;
DESCRIBE observations;

-- Count patients
SELECT count(*) AS total_patients FROM patients;

-- Find diabetic patients with demographics
WITH diabetic_patients AS (
    SELECT DISTINCT c.Patient
    FROM conditions c
    WHERE c.Description LIKE '%Diabetes%'
      AND c.Stop IS NULL
)
SELECT
    p.Id,
    p.First,
    p.Last,
    p.BirthDate,
    p.Race,
    p.Ethnicity,
    p.Gender,
    date_diff('year', p.BirthDate::DATE, CURRENT_DATE) AS age
FROM diabetic_patients dp
JOIN patients p ON dp.Patient = p.Id;

Why save SQL files?

Saving your queries as .sql files documents your methodology. A consulting deliverable includes not just the dashboard, but the reproducible analysis behind it. You can re-run this file anytime with .read 02_explore_and_find_diabetics.sql in the DuckDB CLI.

  • Save the file.

✔️ Awesome, I've got everything!

Great. Make sure 02_explore_and_find_diabetics.sql is saved in your project folder alongside 01_load_data.sql.

ⓧ I'd like to double check the full code

Here is the complete 02_explore_and_find_diabetics.sql file:

-- Explore the schema
DESCRIBE patients;
DESCRIBE conditions;
DESCRIBE observations;

-- Count patients
SELECT count(*) AS total_patients FROM patients;

-- Find diabetic patients with demographics
WITH diabetic_patients AS (
    SELECT DISTINCT c.Patient
    FROM conditions c
    WHERE c.Description LIKE '%Diabetes%'
      AND c.Stop IS NULL
)
SELECT
    p.Id,
    p.First,
    p.Last,
    p.BirthDate,
    p.Race,
    p.Ethnicity,
    p.Gender,
    date_diff('year', p.BirthDate::DATE, CURRENT_DATE) AS age
FROM diabetic_patients dp
JOIN patients p ON dp.Patient = p.Id;

You now have your diabetic cohort identified with full demographics. Next up, you'll dig into the observations table to find when each of these patients last had their HbA1c tested.

Find Each Patient's Most Recent HbA1c Test

You have a working list of diabetic patients. The next question a consultant asks is: when did each of these patients last have their HbA1c tested?

The observations table holds every lab result, vital sign, and screening for every patient. A single patient can have dozens of HbA1c records spanning years. Joining directly will produce duplicate rows, making it impossible to identify the single most recent test date.

In this step, get ready to:
  • Run a naive JOIN and observe the duplicate-row problem firsthand.
  • Fix the duplicates using a ROW_NUMBER() window function.
  • Capture never-tested patients with a LEFT JOIN.
Attempt a direct JOIN to observations

Your first instinct might be to JOIN the diabetic cohort directly to observations, filtering for the HbA1c LOINC code. Let's try it and see what happens.

  • Run this query in your DuckDB CLI to join diabetic patients with their HbA1c observations:
WITH diabetic_patients AS (
    SELECT DISTINCT c.Patient
    FROM conditions c
    WHERE c.Description LIKE '%Diabetes%'
      AND c.Stop IS NULL
)
SELECT
    p.Id AS patient_id,
    p.First,
    p.Last,
    o.Date AS hba1c_date,
    o.Value AS hba1c_value
FROM diabetic_patients dp
JOIN patients p ON dp.Patient = p.Id
JOIN observations o
    ON dp.Patient = o.Patient
    AND o.Code = '4548-4';

What does this query do?

The WHERE o.Code = '4548-4' filters observations to only HbA1c lab results. 4548-4 is the LOINC code for "Hemoglobin A1c/Hemoglobin.total in Blood."

The query joins your diabetic patient cohort to patients for names, then to observations for lab values.

Before you run this, do you think you will get exactly one row per patient, or could some patients appear more than once?

Look at the results. You will see the same patient name appearing on multiple rows, each with a different test date. A patient tested three times over two years shows up as three rows.

This is the duplicate-row problem

A direct JOIN gives you every HbA1c observation for every diabetic patient. You cannot determine the single most recent test without additional logic. This is exactly the gap we will close next.

Fix duplicates with ROW_NUMBER()

The ROW_NUMBER() window function assigns a sequential number to each row within a group. By partitioning by patient and ordering by date descending, row number 1 is always the most recent test.

  • Create a new file called 03_latest_hba1c.sql in your project folder.
  • Write the two CTEs that define your diabetic cohort and rank HbA1c tests:
-- Find each diabetic patient's most recent HbA1c test
WITH diabetic_patients AS (
    SELECT DISTINCT c.Patient
    FROM conditions c
    WHERE c.Description LIKE '%Diabetes%'
      AND c.Stop IS NULL
),
hba1c_tests AS (
    SELECT
        o.Patient,
        o.Date,
        o.Value,
        o.Units,
        ROW_NUMBER() OVER (
            PARTITION BY o.Patient
            ORDER BY o.Date DESC
        ) AS rn
    FROM observations o
    WHERE o.Code = '4548-4'
)

What does ROW_NUMBER() do here?

PARTITION BY o.Patient restarts the numbering for each patient. ORDER BY o.Date DESC puts the newest test first.

The result: row number 1 is always the patient's most recent HbA1c test. Row 2 is the second-most-recent, and so on. When you filter to rn = 1 in the final SELECT, you get exactly one row per patient.

  • Add this SELECT statement directly below the CTEs to complete the query:
SELECT
    p.Id AS patient_id,
    p.First,
    p.Last,
    p.BirthDate,
    p.Race,
    p.Gender,
    h.Date AS last_hba1c_date,
    h.Value AS last_hba1c_value
FROM diabetic_patients dp
JOIN patients p ON dp.Patient = p.Id
JOIN hba1c_tests h
    ON dp.Patient = h.Patient
    AND h.rn = 1;

What does this SELECT do?

It joins the diabetic cohort to patients for demographics, then to the ranked HbA1c tests. The AND h.rn = 1 condition ensures only the most recent test per patient is returned.

  • Save 03_latest_hba1c.sql.
  • Run the full query in your DuckDB CLI:
.read 03_latest_hba1c.sql

You should now see exactly one row per diabetic patient, each showing their most recent HbA1c date and value. No more duplicates.

Still seeing duplicates or an error?

Make sure the hba1c_tests CTE ends with a closing parenthesis ) and there is no trailing comma after it. The comma goes only between CTEs, not after the last one.

Also confirm AND h.rn = 1 is part of the ON clause for the hba1c_tests join, not a standalone WHERE.

Still stuck? Help me debug my ROW_NUMBER query in DuckDB

Include never-tested patients with a LEFT JOIN

Count the rows you just got and compare to your total diabetic cohort from Step 2. You will notice the numbers do not match. Patients who have never had an HbA1c test are excluded entirely by the regular JOIN.

These never-tested patients are actually the highest-priority care gaps. Converting to a LEFT JOIN keeps them in the results with NULL dates.

  • In 03_latest_hba1c.sql, find this line in the SELECT portion:
JOIN hba1c_tests h
  • Replace JOIN with LEFT JOIN, so the line now looks like this:
LEFT JOIN hba1c_tests h
  • Save 03_latest_hba1c.sql.
  • Before you re-run the query, predict: will the row count go up, go down, or stay the same compared to the JOIN version?
  • Run the updated query:
.read 03_latest_hba1c.sql

The row count goes up. You now see every diabetic patient. Patients who have never had an HbA1c test appear with NULL in the last_hba1c_date and last_hba1c_value columns.

Why are NULL patients the highest priority?

A patient overdue by 8 months at least had an HbA1c drawn at some point. A patient with NULL has never been screened at all. In the next step, you will categorize these urgency levels for the dashboard.

Not seeing any NULL rows?

Confirm you changed JOIN hba1c_tests h to LEFT JOIN hba1c_tests h. A regular JOIN discards patients with no matching HbA1c record. A LEFT JOIN keeps the left table (your diabetic cohort) intact and fills unmatched columns with NULL.

Need help? Explain the difference between JOIN and LEFT JOIN for my care gap query

✔️ Awesome, I've got everything!

Great. Confirm you have saved 03_latest_hba1c.sql and that your query returns one row per diabetic patient, including NULL dates for never-tested patients.

ⓧ I'd like to double check the full code

Your 03_latest_hba1c.sql should contain two sections you built above, in this order:

  • The comment line and two CTEs (diabetic_patients and hba1c_tests with ROW_NUMBER()).
  • The SELECT statement joining diabetic_patients, patients, and hba1c_tests with LEFT JOIN and h.rn = 1.

Scroll up and confirm each chunk matches what you pasted. The key details to verify:

  • The HbA1c LOINC code is '4548-4'.
  • ROW_NUMBER() is partitioned by o.Patient and ordered by o.Date DESC.
  • The final join is LEFT JOIN hba1c_tests h (not JOIN).
  • The ON clause includes AND h.rn = 1.

You now have exactly one row per diabetic patient with their most recent HbA1c date, or NULL if they have never been tested. Next up, you will calculate urgency categories and export the results for your Power BI dashboard.

Calculate Care Gaps and Export Results

You can now pull each diabetic patient's most recent HbA1c date. The next question is the one the health center hired you to answer: which patients are overdue for care?

In this step, you'll use CASE expressions and date arithmetic to categorize each patient by urgency. Then you'll export two CSV files that Power BI can consume directly.

In this step, get ready to:
  • Categorize patients by urgency using CASE and date arithmetic.
  • Assemble the complete care gap analysis as a single CTE chain.
  • Export patient-level detail and summary aggregation to CSV for Power BI.
Categorize patients with CASE and date arithmetic

A consultant doesn't just hand over raw dates. You need to translate "last tested on 2024-01-15" into a category an executive can act on: is this patient overdue or current?

You'll use two DuckDB functions together. date_diff calculates the number of days between two dates. CASE assigns a label based on that number.

  • Here is the care_gaps CTE you'll add to your query. Study the logic before you build the full file:
care_gaps AS (
    SELECT
        p.Id AS patient_id,
        p.First,
        p.Last,
        date_diff('year', p.BirthDate::DATE, CURRENT_DATE) AS age,
        p.Race,
        p.Gender,
        h.Date AS last_hba1c_date,
        h.Value AS last_hba1c_value,
        date_diff('day', h.Date::DATE, CURRENT_DATE) AS days_since_test,
        CASE
            WHEN h.Date IS NULL THEN 'Never Tested'
            WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 365 THEN 'Overdue > 12 months'
            WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 180 THEN 'Overdue 6-12 months'
            ELSE 'Current'
        END AS urgency_category
    FROM diabetic_patients dp
    JOIN patients p ON dp.Patient = p.Id
    LEFT JOIN hba1c_tests h
        ON dp.Patient = h.Patient
        AND h.rn = 1
)

What does this CASE logic do?

  • date_diff('day', h.Date::DATE, CURRENT_DATE) counts how many days have passed since the patient's last HbA1c test.
  • If h.Date is NULL (the LEFT JOIN found no test), the patient has never been tested. That is the highest-priority gap.
  • Over 365 days means more than a year overdue. Between 180 and 365 means 6 to 12 months overdue. Anything else is current.
  • The CASE evaluates top to bottom and stops at the first match, so order matters.
Build and run the complete analysis query

Now you'll assemble the full query: the two familiar CTEs from Step 3 plus the new care_gaps CTE. You'll paste this directly into the DuckDB CLI in two parts.

  • In your DuckDB CLI, paste the first part of the query (the two familiar CTEs). DuckDB will not execute until it sees a semicolon:
-- Calculate care gap urgency and export
WITH diabetic_patients AS (
    SELECT DISTINCT c.Patient
    FROM conditions c
    WHERE c.Description LIKE '%Diabetes%'
      AND c.Stop IS NULL
),
hba1c_tests AS (
    SELECT
        o.Patient,
        o.Date,
        o.Value,
        ROW_NUMBER() OVER (
            PARTITION BY o.Patient
            ORDER BY o.Date DESC
        ) AS rn
    FROM observations o
    WHERE o.Code = '4548-4'
),

Why is DuckDB waiting?

DuckDB's CLI buffers multi-line input until it encounters a semicolon. Since this chunk ends with a comma (the CTE chain continues), DuckDB knows more is coming and waits for you to paste the rest.

  • Immediately paste the second part (the care_gaps CTE and final SELECT) below the first:
care_gaps AS (
    SELECT
        p.Id AS patient_id,
        p.First,
        p.Last,
        date_diff('year', p.BirthDate::DATE, CURRENT_DATE) AS age,
        p.Race,
        p.Gender,
        h.Date AS last_hba1c_date,
        h.Value AS last_hba1c_value,
        date_diff('day', h.Date::DATE, CURRENT_DATE) AS days_since_test,
        CASE
            WHEN h.Date IS NULL THEN 'Never Tested'
            WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 365 THEN 'Overdue > 12 months'
            WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 180 THEN 'Overdue 6-12 months'
            ELSE 'Current'
        END AS urgency_category
    FROM diabetic_patients dp
    JOIN patients p ON dp.Patient = p.Id
    LEFT JOIN hba1c_tests h
        ON dp.Patient = h.Patient
        AND h.rn = 1
)
SELECT * FROM care_gaps;

What does the full query produce?

The three CTEs work together as a pipeline. diabetic_patients finds the cohort. hba1c_tests ranks each patient's lab records. care_gaps joins them together and applies the urgency logic.

The final SELECT * FROM care_gaps runs the entire chain and returns one row per diabetic patient with their urgency category.

Before you look at the results, predict: do you expect most patients to be Current, or do you think the majority will be overdue?

You should see a table with one row per diabetic patient. Each row includes patient_id, name, age, demographics, their last HbA1c date, days since that test, and an urgency_category column showing Never Tested, Overdue > 12 months, Overdue 6-12 months, or Current.

Seeing a syntax error?

  • Make sure the first paste ends with a comma after the closing parenthesis of hba1c_tests. The comma separates CTEs in the WITH chain.
  • Confirm the second paste starts with care_gaps AS ( directly, not with WITH or a duplicate comma.
  • Check that the semicolon appears only at the very end, after SELECT * FROM care_gaps.

help me debug my care gap CASE query in DuckDB

Export results to CSV for Power BI

Your query results live only in the CLI right now. Power BI needs CSV files it can import. DuckDB's COPY command exports query results directly to a file on disk.

  • Export the patient-level detail by pasting this first half into your DuckDB CLI:
-- Export patient-level detail
COPY (
    WITH diabetic_patients AS (
        SELECT DISTINCT c.Patient
        FROM conditions c
        WHERE c.Description LIKE '%Diabetes%'
          AND c.Stop IS NULL
    ),
    hba1c_tests AS (
        SELECT
            o.Patient,
            o.Date,
            o.Value,
            ROW_NUMBER() OVER (
                PARTITION BY o.Patient
                ORDER BY o.Date DESC
            ) AS rn
        FROM observations o
        WHERE o.Code = '4548-4'
    ),
  • Immediately paste the second half to complete the export command:
    care_gaps AS (
        SELECT
            p.Id AS patient_id,
            p.First,
            p.Last,
            date_diff('year', p.BirthDate::DATE, CURRENT_DATE) AS age,
            p.Race,
            p.Gender,
            h.Date AS last_hba1c_date,
            h.Value AS last_hba1c_value,
            date_diff('day', h.Date::DATE, CURRENT_DATE) AS days_since_test,
            CASE
                WHEN h.Date IS NULL THEN 'Never Tested'
                WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 365 THEN 'Overdue > 12 months'
                WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 180 THEN 'Overdue 6-12 months'
                ELSE 'Current'
            END AS urgency_category
        FROM diabetic_patients dp
        JOIN patients p ON dp.Patient = p.Id
        LEFT JOIN hba1c_tests h
            ON dp.Patient = h.Patient
            AND h.rn = 1
    )
    SELECT * FROM care_gaps
) TO 'care_gap_results.csv' (HEADER, DELIMITER ',');

What does COPY do here?

COPY wraps your entire query and writes its output to a file. The HEADER option includes column names as the first row. DELIMITER ',' creates a standard CSV that Power BI can read.

The query inside COPY must be self-contained, which is why you repeat the full CTE chain rather than referencing the earlier SELECT.

  • Verify the file was created by running:
ls care_gap_results.csv

You should see care_gap_results.csv listed. This file contains one row per diabetic patient with their urgency category.

Now export a summary aggregation that groups patients by urgency, race, and gender. This gives Power BI a pre-aggregated table for charts.

  • Paste the first half of the summary export:
-- Export summary aggregation
COPY (
    WITH diabetic_patients AS (
        SELECT DISTINCT c.Patient
        FROM conditions c
        WHERE c.Description LIKE '%Diabetes%'
          AND c.Stop IS NULL
    ),
    hba1c_tests AS (
        SELECT
            o.Patient,
            o.Date,
            o.Value,
            ROW_NUMBER() OVER (
                PARTITION BY o.Patient
                ORDER BY o.Date DESC
            ) AS rn
        FROM observations o
        WHERE o.Code = '4548-4'
    ),
  • Paste the second half to complete the summary export:
    care_gaps AS (
        SELECT
            p.Race,
            p.Gender,
            CASE
                WHEN h.Date IS NULL THEN 'Never Tested'
                WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 365 THEN 'Overdue > 12 months'
                WHEN date_diff('day', h.Date::DATE, CURRENT_DATE) > 180 THEN 'Overdue 6-12 months'
                ELSE 'Current'
            END AS urgency_category
        FROM diabetic_patients dp
        JOIN patients p ON dp.Patient = p.Id
        LEFT JOIN hba1c_tests h
            ON dp.Patient = h.Patient
            AND h.rn = 1
    )
    SELECT
        urgency_category,
        Race,
        Gender,
        count(*) AS patient_count
    FROM care_gaps
    GROUP BY urgency_category, Race, Gender
    ORDER BY urgency_category, patient_count DESC
) TO 'care_gap_summary.csv' (HEADER, DELIMITER ',');

How is the summary different from the detail?

The patient-level export has one row per patient. The summary export uses GROUP BY and count(*) to collapse patients into counts by urgency category, race, and gender.

This pre-aggregated format loads faster in Power BI and is ideal for bar charts and matrices.

Before you check, predict: do you expect the summary CSV to have more or fewer rows than the patient-level detail?

  • Confirm both files exist by running:
ls *.csv

You should see both care_gap_results.csv and care_gap_summary.csv listed. The summary file has far fewer rows because it aggregates patients into groups.

Don't see the CSV files?

  • DuckDB writes files relative to the directory where you launched the CLI. If you started DuckDB from your project folder, the CSVs appear there. Check with ls in a separate terminal window.
  • If you see a permissions error, make sure you are not writing to a read-only directory. Try launching DuckDB from your Desktop or project folder.
  • Confirm the COPY statement ended with a semicolon. Without it, DuckDB is still waiting for more input.

help me troubleshoot why DuckDB COPY is not creating my CSV file

  • Save all the SQL you ran in this step to a file called 04_care_gaps_and_export.sql in your project folder for your records.

Keep your SQL files organized

Saving each step's queries to a numbered SQL file creates a replayable audit trail. A senior reviewer or future employer can read your methodology from file 01 through 04 and understand your thought process.

✔️ Awesome, I've got everything!

Great. Confirm you have saved 04_care_gaps_and_export.sql and both CSV files are in your project folder.

ⓧ I'd like to double check the full code

Your 04_care_gaps_and_export.sql file should contain the following sections in order. Scroll up and confirm each is present:

  • The opening comment and analysis query (WITH three CTEs ending with SELECT * FROM care_gaps;).
  • The patient-level COPY export writing to care_gap_results.csv.
  • The summary COPY export with GROUP BY writing to care_gap_summary.csv.

You now have two clean CSV files ready for Power BI: one with every patient's urgency status, and one pre-aggregated for charts. Next up, you'll turn these numbers into a dashboard that a health center executive can actually use to take action.

Build the Power BI Dashboard

Your SQL analysis is complete, with two CSV exports that quantify exactly which diabetic patients are overdue for HbA1c testing. Now it's time to make those findings actionable.

A health center executive cannot act on a spreadsheet of patient rows. They need visuals that reveal the scale of the problem at a glance, spotlight demographic disparities, and direct outreach priorities.

In this step, get ready to:
  • Load your exported CSV files into Power BI Desktop as data sources.
  • Build three visuals: a KPI card, a stacked bar chart, and a demographic breakdown matrix.
  • Frame your dashboard with a title and executive recommendations.
Load your CSV files into Power BI

Power BI Desktop reads CSV files directly with no database connection required. You will load both your patient-level detail and summary aggregation files as separate data tables.

  • Press the Windows key to open your search bar.
  • Type Power BI Desktop and press Enter to open it.

Seeing a splash screen or sign-in prompt?

Power BI Desktop may show a welcome screen or ask you to sign in when it first opens. You can close the welcome screen or click Skip if prompted to sign in. A Microsoft account is not required for local reports.

  • In the Home ribbon, click Get Data.
  • Select Text/CSV from the data source list.
  • Navigate to your project folder and select care_gap_results.csv.
  • Click Open.

A preview window appears showing your patient-level data with columns like patient_id, urgency_category, and Race.

  • Click Load to import the data.
  • Click Get Data in the Home ribbon again.
  • Select Text/CSV again.
  • Select care_gap_summary.csv from your project folder.
  • Click Open, then click Load.

Switch to the Data view by clicking the table icon in the left sidebar. You should see both care_gap_results and care_gap_summary listed in the Data pane on the right.

Don't see your tables listed?

Make sure you clicked Load (not Transform Data) in the preview window. If you accidentally opened the Power Query Editor, click Close & Apply in the Home ribbon to load the data.

If the file paths are wrong, remove the failed query in the Data pane and repeat the Get Data steps above. Help me load CSVs into Power BI Desktop

Build the three core visuals

Each visual answers one executive question: how big is the problem, what does the breakdown look like, and which demographics are most affected.

  • Switch to Report view by clicking the chart icon in the left sidebar.
  • Click an empty area on the canvas to deselect any existing visual.
  • In the Visualizations pane, select the Card visual icon.
  • In the Data pane, expand care_gap_results and drag patient_id into the Values field well.
  • Power BI defaults to Count for text fields, showing the total patient count. Expand the Filters pane on the right.
  • Under Filters on this visual, drag urgency_category from the Data pane into the filter area.
  • Uncheck Current so only overdue patients are counted.

Your card now shows a single number representing the total overdue diabetic patients. Drag the card to the top-left corner of the canvas and resize it to be prominent.

Why filter out Current?

The urgency_category column has four values: Never Tested, Overdue > 12 months, Overdue 6-12 months, and Current. By excluding Current, the card counts only patients who need outreach.

  • Click an empty area on the canvas to deselect the card.
  • In the Visualizations pane, select the Stacked bar chart icon.
  • From the Data pane, expand care_gap_summary and drag urgency_category to the Y-axis well.
  • Drag patient_count to the X-axis well.

You should see four horizontal bars representing each urgency category. Position this chart below or beside the KPI card.

  • Click an empty area on the canvas to deselect the bar chart.
  • In the Visualizations pane, select the Matrix visual icon.
  • From care_gap_summary, drag Race to the Rows well.
  • Drag Gender to the Columns well.
  • Drag patient_count to the Values well.
  • In the Filters pane under Filters on this visual, drag urgency_category and uncheck Current to show only overdue patients.

The matrix now shows overdue patient counts broken down by race and gender. Position it in the remaining space on your canvas.

Visuals showing blank or zero?

Make sure you are dragging fields from the correct table. The card uses care_gap_results, while the bar chart and matrix use care_gap_summary.

If the bar chart shows only one bar, confirm that urgency_category is on the Y-axis (not in the Legend). Help me fix my Power BI visuals

Add a title and executive framing

A dashboard without context is just a collection of charts. Adding a title and written recommendations turns it into a deliverable a health center director can act on.

  • In the Insert ribbon, click Text box.
  • Type Diabetic HbA1c Care Gap Analysis as the title.
  • Increase the font size to 20 or larger and bold it.
  • Position the text box at the top of the canvas above your visuals.
  • Click Text box in the Insert ribbon again to add a second text box.
  • Type 2-3 bullet-point recommendations based on what your data shows.

What should I write in the recommendations?

Look at the actual numbers in your visuals and write findings that drive action. For example:

  • "Priority outreach: X patients have never received an HbA1c test."
  • "Disparity: [demographic group] has the highest overdue rate."
  • "Recommendation: Schedule outreach calls for the Never Tested cohort first."

Replace the placeholders with the real numbers from your card and matrix. This is what makes the dashboard executive-ready.

  • Position the recommendations text box near the bottom or side of the canvas.
  • Save your report by pressing Ctrl+S.
  • Name the file care_gap_dashboard.pbix and save it in your project folder.

Before you look at the final result, think about this: what three pieces of information should a health center executive be able to find at a glance on your dashboard?

Look at your finished report. You should see a KPI card with the total overdue patient count, a stacked bar chart showing all four urgency categories, a matrix breaking down the overdue population by race and gender, and your title with executive recommendations framing the findings.

Your care gap analysis is now a complete deliverable. You have gone from raw CSV exports all the way to an executive-ready dashboard that a health center director can use to prioritize patient outreach and report to their board.

Secret mission

Provider Performance Comparison

Your dashboard tells the health center which patients are overdue. But can it tell them which providers need support? Add a provider-level performance comparison to help leadership identify clinicians with the highest rates of overdue patients.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally with no paid cloud services, so there are no ongoing costs.

Resources you used:

  • DuckDB CLI (installed via winget).
  • Project folder (C:\health-center-project) containing SQL files, DuckDB database, exported CSVs, and Power BI report.
  • Synthea 1K CSV dataset (in the data\ subfolder).

Keep everything running

No action needed. Choose this if you are still actively working on this project or plan to extend it further.

  • Your DuckDB database file, SQL queries, exported CSVs, and .pbix report remain in your project folder.
  • DuckDB stays installed and available from your terminal.
  • Everything is local, so nothing is costing you money while it sits on your machine.

Pause - I'll come back to this later

Free up disk space by removing the raw Synthea data while keeping your analysis work.

  • Delete the raw Synthea CSVs you no longer need by running this command in Command Prompt:
rmdir /s /q C:\health-center-project\data
  • Your exported results (care_gap_results.csv, care_gap_summary.csv, provider_performance.csv), SQL files, DuckDB database, and Power BI report are all preserved.
  • You can re-download the Synthea data later from synthea.mitre.org/downloads if needed.

Delete - I don't want to use this again

Remove all project resources and uninstall DuckDB to start completely fresh.

  • Delete the entire project folder by running this command in Command Prompt:
rmdir /s /q C:\health-center-project
  • Uninstall DuckDB by running:
winget uninstall DuckDB.cli
  • Your Power BI Desktop installation stays on your machine since you had it before this project.

Nice Work!

Nice Work!

You just completed a full analytics consulting engagement from start to finish. You received messy EHR data, defined a clinical quality measure in SQL, identified patients falling through the cracks, and delivered an executive-ready dashboard that a health center could act on today.

You've learned how to:

  • Write SQL using CTEs, JOINs, and window functions (ROW_NUMBER) to solve a real analytic problem against multi-table clinical data.
  • Define and calculate a clinical quality measure (HbA1c care gap) from scratch, categorizing patients by urgency using date arithmetic and CASE logic.
  • Export analysis results and build a Power BI dashboard with executive framing that connects data findings to operational recommendations.
  • Secret Mission: Built a provider performance comparison showing which clinicians have the most overdue patients.

Ready to quiz yourself?