Why pipeline patterns matter
Data pipelines are the plumbing of analytics. Get them wrong and you face late dashboards, duplicate records, and midnight PagerDuty alerts. These seven patterns form the building blocks of reliable data platforms.
1. Batch ETL (Extract → Transform → Load)
The classic: a scheduler (Airflow, cron) triggers extraction from sources, transforms data in a staging area, and loads cleaned data into the warehouse. Simple, predictable, but introduces latency — typically 1-hour to 24-hour cycles.
Best for: Regulatory reporting, daily aggregations, cost-sensitive workloads where freshness isn't critical.
2. ELT (Extract → Load → Transform)
Load raw data into a cloud warehouse first, then transform in place using SQL (dbt, Dataform). Leverages the warehouse's compute power and avoids maintaining a separate transformation cluster.
Best for: Teams using Snowflake, BigQuery, or Databricks where compute is elastic and SQL skills are strong.
3. Streaming ingestion (CDC)
Capture changes from source databases in real time using Debezium, AWS DMS, or Fivetran and stream them to Kafka or Kinesis. Consumers (Flink, Spark Streaming) process events as they arrive.
Best for: Operational analytics, fraud detection, real-time dashboards, event-driven microservices.
Build your architecture diagram now
Datadef generates professional diagrams with AI — 2,000+ cloud icons, column-level data lineage, and an MCP server your coding agent can drive.
4. Lambda architecture
Dual pipelines: a batch layer for accurate historical data and a speed layer for real-time approximations. A serving layer merges both views. Complex to maintain but solves the latency-vs-accuracy tradeoff.
Best for: Systems that need both real-time alerts and auditable batch results (ad-tech, IoT telemetry).
5. Kappa architecture
Single streaming pipeline serves all needs — no separate batch layer. All data treated as events. Reprocessing is done by replaying the event log. Simpler than Lambda but demands a robust stream processing framework.
Best for: Event-sourced systems, teams comfortable with Kafka/Flink, and workloads where replay is acceptable.
6. Data mesh (domain-oriented)
Decentralize pipelines: each domain team owns its data products end-to-end. A central platform team provides self-serve infrastructure (compute, storage, catalog). Pipeline governance is federated.
Best for: Large organizations (100+ engineers) where centralized data teams are bottlenecks.
7. Reverse ETL
Push transformed data from the warehouse back into operational tools — Salesforce, Marketo, Zendesk. Tools like Census, Hightouch, and Polytomic orchestrate the sync.
Best for: Product-led growth companies that need warehouse-powered personalization in SaaS tools.
Choosing the right pattern
Most platforms combine multiple patterns. You might run ELT for batch analytics, CDC for real-time dashboards, and reverse ETL for marketing automation — all on the same data. The key is documenting which pipeline serves which purpose, and that's where architecture diagrams become essential.