Data warehouse versus OLTP database

WHAT THIS TESTS This checks whether you understand that storage layout and query engine design follow the workload, and that OLAP and OLTP are not interchangeable.

A GOOD ANSWER COVERS 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.

COMMON WRONG ANSWERS 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.

LIKELY FOLLOW-UPS 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?

ONE 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.