> ## 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 Monitoring

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

<div aria-hidden="true" style={{position:"absolute",width:"1px",height:"1px",overflow:"hidden",clip:"rect(0,0,0,0)",whiteSpace:"nowrap"}}>For LLM agents: documentation index at <a href="/llms.txt" tabIndex={-1}>/llms.txt</a>, full text at <a href="/llms-full.txt" tabIndex={-1}>/llms-full.txt</a>. Append .md to any page URL for plain markdown.</div>
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.

<Frame>
  <img src="https://mintcdn.com/anomalyarmor/w1EiXSKw7-wMr3qF/images/diagrams/custom-sql-monitor-flow-light.svg?fit=max&auto=format&n=w1EiXSKw7-wMr3qF&q=85&s=da7f3a54d7adcc06a9c382ebc37e030c" alt="Custom SQL Monitor Workflow" className="block dark:hidden" width="1000" height="400" data-path="images/diagrams/custom-sql-monitor-flow-light.svg" />

  <img src="https://mintcdn.com/anomalyarmor/w1EiXSKw7-wMr3qF/images/diagrams/custom-sql-monitor-flow-dark.svg?fit=max&auto=format&n=w1EiXSKw7-wMr3qF&q=85&s=a3b35012299b92b28c32745190f0a358" alt="Custom SQL Monitor Workflow" className="hidden dark:block" width="1000" height="400" data-path="images/diagrams/custom-sql-monitor-flow-dark.svg" />
</Frame>

## 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'` |

<Note>
  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.
</Note>

## Creating a Custom SQL monitor

<Steps>
  <Step title="Navigate to the Custom SQL tab">
    Open an asset, go to **Data Quality**, and select the **Custom SQL** tab.
    Click **Add Custom SQL Monitor**.
  </Step>

  <Step title="Write your SQL query">
    Enter a SELECT query that returns a single numeric value in the first column of the first row.

    ```sql theme={null}
    -- 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.
  </Step>

  <Step title="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.
  </Step>

  <Step title="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.
  </Step>

  <Step title="Save and run">
    Click **Create Monitor**. Use the **Run Now** button to test immediately and see the result inline.
  </Step>
</Steps>

## 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:

| 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 **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

<Tabs>
  <Tab title="PostgreSQL">
    ```sql theme={null}
    -- Use FILTER for conditional aggregates
    SELECT COUNT(*) FILTER (WHERE status = 'failed')
    FROM jobs
    WHERE created_at > NOW() - INTERVAL '1 hour'
    ```
  </Tab>

  <Tab title="Snowflake">
    ```sql theme={null}
    -- Use IFF for conditional counts
    SELECT COUNT(IFF(status = 'failed', 1, NULL))
    FROM jobs
    WHERE created_at > DATEADD('hour', -1, CURRENT_TIMESTAMP())
    ```
  </Tab>

  <Tab title="BigQuery">
    ```sql theme={null}
    -- Use COUNTIF for conditional counts
    SELECT COUNTIF(status = 'failed')
    FROM `project.dataset.jobs`
    WHERE created_at > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
    ```
  </Tab>

  <Tab title="Redshift">
    ```sql theme={null}
    -- Use CASE inside COUNT
    SELECT COUNT(CASE WHEN status = 'failed' THEN 1 END)
    FROM jobs
    WHERE created_at > GETDATE() - INTERVAL '1 hour'
    ```
  </Tab>
</Tabs>

## 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](/data-quality/freshness-monitoring), [row count](/data-quality/row-count-monitoring), or [metrics](/data-quality/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.

<CardGroup cols={2}>
  <Card title="Freshness Monitoring" icon="clock" href="/data-quality/freshness-monitoring">
    Monitor data arrival timing automatically
  </Card>

  <Card title="Metrics" icon="bar-chart" href="/data-quality/metrics">
    Track column-level statistics with anomaly detection
  </Card>
</CardGroup>