DE Data Validation Techniques

Data validation is the act of testing data against defined rules to confirm it meets quality standards before it flows to downstream consumers. Validation catches problems automatically, at pipeline runtime, before incorrect data reaches dashboards, ML models, or business reports. Building robust validation into every pipeline is a hallmark of professional data engineering.

When Validation Runs

Validation should happen at every stage of the pipeline where data changes hands — after ingestion from a source, after transformation, and before loading into the final destination. The earlier a validation fails, the cheaper the problem is to fix.

Pipeline Stage     | Validation Focus
-------------------|--------------------------------------------
After ingestion    | Completeness, format, row count
After transform    | Business rules, referential integrity, ranges
Before load        | Uniqueness, schema conformance, final checks
Post-load          | Row counts match expectation; spot checks

Schema Validation

Schema validation confirms that incoming data has the expected columns, in the expected data types. Source systems change without warning — a developer renames a column or adds a new mandatory field. Schema validation catches this before a mismatched schema corrupts the destination table.

Expected schema for orders CSV:
  order_id:    STRING, required
  customer_id: STRING, required
  order_date:  DATE (YYYY-MM-DD), required
  amount:      FLOAT, required, positive
  status:      STRING, one of [pending, completed, cancelled]

Validation check:
  - All columns present? YES/NO
  - order_date parseable as date? YES/NO
  - amount is numeric? YES/NO
  - status in allowed values? YES/NO
  Action on failure: Reject file, alert engineer, do not load

Rule-Based Validation

Rule-based validation defines explicit business rules and tests every record against them. Rules encode domain knowledge — what values are physically possible, what relationships must hold, what ranges are acceptable.

Not Null Rules

Rule: customer_id must not be null in orders
SQL Check:
  SELECT COUNT(*) FROM orders WHERE customer_id IS NULL;
  -- Fail if count > 0

Range Rules

Rule: order amount must be between 1 and 10,000,000
SQL Check:
  SELECT COUNT(*) FROM orders
  WHERE amount <= 0 OR amount > 10000000;
  -- Fail if count > 0

Format Rules

Rule: email must follow valid email pattern
SQL Check (using REGEXP):
  SELECT COUNT(*) FROM customers
  WHERE email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';
  -- Fail if count > 0

Referential Integrity Rules

Rule: every order must reference an existing customer
SQL Check:
  SELECT COUNT(*) FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.customer_id
  WHERE c.customer_id IS NULL;
  -- Fail if count > 0 (orphaned orders exist)

Statistical Validation

Statistical validation detects anomalies that rule-based checks miss — subtle shifts in data distributions that indicate upstream problems without triggering explicit rule violations.

Row Count Anomaly Detection

A table that normally receives 100,000 rows per day suddenly receives 200 rows. No rule explicitly bans 200 rows — but it signals a broken source connection or extraction failure. Statistical thresholds based on historical averages flag these deviations automatically.

Statistical Row Count Check:
  Calculate 30-day average daily row count: 98,000
  Calculate standard deviation: 3,000
  Today's row count: 12,000

  Z-score = (12,000 - 98,000) / 3,000 = -28.7
  (Extremely far from normal -- alert!)

Distribution Checks

Validate that the distribution of values in a column matches historical patterns. If the proportion of "completed" orders in the status column drops from 80% to 10% overnight, something went wrong — either in the source system or in the transformation logic.

Cross-System Validation

Cross-system validation compares data between two systems to confirm they agree. After loading data from a source database into the data warehouse, validate that row counts and key aggregate totals match.

Cross-System Validation Example:
  Source DB total orders for 2024-05-15: 84,302
  Warehouse table row count after load:  84,302  ✓

  Source DB total revenue for 2024-05-15: $4,821,900
  Warehouse table SUM(amount):            $4,821,900  ✓

  If values differ: quarantine the load and alert the team

Great Expectations: A Validation Framework

Great Expectations is the most widely used Python library for data validation. Engineers define "expectations" — declarative assertions about the data — and the library checks them automatically against any DataFrame or database table.

Great Expectations Example:

import great_expectations as ge

df = ge.read_csv("orders.csv")

# Define expectations
df.expect_column_values_to_not_be_null("order_id")
df.expect_column_values_to_not_be_null("customer_id")
df.expect_column_values_to_be_between("amount", 0, 10000000)
df.expect_column_values_to_be_in_set("status",
    ["pending", "completed", "cancelled"])
df.expect_column_values_to_match_regex("email",
    r"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$")

# Run all expectations and get a report
result = df.validate()
print(result["success"])   # True if all passed, False if any failed

Handling Validation Failures

A validation failure does not always mean stopping the entire pipeline. Data engineers choose a strategy based on how severe the failure is and what the downstream impact would be.

Strategy          | When to Use
------------------|----------------------------------------------
Fail the pipeline | Critical field null; row count drastically low
Quarantine records| Isolate bad rows; load clean rows; alert team
Log and continue  | Minor format issues; non-critical fields only
Auto-correct      | Known fixable issues (trim whitespace, set default)

dbt Tests: Validation in Transformation Pipelines

dbt — the dominant ELT transformation tool — includes a built-in testing framework. Data engineers write test definitions in YAML, and dbt runs SQL-based checks every time models build. Tests cover not-null, uniqueness, accepted values, and relationships between tables.

dbt test definitions (schema.yml):
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - not_null
          - unique
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'completed', 'cancelled']

Summary

Data validation enforces quality rules at every pipeline stage through schema checks, rule-based assertions, statistical anomaly detection, and cross-system comparisons. Tools like Great Expectations and dbt tests automate this validation as part of the pipeline execution. Choosing the right response to validation failures — halt, quarantine, log, or correct — depends on the severity of the issue and its downstream impact. Systematic validation transforms pipelines from hopeful data movers into trustworthy data delivery systems.

Previous lesson

Back to course

Next lesson