Data Quality And Reliability

What is Data Quality and Reliability for Data Engineers?

Data Quality and Reliability is the discipline of keeping data accurate, complete, timely, consistent, and trustworthy across ingestion, transformation, and delivery. For a Data Engineer, this means designing checks, contracts, and observability that prevent bad data from entering pipelines, detect issues early, and drive quick recovery when incidents happen.

Mastering this skill unlocks tasks like building robust ETL/ELT pipelines, implementing data contracts with producers, automating quality gates in CI/CD, and owning SLAs that your analytics and ML customers can rely on.

Who this is for

• Data Engineers building or maintaining batch, streaming, or CDC pipelines.
• Analytics Engineers and Platform Engineers who need reliable data delivery.
• Developers moving into data infrastructure roles.

Prerequisites

• Comfort with SQL (GROUP BY, JOINs, window functions).
• Basic Python for data tasks (pandas or SQLAlchemy).
• Familiarity with data modeling concepts (keys, constraints, SCDs).
• Understanding of pipeline orchestration basics (e.g., scheduling, retries).

Learning path

1. Start with fundamentals: freshness, completeness, null/duplicate/range checks.
2. Reconciliation: confirm source-to-target parity with keys and counts.
3. Contracts: define schemas, SLAs, and breaking vs non-breaking changes.
4. Observability: instrument metrics, logs, and traces for pipelines.
5. Anomaly detection: baseline and alert on unusual behavior.
6. Root cause analysis and ownership: runbooks, on-call basics, SLAs.

Practical roadmap with milestones

1. Milestone 1 — Basic checks in one pipeline
   • Implement freshness, completeness, and null/duplicate/range checks on a bronze-to-silver job.
   • Definition of done: failing checks block downstream runs and notify an owner.
2. Milestone 2 — Reconciliation and contracts
   • Automate source-to-target reconciliation and document a data contract with a producer.
   • Definition of done: contract includes schema, SLAs, and change policy; reconciliation runs daily.
3. Milestone 3 — Observability and anomalies
   • Publish pipeline metrics (latency, throughput, error rate), keep structured logs, add simple anomaly alerts.
   • Definition of done: dashboards show health; alerts page the right on-call.
4. Milestone 4 — Incident readiness
   • Create runbooks for top 3 failure modes and define RTO/RPO-like targets for data.
   • Definition of done: a simulated incident is resolved within target and postmortem recorded.

Worked examples

1) Freshness and completeness in SQL

-- Freshness: latest event within 90 minutes of now
SELECT CASE WHEN MAX(event_time) > NOW() - INTERVAL '90 minutes' THEN 'OK' ELSE 'STALE' END AS freshness_status
FROM bronze.events;

-- Completeness: at least 95% of expected rows (from daily plan)
WITH expected AS (
  SELECT 100000 AS expected_rows -- replace with your schedule-derived value
), actual AS (
  SELECT COUNT(*) AS actual_rows FROM bronze.events WHERE event_date = CURRENT_DATE
)
SELECT CASE WHEN actual.actual_rows >= 0.95 * expected.expected_rows THEN 'OK' ELSE 'INCOMPLETE' END AS completeness
FROM expected, actual;

2) Null, duplicate, and range checks

-- Null check for required columns
SELECT COUNT(*) AS null_violations
FROM silver.orders
WHERE order_id IS NULL OR customer_id IS NULL;

-- Duplicate check by business key
SELECT order_id, COUNT(*) AS dupes
FROM silver.orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Range check for monetary amount
SELECT COUNT(*) AS out_of_range
FROM silver.orders
WHERE amount < 0 OR amount > 100000; -- set sane upper bound

3) Simple anomaly detection in Python (rolling z-score)

import pandas as pd

# df has columns: ts (date), row_count (int)
df = df.sort_values('ts')
df['mean7'] = df['row_count'].rolling(7).mean()
df['std7']  = df['row_count'].rolling(7).std()
df['z']     = (df['row_count'] - df['mean7']) / df['std7']

anomalies = df[df['z'].abs() > 3]
print(anomalies[['ts','row_count','z']])

4) Source-to-target reconciliation

-- Row count comparison by business date
WITH src AS (
  SELECT business_date, COUNT(*) AS c
  FROM src.orders_raw
  GROUP BY business_date
), tgt AS (
  SELECT business_date, COUNT(*) AS c
  FROM dw.orders_clean
  GROUP BY business_date
)
SELECT COALESCE(s.business_date, t.business_date) AS business_date,
       s.c AS src_count, t.c AS tgt_count,
       (t.c - s.c) AS diff
FROM src s
FULL OUTER JOIN tgt t USING (business_date)
ORDER BY business_date;

5) Data contract (example schema and policy)

name: orders_stream
owner: team-orders-platform
pii_classification: contains_pseudonymous_ids
freshness_sla_minutes: 60
schema:
  - name: order_id
    type: string
    nullable: false
    unique: true
  - name: customer_id
    type: string
    nullable: false
  - name: amount
    type: decimal(12,2)
    nullable: false
    constraints:
      min: 0
      max: 100000
  - name: event_time
    type: timestamp
    nullable: false
change_policy:
  breaking:
    - remove_column
    - change_type_widening=false
  non_breaking:
    - add_nullable_column
    - add_enum_value_with_notice
incident_contacts:
  primary_oncall: data-eng-oncall
  escalation: head-of-data

Drills and exercises

• Create a freshness query for one table with a clear threshold and a grace window.
• Add a duplicate check for your primary business key and fail the job if duplicates > 0.
• Define a range constraint for at least two numeric columns; count violations per day.
• Write a reconciliation query comparing source and target counts by partition (date or id range).
• Document a minimal data contract: schema, nullability, freshness SLA, owner.
• Instrument and log: add a metric for rows_in, rows_out, and error_count in a pipeline step.
• Implement a 7-day rolling anomaly detector for row_count or null_rate.

Common mistakes and debugging tips

• Only checking after load: Add pre-ingestion validation to catch issues earlier.
• Hardcoding thresholds: Start fixed, then iterate to adaptive/seasonal baselines.
• Ignoring schema drift: Contracts and CI checks should block breaking changes.
• Unowned data sets: Every critical dataset needs a named owner and on-call.
• Alert fatigue: Classify alerts (blocker vs warning) and deduplicate noisy signals.

Mini project: Warehouse Health Guard

Goal: Add robust quality and reliability to one critical data flow (source → bronze → silver → gold).

1. Define a data contract for the source feed, including schema, nullability, and freshness SLA.
2. Implement checks: freshness, completeness, null/duplicate/range at bronze and silver layers.
3. Add reconciliation between source and silver (counts, sums, optional hash).
4. Publish metrics (rows_in, rows_out, duration, error_rate) and structured logs.
5. Add anomaly detection for daily row_count and null_rate.
6. Write a 1-page runbook with owner, SLAs, and top 3 failure scenarios with steps.

Acceptance criteria:

• Any breaking failure stops the downstream job and notifies the owner.
• Dashboard shows the last 7 days of health and alerts correlate with incidents.
• One simulated incident resolved within your target window and documented.

Practical projects

• Quality Gate Library: reusable SQL/Python checks packaged for multiple teams.
• Contract Validator in CI: a validator that fails PRs on breaking schema changes.
• Reconciliation Bot: scheduled job that comments daily diffs to a team channel.

Subskills

Next steps

• Pick one production table and apply Milestone 1 within a week.
• Expand to Milestone 2 for two more upstream sources.
• Document owners and SLAs for your top 5 critical datasets.
• Return to refine thresholds into adaptive baselines after two weeks of data.