Skip to content This library is free, offered as dana. If it helps your work, donate here →
penguin tree ai
← back to libraryDonate here

This is one of 3,622 free prompts in the penguin tree ai library. Sign up once and the whole library opens, with new prompts by email.

open the library →

Data pipeline requirements builder

Translates a business use case into a complete, engineering-ready data pipeline requirements document covering source systems, extraction methods, transformation logic, output schema, latency and freshness SLAs, data…

download .md

Purpose

Convert a business use case into a structured, unambiguous pipeline requirements document that a data engineer can estimate and build against — specifying every source, transformation, output field, latency SLA, and quality check so the pipeline gets built right the first time instead of through three rounds of Slack clarification.

When to Use

Invoke this skill when the user asks to:

  • Scope or spec a new data pipeline for an analytics or reporting need
  • Turn a stakeholder request ("I need daily churn numbers") into technical requirements
  • Write a pipeline requirements doc or handoff spec for the data engineering team
  • Define the source-to-target mapping and transformation logic for a new dataset
  • Document latency, freshness, and data quality expectations before a build kicks off
  • Define an output schema and grain for a new table, view, or dashboard feed

Inputs Required

Before starting, collect:

  • Business use case: What decision or workflow this data supports, and who consumes it
  • Consumption target: Where the output lands — dashboard, warehouse table, reverse-ETL destination, API, ML feature store
  • Source systems: The systems, apps, or databases where the raw data lives (e.g., Salesforce, Postgres app DB, Stripe, GA4, event stream)
  • Key metrics/entities: The metrics, dimensions, and grain the output must support
  • Freshness expectation: How current the data needs to be (real-time, hourly, daily, weekly)
  • Historical scope: Whether backfill is needed and how far back
  • Known constraints: PII/compliance rules, source API limits, existing models to reuse, tooling in the stack (dbt, Airflow, Fivetran, etc.)

If freshness or historical scope is missing, default to daily refresh with a full historical backfill and flag both assumptions for confirmation.

Instructions

Step 1: Restate the Use Case and Define Success

Open with a 2–3 sentence restatement of the business use case in plain language, then define the pipeline's job in one measurable sentence (e.g., "Deliver a daily-refreshed table of per-account MRR and churn status, grained one row per account per day, available in the warehouse by 6am"). State who consumes it and the decision it drives. This anchors every downstream requirement.

Step 2: Map Source Systems and Extraction

For each source system, specify:

  • System name and type: SaaS API / application database / event stream / flat file / third-party
  • Tables/objects/endpoints needed: The specific entities to pull
  • Extraction method: Full snapshot / incremental (with the incremental key — updated_at, cursor, log-based CDC) / append-only event ingest
  • Extraction cadence: How often the source is polled or synced
  • Known constraints: API rate limits, pagination, soft-deletes, schema drift risk, historical availability
  • Join keys: The natural or surrogate keys used to relate this source to others

Step 3: Define Transformation Logic

Specify the transformations in ordered, testable stages. Use a staging → intermediate → mart framing where relevant:

  • Cleaning/standardization: Type casting, deduplication rule (which record wins), timezone normalization, null handling
  • Business logic: Named metrics with explicit definitions (e.g., "Active account = has ≥1 successful charge in trailing 30 days"). Every derived field gets a one-line calculation rule — no ambiguous terms like "revenue" without a formula.
  • Joins and grain changes: Each join with its key, join type (inner/left), and fan-out risk noted
  • Aggregations: Group-by dimensions, aggregation functions, and the resulting grain
  • Edge cases: How to handle late-arriving data, backfilled corrections, and records with missing dimensions

Step 4: Specify the Output Schema

Produce a column-level output schema as a table with these columns: Field name | Data type | Description | Nullable? | Source or calculation. State the grain explicitly (one row per what). Name the primary key and any surrogate keys. Note partitioning/clustering recommendation if the target warehouse supports it.

Step 5: Define Latency and Freshness SLAs

Translate the freshness expectation into concrete requirements:

  • Refresh cadence: Scheduled interval or trigger
  • Freshness SLA: Maximum acceptable data age at consumption time (e.g., "data no older than 24h")
  • Availability deadline: Wall-clock time the output must be ready (e.g., "by 6am ET")
  • Latency budget: Rough end-to-end runtime expectation, flagging any source that could blow the budget
  • Failure/recovery expectation: What happens on a failed run — retry, alert, hold stale data

Step 6: Define Data Quality Checks

Specify testable checks the pipeline must enforce, grouped by type:

  • Schema tests: Not-null on key fields, uniqueness on the primary key, accepted values on enums
  • Volume tests: Row-count within expected range, no unexpected drop-to-zero, freshness/recency check on the max timestamp
  • Referential tests: Foreign keys resolve, no orphaned join rows
  • Business-logic tests: Metric sanity bounds (e.g., MRR ≥ 0), reconciliation against a source-of-truth total
  • Failure severity: For each check, mark warn (log and proceed) or error (halt the pipeline)

Step 7: Note Dependencies and Open Questions

List upstream/downstream dependencies (models this reuses, jobs this must run after, consumers that break if it changes). Close with a short numbered list of open questions the data engineer or stakeholder needs to resolve before build — these are the ambiguities you could not safely default.

Output Format

  • Structure: Structured requirements document with numbered sections; output schema as a table; quality checks as a labeled list with severity flags
  • Length: 900–1,400 words
  • Format: Technical spec — headers, tables, and tight bullets; not narrative prose
  • Sections: Use Case & Success Definition → Source Systems & Extraction → Transformation Logic → Output Schema → Latency & Freshness SLAs → Data Quality Checks → Dependencies & Open Questions
  • Tone: Analytics engineer writing a handoff spec — precise, unambiguous, formula-driven; every metric has a definition, every field has a source

Quality Checklist

Before delivering output, verify:

  • [ ] Every source system has an extraction method AND an incremental key or snapshot decision stated
  • [ ] Every derived metric has an explicit calculation rule — no undefined terms like "revenue" or "active"
  • [ ] The output schema names the grain and primary key, and every field has a data type and source/calculation
  • [ ] Latency section states a concrete freshness SLA and an availability deadline — not just "daily"
  • [ ] Each data quality check is marked warn or error
  • [ ] Open questions list contains only genuine ambiguities — anything safely defaulted is defaulted and flagged, not deferred

Notes

  • The single most common cause of pipeline rework is undefined metrics — spend the effort in Step 3 making every calculation explicit and testable.
  • Grain is the highest-leverage decision in the whole spec; if the grain is wrong, every downstream join and aggregation is wrong. State it early and defend it.
  • Prefer incremental extraction with a clear cursor over full snapshots wherever the source supports it — but flag soft-delete handling, since incremental loads silently miss deletes.
  • When a source constraint (API rate limit, missing history) conflicts with the stated freshness or backfill need, surface the tradeoff explicitly rather than assuming it away.

How to use this skill

This file is a Claude Code skill in Anthropic's standard format — markdown with YAML frontmatter at the top.

Installing in Claude Code:

  • ~/.claude/skills/<folder-name>/SKILL.md — auto-loads in every session anywhere on your machine (recommended for skills you'll use across multiple projects)
  • <your-project>/.claude/skills/<folder-name>/SKILL.md — auto-loads only when working in that project (recommended for project-specific skills)

Restart Claude Code or open a new session to register.

Using in Claude.ai, ChatGPT, Gemini, or other AI tools:

  1. Drag this file into a new chat, or copy and paste its contents
  2. Send your task or request as your next message — the AI will apply the skill where it fits

For Claude Code skill details: https://docs.anthropic.com/en/docs/claude-code/skills