Data Quality Validation Patterns for Analytics Teams: 8 Essential Checks

Analytics teams implement data quality validation patterns through automated, layered checks—including schema validation, null detection, range constraints, uniqueness tests, referential integrity, statistical profiling, business rule validation, and freshness monitoring—typically enforced via Great Expectations or DBT directly in data pipelines.

The DataExpert-io/data-engineer-handbook outlines a comprehensive framework for embedding these validation patterns into modern data stacks. By implementing these controls at each stage of the pipeline, teams ensure that downstream dashboards and machine learning models operate on trustworthy, production-grade data.

Core Data Quality Validation Patterns

According to the handbook's data cleaning and modeling materials, analytics teams should implement eight fundamental validation patterns. These checks catch errors early, reduce re-processing costs, and maintain audit trails.

Schema and Column Presence Validation

Schema validation ensures all required columns exist with expected data types before processing begins. This pattern catches malformed files and schema drift at ingestion.

• Implementation: Use DBT schema.yml tests or Great Expectations expect_table_columns_to_match_set
• Location: Documented in [data_cleaning.md](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/data_cleaning.md)

Null and Missing Value Checks

Null detection prevents unexpected gaps in non-nullable fields such as primary keys or timestamps. This pattern is critical for referential integrity and temporal analysis.

• Implementation: expect_column_values_to_not_be_null in Great Expectations or DBT's built-in not_null test
• Location: Covered in the Intermediate Bootcamp – Applying Analytical Patterns

Range and Domain Validation

Range validation verifies that numeric values fall within permissible boundaries (e.g., ages between 0-120, positive monetary amounts). This prevents outlier corruption in aggregation functions.

• Implementation: expect_column_values_to_be_between or custom SQL predicates
• Location: Detailed in Data Cleaning — Quality Checks

Uniqueness and Primary Key Constraints

Uniqueness validation ensures each record maintains a unique identifier, preventing duplicate entries that skew counts and violate entity relationships.

• Implementation: expect_column_values_to_be_unique or DBT unique test
• Location: Explained in Intermediate Bootcamp – Dimensional Modeling Homework

Referential Integrity

Referential integrity validates that foreign keys in fact tables reference existing rows in parent dimension tables, maintaining relational consistency across the warehouse.

• Implementation: expect_foreign_key_constraint or SQL joins with row-count checks
• Location: Discussed in Fact Data Modeling Material

Statistical Profiling

Statistical profiling detects outliers, distribution drift, and anomalous patterns using descriptive statistics rather than hard thresholds. This pattern identifies data quality degradation over time.

• Implementation: Great Expectations expect_column_mean_to_be_between, pandas profiling, or Spark-based histogram checks
• Location: Day 2 Lecture on Data-Quality Patterns

Business Rule Validation

Business rule validation enforces domain-specific logic such as temporal constraints (order_date ≤ ship_date) or status dependencies that generic type checks cannot capture.

• Implementation: Custom SQL/Python functions, DBT test macros, or expect_column_pair_values_A_to_be_greater_than_B
• Location: Illustrated in Intermediate Bootcamp – KPI & Experimentation Homework

Timeliness and Freshness Checks

Freshness validation monitors data arrival against SLAs (e.g., lag < 30 minutes), ensuring pipelines meet latency requirements for real-time analytics.

• Implementation: Airflow sensor DAGs or expect_table_row_count_to_be_between
• Location: Covered in Data-Pipeline Maintenance Homework

Layered Architecture for Data Quality

The handbook recommends a medallion architecture where validation patterns are applied at specific layers, creating a fail-fast system that isolates corruption before it reaches analysts.

Ingestion Layer

At the landing zone (S3, Azure Blob), apply lightweight schema checks to catch malformed files immediately. This prevents corrupt data from entering the warehouse.

Staging (Bronze) Layer

Raw data lands in bronze tables with null, range, and basic type checks. Validation results are stored in a dedicated validation-log table for audit trails and debugging.

Transformation (Silver) Layer

DBT models or Spark jobs enforce uniqueness, referential integrity, and business-rule validations. DBT's testing framework emits JSON reports that CI/CD pipelines can evaluate to block deployments with failing tests.

Analytics (Gold) Layer

Before exposing data to BI tools or ML systems, execute statistical profiling and timeliness checks. Great Expectations suites run as nightly jobs, triggering Slack or PagerDuty alerts on anomalies.

Governance Layer

All validation suites are version-controlled alongside transformation code (e.g., in an expectations/ directory). The handbook's Data-Cleaning section demonstrates syncing expectations with schema migrations to prevent drift.

Practical Implementation Examples

Great Expectations Suite

For Python-centric stacks, the handbook provides this comprehensive validation suite using Great Expectations. This pattern combines schema, null, range, uniqueness, and business rule checks in a single executable specification:

# example_01_great_expectations_suite.py

import great_expectations as ge
import pandas as pd

# Load a sample fact table (e.g., sales)

df = pd.read_parquet("s3://my-bucket/bronze/sales.parquet")
ge_df = ge.from_pandas(df)

# 1️⃣ Schema check – required columns & types

ge_df.expect_table_columns_to_match_set(
    ["order_id", "customer_id", "order_date", "amount", "currency"]
)
ge_df.expect_column_values_to_be_of_type("order_id", "int64")
ge_df.expect_column_values_to_be_of_type("order_date", "datetime64[ns]")

# 2️⃣ Null / Not‑null checks

ge_df.expect_column_values_to_not_be_null("order_id")
ge_df.expect_column_values_to_not_be_null("order_date")

# 3️⃣ Range validation – amount should be positive and reasonable

ge_df.expect_column_values_to_be_between("amount", min_value=0, max_value=1_000_000)

# 4️⃣ Uniqueness – order_id must be unique

ge_df.expect_column_values_to_be_unique("order_id")

# 5️⃣ Business rule – order_date must precede ship_date (if present)

if "ship_date" in df.columns:
    ge_df.expect_column_pair_values_A_to_be_greater_than_B(
        column_A="order_date", column_B="ship_date", allow_cross_type=True
    )

# Execute and report

results = ge_df.validate()
print(results)

DBT Test Macros

For SQL-first pipelines, the handbook recommends custom DBT test macros that combine multiple constraints. Store these in your macros/ directory to enforce complex business logic:

-- example_02_dbt_test_macro.sql (DBT – custom test macro)
{% macro test_not_null_and_positive(column_name) %}
  SELECT *
  FROM {{ this }}
  WHERE {{ column_name }} IS NULL
     OR {{ column_name }} < 0
{% endmacro %}

-- Usage in a DBT model (e.g., models/sales.sql)
{{ config(
    materialized='view',
    tests=[
        'not_null',
        {'unique': {}},
        {'relationships': {'to': 'ref("customers")', 'field': 'customer_id'}},
        test_not_null_and_positive('amount')
    ]
) }}
SELECT *
FROM {{ source('raw', 'sales') }}

Key Resources in the Data Engineer Handbook

The DataExpert-io/data-engineer-handbook contains specific files that provide deeper implementation guidance for these patterns:

• data_cleaning.md: Dedicated chapter on data-cleaning and quality-validation patterns, covering schema and range checks
• intermediate-bootcamp/materials/4-applying-analytical-patterns/README.md: Discusses analytical patterns including null detection and statistical profiling
• intermediate-bootcamp/materials/2-fact-data-modeling/README.md: Explains referential-integrity and uniqueness testing for fact tables
• intermediate-bootcamp/materials/5-kpis-and-experimentation/homework/homework.md: Demonstrates business-rule validation in KPI calculations
• intermediate-bootcamp/materials/6-data-pipeline-maintenance/homework/homework.md: Covers timeliness and freshness checks for pipeline health monitoring

Summary

• Data quality validation patterns for analytics teams include schema validation, null checks, range constraints, uniqueness tests, referential integrity, statistical profiling, business rules, and freshness monitoring
• Layered architecture applies lightweight checks at ingestion (bronze), structural validations at transformation (silver), and statistical monitoring at analytics (silver) layers
• Great Expectations provides Python-native implementations for complex validation suites with built-in reporting
• DBT tests and macros enable SQL-first teams to enforce constraints directly in transformation pipelines
• Version control of validation suites alongside code ensures quality checks evolve with schema changes

Frequently Asked Questions

What is the difference between Great Expectations and DBT for data validation?

Great Expectations is a Python library designed for comprehensive data profiling and validation with rich reporting capabilities, making it ideal for statistical checks and exploratory data analysis. DBT provides native testing functionality tightly integrated with SQL transformations, excelling at schema constraints and relationship validations within the transformation layer. Many teams use both: DBT for pipeline-blocking tests and Great Expectations for deep profiling in the analytics layer.

How do I prioritize which data quality checks to implement first?

Start with schema validation and null checks at the bronze layer to catch ingestion failures immediately. Next, implement uniqueness and referential integrity tests at the silver layer to prevent duplicate records and orphaned foreign keys. Finally, add business rule validation and freshness checks at the gold layer before exposing data to business users, as these directly impact downstream reporting accuracy.

Can these validation patterns work with streaming data pipelines?

Yes, though implementations differ. For streaming pipelines (Kafka, Spark Streaming), use windowed aggregations for freshness checks and schema registries (Confluent Schema Registry) for ingestion-layer validation. Great Expectations supports batch validation on micro-batches, while DBT typically operates on materialized views or scheduled batches rather than true streaming, requiring complementary tools like Apache Griffin or custom Spark validators for sub-minute latency requirements.

Where should validation logs be stored for audit purposes?

The handbook recommends storing validation results in a dedicated validation-log table within your data warehouse (typically in the bronze or utility schema). This creates an immutable audit trail of which checks passed or failed at specific timestamps. For compliance requirements, export these logs to long-term storage (S3, Glacier) or integrate with data catalogs like Amundsen or DataHub to surface data quality scores alongside dataset metadata.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}