How do I set up data quality software to monitor schema drift and alert our data team on broken loads?

Here’s a practical way to set up data quality software to catch schema drift and broken loads and alert the right people fast.

1) Define what “bad” looks like

Start by listing the failures you care about:

• Schema drift
  • New columns appear
  • Columns disappear
  • Data types change
  • Nullability changes
  • Column order changes, if relevant
• Broken loads
  • Row count drops to zero unexpectedly
  • Load doesn’t arrive by SLA time
  • Partition/date is missing
  • Duplicate loads happen
  • Primary key uniqueness breaks
  • Referential integrity fails
  • Bad file format / truncated file / malformed records

2) Choose where to run checks

Common places:

• At ingestion time: best for catching bad files before they land
• After landing in raw/staging: best for validating ingestion success
• Before publishing to warehouse/marts: best for protecting downstream users

A good pattern is:

• Source/raw layer: schema and freshness checks
• Staging layer: row count, nulls, uniqueness, key checks
• Curated layer: business rules and reconciliation

3) Create a schema baseline

Most tools support some form of schema expectation. You want to store a known-good schema for each table/source.

Set checks for:

• Required columns exist
• No unexpected columns, or only allow them with warning
• Data types match expected types
• Column mode matches expected nullability

If you expect frequent changes, use tiers:

• Fail on removed columns or type changes
• Warn on new columns
• Ignore allowed-flexibility fields like JSON blobs

4) Add freshness and load-completeness checks

These are what usually catch broken loads.

Examples:

• File/table must arrive by 8:00 AM
• Partition for event_date = yesterday must exist
• Row count must be within expected range vs. previous day or 7-day average
• Source record count should match target within tolerance
• Latest updated_at must be within SLA window

A simple rule:

• Alert if row count is 0
• Alert if row count changes by more than, say, 50% day-over-day unless expected
• Alert if no new data for X hours

5) Validate business-critical data shapes

Beyond schema, add checks that detect “loaded but broken” data:

• Primary key uniqueness
• Non-null on required fields
• Valid date ranges
• Accepted value lists
• Foreign key existence
• Monetary values non-negative
• Event timestamps not in the future beyond tolerance

This helps catch loads that technically succeeded but contain corrupt content.

6) Configure alerting by severity

Don’t page everyone for every issue. Use levels:

• Critical / page
  • Load missing
  • Schema breaking change
  • Zero rows when data should arrive
  • Key constraints broken
• Warning / notify
  • New column added
  • Row count anomaly
  • Small null-rate increase
  • Delayed but not missing

Send alerts to:

• Slack / Teams for warnings
• PagerDuty / Opsgenie / email for critical issues

Include in the alert:

• Dataset/table name
• Check failed
• Expected vs actual
• Time detected
• Link to run logs / dashboard
• Suggested owner

7) Route alerts to the right owner

Map checks to teams by dataset/domain:

• Finance tables → Finance data team
• Product events → analytics engineering
• Ingestion pipelines → platform/data ops

Maintain an ownership registry:

• Dataset
• Owner team
• Primary contact
• Escalation channel
• SLA

8) Build baselines and anomaly thresholds

For row counts, freshness, and null rates, static thresholds often aren’t enough.

Use:

• Rolling averages
• Day-of-week patterns
• Seasonal baselines
• Percentile bands

Example:

• Alert if row count deviates more than 3 standard deviations
• Alert if null rate exceeds historical baseline by 20%
• Alert if load latency exceeds SLA by 30 minutes

9) Integrate into your pipeline

Typical integration points:

• Airflow / Dagster / Prefect / dbt after each job
• Run checks as a downstream task
• Block promotion to prod if critical checks fail
• Store results in a monitoring schema for auditing

A common flow:

1. Ingest data
2. Run schema checks
3. Run freshness/count checks
4. Run content checks
5. Alert on failures
6. Mark pipeline as failed or degraded

10) Keep a history of failures

Track:

• Failed checks
• Duration of failure
• Resolution time
• Root cause
• False positives

This helps tune thresholds and reduce alert noise.

11) Test the alerts

Before rollout:

• Simulate a missing column
• Simulate zero-row load
• Simulate late arrival
• Simulate type mismatch

Confirm:

• Alerts fire
• Severity is correct
• Right team is notified
• Alert contains enough context to act

12) Recommended implementation pattern

If you’re using a modern stack, a good setup looks like:

• Schema drift: compare incoming schema to stored contract/baseline
• Broken load detection: freshness + row count + file existence
• Data validation: business rules and constraints
• Alerting: Slack for warnings, PagerDuty for critical issues
• Orchestration: run checks automatically after ingestion jobs

Example rule set

For a table orders:

• Fail if order_id is missing
• Fail if order_amount < 0
• Fail if order_date absent for today’s partition
• Fail if schema changed in a breaking way
• Warn if new nullable columns are added
• Fail if row count is 0 or 80% below 7-day average
• Alert if load is more than 45 minutes late

Best practice

Use a combination of:

• Contract checks for schema
• SLA/freshness checks for missing loads
• Statistical checks for anomalies
• Business checks for correctness

If you want, I can also give you:

1. a tool-specific setup for Great Expectations / Soda / dbt tests / Deequ, or
2. a reference architecture showing where these checks fit in Airflow, Snowflake, and Slack/PagerDuty.