Bad data in a data pipeline is more insidious than a system outage. When a service goes down, you know immediately. When bad data silently flows through your pipeline and corrupts your analytics, you might not know for days - and by then, the wrong numbers have driven decisions.

Data quality is the practice of detecting, handling, and preventing bad data before it causes downstream harm.

What Bad Data Looks Like

Bad data isn’t always obviously wrong. The categories:

Missing values: NULL where a value is required, empty strings where content is expected. An order with no customer ID. A transaction with no amount.

Out-of-range values: a product price of -$50. An age of 847. A percentage of 1500%. These can result from unit errors (storing cents vs dollars), bugs in the source system, or data corruption.

Type mismatches: a date stored as a string in an inconsistent format (“2024-01-15” vs “01/15/2024” vs “Jan 15 2024”). A numeric field containing alphabetic characters. JSON that’s supposed to contain an array containing a string.

Duplicates: the same event recorded twice, often from retry logic without idempotency keys. Duplicate rows in a dimension table. Counting the same user twice.

Referential integrity violations: an order that references a customer who doesn’t exist in the customer table. A foreign key to a deleted record.

Schema drift: the upstream source changed its schema. A field was renamed, a new required field was added, a field changed type. Your pipeline was built expecting the old schema.

Logical inconsistencies: an order whose total doesn’t match the sum of its line items. An event that happened before the user account was created. A session where “checkout completed” happened before “checkout started.”

Why It’s Hard to Catch

The pipeline receives data, transforms it, and writes results. If the input data is malformed, the pipeline either fails (visibly, which is recoverable) or produces wrong output (silently, which is dangerous).

Silent failures are the real risk. A pipeline that loads 10,000 rows when it should have loaded 11,000, because 1,000 rows failed a silent exception, looks like it worked. The downstream analytics just happen to be 9% wrong.

The challenge is that “wrong” is context-dependent. A NULL in a field might be valid in some rows and invalid in others. A transaction of $0 might be a legitimate free order or a data error. Rule-based validation can only catch what you explicitly define as wrong.

Validation at Ingestion

The first line of defense is validating data at the boundary where it enters your pipeline.

Schema validation: every row must conform to the expected schema. Types must match, required fields must be present.

from pydantic import BaseModel, validator
from decimal import Decimal
from datetime import datetime
from typing import Optional

class OrderEvent(BaseModel):
    order_id: str
    customer_id: str
    amount_cents: int
    currency: str
    created_at: datetime
    status: str

    @validator('amount_cents')
    def amount_must_be_positive(cls, v):
        if v < 0:
            raise ValueError(f"amount_cents must be non-negative, got {v}")
        return v

    @validator('status')
    def status_must_be_valid(cls, v):
        valid = {'pending', 'completed', 'cancelled', 'refunded'}
        if v not in valid:
            raise ValueError(f"Invalid status {v}, must be one of {valid}")
        return v

What to do with invalid rows: options are reject (stop the pipeline), quarantine (write to a dead-letter queue for investigation), or impute (fill in a default value). The right choice depends on how critical the data is and how common the errors are.

For critical pipelines (financial data, reporting that drives business decisions), reject and alert. For pipelines where partial results are acceptable, quarantine bad rows and continue.

dbt Tests for Transformation Validation

For ELT pipelines using dbt, built-in tests run automatically after transformations:

# models/marts/orders.yml
models:
  - name: orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: customer_id
      - name: amount_cents
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'completed', 'cancelled', 'refunded']

If any test fails, dbt fails the run and stops the pipeline. The problem is surfaced immediately rather than propagating downstream.

Custom tests catch more complex issues:

-- tests/orders_total_matches_line_items.sql
-- Returns rows where the order total doesn't match sum of line items
SELECT
    o.order_id,
    o.amount_cents as order_total,
    SUM(li.price_cents * li.quantity) as calculated_total
FROM {{ ref('orders') }} o
JOIN {{ ref('line_items') }} li ON li.order_id = o.order_id
GROUP BY o.order_id, o.amount_cents
HAVING o.amount_cents != SUM(li.price_cents * li.quantity)

If this query returns rows, the test fails. This kind of test catches logical inconsistencies that schema validation can’t detect.

Statistical Monitoring

Rule-based tests catch predefined issues. Statistical monitoring catches unexpected deviations without requiring you to define every possible failure mode.

The idea: for each metric (row count, NULL rate, mean value, distribution), track what “normal” looks like and alert when current values deviate significantly.

# Check if today's order count is within expected range
# based on historical data
from scipy import stats

def check_row_count_anomaly(today_count, historical_counts):
    z_score = (today_count - np.mean(historical_counts)) / np.std(historical_counts)
    if abs(z_score) > 3:  # More than 3 standard deviations from mean
        return f"Row count anomaly: {today_count} rows (z-score: {z_score:.2f})"
    return None

Tools like Great Expectations, Monte Carlo, and Soda implement this kind of monitoring at scale, with dashboards and alerting built in.

Schema Change Detection

Source systems change. An upstream team adds a column, renames a field, or changes a data type - and your pipeline silently breaks or produces wrong output.

The simplest mitigation: version your schemas and detect changes.

def detect_schema_changes(expected_schema: dict, actual_schema: dict) -> list[str]:
    issues = []
    for field, expected_type in expected_schema.items():
        if field not in actual_schema:
            issues.append(f"Missing expected field: {field}")
        elif actual_schema[field] != expected_type:
            issues.append(f"Type changed for {field}: expected {expected_type}, got {actual_schema[field]}")

    unexpected_fields = set(actual_schema) - set(expected_schema)
    for field in unexpected_fields:
        issues.append(f"Unexpected new field: {field}")

    return issues

New fields are usually fine - they’re additive. Missing fields or type changes are problems.

The Culture Problem

Data quality is ultimately a process problem, not just a technical one. Pipelines that catch bad data need to be maintained when source schemas change. Alerts need to be acted on, not silenced. Data producers need to know when their output is causing downstream failures.

The practices that make data quality sustainable: ownership (each pipeline has a clear owner), SLAs (data freshness and quality expectations are explicit), communication channels between data producers and consumers, and postmortems that include data quality incidents.

The alternative - treating bad data as expected background noise - leads to dashboards nobody trusts, analyses that require qualifications (“except for the week in March when the data was wrong”), and business decisions made on wrong information.