Since module 4.12, every service owns its data. That is good for the services and bad for a question that the owner of an office asks every month: “How many appointments were booked, how many of them online, and how many quotes went out?” The answer touches appointments, quotes and sales, so several contexts and soon several databases. Lesson 4.12.3 therefore said: reports across all services need an architecture of their own. This module builds it. It begins with the basic distinction of data architecture: transactions and analytics are two different kinds of work, and they want to be stored differently.
A word on technology up front: Python is common in the world of analytics, in Microsoft Fabric too. This course stays with T-SQL and Spark SQL. What you learn here about schemas, loads and decisions does not depend on the language.
Two kinds of load
| OLTP (transactions) | OLAP (analytics) | |
|---|---|---|
| Question | “Is this appointment still free?”, “Create this quote.” | “How many bookings per office and month, compared with last year?” |
| Access | many small reads and writes, a few rows each | a few large reads over many rows and a few columns |
| Model | normalized: every fact in one place, writes without contradictions | denormalized as a star schema: easy to read, fast to sum |
| Storage | by row (rowstore): a row sits together | by column (columnstore): a column sits together and compresses well |
| Time | the current state; old values are overwritten | history: what held when? |
| Who | the Kladde itself, its grains and endpoints | reports, analyses, planning |
The two kinds of load get along badly on the same database. An analysis over a year reads hundreds of thousands of rows and
competes with every booking for memory, processor and locks. Since lesson 4.4.4, the Kladde has READ_COMMITTED_SNAPSHOT on, so
readers no longer block writers; they still compete for resources.