design-data-pipeline

name	design-data-pipeline
description	Use when designing, building, or reviewing a data pipeline for ingestion, transformation, or analytics
source	Maxime Beauchemin "Functional Data Engineering" (medium.com/@maximebeauchemin, 2018); dbt documentation (docs.getdbt.com); Apache Airflow documentation
tags	["data-engineering","data-pipeline","dbt","airflow","etl","elt","functional-data-engineering"]
verified	true

Design Data Pipeline

Design data pipelines using functional principles — idempotency, immutability, and declarative transformations — for reliability and maintainability.

Why This Is Best Practice

Adopted by: Airbnb (Airflow originator), Fishtown Analytics (dbt originator, now dbt Labs), Lyft, GitLab Impact: Beauchemin's functional data engineering principles, adopted by thousands of data teams, eliminate an entire class of pipeline bugs (non-idempotent transforms, mutable state) that cause silent data corruption. dbt's adoption grew from 0 to 30,000+ companies in 5 years due to its application of software engineering practices to data transformation.

Why best: Traditional ETL pipelines are stateful, brittle, and difficult to test. Functional data engineering applies software engineering principles: pure transformations (same input → same output), immutable historical data, idempotent operations (safe to re-run), and declarative SQL-based transforms that are version-controlled and testable.

Steps

1. Choose ELT over ETL — Load raw data into the warehouse first (ELT), then transform in-warehouse using SQL/dbt. ELT preserves the raw audit trail and uses the warehouse's compute efficiently.
2. Design idempotent stages — Every pipeline stage must be safely re-runnable: use INSERT OVERWRITE or CREATE OR REPLACE semantics; never INSERT APPEND without deduplication guards.
3. Partition by time — Partition tables by event date or processing date. Load and reprocess data in date partitions; never update historical rows — append corrections with a processed_at timestamp.
4. Model in layers (dbt convention) — Staging (raw → typed, renamed) → Intermediate (business logic joins) → Marts (aggregated, analytics-ready). Each layer is independently testable.
5. Add data quality tests — Test at the staging layer: not-null, unique, accepted-values, referential integrity. Use dbt tests or Great Expectations. Fail the pipeline on data quality violations.
6. Orchestrate with DAGs — Define dependencies as directed acyclic graphs (Airflow, Prefect, Dagster). No pipelines with implicit ordering — explicit dependency graphs are observable and retryable.
7. Monitor data freshness and volume — Alert on: SLA breach (table not updated within expected window), row count anomaly (>20% deviation from baseline), and schema change.

Rules

• Raw data is immutable — never overwrite source data; append new loads with ingested_at timestamps.
• Every transformation must be deterministic — the same source data must always produce the same output.
• Pipeline code is version-controlled — no notebook-only pipelines in production; promote to .sql or .py files in a repo.
• Separate ingestion, transformation, and serving layers — each can be monitored, tested, and scaled independently.

Examples

dbt model structure:

models/
  staging/
    stg_orders.sql       -- raw → typed, renamed
    stg_payments.sql
  intermediate/
    int_order_items.sql  -- join orders + line items
  marts/
    fct_orders.sql       -- fact table for analytics
    dim_customers.sql    -- dimension table

Idempotent load (BigQuery):

CREATE OR REPLACE TABLE `project.dataset.orders_2026_03_01`
AS SELECT * FROM `project.raw.orders`
WHERE DATE(created_at) = '2026-03-01';

Common Mistakes

• Append-only pipelines without deduplication — re-running on failure creates duplicate rows; always use INSERT OVERWRITE or MERGE semantics.
• No data quality tests — silent schema changes (a column renamed upstream) corrupt downstream models without failing the pipeline.
• God DAG — one Airflow DAG with 200 tasks; impossible to debug; split by domain and layer.