# Failed rows checks Use a failed rows check to explicitly send samples of rows that failed a check to Soda Cloud. You can also use a failed row check to configure Soda Library to execute a CTE or SQL query against your data, or to group failed check results by one or more categories. ```yaml checks for dim_customer: # Failed rows defined using common table expression - failed rows: samples limit: 50 fail condition: total_children = '2' and number_cars_owned >= 3 ``` ```yaml checks for dim_customer: # Failed rows defined using SQL query - failed rows: fail query: | SELECT DISTINCT geography_key FROM dim_customer as customer ``` ✔️ Requires Soda Core Scientific (included in a Soda Agent)\ ✔️ Supported in Soda Core\ ✔️ Supported in Soda Library + Soda Cloud\ ✔️ Supported in Soda Cloud Agreements + Soda Agent ✔️ Available as a no-code check with a self-hosted Soda Agent connected to any Soda-supported data source, except Spark, and Dask and Pandas\ OR\ with a Soda-hosted Agent connected to a BigQuery, Databricks SQL, MS SQL Server, MySQL, PostgreSQL, Redshift, or Snowflake data source ## Prerequisites To send failed rows samples to Soda Cloud, samples collection must be enabled in Soda Cloud. As a user with [permission](https://docs.soda.io/soda-documentation/soda-v3/collaborate/roles-global#global-roles-and-permissions) to do so, navigate to **your avatar** > **Organization Settings**, then check the box to **Allow Soda to collect sample data and failed row samples for all datasets**. ## Define failed rows checks In the context of [SodaCL check types](https://docs.soda.io/soda-documentation/soda-v3/metrics-and-checks#check-types), failed row checks are user-defined. This check is limited in its syntax variation, but you can customize your expression or query as much as you like. When a failed rows check results in a `warn` or `fail` Soda collects up to 100 failed row samples by default. You can decrease or increase the volume of sample rows using the `samples limit` parameter; see [Set a samples limit](https://docs.soda.io/soda-documentation/soda-v3/run-a-scan/failed-row-samples#set-a-sample-limit). The example below uses [common table expression (CTE)](https://www.essentialsql.com/introduction-common-table-expressions-ctes/) to define the `fail condition` that any rows in the `dim_customer` dataset must meet in order to qualify as failed rows, during a scan, get sent to Soda Cloud. Soda sends any rows which contain the value 2 in the `total_children` column and which contain a value greater than or equal to 3 in the `number_cars_owned` column to Soda Cloud as failed row samples, up to a default volume of 100 rows. The check also uses the `name` configuration key to customize a name for the check so that it displays in a more readable form in Soda Cloud; see image below. ```yaml checks for dim_customer: - failed rows: name: Failed rows with CTE fail condition: total_children = '2' and number_cars_owned >= 3 # OR - failed rows: name: Failed rows with CTE fail condition: | total_children = '2' and number_cars_owned >= 3 ```

If you prefer, you can use a SQL query to define what qualifies as a failed row for Soda to send to Soda Cloud, as in the following simple example. Use this cofiguration to include complete SQL queries in the Soda scan of your data. ```yaml checks for dim_customer: - failed rows: fail query: | SELECT DISTINCT geography_key FROM dim_customer as customer ```

## Optional check configurations

Supported	Configuration	Documentation
✓	Define a name for a failed rows check; see example.	Customize check names
✓	Add an identity to a check.	Add a check identity
✓	Define alert configurations to specify warn and fail alert conditions; see example.	Add alert configurations
	Apply an in-check filter to return results for a specific portion of the data in your dataset.	-
✓	Use quotes when identifying dataset or column names; see example. Note that the type of quotes you use must match that which your data source uses. For example, BigQuery uses a backtick (`) as a quotation mark.	Use quotes in a check
✓	Use wildcard characters in the value in the check.	Use wildcard values as you would with CTE or SQL.
	Use for each to apply failed rows checks to multiple datasets in one scan.	-
✓	Apply a dataset filter to partition data during a scan; see example. Known issue: Dataset filters are not compatible with failed rows checks which use a SQL query. With such a check, Soda does not apply the dataset filter at scan time.	Scan a portion of your dataset
✓	Specify a single column against which to run a failed rows check; see example.	-
	Supports `samples columns` parameter to specify columns from which Soda draws failed row samples.	Customize sampling for checks
	Supports `samples limit` parameter to control the volume of failed row samples Soda collects.	Set a sample limit
	Supports `collect failed rows` parameter instruct Soda to collect, or not to collect, failed row samples for a check.	Customize sampling for checks

#### Example with check name ```yaml checks for dim_customer: - failed rows: name: Failed rows query test fail query: | SELECT DISTINCT geography_key FROM dim_customer as customer ``` #### Example with alert ```yaml checks for dim_customer: - failed rows: fail condition: total_children = '2' and number_cars_owned >= 3 warn: when between 1 and 10 fail: when > 10 ``` #### Example with quotes ```yaml checks for dim_customer: - failed rows: name: Failed rows query test fail query: | SELECT DISTINCT "geography_key" FROM dim_customer as customer ``` #### Example with dataset filter *Known issue:* Dataset filters are not compatible with failed rows checks which use a SQL query. With such a check, Soda does not apply the dataset filter at scan time. ```yaml filter dim_product [new]: where: start_date < TIMESTAMP '2015-01-01' checks for dim_product [new]: - failed rows: name: Failed CTE with filter fail condition: weight < '200' and reorder_point >= 3 ``` #### Example with column parameter ```yaml checks for dim_product: # with SQL query - failed rows: name: Brand must be LUCKY DOG column: product_line fail query: | SELECT * FROM dim_product WHERE product_line LIKE '%LUCKY DOG%' # with CTE - failed rows: name: Brand must be LUCKY DOG column: product_line fail condition: brand LIKE '%LUCKY DOG%' ``` #### Failed rows for large tables You may encounter out of memory errors if you try to run a failed rows check on a table with a large number of rows. In this case, we recommend that you use a [user-defined metric check](https://docs.soda.io/sodacl-reference/user-defined#example-with-failed-row-sample-query) with a failed rows sample query. So, the first query in the check is used to count the total number of failed rows and the second query is used to collect a limited set of example rows. For example: ```yaml checks for CUSTOMERS: - belgium_customers = 0: belgium_customers query: | SELECT count(*) as belgium_customers FROM CUSTOMERS WHERE country = 'BE' failed rows query: | SELECT * FROM CUSTOMERS WHERE country != 'BE' LIMIT 100 ``` ## Go further * Learn how to [manage failed row samples](https://docs.soda.io/soda-documentation/soda-v3/run-a-scan/failed-row-samples). * Learn more about [SodaCL metrics and checks](https://docs.soda.io/soda-documentation/soda-v3/sodacl-reference/metrics-and-checks) in general. * Borrow user-defined check syntax to define a reusable [check template](https://docs.soda.io/soda-documentation/soda-v3/sodacl-reference/check-template). * Use a [schema check](https://docs.soda.io/soda-documentation/soda-v3/sodacl-reference/schema) to discover missing or forbidden columns in a dataset. * Reference [tips and best practices for SodaCL](https://docs.soda.io/soda-documentation/soda-v3/soda-cl-overview/quick-start-sodacl#tips-and-best-practices-for-sodacl). > Need help? Join the [Soda community on Slack](https://community.soda.io/slack).