ASSERT_TRUE

Raise a runtime error if the condition is FALSE or NULL; return NULL silently when TRUE.

Category: conditionalReturns: NULLDialect: Standard

Syntax

ASSERT_TRUE(condition [, message])

Description

## Overview Evaluates the boolean condition and raises a runtime error if it is FALSE or NULL; returns NULL silently if it is TRUE. Use ASSERT_TRUE to embed inline data-quality invariants into pipelines that must fail fast when an expectation is violated. Combined with a custom message, ASSERT_TRUE is a lightweight alternative to full data-quality frameworks for guarding specific rules (row counts, sum invariants, uniqueness). ## Behavior - TRUE condition: returns NULL, no side effect. - FALSE condition: raises a runtime error and aborts the query. - NULL condition: also raises an error (treated as failure). - The optional message is appended to the error output for debugging. - Does not swallow or suppress the error; the enclosing query fails. ## Compatibility - ASSERT_TRUE is not part of ANSI SQL. Alternative idioms include CASE WHEN NOT condition THEN RAISE_ERROR('...') END. - Behavior of NULL-as-failure matches the usual analytics SQL convention but differs from engines that treat NULL as success; verify when porting.

Parameters

Name	Type	Description
`condition`		Specifies the boolean expression to validate. If this expression is FALSE or NULL, the function raises a runtime error.
`message`		Specifies a custom error message to include when the assertion fails. If omitted, a generic 'assertion failed' message is used.

Examples

-- Passes silently
SELECT ASSERT_TRUE(1 = 1) AS ok;

-- Custom message on failure
SELECT ASSERT_TRUE(1 > 2, 'Expected 1 to be greater than 2');

-- NULL condition is treated as a failure
SELECT ASSERT_TRUE(NULL, 'Condition was NULL');

-- Realistic: gate a pipeline step on a row-count invariant
SELECT ASSERT_TRUE(
  (SELECT COUNT(*) FROM ingestion.staging.daily_rows) > 0,
  'staging.daily_rows was empty; refusing to continue'
);

-- Row-level quality guard inside a query
SELECT ASSERT_TRUE(amount >= 0, 'Negative amount detected') , amount
FROM ecommerce.sales.orders
WHERE order_date = DATE '2026-04-19';

Pitfalls

A NULL condition raises an error, not a pass. Either guard with COALESCE(cond, FALSE) or rely on the strict behavior as a NULL-catch.
ASSERT_TRUE aborts the entire query on failure. Do not place it inside a per-row projection unless you want any bad row to fail the whole result.
The message must be a STRING. Concatenating with columns (|| CAST(id AS STRING)) is useful but evaluates per row; do not depend on lazy evaluation to hide a huge string build.
ASSERT_TRUE returns NULL; do not rely on the return value in comparisons.
For aggregate invariants, wrap a scalar subquery: ASSERT_TRUE((SELECT COUNT(*) = 1 FROM ...)) rather than per-row assertions.