20. OLAP¶
Chapter 19 was the shop's transactions as they happen. This chapter is what they leave behind. The owner does not ask the order topic a question; the owner asks how much each customer spent this month, which day of the week sells most, and whether refunds are rising. Those are questions over history, over every order rather than the next one, and the tables they are asked of are the shop's OLAP side: online analytical processing, the warehouse, the lakehouse, the place a report reads.
The two sides differ in almost every way that matters to a pipeline, and a platform that serves both, as Databricks does, keeps them apart on purpose:
| OLTP | OLAP | |
|---|---|---|
| What a row is | one fact as it happened: an order, a payment | one fact at a chosen grain: an order, a customer-day, a customer |
| What the table holds | the current state | the history |
| How it is written | one row at a time, as the fact occurs | in batches, by a load that runs on a schedule or as a stream lands |
| How it is read | one row, by key | many rows, by scan, aggregated |
| The shape of its pipeline | tap, decide each value, sink |
source whole, group and sum, target |
| What goes wrong | a row that does not decode | a row that arrives twice, late, or corrected |
The last line is the subject of most of this chapter. A transactional pipeline delivers each message at least once, and a message delivered twice into a transactional table is a duplicate row to be found later. In an analytical table a duplicate is a wrong total in a report, and the pipeline that loads the table is where it is removed. Every load of an analytical table therefore answers three questions: what a row stands for, which rows are the same row, and which of the same rows is the one to keep.
In the terms of chapter 14, an analytical load is
usually a batch: a source that ends, whole-input steps over it, a target
that replaces or appends. It need not be. The silver table of
chapter 17 is an analytical table
filled by a stream, and the pages here show the same deduplication over a
batch and over micro-batches. The side a table is on says what its rows mean;
whether it is loaded by a batch or by a stream is the type of the input.
The pages¶
| Page | What it covers |
|---|---|
| Bronze, silver and gold | the three layers of a lakehouse, what each promises, and the load rule of each |
| Grain and keys | what one row stands for, the key that names it, and a duplicate at each grain |
| Deduplication | exact duplicates with distinct, duplicates by key with group and max_by, and a replayed stream as their source |
| Windows | one value per row from its place in its partition: over, ranks, neighbours and frames, and a total order with then |
| Merge | a table kept up to date by key: insert or update, today as a pipeline and as design as a write mode |
| Incremental loads | loading only what is new: a watermark the table holds, and a position the server keeps |
The pages run against the shop's vault, --project examples/pipelines/shop.
A program that writes to the lakehouse is shown checked, with the type
hql check prints; a pipeline that ends in target or sink is Step,
the type of a program whose result is what it wrote.
The file silver_replayed is the shop's six valid events as a replay
delivered them: two delivered twice, one corrected after the fact, and one
delivered late.
What is next¶
Part III reads a different kind of data. Chapter 21 opens a folder of documents and asks it the questions chapter 6 asked a table.