Custom SQL Monitoring

Monitor any business rule by writing SQL queries that return a numeric value, with configurable thresholds and alerts.

Custom SQL monitors let you define arbitrary data quality checks by writing SELECT queries against your connected databases. Each monitor returns a single numeric value that is compared against thresholds you configure.

When to use Custom SQL monitors

Custom SQL monitors are ideal when built-in monitors (freshness, row count, schema drift, metrics) do not cover your specific business rules:

Use Case	Example SQL
Zero-dollar transactions	SELECT COUNT(*) FROM orders WHERE total_amount = 0
Source-target reconciliation	SELECT ABS(a.cnt - b.cnt) FROM (SELECT COUNT(*) cnt FROM source) a, (SELECT COUNT(*) cnt FROM target) b
Null percentage	SELECT 100.0 * COUNT(*) FILTER (WHERE email IS NULL) / COUNT(*) FROM users
Duplicate detection	SELECT COUNT(*) FROM (SELECT id, COUNT(*) FROM events GROUP BY id HAVING COUNT(*) > 1) t
SLA compliance	SELECT AVG(EXTRACT(EPOCH FROM completed_at - created_at)) / 3600 FROM jobs WHERE created_at > NOW() - INTERVAL '1 day'

If your check is about freshness (is data arriving on time?), row counts (did volume change?), or column statistics (mean, null%, distinct count), use the dedicated built-in monitors instead. They provide richer baselines, anomaly detection, and require no SQL.

Creating a Custom SQL monitor

1. Navigate to the Custom SQL tab

   Open an asset, go to Data Quality, and select the Custom SQL tab. Click Add Custom SQL Monitor.

2. Write your SQL query

   Enter a SELECT query that returns a single numeric value in the first column of the first row.

   -- Example: Count orders with zero total in the last hour
   SELECT COUNT(*)
   FROM public.orders
   WHERE total_amount = 0
     AND created_at > NOW() - INTERVAL '1 hour'
   

   The query runs against your connected database with the same credentials used for discovery.

3. Set thresholds

   Configure one or both bounds:

   • Upper bound: Alert fires when the value exceeds this number
   • Lower bound: Alert fires when the value drops below this number

   For example, set upper bound to 10 to alert when more than 10 zero-dollar orders appear.

4. Choose a schedule

   Select how often the monitor runs: every 15 minutes, hourly, daily, or weekly. The schedule uses the same infrastructure as all other AnomalyArmor monitors.

5. Save and run

   Click Create Monitor. Use the Run Now button to test immediately and see the result inline.

SQL requirements

Your query must follow these rules:

1. Must be a SELECT or WITH (CTE) statement. INSERT, UPDATE, DELETE, DROP, and other DML/DDL keywords are rejected.
2. Must return at least one row with a numeric value in the first column.
3. Runs with a configurable timeout (default 60 seconds, maximum 300 seconds). Optimize your query if it times out.
4. Runs against your database, not AnomalyArmor’s internal database. The query uses the same connection and credentials as schema discovery.

Threshold configuration

Custom SQL monitors use static bounds:

Configuration	Behavior
Upper bound only	Alert when value > upper bound
Lower bound only	Alert when value < lower bound
Both bounds	Alert when value > upper bound OR value < lower bound
Value at boundary	No alert (boundaries are inclusive)

Viewing results

Each monitor execution stores:

• The numeric value returned by the query
• Pass/Fail status based on threshold comparison
• Execution time in milliseconds
• Timestamp of when the check ran

Historical results are available on the monitor detail page, showing value over time with threshold lines overlaid.

Alerts

When a threshold is breached, an alert flows through the standard AnomalyArmor alert pipeline:

1. Alert rules matching custom_sql event type are evaluated
2. Matching rules route to configured destinations (Slack, email, webhook)
3. The alert appears in the Alert Inbox alongside freshness, schema, and metric alerts

To set up alert routing, create an alert rule that includes the custom_sql event type.

Database-specific tips

PostgreSQL

-- Use FILTER for conditional aggregates
SELECT COUNT(*) FILTER (WHERE status = 'failed')
FROM jobs
WHERE created_at > NOW() - INTERVAL '1 hour'

Snowflake

-- Use IFF for conditional counts
SELECT COUNT(IFF(status = 'failed', 1, NULL))
FROM jobs
WHERE created_at > DATEADD('hour', -1, CURRENT_TIMESTAMP())

BigQuery

-- Use COUNTIF for conditional counts
SELECT COUNTIF(status = 'failed')
FROM `project.dataset.jobs`
WHERE created_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)

Redshift

-- Use CASE inside COUNT
SELECT COUNT(CASE WHEN status = 'failed' THEN 1 END)
FROM jobs
WHERE created_at > GETDATE() - INTERVAL '1 hour'

Best practices

• Keep queries fast. Target under 10 seconds. Use appropriate indexes and time filters.
• Return a single value. Multi-row results use only the first row’s first column.
• Use meaningful names. “Zero-Dollar Orders (Last Hour)” is clearer than “Custom Check 1”.
• Start with wide thresholds and tighten them as you learn normal ranges.
• Use the Run Now button to validate your query before relying on scheduled execution.

Freshness Monitoring

Monitor data arrival timing automatically

Metrics

Track column-level statistics with anomaly detection