Stop Thinking in Rows: Databases Make More Sense as Sets

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.

The set theory behind a query

A set is an unordered collection of distinct elements. x ∈ A means that x belongs to set A; A ⊆ B means that every member of A also belongs to B. Set-builder notation defines membership:

A = { x ∈ U | P(x) }

Read that as “A is the set of members of the universe U for which the predicate P(x) is true.” That is the mathematical shape of a SQL WHERE clause. With NULL, the analogy needs one qualification: SQL keeps a row only when the condition is TRUE; both FALSE and UNKNOWN are discarded.

SELECT *
FROM loan_snapshot
WHERE snapshot_date = DATE '2026-07-31'
  AND delinquency_days >= 30;

If U contains every landed loan observation, the query returns:

DelinquentJuly = { x ∈ U |
                    x.snapshot_date = 2026-07-31
                    and x.delinquency_days >= 30 }

The other basic operations follow naturally:

GROUP BY collapses each group into a result tuple; window PARTITION BY defines calculation groups without collapsing the input. The two forms of difference are not interchangeable: EXCEPT removes duplicates and compares whole rows null-safely, while an anti-join preserves left multiplicity and follows its ON predicate’s null semantics.

In Codd’s relational model, a relation is a set of tuples drawn from named domains:

LoanSnapshot ⊆ LoanId × SnapshotDate × Status × RiskGrade × Balance

Here × is the Cartesian product of the domains; the relation contains only the tuples the data asserts. Relational algebra is closed: each operation returns another relation, so results compose. Inserts add a population, deletes subtract one, and updates replace a selected population with a mapped version.

SQL is set-shaped, not set-pure

The resemblance has limits. Mathematical sets contain no duplicates; SQL normally uses bag, or multiset, semantics. SELECT ALL—the default—does not eliminate duplicate result rows; SELECT DISTINCT does. UNION removes duplicates, while UNION ALL does not. Formal projection deduplicates; an ordinary SQL SELECT list does not.

Sets are unordered, and a query has no guaranteed presentation order without a top-level ORDER BY. In the Spark SQL used here, LAG and LEAD require a window ORDER BY; deterministic predecessor logic also requires expressions that uniquely order each business-key partition. Window ordering controls the calculation, not final presentation. SQL also adds NULL and three-valued logic. Set-based design still requires well-defined keys, explicit duplicate behavior, deterministic ordering, and explicit null handling.

A Type 2 SCD begins with a transition subset

Type 2 slowly changing dimensions are usually taught procedurally: compare the incoming state with the current row, close the old version, and insert the new one. Correct—but the information problem is simpler.

Assume the source sends a complete snapshot each month. A loan might be current in the first two snapshots, 30 days past due in the next two, and current again:

Current, Current, 30 DPD, 30 DPD, Current

History needs only the first observation and each later observation whose meaningful state differs from its predecessor:

Current, 30 DPD, Current

The final Current must remain: this is not DISTINCT over the key and state hash. We remove consecutive repetition, not every state seen before.

Each retained version is valid from its observation time to its successor’s. The last is active because it has no successor. Change detection and interval construction are separate operations.

Freeze-and-squash as set construction

The pattern relies on three persisted state inputs in addition to its MLV outputs:

• S, the append-only source snapshots.
• F, frozen versioned history that preserves closed records and supplies the active boundary state.
• P, a batch manifest with batch_id, committed lower bound τ, immutable upper bound u, and publication status.

Here S, F, and P are ordinary Delta tables in a schema-enabled Fabric Lakehouse, while MLVs 1–4 are Spark SQL MLVs. A notebook or pipeline maintains F and P because MLVs do not support DML. A Warehouse source first needs a table shortcut.

Each source observation has a business key, globally comparable snapshot_version, and snapshot_as_of time. The last two may coincide. Frozen versions add validfrom, validto, isactive, and a deterministic hashdiff over meaningful attributes.

The original pattern used freeze_date as its boundary. A persisted processed-through version, τ, is safer. Before refresh, ingestion records an immutable upper bound, u, through which the source is complete; neither changes during the run. The notation assumes one global version domain. Partitioned sources need a vector of bounds and an authoritative handoff rule for migrating keys. Bootstrap τ explicitly: MAX over an empty table cannot create it.

Across a freeze, no newly admitted observation may have an as-of time at or before the greatest as-of already processed for that business key unless the process reopens history from an earlier anchor. Enforce that with a source ordering contract or a persisted per-key observation-time frontier. Do not infer it from MAX(F.validfrom): squashing deliberately removes unchanged observations. Within one unfrozen window, observations may arrive out of order because MLV 3 sorts them by validfrom.

MLV 1 selects the new working population

The first MLV keeps only source observations in that processing window:

S+ = { s ∈ S | τ < s.snapshot_version ≤ u }

This is a true subset: S+ ⊆ S.

MLV 1 maps those tuples into the working schema, retains snapshot_version for checkpointing, carries snapshot_as_of as validfrom, and computes a deterministic, non-null hashdiff from an unambiguous serialization with fixed type formats, null markers, column order, and length-prefixed or escaped attribute boundaries:

N = map_to_working_schema(S+)

The filter chooses the population. The mapping gives it the schema and comparison key the rest of the chain needs; the chosen hash algorithm must have acceptable collision risk.

MLV 2 supplies the boundary condition

The second MLV selects the active portion of frozen history:

B = { f ∈ F | f.isactive = true }
B' = align_to_working_schema(B)

It combines the aligned baseline with the new population:

W = N ∪ B'

In SQL, this will usually be UNION ALL, so the combined population must contain exactly one authoritative observation at (business_key, validfrom). Every baseline validfrom must precede the post-watermark observations for that key. Resolve same-time corrections by a source-defined precedence rule before LAG; an arbitrary tie-breaker is repeatable, not necessarily correct.

Without the last active state before the boundary, the first post-freeze snapshot has nothing to compare against and always looks like a change. MLV 2 supplies that boundary condition.

MLV 3 keeps only transition points

Within W, pred(x) is the immediately preceding observation for the same business key in the unique validfrom order established above.

The third MLV returns:

C = { x ∈ W |
        pred(x) does not exist
        or x.hashdiff <> pred(x).hashdiff }

Now C ⊆ W. A windowed LAG(hashdiff) compares each tuple with its predecessor; the outer predicate retains the first tuple and every transition. Where a predecessor exists, both hashes are non-null, so <> cannot be UNKNOWN. The first tuple still needs an explicit LAG(hashdiff) IS NULL or ROW_NUMBER() = 1 test.

For a loan moving through the earlier sequence, the result is easy to inspect:

MLV 4 maps transitions into intervals

MLV 4 orders C again by business key and validfrom. It maps each transition to a versioned interval:

• validto is the next transition’s validfrom.
• isactive is true when no next transition exists.

The result uses half-open intervals: [validfrom, validto). A version is valid at its start and ceases to be valid exactly when its successor begins. That produces an unambiguous point-in-time test:

validfrom <= :as_of
AND (validto > :as_of OR validto IS NULL)

These are observation-grain intervals: they date when the source observed a state, not when a change occurred between snapshots, unless the source contract says otherwise.

MLV 4 then takes the union of these recalculated versions and the inactive subset of frozen history:

F- = { f ∈ F | f.isactive = false }
V = F- ∪ close(C)

V is the canonical feed of observed transitions. It is complete if deletions arrive explicitly or cannot occur; deletion-by-absence needs the difference step below. In many models, V already contains the substance of a Type 2 Slowly Changing Dimension, leaving only a surrogate key and naming conventions.

The periodic freeze is the one intentionally imperative step:

F := publish(V, batch_id, u)
P.processed_through := u

Before publishing F, acquire the publisher lease, verify P = τ, and hold the lease through the atomic Delta publication and conditional advance of P to u. A stale writer must abort before altering F. Record batch_id and u in recoverable generation metadata, not only in rows, so an empty feed still has a checkpoint.

The publication write is external to the declarative MLV refresh, although F remains a tracked source dependency. A Fabric Lakehouse cannot atomically commit separate Delta tables F and P, so publish F before advancing P. If that update fails, block the next refresh until recovery reconciles P from the committed generation. Never advance P first: that can strand source versions, while stale P can reopen a window that overlaps the new baseline.

Fabric makes the chain operational, not magically incremental

Fabric Materialized Lake Views became generally available in March 2026. They persist results as Delta tables and record dependencies. A lineage refresh runs the MLVs in its scope in dependency order; cross-lakehouse ordering requires Extended lineage. Incremental refresh is available only to Spark SQL MLVs whose sources are Delta, remain append-only during the cycle, have Change Data Feed enabled on every referenced table or MLV, and use supported SQL. These are eligibility conditions, not a guarantee: Fabric may choose a full refresh when recomputation is cheaper.

LAG and LEAD are window functions, which Microsoft’s optimal-refresh documentation lists as unsupported for incremental refresh. MLVs 3 and 4 therefore cannot refresh incrementally; when their inputs change, they fall back to full refresh. A whole-table overwrite of F is non-append-only, so an MLV reading it falls back to full refresh for that cycle. If MLV 1 obtains a bound through a scalar subquery, a change to the referenced control table also triggers full refresh.

The source filter limits the population passed to the window calculations, but it does not make the whole cycle incremental. A full refresh still recomputes each MLV’s complete defined result; MLV 4 includes inactive history, and publication rewrites the current frozen relation. The pattern avoids replaying every raw snapshot to rediscover transitions. It does not eliminate reading or rewriting historical state.

Stop Asking Which Row Comes Next

The spreadsheet view is useful for inspecting data. It is much less useful for designing database logic.

The freeze-and-squash pattern is a good example. Select the new observations. Add the boundary state. Keep the transition points. Map them into intervals. Preserve the already-closed history. What sounds like a stateful row-processing problem is really a short chain of set operations.

The next time a database process begins turning into a loop, stop asking what should happen to the next row. Write down the population you are starting with, the grain that makes each member distinct, and the rule for membership in the result.

At that point, the SQL is usually the easy part.

Author: Jason Miles

A solution-focused developer, engineer, and data specialist focusing on diverse industries. He has led data products and citizen data initiatives for almost twenty years and is an expert in enabling organizations to turn data into insight, and then into action. He holds MS in Analytics from Texas A&M, DAMA CDMP Master, and INFORMS CAP-Expert credentials. View all posts by Jason Miles