Azure ETL, Data Quality & Governance

01 Problem — example scenario

A financial organization ingests data from an Oracle core-origination system, a SQL Server servicing platform, a PostgreSQL CRM, and partner flat files. Data arrives with inconsistencies, schema drift, and no automated quality checks or anomaly detection. Bad rows reach reporting before anyone notices, and QA is manual and reactive.

02 Constraints

• Sources are heterogeneous (Oracle, SQL Server, PostgreSQL + flat files), part on-prem, and schemas drift over time.
• Quality checks must be automated and logged — no manual spot-checking as the control.
• Anomalies must be caught before data reaches the semantic layer, not after.
• Governance requires lineage, department-level access (RLS), and SLA evidence.

03 Architecture — medallion lakehouse on Azure

The pattern is a medallion lakehouse. Raw extracts land untouched in Bronze on Azure Data Lake Storage (Parquet); they are cleansed, deduplicated, and conformed into Silver; then shaped into star-schema marts in Gold on an Azure Synapse dedicated SQL pool. The validation + anomaly gate sits between Silver and Gold, so only trusted data is ever published to the Power BI semantic layer.

04 Orchestration — the Azure Data Factory pipeline

One ADF pipeline, pl_ingest_to_gold, carries each source from staging to mart. A Copy activity (self-hosted IR for the on-prem Oracle and SQL Server sources) lands Parquet in Bronze; a Mapping Data Flow cleanses and conforms Bronze → Silver with SCD Type 2; a stored-procedure step builds the Gold marts in Synapse; and a Databricks/Python activity runs the anomaly gate before publish. Every activity writes run metrics to logging tables and Azure Log Analytics.

{
  "name": "pl_ingest_to_gold",
  "activities": [
    { "name": "Copy_Source_to_Bronze", "type": "Copy",
      "inputs": ["ds_oracle_origination"], "outputs": ["ds_adls_bronze_parquet"],
      "connectVia": "SelfHostedIR" },
    { "name": "DF_Bronze_to_Silver", "type": "ExecuteDataFlow",
      "dependsOn": ["Copy_Source_to_Bronze"],
      "typeProperties": { "dataFlow": "df_conform_scd2" } },
    { "name": "SP_Load_Gold_Marts", "type": "SqlServerStoredProcedure",
      "dependsOn": ["DF_Bronze_to_Silver"],
      "typeProperties": { "storedProcedureName": "gold.usp_load_fact_marts" } },
    { "name": "Validate_and_Gate", "type": "DatabricksNotebook",
      "dependsOn": ["SP_Load_Gold_Marts"],
      "typeProperties": { "notebookPath": "/dq/anomaly_gate" } }
  ]
}

05 Lift-and-shift — the legacy SSIS package

Not everything is rebuilt. A proven nightly package, nightly_extract.dtsx, still runs its original control flow — truncate staging, an OLE DB data flow with lookups and derived columns, then failure alerting — but now hosted on an Azure-SSIS Integration Runtime and triggered by the same ADF pipeline. Existing SSIS investment keeps running while new sources move to Mapping Data Flows.

06 AI anomaly detection — Python

An Isolation Forest scores each batch for outliers; flagged rows are quarantined and surfaced to the governance dashboard rather than silently loaded. The gate runs as an ADF Databricks activity; lightweight Shell scripts handle file landing and archival, and Power Query / M performs the same shaping for self-service refreshes in Power BI.

from sklearn.ensemble import IsolationForest
import pandas as pd

def score_batch(df: pd.DataFrame, features: list) -> pd.DataFrame:
    """Flag statistical outliers before load. Conservative contamination."""
    model = IsolationForest(
        n_estimators=200,
        contamination=0.01,
        random_state=42,
    )
    scores = model.fit_predict(df[features])
    df["is_anomaly"] = (scores == -1)
    return df

# flagged rows are written to dq.AnomalyLog and moved to a quarantine table

07 Data-quality scorecard

The pipeline scores four dimensions plus an anomaly rate; the composite feeds the governance dashboard and the SLA report (sample values).

08 Governance dashboard

A single pane consumes pipeline logs and anomaly output to show quality trends and SLA compliance. Filter by source, hover the charts for values, or select a source to drill through to the anomaly log. Sample data.

09 Solution demonstration

The example system combines Azure pipeline engineering with governance:

• ADF orchestration — one pipeline carries every source staging → transform → mart on a medallion lakehouse.
• SSIS on the Azure-SSIS IR — legacy packages run unchanged alongside new Mapping Data Flows.
• Validation checkpoints in ETL — row counts, null checks, referential integrity, schema drift.
• AI-based anomaly detection for unusual patterns, with quarantine.
• A governance dashboard showing quality trends, anomalies, lineage, and SLA compliance.
• Row-Level Security for department-level access on the Synapse-backed semantic model.

10 Outcome — demonstration

This example shows how combining ETL + quality + governance can, in practice:

• Improve trust in reporting by publishing only validated data.
• Catch anomalies early, before they reach executives.
• Provide audit-ready lineage and controls for reviewers.
• Reduce manual QA effort through automated checks and scoring.