Data warehouse versus OLTP database
OLAP versus OLTP design.
warehouses use columnar storage for analytical scans, OLTP uses row storage for fast transactions, each fits a different workload.
a warehouse for high-volume single-row writes, or vice versa.
What's really being asked
This checks whether you understand that storage layout and query engine design follow the workload, and that OLAP and OLTP are not interchangeable.
The full answer
An OLTP database like PostgreSQL stores rows together on disk. That layout is ideal for transactional work: insert an order, update a balance, fetch one customer by primary key, all touching whole rows with ACID guarantees and high concurrency of small operations. Indexes make point lookups fast. A data warehouse like BigQuery or Redshift stores data by column. Analytical queries typically read a few columns across enormous row counts to compute sums, averages, and group-bys; columnar storage means the engine reads only the needed columns, compresses them well, and uses massively parallel processing across nodes. BigQuery and Redshift RA3 also separate storage from compute, so you scale query power independently and pay for data scanned. Warehouses are append and bulk-load oriented, not optimized for high-frequency single-row updates.
The mistakes people make
Using a warehouse as an application backend for many small writes, which is slow and costly per operation. Running heavy analytics directly against the production OLTP primary, starving transactional traffic. Believing columnar versus row storage is a minor detail rather than the core difference.
What usually comes next
What is an ETL or ELT pipeline that moves data from OLTP to the warehouse? Why does columnar storage compress better? What is a star schema? How does separating compute and storage affect cost and scaling?
A concrete example
An e-commerce site runs Postgres on RDS for the live store: placing orders, updating inventory, reading carts, all small fast ACID transactions. Each night an ELT job loads order history into BigQuery, where analysts run a query scanning two years of orders to compute revenue per region by month, a single columnar scan over billions of rows that would cripple the transactional database.
Interview question
Why is a columnar data warehouse a poor choice as the primary store for an app doing thousands of single-row updates per second?
- a.Row-based OLTP databases cannot perform aggregations at all
- b.Columnar engines cannot store more than one column per table
- c.Warehouses lack any compression, so writes are too large
- d.Columnar storage is optimized for scanning columns in bulk, not fast single-row transactional writesCorrect
Why? this is the answer
Columnar layout and MPP excel at large analytical scans but are inefficient for frequent single-row writes, which row-oriented OLTP handles well. The other options misstate how these systems work.
Just read this? Test yourself on what you have been reading.
Read the original → aws.amazon.com
- #data-warehouse
- #olap
- #oltp
- #bigquery
- #redshift
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. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles