> For the complete documentation index, see [llms.txt](https://v2.dataos.info/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://v2.dataos.info/references/dataos-resources/vulcan/core-concepts/assertions.md). # Assertions An assertion attaches an audit to a model and declares that the model must pass it. Assertions run after every model execution and halt your models if the audit finds bad data. Two terms come up constantly and are easy to mix up, so define them once and keep them distinct: * An **audit** is the validation rule itself: a named SQL query that returns rows when something is wrong. Audits live in `audits/*.sql`, are defined with `AUDIT (name ...)`, and are reusable across models. * An **assertion** is the attachment of an audit to a model, declared inside a `MODEL (...)` block via `assertions (...)`. An assertion is a property on a model, not a file or folder. The `assertions (...)` block holds both built-in audit functions (such as `not_null`, `unique_values`, `forall`, `accepted_values`) and references to named audits. An audit is the rule ("prices must be positive"). An assertion is you saying "this model follows that rule." The mental model across all three data quality artifacts: **DQ checks watch, audits define, assertions attach.** A [DQ check](/references/dataos-resources/vulcan/core-concepts/data-quality.md) (`kind: dq`) is the third artifact: a declarative profile-and-rule pack that monitors quality over time and never blocks. ```sql -- Define the AUDIT (the rule) AUDIT (name check_positive_price); SELECT * FROM @this_model WHERE price <= 0; -- Make an ASSERTION on your model (attach the audit) MODEL ( name products, assertions (check_positive_price) -- declare this audit should pass ); ``` {% hint style="info" %} You might see code using `audits` instead of `assertions` as the model property name. Both work identically. This documentation uses `assertions` for the property and `audit` for the rule it points to. {% endhint %} Unlike [tests](/references/dataos-resources/vulcan/core-concepts/unit-test.md), which you run manually to verify logic, assertions run automatically whenever you apply a [plan](/references/dataos-resources/vulcan/core-concepts/plan.md). They catch data quality issues early, whether the bad data comes from external vendors, upstream teams, or your own model changes. {% hint style="info" %} For incremental by time range models, audits only run on the intervals being processed, not the entire table. This keeps things fast and focused on what changed. {% endhint %} ## How assertions work When an assertion's audit fails, Vulcan stops everything: no plan application, no run execution. This is by design. It is better to catch problems early than to let bad data flow downstream. When you run a model, Vulcan: 1. **Evaluates the model.** Runs your model SQL (inserts new data, rebuilds the table, and so on). 2. **Runs the audit query.** Executes each attached audit's SQL against the newly updated table. For incremental models, this checks only the intervals you are processing. 3. **Checks the results.** If an audit query returns any rows, the assertion fails and everything stops. Audits query for bad data. If an audit returns rows, that is a problem. If it returns zero rows, you are good to go. ### Plan vs run `plan` and `run` treat assertion failures differently: * **`plan` (the safe way).** Vulcan evaluates and audits all modified models before promoting them to production. If an assertion fails, the plan stops and your production table is untouched. Invalid data stays isolated and never reaches production. * **`run` (the direct way).** Vulcan evaluates and audits models directly against the production environment. If an assertion fails, the run stops, but the invalid data is already in production. The block prevents that bad data from being used to build downstream models. For production changes, use `plan`. Use `run` when you are confident or doing quick iterations. ### Fixing a failed assertion When an assertion fails, fix the root cause, not the symptom: 1. **Find the root cause.** Look at the audit query results. Which data failed? Check upstream models and sources. 2. **Fix the source.** If the bad data comes from an external source, fix it there, then run a [restatement plan](/references/dataos-resources/vulcan/core-concepts/plan.md#restatement) on the first Vulcan model that ingests it. Vulcan restates all downstream models automatically. If the problem is in a Vulcan model, update its logic and apply a `plan`. Vulcan re-evaluates all downstream models. ## User-defined audits You can write your own audits. They are SQL queries that should return zero rows. If they return rows, the audit found bad data and any assertion that attaches it fails. Audits live in `.sql` files in an `audits/` directory, or inline in your model files. You can put multiple audits in one file. ```sql AUDIT ( name assert_item_price_is_not_null, dialect spark ); SELECT * FROM sushi.items WHERE ds BETWEEN @start_ds AND @end_ds AND price IS NULL; ``` The `name` is what you reference when attaching the audit. If your query uses a different dialect than your project, set `dialect`. The `@start_ds` and `@end_ds` macros are filled in automatically for incremental models. Attach the audit to a model with an assertion: ```sql MODEL ( name sushi.items, assertions (assert_item_price_is_not_null) ); ``` ### Generic (parameterized) audits Audits can be parameterized, so one definition covers every model that follows the same shape. Use [macros](/references/dataos-resources/vulcan/core-concepts/macros.md) to make them flexible: ```sql AUDIT ( name does_not_exceed_threshold ); SELECT * FROM @this_model WHERE @column >= @threshold; ``` `@this_model` refers to the model being audited and handles incremental models correctly. `@column` and `@threshold` are parameters you specify when you attach the audit: ```sql MODEL ( name sushi.items, assertions ( does_not_exceed_threshold(column := id, threshold := 1000), does_not_exceed_threshold(column := price, threshold := 100) ) ); ``` You can use the same audit multiple times on the same model with different parameters, and you can set default parameter values: ```sql AUDIT ( name does_not_exceed_threshold, defaults ( threshold = 10, column = id ) ); SELECT * FROM @this_model WHERE @column >= @threshold; ``` ### Global assertions Attach audits to every model by default through `model_defaults`: ```yaml model_defaults: assertions: - assert_positive_order_ids - does_not_exceed_threshold(column := id, threshold := 1000) ``` {% hint style="info" %} In `model_defaults`, both `audits` and `assertions` work as the property name, for backward compatibility. {% endhint %} ### Inline audits Define an audit in the model file when it is specific to one model. Define audits before (or alongside) the `MODEL` that uses them: ```sql MODEL ( name sushi.items, assertions (does_not_exceed_threshold(column := id, threshold := 1000), price_is_not_null) ); SELECT id, price FROM sushi.seed; AUDIT (name does_not_exceed_threshold); SELECT * FROM @this_model WHERE @column >= @threshold; AUDIT (name price_is_not_null); SELECT * FROM @this_model WHERE price IS NULL; ``` ### Naming Avoid SQL keywords when naming audit parameters. If you must use one (such as `values`), quote it: ```sql MODEL ( name sushi.items, assertions ( my_audit(column := a, "values" := (1,2,3)) ) ); ``` ## Built-in audits Vulcan ships a suite of built-in audits ready to attach as assertions, with no SQL to write. All built-in audits are blocking. ### Generic `forall` is the most flexible built-in. It checks arbitrary boolean SQL expressions, and all criteria must pass: ```sql MODEL ( name sushi.items, assertions ( forall(criteria := ( price > 0, LENGTH(name) > 0 )) ) ); ``` ### Row count and NULL values | Audit | What it checks | | ------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------- | | `number_of_rows(threshold := 10)` | The model has MORE THAN the threshold number of rows. A model with exactly `n` rows fails if `threshold = n`. | | `not_null(columns := (id, customer_id))` | The listed columns are never NULL. | | `at_least_one(column := zip)` | The column has at least one non-NULL value. | | `not_null_proportion(column := zip, threshold := 0.8)` | At least the threshold fraction of rows are non-NULL. | ### Specific data values | Audit | What it checks | | ---------------------------------------------------------------- | ----------------------------------------------------- | | `not_constant(column := customer_id)` | The column has at least two distinct non-NULL values. | | `unique_values(columns := (id, item_id))` | Each listed column has unique values. | | `unique_combination_of_columns(columns := (id, ds))` | The combination of columns is unique. | | `accepted_values(column := name, is_in := ('Hamachi', 'Unagi'))` | Values are in the allowed set. | | `not_accepted_values(column := name, is_in := ('Hamburger'))` | Values are never in the rejected set. | {% hint style="info" %} Rows with `NULL` usually pass `accepted_values`. Combine it with `not_null` to reject NULLs. `not_accepted_values` does not reject NULLs either. {% endhint %} ### Numeric | Audit | What it checks | | --------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `sequential_values(column := item_id, interval := 1)` | Values are sequential with the given interval. | | `accepted_range(column := price, min_v := 1, max_v := 100)` | Values fall in the range. Add `inclusive := false` for an exclusive range: `accepted_range(column := price, min_v := 0, inclusive := false)` rejects rows where `price = 0`. | | `mutually_exclusive_ranges(lower_bound_column := min_price, upper_bound_column := max_price)` | Each row's range does not overlap any other row's range. Use for pricing tiers, time slots, or any segmentation where ranges must not overlap. Each row pair `(lower_bound, upper_bound)` must produce non-intersecting ranges across all rows. | ### Character `not_empty_string`, `string_length_equal(column := zip, v := 5)`, `string_length_between(column := name, min_v := 5, max_v := 50)`, `valid_uuid`, `valid_email`, `valid_url`, `valid_http_method`, `match_regex_pattern_list`, `not_match_regex_pattern_list`, `match_like_pattern_list`, and `not_match_like_pattern_list` check string format and patterns. Add `inclusive := false` to `string_length_between` for exclusive bounds: `string_length_between(column := code, min_v := 2, max_v := 10, inclusive := false)` rejects lengths of exactly 2 or 10. ```sql MODEL ( name dim.users, assertions ( valid_email(column := email), string_length_equal(column := zip, v := 5) ) ); ``` `valid_url` accepts values beginning with `http://`, `https://`, or `ftp://`. `valid_http_method` accepts one of `GET`, `POST`, `PUT`, `DELETE`, `PATCH`, `HEAD`, `OPTIONS`, `TRACE`, `CONNECT`. The pattern-list audits take a `patterns := (...)` tuple. `match_regex_pattern_list` / `not_match_regex_pattern_list` match regexes; `match_like_pattern_list` / `not_match_like_pattern_list` use SQL `LIKE`, where `%` matches any characters: ```sql MODEL ( name sales.customers, assertions ( match_regex_pattern_list(column := todo, patterns := ('^\d.*', '.*!$')), match_like_pattern_list(column := name, patterns := ('jim%', 'pam%')) ) ); ``` {% hint style="warning" %} Engines vary in how they handle character sets and languages. Test character audits against your engine. {% endhint %} ### Statistical Statistical audits need tuning to avoid false positives during normal variance. Start with wide ranges and tighten them as you learn what is normal. | Audit | What it checks | | --------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `mean_in_range(column := age, min_v := 21, max_v := 50)` | The column average is in range. Add `inclusive := false` for exclusive bounds: `mean_in_range(column := price, min_v := 10.0, max_v := 1000.0, inclusive := false)` rejects a mean of exactly 10.0 or 1000.0. | | `stddev_in_range(column := age, min_v := 2, max_v := 5)` | The standard deviation is in range. Add `inclusive := false` to make bounds exclusive: a stddev equal to `min_v` or `max_v` will fail. | | `z_score(column := age, threshold := 3)` | No value exceeds the z-score threshold (outlier detection). | | `kl_divergence(column := age, target_column := reference_age, threshold := 0.1)` | The divergence between two distributions stays within the threshold. | | `chi_square(column := user_state, target_column := user_type, critical_value := 6.635)` | The chi-square statistic for two categorical columns stays within the critical value. | `z_score` is calculated as `ABS(([row value] - [column mean]) / NULLIF([column standard deviation], 0))`. `kl_divergence` uses the symmetrised Kullback-Leibler divergence (also called Jeffreys divergence or Population Stability Index); lower values mean the two distributions are more similar. For `chi_square`, critical values come from a chi-square table indexed by degrees of freedom, or you can compute them in Python: ```python from scipy.stats import chi2 chi2.ppf(0.95, 1) # critical value for p := 0.95, degrees of freedom := 1 ``` ## Running audits When you apply a plan, Vulcan automatically runs the audits for every assertion on the models being evaluated. If an audit fails, Vulcan halts the models immediately. Run audits manually for testing or debugging: ```bash $ vulcan -p project audit --start 2022-01-01 --end 2022-01-02 Found 1 audit(s). assert_item_price_is_not_null FAIL. Finished with 1 audit error(s). Failure in audit assert_item_price_is_not_null for model sushi.items (audits/items.sql). Got 3 results, expected 0. SELECT * FROM vulcan.sushi__items__1836721418_83893210 WHERE ds BETWEEN '2022-01-01' AND '2022-01-02' AND price IS NULL Done. ``` The output shows which audit failed, which model it was attached to, the exact query, and how many rows it found when it expected zero. ## Skipping an audit To temporarily disable an audit while debugging, set `skip true`. Use this sparingly: a skipped audit will not catch problems. ```sql AUDIT ( name assert_item_price_is_not_null, skip true ); SELECT * FROM sushi.items WHERE ds BETWEEN @start_ds AND @end_ds AND price IS NULL; ``` ## Troubleshooting * **Audit fails unexpectedly.** Run it with `--verbose` to see the exact query and the failing rows, then fix the data or adjust the audit. * **Audit too strict.** Review thresholds. Statistical audits especially need tuning. Start wide and tighten. * **Performance.** Make sure audit queries use indexes on the columns they check. For incremental models, audits only run on processed intervals, and you can add date filters to the audit query. ## Related docs * [Data quality](/references/dataos-resources/vulcan/core-concepts/data-quality.md): DQ checks (`kind: dq`) that monitor quality over time without blocking. * [Test](/references/dataos-resources/vulcan/core-concepts/unit-test.md): validate model logic against fixtures, separate from runtime data checks. * [Plan](/references/dataos-resources/vulcan/core-concepts/plan.md): how assertions run during plan apply and restatement. --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://v2.dataos.info/references/dataos-resources/vulcan/core-concepts/assertions.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.