ETL Pipeline Design Skill

Source: Distilled from Understanding ETL (Updated Edition) by Matt Palmer, O'Reilly Media, August 2025. ISBN: 979-8-341-66508-8

Applicable when designing data ingestion from APIs, databases, or streaming sources; building transformation pipelines (batch or streaming); selecting orchestration tools or patterns; troubleshooting pipeline failures or data quality issues; or optimizing pipelines for efficiency and scale.

Core Concepts

ETL vs ELT

• ETL: Transform data before loading (legacy, when storage was expensive)
• ELT: Load all data first, transform downstream (modern approach — "storage is cheap")
• Modern pipelines are typically ELT, but the term "ETL" persists

Medallion Architecture

Stage data in three quality layers:

1. Data Ingestion

Source Evaluation Checklist

For every data source, answer:

Destination Considerations

• OLAP (BigQuery, Redshift, Snowflake, Databricks SQL): For analytics, column-oriented
• OLTP (Postgres, MySQL): For transactional apps, row-oriented
• Lakehouse (Delta Lake, Iceberg, Hudi): Combines lake + warehouse benefits

Batch vs. Streaming Decision

Is the data bounded (finite)?
├── YES → Batch processing
└── NO (continuous/unbounded) →
    ├── Latency requirement < 1 second? → True streaming (Flink, Kafka Streams)
    └── Latency 100ms-minutes acceptable? → Micro-batch (Spark Structured Streaming)

Streaming Methods:

• Fixed windows: Data batched in fixed time intervals
• Sliding windows: Overlapping time intervals
• Sessions: Dynamic windows based on activity gaps

Choosing Ingestion Solutions

Recommendation: Use declarative for common sources, imperative for edge cases.

2. Data Transformation

Transformation Patterns

Update Patterns

UPSERT Example (Databricks)

MERGE INTO people10m
USING people10mupdates
ON people10m.id = people10mupdates.id
WHEN MATCHED THEN UPDATE SET
  firstName = people10mupdates.firstName,
  lastName = people10mupdates.lastName
WHEN NOT MATCHED THEN INSERT (id, firstName, lastName)
VALUES (people10mupdates.id, people10mupdates.firstName, people10mupdates.lastName)

Best Practices

• Staging: Always stage intermediate data for recoverability
• Idempotency: Running a pipeline twice should produce the same result
• Incrementality: Process only new/changed data when possible

3. Data Orchestration

What Orchestrators Do

• Manage dependencies between tasks (DAGs)
• Schedule and trigger pipelines
• Handle retries, errors, and alerts
• Provide monitoring and lineage

Orchestrator Selection Criteria

Orchestrator Options

Design Patterns

4. Troubleshooting & Observability

Key Metrics

Observability Methods

Error Handling

Incident Metrics

• N: Number of incidents
• TTD: Time to detection
• TTR: Time to resolution
• Downtime: N × (TTD + TTR)

5. Efficiency & Scalability

Resource Allocation

Optimization Techniques

Scaling Formula

Horizontal scaling = More machines/nodes
Vertical scaling = Bigger machines/nodes

Decision Framework

When designing a pipeline:

1. Understand the source → Use source evaluation checklist
2. Determine frequency → Batch vs. streaming based on bounds + requirements
3. Choose ingestion → Declarative for common, imperative for custom
4. Design transformations → Apply appropriate patterns
5. Plan updates → Overwrite, insert, or upsert based on use case
6. Orchestrate → Select tool, apply design patterns
7. Observe → Implement logging, lineage, assertions
8. Optimize → Incrementality, partitioning, right-sizing resources

Anti-Patterns to Avoid

Reference Files

For detailed patterns and extended guidance, consult:

• patterns/transformation-patterns.md — Detailed transformation pattern catalog with examples
• patterns/orchestration-patterns.md — Advanced orchestration design patterns and DAG strategies
• troubleshooting/observability-guide.md — Comprehensive observability setup, monitoring, and incident response
• checklists/evaluation-checklists.md — Pipeline evaluation checklists for design reviews and production readiness