Healthcare Data Pipeline

Interactive report pages

Open the corresponding page in the interactive Power BI report:

• Executive overview
• Patient population
• Utilisation
• Claims & costs
• Readmissions (30‑day return)
• Pipeline quality

Links open the live report. Page navigation is available within Power BI; exact page anchors may vary by tenant.

1. Project objective

The project builds an automated healthcare analytics platform that ingests multi-table healthcare CSV data, validates it, stores it in DuckDB, transforms it with SQL and dbt, and prepares curated dimensional data for Power BI.

The platform is designed to demonstrate analytical engineering practices. Because the source is synthetic, all results are demonstrations and must not be interpreted as real-world clinical findings.

Business questions the platform will answer

• Which conditions have the highest prevalence?
• Which patient groups have the greatest healthcare utilisation?
• What is the average cost per encounter, and how much is covered by payers?
• Which organisations, providers, procedures, and medications generate the most activity or cost?
• What percentage of patients return within 30 days?
• How do inpatient, emergency, and outpatient encounters differ over time?
• Which patients have multiple chronic conditions?

2. Platform architecture

3. Data source

The data comes from Synthea, an open-source synthetic patient generator. Synthea generates realistic-but-not-real patient histories and can export CSV files covering healthcare encounters, diagnoses, medications, allergies, payers, providers, and other clinical events.

Source extract in this project: Synthea sample CSV export (April 2020). The supplied csv/ directory is retained as the immutable input source.

4. Data inventory

5. Data model and relationships

• patients, organizations, providers, and payers are reference entities that will become conformed dimensions.
• encounters is the central fact table for healthcare utilisation and claim cost analysis.
• Clinical event tables such as conditions, medications, procedures, immunizations, and observations connect to patients and usually to encounters.
• providers.ORGANIZATION links providers to organisations; encounter records link to patient, organisation, provider, and payer IDs.
• Financial analysis will use encounter claim cost, payer coverage, and calculated patient responsibility. Medication and procedure facts remain at their own grain to prevent double counting.

6. Work completed

7. Ingestion reconciliation demonstration

After the Python ingestion completed, the following command ran the project's reconciliation SQL against the persisted DuckDB database:

.venv/bin/python src/run_sql.py sql/analysis/check_raw_load.sql

The query compares three independent counts for each source table: the current raw table count, the source CSV count captured in the audit log, and the loaded count captured in the audit log. A table passes only when the current raw-table count equals the audited loaded count.

This verification demonstrates that the CSV inputs were loaded without row loss and that the audit log can be used to validate the raw data layer before dimensional modelling begins.

8. dbt staging and quality demonstration

dbt is configured to connect directly to the persisted DuckDB warehouse. The staging layer standardises all 16 raw source tables, converting identifiers, dates, timestamps, counts, and monetary values into analytics-ready types while retaining event grain.

The completed staging layer feeds the dimensional Power BI marts described below.

9. Dimensional mart demonstration

The marts schema now contains a Power BI-ready star schema. The four conformed dimensions provide population, organisation, provider, and payer attributes; encounter and clinical facts retain their source event grain to avoid double counting.

The mart layer has been exported as CSV for Power BI. Supplementary dashboard aggregates can be added later if a specific report measure needs them.

10. Airflow orchestration demonstration

Apache Airflow runs locally in Docker Desktop and automatically executes the four pipeline stages in dependency order: Python validation/loading, dbt transformations, dbt tests, and Power BI CSV export.

This verified run demonstrates the complete automated pipeline from synthetic Synthea CSV files to refreshed Power BI-ready CSV extracts.

11. Power BI semantic model preparation

The exported mart CSVs have been prepared in Power Query and are ready for report-page visual design. Keys remain text to preserve UUIDs, financial measures use fixed decimal numbers, count fields use whole numbers, and event timestamps were converted to date-only fields where required for relationships with dim_date.

Created DAX measures

Population and utilisation:

Total Patients = DISTINCTCOUNT(dim_patient[patient_id])
Total Encounters = COUNTROWS(fact_encounter)
Unique Patients = DISTINCTCOUNT(fact_encounter[patient_id])

Claims and cost:

Total Claim Cost = SUM(fact_encounter[total_claim_cost])
Payer Coverage = SUM(fact_encounter[payer_coverage])
Patient Responsibility = SUM(fact_encounter[patient_responsibility])
Average Cost per Encounter = DIVIDE([Total Claim Cost], [Total Encounters])

Clinical and medication cost:

Total Procedures = COUNTROWS(fact_procedure)
Procedure Cost = SUM(fact_procedure[base_cost])
Medication Cost = SUM(fact_medication[total_cost])
Medication Patient Responsibility =
SUM(fact_medication[patient_responsibility])

Readmissions and pipeline monitoring:

30-Day Returns =
CALCULATE(
    COUNTROWS(fact_readmission),
    fact_readmission[returned_within_30_days] = TRUE()
)

30-Day Return Rate =
DIVIDE([30-Day Returns], COUNTROWS(fact_readmission))

Latest Export Time = MAX(export_manifest[exported_at_utc])
Exported Table Count = DISTINCTCOUNT(export_manifest[table_name])
Total Exported Rows = SUM(export_manifest[row_count])

The semantic model and measures are complete. The remaining Power BI work is to arrange these measures and prepared fields into dashboard visuals, filters, and navigation.

12. Interview talking points

Why not use only SQL to export a CSV for Power BI?

SQL alone can create a CSV export, and that is appropriate for a one-off analysis. A dashboard, however, is a recurring data product. This project uses Python validation, DuckDB, dbt, and Airflow to make every refresh reliable, repeatable, and auditable.

The extra engineering is not for the CSV itself; it makes each dashboard refresh trustworthy, reproducible, and maintainable.

How is the dashboard updated when source data changes?

1. Replace or update the source files in csv/.
2. Trigger healthcare_pipeline in Airflow, or let its future schedule run.
3. Airflow validates and reloads raw data, rebuilds dbt models, runs tests, and refreshes data/csv_output/.
4. Refresh the Power BI Folder connector to load the updated dimensions and facts without rebuilding the report.

If validation or dbt tests fail, Airflow does not run the CSV export task. Power BI therefore keeps the previous successful export instead of receiving invalid data.

13. Next steps

1. Build Power BI dashboard visuals and pages for executive overview, patient population, utilisation, claims and costs, readmissions, and pipeline quality.
2. Add supplementary dashboard aggregate marts only when a report measure needs them.
3. Add Docker and dbt checks to GitHub Actions CI.