Email Alerts for Failed Data Checks: Patterns, Templates, and Code

Email remains the most ubiquitous, auditable, and accessible notification channel for data reliability. Whether you’re a data engineer, analyst, or developer, getting email alerts for failed data checks can be the fastest way to catch freshness gaps, schema drift, broken pipelines, or unexpected metric swings before stakeholders notice.

This guide walks through practical patterns, sample queries, email templates, and code to implement robust email alerting for SQL and NoSQL systems. You’ll learn how to structure checks so failures are unambiguous, reduce alert fatigue, secure your messages, and build an end-to-end workflow you can trust.

What counts as a data check?

A “data check” is any assertion that must hold true for your data or pipeline to be considered healthy. Common examples:

• Freshness: Latest event/row timestamp within X minutes.
• Volume: Row count for a period above a minimum threshold.
• Completeness: Null rate for critical columns under a limit.
• Uniqueness: Primary keys are unique; no duplicates.
• Referential integrity: Orphaned foreign keys do not exist.
• Business thresholds: Revenue, conversions, or error rates within expected ranges.

Good checks are deterministic, stable, and have documented runbooks. They should either pass silently or produce a concise failure output that is immediately actionable.

Principle: “An alert you can’t act on is noise.” Include clear next steps in every alert.

Design checks that fail loudly (and predictably)

A reliable pattern is: a query returns zero rows when healthy and one or more rows when unhealthy. That makes it trivial to turn query output into alerts.

PostgreSQL example: daily volume too low

-- Fails if fewer than 500 orders were created in the last day
WITH stats AS (
  SELECT COUNT(*) AS c
  FROM orders
  WHERE created_at >= now() - interval '1 day'
)
SELECT now() AS ts, c AS actual, 500 AS min_expected
FROM stats
WHERE c < 500;

If the SELECT produces a row, the check failed. Your alert body can include actual and min_expected for immediate context.

MySQL example: null rate breach

-- Fails if more than 2% of today's signups have NULL email
WITH stats AS (
  SELECT SUM(email IS NULL) AS nulls, COUNT(*) AS total
  FROM signups
  WHERE created_at >= NOW() - INTERVAL 1 DAY
)
SELECT NOW() AS ts,
       nulls,
       total,
       (nulls / total) * 100 AS null_pct,
       2.0 AS max_pct
FROM stats
WHERE total > 0 AND (nulls / total) * 100 > 2.0;

MongoDB example: freshness breach

// Fails if latest event is older than 15 minutes
// Returns at most one document when failing
[{
  $sort: { ts: -1 }
}, {
  $limit: 1
}, {
  $project: {
    ts: 1,
    ageMinutes: { $divide: [{ $subtract: ["$$NOW", "$ts"] }, 1000 * 60] },
    maxAge: { $literal: 15 }
  }
}, {
  $match: { $expr: { $gt: ["$ageMinutes", "$maxAge"] } }
}]

These “fail-on-row” patterns are portable across schedulers and tools. The orchestration simply needs to email when the query returns results.

Turning failures into emails

You have many options to execute checks and send emails. Here’s a quick comparison to help you pick the right fit.

What to include in the email

An alert that lacks context triggers a Slack thread, not a fix. Include:

• Clear subject: [SEV2][DATA] Daily Orders Volume FAILED (prod)
• What failed: Check name + short description
• Where: Environment, database, table/collection, region
• Evidence: Thresholds, actual values, sample failing rows (limit)
• When: Check timestamp, window covered
• Runbook: Step-by-step remediation link
• Owner: Team or on-call rotation
• Noise controls: Mute/snooze instructions and duration

Plain-text template you can reuse

Subject: [SEV2][DATA] {{check_name}} FAILED ({{env}})

Check: {{check_name}}
Env: {{env}}
Location: {{db_type}} {{db_name}}.{{schema}}.{{object}}
Window: {{window_start}} → {{window_end}}

Threshold: {{threshold_expression}}
Observed: {{observed_values}}
Rows returned: {{row_count}} (showing up to 10)

Sample:
{{sample_rows_table}}

Next steps (Runbook): {{runbook_url}}
Owner: {{owner_group}}
Alert ID: {{alert_id}}

Mute this alert for 2h: {{mute_link}}

Prefer plain text or multipart emails to avoid client rendering issues. Use consistent keys so humans and tooling can parse your alerts.

A minimal end-to-end example (Postgres + Python)

The snippet below runs a failure query and emails if rows are returned. It uses environment variables for secrets and supports a simple rate limit.

import os, smtplib, ssl
import psycopg2
from email.message import EmailMessage

SQL = """
WITH stats AS (
  SELECT COUNT(*) AS c
  FROM orders
  WHERE created_at >= now() - interval '1 day'
)
SELECT now() AS ts, c AS actual, 500 AS min_expected
FROM stats
WHERE c < 500;
"""

def fetch_failures():
    conn = psycopg2.connect(os.environ['PG_DSN'])
    try:
        with conn.cursor() as cur:
            cur.execute(SQL)
            cols = [d[0] for d in cur.description] if cur.description else []
            rows = cur.fetchall()
            return cols, rows
    finally:
        conn.close()

def build_email(cols, rows):
    lines = []
    for r in rows[:10]:
        lines.append(" | ".join(str(v) for v in r))
    body = f"""
Check: Daily Orders Volume < 500
Env: prod
Rows returned: {len(rows)} (showing up to 10)\n\n""" + "\n".join(lines)

    msg = EmailMessage()
    msg['Subject'] = '[SEV2][DATA] Daily Orders Volume FAILED (prod)'
    msg['From'] = os.environ['ALERT_FROM']
    msg['To'] = os.environ['ALERT_TO']
    msg.set_content(body)
    return msg

if __name__ == '__main__':
    cols, rows = fetch_failures()
    if not rows:
        exit(0)

    # naive rate limit: skip if recent file exists
    import time, pathlib
    stamp = pathlib.Path('/tmp/orders_volume_last_alert.txt')
    if stamp.exists() and (time.time() - stamp.stat().st_mtime) < 1800:
        exit(0)

    msg = build_email(cols, rows)

    context = ssl.create_default_context()
    with smtplib.SMTP_SSL(os.environ['SMTP_HOST'], int(os.environ['SMTP_PORT']), context=context) as s:
        s.login(os.environ['SMTP_USER'], os.environ['SMTP_PASS'])
        s.send_message(msg)

    stamp.write_text(str(time.time()))

Schedule this via cron (e.g., every 15 minutes), and you have a functional, rate-limited email alert for a critical volume check.

Reduce noise without missing real incidents

• Deduplicate: Suppress repeats for a window unless the state changes or severity escalates.
• Group: Combine similar failures into a single summary email per run when possible.
• Dynamic thresholds: Use baselines (e.g., rolling means/percentiles) to reduce false positives from seasonality.
• Maintenance windows: Automatically mute during planned outages or backfills.
• Escalation policies: Email first, then page only if unresolved for N minutes or after M consecutive failures.
• Healthy signals: Send a daily “green” summary instead of per-run “pass” emails to cut noise while preserving visibility.

Deliverability and reliability tips

• Authenticate: Set up SPF, DKIM, and DMARC to avoid spam folders.
• Use a dedicated sender: alerts@yourdomain for better reputation and filtering.
• Failover channels: If email bounces or times out, fall back to a secondary SMTP or a webhook to Slack.
• Queue and retry: Persist failed sends and backoff exponential retries.
• Structured subjects: Encode severity and environment in the subject for inbox rules and routing.

Security and compliance

• Read-only access: Checks should run with least privilege, read-only credentials.
• Sanitize content: Don’t email PII or secrets. Summarize and link to a secured dashboard for details.
• Encrypt in transit: Use TLS/SSL for SMTP and database connections.
• Access control: Limit who can subscribe to sensitive alerts; rotate creds regularly.
• Link hardening: Sign alert links or require SSO to view incident dashboards.

Testing and observability

• Unit tests: Simulate data states; ensure the query returns rows on failure and none on success.
• Canary checks: Deliberately failing checks verify your end-to-end pipeline and email delivery.
• Metrics: Track check runtime, row counts returned, alert send latency, bounce rates.
• Audit trail: Log who received which alerts and when; store the failure payload securely.

End-to-end workflow blueprint

1. Define the SLO: Example: “Daily orders ≥ 500.”
2. Write a failure-first query: Returns rows if the SLO is violated.
3. Schedule the check: Cron, Airflow, or a mobile alerting tool at a sane interval.
4. Transform results: If rows > 0, template an email with thresholds, evidence, and a runbook link.
5. Send with resilience: Authenticated SMTP/API, retries, and logging.
6. Control noise: Dedup for 30 minutes; escalate after 3 consecutive failures.
7. Review weekly: Tune thresholds, remove stale checks, and add missing runbooks.

Example subject lines that route well

• [SEV2][DATA][prod] Orders Volume FAILED — 423 < 500
• [SEV3][DATA][stg] Signups Null Rate HIGH — 2.45% > 2.00%
• [SEV1][PIPE][prod] Freshness BREACH — events 28m late

Common pitfalls (and fixes)

• Flaky thresholds: Replace fixed cutoffs with percent-of-baseline or anomaly bands.
• Long-running checks: Add indexes or pre-aggregations; run checks on summary tables.
• Silent failures: Monitor the alerting pipeline itself. If no alerts are sent for N days, send a heartbeat message.
• Inbox floods: Consolidate per-table alerts into a single periodic digest.
• Context gaps: Always include the affected window, environment, and a runbook link.

When email is not enough

Email is ideal for investigations and audit trails, but some events require faster or noisier channels:

• Sev1 incidents: Add paging (phone/Push) when core business KPIs are at risk.
• High-churn environments: Use push notifications or chat webhooks for near-real-time triage.
• Compliance-driven teams: Keep email for records, but pair with dashboards and ticketing integrations for workflow.

Putting it all together

Start with two or three high-value checks (freshness, volume, and null rate on critical tables). Implement the failure-first query pattern, template your emails with consistent subjects, and add light deduplication. As you gain confidence, expand coverage, add baselines, and fold alerts into your incident workflow. The goal is to make email alerts for failed data checks boring: trustworthy signals that arrive quickly, tell you exactly what broke, and point to the fix.

If you prefer a lightweight way to schedule read-only checks across PostgreSQL, MySQL, and MongoDB and receive both email and push notifications, the iOS app Query Alert can help streamline that workflow while keeping connections encrypted and scoped.