# Data Quality Validation Patterns for Analytics Teams: 8 Essential Checks

> Discover 8 essential data quality validation patterns for analytics teams. Learn automated checks for cleaner, reliable data with Great Expectations and DBT.

- Repository: [DataExpert.io/data-engineer-handbook](https://github.com/DataExpert-io/data-engineer-handbook)
- Tags: how-to-guide
- Published: 2026-08-07

---

**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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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)](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](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/4-applying-analytical-patterns/README.md)

### 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](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/data_cleaning.md)

### 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](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/1-dimensional-data-modeling/homework/homework.md)

### 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](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/2-fact-data-modeling/README.md)

### 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](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/5-kpis-and-experimentation/homework/homework.md)

### 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](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/intermediate-bootcamp/materials/6-data-pipeline-maintenance/homework/homework.md)

## 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:

```python

# 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:

```sql
-- 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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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`](https://github.com/DataExpert-io/data-engineer-handbook/blob/main/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.