Validated Model

Combine constraints and audits for comprehensive data quality

This example demonstrates a model with multiple layers of validation: constraints, audits, and warnings.

1. Source Data

-- models/raw/orders.sql
-- @kind: table

SELECT * FROM (VALUES
    (1, 101, 'alice@example.com', 150.00, 'shipped',    '2026-03-28 10:00:00'),
    (2, 102, 'bob@example.com',   250.00, 'pending',    '2026-03-28 11:00:00'),
    (3, 101, 'alice@example.com', 75.50,  'delivered',   '2026-03-29 09:00:00'),
    (4, 103, 'carol@example.com', 500.00, 'processing',  '2026-03-29 14:00:00'),
    (5, 102, 'bob@example.com',   120.00, 'shipped',     '2026-03-30 08:00:00')
) AS t(order_id, customer_id, email, total, status, updated_at)

-- models/staging/stg_customers.sql
-- @kind: table

SELECT * FROM (VALUES
    (101, 'Alice'),
    (102, 'Bob'),
    (103, 'Carol')
) AS t(customer_id, name)

2. Validated Model

-- models/staging/stg_orders.sql
-- @kind: merge
-- @unique_key: order_id
-- @incremental: updated_at

-- Constraints (checked BEFORE insert — fail prevents materialization)
-- @constraint: order_id PRIMARY KEY
-- @constraint: customer_id NOT NULL
-- @constraint: total >= 0
-- @constraint: status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')
-- @constraint: email EMAIL
-- @constraint: customer_id REFERENCES staging.stg_customers(customer_id)

-- Audits (checked AFTER insert — fail triggers rollback)
-- @audit: row_count > 0
-- @audit: mean(total) BETWEEN 10 AND 5000
-- @audit: min(total) >= 0

-- Warnings (logged only — don't fail the model)
-- @warning: total NULL_PERCENT < 5
-- @warning: email NOT EMPTY

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

3. Run

ondatrasql run

Running 3 models...
[OK] raw.orders (table, backfill, 5 rows, 215ms — first run)
[OK] staging.stg_customers (table, backfill, 3 rows, 172ms — first run)
[OK] staging.stg_orders (merge, backfill, 5 rows, 500ms — first run)

Validation Flow

SQL executes — the SELECT runs and results are stored in a temp table
Constraints check — each constraint is validated against the temp data:
- order_id must be non-null and unique
- total must be >= 0
- status must be one of the allowed values
- customer_id must exist in stg_customers
- If any constraint fails, nothing is written
Materialization — data is merged into the target table
Audits check — post-insert validations run:
- At least 1 row must exist
- Average total must be between 10 and 5000
- Minimum total must be >= 0
- If any audit fails, the transaction rolls back
Warnings log — soft checks run and violations are logged (but don’t fail)