Skip to main content
SQL tests let you write bespoke tests that fit your business circumstances and can be run on any table tracked by SYNQ 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

​
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. title
  • Choose a schedule (hourly/daily) and a time (hh: mm) when you want the test to run. title
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 SYNQ UI, you can give the test a human-friendly name. title

​
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 title

​
Audit table schema

When a SQL test fails, the failed records are stored in an audit table in your data warehouse for investigation and troubleshooting. SYNQ automatically creates this table in your data warehouse when you configure a SQL test with saveFailures=true.

​
Default table name

By default, the audit table is named synq_sql_test__audit. The table location can be configured per integration.

​
Schema by data warehouse

The audit table schema varies slightly by data warehouse. Below are the CREATE TABLE statements for each supported platform: 
CREATE TABLE IF NOT EXISTS synq_sql_test__audit (
  integration_id STRING
    , sql_test_path STRING
    , execution_id STRING
    , rows_count INTEGER
    , result STRING
    , created_at TIMESTAMP
);

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

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

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

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

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

​
Column descriptions

ColumnDescription
integration_idUnique identifier of the data warehouse integration in SYNQ
sql_test_pathPath or identifier of the SQL test
execution_idUnique identifier for each test execution
rows_countNumber of rows that failed the test
resultThe failed records in JSON format
created_atTimestamp when the audit record was created
The audit table is created automatically by SYNQ when you run your first SQL test. Ensure SYNQ has the necessary permissions to create tables in your configured schema or database.