Data Pipeline Data Quality Checks: A Practical Rulebook for Nulls, Duplicates, and Drift

Overview

Data pipeline validation is the practice of testing records as they move from a source system into a warehouse, lakehouse, or analytics model. The goal is not to reject every unusual value. It is to detect conditions that could make downstream reporting, machine learning, operations, or regulatory workflows unreliable.

A useful validation program combines five dimensions:

• Completeness: Required fields are present and expected records arrive.
• Uniqueness: A business key does not appear more times than the model allows.
• Validity: Values conform to an accepted type, range, format, or reference set.
• Freshness: Data arrives within the time window required by its consumers.
• Schema stability: Columns, data types, and structural contracts do not change unexpectedly.

These checks should be attached to the point where a failure can be explained and corrected. Source-level checks help identify extraction problems. Staging checks protect raw ingested data from being silently misread. Transformation checks verify joins, filters, and business rules. Published-model checks protect dashboards and downstream consumers.

Not every dataset needs the same strictness. A daily internal activity table may tolerate missing descriptive attributes, while a billing or identity table may require near-complete keys and carefully controlled changes. Start with the fields and datasets that have the greatest business impact, then expand coverage as the pipeline matures.

What to track

Use a rule-selection matrix

The following matrix is a practical starting point. Record the rule, its owner, the expected threshold, and the action taken when it fails.

Completeness and null checks

Separate required fields from optional fields. A null-rate rule is more useful when it is tied to a business purpose and a baseline. For example, a customer identifier may require a zero-null condition, while an optional marketing attribute may be allowed to have a defined percentage of missing values.

SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_ids,
  SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_emails
FROM staging.customers
WHERE load_date = CURRENT_DATE;

Track both the count and the rate. A small percentage can still represent a large operational problem on a high-volume load, while a small batch can produce a misleadingly large rate from only a few records.

Duplicate data checks

Define uniqueness around a business key, not automatically around every column. A source may legitimately contain multiple status events for one order, but it should not contain two current customer records with the same customer identifier unless the model explicitly supports versions.

SELECT customer_id, COUNT(*) AS row_count
FROM staging.customers
WHERE load_date = CURRENT_DATE
GROUP BY customer_id
HAVING COUNT(*) > 1;

If duplicates are expected during incremental ingestion, document the resolution rule. Common options include selecting the latest source timestamp, retaining the highest source sequence number, or preserving all events and creating a separate current-state model. Avoid silently applying a rule that has not been agreed with the data owner.

Validity and referential checks

Validity rules can test ranges, formats, enumerated values, and relationships. Examples include a non-negative quantity, a recognized currency code, a timestamp that can be parsed, or an order whose customer key exists in the customer model.

SELECT order_id, quantity, currency_code
FROM staging.orders
WHERE quantity < 0
   OR currency_code NOT IN ('USD', 'EUR', 'GBP');

Use exception tables when a record must be retained for investigation. An exception should include the dataset, rule name, record key, observed value, load identifier, and failure timestamp. This makes the failure actionable without exposing an incomplete row to downstream users.

Freshness and schema-drift detection

Freshness is not simply whether a job succeeded. Compare the newest event timestamp or ingestion timestamp with the service expectation for that dataset.

SELECT MAX(ingested_at) AS latest_record,
       CURRENT_TIMESTAMP - MAX(ingested_at) AS observed_delay
FROM raw.events;

Choose the timestamp carefully. An old event arriving today may satisfy ingestion freshness but still reveal a delay in the source event stream. Track both event age and ingestion delay when late-arriving data affects consumers.

For schema drift detection, compare the observed schema with a versioned expectation. A new nullable column may be safe for one consumer but disruptive to a strict parser. A renamed column, removed field, or incompatible type should generally require review before the pipeline continues. Treat schema changes as contract events rather than merely technical surprises.

Cadence and checkpoints

Run controls at the smallest useful interval, then review trends on a longer cadence. Per-load checks catch immediate failures; weekly or monthly reviews reveal gradual deterioration.

• Every load: Check required nulls, duplicate keys, validity exceptions, row counts, freshness, and schema compatibility.
• Daily: Review failed checks, quarantined records, late sources, and whether alerts were acknowledged.
• Weekly: Compare null rates, duplicate rates, row-count distributions, and freshness against recent normal behavior.
• Monthly: Confirm that rules still match business definitions, remove obsolete checks, and review recurring exceptions with source owners.
• Quarterly or after a major change: Reassess critical datasets, ownership, thresholds, contracts, and downstream dependencies.

Place checkpoints before irreversible transformations and before publication. A validation failure is easier to investigate when the pipeline preserves the original load identifier, source metadata, and affected partition. For broader architecture decisions, the cloud data warehouse and lakehouse comparison provides useful context for how storage and processing choices influence validation design.

Keep a simple quality register with these fields: dataset, owner, critical columns, rule, threshold, severity, schedule, last result, and remediation path. This register prevents validation logic from becoming hidden inside orchestration code.

How to interpret changes

A failed check is evidence that something changed; it is not automatically proof that the source is wrong. First distinguish a real data issue from a rule that no longer reflects the business process.

1. Confirm scope: Identify the affected load, partition, source, columns, and downstream models.
2. Compare with a baseline: Check recent row counts, null rates, key distributions, timestamps, and schema versions.
3. Classify the failure: Decide whether it is an extraction delay, source change, transformation defect, duplicate delivery, expected business event, or validation defect.
4. Assess impact: Determine which tables, dashboards, exports, or consumers used the affected data.
5. Choose containment: Pause publication, quarantine records, use the last known good partition, or continue with an explicitly documented warning.
6. Record the resolution: Capture the cause, affected interval, decision, owner, and whether the rule or source contract needs updating.

Severity should reflect downstream risk. A missing optional description may be a warning. A missing primary key, incompatible schema change, or stale operational feed may justify blocking the publish step. Avoid using one universal threshold for every table; thresholds should reflect the dataset's purpose and consumers.

For failures involving API delivery or authentication rather than the data itself, separate transport diagnosis from quality diagnosis. The online API debugging workflow can help isolate request, authentication, and webhook problems before you change transformation rules.

When to revisit

Revisit this rulebook monthly for active pipelines and at least quarterly for stable ones. Also review it whenever a source system changes, a new consumer depends on the data, a recurring quality failure appears, or a business definition changes.

Use this action checklist during each review:

• List the checks that failed or produced repeated warnings since the previous review.
• Compare current null, duplicate, validity, freshness, and row-count patterns with the agreed baseline.
• Confirm that every critical dataset still has an owner and a documented remediation path.
• Inspect schema changes and verify that downstream models accept the current contract.
• Retire checks that no longer represent a real risk, and add checks for newly critical fields.
• Test one controlled failure so alerts, quarantine behavior, and escalation routes remain usable.

When onboarding a source, begin with the five core dimensions and a small set of high-value rules. When investigating an incident, preserve the failing data and metadata before rerunning the pipeline. When reviewing pipeline health, look for trends rather than isolated failures. A consistent, revisited validation practice is more valuable than a large collection of rules that nobody owns or understands.

For a broader inventory of ingestion, transformation, orchestration, and analytics components, see the cloud data engineering toolkit. The same principle applies throughout: make quality expectations explicit, measure them at useful checkpoints, and revisit them whenever the data or its consumers change.