What is a data warehouse vs. a transactional database?
Tests your grasp of systems optimized for different access patterns (writes vs. reads). Define OLTP for transactions and OLAP for analytics. Contrast their schema (normalized vs. denormalized), data, and workload. A red flag is calling it a 'big database'.
What's really being asked
This question probes your understanding of fundamental data architecture trade-offs. For a senior role, it's not a vocabulary test. The interviewer wants to know if you understand that different business requirements (running day-to-day operations vs. performing strategic analysis) demand fundamentally different system designs. A great answer connects the technical differences back to the business purpose they serve.
The full answer
A strong answer compares the two systems across four key axes. First, PURPOSE: Online Transaction Processing (OLTP) databases are for running the business, capturing transactions in real-time. Data warehouses, a type of Online Analytical Processing (OLAP) system, are for analyzing the business using historical data. Second, WORKLOAD: OLTP is optimized for a high volume of simple, concurrent read/write/update queries (e.g., creating an order). Data warehouses are optimized for a low volume of complex, read-heavy queries that scan millions of rows (e.g., calculating total sales by region for the last five years). Third, SCHEMA: OLTP uses a highly normalized schema (e.g., 3rd Normal Form) to ensure data integrity and minimize redundancy for writes. Data warehouses use denormalized schemas, like star or snowflake schemas, to make analytical queries faster and simpler to write. Fourth, DATA: OLTP contains current, up-to-the-minute data. Data warehouses contain historical data, often aggregated, which is loaded periodically from one or more OLTP sources via an ETL (Extract, Transform, Load) process.
The mistakes people make
The most common mistake is describing a data warehouse as just a 'backup' or an 'archive' of the production database. This completely misses its purpose as an active system for analysis. Another red flag is calling it a 'big database' without explaining the specific architectural differences in schema, indexing, and query engine optimization. Confusing the terms OLTP and OLAP, or failing to explain why a denormalized schema is better for analytics, also indicates a surface-level understanding.
What usually comes next
Expect questions like: 'Describe a star schema. What are fact and dimension tables?' or 'Walk me through a simple ETL process for moving data from your application DB to a warehouse.' Another common one is, 'When might you query the OLTP database directly for analytics?' (Answer: For simple, real-time operational reporting where latency is critical and the query won't impact production performance). You may also be asked to compare a data warehouse to a data lake.
A concrete example
An e-commerce website's OLTP database (e.g., PostgreSQL) handles individual customer orders in real-time. It has normalized tables like users, products, and orders. When a customer buys a shirt, it's a fast INSERT into the orders table. The company's data warehouse (e.g., BigQuery) is used by the marketing team to analyze trends. Every night, an ETL job pulls data from PostgreSQL, transforms it, and loads it into a sales_facts table and dim_customer and dim_product dimension tables. An analyst can then run a single, complex query to find 'total sales of all medium-sized shirts in California for Q3', a query that would be unacceptably slow and resource-intensive on the live OLTP database.
Interview question
An analyst needs to calculate total sales revenue by region for the last five years. Which system is best suited for this type of complex, read-heavy query?
- a.A nightly backup of the transactional database, to avoid impacting production performance while using the same schema.
- b.A data warehouse, because its denormalized schema is optimized for fast analytical queries across large historical datasets.Correct
- c.The transactional (OLTP) database, because it is designed to handle a high volume of concurrent queries.
- d.The transactional (OLTP) database, because it contains the most current and granular transaction data.
Why? this is the answer
A data warehouse is correct because its purpose is to support complex analytical queries on historical data. Running this query on a transactional (OLTP) database would be slow and could negatively impact real-time business operations.
Just read this? Test yourself on what you have been reading.
Read the original → en.wikipedia.org
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 modeling — each one lists the topics its interview covers.
See open roles