Developers rely on fast feedback loops. Alerts that surface data issues, anomalies, or opportunities keep teams responsive without living inside dashboards all day. This guide walks through building scheduled SQL query alerts for developers that are reliable, secure, low-noise, and easy to maintain.
We will cover when to schedule versus going event-driven, core design principles, SQL patterns for incremental checks, delivery options, performance and safety, testing, and a step-by-step checklist you can apply to your own environment.
When Scheduled Alerts Are the Right Fit
Not every alert needs a streaming pipeline. Scheduling shines when you want predictable, periodic evaluations that are simple to reason about and easy to control.
- Great fits: daily SLA checks, hourly metric thresholds, missing-data detection, reconciliation jobs, periodic quality rules, backfill monitoring, long-running trend watch.
- Possible fits: near real-time detection with minute-level polling (if load is acceptable).
- Poor fits: true real-time triggers, row-by-row notifications at scale, or scenarios requiring exactly-once processing without careful watermarking.
Event-driven patterns (CDC, triggers, message queues) can complement scheduled alerts. A common hybrid: stream critical changes immediately, schedule everything else to reduce complexity and cost.
Core Design Principles
- Idempotence: The same run should not produce duplicate alerts. Use deduplication keys and high-water marks.
- Incrementality: Scan only new or changed data using timestamps, IDs, or change flags, not entire tables.
- Time windows: Evaluate within bounded windows aligned to business periods (e.g., last 15 minutes, yesterday in UTC).
- Alert routing: Categorize by severity and audience (engineering, analytics, support). Avoid one channel for everything.
- Cool-downs: Throttle repeat alerts for the same issue to prevent noise.
- Observability: Log runs, row counts scanned, and alerts emitted. Keep a small ops table with last run status.
- Security: Use read-only connections, least privilege, and encrypted transport.
- Cost control: Index for your query filters. Consider running against a read replica.
Incremental Patterns That Actually Work
1) Timestamp watermark table
Maintain a metadata table that stores the last processed timestamp per alert. Each run reads only rows after that watermark, emits alerts, and advances the watermark atomically.
-- Metadata table for watermarks
CREATE TABLE IF NOT EXISTS alert_watermarks (
alert_key TEXT PRIMARY KEY,
last_ts TIMESTAMPTZ NOT NULL
);
-- Example: detect high-value orders since the last run
WITH cfg AS (
SELECT COALESCE((SELECT last_ts FROM alert_watermarks WHERE alert_key = 'high_value_orders'), '1970-01-01'::timestamptz) AS last_ts
),
new_orders AS (
SELECT o.id, o.customer_id, o.total_amount, o.created_at
FROM orders o, cfg
WHERE o.created_at > cfg.last_ts
AND o.total_amount >= 5000
)
SELECT * FROM new_orders;
-- After sending alerts, advance the watermark to the max created_at you processed.
-- Do this in a transaction to avoid races.
Advantages: portable, transparent, and easy to debug. Key considerations: time zones (store UTC), late-arriving records (consider a small overlap), and atomic updates so you never miss or double-process rows.
2) ID-based watermark
If your table uses a monotonic sequence (e.g., bigserial), track the last processed ID. This avoids time ambiguities.
-- Track the highest order ID seen
CREATE TABLE IF NOT EXISTS alert_watermarks_id (
alert_key TEXT PRIMARY KEY,
last_id BIGINT NOT NULL
);
WITH cfg AS (
SELECT COALESCE((SELECT last_id FROM alert_watermarks_id WHERE alert_key = 'failed_payments'), 0) AS last_id
),
new_failures AS (
SELECT p.id, p.order_id, p.error_code, p.created_at
FROM payments p, cfg
WHERE p.id > cfg.last_id AND p.status = 'failed'
)
SELECT * FROM new_failures;
This pattern ignores out-of-order timestamps and typically scans fewer rows if IDs are indexed by default.
3) Windowed aggregates for SLAs
For SLAs or quality gates, compute aggregates in a time window and compare to thresholds. Store dedupe keys for each emitted alert so you only notify once per period.
-- Example: alert if yesterday's order count drops below target
WITH metrics AS (
SELECT date_trunc('day', created_at) AS d, count(*) AS orders
FROM orders
WHERE created_at >= date_trunc('day', now() - interval '1 day')
AND created_at < date_trunc('day', now())
GROUP BY 1
)
SELECT d, orders
FROM metrics
WHERE orders < 1000;
Combine with a dedupe key like 'orders-below-target-YYYY-MM-DD' so you notify at most once per day for that specific deficit.
SQL Examples You Can Adapt
High-value order alerts
-- Emit alerts for new high-value orders; store a dedupe hash per order
WITH cfg AS (
SELECT COALESCE((SELECT last_ts FROM alert_watermarks WHERE alert_key = 'orders_high_value'), '1970-01-01')::timestamptz AS last_ts
), candidates AS (
SELECT o.id, o.customer_id, o.total_amount, o.created_at,
md5(o.id::text || '-high-value') AS dedupe_key
FROM orders o, cfg
WHERE o.created_at > cfg.last_ts
AND o.total_amount >= 5000
)
SELECT * FROM candidates;
Use the dedupe_key to avoid notifying twice if a retry happens.
Detect stalled pipelines
-- Alert if no rows landed in the last hour for a critical table
SELECT 'landing_missing' AS alert,
now() AS checked_at,
COUNT(*) AS rows_last_hour
FROM events
WHERE created_at >= now() - interval '1 hour'
HAVING COUNT(*) = 0;
Pair with a cool-down (e.g., do not re-alert for 2 hours) so you do not flood channels during an outage.
Simple anomaly check using rolling baseline
-- Compare last hour to 7-day rolling baseline for the same hour-of-day
WITH current_hour AS (
SELECT COUNT(*) AS c
FROM pageviews
WHERE ts >= date_trunc('hour', now())
AND ts < date_trunc('hour', now()) + interval '1 hour'
),
baseline AS (
SELECT AVG(cnt) AS avg_c, STDDEV_POP(cnt) AS sd_c
FROM (
SELECT date_trunc('hour', ts) AS h, COUNT(*) AS cnt
FROM pageviews
WHERE ts >= now() - interval '7 days'
GROUP BY 1
) t
)
SELECT ch.c AS current, b.avg_c, b.sd_c
FROM current_hour ch, baseline b
WHERE ch.c < (b.avg_c - 3 * COALESCE(b.sd_c, 0));
For production, refine the baseline by hour-of-day or weekday to reduce false positives.
Scheduling and Delivery Options
Pick a scheduler you already operate comfortably. The best tool is the one your team will maintain.
| Option | Pros | Cons | Good for |
|---|---|---|---|
| Cron or systemd timers | Simple, cheap, local control | Single host risk, manual scaling | Small teams, low volume |
| DB-native schedulers (e.g., pg_cron) | Close to data, fewer hops | Ops within the DB, permissioning | Postgres shops |
| Orchestrators (Airflow, Dagster) | Dependences, retries, visibility | Heavier footprint, learning curve | Data platform teams |
| Serverless schedulers (Cloud Scheduler, EventBridge) | Managed, scalable | Vendor lock-in, networking setup | Cloud-first stacks |
| CI pipelines (GitHub Actions on schedule) | Infra you already have | Not designed for heavy DB work | Lightweight jobs |
For delivery, email remains the universal fallback. For developer workflows, Slack, Microsoft Teams, and mobile push are faster. Always include a clear title, severity, a compact summary, and links to drill down.
Performance, Cost, and Safety
- Indexes: Index columns used in filters (e.g., created_at, status). Composite indexes can help (status, created_at).
- Partitioning: Time-based partitioning lets scans target the latest partitions only.
- Read replicas: Run alert queries against replicas to protect primaries, accepting slight replication lag.
- Bounded scans: Never full-scan large tables on a tight schedule. Use watermarks and LIMIT in exploratory runs.
- Query budgets: Consider statement timeouts to prevent runaway load.
- Read-only access: Ensure the connection user cannot write business tables. Restrict to specific schemas if possible.
- Secrets management: Store credentials in a secure vault; rotate regularly.
Testing and Noise Reduction
False positives kill trust. Treat alerts as products: test, iterate, and measure precision.
- Dry runs: Log would-be alerts to a table for a week before enabling notifications.
- Shadow channels: Send first to a private Slack channel for tuning.
- Deduplication: Emit a deterministic key per alert event and store it to prevent repeats.
- Cool-downs: Add a minimum time between alerts for the same entity.
- Severity: Use levels (info, warn, critical) and route appropriately.
- Observability: Track alert volume per type and false-positive rate to guide improvements.
-- Example: record emitted alerts for dedupe and auditing
CREATE TABLE IF NOT EXISTS emitted_alerts (
dedupe_key TEXT PRIMARY KEY,
alert_type TEXT NOT NULL,
payload JSONB NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Only emit if not already sent
WITH candidate AS (
SELECT md5(order_id::text || '-high-value') AS dedupe_key, order_id, total_amount
FROM orders
WHERE created_at > now() - interval '15 minutes' AND total_amount >= 5000
),
unsent AS (
SELECT c.*
FROM candidate c
LEFT JOIN emitted_alerts e USING (dedupe_key)
WHERE e.dedupe_key IS NULL
)
SELECT * FROM unsent; -- send these, then insert into emitted_alerts
Tip: prefer one alert per actionable issue. If an outage triggers 500 impacted rows, aggregate them into a single message with a link to details.
Implementation Checklist
- Define the outcome and audience: what action should a developer take?
- Choose scheduling cadence and channels (e.g., every 5 minutes to Slack for critical).
- Write the alert query with a watermark or bounded window.
- Create indexes and validate query plans.
- Add dedupe keys and an emitted_alerts log table.
- Run dry for a week; tune thresholds to reduce noise.
- Enable notifications with cool-downs and severity routing.
- Instrument runs: log duration, rows scanned, alerts emitted, and failures.
- Document: what it checks, schedule, owner, runbook for handling alerts.
- Review quarterly: thresholds, false positives, recipients, and coverage.
Common Pitfalls (and How to Avoid Them)
- Time zone drift: Store and compare in UTC, and label human-facing times with time zone.
- Daylight saving surprises: Use date_trunc and fixed UTC windows to prevent off-by-one-hour issues.
- Silent failures: Monitor the monitor. Add alerts for missed schedules and query errors.
- Chatter storms: Apply cool-downs and aggregate multiple instances into one message.
- Scanning too much: Watermarks, partitions, and limiting early scans are your friends.
- Lack of ownership: Every alert should have an owner and a runbook explaining triage and resolution steps.
A Quick Mobile-First Workflow
Many teams want alerts where they work: on the go. A practical pattern is to keep your SQL and scheduling where it lives best (database or orchestrator) and push summaries to mobile. Use concise titles, short bodies, and links to deeper context. If you prefer an iOS-first experience with secure, read-only checks and push/email delivery across PostgreSQL, MySQL, and similar systems, an app like Query Alert can help tie it together without building a custom stack.
Putting It All Together
Scheduled alerts are deceptively simple. The difference between noisy and trustworthy is in the details: tight windows, strong watermarks, dedupe and cool-downs, and clear ownership. Start with a single high-value check, instrument it, measure false positives, and iterate. Over time you can build a compact, reliable alerting layer that keeps developers informed without adding operational burden.
The goal is not just to be notified, but to be notified only when action is needed. Design your scheduled SQL query alerts with that principle in mind, and your team will thank you.