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.

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

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.

📘
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

sql — SQL query to execute

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.

sql: |
  SELECT COUNT(*) FROM orders
  WHERE customer_id NOT IN (SELECT id FROM customers)

alert_conditions — threshold for alerting

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.

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

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.

alert_conditions:
  - type: threshold
    operator: GT

threshold_value — static threshold

number · required (for threshold and change types)

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.

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.

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

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.

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.

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.

alert_conditions:
  - type: dynamic_threshold
    threshold_sensitivity: medium

min_buffer_value — minimum buffer for dynamic threshold band

number · optional

alert_conditions:
  - type: dynamic_threshold
    min_buffer_value: 10

max_buffer_value — maximum buffer for dynamic threshold band

number · optional

alert_conditions:
  - type: dynamic_threshold
    max_buffer_value: 100

min_buffer_modifier_type — unit for min buffer

enum · optional

Accepted values: METRIC · PERCENTAGE

alert_conditions:
  - type: dynamic_threshold
    min_buffer_modifier_type: PERCENTAGE

max_buffer_modifier_type — unit for max buffer

enum · optional

Accepted values: METRIC · PERCENTAGE

alert_conditions:
  - type: dynamic_threshold
    max_buffer_modifier_type: PERCENTAGE

number_of_agg_periods — aggregation periods in baseline

integer · optional

Range: 1–1000.

alert_conditions:
  - type: dynamic_threshold
    number_of_agg_periods: 7

threshold_lookback_minutes — lookback window for threshold computation

integer · optional

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

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

alert_conditions:
  - type: threshold
    operator: GT
    threshold_value: 5
    is_percentage_threshold: true
    percentage_baseline_sql: |
      SELECT COUNT(*) FROM orders
      WHERE status = 'completed'

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. Monitors with query_result_type: SINGLE_NUMERIC don't record row counts.
Percentage thresholds do not require this setting and work without it.

schedule — when and how often to run

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.

schedule:
  type: fixed
  interval_minutes: 720

Properties

type — schedule type

enum · optional · default: fixed

Accepted values: fixed · dynamic · manual

schedule:
  type: fixed

interval_minutes — run interval for fixed schedules

integer · optional

Required when type is fixed.

schedule:
  type: fixed
  interval_minutes: 720

interval_crontab — cron expressions

array of strings · optional

5-field cron format.

schedule:
  type: fixed
  interval_crontab:
    - "0 8 * * *"

interval_crontab_day_operator — day-of-week/day-of-month combination

enum · optional

Accepted values: AND · OR

schedule:
  interval_crontab_day_operator: AND

start_time — ISO 8601 start time

string · optional

schedule:
  start_time: "2024-01-01T08:00:00Z"

timezone — IANA timezone

string · optional

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.

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

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

schedule:
  type: dynamic
  min_interval_minutes: 30

description — what this monitor checks

string · required

Displayed in the Monte Carlo UI and in incident notifications. Max 512 characters.

description: Alert when orphan orders exist

domains — domain for this monitor

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.

domains:
  - my-domain

warehouse — which warehouse to use

string · optional (yes if multiple warehouses)

Warehouse name or UUID. Overrides default_resource from montecarlo.yml.

warehouse: my-warehouse

name — unique identifier within the namespace

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.

name: orphan_orders

connection_name — named connection

string · optional

Overrides the default connection for this warehouse.

connection_name: analytics-readonly

variables — template variables substituted into SQL

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).

variables:
  schema_name:
    - analytics
    - staging

sampling_sql — investigation query on breach

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.

sampling_sql: |
  SELECT * FROM orders
  WHERE customer_id NOT IN (SELECT id FROM customers)
  LIMIT 100

query_result_type — how to interpret the SQL result

enum · optional

Accepted values: ROW_COUNT · SINGLE_NUMERIC

ROW_COUNT evaluates the row count of the result set instead of the scalar value.

query_result_type: ROW_COUNT

event_rollup_count — consecutive breaches before alerting

integer · optional

Minimum 2. Mutually exclusive with event_rollup_until_changed and alert_grouping.

event_rollup_count: 3

event_rollup_until_changed — suppress repeat notifications

boolean · optional · default: false

Roll up breaches until the result changes. Mutually exclusive with event_rollup_count and alert_grouping.

event_rollup_until_changed: true

alert_grouping — group subsequent breaches into the currently open alert

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

alert_grouping:
  mode: group_into_open_alert

severity — incident severity level

enum · optional

Accepted values: SEV-0 · SEV-1 · SEV-2 · SEV-3 · SEV-4

severity: SEV-2

priority — incident priority level

enum · optional

Accepted values: P1 · P2 · P3 · P4 · P5

priority: P2

audiences — notification channels

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.

audiences:
  - data-eng-oncall
  - platform-alerts
  - audience: oncall
    triage_priority: [HIGH]

failure_audiences — notification channels for run failures

array of strings · optional

Separate audiences for run-failure notifications. Falls back to audiences when omitted.

failure_audiences:
  - data-eng-oncall

notify_run_failure — notify when the query itself fails

boolean · optional

notify_run_failure: true

exception_primary_key_column — column for per-key incident grouping

string · optional

exception_primary_key_column: order_id

timeout — query timeout in seconds

integer · optional

timeout: 300

tags — key-value pairs for organizing monitors

array of objects · optional · default: []

Property	Type	Required	Description
`name`	string	yes	Tag key
`value`	string	no	Tag value

tags:
  - name: team
    value: analytics
  - name: environment
    value: production

data_quality_dimension — data quality category

enum · optional

Accepted values: ACCURACY · COMPLETENESS · CONSISTENCY · TIMELINESS · UNIQUENESS · VALIDITY

data_quality_dimension: CONSISTENCY

notes — internal notes

string · optional

Visible in the Monte Carlo UI. Not included in notifications.

notes: Owned by the analytics team. Reviewed quarterly.

is_draft — create as draft without activating

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.

is_draft: true

uuid — update an existing monitor

string · optional

Include the UUID of an existing monitor to update it instead of creating a new one.

uuid: 0dae7702-0950-45c7-909c-7e183bddca19

auto_tuning — override auto-apply tuning for this monitor

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.

auto_tuning: false

auto_triage — override auto-triage for this monitor

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.

auto_triage: false

Deprecated fields

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

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".

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

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

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

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.

Updated 7 days ago

Did this page help you?