etl-elt-patterns

# ETL/ELT Patterns

Patterns for data extraction, loading, and transformation including modern ELT approaches and pipeline design.

## When to Use This Skill

- Choosing between ETL and ELT
- Designing data pipelines
- Implementing data transformations
- Building modern data stacks
- Handling data quality in pipelines

## ETL vs ELT

### ETL (Extract, Transform, Load)

```text
┌─────────┐     ┌─────────────┐     ┌─────────────┐
│ Sources │ ──► │  Transform  │ ──► │  Warehouse  │
└─────────┘     │   Server    │     └─────────────┘
                └─────────────┘
                (Transformation happens
                 before loading)

Characteristics:
- Transform before load
- Requires ETL server/tool
- Schema-on-write
- Traditional approach

Best for:
- Complex transformations
- Limited target storage
- Strict data quality requirements
- Legacy systems
```

### ELT (Extract, Load, Transform)

```text
┌─────────┐     ┌─────────────┐     ┌─────────────┐
│ Sources │ ──► │   Target    │ ──► │  Transform  │
└─────────┘     │  (Load raw) │     │  (in-place) │
                └─────────────┘     └─────────────┘
                                    (Transformation happens
                                     after loading)

Characteristics:
- Load first, transform later
- Uses target system's compute
- Schema-on-read
- Modern approach

Best for:
- Cloud data warehouses
- Flexible exploration
- Iterative development
- Large data volumes
```

### Comparison

| Factor | ETL | ELT |
| ------ | --- | --- |
| Transform timing | Before load | After load |
| Compute location | Separate server | Target system |
| Raw data access | Limited | Full |
| Flexibility | Low | High |
| Latency | Higher | Lower |
| Cost model | ETL server + storage | Storage + target compute |
| Best for | Complex, pre-defined | Exploratory, iterative |

## Modern Data Stack

```text
Extract:        Fivetran, Airbyte, Stitch, Custom
                        │
                        ▼
Load:           Cloud War