Data Pipeline Patterns

What it is

Data pipeline patterns are the recurring designs used to reliably transform raw data into queryable, trustworthy datasets at scale. Unlike a single-purpose ETL script, well-architected data pipelines are idempotent (safe to re-run), incremental (only process new data), observable (emit quality metrics and lineage), and orchestrated (dependencies are tracked, failures are handled, and retries are controlled). These patterns apply whether the pipeline is batch (nightly Spark job), micro-batch (Structured Streaming), or real-time (Flink).

Why it exists

Data pipelines fail: upstream sources change schemas, API calls return partial data, clusters time out mid-job. Without explicit patterns for idempotency, incremental processing, and quality validation, teams end up with pipelines that corrupt data when re-run, fail silently when quality degrades, and produce incorrect results when processing is not idempotent. Formalising these patterns as design standards dramatically reduces the frequency and impact of pipeline failures.

Typical architecture

MEDALLION ARCHITECTURE (Delta Lake / Databricks standard)
──────────────────────────────────────────────────────────
  Source Systems (APIs, DBs, event streams)
            ↓
    ┌──────────────────────────────────┐
    │  BRONZE (Raw Ingestion Layer)    │
    │  - Exact copy of source data     │
    │  - No transformation             │
    │  - Schema: source schema + meta  │
    │    (ingest_ts, source_file, etc) │
    │  - Append-only, never updated    │
    │  - Retention: unlimited          │
    └──────────────────────────────────┘
            ↓ (validate + clean)
    ┌──────────────────────────────────┐
    │  SILVER (Validated / Cleaned)    │
    │  - Deduplicated                  │
    │  - Nulls handled                 │
    │  - Types cast, parsed correctly  │
    │  - Business keys resolved        │
    │  - Conforms to schema contract   │
    │  - SCD Type 2 for slowly-        │
    │    changing dimensions           │
    └──────────────────────────────────┘
            ↓ (aggregate / enrich)
    ┌──────────────────────────────────┐
    │  GOLD (Business-Level / Serving) │
    │  - Aggregated fact tables        │
    │  - Domain-specific metrics       │
    │  - Optimised for BI / reporting  │
    │  - Materialised views / cubes    │
    └──────────────────────────────────┘

IDEMPOTENT PIPELINES
─────────────────────
  Idempotency: running the pipeline N times produces the same result as
  running it once. Required for safe retries.

  Techniques:
  OVERWRITE by partition:
    spark.write.mode("overwrite")
         .partitionBy("date")
         .parquet("s3://silver/events")
    Re-running for 2024-11-15 overwrites only that partition,
    not the entire table.

  UPSERT (MERGE INTO) with Delta Lake:
    MERGE INTO silver.events AS target
    USING bronze.events AS source
    ON target.event_id = source.event_id
    WHEN MATCHED THEN UPDATE SET *
    WHEN NOT MATCHED THEN INSERT *
    Idempotent: re-processing same batch updates existing rows,
    does not create duplicates.

  Avoid: INSERT INTO without deduplication — leads to duplicates on retry.

INCREMENTAL PROCESSING
────────────────────────
  Only process records newer than the last successful run.

  Watermark pattern (batch):
    1. Read last_processed_timestamp from checkpoint store
       (Delta Lake table, DynamoDB, or Airflow XCom)
    2. Query source WHERE created_at > last_processed_timestamp
    3. Process records
    4. On success: write new last_processed_timestamp to checkpoint

  Delta Lake Change Data Feed (CDF):
    SHOW TBLPROPERTIES silver.orders  -- check delta.enableChangeDataFeed
    df = spark.read.format("delta").option("readChangeFeed", "true")
                   .option("startingVersion", last_checkpoint_version)
                   .table("silver.orders")
    Returns only changed rows since checkpoint version.

ORCHESTRATION COMPARISON
──────────────────────────
  Apache Airflow:
  - Most widely adopted; Python DAGs; rich operator ecosystem
  - Scheduler can become a bottleneck at very high DAG count
  - Managed: MWAA (AWS), Cloud Composer (GCP), Astronomer

  Prefect:
  - Task-based (not DAG-based); dynamic task generation at runtime
  - Better for pipelines with variable fan-out
  - Strong Python-native experience; good local development story

  Dagster:
  - Asset-based (define data assets, not just tasks)
  - First-class data lineage: knows which assets produce which data
  - Built-in data quality checks and asset cataloging
  - Best choice for teams prioritising data observability from day one

DATA QUALITY (GREAT EXPECTATIONS)
───────────────────────────────────
  Validate data between Bronze → Silver transformation:

  # great_expectations suite
  suite.expect_column_values_to_not_be_null("user_id")
  suite.expect_column_values_to_be_between("amount", 0, 1_000_000)
  suite.expect_column_pair_values_A_to_be_greater_than_B(
      "end_ts", "start_ts")
  suite.expect_table_row_count_to_be_between(min_value=10_000)

  On failure: block pipeline, alert on-call, do NOT write bad data to Silver.
  Use Data Docs to publish HTML quality reports to S3 for stakeholders.

Pros and cons

Implementation notes

Schema evolution: Bronze layer should accept schema evolution liberally (new columns added, nullable columns) to prevent ingestion from breaking when upstream adds fields. Silver enforces a strict schema contract; schema changes at Silver must go through a migration process (add column, backfill, remove old column). Use Delta Lake's schema evolution (mergeSchema) for additive changes and explicit schema migration jobs for breaking changes.

Late-arriving data: For batch pipelines processing yesterday's data, late-arriving events (records with yesterday's timestamp arriving today) may fall outside the processing window. Two approaches: (1) reprocess the previous N days on each run (idempotent overwrite); (2) use a grace period — include records up to 48 hours before the watermark. Choose based on how late data can arrive and how much reprocessing cost is acceptable.

Common failure modes

• Non-idempotent INSERT INTO on retry: When a pipeline fails after partial write and is retried, INSERT INTO duplicates records; always use MERGE or partitioned overwrite.
• No data quality gate: Bad data in Bronze (nulls in required fields, corrupted records from upstream API) flows through to Gold and corrupts business metrics before anyone notices.

Decision checklist

• Is the Bronze layer append-only with no in-place updates to raw data?
• Are writes to Silver idempotent (partitioned overwrite or MERGE, not INSERT INTO)?
• Is there an incremental processing pattern with a persisted, recoverable checkpoint?
• Are Great Expectations (or equivalent) quality checks run before data is written to Silver or Gold?
• Is there a data quality alerting pipeline (alert on failures, not just logs)?

Example use cases

• E-commerce order pipeline: Nightly Airflow DAG: (1) extract orders from RDS → Bronze S3 Parquet; (2) Great Expectations validates non-null order_id, positive amount, valid status; (3) Spark MERGE to Silver Delta (deduplication, currency conversion); (4) Spark aggregation to Gold (daily revenue by region, category).
• Event stream Medallion: Kafka → Spark Structured Streaming continuous Bronze write; 5-minute micro-batch Silver job (dedup by event_id, enrich user, filter spam); hourly Gold aggregation job.

Related patterns

• Apache Spark Architecture — Primary execution engine for Medallion architecture transformations.
• Real-Time Analytics — Gold layer often serves as input to OLAP engines (Druid, Pinot) for real-time dashboards.
• Data Governance for Analytics — Data contracts, lineage tracking, and ownership that complement these pipeline patterns.

Further reading

• Medallion Architecture — Databricks
• Great Expectations Documentation
• Dagster Documentation