Data Pipelines: Batch, Streaming & Modern ETL Patterns

Short on time? This is the deep dive. For the quick version, read the Data Pipeline Architecture Guide.

Every company is a data company. The architecture that moves data from where it's created to where it's useful determines how fast you can make decisions, train models, and serve users.

This guide covers the patterns, tools, and trade-offs for building data pipelines at any scale.

What Is a Data Pipeline?#

Data Sources → Ingestion → Processing → Storage → Consumption
(databases,    (collect)   (transform)  (warehouse) (dashboards,
 APIs, logs,                                        ML models,
 events)                                            reports)

Batch vs Streaming#

Most systems need both. Batch for historical analysis, streaming for real-time.

ETL vs ELT#

ETL (Extract, Transform, Load)#

Transform data BEFORE loading into the warehouse:

Sources → Extract → Transform (clean, join, aggregate) → Load → Warehouse

When: Data needs cleaning before it's useful, limited warehouse compute.

ELT (Extract, Load, Transform)#

Load raw data first, transform IN the warehouse:

Sources → Extract → Load (raw) → Warehouse → Transform (SQL/dbt)

When: Modern cloud warehouses (Snowflake, BigQuery) have cheap compute. Transform with SQL.

ELT is winning. Modern warehouses handle transformation better than external tools. dbt is the standard for SQL-based transforms.

Architecture Patterns#

1. Simple Batch Pipeline#

PostgreSQL → Airflow (daily schedule)
                → Extract (pg_dump or query)
                → Load to S3 (raw)
                → dbt transform (SQL)
                → BigQuery/Snowflake
                → Looker/Metabase dashboards

Tools: Airflow + dbt + cloud warehouse Latency: Daily or hourly Best for: Analytics, reporting, ML feature engineering

2. Real-Time Streaming#

App Events → Kafka → Stream Processor (Flink)
                          ↓          ↓
                    Real-time DB   Data Lake
                    (ClickHouse)   (S3/Parquet)
                          ↓          ↓
                    Live Dashboard  Batch Analytics

Tools: Kafka + Flink + ClickHouse Latency: Sub-second Best for: Real-time dashboards, fraud detection, recommendations

3. Lambda Architecture (Batch + Streaming)#

Events → Kafka → Speed Layer (Flink, real-time) → Serving Layer
              → Batch Layer (Spark, hourly)     → Serving Layer
                                                      ↓
                                                  Merge results
                                                      ↓
                                                  Application

Speed layer gives approximate real-time results. Batch layer provides accurate historical results. Serving layer merges both.

Downside: Two codepaths to maintain. Complexity is high.

4. Kappa Architecture (Streaming Only)#

Events → Kafka (immutable log) → Stream Processor → Serving Layer
                                                          ↓
                                                     Application

Everything is a stream. Replay the Kafka log to reprocess historical data. No separate batch layer.

Simpler than Lambda but requires your streaming framework to handle both real-time and batch workloads.

5. Change Data Capture (CDC)#

Capture database changes as events:

PostgreSQL → Debezium (CDC) → Kafka → Stream Processor → Analytics DB
                                    → Search Index (Elasticsearch)
                                    → Cache (Redis)

No dual-write problem. Database is the source of truth. Downstream systems stay in sync via CDC events.

Tools: Debezium, AWS DMS, Airbyte, Fivetran

6. Modern Data Stack#

Sources (SaaS APIs, DBs, events)
    → Ingestion (Fivetran / Airbyte)
    → Raw Layer (Snowflake / BigQuery)
    → Transform (dbt)
    → Semantic Layer (dbt metrics / Cube)
    → BI (Looker / Metabase / Preset)
    → Reverse ETL (Census / Hightouch) → back to SaaS tools

The modern standard for analytics teams. SQL-centric, version-controlled (dbt), modular.

Tool Comparison#

Orchestration#

Ingestion#

Processing#

Storage#

Data Quality#

Bad data is worse than no data. Build quality checks into the pipeline:

Source → Ingest → Quality Check → Transform → Quality Check → Serve
              (schema validation)         (row counts, nulls,
               (freshness check)          distribution checks)

Tools:

• Great Expectations — data quality assertions
• dbt tests — built-in schema and custom tests
• Monte Carlo — data observability platform
• Soda — data quality checks as code

Best Practices#

1. Idempotent pipelines — rerunning produces the same result
2. Schema evolution — handle new/removed/changed fields gracefully
3. Backfill capability — ability to reprocess historical data
4. Monitoring — track freshness, row counts, schema changes
5. Incremental processing — don't reprocess everything every run
6. Data contracts — producers and consumers agree on schema
7. Lineage tracking — know where every column comes from

Scaling Decisions#

Generate your data pipeline architecture →

Summary#

1. ELT over ETL — let the warehouse do the heavy lifting
2. Batch for analytics, streaming for real-time — most need both
3. CDC for database sync — no dual writes, Debezium + Kafka
4. dbt for transforms — SQL, version-controlled, tested
5. Data quality is non-negotiable — test at every stage
6. Start simple — PostgreSQL + dbt before Kafka + Flink

---

This is the 100th blog post on Codelit.io! Design your data pipeline architecture at codelit.io.