Two questions about the same rides
A city bike-share runs on one PostgreSQL database. Thousands of times a minute, the app asks it something like this:
SELECT status FROM bikes WHERE bike_id = 4471
One row, found through an index, answered in a millisecond or two, because a rider is standing at the dock. When the ride ends, one row in rides is updated inside a transaction. That is OLTP, online transaction processing: many small reads and writes on current rows, each of which has to be fast and correct while a person waits.
Once a month, the operations team asks something else:
SELECT start_station_id, AVG(duration_s) FROM rides WHERE started_at >= '2024-10-01' GROUP BY start_station_id
This one reads two years of rides, 300 million rows, to produce 900 numbers, one per station. That is OLAP, online analytical processing: few queries, each scanning and aggregating a large slice of history. Run it on the app's database and it competes with the dock queries for the same disk, memory and CPU, and they slow down while it runs. A read replica takes the load off the primary but not the slowness, because the replica stores rows exactly the same way, and in PostgreSQL a long query on a replica can be cancelled by replication or hold it back.
Reading three columns out of thirty
The monthly query is slow not only because 300 million is a large number. A row store such as PostgreSQL keeps each ride as one record, all thirty columns side by side on a page, so a scan for start_station_id, duration_s and started_at reads whole rows off each page: payment references, member ids, route summaries and all. The query needs three of the thirty columns, and the scan still reads pages holding all of them.
A columnar store turns the layout around. Each column is stored on its own, in large blocks, so the query reads the three columns it names and nothing else. Two effects compound that. A column holds values of one type, often repeated, 900 station ids across 300 million rides, so it compresses far better than mixed rows do. And most engines keep summary statistics per block, such as the minimum and maximum started_at, so a filter on the date skips every block that cannot match. Warehouses then spread the scan across many cores or machines. Together, that is how BigQuery, Snowflake and ClickHouse can answer the monthly question in seconds over data the app's database would take minutes to scan.
The same layout makes the app's questions expensive. Fetching bike 4471's whole record means touching one block per column, and changing one ride means rewriting part of a compressed block: most columnar engines write changed data as new files and merge them later, so single-row writes are slow and costly there. A warehouse is usually fed in batches and read in scans. Pointing the app's dock lookups at it moves the problem rather than solving it.
Tables shaped for the questions
Questions arrive by station, by district, by month, by member plan, so warehouses usually arrange the data as a star schema. A fact table has one row per measured event, here a ride, with its numbers (duration in seconds, price in cents) and keys. Dimension tables describe what those keys point at: stations with their district and dock count, members with their plan, dates with their weekday and holiday flag. A query joins the fact table to the dimensions it needs, filters and groups on their columns, and aggregates the fact columns.
Dimensions are usually flatter than the app's tables, the district copied onto each station row, because the warehouse is read far more than it is written; Normalisation & denormalisation weighs what such a copy costs. A report twenty people open every morning can be computed once into a small table of results, and Materialized View shows how that copy stays fresh.
Files in a lake, tables in a warehouse
Not everything starts in the app database. While a bike moves it reports a GPS point every few seconds, billions of points a month, and the app never stores them in PostgreSQL. They land as files in object storage, one folder per day: a data lake, cheap storage that accepts files in any format and imposes no schema of its own, so any validation happens when data is ingested or when it is read (the storage underneath is described in Object storage & blobs). That freedom is also the risk: a lake nobody curates fills with files nobody can interpret.
A warehouse keeps managed tables instead: a schema checked when data is loaded, SQL, loads that in most engines commit as a whole or not at all, and access control per table and column. Rides are modelled there, and so are GPS tables built from the lake's files; an engine such as Athena can also run SQL straight over the files in S3. A lakehouse brings warehouse-style tables to the lake: a table format such as Apache Iceberg, Delta Lake or Apache Hudi keeps metadata, a schema and a history of atomic commits beside Parquet files in object storage, and the engines that support the format, with a catalog in front of them, supply the SQL and the per-table access control, at the price of more moving parts than one managed warehouse.
Rides are loaded from the app's database; GPS points arrive as files in the lake and are modelled into warehouse tables. Neither copy in the warehouse is the source of truth.
A copy you can throw away
When the warehouse and the app disagree about how many rides a member took, which one is right? The warehouse is a derived read store: when it is loaded from the app DB, the OLTP database stays the source of truth, and the fix is to reload, not to patch the warehouse by hand. A reload brings back only what the app still holds: a member's earlier plan, overwritten in OLTP, survives only if the pipeline recorded it when it changed. For event data that never lives in OLTP, like the GPS points, the raw events, in a log or in the lake, are the source, and every warehouse table built from them should be rebuildable by running the transforms again.
That is why the raw files are kept unchanged and the transforms are kept as code. A bug in last quarter's distance calculation is then fixed by rebuilding, and the corrected numbers come from the same inputs as the wrong ones; running such a rebuild over months of data is the job of Batch processing: partitions, shuffles & backfills. Getting the rows in, incrementally and checked, happens in ETL/ELT & data orchestration; Stream processing: windows, watermarks & state computes results as events arrive, and Data governance: catalogs, lineage & classification records who owns each table and who may read each column.
Where it goes wrong
- Heavy analytical queries run on the OLTP database, and the app that depends on it slows down, because both compete for the same disk, memory and CPU.
SELECT *against a columnar table reads every column and gives up the saving the columnar layout exists for; under BigQuery's on-demand pricing it is also billed by the bytes it reads, and aLIMITdoes not, in general, reduce them.- Analysts adjust numbers directly in warehouse tables. The next rebuild erases the adjustments, or the team stops rebuilding, and the warehouse has become a second source of truth instead of a derived, rebuildable copy of the app DB or the raw event log.
- Data is loaded with no schema or quality checks, so bad input becomes a bad dashboard.
A firehose of clicks
At 100,000 clicks a second, Ad Click Aggregation puts the OLTP-versus-OLAP choice on one arrow. Begin with the storage: land the raw click events in a columnar store built for scans (real-time OLAP plus a batch warehouse fed from the same log) instead of aggregating in a row-oriented database. Feed the batch warehouse from the raw log, not from the real-time aggregates, so its totals can be rebuilt from the events themselves. Analytics Lakehouse is the lake-first version of the same question, with raw files in cheap storage and modelled tables served to BI.