Consistency Checks

Consistency checks validate that data values remain stable and coherent across different time periods, datasets, or related systems. These checks help identify unexpected variations in data patterns, schema changes, or discrepancies between related data sources.

Note

Consistency checks are currently available only through custom SQL and CTE checks. Alation is actively developing standardized consistency check templates that will be available in future releases.

Consider the following points for consistency queries:

  • The query must return a single numerical result that represents the consistency metric.

  • Always use fully qualified table names (e.g., <schema>.<table>) for cross-system or multi-table validations.

Follow these best practices for consistency checks:

  • Start with critical business relationships and cross-system dependencies.

  • Focus on high-impact inconsistencies that affect downstream processes.

  • Use time-based comparisons to detect gradual data drift.

  • Implement referential integrity checks between related systems.

  • Monitor schema consistency across environments (dev, staging, production).

  • Validate business rule consistency across different calculation methods.

Available Check Types

Since consistency checks require custom business logic, they are implemented using:

  • SQL Check: For single-query consistency validations

  • CTE Check: For validations using Common Table Expressions

Configuration Examples

Check Description

Check Type

Example Logic

Validate schema consistency between environments

SQL

SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'customers' AND table_schema = 'prod' compared to staging

Check referential integrity across systems

SQL

SELECT COUNT(*) FROM sales.orders o LEFT JOIN customer.customers c ON o.customer_id = c.id WHERE c.id IS NULL

Validate cross-table aggregation consistency

SQL

SELECT ABS(SUM(order_total) - (SELECT SUM(payment_amount) FROM finance.payments WHERE date = CURRENT_DATE)) FROM sales.orders

Common Consistency Validation Scenarios

Consistency Type

Description

Implementation Approach

Schema

Table structures remain consistent

Validate column counts,

Referential

Foreign key relationships are maintained

Check for orphaned records across related tables

Aggregation

Summary data matches detail data

Validate that rolled-up metrics equal sum of details

Business Rule

Derived fields follow consistent calculation logic

Verify computed columns match expected formulas

Cross-System Consistency

Compare data between source and target systems

SELECT CASE WHEN ABS(current_avg - historical_avg) / historical_avg > 0.2
THEN 1 ELSE 0 END
FROM (SELECT AVG(order_amount) as current_avg FROM sales.orders
WHERE date = CURRENT_DATE) c,
    (SELECT AVG(order_amount) as historical_avg FROM sales.orders
        WHERE date BETWEEN CURRENT_DATE - 30 AND CURRENT_DATE - 1) h

Schema Consistency

Check structural consistency

SELECT COUNT(*) FROM information_schema.columns
WHERE table_name = 'customers' AND table_schema = 'production'

Referential Consistency

Validate relationships between tables

SELECT COUNT(*) FROM sales.orders o
    LEFT JOIN customer.customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL