Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.anomalyarmor.ai/llms.txt

Use this file to discover all available pages before exploring further.

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.
Custom SQL Monitor Workflow

​
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 CaseExample SQL
Zero-dollar transactionsSELECT COUNT(*) FROM orders WHERE total_amount = 0
Source-target reconciliationSELECT ABS(a.cnt - b.cnt) FROM (SELECT COUNT(*) cnt FROM source) a, (SELECT COUNT(*) cnt FROM target) b
Null percentageSELECT 100.0 * COUNT(*) FILTER (WHERE email IS NULL) / COUNT(*) FROM users
Duplicate detectionSELECT COUNT(*) FROM (SELECT id, COUNT(*) FROM events GROUP BY id HAVING COUNT(*) > 1) t
SLA complianceSELECT 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 asset discovery.

​
Threshold configuration

Custom SQL monitors use static bounds:
ConfigurationBehavior
Upper bound onlyAlert when value > upper bound
Lower bound onlyAlert when value < lower bound
Both boundsAlert when value > upper bound OR value < lower bound
Value at boundaryNo 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 Incidents 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

-- Use FILTER for conditional aggregates
SELECT COUNT(*) FILTER (WHERE status = 'failed')
FROM jobs
WHERE created_at > NOW() - 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.

​
Common Questions

​
When should I use a Custom SQL monitor instead of a built-in monitor?

Use Custom SQL when your check is a business rule the built-ins don’t cover, like zero-dollar transactions, cross-table reconciliation, or SLA compliance. Stick with freshness, row count, or metrics for those standard cases, they provide anomaly detection without any SQL.

​
What’s allowed in the SQL query?

Only SELECT and WITH (CTE) statements. INSERT, UPDATE, DELETE, and DDL are rejected. The query must return at least one row with a numeric value in the first column.

​
Where does the query run?

It runs against your connected database, using the same credentials as asset discovery, not against AnomalyArmor’s internal database. Write queries the way you would in your warehouse’s SQL editor.

​
What happens if my query is slow?

Each run has a default 60-second timeout (300 seconds max). If your query times out, add time filters, use indexes, or reduce the scope. Target under 10 seconds for frequent checks.

​
Can I get alerts from Custom SQL monitors?

Yes. Threshold breaches fire alerts through the standard pipeline. Create an alert rule that matches the custom_sql event type and route it to Slack, email, webhook, or any configured destination.

​
Does the monitor support both upper and lower bounds?

Yes. Configure either or both. Setting both alerts if the value exceeds the upper bound or drops below the lower bound. Values at the boundary don’t alert, thresholds are inclusive.

Freshness Monitoring

Monitor data arrival timing automatically

Metrics

Track column-level statistics with anomaly detection