Consistency Data Quality Rules

Consistency rules validate data alignment across tables and columns. These checks ensure referential integrity, and that related data elements maintain logical relationships.

POST /integration/v1/native_data_quality/monitor

Numeric Column Metrics

referential_integrity: Validates that values in the current column exist (or do not exist) in a specified column of another table, ensuring referential integrity across related data. Both tables must be within the same data source, and the target table should be specified using its fully qualified name.

Supported Operators: must exist, must not exist

Basic Foreign Key Check:

 {
       "metric_name": "referential_integrity",
       "operator": "must exist",
       "threshold": "100",
       "configuration_keys": {
           "target_column": "customer_id",
           "target_table": "master_data.public.customers"
       },
       "category": "consistency"
   }

Orphan Record Detection:

 {
       "metric_name": "referential_integrity",
       "operator": "must not exist",
       "threshold": "0",
       "configuration_keys": {
           "target_column": "deleted_customer_id",
           "target_table": "master_data.public.deleted_customers"
       },
       "category": "consistency"
   }

Text Column Metrics

referential_integrity: Validates text-based foreign key relationships and ensures string identifiers exist in reference tables.

Supported Operators: must exist, must not exist

Department Code Validation:

 {
       "metric_name": "referential_integrity",
       "operator": "must exist",
       "threshold": "100",
       "configuration_keys": {
           "target_column": "department_code",
           "target_table": "master_data.public.departments"
       },
       "category": "consistency"
   }

Status Code Validation:

{
       "metric_name": "referential_integrity",
       "operator": "must exist",
       "threshold": "100",
       "configuration_keys": {
           "target_column": "status_code",
           "target_table": "master_data.public.valid_statuses"
       },
       "category": "consistency"
   }

Custom Metrics (All Column Types)

sql (Custom SQL Query): Defines custom consistency metrics using SQL queries for comprehensive data relationship validation.

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

Order-Customer Consistency:

 {
       "metric_name": "sql",
       "operator": "=",
       "threshold": "0",
       "configuration_keys": {
           "custom_metric": "order_customer_consistency",
           "query value": "SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL"
       },
       "category": "consistency"
   }

Product Pricing Consistency:

{
       "metric_name": "sql",
       "operator": "<=",
       "threshold": "10",
       "configuration_keys": {
           "custom_metric": "price_consistency_check",
           "query value": "SELECT COUNT(*) FROM order_items oi JOIN products p ON oi.product_id = p.product_id WHERE oi.unit_price != p.current_price"
       },
       "category": "consistency"
   }