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:
| Layer |
Purpose |
Example |
| Bronze |
Raw, unfiltered data directly from sources |
API responses, raw logs |
| Silver |
Cleaned, filtered, enriched |
Removed duplicates, renamed columns |
| Gold |
Stakeholder-ready, often aggregated |
Reporting tables, ML features |
1. Data Ingestion
Source Evaluation Checklist
For every data source, answer:
| Question |
Why It Matters |
| Who will use this data? |
Aligns incentives, prioritizes work |
| How will it be used? |
Guides downstream decisions |
| Is it bounded or unbounded? |
Determines batch vs. streaming |
| What's the minimum update frequency? |
Sets hard limits on freshness |
| What's the expected volume? |
Informs storage/compute choices |
| What's the format? (JSON, CSV, API, DB) |
Dictates processing requirements |
| What's the quality? |
Determines transformation needs |
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
| Type |
Examples |
Pros |
Cons |
| Legacy declarative |
Talend, Pentaho |
Robust connectors |
Outdated, not MDS-aligned |
| Modern declarative |
Fivetran, Airbyte, Stitch |
Low maintenance, connectors |
Vendor lock-in, cost |
| Native/Platform |
Lakeflow Connect, Glue |
Integrated, managed |
Platform-specific |
| Imperative |
Custom scripts, Singer taps |
Full control |
High build/maintain cost |
| Hybrid |
Mix of above |
Flexibility |
Complexity |
Recommendation: Use declarative for common sources, imperative for edge cases.
2. Data Transformation
Transformation Patterns
| Pattern |
Description |
Example |
| Enrichment |
Add data from other sources |
Join order codes → readable names |
| Joining |
Combine datasets on common keys |
Sales + Users → add country |
| Filtering |
Select only needed records |
WHERE date >= '2025-01-01' |
| Structuring |
Convert formats |
JSON → tabular Parquet |
| Conversion |
Change data types |
String → datetime |
| Aggregation |
Summarize data |
Daily totals from hourly data |
| Anonymization |
Mask PII |
Hash emails |
| Splitting |
Break columns apart |
email → prefix + domain |
| Deduplication |
Remove duplicates |
Keep earliest by UUID |
Update Patterns
| Pattern |
When to Use |
SQL Concept |
| Overwrite |
Small datasets, simple refreshes |
TRUNCATE + INSERT |
| Insert |
Append-only data (logs, transactions) |
INSERT INTO |
| Upsert |
CDC, deduplication, SCD |
MERGE |
| Delete |
Soft (status='deleted') or hard (remove row) |
UPDATE or DELETE |
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
| Criterion |
Questions to Ask |
| Scalability |
Can it handle 10x DAGs/tasks? |
| Reusability |
Can I create reusable components? |
| Connections |
Native integrations with my stack? |
| Support |
Active community or paid support? |
| Observability |
Can I see failures, lineage, logs? |
Orchestrator Options
| Tool |
Type |
Best For |
| Airflow |
Open source |
Mature, widely adopted, many connectors |
| Dagster |
Open source |
Modern, asset-based, good DX |
| Prefect |
Open source |
Python-native, flexible |
| Lakeflow Jobs |
Platform |
Databricks-native, integrated |
| dbt |
SQL orchestrator |
Warehouse transformations |
Design Patterns
| Pattern |
Description |
| Backfills |
Build pipelines that can recreate historical data |
| Event-driven |
Trigger on data arrival, not just schedules |
| Conditional logic |
Branch based on conditions (if/else) |
| Concurrency |
Fan out parallel tasks for performance |
| Parameterized |
Accept variables for flexibility |
| Decomposition |
Break into micro-DAGs for isolation |
4. Troubleshooting & Observability
Key Metrics
| Metric |
What It Measures |
| Freshness |
Time since last update |
| Volume |
Row counts, data sizes |
| Quality |
Uniqueness, completeness, validity |
Observability Methods
| Method |
Purpose |
| Logging |
Capture execution details |
| Lineage |
Track data flow (column-level ideal) |
| Anomaly detection |
Catch unexpected data patterns |
| Data diffs |
See what code changes affect data |
| Assertions |
Validate constraints (price > 0) |
Error Handling
| Technique |
Description |
| Retry logic |
Automatic retries with backoff |
| Conditional handling |
Different paths for different errors |
| Pipeline decomposition |
Isolate failures |
| Graceful degradation |
Partial functionality on failure |
| Alerting |
Notify team (avoid alert fatigue) |
Incident Metrics
- N: Number of incidents
- TTD: Time to detection
- TTR: Time to resolution
- Downtime: N × (TTD + TTR)
5. Efficiency & Scalability
Resource Allocation
| Concept |
Description |
| Spot instances |
Cheaper but interruptible |
| On-demand |
Reliable but costly |
| Pooling |
Pre-warm clusters for faster starts |
| Autoscaling |
Adjust resources to workload |
| Serverless |
Pay per use (BigQuery, Databricks SQL) |
Optimization Techniques
| Technique |
Benefit |
| Incremental processing |
Only process new/changed data |
| Columnar storage |
Faster analytics (Parquet, Delta) |
| Partitioning |
Reduce data scanned |
| Materialization |
Tables vs. views trade-offs |
Scaling Formula
Horizontal scaling = More machines/nodes
Vertical scaling = Bigger machines/nodes
Decision Framework
When designing a pipeline:
- Understand the source → Use source evaluation checklist
- Determine frequency → Batch vs. streaming based on bounds + requirements
- Choose ingestion → Declarative for common, imperative for custom
- Design transformations → Apply appropriate patterns
- Plan updates → Overwrite, insert, or upsert based on use case
- Orchestrate → Select tool, apply design patterns
- Observe → Implement logging, lineage, assertions
- Optimize → Incrementality, partitioning, right-sizing resources
Anti-Patterns to Avoid
| Anti-Pattern |
Why It's Bad |
| Mixing orchestration with transformation |
Orchestrator should trigger, not execute |
| No staging layer |
Hard to recover from failures |
| Non-idempotent pipelines |
Reruns cause duplicates or errors |
| Ignoring lineage |
Impossible to debug data issues |
| Alert fatigue |
Too many alerts = ignored alerts |
| GUI-only tooling |
No version control, hard to collaborate |
Reference Files
For detailed patterns and extended guidance, consult:
patterns/transformation-patterns.md — Detailed transformation pattern catalog with examplespatterns/orchestration-patterns.md — Advanced orchestration design patterns and DAG strategiestroubleshooting/observability-guide.md — Comprehensive observability setup, monitoring, and incident responsechecklists/evaluation-checklists.md — Pipeline evaluation checklists for design reviews and production readiness
Converted and distributed by TomeVault — claim your Tome and manage your conversions.
1---2name: etl-pipeline-design3description: This skill should be used when the user asks to "design an ETL pipeline", "build data ingestion", "set up data orchestration", "troubleshoot pipeline issues", "optimize data workflows", or mentions ELT, medallion architecture, batch vs streaming, or data transformation patterns. Use when this capability is needed.4---56# ETL Pipeline Design Skill78> **Source**: Distilled from *Understanding ETL (Updated Edition)* by Matt Palmer, O'Reilly Media, August 2025. ISBN: 979-8-341-66508-8910Applicable 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.1112---1314## Core Concepts1516### ETL vs ELT1718- **ETL**: Transform data before loading (legacy, when storage was expensive)19- **ELT**: Load all data first, transform downstream (modern approach — "storage is cheap")20- Modern pipelines are typically ELT, but the term "ETL" persists2122### Medallion Architecture2324Stage data in three quality layers:2526| Layer | Purpose | Example |27| ------- | --------- | --------- |28| **Bronze** | Raw, unfiltered data directly from sources | API responses, raw logs |29| **Silver** | Cleaned, filtered, enriched | Removed duplicates, renamed columns |30| **Gold** | Stakeholder-ready, often aggregated | Reporting tables, ML features |3132---3334## 1. Data Ingestion3536### Source Evaluation Checklist3738For every data source, answer:3940| Question | Why It Matters |41| ---------- | ---------------- |42| Who will use this data? | Aligns incentives, prioritizes work |43| How will it be used? | Guides downstream decisions |44| Is it bounded or unbounded? | Determines batch vs. streaming |45| What's the minimum update frequency? | Sets hard limits on freshness |46| What's the expected volume? | Informs storage/compute choices |47| What's the format? (JSON, CSV, API, DB) | Dictates processing requirements |48| What's the quality? | Determines transformation needs |4950### Destination Considerations5152- **OLAP** (BigQuery, Redshift, Snowflake, Databricks SQL): For analytics, column-oriented53- **OLTP** (Postgres, MySQL): For transactional apps, row-oriented54- **Lakehouse** (Delta Lake, Iceberg, Hudi): Combines lake + warehouse benefits5556### Batch vs. Streaming Decision5758```text59Is the data bounded (finite)?60├── YES → Batch processing61└── NO (continuous/unbounded) →62 ├── Latency requirement < 1 second? → True streaming (Flink, Kafka Streams)63 └── Latency 100ms-minutes acceptable? → Micro-batch (Spark Structured Streaming)64```6566**Streaming Methods**:6768- **Fixed windows**: Data batched in fixed time intervals69- **Sliding windows**: Overlapping time intervals70- **Sessions**: Dynamic windows based on activity gaps7172### Choosing Ingestion Solutions7374| Type | Examples | Pros | Cons |75| ------ | ---------- | ------ | ------ |76| **Legacy declarative** | Talend, Pentaho | Robust connectors | Outdated, not MDS-aligned |77| **Modern declarative** | Fivetran, Airbyte, Stitch | Low maintenance, connectors | Vendor lock-in, cost |78| **Native/Platform** | Lakeflow Connect, Glue | Integrated, managed | Platform-specific |79| **Imperative** | Custom scripts, Singer taps | Full control | High build/maintain cost |80| **Hybrid** | Mix of above | Flexibility | Complexity |8182**Recommendation**: Use declarative for common sources, imperative for edge cases.8384---8586## 2. Data Transformation8788### Transformation Patterns8990| Pattern | Description | Example |91| --------- | ------------- | --------- |92| **Enrichment** | Add data from other sources | Join order codes → readable names |93| **Joining** | Combine datasets on common keys | Sales + Users → add country |94| **Filtering** | Select only needed records | `WHERE date >= '2025-01-01'` |95| **Structuring** | Convert formats | JSON → tabular Parquet |96| **Conversion** | Change data types | String → datetime |97| **Aggregation** | Summarize data | Daily totals from hourly data |98| **Anonymization** | Mask PII | Hash emails |99| **Splitting** | Break columns apart | email → prefix + domain |100| **Deduplication** | Remove duplicates | Keep earliest by UUID |101102### Update Patterns103104| Pattern | When to Use | SQL Concept |105| --------- | ------------- | ------------- |106| **Overwrite** | Small datasets, simple refreshes | `TRUNCATE` + `INSERT` |107| **Insert** | Append-only data (logs, transactions) | `INSERT INTO` |108| **Upsert** | CDC, deduplication, SCD | `MERGE` |109| **Delete** | Soft (status='deleted') or hard (remove row) | `UPDATE` or `DELETE` |110111### UPSERT Example (Databricks)112113```sql114MERGE INTO people10m115USING people10mupdates116ON people10m.id = people10mupdates.id117WHEN MATCHED THEN UPDATE SET118 firstName = people10mupdates.firstName,119 lastName = people10mupdates.lastName120WHEN NOT MATCHED THEN INSERT (id, firstName, lastName)121VALUES (people10mupdates.id, people10mupdates.firstName, people10mupdates.lastName)122```123124### Best Practices125126- **Staging**: Always stage intermediate data for recoverability127- **Idempotency**: Running a pipeline twice should produce the same result128- **Incrementality**: Process only new/changed data when possible129130---131132## 3. Data Orchestration133134### What Orchestrators Do135136- Manage dependencies between tasks (DAGs)137- Schedule and trigger pipelines138- Handle retries, errors, and alerts139- Provide monitoring and lineage140141### Orchestrator Selection Criteria142143| Criterion | Questions to Ask |144| ----------- | ------------------ |145| **Scalability** | Can it handle 10x DAGs/tasks? |146| **Reusability** | Can I create reusable components? |147| **Connections** | Native integrations with my stack? |148| **Support** | Active community or paid support? |149| **Observability** | Can I see failures, lineage, logs? |150151### Orchestrator Options152153| Tool | Type | Best For |154| ------ | ------ | ---------- |155| **Airflow** | Open source | Mature, widely adopted, many connectors |156| **Dagster** | Open source | Modern, asset-based, good DX |157| **Prefect** | Open source | Python-native, flexible |158| **Lakeflow Jobs** | Platform | Databricks-native, integrated |159| **dbt** | SQL orchestrator | Warehouse transformations |160161### Design Patterns162163| Pattern | Description |164| --------- | ------------- |165| **Backfills** | Build pipelines that can recreate historical data |166| **Event-driven** | Trigger on data arrival, not just schedules |167| **Conditional logic** | Branch based on conditions (if/else) |168| **Concurrency** | Fan out parallel tasks for performance |169| **Parameterized** | Accept variables for flexibility |170| **Decomposition** | Break into micro-DAGs for isolation |171172---173174## 4. Troubleshooting & Observability175176### Key Metrics177178| Metric | What It Measures |179| -------- | ------------------ |180| **Freshness** | Time since last update |181| **Volume** | Row counts, data sizes |182| **Quality** | Uniqueness, completeness, validity |183184### Observability Methods185186| Method | Purpose |187| -------- | --------- |188| **Logging** | Capture execution details |189| **Lineage** | Track data flow (column-level ideal) |190| **Anomaly detection** | Catch unexpected data patterns |191| **Data diffs** | See what code changes affect data |192| **Assertions** | Validate constraints (`price > 0`) |193194### Error Handling195196| Technique | Description |197| ----------- | ------------- |198| **Retry logic** | Automatic retries with backoff |199| **Conditional handling** | Different paths for different errors |200| **Pipeline decomposition** | Isolate failures |201| **Graceful degradation** | Partial functionality on failure |202| **Alerting** | Notify team (avoid alert fatigue) |203204### Incident Metrics205206- **N**: Number of incidents207- **TTD**: Time to detection208- **TTR**: Time to resolution209- **Downtime**: N × (TTD + TTR)210211---212213## 5. Efficiency & Scalability214215### Resource Allocation216217| Concept | Description |218| --------- | ------------- |219| **Spot instances** | Cheaper but interruptible |220| **On-demand** | Reliable but costly |221| **Pooling** | Pre-warm clusters for faster starts |222| **Autoscaling** | Adjust resources to workload |223| **Serverless** | Pay per use (BigQuery, Databricks SQL) |224225### Optimization Techniques226227| Technique | Benefit |228| ----------- | --------- |229| **Incremental processing** | Only process new/changed data |230| **Columnar storage** | Faster analytics (Parquet, Delta) |231| **Partitioning** | Reduce data scanned |232| **Materialization** | Tables vs. views trade-offs |233234### Scaling Formula235236```text237Horizontal scaling = More machines/nodes238Vertical scaling = Bigger machines/nodes239```240241---242243## Decision Framework244245When designing a pipeline:2462471. **Understand the source** → Use source evaluation checklist2482. **Determine frequency** → Batch vs. streaming based on bounds + requirements2493. **Choose ingestion** → Declarative for common, imperative for custom2504. **Design transformations** → Apply appropriate patterns2515. **Plan updates** → Overwrite, insert, or upsert based on use case2526. **Orchestrate** → Select tool, apply design patterns2537. **Observe** → Implement logging, lineage, assertions2548. **Optimize** → Incrementality, partitioning, right-sizing resources255256---257258## Anti-Patterns to Avoid259260| Anti-Pattern | Why It's Bad |261| -------------- | -------------- |262| Mixing orchestration with transformation | Orchestrator should trigger, not execute |263| No staging layer | Hard to recover from failures |264| Non-idempotent pipelines | Reruns cause duplicates or errors |265| Ignoring lineage | Impossible to debug data issues |266| Alert fatigue | Too many alerts = ignored alerts |267| GUI-only tooling | No version control, hard to collaborate |268269---270271## Reference Files272273For detailed patterns and extended guidance, consult:274275- **`patterns/transformation-patterns.md`** — Detailed transformation pattern catalog with examples276- **`patterns/orchestration-patterns.md`** — Advanced orchestration design patterns and DAG strategies277- **`troubleshooting/observability-guide.md`** — Comprehensive observability setup, monitoring, and incident response278- **`checklists/evaluation-checklists.md`** — Pipeline evaluation checklists for design reviews and production readiness279280---281> Converted and distributed by [TomeVault](https://tomevault.io/claim/back1ply) — claim your Tome and manage your conversions.282<!-- tomevault:4.0:skill_md:2026-04-14 -->
tomevault-io