SQL Tests

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—An 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 takes the columns and values it needs, so a test of a given type is the same assertion wherever it is defined — in the app, over the API, or in a YAML configuration, where 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 Health overview

• In the SQL tests section, click Add sql test

• 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 Health overview

• Click on SQL tests 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. It is the only supported placeholder — 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.

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 = CURRENT_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 same test in the app — the query, then one card per evaluator carrying its own name, severity and expression. The first two of the four are shown.

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. 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
);

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.

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.