Scaling Data · lesson 7 of 7 · about 28 min
How data gets from the system that runs the business to the system that analyses it: why scans want columns, why copying whole tables stops working, how change data capture reads the transaction log, how a lake is laid out so queries stay cheap, what separates a warehouse from a lake and a lakehouse, and why change events must carry a sequence number. Tap any for a plain-language card.
Two workloads
Heavy scans also hurt: running them on the production database steals the cache and I/O that customers depend on. So analytical queries run on a separate system, a data warehouse, fed by copying data out of the operational databases. Compare how much a one-billion-row aggregate has to read from each layout.
With 50 columns and a query that needs 2, the column store reads about a hundredth of the data.
A query over 1,000,000,000 rows that needs 2 of 50 columns reads 400.0 GB in a row store (3.3 min at 2 GB/s) and 4.0 GB in a column store (2.0 s): 100 times less, from skipping the unneeded columns and compressing the rest.
Bytes = rows × columns × 8 bytes for a row store; rows × needed columns × 8 ÷ compression for a column store. Real systems add indexes, vectorised execution and partition pruning.
Moving the data
The refresh interval also sets the freshness of every dashboard downstream: a copy refreshed every T is on average T ÷ 2 stale. Shorter intervals demand incremental loads and eventually a continuous stream (next scene). Set the size of your source, how much of it changes each day, how fast you can move data, and how often you want it fresh.
With 2 TB at 200 MB/s a full reload takes about 2.8 hours, so an hourly full reload cannot work. The incremental load takes seconds.
A full reload takes 2.8 h but runs are scheduled every 1.0 h: the pipeline can never catch up. An incremental load takes 9 s. The copy is on average 30.0 min stale.
Full = size ÷ throughput. Incremental = changed share per day × size × (interval ÷ one day) ÷ throughput. Staleness = interval ÷ 2.
Change data capture
(CDC) reads the database's own transaction log (the write-ahead log, binlog or oplog it already keeps for replication and recovery) and turns each insert, update and delete into an ordered event, usually published to a stream such as Kafka. Nothing is missed, order is preserved, the source sees almost no extra load, and the delay is seconds. Debezium is the best-known open-source tool. Below, a table changes at the rate you choose and a poller reads it at the interval you choose.
Poll once a minute against 20 changes a second: about one change in five is invisible to the poller, and none of the deletes arrive.
Polling every 1.0 min against 20 changes a second delivered 9,776 of 12,000 changes: 18.5% were overwritten before the next poll, and none of the 611 deletes could be seen at all. Reading the transaction log delivers every one, in order, without querying the table.
Seeded 10-minute simulation: changes hit 5,000 rows with skewed popularity; the poller keeps one version per row per interval and cannot see deletions. CDC reads every log record.
Data lakes
The second layout trap is the small-files problem. Streaming jobs write a new file every few seconds, and a table can accumulate millions of tiny files. Each file costs an open, a footer read and a task to schedule, so the query spends longer on bookkeeping than on data. The fix is compaction: periodically rewrite many small files into fewer large ones (a few hundred MB each). Table formats such as Delta Lake, Iceberg and Hudi add transactions and metadata so this can be done safely.
Remove partitioning and the query scans every file. Then raise the file count to 100 million and watch bookkeeping dominate.
A one-day query over 10 TB in 10,000 files with date partitioning reads 3 GB from 3 files: about 0.5 s of reading plus 0.0 s of opening files. Partition pruning makes the query touch a 3,650th of the lake.
Scan = data read ÷ 5 GB/s. Opening = files × 5 ms ÷ 64 parallel tasks. Ten years of daily partitions: a one-day query keeps 1 of 3,650.
Choosing a home
None is universally right. Many organisations run a lake for raw and ML data and a warehouse for curated business reporting, moving toward a single lakehouse as table formats mature. The decision rests on who the users are (analysts want SQL and trust; data scientists want raw files), how much data, how structured, and how much governance you need. Tap through the three.
Compare the “schema” lines: write-time rejection catches bad data early; read-time schemas find it late.
| What it is | Structured, curated tables in a column store with its own query engine; data is cleaned and modelled before it lands. |
| Schema | Schema on write: data that does not fit is rejected or fixed at load. |
| Good at | Fast, reliable SQL for dashboards and analysts; governance and access control are built in. |
| Weak at | Costs more per terabyte; awkward for unstructured data, images and machine-learning pipelines. |
| Examples | BigQuery, Snowflake, Redshift |
Data warehouse: Fast, reliable SQL for dashboards and analysts; governance and access control are built in. The trade-off: costs more per terabyte; awkward for unstructured data, images and machine-learning pipelines.
A qualitative summary of how these systems are positioned; real products overlap and the lines move quickly.
Where does it belong?
The operational database serves transactions, the warehouse serves trusted analytics, the lake holds raw data cheaply, and a change stream carries every change between them. Each answer is worth 20 XP.
Ask: is it a transaction, an analysis, raw storage, or moving changes?
Handle checkout: insert the order and decrement the stock in one transaction.
Tip: press 1 to 4 to answer, Enter for the next one.
Who lives by this
Four published sources on analytics data.
interactive analysis of web-scale datasets
The Dremel paper described a columnar storage format for nested data and a query engine that scans only the columns a query needs across thousands of servers, giving interactive speeds over huge tables. Its ideas underlie BigQuery and the Parquet format.
the elastic data warehouse
The Snowflake paper described a warehouse that keeps data in cloud object storage and runs queries on independent, elastic compute clusters, so storage and compute can scale separately and several teams can query the same data without contending for the same machines.
open-source change data capture
Debezium reads the transaction logs of databases such as MySQL (the binlog) and PostgreSQL (the write-ahead log) and publishes each row-level insert, update and delete as an event, typically to Kafka, preserving order and catching deletes that polling would miss.
incremental processing on the data lake
Uber built Hudi to bring upserts, deletes and incremental pulls to data stored as files in a lake, so that tables could be updated within minutes instead of rewritten in daily batches. It was open-sourced and became an Apache project, alongside Delta Lake and Iceberg.
Interview angle
Six questions, each worth 20 XP. Pipelines and warehouses appear in any design that has reporting.
Separate OLTP from analytics, use CDC rather than polling, and say how you keep ordering and deletes correct.
Why are column stores faster for analytics?
Tip: press 1 to 3 to answer, Enter for the next one.
Break it
The fix is to make every change carry an ordering key from the source (the log sequence number or a version) and have the sink apply a change only if its key is higher than the one already stored. That makes applying idempotent and order-independent. Related defences: partition the stream by key so one row's events go through one consumer in order, deduplicate on event id, and reconcile warehouse and source periodically. Set how often events are swapped.
With 5% of events delayed and five updates per order, about one order in ten ends in the wrong state when applied by arrival. Switch to the sequence-number rule.
Applied in arrival order, 9.4% of orders end in a stale state (9,430 per 100,000) because an older event arrived last and overwrote the newer one.
10,000 seeded keys; each update arrives on time or, with the given probability, delayed past one to three later events. The sequence rule keeps the highest sequence number seen.