Custom SQL Monitor

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) or event_rollup_until_changed at the monitor level (not inside alert_conditions) to suppress notifications until N consecutive breaches or until the result changes. These two fields are mutually exclusive.

📘
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 — percentage-based threshold

boolean · optional

alert_conditions:
  - type: threshold
    is_percentage_threshold: true

percentage_baseline_sql — SQL for percentage baseline denominator

string · optional

alert_conditions:
  - type: threshold
    percentage_baseline_sql: "SELECT COUNT(*) FROM orders"

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, on all accounts created after January 2025

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. Rollup caps at 100 consecutive breaches.

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.

event_rollup_until_changed: true

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 · optional · default: []

Audience names linking this monitor to channels defined in Notifications as Code. In exported/rendered YAML, appears as labels.

audiences:
  - data-eng-oncall
  - platform-alerts

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

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

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.

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 and event_rollup_until_changed are mutually exclusive. Setting both 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.