Data Warehouse, ETL & Big Data Analytics
Bringing the data of all systems into one place to analyze for business reports, dashboards and ML.
22.1 Why is it used?
- If you run a heavy query like 'last 1 year's city-wise sales' on the production (OLTP) DB, live orders will slow down.
- Data is spread out - orders in MySQL, clicks in Kafka, payments in a separate service - and analysis needs it in one place.
- Management needs daily/real-time dashboards: GMV, conversion rate, top products.
- ML models (recommendation, fraud) need a very large amount of historical data for training.
- A data warehouse uses columnar storage to aggregate billions of rows in seconds.
22.2 How it works
- OLTP vs OLAP: OLTP = small fast transactions (placing an order), row-based DB; OLAP = large analytical queries (SUM, GROUP BY), columnar warehouse.
- ETL vs ELT: in ETL you transform first and then load; in modern ELT you load raw data into the warehouse and transform with SQL (dbt).
- Batch vs Streaming: batch (Spark, every day at night) vs streaming (Kafka -> Flink, real-time in seconds).
- Pipeline: sources (DB CDC, app events) -> Kafka -> Spark/Flink processing -> data lake (S3) + warehouse (Snowflake/BigQuery/Redshift) -> BI dashboards.
- Data lake / lakehouse: raw files in S3/GCS in Parquet format; Delta Lake / Apache Iceberg give ACID tables.
- Star schema: a fact table (orders) in the middle, with dimension tables (customer, product, date) around it.
- Columnar storage + compression + MPP: read only the needed columns, and the query runs in parallel across many nodes.
- Orchestration: Airflow manages job schedules and dependencies; data quality checks are essential.
- Lambda vs Kappa architecture: in Lambda, the batch layer and the speed (stream) layer are separate; in Kappa everything goes through a single streaming pipeline and is reprocessed by replay.
- 1.Sources: app events (order, click, add-to-cart) + MySQL orders (OLTP live) via Debezium CDC
- 2.Kafka: topics, lakhs of events/sec
- 3.Real-time lane (in seconds): Flink, 1-min windows: city GMV, orders/min
- 4.Druid / Pinot: real-time OLAP -> war-room live sales dashboard
- 5.Batch lane (nightly, daily): S3 data lake, raw events in Parquet
- 6.Airflow schedule: Spark ETL (clean, dedupe, join) -> Snowflake / BigQuery columnar warehouse
- 7.Analysts: SQL category-wise sales report; Data scientists: retrain the recommendation model
Mistake: a heavy report on the production OLTP DB, 'last 1 year's city-wise sales' -> live orders slow down. Use a warehouse / read replica; do not SELECT *
- 1.OLTP MySQL (row store): placing an order = writing 1 whole row (id, city, amt, date)
- 2.OLAP (columnar): SUM(amt) = read only the 'amt' column
- 3.Columnar + compression + MPP: aggregation over billions of rows in seconds
- 1.dim_customer, dim_product, dim_date, dim_city: dimensions all around
- 2.fact_orders (in the middle): amount, qty, keys
22.3 Tools you can use
| Tool | Type | How it is used |
|---|---|---|
| Apache Kafka | Event streaming platform | Central pipe for clicks, orders and CDC events; downstream Spark/Flink and the warehouse consume from it. |
| Apache Spark | Batch + micro-batch processing engine | ETL, joins and aggregations on large files; runs on Databricks/EMR/Dataproc. |
| Apache Flink | Real-time stream processing | True streaming, event-time windows; live dashboards, fraud detection, real-time counts. |
| Snowflake | Cloud data warehouse (multi-cloud) | Storage and compute separate, virtual warehouses scale up/down; SQL analytics on AWS/Azure/GCP. |
| Google BigQuery | Serverless data warehouse (GCP) | No cluster, pay per TB scanned or for slots; petabyte SQL queries in seconds. |
| Amazon Redshift | Data warehouse (AWS) | MPP columnar cluster or Serverless; tight integration with the AWS ecosystem (S3, Glue, Kinesis). |
| Airflow / dbt | Orchestration / transformation | Airflow manages schedules and dependencies, and dbt does the SQL transforms. |
| Azure Synapse / Fabric | Azure warehouse | Azure's warehouse option. |
22.4 Comparison: Snowflake vs BigQuery vs Redshift
| Point | Snowflake | BigQuery | Redshift |
|---|---|---|---|
| Cloud | Multi-cloud (AWS, Azure, GCP) | GCP only | AWS only |
| Architecture | Storage-compute separate, virtual warehouses | Fully serverless | Provisioned cluster (RA3) or Serverless |
| Scaling | Change warehouse size/count in seconds | Automatic, slots based | Resize/concurrency scaling, Serverless auto |
| Pricing | Compute credits per second + storage | Per TB scanned or reserved slots | Per node-hour or RPU-hour |
| Ops effort | Low | Lowest | Medium (tuning: sort/dist keys) |
| Streaming ingest | Snowpipe / Snowpipe Streaming | Storage Write API, real-time | Kinesis/MSK streaming ingestion |
| Best for | Multi-cloud, data sharing, easy scaling | GCP users, huge ad-hoc queries | AWS-heavy companies |
22.5 When to choose which
- Snowflake: you need multi-cloud or cloud-neutral, and isolated compute for different teams.
- BigQuery: you are on GCP, want zero ops, and do a lot of ad-hoc analysis.
- Redshift: the whole stack is on AWS (S3, Glue, Kinesis) and the workload is predictable.
- Spark: large batch ETL / ML feature jobs; Flink: second-level real-time processing.
- Kafka: when there are many sources and many consumers - a decoupling layer in the middle.
22.6 Real-world example
Real world: Flipkart Big Billion Day live sales dashboard (a hypothetical scenario)
- Every order, click and add-to-cart event goes from the app to Kafka topics - lakhs of events per second on sale day.
- Orders from MySQL also reach Kafka through Debezium CDC.
- A Flink job counts city-wise GMV, orders/min and top products in 1-minute windows.
- Real-time results go to a live dashboard (an OLAP store like Druid/Pinot), and management watches it in the war-room.
- At the same time, raw events are saved in the S3 data lake in Parquet format.
- At night Airflow runs a Spark ETL: cleaning, dedupe, join, then a load into a warehouse like Snowflake/BigQuery.
- The next day analysts build a category-wise sales report with SQL, and data scientists retrain the recommendation model.
22.7 Interview tips
- Common mistake: running heavy reports on the production OLTP DB - use a read replica or a warehouse.
- Common mistake: running SELECT * in the warehouse - in columnar, take only the needed columns to save cost (BigQuery bills on scanned bytes). Ignoring data quality and schema changes is also wrong - a wrong dashboard leads to a wrong business decision.
- Interview tip: keep OLTP vs OLAP, batch vs stream and the Lambda/Kappa architecture difference ready.
- Worth quoting: columnar + MPP aggregates billions of rows in seconds; BigQuery charges on scanned bytes, so select only the columns you need.