pipeline-design

timequity's avatarfrom timequity

Design ETL/ELT pipeline architectures with proper patterns for reliability and scalability.

⭐0stars🔀0forks📁View on GitHub🕐Updated Dec 18, 2025
[Architecture Design]

When & Why to Use This Skill

This Claude skill empowers users to architect high-performance ETL/ELT data pipelines with a focus on reliability and scalability. It provides expert guidance on selecting optimal processing patterns—whether batch or streaming—and implementing industry best practices like idempotency, observability, and robust error handling to ensure data integrity across modern data warehouses.

Use Cases

  • Case 1: Designing scalable ELT architectures for modern cloud data warehouses such as Snowflake, BigQuery, or Redshift to optimize compute efficiency.
  • Case 2: Architecting real-time, event-driven streaming pipelines using Kafka or Kinesis with exactly-once processing semantics.
  • Case 3: Implementing idempotent and incremental loading patterns to ensure data consistency and reduce redundant processing costs.
  • Case 4: Developing resilient error-handling strategies, including dead letter queues and exponential backoff, to maintain high pipeline uptime.
namepipeline-design
descriptionDesign ETL/ELT pipeline architectures with proper patterns for reliability and scalability.

Pipeline Design

ETL vs ELT

Approach When to Use
ETL Transform before load, limited warehouse compute
ELT Modern warehouses (Snowflake, BigQuery, Redshift)

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