data-pipeline-design

name	data-pipeline-design
description	Design batch, streaming, and CDC pipelines on a lakehouse with dbt and OpenLineage. Use when ingesting new sources, replacing nightly ETL, or building a real-time analytics surface.

Data Pipeline Design

Land raw, model in the lakehouse, expose via marts. Choose batch vs streaming by SLA, not by fashion. Track every hop with OpenLineage so failures and PII flows are inspectable.

Stack Baseline (2026)

Concern	Recommended
Ingest - batch	Airbyte 1.0, Fivetran, dlt
Ingest - CDC	Debezium 2.6, Estuary Flow, AWS DMS
Ingest - streaming	Kafka 3.7 + Connect, Redpanda, Pulsar
Storage	Iceberg + Polaris REST catalog, Delta + Unity
Compute - batch	Spark 3.5, Trino 446, DuckDB 1.0
Compute - streaming	Flink 2.0, Materialize, Kafka Streams
Transform	dbt 1.8, SQLMesh
Orchestration	Dagster 1.8, Airflow 2.9, Prefect 3
Quality	Great Expectations, Soda Core, dbt tests
Lineage / catalog	OpenLineage 1.x, OpenMetadata, DataHub, Unity
Contracts	Data Contract CLI, PactFlow

When to Use

• Adding a new source-to-mart path.
• SLA tightens from daily to minutes or seconds.
• Replacing point-to-point integrations with a lakehouse spine.
• Need lineage and PII classification across the chain.

Prerequisites

• Source system access with CDC or full-load capability.
• Lakehouse catalog (Polaris/Unity) and object store buckets per zone.
• Schema registry and data contract template.
• SLA per dataset: freshness, completeness, accuracy.

Instructions

1. Pick the architecture per dataset

flowchart LR
    S[(Source DB)] -->|CDC Debezium| K[Kafka 3.7]
    S2[(SaaS API)] -->|Airbyte batch| B[Bronze Iceberg]
    K -->|Flink 2.0| B
    B -->|dbt| Si[Silver Iceberg]
    Si -->|dbt| G[Gold marts]
    G --> BI[BI / ClickHouse]
    G --> ML[Feature Store]
    B & Si & G -.-> OL[OpenLineage]

Bronze = raw + immutable. Silver = conformed. Gold = business marts.

2. Choose batch vs streaming

SLA	Choice
> 1 hour	Batch with Airbyte + dbt incremental
1-60 min	Micro-batch (dbt incremental every N min)
< 1 min	Streaming with Flink/Materialize
Sub-second	Materialized views in Materialize, ClickHouse

3. Land CDC into Iceberg

# Debezium connector
name: pg-orders
config:
  connector.class: io.debezium.connector.postgresql.PostgresConnector
  plugin.name: pgoutput
  database.hostname: pg-prod
  database.dbname: orders
  table.include.list: public.order,public.order_line
  topic.prefix: cdc.orders
  schema.history.internal.kafka.topic: schema-history.orders
  transforms: outbox

4. Model with dbt incremental

{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='append_new_columns',
    incremental_strategy='merge'
) }}

select
  order_id,
  customer_id,
  status,
  total_cents,
  updated_at
from {{ source('bronze','orders_cdc') }}
{% if is_incremental() %}
  where updated_at > (select coalesce(max(updated_at), '1970-01-01') from {{ this }})
{% endif %}

5. Enforce data contracts and quality

# data-contract.yaml
dataContractSpecification: 1.1.0
id: orders.gold.fact_order
info: { owner: orders-team, version: 2.3.0 }
servers: { production: { type: iceberg, catalog: polaris } }
models:
  fact_order:
    fields:
      order_id:    { type: string, required: true, unique: true }
      total_cents: { type: long,   required: true, minimum: 0 }
      placed_at:   { type: timestamp, required: true }
servicelevels:
  freshness: { threshold: 15m }
  completeness: { threshold: 99.9 }

6. Emit lineage with OpenLineage

Configure dbt and Spark/Flink to emit OpenLineage events to Marquez, OpenMetadata, or DataHub. Validate that every gold table traces back to a source.

7. Operate it

• Idempotent jobs keyed by partition.
• Backfill via parameterized runs, never manual SQL.
• Alert on freshness, row-count anomaly, schema drift, and SLA burn.
• Cost guardrails: file compaction (Iceberg rewrite_data_files), partition evolution.

Common Pitfalls

Pitfall	Why it hurts	Mitigation
Streaming for batch SLAs	10x cost, ops burden	Use micro-batch dbt
Tiny files on Iceberg	Query latency explodes	Schedule compaction jobs
No idempotency key	Duplicates on retry	Merge by unique_key
Hidden upstream schema drift	Silent silver corruption	Schema registry + contract tests
One giant DAG	Slow recovery, blast radius	Split per domain
No lineage	Can't answer GDPR DSAR	OpenLineage from day 1
Direct source -> BI	Ties analytics to OLTP	Always via lakehouse

Output Format

Deliver: source inventory, bronze/silver/gold model list, ingestion mode per source, dbt project, data contracts, orchestration DAG, OpenLineage config, SLO + alert spec, and a backfill runbook.

Authoritative References

• Apache Iceberg spec - https://iceberg.apache.org/spec/
• dbt incremental models - https://docs.getdbt.com/docs/build/incremental-models
• Debezium docs - https://debezium.io/documentation/
• OpenLineage spec - https://openlineage.io/docs/
• Data Contract Specification - https://datacontract.com/
• Flink 2.0 release notes - https://flink.apache.org/news/