SQL Tests - Coalesce Quality Documentation

SQL tests let you write bespoke tests that fit your business circumstances and can be run on any table tracked in the platform through a unified workflow.

Example: a SQL test runs a query to check for rows where the workspace is null. If any rows match the test, it will throw an error

Test types

Alongside a query you write yourself, a test can be one of the following types. Each type takes the columns and values it needs, and works the same in the app, the API and YAML. In YAML, the name in the first column is the type: value.

Type	What it checks
`not_null`	Column values are never null
`empty`	Column values are not empty. A null or whitespace-only value counts as empty
`unique`	Column values are unique, optionally within a time window
`accepted_values`	Values are in an allowed list
`rejected_values`	Values are not in a forbidden list
`min_max`	Values fall within a numeric or date range
`min_value`	Values meet or exceed a minimum
`max_value`	Values stay at or below a maximum
`freshness`	Data has been updated within a time window
`relative_time`	One timestamp column never comes after another, for example `order_date` at or before `ship_date`
`business_rule`	A SQL condition identifying the rows that violate the rule
`business_query`	A full `SELECT` whose result rows are reported as failures, or a rollup graded by evaluators
`relationships`	Referential integrity: every source value exists in a reference table

Creating a SQL test

Head to the
In the SQL tests section, click
Select the connection to execute your SQL query
Specify a SQL query. The test is considered a success if it returns zero records. If any records are returned, the test will trigger an error, and the failed records will be stored in an audit table for investigation. To have the query return metrics and grade them individually instead, add evaluators.
Choose a schedule (hourly/daily) and a time (hh: mm) when you want the test to run.

Running an excessive amount of tests or running a test too often will impact your data warehouse costs. Avoid running tests more often than needed

The confirmation page will show you a summary of the setup. To make it easier to locate in the UI, you can give the test a human-friendly name.

Editing a SQL test

Head to the
Click on to see all your SQL tests
Select the SQL test you want to edit by clicking on it
In the popout, navigate to the settings tab. Click Edit under SQL test configurations to edit the name, run schedule, or SQL code. To delete the test, click Delete SQL test

Managing SQL tests programmatically

SQL tests can be created and changed outside the app, one at a time or in bulk across every table matching a selection.

YAML and the CLI: tests live under an entity’s tests: in a monitors-as-code configuration and are deployed with synqcli deploy. To cover many tables by a selection instead of listing each one, put the tests under a type: sql_tests deployment rule.
Public API: synq.datachecks.sqltests.v1.SqlTestsService for individual tests and synq.datachecks.sqltests.v1.SqlTestsDeploymentRulesService for rules that deploy a set of tests onto every table a selection matches. Reading needs a token with SCOPE_DATACHECKS_SQLTESTS_READ, writing needs SCOPE_DATACHECKS_SQLTESTS_EDIT. See the API reference and API scopes.
MCP: the SQL test tools create tests from a template (not-null, unique, accepted values, relationships, freshness, range, business rules) or from raw SQL, and the deployment rule tools do the same across a selection of tables. Listing tests is covered by read-only consent; writing needs the Deploy permission, and every write is confirmation-gated.

The table placeholder

A business_rule and a business_query carry SQL you write yourself, so nothing in them names the table unless you do. Write {{ table }} and it is replaced with the fully qualified name of the table the test is anchored to, quoted for your warehouse. The other supported placeholders are the time placeholders. Any other {{ name }} token is rejected when the test is saved. The substitution is textual, so the token is replaced everywhere it appears, inside a string constant or a comment as well as in a FROM clause. There is no escape sequence; write the name out yourself if you need the literal text in a result. On a single test it saves repeating a name you already gave. On a sql_tests deployment rule it is the difference between a test and a copy: the rule deploys its tests onto every table the selection matches, so without the placeholder all of them run the same query against whichever table you typed.

- type: business_query
  name: Orders with a negative total
  sql_query: |
    SELECT * FROM {{ table }} WHERE total < 0

The placeholder renders a table name, so put it where a table name goes. In a business_query you own the whole statement and can alias it, which is what a correlated subquery needs. In a business_rule the FROM clause is generated for you and carries no alias, so the placeholder is for a subquery of your own rather than for qualifying a column:

- type: business_rule
  name: Order predates the first shipped order
  sql_expression: "created_at < (SELECT min(created_at) FROM {{ table }} WHERE status = 'shipped')"

The subquery has to name the same table the test is anchored to, which is what the placeholder is for: on a rule the test lands on every matched table, and each copy aggregates over its own rows. Writing {{ table }}.column is not portable: on BigQuery the fully qualified name is a single quoted identifier and cannot qualify a column. Reach for business_query when you need to name the anchor’s columns.

Time placeholders

A test that uses NOW(), CURRENT_DATE or a similar function checks different rows each time it runs. If you fix the data and run the test again, it does not check the rows that failed. Use the time placeholders instead. They work in the SQL of every SQL test: a query you write yourself, the sql_expression of a business_rule, the sql_query of a business_query, the SQL of an evaluator, and the tests of a sql_tests deployment rule. In the query editor of the app, type {{ to insert a placeholder. When the test runs, each placeholder is replaced with a UTC timestamp literal in the SQL dialect of your warehouse.

Placeholder	Value
`{{ executed_at }}`	The time of this run. For a scheduled run, it is the scheduled time, also when the run starts late. For a run you start from the app or the API, it is the time you started it.
`{{ last_executed_at }}`	The `{{ executed_at }}` of the previous run that had a result: the run passed, or it failed on data. A run with an error is skipped, so the next run checks its rows. A run again does not count.
`{{ last_passed_at }}`	The `{{ executed_at }}` of the previous run that passed.

Check only new rows

To check only the rows that are new since the previous run, filter on both ends of the window. Each window starts where the previous window ended. No row is checked twice and no row is skipped.

- type: business_query
  id: new-orders-have-a-customer
  name: New orders have a customer
  sql_query: |
    SELECT * FROM {{ table }}
    WHERE loaded_at > {{ last_executed_at }} AND loaded_at <= {{ executed_at }}
      AND customer_id IS NULL

Use {{ last_passed_at }} when all rows since the last good state must be checked again. While the test fails, its window grows on each run. After you fix the data, the next run checks the full period that failed. When it passes, the window becomes small again. Filter on the time a row was loaded, if the table has such a column. A row can arrive late, with an event time in a window that was already checked. A filter on the event time does not check that row.

Compare a placeholder with a column

A placeholder has the type that NOW() returns on your warehouse, so you can use it wherever NOW() works: loaded_at > {{ last_executed_at }}. Write it without quotes. '{{ executed_at }}' is rejected when the test is saved, because the value is already a timestamp. Convert it only when the column is not a timestamp:

Column type	Write
Timestamp, on any warehouse	`loaded_at > {{ last_executed_at }}`
`DATE` or `DATETIME` on BigQuery	`load_date >= DATE({{ last_executed_at }})` or `load_dt > DATETIME({{ last_executed_at }})`. BigQuery does not compare a `TIMESTAMP` with these types.
`DATE` on other warehouses	`load_date >= CAST({{ last_executed_at }} AS DATE)`

A comparison with a DATE column is per day. Use >= at the start of the window, so the rows of the day of the previous run are checked again and none are missed.

Lookback

lookback sets how far back the placeholders can go, for example 6h or 1d:

If a placeholder has no earlier run to take its value from, its value is {{ executed_at }} minus the lookback. This is the case on the first run, and for {{ last_passed_at }} on a test that has not passed yet.
No placeholder is older than {{ executed_at }} minus the lookback. When this limit applies, the run message says so, for example: “The lookback of 1 day limited the time window: rows before 2026-01-07T09:00:00Z were not checked.”

- type: business_query
  id: new-orders-have-a-customer
  name: New orders have a customer
  lookback: 2d
  sql_query: |
    SELECT * FROM {{ table }}
    WHERE loaded_at > {{ last_passed_at }} AND loaded_at <= {{ executed_at }}
      AND customer_id IS NULL

In the public API, the field is SqlTest.lookback, a duration. If you do not set lookback, the first run looks back one interval of the schedule, or 1 day for a test without a schedule. The limit is seven intervals, and at least 7 days. A test from a sql_tests deployment rule always uses this default, and the rule does not accept lookback.

A warning for time functions

If the SQL of a test uses NOW(), CURRENT_TIMESTAMP, GETDATE(), SYSDATE, SYSTIMESTAMP, LOCALTIMESTAMP, CURRENT_DATE, CURRENT_TIME or LOCALTIME, the test is saved with a warning. The warning names the function and gives a suggested SQL. The suggested SQL replaces each function with {{ executed_at }}. For CURRENT_DATE it is CAST({{ executed_at }} AS DATE), and for CURRENT_TIME and LOCALTIME it is CAST({{ executed_at }} AS TIME). A call with a time zone, such as CURRENT_DATE('America/Los_Angeles'), stays as it is. {{ executed_at }} is in UTC, while CURRENT_DATE uses the time zone of your warehouse session. If that time zone is not UTC, the suggested SQL can check a different day than your SQL. Check the suggestion before you use it. You see the warning in these places:

The query editor in the app shows the warning and the suggested SQL as a diff. Click Use suggested SQL to use it. If Scout is set up, you can also click Suggest a rewrite with AI. Scout suggests a rewrite to the placeholders and gives the reason. The rewrite is applied only when you click Use AI suggestion and save the test.
synqcli deploy prints the warning, followed by Suggested SQL:.
The public API returns it in the upsert response, in warnings[].suggested_sql_expression.
The MCP tools create_sql_tests and update_sql_tests return it in their results.

A change of the filter to {{ last_executed_at }} makes the test check different rows. Decide on that change yourself.

Run a test again

You can run an earlier execution of a SQL test again with its time values, for example after you fix the data, or to look at a failure. The run uses the current SQL of the test and the placeholder values of that execution, so it checks the same rows. Later scheduled runs do not take their values from it.

App: open the run of the SQL test and click Run again. You need a role that can edit SQL tests. The run shows the placeholder values under Time window.
API: send POST /api/datachecks/scheduler/v1/trigger/sql-test-run-again with the execution_id of the earlier execution. This is TriggerService.TriggerSqlTestRunAgain, and it needs SCOPE_DATACHECKS_TRIGGER or SCOPE_DATACHECKS_SQLTESTS_EDIT. It accepts the execution_id of a trigger result, or the id of an execution from the executions API. The result has the time_values of the run and the rerun_of_execution_id.
MCP: use the run_sql_test_again tool.

Evaluators

A business_query with no evaluators is a single verdict: every row the query returns is a failure, so the query itself has to encode what “wrong” means and the whole test is one pass or one fail. Evaluators split that in two. The query returns the numbers, and each evaluator is a named condition over those numbers with its own severity. One scan of the warehouse, several checks, each of them named in the alert. Reach for them when one rollup answers several independent questions, when those questions deserve different severities, or when you want the alert to say which condition broke rather than how many rows came back.

Daily order health by channel

The query groups yesterday’s orders by sales channel and computes four numbers per channel. Each evaluator reads one of them. SQL

SELECT
  channel,
  COUNT(*)                                                    AS order_count,
  SUM(total_amount)                                           AS revenue,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) * 1.0
    / COUNT(*)                                                AS refund_rate,
  AVG(total_amount)                                           AS avg_order_value
FROM orders
WHERE order_date = CAST({{ executed_at }} AS DATE) - 1
GROUP BY channel

Evaluators

Name	SQL expression	Severity
Revenue must not be negative	`revenue < 0`	Error
Refund rate above 5%	`refund_rate > 0.05`	Warning
Fewer than 50 orders in a channel	`order_count < 50`	Warning
Average order value outside the expected band	`avg_order_value < 10 OR avg_order_value > 500`	Info

The query returns one row per channel, and every evaluator is applied to every row. A 7% refund rate on the affiliate channel flags that one row against the second evaluator and leaves the other channels alone. The test reports a warning and names the evaluator that flagged, so whoever picks up the alert knows it is refunds on one channel rather than revenue across all of them. The same coverage without evaluators is four separate tests, each with its own WHERE clause and its own scan of orders, or one test that returns a row when anything at all is wrong and cannot say what.

Writing an expression

Refer to the columns of the query’s select list by the alias they were given. refund_rate works because the query aliased it.
The expression is a fail condition. The row is flagged when the expression is TRUE, so write what you do not want to see rather than what you expect.
NULL passes. A metric that can be null and should count as a failure has to say so: revenue IS NULL OR revenue < 0.
Aggregate in the query and compare in the evaluator. The expression is evaluated one result row at a time, so it cannot total across rows.
The expression is sent to your warehouse as written, in that warehouse’s SQL dialect.

Severity

Each evaluator carries its own severity, and the test reports the worst severity among the evaluators that flagged a row.

Worst severity that flagged	The test reports
Nothing flagged	OK
Info	OK
Warning	Warning
Error	Error

An Info evaluator records its outcome without moving the test’s status, which suits a threshold you are still calibrating or a number you want in the run history without paging anyone. A test created over the API or from a YAML configuration can leave an evaluator’s severity unset, and it takes the test’s own.

Failed rows

With Save failures on, the rows an evaluator flagged are written to the audit table, alongside how each evaluator did on that run. You can go from a failing test to the channels behind it rather than to a count. Rows are written whenever any evaluator flags one, including when only Info evaluators did and the test itself reports OK. A threshold you are still calibrating therefore collects real rows before you promote it to a warning.

Editing evaluators

Adding, removing or changing an evaluator updates the test in place, so its history, its issues and its past runs are kept. A test on a schedule runs again straight away instead of waiting for its next slot, so the change shows up in the run view immediately.

Evaluators belong to a business_query, because the evaluator reads the columns the query returns and only a business_query gives you the whole SELECT.

Audit table schema

When a test has Save failures on, the rows it flagged are written to an audit table in your data warehouse, so you can look at the records themselves. The table belongs to the warehouse integration rather than to any one test: set its fully qualified name in the integration’s Audit table FQN field and Coalesce Quality creates it if it does not already exist. While that field is empty a test writes nothing, whatever its Save failures setting.

Naming the table

The name is yours to choose. Give the integration a fully qualified name the connection can create and write to; the placeholder in that field shows the shape your platform expects, from a bare table name on Redshift and PostgreSQL to catalog.schema.table on Databricks and [warehouse].[schema].[table] on Microsoft Fabric. One audit table serves every SQL test on that integration, and the statements below use quality_sql_test__audit as the name.

Schema by data warehouse

The audit table schema varies slightly by data warehouse. Below are the CREATE TABLE statements for each supported platform:

BigQuery

CREATE TABLE IF NOT EXISTS quality_sql_test__audit (
  integration_id STRING
    , sql_test_path STRING
    , sql_test_name STRING
    , execution_id STRING
    , rows_count INTEGER
    , result STRING
    , evaluators STRING
    , created_at TIMESTAMP
);

Snowflake

CREATE TABLE IF NOT EXISTS quality_sql_test__audit (
  integration_id STRING
    , sql_test_path STRING
    , sql_test_name STRING
    , execution_id STRING
    , rows_count INTEGER
    , result STRING
    , evaluators STRING
    , created_at TIMESTAMP
);

Databricks

CREATE TABLE IF NOT EXISTS quality_sql_test__audit (
  integration_id STRING
  , sql_test_path STRING
  , sql_test_name STRING
  , execution_id STRING
  , rows_count INT
  , result STRING
  , evaluators STRING
  , created_at TIMESTAMP
);

PostgreSQL

CREATE TABLE IF NOT EXISTS "quality_sql_test__audit" (
  integration_id TEXT
  , sql_test_path TEXT
  , sql_test_name TEXT
  , execution_id TEXT
  , rows_count INTEGER
  , result TEXT
  , evaluators TEXT
  , created_at TIMESTAMPTZ
);

MySQL

CREATE TABLE IF NOT EXISTS quality_sql_test__audit (
  integration_id TEXT
  , sql_test_path TEXT
  , sql_test_name TEXT
  , execution_id TEXT
  , rows_count INT
  , result TEXT
  , evaluators TEXT
  , created_at TIMESTAMP
);

Redshift

CREATE TABLE IF NOT EXISTS "quality_sql_test__audit" (
  integration_id VARCHAR
  , sql_test_path VARCHAR
  , sql_test_name VARCHAR
  , execution_id VARCHAR
  , rows_count INTEGER
  , result VARCHAR
  , evaluators VARCHAR
  , created_at TIMESTAMP
);

ClickHouse

CREATE TABLE IF NOT EXISTS quality_sql_test__audit (
  integration_id String
    , sql_test_path String
    , sql_test_name String
    , execution_id String
    , rows_count Int32
    , result String
    , evaluators String
    , created_at DateTime64(8, 'UTC')
) Engine = MergeTree()
    ORDER BY (integration_id, sql_test_path, execution_id, created_at)
;

Trino

CREATE TABLE IF NOT EXISTS quality_sql_test__audit (
  integration_id VARCHAR
  , sql_test_path VARCHAR
  , sql_test_name VARCHAR
  , execution_id VARCHAR
  , rows_count INTEGER
  , result VARCHAR
  , evaluators VARCHAR
  , created_at TIMESTAMP
)

SQL Server

IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('quality_sql_test__audit'))
CREATE TABLE quality_sql_test__audit (
  integration_id NVARCHAR(MAX)
  , sql_test_path NVARCHAR(MAX)
  , sql_test_name NVARCHAR(MAX)
  , execution_id NVARCHAR(MAX)
  , rows_count INT
  , result NVARCHAR(MAX)
  , evaluators NVARCHAR(MAX)
  , created_at DATETIME2
);

Microsoft Fabric

IF OBJECT_ID('quality_sql_test__audit', 'U') IS NULL
CREATE TABLE quality_sql_test__audit (
  integration_id VARCHAR(MAX)
  , sql_test_path VARCHAR(MAX)
  , sql_test_name VARCHAR(MAX)
  , execution_id VARCHAR(MAX)
  , rows_count INT
  , result VARCHAR(MAX)
  , evaluators VARCHAR(MAX)
  , created_at DATETIME2(6)
);

Oracle

BEGIN
  EXECUTE IMMEDIATE 'CREATE TABLE quality_sql_test__audit (
    integration_id VARCHAR2(4000)
    , sql_test_path VARCHAR2(4000)
    , sql_test_name VARCHAR2(4000)
    , execution_id VARCHAR2(4000)
    , rows_count NUMBER
    , result VARCHAR2(4000)
    , evaluators VARCHAR2(4000)
    , created_at TIMESTAMP
  )';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE != -955 THEN RAISE; END IF;
END;

DuckDB

CREATE TABLE IF NOT EXISTS quality_sql_test__audit (
  integration_id VARCHAR
  , sql_test_path VARCHAR
  , sql_test_name VARCHAR
  , execution_id VARCHAR
  , rows_count INTEGER
  , result VARCHAR
  , evaluators VARCHAR
  , created_at TIMESTAMP
);

Column descriptions

Column	Description
`integration_id`	Unique identifier of the data warehouse integration
`sql_test_path`	Path or identifier of the SQL test
`sql_test_name`	Name of the SQL test at the time of the run
`execution_id`	Unique identifier for each test execution
`rows_count`	Number of rows that failed the test
`result`	The failed records, as JSON
`evaluators`	Per-evaluator outcomes for a `business_query` test, as JSON. Each entry carries the evaluator’s `id`, `name`, `sql_expression`, `severity`, `fail_count`, `status` and `message`, snapshotted as they were at execution time. Empty for every other test type
`created_at`	Timestamp when the audit record was created

The table is created when you save the integration with an Audit table FQN set, so the connection needs permission to create tables in that schema or database. Without the privilege the integration reports the failure on its status; you can also create the table yourself with the statement above and grant INSERT on it instead. On Microsoft Fabric, the service principal needs the Contributor workspace role, see Microsoft Fabric.

An audit table is created once and is not altered afterwards, so a table that predates the sql_test_name and evaluators columns keeps its original shape and those two values are dropped from the rows written to it. Add the two columns yourself to start recording them.