Custom SQL Validation

The Custom SQL Validation rule executes custom SQL queries to validate specific data quality conditions.
This rule is commonly used to:

• Implement complex validation logic beyond standard rules
• Validate business-specific data relationships
• Check cross-table data integrity

Example Usage:

• Ensure product prices are positive values across multiple tables
• Validate that customer orders reference existing customer IDs

Important Requirement:
The custom SQL query must return a result set with a column named is_valid, where:

• 1 indicates a passing record
• 0 indicates a failing record

Configuration Fields

Rule-Specific Configuration

Field Name	Description	Required	Data Type
Query	SQL query returning an is_valid column (1=pass, 0=fail)	Yes	String

---

Success Criteria Configuration

This section defines how the rule’s outcome is measured against expected thresholds.

Field Name	Description	Required	Options / Format
Operator	Comparison operation for result count	Yes	GreaterThan, LessThan, EqualTo, Between
Threshold Value	Value for comparison (single value for most operators)	Conditional	Number
Threshold Min	Minimum value (for Between operator)	Conditional	Number
Threshold Max	Maximum value (for Between operator)	Conditional	Number
Is Percentage	Whether the threshold represents a percentage of total rows	No	true / false (default: false)
Allow Nulls	Whether null values should be treated as valid	No	true / false (default: false)
Check For Match	When false, validates for negation of the condition	No	true / false (default: true)

---

Sample Input Data

Table: product_sales_data

ID	Product Name	Category	Price
1	Refrigerator	Appliances	899
2	Chair	Furniture	120
3	Smartwatch	Appliances	250
4	Laptop	Electronics	1200
5	Smartphone	Electronics	800

---

Sample Configurations

Example 1: Category Validation

Configuration Field	Value
Query	SELECT CASE WHEN Category IN ('Appliances','Electronics') THEN 1 ELSE 0 END AS is_valid FROM product_sales_data
Operator	Between
Threshold Min	3
Threshold Max	5
Is Percentage	false
Allow Nulls	false

Explanation:
Validates that 3–5 records belong to either ‘Appliances’ or ‘Electronics’ categories.

---

Sample Output

Column Name	Rule Name	Success Count	Failure Count	Null Count	Within Threshold
Category	Custom SQL Validation	4	1	0	Yes

---