Explain data warehouse purpose and how it differs from OLTP

WHAT THIS TESTS: The interviewer wants to know if you understand that databases are not one-size-fits-all and that the engineering trade-offs for a system handling customer orders are radically different from those supporting executive dashboards. Specifically, they are checking whether you can articulate the workload differences between online transaction processing and online analytical processing, and why attempting to serve both from the same schema and hardware usually fails at scale.

A GOOD ANSWER COVERS: First, purpose: a data warehouse is a centralized repository that integrates current and historical data from disparate sources to support reporting, business intelligence, and decision-making. Second, schema design: warehouses typically use denormalized dimensional models such as star or snowflake schemas that optimize for read performance and aggregations across billions of rows, whereas OLTP databases use highly normalized third-normal-form schemas to minimize redundancy and ensure consistency during frequent small writes. Third, workload characteristics: OLTP systems prioritize millisecond latency for row-level inserts, updates, and deletes with strict ACID guarantees for daily operations, while warehouses prioritize throughput for complex queries scanning millions of rows. Fourth, mutability: warehouses are often append-only or batch-updated, while OLTP systems handle constant random mutations.

COMMON WRONG ANSWERS: A major red flag is stating that a data warehouse is simply a bigger version of a transactional database like PostgreSQL or MySQL. Another warning sign is claiming that normalization is always better, which reveals confusion about why denormalization is intentionally accepted in analytics to reduce expensive joins. Candidates also stumble by suggesting you can run heavy BI queries directly against the production OLTP database without mentioning performance degradation, lock contention, or the risk of impacting customer-facing transactions.

LIKELY FOLLOW-UPS: The interviewer may ask how data actually gets into the warehouse, so be ready to discuss ETL or ELT pipelines, change data capture, and batch versus streaming ingestion. They might also probe specific warehouse technologies such as Snowflake, BigQuery, Redshift, or ClickHouse and ask when to use a data lake instead. A senior candidate should also be prepared to discuss slowly changing dimensions, partitioning strategies for cost and query performance, and how to handle schema evolution in an analytics environment.

ONE CONCRETE EXAMPLE: Imagine an e-commerce company. The OLTP database records a customer order in milliseconds using a normalized schema with separate tables for customers, orders, order_items, and products to ensure no anomalies during checkout. Each night, an ETL pipeline extracts that data, transforms it into a star schema with a central orders fact table surrounded by customer and product dimension tables, and loads it into a data warehouse. A business analyst can then run a query aggregating total revenue by product category over the last five years in seconds without slowing down the checkout flow or locking production tables.

Source: Wikipedia: Data warehouse