Roadmap To Be A Data Engineer / Lesson 01
The Monday Morning Query
One reasonable question, asked of the wrong kind of database, took the shop’s checkout down. Why row stores and column stores exist, and why data engineers build a second copy of your data.
1The situation
Monday, 09:05 You ask a simple question:
“For the last 12 months, what’s our average sale price per brand?”
An analyst writes one SQL query and points it at the production database, the same one the shop runs on. Eight minutes later checkout starts timing out. The query gets cancelled.
Nobody did anything wrong. The question was asked of the wrong kind of database.
2Why it hurt
The orders table has about 60 columns and 40 million rows at roughly 1 KB per row, so around 40 GB. The question needs only 3 columns: sold_at, brand_id and sale_price, about 20 bytes per row.
Whether the other 57 columns can be skipped depends entirely on how the bytes are laid out on disk.
Row store (OLTP, the shop database)
One whole row is stored together. Each bar below is one order on disk.
Reads all 60 columns to get 3. Full table scan: 40 GB.
Column store (OLAP, a warehouse)
One whole column is stored together. Each bar below is one column on disk.
Reads 3 column blocks and skips 57. Compressed: 180 MB.
Simplified to 12 columns × 6 rows. Neither layout is “better”: a row store is built for “give me order #88421, all of it”, a column store for “give me one field across 40 million rows”. Repeated values such as a brand id also compress very well in a column.
3The numbers, same query
| Where it runs | Bytes read | Bytes read, to scale | Time |
|---|---|---|---|
| Row store, full scan | 40,000 MB | ~8 min, and the shop slows down | |
| Column store, 3 columns, compressed | 180 MB | ~3 sec | |
| …plus date partitioning (only the months asked for) | 90 MB | ~3 sec |
The blue bars are not missing: at true scale they are 222× and 444× smaller than the red one. In a warehouse billed by bytes scanned, this table is also the invoice. Figures are illustrative.
4What the data engineer actually builds
Not a faster query. A second copy of the data, shaped for questions and kept fresh on a schedule.
The rule this architecture enforces: analytics never touches the system that takes the money. The cost is freshness: the data is about an hour old instead of live. Everything else in data engineering, from orchestration to testing to modelling, is about making that second copy trustworthy.
5Three questions to ask in a standup
- “Is this query hitting production or the warehouse?”The single question that prevents the incident above.
- “Is that table partitioned by date, and does the query filter on it?”Partitioning is the biggest cost lever a warehouse has. Unpartitioned means every query pays for all of history.
- “Are we selecting only the columns we need?”
SELECT *is nearly free in a row store and expensive in a column store. This habit alone routinely doubles a warehouse bill.
615-minute hands-on
- Open the BigQuery console. The free sandbox needs no credit card.
- Type (or paste) each query below, one at a time.
- Do not run them. Read the byte estimate in the top right of the editor and write it down.
-- 1. all columns, all history
SELECT * FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2018`;
-- 2. two columns only
SELECT passenger_count, total_amount
FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2018`;
-- 3. two columns, one month (partition pruning)
SELECT passenger_count, total_amount
FROM `bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2018`
WHERE pickup_datetime BETWEEN '2018-06-01' AND '2018-06-30';Write down the three estimates. That is this whole lesson, measured in euros.
7Takeaway
Row stores answer “everything about one order.” Column stores answer “one thing about every order.” Data engineering starts the moment the business needs the second kind of answer.
Vocabulary
