Skip to main content

Test SQL

In this guide, you'll learn how to add data quality and validation tests to your SQL Components using Jinja blocks to ensure data integrity in your pipelines.

Prerequisites​

info

For a comprehensive overview of test types and when to use them, see our Tests concept guide.

Test format​

SQL Components support native tests through Jinja with_test blocks. These tests validate data quality automatically during pipeline execution and can be added directly within your SQL files.

Add native tests to SQL Components by including with_test Jinja blocks after your SELECT statement:

SELECT
    *
FROM
    {{ ref("my_table", flow="my_flow") }}

{{ with_test("test_name", column="column_name", parameter="value") }}

Column-level tests​

Column-level tests validate individual columns in your dataset. Include the column parameter to specify which column to test.

Basic validation tests​

Check for null values and uniqueness:

SELECT
    user_id,
    email,
    created_at
FROM
    {{ ref("users", flow="user_management") }}

{{ with_test("not_null", column="user_id") }}
{{ with_test("unique", column="user_id") }}
{{ with_test("not_null", column="email") }}
{{ with_test("unique", column="email") }}

String validation tests​

Validate string columns for empty values and patterns:

SELECT
    feedback_id,
    feedback_text,
    rating
FROM
    {{ ref("customer_feedback", flow="analytics") }}

{{ with_test("not_empty", column="feedback_text") }}
{{ with_test("not_null", column="feedback_text") }}

Component-level tests​

Component-level tests validate the entire output of your Transform. Omit the column parameter for Component-level tests.

Count validation​

Verify that your Transform produces exactly the expected number of records:

SELECT
    *
FROM
    {{ ref("processed_orders", flow="sales") }}
WHERE
    status = 'completed'

{{ with_test("count_equal", count=75471) }}

Test Components​

You can also create dedicated Test Components in SQL for custom validation logic:

test_combinations.sql
{% test combination_unique(component, column, columns) %}
{% set columns_str = columns | join(', ') %}
  SELECT COUNT(*) as cnt, {{ columns_str }}
  FROM {{ component }}
  WHERE {{ columns | map('safe') | join(' IS NOT NULL OR ') }} IS NOT NULL
  GROUP BY {{ columns_str }}
  HAVING cnt > 1
{% endtest %}

This Test Component checks for uniqueness across a combination of columns.

Next steps​

Last updated on