Data Warehouse
Column-oriented analytics store for OLAP — aggregations across billions of rows, not transactional OLTP.
Interview tip Lead with a 30-second definition, then one real system example and name 2–3 designs where Data Warehouse is non-negotiable.
① What it is (30 seconds)
Column-oriented analytics store for OLAP — aggregations across billions of rows, not transactional OLTP.
② How it works in system design
ETL/ELT pipelines load events from OLTP into warehouse (BigQuery, Snowflake, Redshift). Star schema: fact tables + dimension tables. Partition by date for prune.
Typical placement
Client→Edge / Gateway→Data→Services→Data stores
③ Concrete system design example
Scenario: Google Trends: billions of search events land in BigQuery. Analysts query daily aggregates by region and term — separate from live search serving path.
④ Important interview Q&A
| Question | Answer |
|---|---|
| Warehouse vs lake? | Warehouse structured schema + SQL; data lake raw files (S3) + schema-on-read. |
| OLTP vs OLAP? | OLTP many small transactional writes; OLAP large scan aggregations — different engines. |
| Real-time analytics? | Lambda architecture: speed layer (Flink) + batch layer (warehouse) merged at query. |
⑤ Seen in these system designs
- Google Trends — aggregate queries
- Real-time Analytics — lambda arch
- Apache Spark — batch processing
In interviews, after explaining the concept, say: "This shows up directly in …" and link two designs.
⑥ Revision checklist
- OLTP vs OLAP
- Partition strategy
- ETL vs ELT
- Star schema basics