On this page

• SQL checks (custom_sql)
  • Minimal example
  • Options
  • Expectation entries
  • Base fields
  • Generated output
  • Related guides

SQL checks (custom_sql)

Runs a SQL query whose returned rows are treated as violations.

Minimal example

Add this entry to the actions list in your flowgroup.

- name: orders_negative_amount
  type: test
  test_type: custom_sql
  source: v_orders_clean
  sql: "SELECT * FROM v_orders_clean WHERE amount < 0"
  expectations:
    - name: no_negative_rows
      expression: "amount >= 0"
      on_violation: fail

Options

Field	Type	Required	Default	Description
sql	string	Yes	—	Query whose returned rows represent violations.
source	string	No	—	Table or view the query reads from.
expectations	list	No	—	Named expectations attached to the query; each carries its own expression and on_violation.

Expectation entries

Each item under expectations has these fields:

Field	Default	Meaning
name	required	Name used in the generated expectation dictionary.
expression	required	SQL boolean expression evaluated for each query row.
on_violation	fail	fail, warn or drop. Set a supported value; unrecognised values produce no decorator for the entry.

Base fields

Common to every test action, regardless of test_type.

Field	Type	Required	Default	Description
name	string	Yes	—	Action name; unique within the flowgroup.
type	string	Yes	—	Must be test.
test_type	string	Yes	—	One of the nine supported test types. Any other value is rejected at validation.
target	string	No	tmp_test_<name>	Name of the generated temporary table.
description	string	No	Test: <test_type>	Table comment and function docstring.
depends_on	list[string]	No	—	Extra upstream table or view references added to the dependency graph. See Dependency analysis reference.
on_violation	string	No	fail	fail, warn, or drop; invalid values coerced to fail. Selects the decorator for the seven built-in types; for custom_sql / custom_expectations the decorator comes from each entry in expectations.
test_id	string	No	—	Correlates this test with a test-reporting hook.

Run lhp validate --env dev --include-tests and lhp generate --env dev --include-tests to include test actions. For result delivery, see Test reporting configuration.

A test action always reads its source with batch SQL. Point it at a table or a batch view: a streaming view fails on Databricks, and lhp validate reports it as LHP-VAL-070.

Generated output

Every test action generates a function decorated with @dp.table(name="<target>", comment="<description>", temporary=True) that returns the violation query, plus one expectation decorator per violation bucket. The on_violation value maps to the decorator as follows:

on_violation	Decorator	Behavior
fail	@dp.expect_all_or_fail	Pipeline update fails when any row violates.
warn	@dp.expect_all	Violations recorded as metrics; rows retained.
drop	@dp.expect_all_or_drop	Violating rows dropped from the output.

For the seven built-in types the action-level on_violation drives the decorator. For custom_sql and custom_expectations the decorators are built from each entry in expectations using that entry’s own on_violation.

Related guides

• Write a data test with a test action

• All test actions