Building Reliable ETL Pipelines
Production pipelines must be idempotent, observable, and recoverable. Learn incremental loading, idempotent writes, error handling, and data quality checks that keep pipelines trustworthy.
Anatomy of a Production Pipeline
A production-grade pipeline is more than extract-transform-load code. It includes:
- Ingestion — Reading from sources with retries and rate limiting.
- Staging — Landing raw data unchanged for auditability.
- Transformation — Cleaning, conforming, and modeling the data.
- Validation — Automated data quality checks before publishing.
- Monitoring — Alerts on failures, latency, and anomalies in row counts.
Full vs Incremental Loads
A full load reprocesses everything each run — simple but expensive. An incremental load processes only new or changed records, typically tracked with a *watermark* such as updated_at or an auto-increment ID. Incremental loads scale far better as data grows.
from datetime import datetime
def get_watermark(conn) -> datetime:
row = conn.execute(
"SELECT MAX(updated_at) FROM staging.orders"
).fetchone()
return row[0] or datetime(1970, 1, 1)
def extract_incremental(source_conn, watermark):
return source_conn.execute(
"SELECT * FROM orders WHERE updated_at > %s", (watermark,)
).fetchall()Idempotency
A pipeline is idempotent if running it multiple times with the same input produces the same result. This makes retries and backfills safe. The most common technique is an upsert (MERGE) keyed on a unique ID rather than blind INSERTs.
MERGE INTO warehouse.orders AS t
USING staging.orders_batch AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
UPDATE SET amount = s.amount,
status = s.status,
updated_at = s.updated_at
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status, updated_at)
VALUES (s.order_id, s.customer_id, s.amount, s.status, s.updated_at);Data Quality Checks
Validate data before it reaches consumers. Typical checks include:
- Not-null and uniqueness checks on key columns.
- Referential integrity between fact and dimension tables.
- Accepted-value and range checks (e.g. amount >= 0).
- Row-count and freshness checks versus the previous run.
Key Takeaways
- Land raw data first, then transform — it makes debugging and replay easy.
- Prefer incremental loads with watermarks as data volume grows.
- Idempotent writes (MERGE/upsert) make retries safe.
- Automate data quality checks and alert on failures.
Contact Us
Have a question or feedback? Fill out the form below or reach us directly at support@nvaitraining.com