Data Warehouses & Analytics Storage

Everything in this course so far has served the product: a customer places an order, a profile loads, a balance updates. But the business also has questions about the product: what was revenue by region last quarter, which signup source retains best? Those questions look nothing like the product's queries, and trying to answer them on the same database that serves customers is a classic way to cause an outage. This chapter explains why, and what to do instead.

A shop's till handles one customer at a time: scan an item, take a payment, print a receipt. It has to be fast and it is used all day. The accountant who prepares the annual figures wants something entirely different: totals across every sale of the year. If the accountant sat at the till to do that work, the queue would stretch out of the door. So the shop copies its sales records to the back office, and the accountant works there.

• -The till is the OLTP database: many small, quick reads and writes, one record at a time.
• -The annual accounts are OLAP: a few huge queries that scan millions of records.
• -The back office, working on a copy, is the data warehouse.

Everything built so far is an OLTP workload (Online Transaction Processing): place an order, update a balance, fetch a profile. OLAP (Online Analytical Processing) is the opposite shape: a handful of large queries that scan millions of rows to answer questions such as total revenue by region, by month, for the last two years. Running those directly against the production database does real damage: large scans hold locks and burn the disk and memory that live traffic needs, so customers feel it.

The second half of the answer is how the warehouse stores data. A normal database stores a table row by row, with all of one record's columns together on disk, which is ideal for fetching or updating one whole record. A warehouse stores it column by column, with all the values of one column together. See what that does to an analytical query. Take 100 million orders with 20 columns, around 200 bytes per row. To total revenue by region you need only two columns, region and amount.

• -Row storage reads whole rows, so it scans 100 million x 200 bytes = 20 GB to use two columns.
• -Column storage reads only the two columns it needs, roughly 10 bytes per row, so about 100 million x 10 bytes = 1 GB, around twenty times less.
• -Values in a column look alike (a few dozen regions repeated millions of times), so they compress very well, and the real amount read is smaller still.

The price is that column storage is poor at fetching or updating one whole record, which is why it complements the OLTP database rather than replacing it. The card below describes the warehouse component as it appears in the lab:

Data is copied from the production databases into the warehouse by a pipeline: extract it, transform it (clean it, join it with other sources, reshape it), and load it. This is called ETL, or ELT when the transformation happens after loading, inside the warehouse. It may run nightly, or continuously by streaming changes out of the database. The diagram shows the shape:

Two consequences follow. First, a warehouse is slightly behind the product: a report is only as fresh as the last load, from minutes to a day. That is fine for business questions, but never use the warehouse to decide whether an order may be placed. Second, warehouse data is mostly appended, not edited row by row: you add new loads and query across them.