Data Quality Checks in ETL Pipelines: A Best Practices Guide

An ETL job can finish successfully while loading incomplete or invalid data. Validation at ingestion and between transformation stages helps detect those problems before they reach downstream tables and reports.

Why Checks at the Pipeline Level Matter

The further downstream a data quality issue travels, the more expensive it is to fix. A null value in a critical field caught during ingestion costs a single re-run. The same null value propagated through six downstream tables, aggregated into a weekly report, and used in a business decision costs investigation time, trust, and potential rework across multiple systems.

The principle: fail fast at the boundary. Every stage transition in your pipeline — extract to raw, raw to staging, staging to production — is an opportunity to validate that data meets expectations before promoting it.

Types of Data Quality Checks

1. Schema Validation

Validate that the incoming data matches the expected structure before any transformation runs.

• Column names are present and have not been renamed
• Data types match expected types (a field declared as integer does not contain strings)
• No unexpected new columns (or new columns are explicitly allowed and handled)
• No columns silently dropped

Schema drift is one of the most common causes of silent pipeline failures. A source system renames a field, the pipeline continues running, and downstream tables contain nulls where values should be.

2. Row Count Checks

Compare the number of rows extracted from the source against the number of rows loaded to the destination.

• Extract count matches load count (no rows dropped silently during transformation)
• Row count is not zero (an empty load may indicate a source connection failure)
• Row count is not implausibly large (sudden spikes may indicate a filter bug causing a full-table reload)

Row count checks are lightweight but catch a surprisingly broad class of failures.

3. Null Checks on Critical Fields

Define which fields must never be null in your destination tables, and assert this on every run.

• Primary keys are never null
• Foreign keys are never null
• Business-critical fields (revenue amounts, user IDs, event timestamps) are never null

This is especially important for fields used in joins downstream — a null foreign key means the row will silently disappear from joined results.

4. Uniqueness Checks

Validate that fields that should be unique are in fact unique.

• Primary keys contain no duplicates
• Natural keys (e.g., user email, order number) contain no unexpected duplicates

Duplicate rows are a common side effect of non-idempotent pipelines or retry logic that does not deduplicate correctly.

5. Referential Integrity Checks

Validate that foreign keys resolve to valid values in reference tables.

• Every user_id in an events table maps to a real user in the users table
• Every product_id in an orders table maps to a real product

Orphaned foreign keys typically indicate a loading-order problem (child table loaded before parent) or a data lifecycle issue (records deleted from the parent without cascading).

6. Range and Distribution Checks

Validate that numeric and date fields fall within expected ranges.

• Revenue values are not negative (unless refunds are valid)
• Timestamps are not in the future or suspiciously far in the past
• Percentage fields are between 0 and 100
• Distribution of values has not shifted dramatically from the previous run (a useful signal for subtle data drift)

These checks catch transformation bugs and source data anomalies that schema and null checks miss.

Where to Run Checks

At ingestion (raw layer)

Check that what you extracted matches what you expected. Schema validation and row counts belong here. Fail immediately if the raw data is structurally incorrect.

After transformation (staging layer)

Before promoting transformed data to production tables, run business logic checks. Null checks, uniqueness, referential integrity, and range checks belong here. If checks fail, the production table remains unchanged.

In production (ongoing monitoring)

Run freshness checks (data was updated within the expected window) and aggregate plausibility checks (today's total revenue is within a reasonable range of yesterday's) as ongoing monitoring, not just pipeline validation.

Tools for Data Quality in ETL Pipelines

dbt tests — the simplest entry point if you are already using dbt. Built-in generic tests (not_null, unique, accepted_values, relationships) cover most common cases. Custom singular tests handle business-specific logic. Tests run as part of dbt test in your pipeline.

Great Expectations — a standalone Python framework for defining and running data quality expectations. More expressive than dbt tests for complex statistical checks. Generates documentation of expectations and results.

Soda — similar to Great Expectations but with a YAML-first configuration syntax and a managed cloud offering for monitoring and alerting.

Elementary (dbt extension) — adds observability and anomaly detection on top of dbt, including schema change monitoring and historical comparison of metrics.

Handling Check Failures

When a check fails, the pipeline should:

1. Fail the current run — do not promote data that fails validation
2. Fire an alert — notify the on-call engineer or data team immediately
3. Log the failure details — which check failed, on which table, with what value, at what run timestamp
4. Preserve the raw data — failed transformation does not mean the source data is gone; raw data should be retained so the pipeline can re-run once the issue is resolved

Silently skipping failed checks defeats the purpose. A check that does not fail the pipeline is documentation, not quality control.

A Practical Starting Point

If you are adding quality checks to an existing pipeline for the first time, start here:

• Row count: extract count equals load count, no zero-row loads
• Null check: primary key is never null
• Uniqueness: primary key has no duplicates
• Freshness: production table was updated within the expected run window

These checks provide a starting point. Add checks for the specific business rules and failure patterns in your pipeline.

Discuss ETL validation and data quality with LinkIT.