Detect and Alert on Schema Drift

This skill designs schema drift detection systems for data engineering pipelines, monitoring, detecting, and alerting on schema changes across data sources, formats, and storage systems to prevent downstream pipeline failures. It classifies drift into five types - structural (column additions, deletions, reordering), type (data type changes), constraint (nullability, uniqueness, foreign-key changes), semantic (same structure but different meaning or format), and cardinality (significant shifts in distinct value counts) - detected via proactive continuous validation, reactive post-ingestion analysis, version-controlled baseline comparison, or statistical profiling.

It provides three detector implementations: a Python/pandas-based detector that extracts a schema fingerprint (columns, dtypes, null counts, a schema hash) and diffs it against a stored baseline to report added, removed, and type-changed columns with a severity rating; a SQL-based monitor using information_schema queries and a full outer join against stored baselines to flag column additions, removals, type changes, nullability changes, and position changes; and an Apache Spark detector that registers a baseline schema per table and compares field-by-field on each new batch, catching both structural changes and schema-merge incompatibilities.

Best practices for monitoring configuration cover setting different alert sensitivity per change type, whitelisting expected changes (like new optional columns), using different strategies for batch versus streaming data, and maintaining separate baselines per environment. Alert management covers a three-tier severity classification (critical for breaking changes, warning for compatible changes, info for metadata-only changes), routing to Slack/email/PagerDuty by severity, automatic baseline updates for approved changes, and a change-approval workflow. Integration patterns cover an Airflow sensor that halts the pipeline when drift is detected, and Great Expectations integration that auto-generates column-existence and data-type expectations from a reference DataFrame. Performance guidance covers sampling for large datasets, caching schema metadata, incremental detection on new partitions only, parallel analysis across workers, and hash-based fingerprinting for fast pre-comparison.

def _generate_schema_hash(self, df: pd.DataFrame) -> str:
    schema_str = json.dumps({
        'columns': sorted(df.columns.tolist()),
        'dtypes': sorted([(col, str(dtype)) for col, dtype in df.dtypes.items()])
    }, sort_keys=True)
    return hashlib.md5(schema_str.encode()).hexdigest()

Use this skill when building schema drift monitoring for a data pipeline - choosing a detector (pandas, SQL, or Spark) matched to your stack, classifying drift severity, configuring alerts, or integrating drift checks into Airflow or Great Expectations.

It is not a fit for data-quality validation beyond schema (value-range checks, business-rule validation) - it's scoped specifically to structural, type, constraint, semantic, and cardinality drift in the schema itself.

Inputs are a baseline schema (stored or derived from a reference dataset) and the current data batch or table to check. Outputs are a structured drift report (change type, column, old/new value, severity), CI-ready sensor code for Airflow, and auto-generated Great Expectations suites for ongoing validation.

Data engineers building pipeline reliability tooling who need concrete, working schema drift detectors for pandas, SQL, or Spark, plus integration patterns for halting pipelines on breaking drift (Airflow sensors) and generating validation suites automatically (Great Expectations) rather than building schema monitoring from scratch.