# Pipeline Design

> Use when designing data pipelines for moving, transforming, and delivering data. Covers ETL vs ELT pattern selection, orchestration tool choice, batch vs streaming trade-offs, idempotency guarantees, data quality checkpoints, and lineage tracking. Do not use for schema modeling (use schema-evaluation) or ML workflows (use ml-workflow).

- **Type:** Skill
- **Install:** `agentstack add skill-dtsong-my-claude-setup-pipeline-design`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [dtsong](https://agentstack.voostack.com/s/dtsong)
- **Installs:** 0
- **Category:** [Data & Analytics](https://agentstack.voostack.com/c/data-and-analytics)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [dtsong](https://github.com/dtsong)
- **Source:** https://github.com/dtsong/my-claude-setup/tree/main/skills/council/alchemist/pipeline-design

## Install

```sh
agentstack add skill-dtsong-my-claude-setup-pipeline-design
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

# Pipeline Design

## Purpose

Design data pipelines that reliably move, transform, and deliver data from source systems to consumption layers. Covers ETL vs ELT pattern selection, orchestration tool choice, batch vs streaming trade-offs, idempotency guarantees, data quality checkpoints, and lineage tracking.

## Scope Constraints

Reads pipeline configurations, DAG definitions, orchestration manifests, and infrastructure specs for analysis. Does not execute pipelines, deploy infrastructure, or modify production configurations.

## Inputs

- Source systems and their data formats (databases, APIs, event streams, files)
- Destination systems (warehouse, lake, feature store, BI tool)
- Data volume and velocity (rows/day, events/second, payload size)
- Freshness requirements (real-time, near-real-time, hourly, daily)
- Existing infrastructure (cloud provider, orchestration tools, current pipelines)
- Team size and expertise (SQL-heavy? Python-heavy? Platform team available?)

## Input Sanitization

No user-provided values are used in commands or file paths. All inputs are treated as read-only analysis targets.

## Procedure

### Progress Checklist
- [ ] Step 1: Map source-to-destination flows
- [ ] Step 2: Choose ETL vs ELT pattern
- [ ] Step 3: Select batch vs streaming
- [ ] Step 4: Design for idempotency
- [ ] Step 5: Define data quality checkpoints
- [ ] Step 6: Plan lineage and observability
- [ ] Step 7: Select orchestration tool

### Step 1: Map Source-to-Destination Flows

Document every data flow:
- Source system, extraction method (CDC, API poll, event subscription, file drop)
- Transformation requirements (cleaning, joining, aggregating, enriching)
- Destination system and loading pattern (append, upsert, full refresh)
- Data volume per flow (rows/batch, events/second)

Produce a flow diagram showing all sources, transformations, and destinations.

### Step 2: Choose ETL vs ELT Pattern

Evaluate the trade-offs:
- **ETL (Extract-Transform-Load):** Transform before loading. Best when: destination has limited compute, transformations reduce data volume significantly, or sensitive data must be filtered before landing.
- **ELT (Extract-Load-Transform):** Load raw first, transform in-warehouse. Best when: destination has powerful compute (BigQuery, Snowflake, Databricks), you want raw data preserved for auditability, or transformation logic changes frequently.

Document the chosen pattern per flow and the reasoning.

### Step 3: Select Batch vs Streaming

For each flow, determine the processing mode:
- **Batch:** Scheduled intervals (hourly, daily). Simple, cost-effective, good tooling. Best when freshness SLA is minutes-to-hours.
- **Micro-batch:** Frequent small batches (every 1-5 minutes). Compromise between batch simplicity and near-real-time freshness.
- **Streaming:** Continuous processing (Kafka, Kinesis, Flink). Best when freshness SLA is seconds and data arrives as an event stream.

Document latency requirements, cost implications, and complexity trade-offs for the chosen mode.

### Step 4: Design for Idempotency

Ensure every pipeline step is safe to re-run:
- Use merge/upsert patterns instead of blind inserts
- Partition data by time to enable clean backfills without full re-processing
- Use deterministic IDs (content-based hashing) or natural keys for deduplication
- Document the idempotency strategy for each pipeline step: "If this step runs twice for the same input, what happens?"

### Step 5: Define Data Quality Checkpoints

Insert quality gates between pipeline stages:
- **Source validation:** Schema conformance, null rates, row count thresholds
- **Transformation validation:** Referential integrity, business rule assertions, duplicate detection
- **Destination validation:** Row count reconciliation (source vs destination), freshness checks, metric drift alerts

For each checkpoint, define: what is checked, what threshold triggers a failure, and what happens on failure (halt pipeline, alert, quarantine bad records).

### Step 6: Plan Lineage and Observability

Design data lineage tracking:
- Column-level lineage from source to destination
- Transformation dependency graph (which tables feed which downstream models)
- Pipeline execution metadata (start time, end time, rows processed, errors)
- Alerting on SLA breaches, data quality failures, and pipeline errors

Specify tooling: dbt lineage, OpenLineage, Datahub, or custom metadata tables.

### Step 7: Select Orchestration Tool

Choose the orchestration layer based on team and requirements:
- **dbt** — SQL-centric transformations, built-in lineage, great for ELT. Best for analytics engineering teams.
- **Airflow** — General-purpose DAG orchestration, large ecosystem, battle-tested. Best for complex multi-system pipelines.
- **Dagster** — Software-defined assets, strong typing, built-in observability. Best for teams wanting modern DX and asset-centric thinking.
- **Prefect** — Python-native, dynamic workflows, lightweight. Best for Python-heavy teams with simpler orchestration needs.

Document the choice, alternatives considered, and migration path if the team outgrows the tool.

> **Compaction resilience**: If context was lost during a long session, re-read the Inputs section to reconstruct what system is being analyzed, check the Progress Checklist for completed steps, then resume from the earliest incomplete step.

## Handoff

- Hand off to schema-evaluation if the pipeline design reveals data modeling or warehouse schema concerns.
- Hand off to warden/incident-analysis if pipeline failures indicate systemic reliability or incident response needs.

## Output Format

```markdown
# Pipeline Design: [Project/Domain Name]

## Flow Diagram

```
[ASCII diagram showing sources → transformations → destinations]
```

## Flow Inventory

| Flow | Source | Extraction | Transform | Load Pattern | Volume | Freshness SLA |
|------|--------|-----------|-----------|-------------|--------|---------------|
| ...  | ...    | ...       | ...       | ...         | ...    | ...           |

## Architecture Decisions

| Decision | Chosen | Alternatives | Rationale |
|----------|--------|-------------|-----------|
| ETL vs ELT | ... | ... | ... |
| Batch vs Streaming | ... | ... | ... |
| Orchestration tool | ... | ... | ... |

## Idempotency Strategy

| Pipeline Step | Idempotency Method | Re-run Behavior |
|---------------|-------------------|-----------------|
| ...           | ...               | ...             |

## Data Quality Checkpoints

| Stage | Check | Threshold | On Failure |
|-------|-------|-----------|------------|
| Source | ... | ... | ... |
| Transform | ... | ... | ... |
| Destination | ... | ... | ... |

## Lineage and Observability

| Capability | Tool/Method | Coverage |
|-----------|-------------|----------|
| Column lineage | ... | ... |
| Pipeline metrics | ... | ... |
| Alerting | ... | ... |

## Orchestration Design

| DAG/Pipeline | Schedule | Dependencies | SLA |
|-------------|----------|-------------|-----|
| ...         | ...      | ...         | ... |
```

## Quality Checks

- [ ] Every source-to-destination flow is documented with volume and freshness SLA
- [ ] ETL vs ELT decision is justified per flow, not assumed globally
- [ ] Batch vs streaming choice is driven by freshness requirements, not preference
- [ ] Every pipeline step has an idempotency strategy documented
- [ ] Data quality checkpoints exist between each pipeline stage
- [ ] Failure handling is specified for each checkpoint (halt, alert, quarantine)
- [ ] Lineage tracking covers column-level provenance for critical fields
- [ ] Orchestration tool selection considers team expertise and existing infrastructure
- [ ] Backfill strategy is documented — how to reprocess historical data safely

## Evolution Notes

## Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

- **Author:** [dtsong](https://github.com/dtsong)
- **Source:** [dtsong/my-claude-setup](https://github.com/dtsong/my-claude-setup)
- **License:** MIT

Install and usage instructions live in the source repository linked above.

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-dtsong-my-claude-setup-pipeline-design
- Seller: https://agentstack.voostack.com/s/dtsong
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.