Data Pipeline Architect

This skill provides guidance for designing robust, scalable data pipelines that move data reliably from sources to destinations.

Core Competencies

• •ETL vs ELT: Traditional Extract-Transform-Load vs modern Extract-Load-Transform patterns
• •Orchestration: Airflow, Dagster, Prefect, dbt for workflow management
• •Data Quality: Validation, monitoring, lineage tracking
• •Scalability: Batch vs streaming, partitioning, parallelization

Pipeline Design Process

1. Requirements Analysis

To begin pipeline design, gather:

• •Source systems and data formats (APIs, databases, files, streams)
• •Target destinations (data warehouse, lake, lakehouse)
• •Freshness requirements (real-time, hourly, daily)
• •Data volume and velocity estimates
• •Quality and compliance requirements

2. Architecture Selection

Batch Pipelines - For periodic bulk processing:

• •Schedule-driven (hourly, daily, weekly)
• •Higher latency tolerance
• •Simpler error recovery (re-run entire batch)
• •Tools: Airflow, dbt, Spark

Streaming Pipelines - For real-time requirements:

• •Event-driven processing
• •Sub-second to minute latency
• •Complex state management
• •Tools: Kafka, Flink, Spark Streaming

Hybrid Approaches - Lambda or Kappa architecture:

• •Batch layer for completeness
• •Speed layer for low latency
• •Serving layer for queries

3. ETL vs ELT Decision

ETL (Transform before Load):

• •When target has limited compute
• •When transformation reduces data volume significantly
• •When sensitive data must be masked before landing
• •Legacy data warehouse patterns

ELT (Transform after Load):

• •Modern cloud warehouses with cheap compute
• •When raw data preservation is needed
• •When transformations change frequently
• •dbt-style transformations in warehouse

4. Pipeline Components

Extraction Layer:

• •Full extraction vs incremental (CDC, timestamp-based)
• •API pagination and rate limiting
• •Connection pooling and retry logic
• •Schema detection and drift handling

Transformation Layer:

• •Data cleansing and standardization
• •Business logic application
• •Aggregation and denormalization
• •Type casting and null handling

Loading Layer:

• •Upsert strategies (merge, delete+insert)
• •Partitioning schemes (time, hash, range)
• •Index management
• •Transaction boundaries

5. Error Handling Patterns

┌─────────────────────────────────────────────────────────┐
│                    Pipeline Execution                    │
├─────────────────────────────────────────────────────────┤
│  ┌─────────┐    ┌───────────┐    ┌──────────┐          │
│  │ Extract │───▶│ Transform │───▶│   Load   │          │
│  └────┬────┘    └─────┬─────┘    └────┬─────┘          │
│       │               │               │                 │
│       ▼               ▼               ▼                 │
│  ┌─────────┐    ┌───────────┐    ┌──────────┐          │
│  │  Retry  │    │ Dead Letter│    │ Rollback │          │
│  │ w/Backoff│   │   Queue   │    │ Checkpoint│          │
│  └─────────┘    └───────────┘    └──────────┘          │
└─────────────────────────────────────────────────────────┘

• •Retry with backoff: Transient failures (network, rate limits)
• •Dead letter queues: Poison messages that can't be processed
• •Checkpointing: Resume from last successful point
• •Idempotency: Safe to re-run without duplicates

6. Data Quality Framework

Implement checks at each stage:

7. Monitoring and Observability

Essential metrics to track:

• •Pipeline duration and trends
• •Row counts at each stage
• •Error rates and types
• •Data freshness (time since last successful run)
• •Resource utilization

Alert on:

• •SLA breaches (data not fresh)
• •Anomalous row counts (±20% from baseline)
• •Schema changes in sources
• •Repeated failures

Common Patterns

Slowly Changing Dimensions (SCD)

• •Type 1: Overwrite (no history)
• •Type 2: Add row with validity dates
• •Type 3: Previous value column
• •Type 4: History table

Incremental Processing

-- Timestamp-based incremental
SELECT * FROM source
WHERE updated_at > {{ last_run_timestamp }}

-- CDC-based (Change Data Capture)
-- Captures inserts, updates, deletes from transaction log

Idempotent Loads

-- Delete + Insert pattern
DELETE FROM target WHERE date_partition = '2024-01-15';
INSERT INTO target SELECT * FROM staging WHERE date_partition = '2024-01-15';

-- Merge/Upsert pattern
MERGE INTO target t
USING staging s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET ...
WHEN NOT MATCHED THEN INSERT ...

References

• •references/orchestration-patterns.md - Airflow, Dagster, Prefect patterns
• •references/data-quality-checks.md - Validation frameworks and rules
• •references/pipeline-templates.md - Common pipeline architectures