Audits

Audits are SQL queries that verify data quality. Violation audits return invalid rows. Measurement audits return a value that SQLBuild evaluates against authored thresholds and sample policy. SQLBuild can run audits before table promotion or incremental DML so error-severity failures do not reach the target.

How audits work

Violation audits pass when their query returns zero rows. Measurement audits produce one value and optionally a sample count; their outcome is pass, warn, fail, or insufficient.

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.

Measurement audits

A reusable measurement audit separates the aggregate query from optional bounded evidence:

-- audits/generic/valid_order_rate.sql
AUDIT (
  evaluation measurement,
  value valid_rate,
  sample_count total_rows,
  sample_unit rows
);

MEASURE (
  SELECT
    COUNT(*) AS total_rows,
    100.0 * AVG(CASE WHEN @condition THEN 1 ELSE 0 END) AS valid_rate
  FROM @relation
);

EVIDENCE (
  SELECT * FROM @relation WHERE NOT (@condition)
);

Attach threshold and sample policy where the audit is used:

MODEL (
  audits [
    valid_order_rate (
      condition "order_id IS NOT NULL",
      minimum_samples 100,
      evidence_limit 20,
      thresholds (warn (below 99.9), error (below 99))
    )
  ]
);

minimum_samples keeps low-volume measurements distinct as insufficient rather than inventing a pass or failure. Evidence is diagnostic and bounded by evidence_limit; the measurement and threshold determine the outcome.

Audit factories

Use a Python audit factory when many related audit instances should be generated from one reviewed declaration:

from sqlbuild.audits import AuditCase, AuditSeverity, audit_factory


@audit_factory
def order_quality():
    return [
        AuditCase(
            name="positive_amount",
            definition="expression_is_true",
            arguments={"expression": "amount > 0"},
            severity=AuditSeverity.ERROR,
        )
    ]

Attach it with MODEL (audit_factories [order_quality]). Generated cases compile to the same audit contract as directly authored instances.

Result history

Native warehouse adapters best-effort append confirmed audit outcomes to _sqlbuild_audit_results. Rows are immutable and use deterministic IDs, so retrying the same result is idempotent. Projection failure is reported separately and does not change the audit outcome or command exit code. Lifecycle sinks can also consume the corresponding audit_completed fact, which is published as each audit finishes.

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:

Audit	Description	Parameters
not_null	Fails if any row has a NULL value in the column	Column-level only
unique	Fails if any non-NULL value appears more than once	Column-level only
accepted_values	Fails if any non-NULL value is not in the allowed list	values - list of allowed values
relationships	Fails if any non-NULL value does not exist in the referenced column	to - 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:

Parameter	Description
@column	The column name (auto-populated for column-level audits)
@relation	The target relation (auto-populated from the attached model or source)
@'name'	A quoted parameter passed from the audit declaration (e.g. @'values')
@name	An unquoted parameter passed from the audit declaration (e.g. @expression)

Generic and singular audit SQL uses macros, constants, and enums available from the audit file under audits/, not from a model or source that uses the audit. See How Visibility Works.

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. Their canonical home is audits/singular/, and they reference models directly. They’re useful for one-off checks that don’t fit a reusable template. For backward compatibility, singular audits directly under audits/ or another non-generic child directory continue to compile.

-- audits/singular/orders_have_payments.sql
AUDIT (
  name "completed_orders_have_payments",
  severity error
);

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

Severity	Behavior
error	Blocks the build. Staging table is not promoted, DML is not applied.
warn	Reports 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:

Scope	Behavior
final	Run once against the staged table before promotion (default).
delta_and_final	Run 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.

Standalone audits run serially unless concurrency is configured explicitly, through SQLBUILD_CONCURRENCY, or in project settings. For example, sqb audit --concurrency 8 runs up to eight selected audits at once, using one warehouse connection per active worker. Increase this limit deliberately because parallel queries can increase warehouse load and cost. See sqb audit for precedence, ordering, and cancellation details.