Custom Data Quality Rules

Custom rules enable you to define user-specific data quality metrics that don't fit into standard categories. These rules allow for complex business logic validation, domain-specific requirements, and advanced data quality scenarios using SQL queries and Common Table Expressions (CTEs).

POST /integration/v1/native_data_quality/monitor

Table-Level Custom Metrics

sql (Custom SQL Query): Defines custom table-level metrics using SQL queries that can evaluate complex business rules and cross-column validations.

Supported Operators: =, <, >, <=, >=, !=, <>, between, not between

Required Configuration:

Property	Description
`custom_metric`	SQL Query name for identification (no spaces allowed)
`query value`	Actual SQL query using Fully Qualified Names (FQN)

Business Rule Validation:

{
       "metric_name": "sql",
       "operator": "=",
       "threshold": "0",
       "configuration_keys": {
           "custom_metric": "business_rule_violations",
           "query value": "SELECT COUNT(*) FROM schema.orders WHERE total_amount < 0 OR (status = 'SHIPPED' AND ship_date IS NULL)"
       },
       "category": "custom"
   }

Data Quality Score:

 {
       "metric_name": "sql",
       "operator": ">=",
       "threshold": "95",
       "configuration_keys": {
           "custom_metric": "data_quality_score",
           "query value": "SELECT (COUNT(CASE WHEN email IS NOT NULL AND phone IS NOT NULL AND address IS NOT NULL THEN 1 END) * 100.0 / COUNT(*)) FROM schema.customers"
       },
       "category": "custom"
   }

Cross-Table Validation:

{
       "metric_name": "sql",
       "operator": "<=",
       "threshold": "5",
       "configuration_keys": {
           "custom_metric": "inventory_discrepancy",
           "query value": "SELECT COUNT(*) FROM schema.inventory i JOIN schema.orders o ON i.product_id = o.product_id WHERE i.available_quantity < 0"
       },
       "category": "custom"
   }

Column-Level Custom Metrics

cte (Common Table Expression): Defines custom numeric validations using CTE expressions for complex calculations.

Supported Operators: =, <, >, <=, >=, !=, <>, between, not between

Required Configuration:

Property	Description
`cte_custom_metric`	CTE name for identification (no spaces allowed)
`expression value`	Valid CTE SQL expression

Aggregate Expression Example:

{
        "metric_name": "cte",
        "operator": ">=",
        "threshold": "70",
        "configuration_keys": {
            "cte_custom_metric": "average_order_value",
            "expression value": "AVG(order_amount)"
        },
        "category": "custom"
    }

sql (Custom SQL Query): Defines custom numeric metrics using direct SQL queries.

Supported Operators: =, <, >, <=, >=, !=, <>, between, not between

Required Configuration:

Property	Description
`custom_metric`	SQL Query name for identification (no spaces allowed)
`query value`	Actual SQL query using Fully Qualified Names (FQN)

Financial Ratio Validation:

 {
       "metric_name": "sql",
       "operator": ">=",
       "threshold": "1.2",
       "configuration_keys": {
           "custom_metric": "current_ratio",
           "query value": "SELECT SUM(CASE WHEN account_type = 'CURRENT_ASSET' THEN balance ELSE 0 END) / NULLIF(SUM(CASE WHEN account_type = 'CURRENT_LIABILITY' THEN balance ELSE 0 END), 0) FROM schema.accounts WHERE report_date = CURRENT_DATE"
       },
       "category": "custom"
   }