Custom Checks

Custom checks provide flexibility for complex validation scenarios that can’t be addressed with standard checks. These use SQL or CTE expressions to perform tailored validations specific to your business requirements including cross-table and multi-table validations within the same data source.

Follow these best practices for custom checks:

  • Start with predefined checks when possible before creating custom ones.

  • Optimize queries for performance, especially on large tables.

  • Add comments within SQL to document the purpose of complex logic.

  • Use consistent naming conventions for custom checks.

  • Consider query timeout limits when designing checks.

  • Test custom checks thoroughly before implementing in production.

  • For join operations, ensure all tables belong to the same data source and the query returns a single numeric value.

  • For cross-table operations, ensure all tables belong to the same data source and the query returns a single numeric value.

  • When composing a query, ensure that the query must return a single numerical result.

    • Always use fully qualified table names (e.g., <schema>.<table>).

    • Ensure correct column referencing.

    • For cross-table operations, verify all tables belong to the same data source.

Available Check Types

  • SQL Check: For single-query validations.

  • CTE Check: For multi-step validations using Common Table Expressions.

Example Custom Check Scenarios

Business Requirement

Check Type

Example Query

Ensure recent orders have shipping information

SQL

SELECT COUNT(*) FROM sales.orders WHERE order_date >= CURRENT_DATE - 2 AND shipping_id IS NULL

Check referential integrity between systems

SQL

SELECT COUNT(*) FROM system_a.orders o LEFT JOIN system_b.fulfillment f ON o.order_id = f.order_id WHERE o.status = 'SHIPPED' AND f.order_id IS NULL

Monitor data arrival timeliness

SQL

SELECT DATEDIFF(hour, MAX(batch_processed_time), CURRENT_TIMESTAMP) FROM etl.processing_log WHERE batch_date = CURRENT_DATE

Validate product price changes are within acceptable range

CTE

Complex query comparing current prices to historical averages using multiple CTEs.

Common Custom Check Types

Row Count Validation

This is a row count validation that verifies data existence in the product_stock table and serves as a quick health check for table population.

Query Name: product_stock_count

SELECT COUNT(*) FROM product_stock;

Numeric Aggregation Check

This numeric aggregation check validates the completeness of financial or quantitative data in the summer_sales table for a specific geographic region.

Query Name: summer_sales_san_mateo

SELECT SUM(amount) FROM summer_sales WHERE county = "San Mateo";

Rule-Based Filter Check

This rule-based filter check uses a CTE to identify and quantify records that meet specific business conditions, such as high-value bonus thresholds. This pattern is useful for validating data against business rules and identifying outliers or exceptions.

Check Name: bonus_cte_check

WITH bonus_cte AS (
    SELECT bonus FROM sales_demo WHERE bonus > 1000
)
SELECT COUNT(*) FROM bonus_cte;