# Validation results (/docs/analytical-apps/pipeline/validation) When you configure validations on a dataset node - "this column is required", "these two columns must be unique together", "this code must exist in that other dataset" - the platform executes them on every pipeline run and publishes the results as two nodes in your pipeline (it also runs them whenever you look at the data, which is [further down this page](#in-the-data-grid)): | Node | What it holds | | --------------------- | --------------------------------------------------------------------------------- | | **Validation rules** | One row per validation you have configured, across every dataset node in the app. | | **Validation errors** | One row per dataset row that fails a validation. | Both appear on the canvas, have a Data tab, can be read by a SQL node and charted by a Visualisation widget. What is different is that the platform writes them: they are their own kind of node, with their own icon, and they are not offered when you add a node. They open read-only and update themselves whenever you change a validation - there is nothing to maintain, and nothing to break. Opening one shows the query the platform wrote, and you can **Run** it to see the current results, exactly as you would in a SQL node. They appear the moment your app has its first configured validation, and they stay after that. If you later remove every validation, both nodes remain and simply return no rows, because your own queries may already read them. ## What the nodes are called [#what-the-nodes-are-called] Normally they are called `validation_rules` and `validation_errors`, and the examples on this page use those names. They are not guaranteed, though. If one of your own nodes already holds the name, the platform takes the next free one - `validation_rules_2`, `validation_errors_2` and so on - rather than touching your node. Your existing node keeps its name and its contents; the generated pair simply sits beside it. If you are writing a query against these nodes, check the name on the canvas first and use the one your app actually has. ## Validation errors [#validation-errors] | Column | Meaning | | ---------------------- | ------------------------------------------------------------------- | | `dataset_technical_id` | The dataset node the failing row belongs to. | | `row_id` | That row's stable id. | | `column_technical_id` | The column, for a per-column rule. Empty for a condition-tree rule. | | `rule_id` | Joins to `validation_rules.rule_id`. | | `check_type` | The check that failed. | | `validation_error` | The message describing the failure. | `row_id` is the point of the table. It is the same stable row id the data grid uses, and every dataset node's table carries it as `_row_id` (see [SQL nodes](./sql-nodes.mdx#reading-a-datasets-row-id)), so you can join a finding back to the row it belongs to - substituting the name your app actually gave the node, if it is not the usual one: ```sql title="orders_with_findings.sql" SELECT o.*, e.validation_error FROM orders o LEFT JOIN validation_errors e ON e.dataset_technical_id = 'orders' AND e.row_id = o._row_id ``` A row with no matching finding is a trusted row. A row with several failing rules appears once per rule, so a row that breaks two checks gives you two rows, told apart by `rule_id`. ### Gating a SQL node on the findings [#gating-a-sql-node-on-the-findings] The most useful thing to build from this is a node that carries only the rows nothing reported - so everything downstream of it works on data you have already checked: ```sql title="trusted_orders.sql" SELECT o.* FROM orders o WHERE NOT EXISTS ( SELECT 1 FROM validation_errors e WHERE e.dataset_technical_id = 'orders' AND e.row_id = o._row_id ) ``` **Declare `validation_errors` as an input table**, beside the dataset: ```yaml input_tables: - orders - validation_errors ``` The validation nodes always run first, before any node you write, so a query that reads them always sees the findings for the run it is part of - whether or not you declare them. What the declaration buys is the arrow on the canvas: leave it out and the node still gives the right answer, but nothing on the diagram shows that it depends on the validation results, and the next person to read your pipeline will not see the link. To gate on a specific check rather than on any failure at all, add `AND e.check_type = 'required'` (or join `validation_rules` and filter there). ## In the data grid [#in-the-data-grid] You do not have to query the node to see what failed. Open a dataset node's **Data** tab and the failing cells are marked there, with the reason on hover: a strong mark on the cell a per-column rule named, a lighter one across every cell of a row a condition tree rejected. Hovering a marked cell shows the reason and only the reason - the usual tooltip that repeats the cell's own content steps aside for it. **The marks arrive with the rows.** The grid asks for a page of data and gets that page's findings back in the same answer, checked against the very rows it is about to show you. There is no run to name and no "checked at" date to read past, because the rows and the marks come from one and the same read. So: * **Nothing is stale, and nothing is withdrawn.** Save a change in the grid, or complete an import, and the rows you touched are re-checked the next time the grid reads them: a mark you fixed is gone, a rule you have just broken appears, and every other row keeps its own marks. You do not run the pipeline to see this. * **A row someone else added or changed** is checked the same way, on the next read. * **Every failing row in front of you is marked**, at any dataset size. A row's findings travel with the row, so a block of rows is marked the moment it appears - there is no fixed first slice, no partially marked screen and nothing that arrives late. * **The line above the grid describes the whole dataset**, not the page: either no findings, or how many rows fail something. That count does not move as you scroll, search or filter. Beside the count is **Show only failing rows**, which narrows the grid to the rows that fail at least one check, and back again. A dataset that has no validations of its own shows no line at all, even when other datasets in the same app have plenty. **A run's data view and a version's data view are marked too**, each by its own answer. A run shows exactly what that run found. A version is checked by the validations as they were configured when it was published, over the data as it stood then - so a historical view is never re-judged by today's rules. If the checks cannot be run at all - the platform could not resolve one of the tables a rule reads, say - the line says the validations could not be checked and nothing is marked. **Your data is unaffected**: every row is there, in order, with the correct total. A failing validation costs you the marks, never the data. ## On a Visualisation page [#on-a-visualisation-page] The same marks appear on a **table widget** backed by a dataset, so the people who read the app - rather than build it - see what the validation found without opening the pipeline. The reason is on hover there too, and the marks are checked against the same rows the table is showing. Three differences from the Data tab, all deliberate: * **The line above the table appears only when there is something to warn about**: that findings exist, or that the checks could not be run. A clean dataset is simply shown without a strip, so a page of tables does not turn into a page of notices. * **A grouped or summarised table is not marked.** Its rows are totals over many source rows, not the rows a rule named. Neither is a table over a SQL transform: a finding belongs to a dataset row, and a transform's output rows are not dataset rows. * **There is no "show only failing rows" control.** That is the author's tool, and it stays on the Data tab; a page is laid out by the person who built it. ### While a draft is open, the published app is checked as it was published [#marks-and-drafts] An open draft makes every dataset table on the published app read the **published snapshot** of the data rather than the live rows, so that a half-finished edit of yours cannot leak to readers (see [Draft and publish](../../overview/apps.mdx#draft-and-publish)). The marks follow that exactly: the table is checked by the validations of the version it is showing, over the snapshot it is showing. Your team therefore sees what the published app says about the published data, while the **Data** tab in the editor goes on checking the live rows against the rules you are editing. Neither borrows the other's answer, and neither has to wait for a run. ## Validation rules [#validation-rules] | Column | Meaning | | ---------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `rule_id` | The rule's identity within this run - `col:..` for a per-column rule, where `n` is its position in that column's list of checks, and `tree:.` for a condition tree, where `n` is its position in that dataset's list. | | `dataset_technical_id` | The dataset node the rule is configured on. | | `column_technical_id` | The column, for a per-column rule. Empty for a condition-tree rule. | | `rule_kind` | `column`, `complex`, or `overflow`. | | `check_type` | The operator for a per-column rule, or the top-level group for a condition tree. | | `parameter` | The value the check compares against, where the check takes one. | | `error_message` | The message the rule reports when it fires. | | `compilable` | Whether the rule actually runs. | | `skip_reason` | Empty when the rule runs, otherwise why it does not. | This table is not decoration: **Validation errors** reads `check_type` and `error_message` from it. So editing a condition-tree rule's message changes what your findings say, without anything else changing. ## What each check means [#what-each-check-means] Every check below shares one rule, which is worth reading before the table: > **A blank value passes every check except `Required`.** A blank value is an empty cell, or one holding only spaces. Without this rule an optional column with a pattern check would report every empty cell as an error, which is not what "optional" means. If blanks matter for a column, add `Required` beside the other check. | Check | Fails when | | ------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------- | | Required | The cell is empty. What "empty" means per column type is the table below. This is the only check a blank fails. | | Unique | Another row holds the same value. Every copy of a duplicate is reported, not just the later ones. Blank cells are never duplicates of each other. | | Equals | The value is not equal to the one you gave. | | Greater than | The value is not greater than the one you gave. | | Less than | The value is not less than the one you gave. | | Greater or equal | The value is below the one you gave. | | Less or equal | The value is above the one you gave. | | Matches pattern (`like`) | The text does not match the pattern. `%` stands for any run of characters, `_` for one character. | | Matches pattern (any case) (`ilike`) | The same, comparing without regard to upper and lower case. | | Starts with | The text does not start with the value you gave. | | Ends with | The text does not end with the value you gave. | | Matches expression (`regex`) | The text does not match the regular expression. | Two consequences follow from these, and both are deliberate: * **A value that cannot be compared fails a comparison.** If a column holding `abc` carries "greater than 0", that row is reported. The value cannot be compared, and an uncomparable value is not greater than zero. * **A check whose answer is unknown counts as a failure.** The platform never reads "unknown" as "fine". ### Which checks a column offers [#which-checks-a-column-offers] The checks a column offers follow from what that column holds, so a date is compared against a date and a yes/no against a yes/no: | Column type | Required | Unique | Equals | Greater / less than | The five text patterns | | ------------ | -------- | ------ | ------ | ------------------- | ---------------------- | | Text | yes | yes | no | no | yes | | Long text | yes | yes | no | no | yes | | Phone | yes | yes | no | no | yes | | Email | yes | yes | no | no | yes | | Integer | yes | yes | yes | yes | no | | Decimal | yes | yes | yes | yes | no | | Percent | yes | yes | yes | yes | no | | Currency | yes | yes | yes | yes | no | | Year | yes | yes | yes | yes | no | | Date | yes | yes | yes | yes | no | | Time | yes | yes | yes | yes | no | | Timestamp | yes | yes | yes | yes | no | | Boolean | yes | no | yes | no | no | | Dropdown | yes | yes | yes | no | no | | Multi-select | yes | no | no | no | no | | Attachment | yes | no | no | no | no | The rule behind the table: **Required** is offered everywhere; **Unique** wherever a cell holds one value that could identify a row (so not on a boolean, which has two values, nor on a multi-select or an attachment); **Equals** wherever there is one exact value to compare against; the ordered comparisons wherever values have an order; and the five text patterns on the four free-text types. On a text column, **Matches pattern** with no `%` already IS an exact match, which is why Equals is not offered there as a second way to spell the same check. **A check you configured earlier is never removed.** If you change a column's type after configuring its checks, the checks stay, keep running, and keep showing in the editor - they are simply no longer offered as something to add. ### What to type as the value [#what-to-type-as-the-value] A comparison's value has to be a value the column itself could hold: | Column type | Type this | Example | | ----------------------------------- | --------------------------- | --------------------- | | Integer, Decimal, Percent, Currency | a number | `1500`, `-3.5` | | Year | four digits | `2026` | | Date | `YYYY-MM-DD` | `2026-01-01` | | Time | `HH:mm` or `HH:mm:ss` | `12:30` | | Timestamp | `YYYY-MM-DDTHH:mm:ss` | `2026-01-01T08:00:00` | | Boolean | picked from the two options | `True` | | Dropdown | the option's exact text | `EU` | | Text, Long text, Phone, Email | any text (the pattern) | `A%` | A value outside that format leaves the rule **skipped** rather than guessed at: it appears in **Validation rules** with `compilable` false and `skip_reason` `operand_type_mismatch`, and every other rule keeps running. The editor marks the field as you type, so you normally see it before you save. Two details worth knowing: * The value is stored normalised, and that is what **Validation rules** reports back in `parameter`: `12:30` becomes `12:30:00`, `2026-01-01T08:00:00` becomes `2026-01-01 08:00:00`, and `YES` becomes `true`. * Decimal, Percent and Currency compare as floating-point numbers. A value carrying more precision than that can represent may not compare exactly equal, so **Equals** on a money column is best kept for round amounts. ### What "Required" means, per column type [#what-required-means-per-column-type] Required is one check - "the cell is not empty" - and what empty means follows from what the column stores: | Column type | Required fails when | | --------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Multi-select | No option is selected. A multi-select cell stores its options as one text, so no selection stores nothing at all - "at least one option selected" is exactly what Required means here. | | Boolean | The cell is empty. `false` is a VALUE and PASSES: to require the box be ticked, use **Equals** `True`. | | Attachment | The cell holds no attachment. | | Dropdown | No option is picked. | | Everything else | The cell is empty or holds only spaces. | A cell that holds the literal text `[]` in an attachment column is NOT treated as empty. The "blank" rule is one rule for every column type, and giving attachments their own would be a second rule to keep in step with it. ### Condition trees [#condition-trees] A complex validation combines checks into a tree, and the groups mean: | Group | Fails when | | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | AND | Any child check fails. | | OR | Every child check fails. | | NOT | The child check passes. | | UNIQUE | Another row holds the same combination of the listed columns. A row where ANY of those columns is blank is exempt - a partial key is not a key. | | REFERENCE | The row's value does not exist in the target dataset's column. A blank source value is exempt. The comparison ignores surrounding spaces, so `" 1"` matches `"1"`. | A check inside a tree behaves exactly like the per-column version, blank rule included - which has one consequence worth knowing: > `NOT (column matches "x%")` **fires on a blank column**, because the inner check > passes on a blank and `NOT` inverts it. If that is not what you want, put the check inside an `AND` beside `Required`. ## When a rule cannot run [#when-a-rule-cannot-run] A rule the platform cannot turn into a correct check is never run as a broken one. It is skipped, and the skip is visible: the rule is still a row in **Validation rules**, with `compilable` false and a `skip_reason`. Every other rule keeps running, and the pipeline run itself is unaffected. Find them with a SQL node, a Visualisation widget or an ad-hoc query: ```sql title="skipped_rules.sql" SELECT rule_id, dataset_technical_id, column_technical_id, check_type, skip_reason FROM validation_rules WHERE NOT compilable ``` | `skip_reason` | What to do | | ------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `operand_missing` | The check needs a value to compare against and none was given. Fill it in. | | `operand_not_numeric` | A check on a number column was given something that is not a number. Correct the value. | | `operand_type_mismatch` | The value is not one the column's type could hold - a date that is not `YYYY-MM-DD`, a year that is not four digits, a time outside 24 hours, a yes/no that is neither. See "What to type as the value" above. | | `column_not_found` | The rule names a column the dataset no longer has. Point it at an existing column, or remove it. | | `reference_target_not_found` | The dataset the reference points at no longer exists. | | `reference_target_not_dataset` | A reference must point at a dataset node, not at a SQL node or an import. | | `reference_column_not_found` | The reference names a column the target dataset does not have. | | `tree_incomplete` | A group has no children, a `NOT` has more than one, a `UNIQUE` lists no columns, or a `REFERENCE` maps no columns. Finish the rule. | | `rule_budget_exceeded` | See below. | ### The overflow row [#the-overflow-row] There is a ceiling on how many validations one app's generated nodes can represent. An app that goes past it gets one extra row in **Validation rules** with `rule_id` `overflow`, whose `parameter` is how many configured validations are not represented and whose message says so. The validations that did not fit are simply absent; your dataset still saves normally. If you see it, the fix is to have fewer, more meaningful validations - or to split the datasets across apps. ## Things this does not do [#things-this-does-not-do] * **A failing validation never blocks a write.** Validation is observational: a run reports what is wrong, it never refuses your data. You decide what to do about it. * **There is no warning level.** Every configured validation is an error when it fires. * **The stored messages are in English.** They belong to a shared table that has no reader's language at the moment it is written. The structured columns beside them (`check_type`, `parameter`, `column_technical_id`) are what a translated view renders from - which is what the data grid does: its cell reasons are in your language for every per-column check, and are the message you typed yourself for a condition tree.