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.

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.

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.