ETL, Data Quality & Governance

01 Problem — example scenario

A financial organization ingests data from multiple SQL Server systems and 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 (multiple SQL Server systems + flat files) 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 — ETL pipeline

Every stage writes to logging tables, and the validation + anomaly gate sits between the warehouse and the semantic layer so only trusted data is published.

04 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.

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

05 Data-quality scorecard

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

06 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. Illustrative data.

07 Solution demonstration

The example system combines pipeline engineering with governance:

• Validation checkpoints in ETL — row counts, null checks, referential integrity, schema drift.
• Automated reconciliation between source and warehouse.
• 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.

08 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.