Creating an SQL-based rule monitor — write a pushdown SQL query to define record-level validation logic.
SQL-Based Validation Rules give you full SQL pushdown for record-level data quality checks. Instead of [Telmai's rule expression builder,](/telmai/monitoring-data/user-defined-monitors/record-validation-rules.md) you write a standard SQL query directly against your data source. Telmai executes it natively on the compute engine (Snowflake, BigQuery, Databricks, etc.) and ingests the results.
This is ideal when:
* Your validation logic is complex and already expressed in SQL
* You need to join across multiple tables or use warehouse-specific functions
* You want to reuse existing SQL-based data quality checks without rewriting them in DSL
{% hint style="info" %}
SQL-Based Validation Rules follow the same **Correctness** metric model as [Record Validation Rules](/telmai/monitoring-data/user-defined-monitors/record-validation-rules.md). The key difference is execution: Expression rules run inside Telmai's Spark engine, while SQL-Based rules are pushed down and executed natively on your connected data source
{% endhint %}
***
### How It Works
You provide a SQL query (`Q`) that Telmai executes against your data source. The query must return a specific output schema. Telmai reads the results and computes the **Correctness** metric as the percentage of records where `is_valid = 1`.
For example, for a table with 1M rows: if 50,000 records return `is_valid = 0`, the Correctness score is **95%**.
***
### Query Structure
Your SQL query must return columns in the following exact order:
| Column | Type | Required | Description |
| ---------------- | ----------------- | -------- | ---------------------------------------------------------------------------------- |
| `is_valid` | Integer | Yes | Validation result. `1` = valid, `0` = invalid. |
| `record_id` | String or Integer | No | The unique identifier for the record. Used to surface failing records in the UI. |
| `record_id_name` | String | No | A static label for the identifier field. Used as metadata in incident drill-downs. |
{% hint style="warning" %}
\*\*Column order matters.\*\* Telmai reads output positionally. \`is\_valid\` must always be the first column, followed by \`record\_id\`, then \`record\_id\_name\`.
{% endhint %}
***
### Example Queries
#### Minimal — validation result only
```sql
SELECT
CASE WHEN transaction_amount > 0 THEN 1 ELSE 0 END AS is_valid
FROM my_asset.transactions
```
#### With record identifier
```sql
SELECT
CASE WHEN transaction_amount > 0 THEN 1 ELSE 0 END AS is_valid,
transaction_id AS record_id
FROM my_asset.transactions
```
#### Full output — with metadata label
```sql
SELECT
CASE WHEN transaction_amount > 0 THEN 1 ELSE 0 END AS is_valid,
transaction_id AS record_id,
'transaction_id' AS record_id_name
FROM my_asset.transactions
```
#### Cross-table join validation
```sql
SELECT
CASE WHEN o.status = 'CLOSED' AND p.paid_at IS NOT NULL THEN 1 ELSE 0 END AS is_valid,
o.order_id AS record_id,
'order_id' AS record_id_name
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id
```
#### Conditional logic with warehouse functions
```sql
SELECT
CASE
WHEN region = 'US' AND TRY_CAST(zip_code AS INT) IS NULL THEN 0
WHEN region != 'US' AND zip_code IS NULL THEN 0
ELSE 1
END AS is_valid,
customer_id AS record_id,
'customer_id' AS record_id_name
FROM customers
```
***
### Creating a SQL-Based Rule
1. Navigate to **Alerting Monitors** and click **New Monitor**
2. Choose **SQL-Based Rule** as the monitor type
3. Enter a monitor name and optional description
4. Write your SQL query following the [required output schema](#query-structure) above
5. Validate and save the rule
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://docs.telm.ai/telmai/monitoring-data/user-defined-monitors/sql-based-rules.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.