National Hospital: Azure ELT data warehouse
for a healthcare analytics scenario

A 13-activity ADF pipeline with a two-layer DQ suite

The whole pipeline runs on managed Azure services. Azure Data Factory hosts the pipeline definition. Azure Databricks runs the PySpark transformations. Azure Data Lake Storage Gen2 (accessed via WASB protocol) holds raw CSVs, processed Parquet, and a quarantine folder. Azure SQL Database stores the STG and EDW schemas. Azure Key Vault stores the storage account key so the Databricks notebook can access blob storage securely.

The pipeline shape is a fan-out / converge / validate pattern. One Databricks transformation runs first, producing six cleaned datasets in ADLS. Six Copy Data activities then fan out in parallel to load STG tables. Five EDW load scripts fan back out to load the warehouse. Finally, a single Script activity runs four data quality checks against the loaded EDW.

The pattern that matters here is separation of concerns. Databricks handles the messy work of reading CSVs with mixed quoting, filtering nulls, quarantining suspicious rows, and writing clean data with audit timestamps. ADF handles orchestration, dependency management, retries, scheduling, and monitoring. Azure SQL Database is the queryable analytics surface. Each service does what it does best.

A visibility-first cleaning layer and a four-check SQL safety net

The Databricks transformation notebook uses a helper function called clean_with_visibility() that replaces the common anti-pattern of silent dropDuplicates + na.drop(). Instead of quietly removing rows, it reports raw count, deduplication count, missing-critical-field count, and final count for every entity. It only drops rows missing genuinely critical fields (patient_id, record_id) rather than any null in any column, so a patient record with a missing address is preserved rather than silently discarded.

def clean_with_visibility(df, entity_name, critical_cols, quarantine=True,
                          original_raw_count=None, pre_filtered_count=0,
                          pre_filtered_reason=None):
    """Clean a DataFrame with full DQ visibility. Reports source-to-warehouse
    retention rate, quarantines dropped rows, and only drops on critical field
    nulls rather than any-null."""
    raw_count = df.count()
    true_raw = original_raw_count if original_raw_count is not None else raw_count

    deduped_df = df.dropDuplicates()
    duplicates_removed = raw_count - deduped_df.count()

    null_filter = F.lit(False)
    for col in critical_cols:
        null_filter = null_filter | F.col(col).isNull()

    dropped_df = deduped_df.filter(null_filter)
    cleaned_df = deduped_df.filter(~null_filter).withColumn("loaded_at", F.current_timestamp())

    if quarantine and dropped_df.count() > 0:
        dropped_df.write.mode("overwrite").csv(f"{storage_path}/quarantine/{entity_name}", header=True)

    print(f"[{entity_name}] Retention: {(cleaned_df.count() / true_raw * 100):.1f}% (source to warehouse)")
    return cleaned_df

Before this helper runs on medical_records, a date sanity filter identifies rows where discharge_date < admission_date and writes them to a quarantine folder. The filter caught 14 rows, all from a January 2024 cluster (dates like admission 2024-01-14 with discharge 2024-01-07). The pattern is too tight to be random. In a real hospital, this would trigger investigation with the source-system team about a specific window of data entry.

bad_dates_df = med_records_df.filter(
    F.col("discharge_date").isNotNull()
    & F.col("admission_date").isNotNull()
    & (F.col("discharge_date") < F.col("admission_date"))
)

if bad_dates_df.count() > 0:
    print(f"[medical_records] Date sanity: {bad_dates_df.count()} records with illogical dates")
    bad_dates_df.write.mode("overwrite").csv(
        f"{storage_path}/quarantine/medical_records_bad_dates", header=True
    )

med_records_df = med_records_df.filter(
    F.col("discharge_date").isNull()
    | F.col("admission_date").isNull()
    | (F.col("discharge_date") >= F.col("admission_date"))
)

The SQL side runs a four-check data quality safety net after all EDW loads succeed: row count parity between STG and EDW, primary key uniqueness on every table, foreign key orphan detection (facts referencing missing patients), and date sanity as a downstream check that upstream Databricks quarantine actually worked. Hard failures use THROW to fail the pipeline; soft warnings pass with alerts.

-- Catches the classic pipeline bug: Databricks dropped a patient,
-- but the medical records for that patient survived. Orphan facts
-- silently corrupt downstream analytics.
SELECT 'fact_med_record',
       (SELECT COUNT(*) FROM EDW.fact_med_record f
        LEFT JOIN EDW.dim_patient p ON f.patient_id = p.patient_id
        WHERE p.patient_id IS NULL)

IF @orphan_total > 0
BEGIN
    SET @error_count = @error_count + 1
    SET @error_message = 'ORPHAN FACTS DETECTED: ' +
        CAST(@orphan_total AS VARCHAR) +
        ' fact rows reference patients that do not exist in EDW.dim_patient.'
END

Real pipeline runs, real numbers, real analytics output

The pipeline runs end-to-end in about 2 minutes and 20 seconds on real Azure infrastructure. Every activity is logged in Data Factory's monitoring UI with per-activity start times, durations, and success or failure status. Below is a captured run from August 23, 2026.

The January 2024 anomaly

The monthly admission trend query surfaced 49 months of data (January 2020 through January 2024). Baseline admission volume is roughly 40 per month with modest seasonal variation. January 2024 stood out with only 28 admissions, meaningfully below the baseline. Interestingly, this is the same month where the DQ suite flagged 14 medical records with illogical discharge_date < admission_date values.

Two independent data quality signals converging on the same time period suggest a source-system issue during January 2024 rather than random noise. In a real hospital deployment, this pattern would trigger investigation with the source IT team about that specific window (potentially a data-entry system bug, a shift change with training gaps, or a data migration during that month). Neither the low admission count nor the bad dates would be visible without the pipeline surfacing them. This is the difference between a data pipeline that moves data and a data platform that produces insight.

What this project does not do (yet)