Data Pipeline Specification Template

Batch or streaming pipeline: sources, lineage, schedule, mapping, quality checks, backfills, volumes, runbook

AI & DataFulldata pipeline spec mathUpdated Oct 20, 2026

How to use: Use when designing or documenting a production data pipeline. Fill purpose, sources and sinks, and the output grain first, and review them with consumers. The main pitfall is a pipeline that cannot be re-run idempotently, which makes every backfill risky.

Preview

Data Pipeline Specification: [Pipeline name]

Complete specification for a batch or streaming data pipeline: purpose, sources and sinks, lineage, schedule, extraction, transformations with a source-to-target mapping, data quality checks, idempotency and backfills, orchestration, error handling, volume and performance estimates, security and privacy, testing, operations and ownership. Replace every [bracketed] value and every row marked Example. Worked examples use a daily pipeline that builds an orders fact table.

Document control

Field	Value
Document ID	[DPS-PIPELINE-001]
Pipeline name	[Name]
Repository	[Repository URL and path]
Version	[0.1]
Status	[Draft / In review / Approved / In production / Retired]
Owner	[@pipeline-owner], [team]
Classification	[Internal / Confidential]
Last updated	YYYY-MM-DD
Related documents	[Data contracts for inputs and outputs], [Metric definitions], [Architecture description]

Approvals

Role	Name	Decision	Date
Data engineering lead	[@name]	[Approved / Changes requested]	YYYY-MM-DD
Main consumer representative	[@name]	[Decision]	YYYY-MM-DD
Data platform / SRE	[@name]	[Decision]	YYYY-MM-DD
Privacy or security (if personal data)	[@name]	[Decision]	YYYY-MM-DD

Revision history

Version	Date	Author	Change summary
0.1	YYYY-MM-DD	[@name]	First draft
0.2	YYYY-MM-DD	[@name]	Example: Switched order extraction to change data capture after volume review
1.0	YYYY-MM-DD	[@name]	Example: Approved for production

Purpose and scope
Sources and sinks
Lineage
Schedule and triggers
Extraction
Transformations
Data quality checks
Idempotency and backfills
Orchestration
Error handling and alerting
Performance and volume estimates
Security and privacy
Testing
Deployment and change management
Operations runbook
Ownership and support

1. Purpose and scope

1.1 Purpose

[Example: "Builds analytics.fct_orders, one row per order with customer, channel and net revenue in EUR, refreshed daily by 06:00 UTC for finance reporting and the demand forecast."]

1.2 Scope

In scope	Out of scope
Example: Orders from the order service and marketplace feed	Example: Payments and refunds (separate pipeline)
Example: Currency conversion to EUR at daily reference rate	Example: Real-time dashboards
[Item]	[Item]

1.3 Consumers and requirements

Consumer	Need	Deadline	Contract
Example: Finance revenue dashboard	Complete previous day	06:00 UTC	dc-fct-orders-001
Example: Demand forecast training	3 years history, daily	07:00 UTC	dc-fct-orders-001
[Consumer]	[Need]	[Time]	[Contract]

2. Sources and sinks

2.1 Sources

ID	Source	System	Format / access	Volume per day	Owner	Contract
S-01	Example: Orders	Order service database	Change data capture to object storage, Parquet	300 000 rows	Commerce	dc-orders-001
S-02	Example: Marketplace orders	Partner SFTP	CSV, daily file by 02:00 UTC	40 000 rows	Partnerships	Partner agreement 7.3
S-03	Example: Exchange rates	Reference data API	JSON, daily	30 rows	Treasury	dc-fx-002
S-04	Example: Customers	Warehouse table `core.customers`	SQL	2 M rows (full)	CRM team	dc-customers-003
[S-nn]	[Source]	[System]	[Format]	[Volume]	[Owner]	[Contract]

2.2 Sinks

ID	Sink	Format	Write mode	Partitioning	Retention
T-01	Example: `analytics.fct_orders`	Warehouse table	Overwrite partition by `order_date`	Daily on `order_date`	7 years
T-02	Example: `analytics.fct_orders_rejects`	Warehouse table	Append	Daily on `run_date`	90 days
[T-nn]	[Sink]	[Format]	[Mode]	[Partitioning]	[Retention]

3. Lineage

Data flows from four sources through staging and an intermediate join to the fact table and a rejects table.

4. Schedule and triggers

Item	Value
Schedule	Example: Daily at 03:00 UTC
Upstream dependencies	Example: Marketplace file present; CDC watermark past 00:00 UTC; FX rates for the date
Wait policy	Example: Sensor waits up to 90 minutes, then runs without marketplace data and flags the partition provisional
Completion deadline	Example: 05:30 UTC (30 min buffer before consumer deadline)
Ad-hoc triggers	Example: Manual run for a date range (backfill), see 8
Time zone	All dates and windows in UTC

5. Extraction

Source	Method	Watermark / window	Late data handling	Limits
S-01	Example: Incremental from CDC files	`commit_ts` greater than last watermark	Reprocess previous 2 days each run	None
S-02	Example: Full daily file	File date	Late file triggers re-run for that date	1 file per day
S-03	Example: API call for date	Rate date	Retry 3 times; use previous day rate and flag	100 requests per minute
S-04	Example: Snapshot join	As of run time	Not applicable	None
[S-nn]	[Method]	[Watermark]	[Handling]	[Limits]

6. Transformations

6.1 Steps

Step	Input	Output	Logic
1	S-01	`stg_orders`	Example: Keep latest version of each `order_id` by `commit_ts`; cast types; rename columns
2	S-02	`stg_marketplace_orders`	Example: Parse CSV; map partner status codes to internal statuses
3	`stg_orders`, `stg_marketplace_orders`	`int_orders_unioned`	Example: Union; deduplicate on `order_id`; route invalid rows to rejects
4	`int_orders_unioned`, `stg_fx_rates`, S-04	`fct_orders`	Example: Convert to EUR; join customer segment; derive `order_date`

6.2 Source-to-target mapping

Target column	Source(s)	Transformation rule	Null handling
`order_id`	S-01 `id`; S-02 `partner_order_ref`	Marketplace IDs prefixed `MKT-`	Reject row if null
`order_date`	`order_ts`	Date part of `order_ts` in UTC	Reject row if null
`channel`	S-01 `channel`; constant for S-02	S-02 rows get `marketplace`	`unknown` before 2022
`net_amount_eur`	`net_amount`, `currency`, FX rate	`net_amount / rate` rounded half-even to 2 decimals	Reject if rate missing after fallback
`customer_segment`	S-04 `segment`	Segment as of run date	`unassigned`
[column]	[source]	[rule]	[handling]

7. Data quality checks

ID	Stage	Check	Threshold	Severity
Q-01	Input S-02	File row count matches trailer record	Exact	Blocking
Q-02	Staging	`order_id` unique in `int_orders_unioned`	100%	Blocking
Q-03	Output	`net_amount_eur` not null	100%	Blocking
Q-04	Output	Daily total within 3 standard deviations of 28-day mean for that weekday	Within range	Warning
Q-05	Output	Rejects below 0.5% of input rows	at most 0.5%	Blocking above 2%, warning above 0.5%
Q-06	Reconciliation	Total EUR matches finance ledger within tolerance	at most 0.1%	Warning
[Q-nn]	[Stage]	[Check]	[Threshold]	[Blocking / Warning]

8. Idempotency and backfills

Topic	Design
Idempotency	Example: Each run computes complete partitions for its dates and overwrites them atomically; no appends to the fact table
Determinism	Example: FX rate chosen by date, not by run time; customer segment taken from dated snapshot
Backfill command	Example: `pipeline run fct_orders --start 2024-01-01 --end 2024-12-31 --parallel 4`
Backfill limits	Example: At most 31 dates per batch; outside 03:00 to 06:00 UTC window
Consumer notice	Example: Announce backfills over 7 days in [channel] one day ahead
Reprocessing window	Example: Each daily run also recomputes the previous 2 days for late data

9. Orchestration

The task graph runs staging in parallel, then the join, then checks before the atomic publish.

Setting	Value
Orchestrator	[Tool name and version]
Retries	Example: 3 retries with exponential back-off from 5 minutes for transient errors
Timeouts	Example: 45 minutes per task; 150 minutes per run
Concurrency	Example: One run per date; at most 4 dates in parallel
Compute	Example: Warehouse size M for build; S for checks

10. Error handling and alerting

Failure	Pipeline behaviour	Alert	Recipient
Source file missing after wait	Run without it; partition marked provisional	Warning	Pipeline owner channel
Transient error (network, lock)	Retry per section 9	Only if retries exhausted	On-call
Blocking quality check fails	Do not publish; previous data stays	Page	On-call; consumers notified
Deadline at risk (not finished by 05:00 UTC)	Continue	Warning	On-call; finance contact
Schema change in source	Fail at staging with clear error	Page	On-call; source owner
[Failure]	[Behaviour]	[Alert]	[Recipient]

11. Performance and volume estimates

Input	Symbol	Value
Rows per day (all sources)	$R$	Example: 340 000
Average row size after compression	$b$	Example: 120 bytes
Growth per year	$g$	Example: 25%
Retention	$Y$	Example: 7 years
Throughput of build step	$\theta$	Example: 2 000 000 rows per minute

Storage for the retention period, with yearly growth:

S \approx 365 \cdot R \cdot b \sum_{y=0}^{Y-1} (1+g)^{y} = 365 \times 340\,000 \times 120 \ \text{B} \times 15.07 \approx 225 \ \text{GB}

Daily build time including a 3-day reprocessing window:

T_{build} \approx \frac{3R}{\theta} = \frac{1\,020\,000}{2\,000\,000} \approx 0.5 \ \text{min}

Measure	Estimate	Limit	Headroom
Daily run time end to end	Example: 25 minutes	150 minutes	6x
Backfill of one year	Example: 4 hours at parallel 4	Outside business hours	OK
Storage at 7 years	Example: about 225 GB	[Budget]	[value]
Compute cost per month	[value]	[Budget]	[value]

12. Security and privacy

Topic	Design
Credentials	Example: Service identity per pipeline; secrets in [secret manager]; rotated every 90 days
Least privilege	Example: Read on sources, write only on T-01 and T-02
Personal data	Example: Only pseudonymous `customer_id`; no names, emails or addresses copied
Data in transit and at rest	Example: TLS for SFTP and API; warehouse encryption at rest
Deletion requests	Example: Customer deletion propagates via `core.customers` tombstones; fact rows keep order but null `customer_id`
Logging	Example: No row-level data in logs

13. Testing

Level	What is tested	Data	When
Unit	Example: Status mapping, currency conversion, rounding	Small fixtures	Every commit
Integration	Example: Full DAG on a sampled day in a test schema	1% sample, masked	Every pull request
Data tests	Example: Checks in section 7 against test output	Test schema	Every pull request
Regression	Example: Compare output for 7 fixed dates with production	Production snapshot copy	Before release
[Level]	[What]	[Data]	[When]

14. Deployment and change management

[Example: "Changes merged to the main branch deploy through CI to the orchestrator. Changes to output schema follow the data contract's breaking-change policy: 30 days notice and a parallel table version. Rollback redeploys the previous tag; data is restored by re-running the affected dates."]

15. Operations runbook

Situation	Steps
Run failed on a transient error	1. Check task log in [link]. 2. Clear and re-run the failed task. 3. Confirm checks pass.
Blocking quality check failed	1. Read failing check output. 2. If source issue, contact source owner from 2.1. 3. Post status in [consumer channel]. 4. Re-run date when fixed.
Marketplace file late	1. Confirm with partner contact. 2. When file arrives, run `pipeline run fct_orders --date YYYY-MM-DD`. 3. Remove provisional flag.
Need to reprocess history	Follow section 8; announce first.
[Situation]	[Steps]

16. Ownership and support

Role	Name / team	Contact
Pipeline owner	[@name], [team]	[channel]
On-call	[Rota name]	[Paging tool]
Source owners	See 2.1	[Links]
Main consumer contacts	See 1.3	[Links]
Escalation	[@data-engineering-lead]	[contact]

Standard and licence note

Based on common industry practice for data engineering pipeline specifications. Original text written for this collection; example numbers are illustrative. Template licence: MIT.

Related templates

AI & Data