Data Pipeline Engineering: ETL Architecture from Collection to Storage

The Bottom Line: Data Pipelines Look Simple, But Are Full of Traps

ETL requirements sound simple — “move data from A to B, do some cleaning in between.” But in practice, edge cases, data quality, failure recovery, and performance issues turn simple tasks into complex ones.

This article covers three stages: Extract, Transform, Load — with key decisions and engineering practices for each.

---

1. Extract

1.1 Extraction Modes

Mode	Best For	Pros	Cons
Full load	Small tables (< 1M rows)	Simple, no incremental logic	Poor performance at scale
Incremental load	Large tables (> 10M rows)	Pulls only changed data	Handles deletes and updates
Log subscription	Real-time data (CDC)	Non-invasive, real-time	Complex, requires message queue

1.2 Three Incremental Methods

Timestamp: Source table has updated_at field, pull WHERE updated_at > last_sync. Simplest, but cannot handle deletes.

Version number: Source table has version field, incremented on each update. Reliable, but requires source system cooperation.

CDC (Change Data Capture): Capture changes via database binlog or WAL (e.g., Debezium + Kafka). Most real-time, but most complex architecture.

Recommendation: Start with timestamps, add soft-delete (deleted_at) for delete detection, and only consider CDC when scale demands it.

---

2. Transform

2.1 Common Transform Types

// Clean: remove nulls, deduplicate, normalize format
function clean(row: RawRow): CleanRow {
  return {
    id: row.id,
    email: row.email?.toLowerCase().trim(),
    name: row.name?.trim() || 'unknown',
    created_at: new Date(row.created_at).toISOString(),
  };
}

// Transform: type mapping, unit conversion, enum mapping
function transform(row: CleanRow): TargetRow {
  return {
    ...row,
    status: STATUS_MAP[row.status] ?? 'unknown',
    amount: Math.round(row.amount * 100),
  };
}

2.2 Data Quality Checks

After each transform step, check three things:

Check	Method	Threshold
Row count	Source rows vs current rows	Alert if deviation > 5%
Null rate	Null percentage of key fields	Alert if > 1%
Distribution shift	Mean/percentile change	Alert if deviation > 10%

2.3 Idempotency

Every task must be rerunnable. Core principle: running the same task multiple times produces the same result.

const batchId = uuid();
await db.query(`DELETE FROM target_table WHERE batch_id = $1`, [batchId]);
await db.query(`INSERT INTO target_table SELECT *, $1 as batch_id FROM staging`, [batchId]);

---

3. Load

3.1 Loading Strategies

Strategy	Method	Best For
Full replace	TRUNCATE + INSERT	Small tables, daily full
Incremental merge	UPSERT (INSERT ON CONFLICT)	Large tables, incremental
Partition swap	Write to temp table → atomic swap	Large tables, full refresh

3.2 Partition Swap Implementation

CREATE TABLE target_20260722 (LIKE target INCLUDING ALL);
INSERT INTO target_20260722 SELECT * FROM staging;

BEGIN;
DROP TABLE IF EXISTS target_old;
ALTER TABLE target RENAME TO target_old;
ALTER TABLE target_20260722 RENAME TO target;
COMMIT;

DROP TABLE IF EXISTS target_old;

Atomic swap: queries are unaffected during the swap, and rollback is fast if the swap fails.

---

4. Monitoring and Alerting

check_pipeline() {
  local last_run=$(psql -t -c "SELECT max(created_at) FROM pipeline_log" | xargs)
  local now=$(date -u +%s)
  local diff=$((now - $(date -d "$last_run" +%s)))
  if [[ $diff -gt 3600 ]]; then
    echo "Pipeline has not run for over 1 hour" | send_alert
  fi
}

---

Summary

Stage	Key Decision	Recommended Practice
Extract	Full vs incremental	Small tables full, large tables incremental (timestamp)
Transform	Data quality	Check row count, null rate, distribution per step
Load	Idempotency	Batch ID + delete-then-insert
Recovery	Failure retry	Exponential backoff, max 3 retries
Monitoring	Pipeline health	Check last run time, alert on threshold breach

The core of a data pipeline is not “moving fast” — it is “moving stably, accurately, and recoverably.” Idempotent design is far more important than performance optimization. A pipeline that can be safely rerun with consistent results is worth a hundred times more than one that is fast but breaks without recovery.

Need data collection or ETL pipeline design? Contact us — tell us about your data sources and scale, feasibility within 24 hours.

Related reading

• Compliant Data Scraping in Practice — upstream data collection for ETL pipelines
• Ops Automation Script Patterns — automation practices for data pipeline operations