> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sqlbuild.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Audits

> Data quality checks that run before data reaches the target table.

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:

| 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:

```sql theme={null}
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.

```sql theme={null}
-- 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`) |

### Attaching custom generic audits

```sql theme={null}
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.

```sql theme={null}
-- 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:

```yaml theme={null}
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`:

```toml theme={null}
[settings]
default_audit_severity = "warn"
```

Override per audit instance in the `MODEL()` header:

```sql theme={null}
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. |

```sql theme={null}
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

```bash theme={null}
sqb audit
```

This runs all audits without rebuilding any models.