How do I set up an ETL assistant for detecting data issues in warehouse tables?

To set up an ETL assistant for detecting data issues in warehouse tables, design it as a data quality monitoring assistant that checks tables after loads and flags anomalies, schema drift, freshness problems, and record-level issues.

1) Define the assistant’s job

Have it monitor for common warehouse problems such as:

• Row count anomalies: sudden spikes/drops
• Null-rate changes: important columns becoming unexpectedly null
• Duplicate keys: repeated business keys or primary keys
• Schema drift: columns added/removed/type changes
• Freshness delays: tables not updated on time
• Value distribution shifts: unusual changes in metrics or categories
• Referential integrity issues: orphan rows between fact/dimension tables
• Outlier values: negative amounts, impossible dates, etc.

2) Put the checks in three layers

A good setup uses three layers of detection:

A. Load-level checks

Run right after ETL finishes:

• Did the job complete successfully?
• Did source and target row counts match expected tolerance?
• Did the load timestamp update?

B. Table-level checks

Run on each warehouse table:

• Row count vs. baseline
• Null rate by column
• Duplicate key checks
• Schema comparison vs. expected schema
• Min/max/range checks

C. Cross-table checks

Run across tables:

• Fact table rows match dimension keys
• Aggregates reconcile across staging and final tables
• Parent-child record consistency

3) Establish baselines

The assistant needs “normal” behavior to compare against.

Use:

• Static rules for hard constraints
  • order_id must be unique
  • customer_id cannot be null
  • amount >= 0
• Historical baselines for dynamic behavior
  • row count within ±20% of 30-day average
  • null rate below 2%
  • late-arriving records under threshold

Store baselines in:

• a metadata table
• a metrics history table
• a data quality config file in Git

4) Create a metadata-driven rules engine

Instead of hardcoding every table, define checks in config.

Example:

table: orders
checks:
  - type: row_count
    compare_to: 30_day_avg
    tolerance: 0.2
  - type: not_null
    columns: [order_id, customer_id, order_date]
  - type: unique
    columns: [order_id]
  - type: range
    column: amount
    min: 0
  - type: freshness
    max_age_minutes: 60

Your ETL assistant can read this config, execute the checks, and produce alerts.

5) Implement the assistant logic

A practical workflow:

1. Discover tables

   • read warehouse metadata
   • identify critical tables and columns

2. Collect metrics

   • row counts
   • null counts
   • distinct counts
   • min/max
   • last updated timestamp

3. Compare with expectations

   • apply rules and statistical thresholds

4. Classify issues

   • critical, warning, info
   • schema, freshness, volume, validity, consistency

5. Generate a report

   • what failed
   • how far from normal
   • likely cause
   • suggested next step

6. Notify

   • Slack, email, PagerDuty, Jira, or ticketing system

6) Use anomaly detection for smarter alerts

For noisy tables, rule thresholds may not be enough. Add anomaly detection for:

• seasonal row count patterns
• unusual null-rate increases
• distribution drift in numeric columns

Simple methods:

• z-score
• rolling average with standard deviation
• percent change vs. trailing 7/30 days

More advanced:

• isolation forest
• prophet-style forecasting
• drift metrics like PSI or KS test

7) Make alerts actionable

Don’t just say “table failed.” Include:

• table name
• check name
• expected vs actual
• severity
• time detected
• sample bad rows
• likely root cause if known
• query to investigate

Example alert:

orders.amount has 8.4% negative values, expected 0%.
Severity: critical
Detected after load etl_orders_2026_07_19_01.
Sample rows: 10 shown.
Suggested action: check currency conversion logic in staging pipeline.

8) Build an investigation layer

The assistant should help troubleshoot:

• show failing rows
• compare today vs yesterday
• identify source system changes
• inspect recent schema changes
• check ETL logs

Useful outputs:

• SQL snippets for debugging
• dashboard links
• lineage information
• last successful run

9) Choose the implementation stack

Common options:

• dbt tests for SQL-based validation
• Great Expectations for broader validation suites
• Soda for data quality monitoring
• Airflow/Prefect/Dagster to schedule and orchestrate checks
• Warehouse-native SQL for scalable checks
• LLM layer for summarizing failures and suggesting next steps

10) Recommended architecture

A simple architecture:

• ETL pipeline loads data
• Post-load quality job runs checks
• Metrics written to dq_metrics
• Failures written to dq_alerts
• Assistant reads results
• Assistant summarizes issues and sends notifications

11) Start with a minimal viable version

For v1, monitor:

• row count
• nulls
• uniqueness
• freshness
• schema drift

Then expand to:

• distribution drift
• referential integrity
• business rule validation
• automated root-cause suggestions

12) Example assistant behavior

The assistant can answer questions like:

• “Which tables failed today?”
• “Why did orders row count drop 40%?”
• “Show me columns with new null spikes.”
• “What changed in the schema since last load?”
• “Which upstream job likely caused the issue?”

13) Practical implementation pattern

A common pattern is:

• SQL/dbt for the checks
• Python service for orchestration and alert formatting
• Warehouse tables for storing metrics and results
• LLM for natural-language summaries

If you want, I can also provide:

1. a sample architecture diagram,
2. a dbt + Great Expectations setup, or
3. a Python example that scans warehouse tables and flags anomalies.