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

<!--
Guidance: How to use this template
- Fill sections 1 to 3 first and review them with the consumers; most
  rework comes from a misunderstood grain or a missing source.
- Design for re-runs from day one: every run for a given date must give
  the same result (section 8). Backfills then become routine.
- Put numbers on volume and run time (section 11). They decide partitioning,
  compute size and whether the schedule is achievable.
- The runbook (section 15) is written for the on-call engineer at 3 a.m.
  who has never seen this pipeline. Test it with someone outside the team.
-->

## Document control

<!-- Guidance: Identify the pipeline, its version and the people who approved the design. -->

| 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

<!-- Guidance: Record design changes; code changes are tracked in the repository. -->

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

## Contents

<!-- Guidance: Keep aligned with the headings below. -->

1. [Purpose and scope](#1-purpose-and-scope)
2. [Sources and sinks](#2-sources-and-sinks)
3. [Lineage](#3-lineage)
4. [Schedule and triggers](#4-schedule-and-triggers)
5. [Extraction](#5-extraction)
6. [Transformations](#6-transformations)
7. [Data quality checks](#7-data-quality-checks)
8. [Idempotency and backfills](#8-idempotency-and-backfills)
9. [Orchestration](#9-orchestration)
10. [Error handling and alerting](#10-error-handling-and-alerting)
11. [Performance and volume estimates](#11-performance-and-volume-estimates)
12. [Security and privacy](#12-security-and-privacy)
13. [Testing](#13-testing)
14. [Deployment and change management](#14-deployment-and-change-management)
15. [Operations runbook](#15-operations-runbook)
16. [Ownership and support](#16-ownership-and-support)

## 1. Purpose and scope

<!-- Guidance: Why the pipeline exists, what it produces, for whom, and what it deliberately does not do. -->

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

<!-- Guidance: List every input and output with its owner, format, access method and contract. Unknown owners are a risk; record them as such. -->

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

<!-- Guidance: Show the flow from every source to every sink, including intermediate tables. Keep it small enough to read; link to the lineage tool for column level. -->

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

```mermaid
flowchart LR
  s1[("Order service CDC")] --> stg1["stg_orders"]
  s2[("Marketplace SFTP")] --> stg2["stg_marketplace_orders"]
  s3[("FX rates API")] --> stg3["stg_fx_rates"]
  s4[("core.customers")] --> int1
  stg1 --> int1["int_orders_unioned"]
  stg2 --> int1
  stg3 --> fct
  int1 --> fct["analytics.fct_orders"]
  int1 --> rej["analytics.fct_orders_rejects"]
```

## 4. Schedule and triggers

<!-- Guidance: State when the pipeline runs, what it waits for, and the latest acceptable completion time. -->

| 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

<!-- Guidance: For each source describe how data is read: full or incremental, the watermark, late data handling and limits. -->

| 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

<!-- Guidance: Describe each step and give a source-to-target mapping for the output. Every rule should be specific enough to be implemented and tested without asking the author. -->

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

<!-- Guidance: Check inputs before transforming and outputs before publishing. Each check has a severity: blocking stops the publish, warning alerts only. -->

| 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

<!-- Guidance: Explain why re-running a date gives the same result and how to reprocess history safely. -->

| 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

<!-- Guidance: Describe the tasks, dependencies, retries and resource settings in the orchestrator. -->

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

```mermaid
flowchart LR
  w1["wait_marketplace_file"] --> t2["stg_marketplace_orders"]
  w2["wait_cdc_watermark"] --> t1["stg_orders"]
  t3["stg_fx_rates"] --> t5
  t1 --> t4["int_orders_unioned"]
  t2 --> t4
  t4 --> t5["fct_orders build"]
  t5 --> q["quality checks"]
  q --> p["publish partition"]
```

| 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

<!-- Guidance: For each class of failure, say what the pipeline does and who is told. -->

| 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

<!-- Guidance: Estimate volumes and run time with formulas and inputs, and check them against the schedule window. -->

| 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

<!-- Guidance: Cover credentials, access to outputs, personal data, and retention and deletion. -->

| 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

<!-- Guidance: Describe tests at each level and the data they use. Tests run in CI on every change. -->

| 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

<!-- Guidance: How changes reach production and how consumers learn about them. -->

[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

<!-- Guidance: Step-by-step actions for the most likely incidents. Use exact commands and links. -->

| 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

<!-- Guidance: Name who owns the pipeline, who is on call, and how to escalate. -->

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