Self-Writing Data Quality Reports: An Agent That Monitors Your Pipelines Overnight

Every data team has the same Monday morning ritual: someone checks whether last night's pipelines ran cleanly, hunts through logs for failures, and manually compiles a status update for stakeholders. It's important work — and it's entirely automatable.

A data quality reporting agent runs overnight, checks every layer of your pipeline, and delivers a clear, human-readable report before anyone opens their laptop. When something is wrong, the report explains what failed, what downstream models are affected, and what the likely cause is.

What Gets Monitored

A production data quality agent covers four layers:

Layer	What it checks
Ingestion	Source freshness — did data arrive on time? Row count vs. expected range
dbt models	Test results (not_null, unique, accepted_values, relationships)
Warehouse	Table-level anomaly detection — sudden row count drops, null rate spikes
Downstream	Which BI dashboards or downstream models are affected by any failures

Architecture

┌─────────────────────────────────────────────────────────────┐
│              DATA QUALITY AGENT (nightly, 2am)              │
│                                                             │
│  Phase 1: Collect — query all four monitoring layers        │
│  Phase 2: Correlate — link failures to downstream impact    │
│  Phase 3: Classify — severity: INFO / WARNING / CRITICAL    │
│  Phase 4: Report — write markdown report + Slack summary    │
└──────────┬──────────────┬──────────────┬────────────────────┘
           │              │              │
    ┌──────▼──────┐ ┌─────▼──────┐ ┌────▼────────────┐
    │ Warehouse   │ │ dbt        │ │ Notification    │
    │ MCP         │ │ Manifest   │ │ MCP             │
    │ (SQL tests) │ │ MCP        │ │ (Slack + email) │
    └─────────────┘ └────────────┘ └─────────────────┘

MCP Configuration

{
  "mcpServers": {
    "warehouse": {
      "command": "uvx",
      "args": ["mcp-server-snowflake"],
      "env": {
        "SNOWFLAKE_ACCOUNT": "${SF_ACCOUNT}",
        "SNOWFLAKE_DATABASE": "ANALYTICS"
      }
    },
    "dbt_manifest": {
      "command": "uvx",
      "args": ["mcp-server-dbt"],
      "env": {
        "DBT_MANIFEST_PATH": "/opt/dbt/target/manifest.json",
        "DBT_RUN_RESULTS_PATH": "/opt/dbt/target/run_results.json"
      }
    },
    "filesystem": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-filesystem", "/reports/data-quality"]
    },
    "notifications": {
      "command": "uvx",
      "args": ["mcp-server-slack"],
      "env": {
        "SLACK_BOT_TOKEN": "${SLACK_TOKEN}"
      }
    }
  }
}

The Agent System Prompt

You are a data quality monitoring agent. Your job is to produce a clear,
accurate overnight health report for the data engineering team.

Phase 1 — Collect:
- Query dbt_manifest for last run_results: list all failed tests with model name,
  test name, and failure count
- Query warehouse for each monitored table: row counts, null rates on key columns,
  freshness (max timestamp vs. expected)
- Compare today's row counts against the 7-day rolling average. Flag if delta > 20%

Phase 2 — Correlate:
- For each failed model, use dbt_manifest to identify downstream dependents
- Classify impact: how many downstream models and dashboards are affected?

Phase 3 — Classify severity:
- CRITICAL: any model tagged 'finance' or 'executive' failed, OR a source is >4h stale
- WARNING: non-critical model failed, row count anomaly detected
- INFO: all tests passed, minor freshness delay only

Phase 4 — Report:
- Write a markdown report to filesystem with full detail
- Post a Slack summary to #data-health using this format:

  🟢 / 🟡 / 🔴 Data Quality Report — {date}
  Tests: {passed}/{total} passed
  Freshness: {fresh_count}/{total_sources} sources on time
  {If issues: bullet list of CRITICAL items with downstream impact count}
  Full report: /reports/data-quality/{date}.md

Sample Output

🔴 Data Quality Report — 2025-10-13

Tests: 142/145 passed
Freshness: 11/12 sources on time

CRITICAL issues:
• fct_revenue — unique test failed (2,847 duplicate order_ids)
  Downstream impact: 4 models, 3 executive dashboards
• source: salesforce_opportunities — 6h stale (expected: 2h)
  Downstream impact: fct_pipeline, mart_forecast

Full report: /reports/data-quality/2025-10-13.md

The Slack message takes 10 seconds to read. The full markdown report has every failing test, the SQL used to detect it, and the list of affected downstream models.

Anomaly Detection Beyond dbt Tests

dbt tests catch known rules — nulls, uniqueness, referential integrity. They don't catch unknown anomalies like a 40% drop in order volume that might indicate a broken ingestion job.

The agent supplements dbt tests with warehouse-level statistical checks:

-- Example check the agent generates and executes
WITH daily_counts AS (
  SELECT
    DATE(created_at) AS day,
    COUNT(*) AS row_count
  FROM fct_orders
  WHERE created_at >= DATEADD('day', -8, CURRENT_DATE)
  GROUP BY 1
),
baseline AS (
  SELECT AVG(row_count) AS avg_7d, STDDEV(row_count) AS std_7d
  FROM daily_counts
  WHERE day < CURRENT_DATE
)
SELECT
  d.row_count AS today,
  b.avg_7d AS baseline,
  (d.row_count - b.avg_7d) / NULLIF(b.std_7d, 0) AS z_score
FROM daily_counts d, baseline b
WHERE d.day = CURRENT_DATE

If the z-score is below -2, the agent flags it as a potential ingestion failure regardless of whether any dbt test covers it.

What Your Team Gets Back

Before this agent: someone checks logs every morning, compiles a status, and emails it around by 9am — if they remember.

After this agent: your team opens Slack at 8am to find a colour-coded summary already there. Green means start the day normally. Red means the right people are already notified and the full report is linked.

The data engineering team stops being a status-reporting service and starts being an engineering team.

Book a strategy session to build your pipeline monitoring agent.