Skip to main content
Audits are SQL queries that verify data quality. If an audit returns rows, something is wrong. SQLBuild runs audits before data is promoted to the target table, so bad data never reaches production.

​
How audits work

An audit is a SELECT query that returns rows that violate a condition. Zero rows means the audit passes. Any rows returned means a failure. For error severity audits:
  • Full table builds: SQLBuild materializes into a staging table, runs audits against it, and only promotes to the target if all audits pass. If any fail, the staging table is kept for inspection and the production table is untouched.
  • Incremental models: Delta-phase audits validate each batch before DML is applied. If an audit fails, the batch is not applied.
For warn severity audits, the build continues and the failure is reported in the output.

​
Built-in audits

SQLBuild includes four generic audits out of the box. You do not need to define these in audits/generic/ - they are available automatically:
AuditDescriptionParameters
not_nullFails if any row has a NULL value in the columnColumn-level only
uniqueFails if any non-NULL value appears more than onceColumn-level only
accepted_valuesFails if any non-NULL value is not in the allowed listvalues - list of allowed values
relationshipsFails if any non-NULL value does not exist in the referenced columnto - target relation, field - target column

​
Using built-in audits

Attach them in the MODEL() header like any generic audit: 
MODEL (
  materialized view,
  tags [staging],
  columns (
    order_id (audits [not_null, unique]),
    customer_id (audits [not_null]),
    status (
      audits [
        accepted_values (values ["placed", "preparing", "ready", "completed", "cancelled"]),
      ],
    ),
    payment_method (
      audits [
        relationships (to "stg_payments", field "method"),
      ],
    ),
  ),
);

​
Overriding built-in audits

If you define a generic audit with the same name as a built-in (e.g. audits/generic/not_null.sql), your definition takes precedence. SQLBuild emits a warning so you’re aware of the override: 
warning[P003]: project audit 'not_null' overrides built-in audit 'not_null'

​
Custom generic audits

Beyond the built-ins, you can define reusable SQL templates under audits/generic/. They use @parameter placeholders that are resolved by the audit engine at compile time. 
-- audits/generic/expression_is_true.sql
AUDIT ();

SELECT *
FROM @relation
WHERE NOT (@expression)

​
Audit parameters

Generic audit SQL uses @name for parameter placeholders. These are resolved by the audit engine, not the general SQL interpolation system:
ParameterDescription
@columnThe column name (auto-populated for column-level audits)
@relationThe target relation (auto-populated from the attached model or source)
@'name'A quoted parameter passed from the audit declaration (e.g. @'values')
@nameAn unquoted parameter passed from the audit declaration (e.g. @expression)

​
Attaching custom generic audits

MODEL (
  materialized table,
  audits [
    expression_is_true (
      name "revenue is non-negative",
      expression "total_revenue_cents >= 0",
    ),
  ],
);

​
Singular audits

Singular audits are standalone SQL files under audits/ (outside the generic/ directory) that reference models directly. They’re useful for one-off checks that don’t fit a reusable template. 
-- audits/orders_have_payments.sql
AUDIT ();

SELECT o.order_id
FROM __ref("fact_orders") o
LEFT JOIN __ref("stg_payments") p ON o.order_id = p.order_id
WHERE p.payment_id IS NULL
  AND o.order_status = 'completed'
SQLBuild automatically infers which model a singular audit attaches to based on the __ref() calls in the query. If the audit references a single model, it attaches to that model. If it references multiple models, SQLBuild attaches it to the latest (most downstream) model in the DAG. If attachment can’t be inferred, the audit runs at the end of the build.

​
Source audits

Sources support the same audit system as models. Audits attached to sources run before any dependent model is built: 
sources:
  - name: raw__orders
    columns:
      - name: id
        audits:
          - not_null
          - unique
    audits:
      - expression_is_true:
          name: no future orders
          expression: "ordered_at <= CURRENT_TIMESTAMP"
If a source audit with error severity fails, all downstream models that depend on that source are blocked. This lets you catch data quality issues at the source before any transformations run.

​
Severity

SeverityBehavior
errorBlocks the build. Staging table is not promoted, DML is not applied.
warnReports a warning but allows the build to continue.
Set the default severity in sqlbuild_project.toml: 
[settings]
default_audit_severity = "warn"
Override per audit instance in the MODEL() header: 
columns (
  order_id (audits [not_null (severity error)]),
),

​
Run scope

Audits on incremental models can run at different lifecycle phases:
ScopeBehavior
finalRun once against the staged table before promotion (default).
delta_and_finalRun against each delta batch before DML, then again against the target after all batches complete.
MODEL (
  materialized incremental,
  ...
  columns (
    activity_hour (audits [not_null (run_scope delta_and_final)]),
  ),
  audits [
    expression_is_true (
      name "orders placed is non-negative",
      expression "orders_placed >= 0",
      run_scope delta_and_final,
    ),
  ],
);
Delta-phase audits with error severity block DML before the target is updated. This is visible in the build output as audit (d) for delta-phase and audit (f) for final-phase: 
  10/13  table     hourly_order_activity  (delete_insert)                OK     0.16s
           audit (d) expression_is_true                                  PASS  4/4
           audit (d) not_null (activity_hour)                            PASS  4/4
           audit (f) expression_is_true                                  PASS
           audit (f) not_null (activity_hour)                            PASS
The 4/4 indicates the audit passed for all 4 microbatch batches. If a model is not incremental, delta_and_final degrades to final automatically.

​
Running audits standalone

sqb audit
This runs all audits without rebuilding any models.