data-pipeline-architect

Files (5)

• SKILL.md
• data-quality-checks.md · references/
• orchestration-patterns.md · references/
• pipeline-templates.md · references/
• workflow-integration.md · references/

SKILL.md (first lines)

# 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:

| Stage | Check Type | Example |
|-------|------------|---------|
| Extract | Completeness | Row count matches source |
| Extract | Freshness | Data timestamp within SLA |
| Transform | Validity | Values in expected ranges |
| Transform | Uniqueness | Primary keys unique |
| Load | Reconciliation | Target matches source totals |
| Load | Integrity | Foreign keys valid |

### 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

From 4444J99/a-i--skills, MIT.

More from 4444J99/a-i--skills