> For the complete documentation index, see [llms.txt](https://docs.telm.ai/telmai/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.telm.ai/telmai/monitoring-data/user-defined-monitors/sql-based-rules.md).

# SQL Based Rules

SQL-Based Validation Rules give you full SQL pushdown for record-level data quality checks. Instead of [Telmai's rule expression builder](https://docs.telm.ai/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" %}
A **SQL row check** works like a [Row quality check](https://docs.telm.ai/telmai/monitoring-data/user-defined-monitors/record-validation-rules.md): it tracks the share of rows that pass, as a **pass rate**. 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 **pass rate** 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 pass rate 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. Without it, Telmai computes the pass rate but can't show failing records or use them in data binning. |
| `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`. To return `record_id_name`, you must also return `record_id`.
{% endhint %}

***

### Example Queries

#### Minimal — validation result only

Returns the pass rate only. Failing records aren't shown.

```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. Open the asset from **Assets**, go to its **Monitors** tab, and click **New monitor**.
2. Choose **SQL row check** as the **Monitor Type**.
3. Write your SQL query following the [required output schema](#query-structure) above: **is\_valid** (1 or 0), **record\_id**, then optionally **record\_id\_name**, in that order. You can leave out **record\_id**, but then Telmai can't show failing records.
4. Choose when the pass rate is unacceptable, then click **Next**. Telmai checks the SQL against your data before you can continue, and shows any problem under the editor.
5. Complete the remaining steps and **Save**. See [Creating a Monitor](https://docs.telm.ai/telmai/monitoring-data/monitors-management/creating-a-monitor.md).
