Data Pipeline Architect

Overview

This skill helps you design and review production-grade data pipelines. It covers the full lifecycle: ingestion, transformation, idempotency and backfills, schema evolution, orchestration, and data quality. It is opinionated toward modern ELT (load raw, transform in-warehouse) but supports classic ETL where it fits.

Keywords: ETL, ELT, data pipeline, ingestion, idempotency, backfill, schema evolution, schema drift, CDC, incremental load, watermark, orchestration, Airflow, Dagster, Prefect, dbt, data quality, freshness, dedup, late-arriving data, partitioning, medallion, bronze silver gold.

Use this skill to produce a concrete pipeline design (a design doc), to review an existing pipeline against best practices, or to generate skeleton DAGs/models and data-quality checks.

Decision: ETL vs ELT

Default to ELT when the destination is a modern columnar warehouse/lake (Snowflake, BigQuery, Redshift, Databricks, DuckDB). Land raw data first, transform with SQL/dbt. Use ETL when: the destination can't transform cheaply, you must mask/drop PII before it lands (compliance), or you transform in-flight for a stream. See references/etl-vs-elt.md.

Workflow

Follow these steps in order. Produce the design document in templates/pipeline-design.md as you go.

1. Clarify requirements. Capture: sources, destination, SLA/freshness (real-time, hourly, daily), volume (rows/day, GB/day), data sensitivity (PII?), and consumers (BI, ML, reverse-ETL). Don't design before you know freshness and volume — they drive batch-vs-stream and incremental-vs-full.

1. Choose load pattern. Decide ETL vs ELT (above) and batch vs streaming. Map each source to an extraction strategy: full snapshot, incremental by watermark, or CDC. See references/ingestion-patterns.md.

1. Design for idempotency. Every load step must be safe to re-run and produce the same result. Use the techniques in references/idempotency.md: deterministic partition keys, MERGE/upsert on a stable business key, delete-insert by partition, or staging-then-atomic-swap. Never blind INSERT into a target on retry.

1. Plan incremental extraction + watermarks. Pick a high-watermark column (updated_at, monotonic id, or LSN/CDC offset). Store the watermark in a state table, not in code. Re-read with overlap (lookback window) to catch late updates, then dedup. See references/ingestion-patterns.md.

1. Handle schema evolution. Decide a policy per layer: additive-only in raw (auto-add columns), explicit contracts in curated layers. Choose a file format that supports evolution (Parquet/Avro/Delta/Iceberg). Define what happens on a breaking change (quarantine, alert, fail). See references/schema-evolution.md.

1. Model the transformation layers. Use the medallion pattern: bronze (raw, append-only, typed minimally) → silver (cleaned, deduped, conformed) → gold (business marts). Keep transformations declarative and version-controlled (dbt models). See references/orchestration.md.

1. Choose orchestration. Map dependencies as a DAG. Pick a tool by fit (Airflow for ops maturity, Dagster for asset/data-aware, Prefect for Pythonic dynamism, dbt for SQL transforms). Define schedules, retries with backoff, timeouts, SLAs, and alerting. See references/orchestration.md.

1. Add data quality gates. Place checks at boundaries: post-ingestion (row counts, schema), post-transform (uniqueness, not-null, referential, accepted values), and freshness. Fail loud or quarantine — never silently pass bad data downstream. See references/data-quality.md.

1. Plan backfills & reprocessing. Pipelines must support re-running an arbitrary date range without duplicates. Parameterize by partition/date; lean on idempotency from step 3. Document the backfill command.

1. Observability & cost. Emit metrics (rows in/out, duration, freshness lag, failure rate), structured logs, and lineage. Right-size partitions and prune scans to control warehouse cost.

1. Validate the design. Run scripts/pipeline_lint.py against the design doc / config to catch missing idempotency keys, absent quality checks, no retry policy, and no schema-evolution policy.

Quick Heuristics

• Land raw, immutable, partitioned by ingestion date. Raw is your replay log; never mutate it.
• One business key, everywhere. Idempotency, dedup, and MERGE all hinge on a stable natural/surrogate key.
• Watermark with a lookback. Always re-scan a small overlap window and dedup, or you will lose late-arriving rows.
• Partition by event/date, not by load time, for query pruning — but partition raw by load date for replay.
• Quality checks are part of the DAG, not a cron afterthought. A failed check should block promotion to the next layer.
• Make every task retry-safe before adding retries. Retries amplify non-idempotent bugs.

Best Practices

• Separate extract (E) from load (L) so you can replay loads from cached raw extracts.
• Use staging tables + atomic swap/MERGE; never partially-write a target table.
• Store pipeline state (watermarks, run metadata) in a queryable table, not in files or memory.
• Treat schema as a contract between producer and consumer; version it and test it.
• Tag/partition data with a _loaded_at, _source, and _batch_id for lineage and debugging.
• Encode SLAs and freshness as monitored checks, not tribal knowledge.
• Keep secrets out of code; use a secret manager and least-privilege source credentials.

Common Pitfalls

• Blind appends on retry → duplicates. Fix with upsert/MERGE or delete-insert by partition.
• Watermark = exactly last max(updated_at) → silently drops rows updated within the same second or arriving late. Use a lookback window.
• Auto-evolving schema into gold tables → breaks BI dashboards. Auto-evolve only in raw/bronze.
• Full reloads "because it's simpler" at scale → cost and SLA blowups. Move to incremental once volume grows.
• Quality checks that warn but don't block → bad data reaches consumers. Gate promotion on checks.
• No backfill story → an upstream outage means manual, error-prone recovery. Parameterize by date from day one.
• Coupling extraction and transformation → can't replay or reprocess without re-hitting the source API/DB.

Bundled Resources

• references/etl-vs-elt.md — decision matrix, batch vs streaming, when each wins.
• references/ingestion-patterns.md — full/incremental/CDC, watermarks, late-arriving data, dedup SQL.
• references/idempotency.md — MERGE, delete-insert, staging swap patterns with SQL.
• references/schema-evolution.md — formats, policies per layer, breaking-change handling.
• references/orchestration.md — tool comparison, DAG/retry/SLA patterns, medallion layering.
• references/data-quality.md — check taxonomy, where to place gates, dbt/Great Expectations examples.
• templates/pipeline-design.md — fill-in design document.
• examples/orders-pipeline.md — worked end-to-end example (Postgres → warehouse).
• scripts/pipeline_lint.py — lints a pipeline design config (YAML/JSON) for required safeguards.