A recruiter-ready Azure Data Engineering project that transforms public Australian healthcare expenditure and population data into a governed Databricks Delta Lakehouse with Bronze, Silver, Gold, quality checks, SQL serving views, CI-backed data contracts, and Power BI analytics.

---

Executive Summary

This project demonstrates how public Australian Institute of Health and Welfare (AIHW) healthcare expenditure data can be engineered into a professional analytics lakehouse using Azure Data Factory, ADLS Gen2, Databricks, PySpark, Delta Lake, Spark SQL, GitHub Actions, and Power BI. It keeps the dataset truthful and portfolio-friendly while showing the engineering practices recruiters expect: clean repository structure, medallion architecture, reusable configuration, data contracts, quality checks, secure secret placeholders, curated Gold tables, and business-ready reporting outputs.

---

Why This Project Stands Out

Most portfolio data projects stop at a dashboard or a notebook. This project is structured like a real data engineering delivery:

• ADF orchestration assets organised into datasets, linked services, pipelines, triggers, and source configuration.
• Databricks medallion pipeline with dedicated Bronze, Silver, Gold, and Quality PySpark scripts.
• Real transformations including currency cleaning, financial-year keys, jurisdiction normalisation, population unpivoting, aggregation, joins, and per-person metrics.
• Delta Lake-ready design with clear paths for raw, bronze, silver, gold, and quality layers.
• Data quality checks for missing business keys, invalid measures, duplicate rows, failed population joins, and negative population.
• CI-backed data contracts using pytest and GitHub Actions.
• No hard-coded secrets; Azure Key Vault and Databricks secret placeholders are used.
• Power BI-ready Gold layer designed for healthcare expenditure analysis.
• Recruiter-friendly documentation with architecture, setup, data dictionary, SQL views, and CV bullets.

---

Project Screenshots

Complete Azure Data Engineering Architecture

Azure Data Factory Orchestration

ADLS Gen2 Lake Containers

Databricks Ingestion Workflow

Power BI Healthcare Expenditure Report

---

Complete Architecture

AIHW public source CSVs
        |
        v
Azure Data Factory
Extraction, parameterised copy, orchestration
        |
        v
ADLS Gen2 Raw Zone
Public health expenditure and population CSVs
        |
        v
Databricks Bronze Delta
Raw schema preserved + ingestion metadata
        |
        v
Databricks Silver Delta
Cleaned columns, typed measures, normalised population
        |
        v
Databricks Gold Delta
Healthcare expenditure aggregates + per-person metrics
        |
        v
Spark SQL Serving Views
Power BI-ready analytics layer
        |
        v
Power BI Dashboard
Healthcare expenditure trends and funding insights

Pipeline Flow

1. Source extraction - Azure Data Factory extracts public AIHW files into the raw data lake zone.
2. Bronze ingestion - Databricks reads raw CSV files, adds ingestion_timestamp and source_file, and writes Bronze Delta outputs.
3. Silver cleaning - PySpark standardises columns, converts money fields to numeric values, normalises categories, and unpivots population data.
4. Gold modelling - Expenditure is aggregated by year, jurisdiction, sector, area, and funding source, then joined with population.
5. Quality gate - Databricks writes quality results and raises failures for critical issues.
6. Serving layer - Spark SQL views provide curated outputs for Power BI.

---

Core Engineering Features

Feature	What It Demonstrates
ADF Export Structure	Cleanly organised pipelines, datasets, linked services, triggers, and source file configuration.
Bronze Delta Layer	Raw-to-Delta ingestion with metadata columns for lineage and observability.
Silver Delta Layer	Data cleaning, type casting, categorical normalisation, financial-year logic, and population unpivoting.
Gold Delta Layer	Business-ready aggregations for healthcare expenditure and expenditure-per-person analytics.
Quality Notebook	Missing key checks, duplicate checks, invalid value checks, failed join checks, and Delta quality output.
SQL Serving Views	BI-friendly Spark SQL views for sector, funding source, and detailed dashboard analysis.
Data Contracts	Local pytest checks for raw schemas, files, parseability, and secret-file hygiene.
GitHub Actions CI	Automated validation on pull request and push to main.
Power BI Asset	Existing report retained under powerbi/ for analytics presentation.
Documentation	Data dictionary, project inventory, setup guide, and CV-ready project bullets.

---

Tech Stack

Layer	Tools
Cloud Platform	Microsoft Azure
Orchestration	Azure Data Factory
Storage	Azure Data Lake Storage Gen2
Processing	Azure Databricks, PySpark
Lakehouse Format	Delta Lake
Analytics SQL	Spark SQL
Reporting	Power BI
Secrets Pattern	Azure Key Vault, Databricks secrets
CI and Testing	GitHub Actions, pytest
Language	Python

---

Data Source

The project uses public Australian healthcare expenditure and population data from the Australian Institute of Health and Welfare (AIHW). The source files are stored in data/raw/ and are kept as public-data inputs for this portfolio project.

Included raw files:

data/raw/australian_health_expenditure.csv
data/raw/australian_population_by_state.csv

---

Repository Structure

AU-Healthcare-Expenditure-Azure-Databricks-PySpark/
|
|-- azure-data-factory/
|   |-- datasets/
|   |-- linked_services/
|   |-- pipelines/
|   |-- triggers/
|   `-- source_file_list.json
|
|-- databricks/
|   |-- 00_setup/
|   |   `-- 00_mount_adls_storage.py
|   |-- 01_bronze/
|   |   `-- 01_bronze_ingestion_pyspark.py
|   |-- 02_silver/
|   |   `-- 02_silver_cleaning_validation_pyspark.py
|   |-- 03_gold/
|   |   `-- 03_gold_healthcare_aggregations_pyspark.py
|   |-- 04_quality/
|   |   `-- 04_quality_checks_pyspark.py
|   `-- includes/
|       `-- configuration.py
|
|-- data/
|   `-- raw/
|       |-- australian_health_expenditure.csv
|       `-- australian_population_by_state.csv
|
|-- docs/
|   |-- data_dictionary.md
|   |-- cv_project_bullets.md
|   `-- original_project_file_inventory.md
|
|-- powerbi/
|   `-- AU Health Expenditure.pbix
|
|-- sql/
|   `-- gold_healthcare_analytics.sql
|
|-- src/
|   |-- config.py
|   `-- quality_rules.py
|
|-- tests/
|   `-- test_raw_data_contracts.py
|
|-- .github/workflows/
|   `-- data-contract-checks.yml
|
|-- assets/
|   |-- architecture/
|   `-- screenshots/
|
|-- requirements.txt
|-- requirements-dev.txt
|-- Makefile
|-- .gitignore
|-- LICENSE
`-- README.md

---

Databricks Medallion Workflow

Bronze Layer

Raw CSV files -> Bronze Delta tables

The Bronze script reads the raw health expenditure and population files from ADLS, adds ingestion metadata, and writes Delta outputs.

Key file:

databricks/01_bronze/01_bronze_ingestion_pyspark.py

Silver Layer

Bronze Delta -> cleaned and validated Silver Delta

The Silver script standardises expenditure fields, converts currency strings into numeric values, creates an end-financial-year key, cleans population data, and unpivots state columns into a long analytics format.

Key file:

databricks/02_silver/02_silver_cleaning_validation_pyspark.py

Gold Layer

Silver Delta -> Power BI-ready Gold Delta

The Gold script aggregates expenditure by year, jurisdiction, sector, area, and source of funds. It joins expenditure with population and calculates expenditure per person.

Key file:

databricks/03_gold/03_gold_healthcare_aggregations_pyspark.py

Quality Layer

Silver + Gold Delta -> quality results Delta table

The Quality script checks missing years, missing jurisdictions, missing sectors, duplicate business rows, missing population joins, negative population, and negative expenditure signals.

Key file:

databricks/04_quality/04_quality_checks_pyspark.py

---

SQL Analytics Layer

The SQL file creates curated views for Power BI and business analysis:

sql/gold_healthcare_analytics.sql

Included views:

View	Purpose
vw_expenditure_by_sector	Compare expenditure across sectors, years, and jurisdictions.
vw_expenditure_by_funding_source	Analyse broad and detailed funding sources.
vw_powerbi_healthcare_summary	Detailed Power BI-ready healthcare analytics table.

---

Local Setup

git clone https://github.com/dineshbarri/healthcare-insights-lakehouse.git
cd healthcare-insights-lakehouse
python -m venv .venv

Activate the environment:

# Windows
.venv\Scripts\activate

# macOS / Linux
source .venv/bin/activate

Install test dependencies:

pip install -r requirements-dev.txt

Run data contract checks:

python -m pytest -q

Expected result:

4 passed

---

Azure and Databricks Deployment

1. Create or reuse an ADLS Gen2 storage account.
2. Create lake paths for raw, bronze, silver, gold, and quality.
3. Store credentials in Azure Key Vault.
4. Expose secrets to Databricks through a secret scope named au-health-kv.
5. Upload raw CSV files from data/raw/ to the ADLS raw zone.
6. Import or sync the databricks/ scripts into a Databricks workspace.
7. Run the scripts in this order:

00_setup/00_mount_adls_storage.py
01_bronze/01_bronze_ingestion_pyspark.py
02_silver/02_silver_cleaning_validation_pyspark.py
03_gold/03_gold_healthcare_aggregations_pyspark.py
04_quality/04_quality_checks_pyspark.py

8. Execute sql/gold_healthcare_analytics.sql.
9. Connect Power BI to the Gold Delta table or SQL serving views.

---

Data Quality and CI

This repository includes two quality layers:

Layer	Location	Purpose
Local contracts	tests/test_raw_data_contracts.py	Validates raw file existence, schema, parseability, and secret-file hygiene.
Databricks quality	databricks/04_quality/04_quality_checks_pyspark.py	Validates transformed lakehouse data and writes quality results to Delta.

GitHub Actions workflow:

.github/workflows/data-contract-checks.yml

Run locally:

python -m pytest -q

---

Business Impact

The Gold layer supports analysis of Australian healthcare expenditure by:

• Financial year
• State, territory, and national jurisdiction
• Healthcare sector
• Area of expenditure
• Broad source of funds
• Detailed source of funds
• Population-adjusted expenditure per person

This makes the reporting layer more meaningful than raw totals alone, especially when comparing jurisdictions with different population sizes.

---

Recruiter-Friendly CV Bullets

• Built an Azure Data Engineering lakehouse project using Azure Data Factory, ADLS Gen2, Databricks, PySpark, Delta Lake, Spark SQL, and Power BI.
• Developed Bronze, Silver, Gold, and Quality layers to transform public Australian healthcare expenditure and population datasets into analytics-ready Delta tables.
• Implemented PySpark transformations for financial-year standardisation, currency cleaning, jurisdiction normalisation, population unpivoting, aggregations, and expenditure-per-person metrics.
• Added Databricks data quality checks for missing dimensions, invalid measures, duplicate business keys, missing population joins, and negative population values.
• Created CI-backed raw data contracts with pytest and GitHub Actions to validate schema and quality before repository changes are merged.
• Produced professional documentation including architecture, setup, SQL serving views, data dictionary, screenshots, and Power BI assets.

---

Future Enhancements

• Add incremental ingestion using file tracking and Delta merge patterns.
• Add Delta Live Tables expectations or Great Expectations for richer quality reporting.
• Add Terraform or Bicep templates for repeatable Azure infrastructure.
• Add Databricks Asset Bundles for environment-specific deployments.
• Publish Power BI semantic model documentation and DAX measure definitions.
• Add cost monitoring and pipeline run observability dashboards.

---

Author

Built by Dinesh Barri

Data Analyst | AI Automation Engineer | Founder @ Plemdo AI

---

Support

If this project is useful, consider starring the repository. It helps recruiters, Azure learners, and data engineering teams discover the work.

---

License

This repository is available under the MIT License. See LICENSE for details.