What is a data warehouse vs. a transactional database?
Tests your grasp of read-optimized (OLAP) vs. write-optimized (OLTP) systems. A great answer defines warehouses for analysis, contrasts them with transactional DBs for operations, and explains the resulting differences in workload, schema, and data structure.
What's really being asked
This question isn't a vocabulary quiz. It tests your understanding of fundamental system design trade-offs. The interviewer is probing whether you understand that different business requirements (e.g., running an e-commerce site vs. analyzing monthly sales trends) demand fundamentally different data architectures. They are looking for your ability to connect business needs to technical design choices, specifically for write-optimized (OLTP) vs. read-optimized/analytical (OLAP) systems.
The full answer
A strong answer addresses four key differences in order. First, PURPOSE: OLTP databases (e.g., MySQL for a web app) are for running the business with real-time transactions. Data warehouses are for understanding the business by integrating historical data for analysis. Second, WORKLOAD: OLTP handles thousands of small, simple transactions per second (e.g., 'get user by ID'). Warehouses handle a few very complex queries that scan billions of rows (e.g., 'calculate LTV by channel over 5 years'). Third, SCHEMA: OLTP uses highly normalized schemas (3NF) to ensure data integrity for writes. Warehouses use denormalized star/snowflake schemas to speed up large, complex joins for reads. Fourth, DATA: OLTP holds current, atomic data. Warehouses hold historical, aggregated, time-variant data, usually loaded via periodic batch jobs (ETL/ELT).
The mistakes people make
A weak answer is often vague, like "a data warehouse is just a really big database," which misses the architectural point. Another red flag is confusing the terms OLAP and OLTP or not knowing what they stand for (Online Analytical Processing vs. Online Transactional Processing). The most common mistake is stating facts without explaining the 'why'—for example, saying one schema is normalized and one is not, without connecting this choice to the write-integrity vs. read-performance trade-off.
What usually comes next
Expect questions that dive deeper into the mechanics. For example: "Describe the process of getting data from our OLTP databases into the data warehouse. What is ETL?" or "What is a star schema? Walk me through an example for an e-commerce company." Another common path is to explore alternatives: "When might you choose not to build a data warehouse?" or "How have cloud warehouses like BigQuery or Snowflake changed this picture?"
A concrete example
An e-commerce site uses an OLTP database (PostgreSQL) to manage live orders. When you click "buy," it creates a new row in an orders table. This is a fast, small transaction. Every night, an ETL process extracts data from PostgreSQL, transforms it, and loads it into a data warehouse (BigQuery). A business analyst can then run a query in the warehouse like "Show me total sales of 'Product X' in 'California' for each month of the past two years." This complex query would cripple the live OLTP database but is exactly what the warehouse is designed for.
Interview question
When a business needs to analyze historical sales trends over multiple years, which database system is typically preferred and why?
- a.A data warehouse, because it provides real-time data updates and immediate operational insights.
- b.A data warehouse, because its architecture is optimized for complex, read-heavy analytical queries on large, historical datasets.Correct
- c.A transactional database, because its normalized structure ensures data integrity for all historical records.
- d.A transactional database, because it efficiently handles a high volume of small, individual data retrievals for reporting.
Why? this is the answer
Data warehouses are designed for understanding the business through historical data analysis, handling complex queries across large datasets. Transactional databases, while ensuring data integrity, are optimized for real-time operational transactions and high volumes of small, fast operations, not complex historical trend analysis.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
- #data warehouse
- #oltp
- #olap
- #system design
- #databases
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Open roles that interview on data warehouse — each one lists the topics its interview covers.
See open roles