Topic 13B.7
Batch vs Stream Processing, OLTP vs OLAP
In one line
Operational databases serve the app (OLTP); analytics needs different storage (OLAP). Batch jobs process large bounded datasets periodically; stream processing handles unbounded events continuously. Most companies run both, connected by change data capture.
Think of it like this
A shop's till vs its accountant. The till (OLTP) records each sale instantly and must never lose one. The accountant (OLAP) analyses a whole year of sales at once to answer 'which products sell best on Fridays?'. Doing the accountant's work on the till would stop sales.
Key ideas
- 01
OLTP (PostgreSQL, MySQL, DynamoDB): many small reads and writes by key, row-oriented storage, strong consistency. OLAP (Snowflake, BigQuery, Redshift, ClickHouse, Databricks): few huge scans and aggregations, COLUMNAR storage (read only the columns you need, compress them heavily). Keep analytics off the production database; move data out.
- 02
BATCH processing (Spark, dbt, SQL on a warehouse; historically Hadoop MapReduce): process a bounded dataset (yesterday's orders) on a schedule. Simple, easy to re-run and correct, but results are hours old. The DATA LAKE (files in S3 in open formats like Parquet with table formats such as Apache Iceberg or Delta) plus warehouse is the common home.
- 03
STREAM processing (Kafka Streams, Flink, Spark Structured Streaming): process events continuously as they arrive, with state, WINDOWS (counts per 1-minute window), and WATERMARKS for late events. Results in seconds: fraud detection, live dashboards, trending topics, real-time recommendations. Harder: state management, exactly-once, out-of-order events.
- 04
CHANGE DATA CAPTURE (Debezium reading the database WAL into Kafka) streams every row change from OLTP to consumers: search indexes, caches, the warehouse, other services, without dual writes. LAMBDA architecture runs batch + stream paths in parallel; KAPPA uses a single streaming path with replay from the log. Most teams today: CDC + stream for freshness, warehouse/lakehouse for history.
Code & diagrams
Explain without notes
Why are columnar formats faster for analytics?
Practice
The business wants a 'live orders per minute by city' dashboard plus a monthly revenue report. Design the data flow.
Trade-offs
- ↔
Batch: simple, correct, cheap, stale. Streaming: fresh, complex to operate. OLTP stays fast only if analytics runs elsewhere.
Run it in production
You've designed it. Now build, operate, and break the same idea hands-on in the DevOps courses:
Completion checklist
I can distinguish OLTP/OLAP and batch/stream
I can explain CDC and where it fits