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

• Ascend Flow

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

• Explore YAML tests
• Learn about Python tests for custom validation logic
• Review the Tests concept guide for comprehensive test strategy guidance