ASSERT

First-class assertion framework that validates query results against declarative conditions with configurable severity levels, supporting row counts, scalar values, result sets, expressions, uniqueness checks, and no-fail scans.

Syntax

ASSERT [ERROR|WARNING|INFO] <assertion_kind>
<inner_sql>

Assertion kinds:
  ROW_COUNT <op> <n>
  VALUE <column> <op> <value> [WHERE <filter>]
  VALUE <column> BETWEEN <lower> AND <upper>
  VALUE <column> IN (<values>)
  NO_FAIL [IN <column>]
  RESULT SET [EXACT|ORDERED|INCLUDES|EXCLUDES] [(<columns>)] (<tuples>)
  EXPRESSION <expr>
  UNIQUE <col1>[, <col2>, ...]

Description

## Overview ASSERT is a first-class SQL command that attaches declarative validation conditions to any executable statement. The engine executes the inner SQL first, then evaluates each assertion clause against the result. Assertions produce a structured result set with per-clause pass/fail status, making them suitable for automated demo testing, data quality checks, and CI/CD pipelines. ## Behavior Execution proceeds in three phases: 1. **Execute**: The inner SQL statement runs through the full execution pipeline, including table registration, macro expansion, Cypher resolution, and graph queries. The result set (or rows_affected count for DML) is captured. 2. **Evaluate**: Each assertion clause is evaluated independently against the captured result. ROW_COUNT compares the total row count. VALUE extracts a column value from rows matching an optional WHERE filter. NO_FAIL scans for the string 'FAIL' in a specified column (or auto-detects a result/status column). RESULT SET compares expected tuples against actual rows using the selected mode (default exact match, ORDERED, INCLUDES, or EXCLUDES). EXPRESSION evaluates an arbitrary boolean SQL expression. UNIQUE checks that the specified columns contain no duplicate values. VALUE BETWEEN validates that a column falls within an inclusive range. VALUE IN verifies that every row's column value is a member of the specified set. 3. **Report**: A summary result set is returned with columns: assertion_index, kind, severity, passed, message, expected, and actual. If any ERROR-severity assertion fails, the statement returns an error and pipeline execution halts. Multiple ASSERT clauses may be stacked before a single inner statement. Each clause is parsed as a separate AssertClause with its own severity, and all are evaluated against the same result set. ### Comparison Operators ROW_COUNT and VALUE assertions support =, !=, >, <, >=, <=, IS NULL, IS NOT NULL, LIKE, and NOT LIKE. ### Result Set Modes - **Default (no keyword)**: When no mode keyword is specified, the actual rows must match the expected tuples exactly, in any order. - **ORDERED**: The actual rows must match the expected tuples in the specified order. - **INCLUDES**: The expected tuples must appear somewhere in the actual result (other rows are allowed). - **EXCLUDES**: The expected tuples must not appear anywhere in the actual result. An optional column projection list restricts comparison to the named columns. ## Compatibility ASSERT is a DeltaForge extension with no direct equivalent in standard SQL. It replaces comment-based assertion parsing (e.g., `-- Expected: 34 rows`) with a structured, engine-parsed command. The inner SQL statement can be any valid DeltaForge SQL, including Cypher MATCH queries, PL/pgSQL blocks, and Delta commands.

Parameters

Name	Type	Description
severity		Controls the behavior when an assertion fails. ERROR (the default) fails the statement and halts pipeline execution. WARNING logs a warning message but allows execution to continue. INFO records the check result as informational and always continues. Each stacked assertion clause may specify its own severity independently.
assertions		One or more assertion clauses.
inner_sql		The SQL statement to execute before applying assertions. Supports SELECT, Cypher MATCH, INSERT, UPDATE, DELETE, and any other executable statement. For DML operations, row count assertions validate the rows_affected metric rather than the result set.

Examples

-- Verify an exact row count
ASSERT ROW_COUNT = 34
SELECT * FROM demo.karate.vertices;

-- Check a scalar value with a WHERE filter and WARNING severity
ASSERT WARNING VALUE total >= 500 WHERE product = 'Widget A'
SELECT product, SUM(amount) AS total
FROM warehouse.sales.line_items
GROUP BY product;

-- Scan for failures in a self-verification query
ASSERT NO_FAIL
SELECT check_name,
       CASE WHEN COUNT(*) = 15 THEN 'PASS' ELSE 'FAIL' END AS result
FROM demo.xml.books;

-- Validate exact result set with tuples (any order)
ASSERT RESULT SET
  ('Electronics', 6),
  ('Furniture',   6),
  ('Stationery',  6),
  ('Audio',       4)
SELECT category, COUNT(*) AS cnt
FROM warehouse.products
GROUP BY category;

-- Stack multiple assertions with different severities
ASSERT ERROR VALUE order_count > 0 WHERE region = 'US'
ASSERT WARNING VALUE order_count > 100 WHERE region = 'US'
ASSERT INFO VALUE order_count > 1000 WHERE region = 'US'
SELECT region, COUNT(*) AS order_count
FROM production.orders
GROUP BY region;

-- Verify column uniqueness
ASSERT UNIQUE customer_id
SELECT * FROM warehouse.sales.customers;

Pitfalls

• ASSERT without a severity keyword defaults to ERROR, which halts pipeline execution on failure. Use WARNING or INFO for non-critical checks that should not block downstream statements.
• NO_FAIL scans column values for the literal string 'FAIL' (case-insensitive). If no column is specified with the IN clause, the engine auto-detects a column named 'result' or 'status'. Queries that do not produce such a column will cause the assertion to pass vacuously.
• RESULT SET comparisons coerce all values to strings before matching. Floating-point results may fail exact matching due to formatting differences. Use EXPRESSION with a tolerance check for approximate numeric comparisons.

See Also

• CONCURRENT_BLOCK