Skip to content

skillData and AI

Data Pipeline Design

Designs idempotent, testable batch pipelines with safe backfills and data quality checks. Use it when planning or reviewing a scheduled data job.

npx workspacetemplates add data-pipeline-design --tool claude-code

Writes 1 file to .claude/skills/data-pipeline-design/

.claude/skills/data-pipeline-design/SKILL.md28 lines · 1.7 KBFile
---
name: data-pipeline-design
description: Designs idempotent, testable batch pipelines with safe backfills and data quality checks. Use it when planning or reviewing a scheduled data job.
---

# Data pipeline design

Design for the rerun. Assume every job will fail midway and be started again.

## Steps

1. Define the contract: source, destination, schedule, the grain of the output, and who consumes it. Write the output schema with types and a unique key.
2. Partition by processing window (usually a date). Each run takes the window as a parameter and never reads "now" or "latest" implicitly.
3. Make writes idempotent: overwrite the partition, or `MERGE`/upsert on the key. Write to a staging location, validate, then swap or publish atomically. Never append blindly.
4. Separate stages: extract (raw, immutable copy), transform (pure functions of raw data), load. Keep transform code free of I/O so it can be unit tested with a tiny fixture.
5. Add quality checks between stages: row count against the source within a tolerance, non-null keys, unique keys, value ranges, and freshness. A failed check stops the run and alerts; it does not warn and continue.
6. Plan late and changed data: reprocess a trailing window (for example 3 days) or key on event time with a watermark.
7. Document the backfill: the command, the date range parameters, the expected runtime, and how to verify afterwards.

## Rules

- No hidden state: no files in local temp directories, no global counters.
- Secrets come from the environment, never from the repo.
- Prefer boring tools already in the stack over a new orchestrator.

## Report

A stage diagram in text, the key and partition choices, the checks with thresholds, the failure modes considered, and the backfill steps.

What it does

A pipeline that cannot be rerun safely will eventually double-count a day of revenue. This skill designs batch jobs around three properties: the same input always yields the same output, any date range can be reprocessed, and bad data is stopped before it reaches consumers.

The agent produces a design with named stages, partition keys, checks and a backfill procedure, or reviews an existing job against those properties.

Example

you › Design the nightly job that loads orders from the shop API into the warehouse.

agent › Three stages: extract writes raw JSON per order date to raw/orders/dt=YYYY-MM-DD, transform builds a typed table, load MERGEs on order_id. Reprocesses the last 3 days each run to catch edits. Checks: row count within 2% of the API total, order_id unique, amount at least 0. Backfill: run.py --from 2026-08-01 --to 2026-08-31, safe to repeat.

Questions

Is this tied to Airflow or any other scheduler?
No. The design is about the job itself. It maps cleanly onto cron, Airflow, Dagster or a cloud scheduler, and says which parameters each needs.
Why stop on a failed check instead of warning?
Warnings get ignored, and bad rows then spread to every table built on top. Stopping early is cheaper than cleaning up downstream.
  • dbt Modeling

    SkillData and AI

    Free
  • LLM Cost and Latency

    SkillData and AI

    Free