--- updatedAt: 2026-09-26T00:06:24.000Z agentTools: projectIndex: https://docs.getmontecarlo.com/llms.txt --- # Custom SQL Monitor Run any SQL query on a schedule and alert when the result crosses a threshold. ## Overview Run any SQL query on a schedule and alert when the result crosses a threshold. The custom SQL monitor is your escape hatch when none of the purpose-built types (metric, validation, comparison) can express the check you need. ### **Reference scope** This page covers MaC YAML configuration. For UI-based creation and SQL templates, see [Custom SQL Monitors](https://docs.getmontecarlo.com/docs/creating-sql-rules). Your SQL must return exactly one row with one numeric column. Monte Carlo compares that value to the operator and threshold in your `alert_conditions` and opens an incident when the condition is met. ## Quick Start ```yaml montecarlo: custom_sql: - name: orphan_orders description: Alert when orphan orders exist warehouse: my-warehouse-name sql: | SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT id FROM customers) schedule: type: fixed interval_minutes: 720 alert_conditions: - type: threshold operator: GT threshold_value: 0 domains: - my-domain ``` Create interactively with `create_or_update_sql_monitor(dry_run=True)` via the [Monte Carlo MCP server](https://docs.getmontecarlo.com/docs/agent-toolkit). ### **MCP vs MaC naming** The MCP tool parameter is `alert_condition` (singular object), while MaC YAML uses `alert_conditions` (plural array). The tool wraps it automatically. The `warehouse` parameter takes a UUID — use `get_warehouses` to resolve a name. ## Configuration `string` · required Must return exactly one row with one numeric column. Avoid trailing semicolons and SQL comments (`--`, `/* */`) — some warehouses reject them when executed programmatically. Wrap NULLable results with `COALESCE`. ```yaml sql: | SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT id FROM customers) ``` `array of objects (exactly one entry)` · required Exactly one alert condition is required (`maxItems: 1`). Create separate monitors for multiple thresholds. **Available operators:** `EQ` · `NEQ` · `GT` · `GTE` · `LT` · `LTE` · `OUTSIDE_RANGE` · `INSIDE_RANGE` · `NOOP` · `AUTO` · `AUTO_HIGH` · `AUTO_LOW` AUTO operators (`AUTO`, `AUTO_HIGH`, `AUTO_LOW`) are only supported with `type: dynamic_threshold`, not with `type: threshold`. **Threshold types:** - **Static (**`threshold`**)** — fixed numeric value via `threshold_value` with an explicit operator. - **Range** — `lower_threshold` and `upper_threshold` with `INSIDE_RANGE` or `OUTSIDE_RANGE`. - **Dynamic threshold (**`dynamic_threshold`**)** — ML anomaly detection comparing current value against a rolling baseline. Accepts `AUTO`/`AUTO_HIGH`/`AUTO_LOW`, or explicit operators with a threshold relative to the baseline. - **Change (**`change`**)** — compares the current value against the previous run's value. Requires `is_threshold_relative`, `baseline_agg_function`, and `baseline_interval_minutes`. **Noise reduction:** Set `event_rollup_count` (min 2), `event_rollup_until_changed`, or `alert_grouping` at the monitor level to suppress notifications for subsequent breaches. These fields are mutually exclusive — only one can be set at a time. ### **UI name mapping** The UI uses different names for threshold types: **Automatic** = `dynamic_threshold`, **Absolute** = `threshold`, **Relative** = `change`. ```yaml alert_conditions: - type: threshold operator: GT threshold_value: 0 ``` **Properties** *** `type` — threshold type `enum` · optional · default: `threshold` **Accepted values:** `threshold` · `dynamic_threshold` · `change` · `absolute_volume` · `growth_volume` · `noop` ```yaml alert_conditions: - type: threshold ``` *** `operator` — comparison operator `enum` · required (for `threshold` and `change` types) **Accepted values:** `EQ` · `NEQ` · `GT` · `GTE` · `LT` · `LTE` · `OUTSIDE_RANGE` · `INSIDE_RANGE` · `NOOP` · `AUTO` · `AUTO_HIGH` · `AUTO_LOW` AUTO operators (`AUTO`, `AUTO_HIGH`, `AUTO_LOW`) are only supported with `type: dynamic_threshold`, not with `type: threshold`. ```yaml alert_conditions: - type: threshold operator: GT ``` *** `threshold_value` — static threshold `number` · required (for `threshold` and `change` types) ```yaml alert_conditions: - type: threshold operator: GT threshold_value: 0 ``` *** `lower_threshold` — lower bound for range operators `number` · optional Used with `OUTSIDE_RANGE` or `INSIDE_RANGE` operators. ```yaml alert_conditions: - type: threshold operator: OUTSIDE_RANGE lower_threshold: 100 upper_threshold: 1000 ``` *** `upper_threshold` — upper bound for range operators `number` · optional Used with `OUTSIDE_RANGE` or `INSIDE_RANGE` operators. ```yaml alert_conditions: - type: threshold operator: INSIDE_RANGE lower_threshold: 0 upper_threshold: 50 ``` *** `baseline_agg_function` — aggregation for baseline window `enum` · required (for `change` and `dynamic_threshold` types) **Accepted values:** `AVG` · `MIN` · `MAX` ```yaml alert_conditions: - type: change baseline_agg_function: AVG ``` *** `baseline_interval_minutes` — lookback window in minutes `integer` · required (for `change` and `dynamic_threshold` types) Range: 0–129600. ```yaml alert_conditions: - type: change baseline_interval_minutes: 10080 ``` *** `is_threshold_relative` — percentage vs absolute change `boolean` · required (when `type` is `change`) Set `true` for percentage change, `false` for absolute change. ```yaml alert_conditions: - type: change is_threshold_relative: true ``` *** `threshold_sensitivity` — sensitivity for dynamic thresholds `enum` · optional **Accepted values:** `low` · `medium` · `high` For `dynamic_threshold` type only. ```yaml alert_conditions: - type: dynamic_threshold threshold_sensitivity: medium ``` *** `min_buffer_value` — minimum buffer for dynamic threshold band `number` · optional ```yaml alert_conditions: - type: dynamic_threshold min_buffer_value: 10 ``` *** `max_buffer_value` — maximum buffer for dynamic threshold band `number` · optional ```yaml alert_conditions: - type: dynamic_threshold max_buffer_value: 100 ``` *** `min_buffer_modifier_type` — unit for min buffer `enum` · optional **Accepted values:** `METRIC` · `PERCENTAGE` ```yaml alert_conditions: - type: dynamic_threshold min_buffer_modifier_type: PERCENTAGE ``` *** `max_buffer_modifier_type` — unit for max buffer `enum` · optional **Accepted values:** `METRIC` · `PERCENTAGE` ```yaml alert_conditions: - type: dynamic_threshold max_buffer_modifier_type: PERCENTAGE ``` *** `number_of_agg_periods` — aggregation periods in baseline `integer` · optional Range: 1–1000. ```yaml alert_conditions: - type: dynamic_threshold number_of_agg_periods: 7 ``` *** `threshold_lookback_minutes` — lookback window for threshold computation `integer` · optional ```yaml alert_conditions: - type: dynamic_threshold threshold_lookback_minutes: 10080 ``` *** `is_percentage_threshold` — evaluate the threshold as a percentage `boolean` · optional (default: `false` ) Measures `threshold_value` as a percentage rather than a row count. With `is_percentage_threshold: true`, `threshold_value: 5` means 5%, not 5 rows. Requires: * `type: threshold` * `percentage_baseline_sql` * `threshold_value` of 0 or more * `query_result_type` of `ROW_COUNT`, or unset ```yaml alert_conditions: - type: threshold operator: GT threshold_value: 5 is_percentage_threshold: true percentage_baseline_sql: | SELECT COUNT(*) FROM orders WHERE status = 'completed' ``` *** `percentage_baseline_sql` — SQL counting the population the monitor evaluates `string` · **required when** `is_percentage_threshold` is `true`, **optional otherwise** Your `sql` returns only the rows that fail. The baseline sql query returns the total population those rows came from. It must return exactly one row with one numeric column. | If you want to... | Set | What happens | | ----------------------------------------------------------------------------- | ------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | Alert when a **percentage** of rows fail | `is_percentage_threshold: true` and `percentage_baseline_sql` | The baseline is used as the denominator that the percentage is measured against. | | Keep alerting on a **row count**, and record how many rows each run evaluated | `percentage_baseline_sql` only | Alerting is unchanged. Each run records a **Rows evaluated** count, so a breach reads "1,000 of 20,000" rather than "1,000". See [Measuring evaluated rows](https://docs.getmontecarlo.com/docs/creating-sql-rules#measuring-evaluated-rows) for more information. | Supported on `type: threshold` conditions only. Send an empty string to remove a stored baseline. The `percentage_` prefix is historical — the field isn't limited to percentage thresholds. ```yaml Baseline query for Percentage-based threshold alert_conditions: - type: threshold operator: GT threshold_value: 5 is_percentage_threshold: true percentage_baseline_sql: | SELECT COUNT(*) FROM orders WHERE status = 'completed' ``` ```yaml Baseline query for Row count threshold alert_conditions: - type: threshold operator: GT threshold_value: 5 percentage_baseline_sql: | SELECT COUNT(*) FROM orders WHERE status = 'completed' ``` **Recording evaluated row counts is opt-in.** It's off by default and enabled per account — see [Measuring evaluated rows](https://docs.getmontecarlo.com/docs/creating-sql-rules#measuring-evaluated-rows). Monitors with `query_result_type: SINGLE_NUMERIC` don't record row counts. Percentage thresholds do **not** require this setting and work without it. `object` · required Supported modes: `fixed`, `dynamic`, `manual`. Crontab (`interval_crontab`) is supported. Both the JSON Schema and the CLI require `schedule` — `apply` aborts if it is omitted. ```yaml schedule: type: fixed interval_minutes: 720 ``` **Properties** *** `type` — schedule type `enum` · optional · default: `fixed` **Accepted values:** `fixed` · `dynamic` · `manual` ```yaml schedule: type: fixed ``` *** `interval_minutes` — run interval for fixed schedules `integer` · optional Required when `type` is `fixed`. ```yaml schedule: type: fixed interval_minutes: 720 ``` *** `interval_crontab` — cron expressions `array of strings` · optional 5-field cron format. ```yaml schedule: type: fixed interval_crontab: - "0 8 * * *" ``` *** `interval_crontab_day_operator` — day-of-week/day-of-month combination `enum` · optional **Accepted values:** `AND` · `OR` ```yaml schedule: interval_crontab_day_operator: AND ``` *** `start_time` — ISO 8601 start time `string` · optional ```yaml schedule: start_time: "2024-01-01T08:00:00Z" ``` *** `timezone` — IANA timezone `string` · optional ```yaml schedule: timezone: America/New_York ``` *** `dynamic_schedule_tables` — tables that trigger the monitor `array of strings` · optional (required when `type` is `dynamic`) Custom SQL monitors support only one table in `dynamic_schedule_tables`. ```yaml schedule: type: dynamic dynamic_schedule_tables: - analytics:public.orders ``` *** `dynamic_schedule_jobs` — jobs that trigger the monitor `array of objects` · optional | Property | Type | Required | Description | | -------------- | ------ | -------- | ---------------------------------------------------- | | `job_type` | enum | yes | `AdfJob` · `AirflowDag` · `DatabricksJob` · `DbtJob` | | `job_name` | string | yes | Name of the job | | `project_name` | string | yes | Project containing the job | | `task_name` | string | no | Specific task within the job | | `mcon` | string | no | MCON identifier for the job | ```yaml schedule: type: dynamic dynamic_schedule_jobs: - job_type: AirflowDag job_name: etl_orders project_name: my-airflow ``` *** `min_interval_minutes` — minimum interval for dynamic schedules `integer` · optional ```yaml schedule: type: dynamic min_interval_minutes: 30 ``` `string` · required Displayed in the Monte Carlo UI and in incident notifications. Max 512 characters. ```yaml description: Alert when orphan orders exist ``` `array of strings (exactly one entry)` · required when creating a monitor Required on all accounts, regardless of when the account was created. Monitors that already exist without a domain can still be updated without adding one. Set `default_domain` in `montecarlo.yml` to avoid repeating it on every monitor. ```yaml domains: - my-domain ``` `string` · optional (yes if multiple warehouses) Warehouse name or UUID. Overrides `default_resource` from `montecarlo.yml`. ```yaml warehouse: my-warehouse ``` `string` · required Required for monitors created after Jan 29, 2024 (existing monitors keep working). Changing the name creates a new monitor and deletes the old one — incident history does not transfer. ```yaml name: orphan_orders ``` `string` · optional Overrides the default connection for this warehouse. ```yaml connection_name: analytics-readonly ``` `object` · optional · default: `{}` Variables are substituted into the `sql` field using `{{ variable_name }}` syntax. When variables are defined, Monte Carlo runs the SQL once per combination of variable values and evaluates each result independently. Two formats are supported: static (list of values) and runtime (object with `type: runtime` and `default`). ```yaml variables: schema_name: - analytics - staging ``` `string` · optional SQL returning sample rows on breach for incident context. Called "investigation query" in the UI. Runs only on breach — zero load on clean runs. ```yaml sampling_sql: | SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM customers) LIMIT 100 ``` `enum` · optional **Accepted values:** `ROW_COUNT` · `SINGLE_NUMERIC` `ROW_COUNT` evaluates the row count of the result set instead of the scalar value. ```yaml query_result_type: ROW_COUNT ``` `integer` · optional Minimum `2`. Mutually exclusive with `event_rollup_until_changed` and `alert_grouping`. ```yaml event_rollup_count: 3 ``` `boolean` · optional · default: `false` Roll up breaches until the result changes. Mutually exclusive with `event_rollup_count` and `alert_grouping`. ```yaml event_rollup_until_changed: true ``` `object` · optional · default: no grouping Groups subsequent breaches into the currently open alert rather than creating a new one, for as long as the alert remains open and unresolved. Mutually exclusive with `event_rollup_count` and `event_rollup_until_changed`. `mode` **Accepted values:** `group_into_open_alert` ```yaml alert_grouping: mode: group_into_open_alert ``` `enum` · optional **Accepted values:** `SEV-0` · `SEV-1` · `SEV-2` · `SEV-3` · `SEV-4` ```yaml severity: SEV-2 ``` `enum` · optional **Accepted values:** `P1` · `P2` · `P3` · `P4` · `P5` ```yaml priority: P2 ``` `array of strings or mappings` · optional · default: `[]` Audience names linking this monitor to channels defined in Notifications as Code. In exported/rendered YAML, appears as `labels`. An entry can instead be a mapping that narrows an audience to specific triage priorities, so it is only notified about alerts scored at those priorities. Some audience must cover `NOT_TRIAGED`. See [Routing alerts by triage priority](https://docs.getmontecarlo.com/docs/notifications-v2#routing-alerts-by-triage-priority). ```yaml audiences: - data-eng-oncall - platform-alerts - audience: oncall triage_priority: [HIGH] ``` `array of strings` · optional Separate audiences for run-failure notifications. Falls back to `audiences` when omitted. ```yaml failure_audiences: - data-eng-oncall ``` `boolean` · optional ```yaml notify_run_failure: true ``` `string` · optional ```yaml exception_primary_key_column: order_id ``` `integer` · optional ```yaml timeout: 300 ``` `array of objects` · optional · default: `[]` | Property | Type | Required | Description | | -------- | ------ | -------- | ----------- | | `name` | string | yes | Tag key | | `value` | string | no | Tag value | ```yaml tags: - name: team value: analytics - name: environment value: production ``` `enum` · optional **Accepted values:** `ACCURACY` · `COMPLETENESS` · `CONSISTENCY` · `TIMELINESS` · `UNIQUENESS` · `VALIDITY` ```yaml data_quality_dimension: CONSISTENCY ``` `string` · optional Visible in the Monte Carlo UI. Not included in notifications. ```yaml notes: Owned by the analytics team. Reviewed quarterly. ``` `boolean` · optional · default: `false` Creates the monitor in a paused state. Omitting this on a later update resets to `false` (active) due to PUT semantics — always include it if you want the monitor to stay in draft. ```yaml is_draft: true ``` `string` · optional Include the UUID of an existing monitor to update it instead of creating a new one. ```yaml uuid: 0dae7702-0950-45c7-909c-7e183bddca19 ``` `boolean` · optional Force auto-apply tuning on (`true`) or off (`false`) for this monitor, overriding the account or domain default. Omit to inherit. See [the Tuning Agent](https://docs.getmontecarlo.com/docs/tuning-agent#per-monitor-auto-apply-override). ```yaml auto_tuning: false ``` `boolean` · optional Force auto-triage on (`true`) or off (`false`) for this monitor, overriding the account or domain default. Omit to inherit. See [the Triage Agent](https://docs.getmontecarlo.com/docs/triage-agent#per-monitor-override). ```yaml auto_triage: false ``` | Field | Use instead | | ------------------------- | -------------------- | | `resource` | `warehouse` | | `domain` | `domains` | | `domain_uuids` | `domains` | | `labels` | `audiences` | | `notify_rule_run_failure` | `notify_run_failure` | | `comparisons` | `alert_conditions` | ### **API-only fields** Some fields visible in the API or JSON Schema (`skip_reset`, `fail_on_reset`, `metadata`) are present in the schema but silently stripped during MaC YAML processing. They have no effect and should not be included. ## Examples ### Threshold check — alert when orphan rows exist ```yaml montecarlo: custom_sql: - name: orphan_orders description: Detect orders referencing deleted customers warehouse: my-warehouse sql: | SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT id FROM customers) schedule: type: fixed interval_minutes: 720 alert_conditions: - type: threshold operator: GT threshold_value: 0 priority: P2 data_quality_dimension: CONSISTENCY audiences: - data-eng-oncall domains: - my-domain ``` ### Threshold check that also records how many rows were evaluated Alerts when any row fails, and records the size of the population on each run — so an incident reads "12 of 300,000" rather than "12". ```yaml montecarlo: custom_sql: - name: invalid_shipment_status description: Detect shipments with an unrecognized status warehouse: my-warehouse sql: | SELECT COUNT(*) FROM shipments WHERE status NOT IN ('pending', 'in_transit', 'delivered', 'cancelled') schedule: type: fixed interval_minutes: 720 alert_conditions: - type: threshold operator: GT threshold_value: 0 percentage_baseline_sql: "SELECT COUNT(*) FROM shipments" priority: P2 data_quality_dimension: VALIDITY audiences: - data-eng-oncall domains: - my-domain ``` Requires [evaluated row counts](https://docs.getmontecarlo.com/docs/creating-sql-rules#measuring-evaluated-rows) to be enabled on your account — see `percentage_baseline_sql`. Add `is_percentage_threshold: true` to alert on a percentage of the population instead of an absolute count. ### Percentage threshold — alert when a share of rows fails Useful when absolute counts are misleading because volume varies. 500 missing addresses matters on a slow day and not on Black Friday; a percentage holds either way. ```yaml montecarlo: custom_sql: - name: orders_missing_address description: Alert when more than 2% of orders have no shipping address warehouse: my-warehouse sql: | SELECT COUNT(*) FROM orders WHERE shipping_address IS NULL AND created_at >= CURRENT_DATE - 1 schedule: type: fixed interval_minutes: 1440 alert_conditions: - type: threshold operator: GT threshold_value: 2 is_percentage_threshold: true percentage_baseline_sql: | SELECT COUNT(*) FROM orders WHERE created_at >= CURRENT_DATE - 1 priority: P3 data_quality_dimension: COMPLETENESS audiences: - data-eng-oncall domains: - my-domain ``` `threshold_value: 2` means 2%, not 2 rows. ⚠️ **Match your filters.** The baseline query runs exactly as written. Here, both queries scope to `created_at >= CURRENT_DATE - 1`. Leaving the date filter off the baseline would compare yesterday's failures against every order ever written. ### Change detection — alert when metric changes more than 20% from baseline ```yaml montecarlo: custom_sql: - name: revenue_change description: Alert on significant daily revenue change warehouse: my-warehouse sql: | SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE created_at >= CURRENT_DATE - 1 AND created_at < CURRENT_DATE schedule: type: fixed interval_minutes: 1440 alert_conditions: - type: change operator: GT threshold_value: 0.2 is_threshold_relative: true baseline_agg_function: AVG baseline_interval_minutes: 10080 domains: - my-domain ``` ### Anomaly detection — alert when result deviates from learned baseline ```yaml montecarlo: custom_sql: - name: daily_revenue_anomaly description: Detect anomalous daily revenue using ML warehouse: my-warehouse sql: | SELECT COALESCE(SUM(amount), 0) FROM transactions WHERE created_at >= CURRENT_DATE - 1 AND created_at < CURRENT_DATE schedule: type: fixed interval_minutes: 1440 alert_conditions: - type: dynamic_threshold operator: AUTO baseline_agg_function: AVG baseline_interval_minutes: 10080 threshold_sensitivity: medium domains: - my-domain ``` ## Troubleshooting ### Alert conditions and thresholds * **AUTO operators not supported for **`threshold`** type.** AUTO operators (`AUTO`, `AUTO_HIGH`, `AUTO_LOW`) require `type: dynamic_threshold`. If your alert condition uses `type: threshold` (the default), switch to `type: dynamic_threshold` to use anomaly detection, or use an explicit operator (GT, LT, etc.). * **Incomplete **`change`** conditions.** `type: change` requires all four: `baseline_agg_function`, `baseline_interval_minutes`, `is_threshold_relative`, and `threshold_value`. Omitting `is_threshold_relative` produces: "`is_threshold_relative` is a required field." * **Multiple alert conditions.** Exactly one is required. Create separate monitors for multiple thresholds. * **Baseline set but no row counts appear.** Recording evaluated row counts is off by default and must be enabled on your account. * Also confirm `query_result_type` is `ROW_COUNT` or unset. * **Monitor started alerting differently after adding a baseline.** Check whether `is_percentage_threshold: true` was set alongside it. That switches `threshold_value` from an absolute count to a percentage — `threshold_value: 5` becomes 5%, not 5 rows. To record row counts without changing alerting, set `percentage_baseline_sql` alone. ### SQL pitfalls * **SQL returns NULL.** A `NULL` silently passes. Wrap with `COALESCE`: `SELECT COALESCE(SUM(amount), 0) ...` * **Trailing semicolons or comments.** Avoid trailing semicolons and SQL comments (`--`, `/* */`) — some warehouses reject them when executed programmatically. ### Noise reduction and updates * `event_rollup_count`** , **`event_rollup_until_changed`** **, and** **`alert grouping`** are mutually exclusive.** Setting more than one causes a validation error. Choose one strategy. * **Forgetting PUT semantics on updates.** When providing `uuid`, omitted fields revert to defaults. Always include every field you want to preserve.