Failed Rows check

In the Soda Cloud no-code builder, Failed Rows Checks are labelled SQL Expression, SQL Query – Failed Rows, and SQL Query – Failed Keys

expression vs query vs keys_query

A failed_rows check flags individual rows that break a rule and sends them to the Diagnostics Warehouse (if it's set up).

• You must provide exactly one of expression, query, or keys_query.
• The parser rejects zero or more than one (failed_rows_check_yaml.py).
• All three support metric: count (default, threshold = 0) or metric: percent.

	SQL Expression (expression)	SQL Query – Failed Rows (query)	SQL Query – Failed Keys (keys_query)
What you write	A boolean predicate that is TRUE for a failing row	A full SELECT returning the failing rows	A full SELECT returning only the key column(s) of the failing rows, which should be mapped to the Diagnostics Warehouse key column(s)
How Soda counts failures	COUNT(CASE WHEN <expr> THEN 1) — Soda builds the query	Counts rows returned by your query	Counts rows returned by your query (identical to query)
Where failed rows are stored	Shared diagnostics-warehouse tables (fk_/fr_)	Per-check table (frq_)	Shared diagnostics-warehouse tables (fk_/fr_)

When to use

• Boolean condition on one dataset? → expression. Simplest; Soda applies filters, derives passing rows, and computes rows-tested for you.
• Need custom SQL (joins/aggregations) and want the failing rows themselves, stored per-check? → query.
• Same, but you want only key identifiers stored (hashed, in the shared diagnostics warehouse) for privacy/correlation, or your rule is key-based? → keys_query.

1. SQL Expression (expression)

A boolean SQL expression that evaluates to TRUE for a failing row. It functions similar to a WHERE-clause predicate. Soda counts a row as failing only when the expression is TRUE, via COUNT(CASE WHEN <expression> THEN 1); rows where it's FALSE or NULL/UNKNOWN pass (failed_rows.py, failed_rows_check.py).

The expression is transparent to Soda, which means this is the most capable form:

• Soda can derive the passing (complement) set of rows.
• Soda applies the dataset filter and check-level filter: at scan time.
• Soda computes check_rows_tested automatically, so metric: percent needs no extra query.

checks:
  - failed_rows:
      expression: "discount > list_price"   # rows where this is TRUE are failures

Use it when the rule is a row-level boolean condition on columns of the same dataset (ranges, allowed values, cross-column comparisons, null logic). It's the simplest and the only form where Soda understands and can scope the query.

2. SQL Query – Failed Rows (query)

A verbatim, full SELECT you supply; every row it returns is a failed row. Soda treats it as opaque SQL, which means it cannot compute the complement and cannot express it as a dataset+filter AST, so the dataset filter is not applied (failed_rows.py). The failing rows land in a per-check diagnostics table (frq_).

checks:
  - failed_rows:
      query: |
        SELECT o.* FROM orders o
        LEFT JOIN customers c ON o.customer_id = c.id
        WHERE c.id IS NULL          -- orphaned orders
      metric: percent
      rows_tested_query: "SELECT COUNT(*) FROM orders"   # required for percent

Use it when the failing logic needs joins, aggregations, CTEs, or cross-dataset lookups and you want the actual failing rows captured/displayed. For metric: percent you must supply rows_tested_query for the denominator (the parser errors otherwise).

3. SQL Query – Failed Keys (keys_query)

Also a verbatim, full SELECT, but it returns only the key/identifier column(s) of the failing rows. Counting is identical to query. The returned keys are hashed by Soda and routed into the shared diagnostics-warehouse tables (fk_/fr_) rather than a per-check frq_ table (failed_rows_check.py, failed_rows_check_yaml.py).

It additionally supports key_column_mapping, which maps each diagnostics-warehouse key-column name to the output column of your query that supplies it (identity mapping when omitted).

checks:
  - failed_rows:
      keys_query: |
        SELECT order_id, line_no          -- composite key of failing rows
        FROM order_lines
        GROUP BY order_id, line_no
        HAVING COUNT(*) > 1               -- duplicates
      key_column_mapping:
        dwh_order_id: order_id
        dwh_line_no: line_no
      metric: count

Use it when you want key-based diagnostics stored centrally.

The keys are hashed and share a warehouse, so this suits not persisting full row payloads (privacy/sensitive tables), and identifier-centric rules (dedup, orphans, cross-source mismatches). Same limitations as query: opaque to Soda, dataset filter not applied, rows_tested_query required for percent.

Shared behavior & limitations

• Diagnostics carry three SQL fields end-to-end: failedRowsSourceQuery, failedRowsDataQuery, and failedRowsKeysQuery (soda_cloud_failed_rows_diagnostic.py).
• Sample collection must be enabled organization wide (Organization Settings → Allow Soda to collect sample data and failed row samples); default sample volume is 100 rows, adjustable via the samples limit (set 0 to disable).
• Dataset filters do not apply to query/keys_query. Scope with a WHERE clause inside the query yourself. Only expression respects the dataset filter and check filter:.
• rows_tested_query is only used with query/keys_query

  • Supplying it with expression just logs a warning.
  • key_column_mapping is only used with keys_query.