A database table looks like a spreadsheet. That resemblance is useful—and damaging. It makes tables approachable, but encourages a procedural picture: start at the top, inspect a row, make a decision, and move on. That is usually the wrong mental model.
Rows still matter. They have keys, carry facts, and occasionally ruin your afternoon. But the more useful unit of thought is the set: the population of tuples that should exist at a particular stage of processing.
With that shift, SQL operators construct populations. This post develops that mental model, then recasts the EduDataSci freeze-and-squash pattern for a Fabric MLV change feed as small set operations.
The tuple is the unit of assertion; the set is the unit of thought
Suppose a lender receives a monthly snapshot of every commercial loan. “One row per loan per month” states the grain of loan_snapshot, but not its meaning. A better description is:
loan_snapshot is the set of loan-state observations supplied by the servicing system, keyed by loan and snapshot date.
Each tuple asserts a loan’s balance, servicing status, risk grade, and other attributes at a time. “Find the delinquent loans” now defines a subset rather than a row-by-row flagging process. “Find loans missing from the new complete snapshot” defines the difference between two key sets. The database decides how to produce those populations; our first job is to define them correctly.
Continue reading “Stop Thinking in Rows: Databases Make More Sense as Sets”