You can use a SQL query in a failed row check to group failed check results by one or more categories using Soda Core.

You can use a SQL query in a failed row check to group failed check results by one or more categories using Soda Core.This approach is particularly useful when you want to identify specific subsets of data that fail quality checks and understand patterns in your data quality issues.

Use a SQL editor to build and test a SQL query with your data source, then add the query to a failed rows check to execute it during a Soda scan.

The following example illustrates how to build a query that identifies the countries where the average age of people is less than 25.

The following example demonstrates how to build a query that identifies countries where the average age of people is less than 25. This step-by-step approach helps you develop and test your query before implementing it in Soda Core.

1. Beginning with a basic query, the output shows the data this example works with.

1. Begining with a basic query, the output shows the data this example works with.

SELECT*FROM Customers;

{:height="600px" width="600px"}


2. Build a query to select groups with the relevant aggregations.

SELECT country,AVG(age)as avg_age

FROM Customers

GROUP BY country

{:height="600px" width="600px"}


3. Identify the "bad" group (where the average age is less than 25) from among the grouped results.

SELECT country,AVG(age)as avg_age

FROM Customers

GROUP BY country

HAVINGAVG(age)<25

SELECT country,AVG(age)as avg_age

FROM Customers

GROUP BY country

HAVINGAVG(age)<25

{:height="600px" width="600px"}


4. Now that the query yields the expected results, add the query to a failed row check, as per the following example.

checks for dim_customers:

-failed rows:

name:Average age of citizens is less than 25

fail query:|