SQL Based Rules

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

SQL-Based Validation Rules follow the same Correctness metric model as Record Validation Rules. 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

---

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.

**Column order matters.** Telmai reads output positionally. `is_valid` must always be the first column, followed by `record_id`, then `record_id_name`.

---

Example Queries

Minimal — validation result only

SELECT
    CASE WHEN transaction_amount > 0 THEN 1 ELSE 0 END AS is_valid
FROM my_asset.transactions

With record identifier

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

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

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

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 above

5. Validate and save the rule