Skip to main content
Custom SQL tests validate business rules that are specific to your data. They run as cloud tests directly against your data warehouse, without requiring a pull request or dbt job.
Custom SQL cloud tests are available on request. Contact the Elementary team to enable them for your account.
Use them for checks that are difficult to express with an existing generic test, such as validating calculations or relationships across multiple tables.

​
How it works

Write a query that returns the rows that violate your expectation. For example:
On each run, Elementary executes the query using the environment’s existing warehouse connection and evaluates the number of rows returned: By default, Elementary stores a limited sample of the returned rows with the test result. You can turn off row samples or change the sample limit.
Disable row samples if the query could return sensitive values that should not be stored with test results.
Custom SQL tests query table data and consume compute in your data warehouse. Choose a schedule, timeout, and query scope that are appropriate for your warehouse.

​
Cloud test behavior

Like other cloud tests, custom SQL tests:
  • Are created and configured in the Elementary UI
  • Run independently of your dbt pipeline
  • Can run on a recurring schedule or on demand
  • Appear alongside dbt tests and other monitors in test results, incidents, and coverage views
Elementary uses the warehouse credentials already configured for the environment. The connection must be able to read every table referenced by the query.

​
SQL requirements

A custom SQL test must:
  • Contain exactly one read-only query
  • Use a SELECT statement, including queries built with CTEs or UNION
  • Reference tables by their fully qualified names
  • Reference only tables that exist as assets in the Elementary Catalog
Elementary validates the SQL before saving the test and again before every execution. A validation run can also check the query against your warehouse and preview its result columns without reading result rows.
If any referenced table has not been synced to the Elementary Catalog, the test cannot be created. The validation result lists unresolved tables so you can sync them or update the query.

​
Asset linkage and permissions

Elementary derives asset linkage from the tables referenced in the SQL:
  • If the query references one asset, Elementary links it automatically.
  • If it references multiple assets, choose a primary asset during configuration.
The primary asset determines where the test appears in the Catalog and how Elementary groups its incidents. It does not limit which tables the query can read. To create or edit the test, you need permission to edit tests in the environment, view every referenced asset, and create tests on the primary asset.

​
Create a custom SQL test

1

Add a custom SQL test

Go to Test Configuration, click Add Test, and choose Custom SQL under Elementary Cloud tests.
2

Write and validate the query

Enter a name, an optional description, and a query that returns failing rows. Validate the query to check its SQL, warehouse compatibility, and referenced assets.
3

Choose the primary asset

If the query references multiple assets, select the asset that the test primarily represents.
4

Configure the result behavior

Set failure and optional warning thresholds. Choose whether to store a sample of failing rows, then configure the row-sample limit and query timeout.
5

Set the schedule and ownership

Choose a schedule, severity, owners, and tags, then submit the test. No pull request is required.

​
Scheduling

Set the test to run hourly, every few hours, daily, or weekly. You can also run the test immediately with Test now.

​
Edit configuration

Open the test from test results or an incident and go to the Configuration tab. You can update the SQL, thresholds, row-sample settings, timeout, schedule, severity, owners, and tags. When you save an updated query, Elementary validates it again and re-derives its referenced assets. If the query now references multiple assets or no longer references the current primary asset, you must choose a new primary asset. Saving changes re-runs the test with the updated configuration.

​
Test results

Each execution shows its status and the number of failing rows returned. When row samples are enabled, the execution details also show the sampled rows and whether the sample was truncated. Execution errors, such as invalid SQL or a query timeout, are shown separately from failures caused by returned rows.

​
Alerts

Custom SQL test failures map to the Custom SQL tests category in alert rules. Include this category in a rule’s test-type filters to receive alerts. By default, the catch-all alert rule may not include every cloud test category. Review your alert rules if you want custom SQL cloud test failures to notify your team.

​
Comparison with dbt singular tests

dbt singular tests also use SQL queries that return failing rows, but they are managed and executed through your dbt project. Choose a custom SQL cloud test when you want Elementary to manage the configuration and schedule. Choose a dbt singular test when the validation should live in version-controlled project code and run with your dbt jobs.