Constraints

Validation is part of execution — not a separate step

Constraints stop bad data before it enters your tables.

If a single row violates a constraint, the entire model fails and nothing is written.

No separate testing framework. No post-hoc checks. Validation is part of execution.

Why This Matters

Most data tools validate data after it’s already written. By then, bad data is already in your tables.

OndatraSQL validates before insertion — so invalid data never lands.

Mental Model

Constraints are enforced on the result of your query before it is written.

If they fail:

Nothing is inserted
Downstream models do not run

Usage

Add constraints directly in your model:

-- @constraint: column_name PATTERN

You can define multiple constraints per model.

-- @constraint: email EMAIL
-- @constraint: total >= 0
-- @constraint: status IN ('pending', 'shipped', 'cancelled')
-- @constraint: customer_id REFERENCES customers(id)

If any of these fail, the model does not run.

Common Patterns

Pattern	Example	What it checks
`PRIMARY KEY`	`id PRIMARY KEY`	NOT NULL + UNIQUE
`NOT NULL`	`email NOT NULL`	No NULL values
`UNIQUE`	`code UNIQUE`	No duplicates
`IN (...)`	`status IN ('active', 'inactive')`	Allowed values
`>= N`	`total >= 0`	Minimum value
`EMAIL`	`email EMAIL`	Email format
`REFERENCES`	`customer_id REFERENCES customers(id)`	Foreign key
`NULL_PERCENT`	`email NULL_PERCENT < 10`	Max NULL percentage

All 26 Patterns

Identity & Nullability

Pattern	Example	Description
`PRIMARY KEY`	`id PRIMARY KEY`	NOT NULL + UNIQUE combined
`NOT NULL`	`email NOT NULL`	Column must not be NULL
`UNIQUE`	`code UNIQUE`	No duplicate values
`(col1, col2) UNIQUE`	`(year, month) UNIQUE`	Composite uniqueness
`NOT EMPTY`	`name NOT EMPTY`	Not NULL and not empty string

Comparison

Pattern	Example	Description
`>= N`	`age >= 0`	Greater than or equal
`<= N`	`score <= 100`	Less than or equal
`> N`	`amount > 0`	Greater than
`< N`	`discount < 1`	Less than
`= N`	`version = 1`	Equal to
`!= N`	`status != 0`	Not equal to
`BETWEEN x AND y`	`rating BETWEEN 1 AND 5`	Range check

Membership

Pattern	Example	Description
`IN (...)`	`status IN ('active', 'inactive')`	Allowed values (auto-quotes unquoted strings)
`NOT IN (...)`	`role NOT IN ('deleted')`	Forbidden values

Pattern Matching

Pattern	Example	Description
`LIKE`	`email LIKE '%@%'`	SQL LIKE pattern
`NOT LIKE`	`name NOT LIKE 'test%'`	Inverse LIKE
`MATCHES`	`phone MATCHES '^\+[0-9]+'`	Regular expression
`EMAIL`	`email EMAIL`	Email format validation
`UUID`	`id UUID`	UUID format validation

String Length

Pattern	Example	Description
`LENGTH BETWEEN a AND b`	`name LENGTH BETWEEN 1 AND 255`	String length range
`LENGTH = N`	`code LENGTH = 3`	Exact string length

Referential & Custom

Pattern	Example	Description
`REFERENCES`	`customer_id REFERENCES customers(id)`	Foreign key check
`CHECK`	`total CHECK (total >= 0)`	Custom SQL expression

Statistical

Pattern	Example	Description
`AT_LEAST_ONE`	`email AT_LEAST_ONE`	At least one non-NULL value
`NOT_CONSTANT`	`status NOT_CONSTANT`	At least 2 distinct values
`NULL_PERCENT`	`email NULL_PERCENT < 10`	Maximum NULL percentage
`DISTINCT_COUNT OP N`	`status DISTINCT_COUNT >= 3`	Number of distinct values (cardinality)
`DUPLICATE_PERCENT < N`	`email DUPLICATE_PERCENT < 5`	Maximum duplicate percentage
`SEQUENTIAL`	`id SEQUENTIAL`	No gaps in integer sequence
`SEQUENTIAL(N)`	`id SEQUENTIAL(5)`	Gaps allowed only by step N
`NO_OVERLAP`	`(start_date, end_date) NO_OVERLAP`	Time intervals don’t overlap

Full Example

-- @kind: merge
-- @unique_key: order_id
-- @constraint: order_id PRIMARY KEY
-- @constraint: total >= 0
-- @constraint: status IN ('pending', 'shipped', 'delivered', 'cancelled')
-- @constraint: status DISTINCT_COUNT >= 2
-- @constraint: customer_id REFERENCES customers(id)
-- @constraint: email EMAIL
-- @constraint: email DUPLICATE_PERCENT < 10
-- @constraint: order_date NOT NULL

SELECT order_id, customer_id, email, total, status, order_date
FROM raw.orders

Compared to Traditional Validation

Traditional approach:

Write tests separately (dbt tests, Great Expectations)
Run after data is written
Failures require cleanup

OndatraSQL:

Define constraints in the model
Run before data is written
Fail fast — no bad data enters the table