| name | pipeline-design |
| description | Use when designing data pipelines for moving, transforming, and delivering data. Covers ETL vs ELT pattern selection, orchestration tool choice, batch vs streaming trade-offs, idempotency guarantees, data quality checkpoints, and lineage tracking. Do not use for schema modeling (use schema-evaluation) or ML workflows (use ml-workflow). |
| metadata | {"author":"dtsong"} |
Pipeline Design
Purpose
Design data pipelines that reliably move, transform, and deliver data from source systems to consumption layers. Covers ETL vs ELT pattern selection, orchestration tool choice, batch vs streaming trade-offs, idempotency guarantees, data quality checkpoints, and lineage tracking.
Scope Constraints
Reads pipeline configurations, DAG definitions, orchestration manifests, and infrastructure specs for analysis. Does not execute pipelines, deploy infrastructure, or modify production configurations.
Inputs
- Source systems and their data formats (databases, APIs, event streams, files)
- Destination systems (warehouse, lake, feature store, BI tool)
- Data volume and velocity (rows/day, events/second, payload size)
- Freshness requirements (real-time, near-real-time, hourly, daily)
- Existing infrastructure (cloud provider, orchestration tools, current pipelines)
- Team size and expertise (SQL-heavy? Python-heavy? Platform team available?)
Input Sanitization
No user-provided values are used in commands or file paths. All inputs are treated as read-only analysis targets.
Procedure
Progress Checklist
Step 1: Map Source-to-Destination Flows
Document every data flow:
- Source system, extraction method (CDC, API poll, event subscription, file drop)
- Transformation requirements (cleaning, joining, aggregating, enriching)
- Destination system and loading pattern (append, upsert, full refresh)
- Data volume per flow (rows/batch, events/second)
Produce a flow diagram showing all sources, transformations, and destinations.
Step 2: Choose ETL vs ELT Pattern
Evaluate the trade-offs:
- ETL (Extract-Transform-Load): Transform before loading. Best when: destination has limited compute, transformations reduce data volume significantly, or sensitive data must be filtered before landing.
- ELT (Extract-Load-Transform): Load raw first, transform in-warehouse. Best when: destination has powerful compute (BigQuery, Snowflake, Databricks), you want raw data preserved for auditability, or transformation logic changes frequently.
Document the chosen pattern per flow and the reasoning.
Step 3: Select Batch vs Streaming
For each flow, determine the processing mode:
- Batch: Scheduled intervals (hourly, daily). Simple, cost-effective, good tooling. Best when freshness SLA is minutes-to-hours.
- Micro-batch: Frequent small batches (every 1-5 minutes). Compromise between batch simplicity and near-real-time freshness.
- Streaming: Continuous processing (Kafka, Kinesis, Flink). Best when freshness SLA is seconds and data arrives as an event stream.
Document latency requirements, cost implications, and complexity trade-offs for the chosen mode.
Step 4: Design for Idempotency
Ensure every pipeline step is safe to re-run:
- Use merge/upsert patterns instead of blind inserts
- Partition data by time to enable clean backfills without full re-processing
- Use deterministic IDs (content-based hashing) or natural keys for deduplication
- Document the idempotency strategy for each pipeline step: "If this step runs twice for the same input, what happens?"
Step 5: Define Data Quality Checkpoints
Insert quality gates between pipeline stages:
- Source validation: Schema conformance, null rates, row count thresholds
- Transformation validation: Referential integrity, business rule assertions, duplicate detection
- Destination validation: Row count reconciliation (source vs destination), freshness checks, metric drift alerts
For each checkpoint, define: what is checked, what threshold triggers a failure, and what happens on failure (halt pipeline, alert, quarantine bad records).
Step 6: Plan Lineage and Observability
Design data lineage tracking:
- Column-level lineage from source to destination
- Transformation dependency graph (which tables feed which downstream models)
- Pipeline execution metadata (start time, end time, rows processed, errors)
- Alerting on SLA breaches, data quality failures, and pipeline errors
Specify tooling: dbt lineage, OpenLineage, Datahub, or custom metadata tables.
Step 7: Select Orchestration Tool
Choose the orchestration layer based on team and requirements:
- dbt — SQL-centric transformations, built-in lineage, great for ELT. Best for analytics engineering teams.
- Airflow — General-purpose DAG orchestration, large ecosystem, battle-tested. Best for complex multi-system pipelines.
- Dagster — Software-defined assets, strong typing, built-in observability. Best for teams wanting modern DX and asset-centric thinking.
- Prefect — Python-native, dynamic workflows, lightweight. Best for Python-heavy teams with simpler orchestration needs.
Document the choice, alternatives considered, and migration path if the team outgrows the tool.
Compaction resilience: If context was lost during a long session, re-read the Inputs section to reconstruct what system is being analyzed, check the Progress Checklist for completed steps, then resume from the earliest incomplete step.
Handoff
- Hand off to schema-evaluation if the pipeline design reveals data modeling or warehouse schema concerns.
- Hand off to warden/incident-analysis if pipeline failures indicate systemic reliability or incident response needs.
Output Format
# Pipeline Design: [Project/Domain Name]
## Flow Diagram
[ASCII diagram showing sources → transformations → destinations]
## Flow Inventory
| Flow | Source | Extraction | Transform | Load Pattern | Volume | Freshness SLA |
|------|--------|-----------|-----------|-------------|--------|---------------|
| ... | ... | ... | ... | ... | ... | ... |
## Architecture Decisions
| Decision | Chosen | Alternatives | Rationale |
|----------|--------|-------------|-----------|
| ETL vs ELT | ... | ... | ... |
| Batch vs Streaming | ... | ... | ... |
| Orchestration tool | ... | ... | ... |
## Idempotency Strategy
| Pipeline Step | Idempotency Method | Re-run Behavior |
|---------------|-------------------|-----------------|
| ... | ... | ... |
## Data Quality Checkpoints
| Stage | Check | Threshold | On Failure |
|-------|-------|-----------|------------|
| Source | ... | ... | ... |
| Transform | ... | ... | ... |
| Destination | ... | ... | ... |
## Lineage and Observability
| Capability | Tool/Method | Coverage |
|-----------|-------------|----------|
| Column lineage | ... | ... |
| Pipeline metrics | ... | ... |
| Alerting | ... | ... |
## Orchestration Design
| DAG/Pipeline | Schedule | Dependencies | SLA |
|-------------|----------|-------------|-----|
| ... | ... | ... | ... |
Quality Checks
Evolution Notes
Converted and distributed by TomeVault — claim your Tome and manage your conversions.
