Pipeline Design

ETL vs ELT

Pipeline Patterns

Batch

Source → Extract → Stage → Transform → Load → Target
         │                    │
         └── Checkpoint ──────┘

• •Scheduled intervals (hourly, daily)
• •Full or incremental loads
• •Idempotent operations

Streaming

Source → Kafka/Kinesis → Process → Sink
              │
              └── State Store

• •Real-time requirements
• •Event-driven architecture
• •Exactly-once semantics

Design Principles

1. •Idempotent - Safe to re-run
2. •Incremental - Process only new/changed data
3. •Observable - Metrics, logs, alerts
4. •Testable - Unit tests for transformations
5. •Recoverable - Checkpoints, retry logic

Staging Pattern

-- 1. Land raw data
COPY INTO raw.source_data FROM @stage;

-- 2. Deduplicate
CREATE TABLE staging.deduped AS
SELECT * FROM raw.source_data
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY _loaded_at DESC) = 1;

-- 3. Transform to target
MERGE INTO target.dim_customer
USING staging.deduped
ON target.id = staging.id
WHEN MATCHED THEN UPDATE ...
WHEN NOT MATCHED THEN INSERT ...;

Error Handling

• •Dead letter queues for failed records
• •Retry with exponential backoff
• •Alert on threshold breaches
• •Quarantine bad data for review