DEV Community: Nariman Baubekov

Parquet, Explained: How One File Format Quietly Won the Data World

Nariman Baubekov — Sun, 06 Sep 2026 12:39:57 +0000

Snowflake queries it. Spark writes it. Trino scans it, Flink lands it, BigQuery federates it, and DuckDB treats a directory of it as a database. Your pandas read_parquet pulls gigabytes per second through it. Cloud object storage is full of it. Recent versions of Excel can even open it.

It's not a database. It's not a query engine. It's not a product anyone sells, and no company owns it. It's a file format — Apache Parquet — and it's the closest thing modern data engineering has to a universal standard. The three big "table format" layers — Delta Lake, Apache Iceberg, and Hudi — all store their data as Parquet files underneath. The entire lakehouse movement is, physically, an enormous pile of Parquet plus opinions.

How does a file format nobody owns become the center of gravity for an entire industry? And why do practitioners who learn its inner workings keep discovering that their storage bills were three times bigger than they needed to be?

This article is the full story: how Parquet works down to structures you can point at in a hex dump, why it became ubiquitous, where it genuinely hurts, and one physical-design trick — sort order — that routinely makes Parquet tables an order of magnitude smaller. That last one sounds like a stunt. By the time we get there, it'll just be arithmetic.

It's 2013, and analytics has a reading problem
Store it sideways
Inside a Parquet file
The life of a query
How the compression sausage gets made
Why it won
The honest cons
When to use it (and when not to)
The trick that sounds fake: sort by cardinality, low to high
The fine print on sort order
More lifehacks in the same spirit
Closing thought

It's 2013, and analytics has a reading problem

It's 2013. Storage has never been cheaper — Hadoop clusters built from commodity disks can hold everything — but reading it hasn't caught up. An analytical query like "average revenue by country" needs three columns out of forty, and yet the formats of the day (CSV, JSON, Avro, SequenceFiles) all store data row by row. To touch the three columns you want, you pay to read all forty.

Meanwhile, the queries are getting bigger. Twitter's analytics team is scanning billions of rows across a cluster and watching half the cluster's throughput carry bytes nobody asked for: usernames and device strings and JSON blobs, streamed off disk only to be immediately discarded.

The row-versus-column idea wasn't new — data warehouses and C-store-style research systems had exploited it for years, and Google's 2010 Dremel paper had just demonstrated columnar storage working at web scale, including a scheme ("repetition and definition levels") for storing nested data — structs, arrays, maps — column by column without shredding it into mush. What was missing was an open, general-purpose file format that brought all of it to the Hadoop ecosystem.

So in 2013, engineers from Twitter and Cloudera built one. The lore says the name comes from parquetry — the wood-floor pattern where slats lie side by side — which is either a beautiful coincidence or exactly the right metaphor, because that's precisely how it stores your columns. Parquet joined the Apache Software Foundation, and the rest is a very quiet, very complete victory.

One framing note before we go deeper: Parquet is a file format, and nothing more. Not a database, not an engine, not a query planner, not a table. It's a meticulously specified way to lay bytes down on disk so that any engine — Spark, Trino, Snowflake, DuckDB, pandas — can read them back efficiently. That humility turns out to be a big part of the answer to "why did it win," and we'll return to it.

Store it sideways

Everything about Parquet starts from one decision: store data by column instead of by row.

Take this tiny events table — our running example for the article:

name	os	country	amount
Alice	iOS	DE	42
Bob	iOS	US	17
Cara	Android	DE	8
Dan	iOS	DE	23

A row-oriented format (CSV, JSON, the heap files of your favorite OLTP database) stores it exactly as printed: one row after another, all columns of a row adjacent. That's great when you want Dan's entire row — it's one contiguous read. It's terrible when you want the average of amount, because you have to wade past every name, os, and country byte to fish out the values you care about.

Analytical queries almost always touch some columns of lots of rows — SELECT country, AVG(amount) ... GROUP BY country — so flipping the layout pays twice:

You read less. Want two columns out of forty? Read two column chunks. The other thirty-eight never leave disk.
You compress better. Values of the same type and same domain now sit next to each other, and "things that look alike compress alike" is the oldest trick in the information-theory book. A column of country codes is a sea of repetition; the same codes scattered between names and timestamps are noise.

Column-orientation is the idea. The next two sections are the engineering.

Inside a Parquet file

Parquet files are built from four nested structures, and their names come up constantly in tuning guides, so they're worth knowing cold:

Row group — a horizontal slice of the table (all columns, a batch of rows). This is the unit of parallelism and of I/O: one worker per row group, and — this matters for later — the entire row group is typically processed by one task. Most writers default to something around 128 MB or a million rows per group.
Column chunk — within a row group, each column's values are stored contiguously. This is the unit of column pruning: skip a column, skip its whole chunk.
Page — column chunks are sliced into pages of roughly 1 MB. This is the unit of encoding and compression, and the smallest thing a reader decodes.
Footer — the metadata mother lode: the full schema, the offsets of every row group and column chunk, which encodings and compression each chunk uses, and per-chunk statistics (min, max, null count, distinct count).

And here's the layout of the file itself, byte by byte:

The file starts and ends with the magic bytes PAR1, so you can identify one from a mile away. Everything important is at the end: to open a Parquet file, a reader grabs the last 8 bytes (footer length + magic), then range-requests exactly the metadata it needs, then range-requests exactly the column chunks it needs. On object storage like S3, where a ranged GET is cheap and a full scan is not, this layout is native-level friendly.

Two more structural superpowers worth flagging:

Splittability. Row-group boundaries are recorded in the footer, so a big file can be split across many workers with zero coordination — no "unzip the whole 40 GB gzipped CSV on one node" era nostalgia.
Self-description. The schema, the types, the encodings, the statistics — it's all inside the file. Hand a Parquet file to a tool that has never seen your data stack, and it knows what it's looking at. (This is also what the table formats build on — more on that in a minute.)

The life of a query

Here's the payoff diagram — the life of an analytical query against a Parquet-based lake, using our events table:

Every one of those steps is the format doing work your query engine would otherwise have to do expensively, with data it would otherwise have to load. Engines pair this with vectorized execution — decoding columns straight into dense, SIMD-friendly batches — which is why "just point DuckDB at a directory of Parquet files" is a legitimate analytics strategy in 2026.

Notice how much of this diagram runs on those footer statistics. Remember that; it's about to become load-bearing, twice.

How the compression sausage gets made

Parquet's size reductions come from two layers working in series — specialized encodings first, general-purpose compression second:

Dictionary encoding: each page's distinct values go into a small dictionary, and the column becomes a stream of tiny integer indices into it. Your 2-byte country strings become 1-byte numbers before any "compression" has even happened.
Run-length encoding (RLE): consecutive repeats collapse into (value, count) pairs. This is the encoding that cares about sort order, and therefore the encoding doing the heavy lifting in the trick at the end of this article.
Bit-packing: when values have no runs, they're packed at minimum bit width (3 distinct OSes ≈ 2 bits each).
Delta encodings (for integers, dates, sorted keys): store differences between consecutive values instead of the values. Sorted IDs have small gaps; shuffled IDs have giant ones.
Then zstd / snappy / gzip squeezes whatever is left, finding repeated byte patterns across everything above.

The important mental model: these are pattern-finders. Dictionary encoding exploits few distinct values. RLE exploits adjacent equal values. Delta exploits monotonic sequences. zstd exploits repeated byte patterns. A column only gets compressed to the extent its physical arrangement exhibits one of those patterns — and sort order is the one lever you control that changes which patterns exist, without changing the data's meaning at all.

Hold onto that sentence. It's the key to the last third of this article.

Why it won

You'll notice this article hasn't claimed Parquet is objectively the best at any single thing. It doesn't have ORC's pedigree of Hive-native optimization; a bespoke binary format could be smaller or faster for any specific workload. Its dominance comes from a more interesting place: it's excellent at everything analytical workloads need, open, and — critically — everywhere.

The properties that got it there:

It's open and neutral. Apache-licensed, vendor-owned by no one. In a market terrified of lock-in, "your bytes are yours" is a strategy.
It decouples storage from compute. Because it reads beautifully over cheap object storage with ranged GETs, your data can live on S3 while Snowflake, Trino, and DuckDB all take turns querying it. The lakehouse architecture — the dominant pattern of the last decade — is basically unthinkable without a format like this.
Network effects. Every engine reads it because every other engine reads it. New tools ship Parquet support on day one to be relevant. That flywheel, not technical superiority, is the moat — but it's a very real moat. (Just ask ORC, which is excellent and still lives mostly in Hive-land.)
It's the substrate of the table formats. Delta Lake, Apache Iceberg, and Hudi — the three big "SQL table" layers over lakes — all store their data as Parquet files, adding ACID transactions, schema evolution, and time travel on top. The "new standard" is literally built out of the old one.
It's cheap to keep. Three-to-ten-times smaller than the equivalent CSV/JSON is the typical range, before you've applied a single trick from this article. Storage is a recurring cost; good layout is a one-time effort.
It's fast to query for the reasons in the query-path diagram: column pruning, row-group pruning, predicate pushdown, splittable parallelism.

A quick aside for the Arrow-curious: Apache Arrow is Parquet's in-memory cousin — same columnar religion, designed for zero-copy in-process work rather than on-disk storage. The two are designed to round-trip each other cheaply, and the pairing is why "read Parquet into Arrow, compute vectorized, write Parquet back" is the default circulation of the modern data stack. (If you've never watched a pandas read_parquet hit multiple GB/s via Arrow, it's worth trying.)

The honest cons

A dominant format with no downsides would be suspicious. Here's where Parquet genuinely hurts:

Point lookups and OLTP are not its job. Fetching one full row means seeking into every column chunk to reassemble it. Row formats exist precisely because transactional workloads want rows. Use Postgres for your orders table; use Parquet for your analytics over the orders table.
Writes are expensive and files are immutable. A writer must buffer an entire row group, encode it, compress it, and write it with its footer. There's no "update row 4,077" — Parquet files are write-once. Updates and deletes are why Delta/Iceberg/Hudi exist: they rewrite files for you and keep a transaction log straight.
The small-files problem. Thousands of tiny Parquet files (the natural byproduct of frequent streaming writes) wreck performance: metadata overhead per file, footer round-trips per file, task scheduling chaos, S3 request charges. This is such a consistent foot-gun that "compaction" is a core feature of every table format.
Not human-readable. You can't cat it, can't eyeball it in a text editor, can't email it to a business analyst who opens it in Notepad. (The world is softening — recent Excel can import Parquet, and a quick duckdb -c "select * from file.parquet limit 5" gets you a peek in seconds, not minutes — but CSV's universal readability remains unmatched.)
Overkill for small data. A 5,000-row lookup table shipped with your app? A config file? Just use CSV or JSON. Parquet's machinery pays off at scale and is pure ceremony below it.
Randomness doesn't compress. GUID columns, uniformly random floats, already-compressed blobs (images, encrypted data): columnar layout and dictionaries have nothing to say about entropy. Don't expect magic, and don't bother compressing binary blobs a second time.
Deeply nested, repeated data can be slow to read back. Repetition/definition levels are elegant for storage, but reconstructing a forest of nested structs row by row costs CPU. Very nested Parquet read into very row-oriented code is where the format's reputation for "slowness" actually comes from.
Schema evolution is limited. Adding nullable columns at the end: fine. Renaming, reordering, or changing types: prepare for pain (or a table format's versioning machinery).

None of these are scandalous. They're the predictable trade-offs of a format optimized for scanning billions of rows, touching a few columns, from cheap shared storage. It's a sprinter complaining about its swimming.

When to use it (and when not to)

Format	Reach for it when
Parquet	Analytical data at any scale beyond "fits in a spreadsheet"; data lakes and lakehouses; interchange between engines; long-term storage of query-able data
CSV	Human-facing handoffs, tiny datasets, maximum tool compatibility
JSON (lines)	APIs, semi-structured streaming events, documents with wildly varying shape
Avro	Row-oriented streaming pipelines with schema-registry needs (Kafka ecosystems)
ORC	Deep Hive-land; otherwise Parquet's ecosystem gravity usually wins
A real database	OLTP, concurrent transactions, point updates, enforceable constraints

The trick that sounds fake: sort by cardinality, low to high

Here's a stunt. Take a wide events table — the kind with a few low-cardinality dimension columns (device_operating_system: 3 values; country: ~250 values; maybe app_version, event_name, locale...) and some high-cardinality ones (user_id: millions). Write it to Parquet in the order the events arrived. Note the file size — call it 30 GB.

Now rewrite the exact same rows, sorted by (device_operating_system, country, user_id). Same schema, same row count, same everything — SQL results are byte-for-byte identical. The file lands under 3 GB.

Nothing was deleted. Nothing was lossy-compressed. The data just lies down differently. Reductions of this shape — 10x and sometimes much more — are real and reproducible, and this section will get you to the point where you can predict which of your tables has this hiding inside them. It comes down to two facts you already learned:

Parquet compresses a column by finding patterns among adjacent values.
Sort order is the one lever that changes which patterns exist.

Step 1: Meet your columns as Parquet sees them

With dictionary encoding, each low-cardinality column becomes a stream of tiny integers — the dictionary indices. What does that stream look like?

Unsorted data (rows arrive in event order, i.e., chaos):

os column, as dictionary indices:      1 0 1 2 0 0 1 2 1 0 0 2 1 0 1 1 2 0 ...
country column, as dictionary indices: 83 17 42 0 91 17 3 88 42 17 91 0 55 ...

Equal values are almost never adjacent. RLE finds no runs, so each column falls back to bit-packing: roughly 2 bits per row for os, roughly 8 bits per row for country — forever, no matter what. On a billion-row table, that's ~250 MB for the OS column and ~1 GB for the country column, before zstd shrugs at the noise. And user_id, with millions of distinct values? Its dictionary overflows and the writer quietly falls back to storing raw 8-byte IDs — random, unordered, essentially incompressible.

Sorted by (os, country, user_id):

os column:      0 0 0 0 0 0 ... 0 | 1 1 1 1 1 ... 1 | 2 2 2 ... 2
country column: 0 0 0 ... 0 | 1 1 1 ... 1 | ... (sorted runs inside every os block)
user_id column: locally sorted, ascending, inside every (os, country) block

Now every encoding in the pipeline has something to eat:

os collapses to 3 runs. Three (value, count) tuples. The column that cost 250 MB now costs bytes.
country becomes at most ~750 long runs (250 values inside each of 3 OS blocks). The 1 GB column now costs kilobytes.
user_id is locally sorted, so delta encoding turns it into a stream of small gaps instead of random 64-bit noise.

And then zstd, arriving last, finds every page monotonous instead of chaotic, and gets dramatically better matches on everything — including the payload columns you didn't sort by, because rows that share (os, country) tend to correlate on app version, event type, locale, and friends. That compounding across all columns at once is where order-of-magnitude reductions come from.

Step 2: Why lowest cardinality first, specifically?

This is the part people memorize without deriving, so here's the derivation. It's short.

When data is sorted by keys (k1, k2, ..., kn), a column can only collapse into long runs if the columns before it in the sort have a small combined number of distinct combinations. Formally: column j ends up with at most

runs(column j)  ≤  C(k1) x C(k2) x ... x C(kj)

distinct runs, where C() is cardinality, capped by the row count. The total storage for the sort-key columns is roughly the sum of prefix products, and the last term is C1 x C2 x ... x Cn — which is identical no matter how you permute the keys. So you minimize the sum by minimizing the earlier terms, which means putting the smallest cardinalities first. Ascending order is provably the best of the simple orderings.

Concretely, with our three columns (cardinalities 3, 250, ~300M) on a billion rows:

Sort order	`os` runs	`country` runs	`user_id` state
`(os, country, user_id)`	3	≤ 750	locally sorted → delta-friendly
`(country, os, user_id)`	≤ 750	250	locally sorted → delta-friendly
`(os, user_id, country)`	3	~1 billion (catastrophe)	locally sorted
Unsorted	~1 billion	~1 billion	random noise

Two things jump out:

The truly catastrophic ordering is the third one — interleaving a high-cardinality column between two low-cardinality ones. Once user_id comes before country, every (os, user_id) pair is nearly unique, so country degenerates back to one value per row and pays bits-per-row again. Keep your low-cardinality dimensions adjacent, at the front — that's the sharpest cliff in the whole landscape.
The difference between the first two rows is real but modest: swapping (os, country) for (country, os) mostly moves a few hundred runs between two columns that are both nearly free either way. The big wins are (a) sorting at all, and (b) keeping the low-cardinality cluster together at the front. Ascending order is the safe default that also wins the math, so use it — just don't expect a further 10x from re-swapping two adjacent low-card columns.

Honest calibration: the 30x-and-beyond stunts happen on wide, dimension-heavy tables — a dozen scattered low-cardinality string dimensions all collapsing at once. Typical gains from adding a thoughtful sort are more like 2x–10x on storage, which is still, conservatively, free money. And the trick does nothing for pure entropy: a table of GUIDs and random floats has no patterns to expose, no matter how you sort it.

Step 3: There's a second, hidden prize — data skipping

Remember the footer statistics? Sorted data doesn't just compress better; it prunes better. If os is the first sort key and the table has 100 row groups, each row group contains essentially one OS value, so its min = max = 'iOS' (or 'Android', or...). A WHERE os = 'iOS' filter can now skip two-thirds of the file without reading a byte of it. DuckDB's Parquet tips call this out explicitly: sort by your frequently-filtered columns, and row-group min/max stats become a homemade index. Sort keys are the gift that keeps giving — once per compression, again per query.

Step 4: Do it

The canonical pattern — partition by time, sort by ascending cardinality within each partition — in DuckDB:

COPY (
    SELECT *
    FROM 'raw_events/'
    ORDER BY device_operating_system, country, user_id
) TO 'events/'
(FORMAT parquet, PARTITION_BY (event_date), COMPRESSION zstd, ROW_GROUP_SIZE 1_000_000);

And in Spark, one crucial refinement — you don't need a global sort. Compression happens inside each file's row groups, so sorting within each partition is sufficient, and sortWithinPartitions gets you that without the cluster-wide shuffle of a full orderBy:

(df.sortWithinPartitions("device_operating_system", "country", "user_id")
   .write
   .partitionBy("event_date")
   .option("compression", "zstd")
   .save("s3://lake/events/"))

Neither operation changes the data. Every downstream SELECT returns identical results. You've simply packed the suitcase better — same clothes, half the suitcase.

The fine print on sort order

One honest caveat before you ascend-cardinality-sort every table you own: the compression-optimal sort is not always the query-optimal sort.

If your hot queries are WHERE event_date BETWEEN ... time-range scans, putting event_date early in the sort (or partitioning by it — say, a directory per day) gives dramatically better pruning than burying it last. The canonical resolution of this tension is the pattern from Step 4: partition by the range-filtered column, sort by ascending cardinality within partitions. Partitioning handles the time dimension; the sort handles compression and dimension filtering. If you can't partition, a pragmatic hybrid is (your_most_filtered_column, lowest-card column, ..., highest-card column) — sacrifice a little compression for a lot of skipping.

Rules of thumb, in descending priority:

Sort by something. Unsorted is the expensive state.
Partition (or lead the sort with) the column your queries range-filter on most — usually time.
Keep low-cardinality dimensions adjacent, at the front of the sort.
Within that, ascending cardinality.
Never interleave a high-cardinality column between low-cardinality ones.

More lifehacks in the same spirit

The sort trick has siblings. All of them are "arrange the bytes so the format's machinery has something to eat."

1. Switch from Snappy to Zstd. Snappy became the default when CPU was scarcer than disk; in 2026, Zstd at a moderate level typically lands 20–30% smaller at comparable read speeds. One word in your writer options. It stacks multiplicatively with the sort trick.

2. Kill your small files (and small row groups). Every Parquet file pays footer metadata, and every row group truncates runs — a thousand 5 MB files hold a thousand tiny, badly-compressed fragments of what should be long runs. Target files in the 128 MB–1 GB range with row groups big enough for runs to actually form (DuckDB's default row-group size is 122,880 rows; their guidance is at least as many row groups per file as you have threads reading it, which biases toward somewhat larger files). If streaming ingestion gives you a swarm of small files nightly, run compaction — it's the sort trick's best friend, because compaction + re-sort is exactly the "rewrite the table neatly" operation.

3. Use Parquet v2 data pages — but check your data shape first. The format's newer encodings — DELTA_BINARY_PACKED for integers and timestamps, BYTE_STREAM_SPLIT for floats — live in v2 data pages, and on the right data they're dramatic: DuckDB's own best-case numbers show up to 99% size reduction on a cleanly monotonic integer sequence. That's the best case, though, not the typical one. DuckDB's own issue tracker documents the opposite result on medium-entropy data — values that repeat but aren't monotonically increasing — where DELTA_BINARY_PACKED can make a file ~3x larger than v1, because it turns repeated values into effectively random-looking deltas that compress worse than the raw values would have. That's one of two real reasons DuckDB doesn't default to writing v2 yet — the other being that some reader engines still can't parse it.

The rule of thumb: v2 is close to a free win on sorted IDs, timestamps, and counters — test before flipping it on for anything else. In DuckDB it's COPY ... TO 'f.parquet' (FORMAT parquet, PARQUET_VERSION v2).

4. Store real types. Dates as DATE instead of '2026-08-27' strings, 3-value flags as dictionary-encoded booleans or tiny ints instead of 'YES'/'NO' strings, IDs as integers instead of zero-padded strings. Types are compression decisions: the string version of every value fights your dictionary, your bit-packing, and your min/max statistics all at once. (Strings-as-types also quietly breaks statistics pruning — min over '10' and '9' is meaningless.)

5. Turn on the skipping machinery for high-cardinality lookups. Min/max statistics can't help with WHERE user_id = 42 on a shuffled billion-row table (42 is plausibly in every row group's range). Two features fix exactly this: bloom filters (a compact "definitely not in this chunk" probabilistic summary — writers like Spark and Iceberg can emit them per column) and the page index (page-level min/max stats, enabling skips within chunks). Enable both for your favorite equality-filtered high-cardinality columns.

6. Diagnose before you tune. Don't guess where the bytes are — look. DuckDB will show you every column chunk's encodings, compression, and sizes straight from the file:

SELECT path_in_schema, encodings, compression,
       total_uncompressed_size, total_compressed_size
FROM parquet_metadata('events/partition_date=2026-08-01.parquet')
ORDER BY total_compressed_size DESC;

If the user_id column dwarfs everything, think delta encodings and sort position. If a low-card column is huge, it wasn't sorted when written. If encodings says PLAIN on a column that should be dictionary-encoded, its dictionary overflowed — another sign sorting (or better typing) is needed:

Thirty seconds of this beats an afternoon of folklore.

Closing thought

Parquet won not by being magical but by being well-mannered: open, self-describing, columnar, splittable, compressible, and readable by everyone. It stores your data sideways, tells readers what it knows, and gets out of the way.

And once you see that its entire compression story is "find patterns among adjacent values," a whole shelf of lifehacks stops being folklore and becomes arithmetic. Sort low-to-high cardinality. Keep the little dimensions together. Partition by what you filter, sort by what you group. Use Zstd and v2 pages and real types. Don't drown in small files. None of these change your data — they change how it lies down on disk, and Parquet is ferociously opinionated about lying down.

Somewhere in your data lake is a table that's three times bigger than it needs to be, wearing a trench coat. You now know exactly how to take the coat off.

Further reading

The Apache Parquet format specification — readable, surprisingly short, and the encodings section will click now that you know why they exist.
Google's Dremel paper (2010) — where columnar-at-scale and nested-data-via-levels were proven out.
DuckDB's Parquet tips — the practical writer-side guidance quoted in the lifehacks section. Their Parquet encodings post and bloom filters post are excellent companions.
Your favorite table format's docs (Iceberg, Delta, Hudi) — note how each one is, at bottom, a very clever pile of Parquet files plus a log.

Kimball's Last Hard Problem: When There Is No Right Grain

Nariman Baubekov — Sun, 06 Sep 2026 08:29:05 +0000

Three people walk into the same board meeting with three different numbers for the same deal, and all three are right.

Sales says $648,000 — the full three-year contract Tabby just signed with PetCo Org, a vet chain rolling out cat trackers across 12 locations. Finance says $18,000 — the revenue actually recognized so far, because most of those locations haven't gone live yet and you can't book revenue for service you haven't delivered. Customer Success says 8 of 12 — not a dollar figure at all, but the fraction of locations actually onboarded and using the product, which is what predicts whether this account renews. Nobody in that room is lying, confused, or bad at their job. They're answering three different questions that happen to sound like the same question.

Every article in this series so far has had a textbook answer once you found the right technique — grain, SCD2, accumulating snapshots, bridge tables. This one doesn't. It's the case the first four were building toward: what do you actually do when the "correct" grain depends entirely on who's asking, and picking one is itself a decision you have to be able to defend.

The 60-second recap

Grain = the exact definition of one fact row. Different real questions at different grains get different fact tables — a rule this series has invoked at every stop: the coffee shop's order lines vs. daily snapshots, Tabby's lifecycle vs. monthly MRR, fulfillment's orders vs. shipments, claims vs. diagnosis bridges.
SCD Type 2 preserves history so a fact always joins to the dimension row that was true when the event happened.
Accumulating snapshots update one row per entity across milestones; periodic snapshots take regular photos; semi-additive measures can be summed across some dimensions but not across time.
Bridge tables resolve genuine many-to-many relationships without inventing numbers through a naive join.

What none of the last four articles said out loud: every fact table in this series has shared the same dim_date, and most have shared the same dim_account. That's not incidental — it's the thing that makes what happens in this article possible at all, and it finally gets a name below.

Meet the conflict

Tabby (Part 2's cat-collar SaaS company) just closed PetCo Org — a 12-location vet chain, three-year contract, $648,000 total contract value, $18,000/month once every location is fully live. The contract was signed two months ago. Locations are onboarding in waves: 4 went live in month one, 4 more in month two, the remaining 4 are scheduled for month three.

Three teams need a number for this deal, and none of them are asking for the same thing:

Stakeholder	Question they're actually asking	Their answer
Sales	"How much did we sell, for commission and pipeline purposes?"	$648,000 — full contract value, credited at signature
Finance	"How much revenue have we actually earned so far?"	$18,000 — ratable recognition, only for location-months of service actually delivered
Customer Success	"Is this account actually succeeding, right now?"	8 of 12 locations active — not a dollar figure at all

Look at the units before anything else: two dollar figures thirty-six times apart, and a fraction that isn't a dollar figure. Forcing these into one number isn't hard because the math is hard — it's a category error, the same way "what's the average of a distance and a color" is a category error. There is no arithmetic that turns $648,000, $18,000, and 67% into one honest figure, because they were never measuring the same thing.

Why each one is correct, on its own terms

Sales is correct because commission and pipeline reporting exist to measure and reward the act of closing the deal — the moment the contract was signed is the event, and its full value is the relevant fact, regardless of how long delivery takes. Waiting three years to credit a three-year deal would break sales compensation entirely.

Finance is correct because revenue recognition rules exist specifically to prevent booking revenue for service not yet delivered — recognizing $648,000 today would overstate the company's earnings by the entire undelivered two years and ten months of the contract, which is not a rounding error, it's the difference between real and fictional financial statements.

Customer Success is correct because renewal risk tracks with product usage and onboarding health, not with contract value or accounting timing — a fully-recognized, fully-paid contract where nobody's using the product is a churn risk regardless of what Finance's ledger says.

None of these is a rougher approximation of one true number. They're three different, equally precise answers to three different questions.

What breaks if you force one grain to answer all three

It's worth actually trying each option, because the failure modes are the argument, not just an assertion:

Force everything to contract grain (Sales' shape). One row per contract, $648,000. Finance now has no way to recognize revenue ratably — the number either overstates earned revenue on day one or requires bolting a second, contradictory recognition schedule onto a table whose whole point was "one clean number per deal." Customer Success has nothing at all — a contract-grain table has no concept of "location," so activation tracking doesn't exist in this model.

Force everything to subscription-month grain (Finance's shape, and it already exists — fact_subscription_month from Part 2). Sales' $648,000 evaporates — a monthly snapshot only ever shows revenue for months that have already been recognized, so on day one of a three-year deal, this view of the world shows nothing, which is exactly backwards from what commission tracking needs. Customer Success again has no location-level detail, because the grain is subscription-month, not subscription-location-month.

Force everything to location-activation grain (Customer Success's shape). Now there's no way to attach a dollar figure at all without allocating the $648,000 across 12 locations and however many months — which reintroduces the exact allocation problem Part 4 spent an entire article on, for a question (activation health) that never needed a dollar figure to begin with.

Every single-grain option doesn't just make one team's job harder — it makes their question structurally unanswerable inside that model, not just inconvenient.

The resolution: three fact tables, one shared dimension

The fix isn't a fourth, cleverer grain. It's building all three, and linking them through a dimension every one of them can reference — without any of them referencing each other.

CREATE TABLE dim_contract (
    contract_sk           BIGSERIAL PRIMARY KEY,
    contract_id           TEXT NOT NULL,
    account_sk            BIGINT NOT NULL REFERENCES dim_account(account_sk),
    signed_date           DATE NOT NULL,
    term_months           INT NOT NULL,
    total_contract_value  NUMERIC(12,2) NOT NULL,
    location_count        INT NOT NULL,
    sales_rep_sk          INT REFERENCES dim_employee(employee_sk)
);

Sales gets fact_booking — one row per signing event, referencing dim_contract:

CREATE TABLE fact_booking (
    booking_sk    BIGSERIAL PRIMARY KEY,
    contract_sk   BIGINT NOT NULL REFERENCES dim_contract(contract_sk),
    account_sk    BIGINT NOT NULL REFERENCES dim_account(account_sk),
    booked_date_sk INT NOT NULL REFERENCES dim_date(date_sk),
    booking_type  TEXT NOT NULL,   -- NEW / RENEWAL / AMENDMENT / UPSELL
    booked_value  NUMERIC(12,2) NOT NULL
);

Finance keeps using fact_subscription_month from Part 2 — no new table needed, just a new foreign key added so it can be tied back to the deal that produced it:

ALTER TABLE fact_subscription_month
    ADD COLUMN contract_sk BIGINT REFERENCES dim_contract(contract_sk);

Customer Success gets fact_location_activation — an accumulating snapshot in the same style as Part 3's fulfillment milestones, one row per location within the contract:

CREATE TABLE fact_location_activation (
    activation_sk               BIGSERIAL PRIMARY KEY,
    contract_sk                 BIGINT NOT NULL REFERENCES dim_contract(contract_sk),
    location_sk                 BIGINT NOT NULL REFERENCES dim_location(location_sk),
    scheduled_activation_date_sk INT REFERENCES dim_date(date_sk),
    actual_activation_date_sk    INT REFERENCES dim_date(date_sk),
    current_status               TEXT NOT NULL   -- SCHEDULED / ACTIVE / DELAYED
);

Notice what's not here: no fact table has a foreign key into another fact table. fact_location_activation doesn't reference fact_booking; fact_subscription_month doesn't reference fact_location_activation. They're linked only through dim_contract — a conformed dimension, the formal name for exactly this pattern. It's the same idea dim_date has been quietly doing since the first article's role-playing order-date/pickup-date trick: the same dimension, referenced by multiple fact tables at different grains, is what lets you query across them without ever needing them to share a grain, or even be joinable to each other directly.

The three "what's the number" queries

-- Sales: bookings
SELECT SUM(f.booked_value) AS bookings
FROM fact_booking f
JOIN dim_contract c ON c.contract_sk = f.contract_sk
WHERE c.contract_id = 'PETCO_2026_01';
-- $648,000

-- Finance: recognized revenue to date
SELECT SUM(f.mrr) AS recognized_revenue_to_date
FROM fact_subscription_month f
JOIN dim_contract c ON c.contract_sk = f.contract_sk
WHERE c.contract_id = 'PETCO_2026_01'
  AND f.month_sk BETWEEN 20260101 AND 20260228;
-- $6,000 (month 1, 4 locations) + $12,000 (month 2, 8 locations) = $18,000

Worth a pause on that second query: summing mrr across two consecutive months for the same subscription is only meaningful because it's being read as "revenue recognized," not as "MRR." The exact same column, summed the exact same way, would be nonsense if the question were "what's the account's MRR across January and February" — you wouldn't add two run-rates together and call it a run-rate. Part 3 named this problem for a count that couldn't be summed across time at all; here it's sharper still — the same number is fully additive under one interpretation and meaningless under another, depending on which question you're actually asking of it. That's worth remembering any time a measure gets summed across a date range: check what you're claiming the sum means, not just whether the arithmetic runs.

-- Customer Success: activation
SELECT
    COUNT(*) FILTER (WHERE current_status = 'ACTIVE') AS locations_active,
    COUNT(*) AS locations_total
FROM fact_location_activation f
JOIN dim_contract c ON c.contract_sk = f.contract_sk
WHERE c.contract_id = 'PETCO_2026_01';
-- 8 of 12

Three queries, same dim_contract, three genuinely different answers — none of them wrong, none of them reconcilable into each other by any transformation, because they were never the same measurement.

The governance move: name the metrics, don't blend them

The technical fix is three fact tables. The organizational fix — the part that actually prevents this from becoming a recurring argument — is refusing to let anyone build a dashboard that presents these as one number. The way to do that concretely:

-- An executive summary view: three named, defined metrics, never blended
SELECT 'Bookings (Sales)' AS metric,
       '$648,000' AS value,
       'Full 3-year contract value, credited at signature' AS definition
UNION ALL
SELECT 'Recognized Revenue to Date (Finance)',
       '$18,000',
       'Ratable revenue for location-months of service actually delivered'
UNION ALL
SELECT 'Location Activation (Customer Success)',
       '8 of 12 (67%)',
       'Locations live and actively using the product as of today';

This isn't a workaround for not having a single number — it is the answer. "Bookings," "Recognized Revenue," and "Activation Rate" become three named, documented metrics in whatever metrics glossary or semantic layer the company uses, each with an owner and a definition, structurally incapable of being confused for one another because they're never presented as the same field. The failure mode this prevents isn't a technical one — it's someone building a dashboard that labels a column just "Revenue" and quietly picks whichever of the three numbers happens to be sitting in whatever table they joined to first.

A note on the whole series: conformed dimensions were always the point

Every fact table across all five articles in this series — fact_order_line, fact_subscription_month, fact_order_lifecycle, fact_claim_line, and the three built above — has shared the same dim_date. Most have shared dim_account or its equivalent. That repetition wasn't incidental; it's the single idea that makes a dimensional model more than a collection of unrelated tables.

A conformed dimension is a dimension built once, with one consistent set of keys and attributes, and reused across every fact table that needs it. It's what lets a coffee shop's daily sales and a SaaS company's MRR waterfall both be sliced by "quarter" using the exact same dim_date, and it's what let three fact tables at three irreconcilable grains sit next to each other in this article without contradiction. The grain of a fact table answers "what is one row." The conformed dimensions answer the question this whole series has actually been building toward: how do a dozen fact tables, at a dozen different grains, built at different times by different teams, still add up to one coherent model instead of a pile of disconnected spreadsheets with SQL in front of them.

Common mistakes

Treating "which number is right" as a technical question. It's a scope question — right for what audience, right for what decision. The fix is naming the metric precisely enough that "right" stops being ambiguous, not searching harder for a formula that reconciles $648,000 and $18,000.
Building the dashboard leadership asked for instead of the one that's honest. "Just give me one number for the deal" is a request that will get answered whether or not you push back — better to hand over three clearly labeled numbers than one blended, quietly wrong one that leadership will eventually catch and stop trusting.
Letting a fact table reference another fact table instead of a shared dimension. It's tempting to just point fact_location_activation.booking_sk at fact_booking directly — it even works, mechanically. It also means the two tables' futures are now coupled for no reason: if bookings ever need a second grain (amendments as their own rows, say), every downstream reference into the old grain has to be found and fixed. Route through the dimension.
Summing a measure across time without checking what the sum means. The mrr-as-revenue example above: right when read as recognized revenue, meaningless when read as a run-rate. The column doesn't tell you which one you're doing — you have to know the question.
Resolving the conflict once, informally, in a meeting, and not writing it down. Whatever gets agreed about which team owns which metric needs to live in a metrics glossary, not in the memory of whoever was in that board meeting — the alternative is having the same argument again in two quarters with different people in the room.

Exercises

This is the last set in the series, and there's no answer key for the first one — that's the point.

1. A professional services company bills clients by the hour but pays consultants a fixed salary. The PM wants project profitability tracked by project phase (discovery, build, delivery). Finance wants it by invoice, since that's what's actually billed and collectible. Resourcing wants it by consultant-week, since that's what determines who's overbooked next month. Design the fact tables. Which dimension conforms all three?

Hint — not a full solution, on purpose
Start by writing each stakeholder's actual question in one sentence, the way this article did for Sales/Finance/CS, before touching a schema. If you can't state the question precisely, you can't design the grain for it. A conformed dim_project or dim_engagement is a likely candidate — but defend it against at least one alternative before you commit, the way this article walked through what breaks under each single-grain option.

2. Revisit Part 4's healthcare claims model. Sales credits an insurance broker for signing a new employer group; Finance recognizes premium revenue ratably over the policy period; a Care Management team tracks which specific members within the group have actually completed onboarding health screenings. Sketch the three fact tables and the dimension that conforms them, following the pattern in this article.

Hint
This is structurally the same shape as the PetCo Org case — a group-level commitment (the employer contract), a ratable financial recognition, and a member-level activity tracker — with dim_employer_group or an equivalent doing the same job dim_contract did here.

Closing: the whole series in one paragraph

Five articles, one underlying argument: a dimensional model isn't a diagram of your data, it's a set of decisions about what one row means, made explicitly enough that two people looking at the same fact table agree on what it's telling them. Grain is the first and most important of those decisions. SCD2 handles the decision changing over time. Accumulating snapshots handle a decision that takes multiple steps to resolve. Bridge tables handle a decision that legitimately has more than one right answer within a single fact row. And this article handles the case where the decision doesn't have one right answer at all — where the correct move isn't picking, it's building enough separately-grained, honestly-labeled fact tables, conformed through shared dimensions, that nobody has to lie to get an answer. That's the actual skill underneath all the SQL: not knowing the four fact table types, but knowing which one — or which three — a real, messy, human question actually needs.

Resources

Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — chapter 4 covers conformed dimensions and the bus matrix directly; it's the concept this entire series has been resting on since Part 1's dim_date.
Kimball Group — Conformed Dimensions — the canonical short reference.

That's the series. If you read all five, you now have the fundamentals, the SaaS extensions, late-arriving facts, many-to-many relationships, and the judgment call none of the others could hand you a formula for. The companion repo has the full schema and exercises for all five parts, start to finish. Go build something someone else's team can actually trust.

Kimball for Many-to-Many: Bridge Tables, Weighting Factors, and the Diagnosis Code Problem

Nariman Baubekov — Sun, 06 Sep 2026 08:27:28 +0000

Everything so far in this series has been one-to-many, and cleanly so: one order to many order lines, one account to many months, one order to many shipments. Every fact row had exactly one of each dimension it referenced. That assumption holds until it doesn't — a single insurance claim can carry three diagnosis codes, a bank account can have two joint owners, a sales transaction can be attributed to more than one promotion at once. None of those are edge cases to shrug off; they're a genuinely different relationship shape, and modeling them like a one-to-many relationship produces numbers that are quietly, confidently wrong.

This is the pattern Ralph Kimball himself used a healthcare example to introduce, for good reason — it's where the problem is most obvious and the fix is most instructive.

The 60-second recap

Fact tables hold events. Transaction, periodic snapshot, accumulating snapshot (updated in place across milestones), factless (no measures — row existence is the fact).
Grain = the exact definition of one fact row, stated as a sentence, before anything else. Different real questions at different grains get different fact tables.
SCD Type 2 preserves history with valid_from/valid_to/is_current rows, so a fact always joins to the dimension row that was true when the event happened.
Part 3 added late-arriving fact handling and named semi-additive measures — summable across some dimensions, not across time.

New for this article: dimensions that are legitimately multivalued for a single fact row, and the specific trap that shows up the moment you try to sum a dollar amount through one.

Meet Meadowlark Health

Meadowlark Health processes insurance claims. The detail that makes this article necessary: a single claim routinely carries more than one diagnosis code (ICD-10 codes — E11.9 for Type 2 diabetes, I10 for hypertension, and so on), because patients frequently present with more than one condition at a visit. The fact table Meadowlark needs is fact_claim_line — one row per billed service line on a claim — and every one of those rows can legitimately be about two or three diagnoses at once, not one.

That's the whole problem in one sentence: the fact-to-diagnosis relationship is many-to-many, and a standard dimensional model has no native way to express that.

The fixed-width hack, again

The instinctive fix looks exactly like the one flagged as a mistake in the fulfillment article, just wearing a different column name:

-- Don't do this
CREATE TABLE fact_claim_line (
    claim_line_sk    BIGSERIAL PRIMARY KEY,
    claim_id         TEXT,
    member_sk        BIGINT REFERENCES dim_member(member_sk),
    provider_sk      BIGINT REFERENCES dim_provider(provider_sk),
    service_date_sk  INT REFERENCES dim_date(date_sk),
    diagnosis_1_sk   INT REFERENCES dim_diagnosis(diagnosis_sk),
    diagnosis_2_sk   INT REFERENCES dim_diagnosis(diagnosis_sk),
    diagnosis_3_sk   INT REFERENCES dim_diagnosis(diagnosis_sk),
    billed_amount    NUMERIC(10,2)
);

This breaks for the same structural reason shipment_2_carrier did in the last article: it's a fixed guess at a variable-length list. A claim with a fourth diagnosis has nowhere to go. WHERE diagnosis_1_sk = :code OR diagnosis_2_sk = :code OR diagnosis_3_sk = :code has to be repeated at every column, forever, and gets silently wrong the day someone adds a diagnosis_4_sk and forgets to update every query that predates it.

The bridge table

Kimball's own name for the fix, when he first wrote it up using this exact example, was the Diagnosis Group table — what's generally called a bridge table today. Instead of the fact row pointing at diagnosis codes directly, it points at a group, and the group resolves to however many diagnoses actually apply:

CREATE TABLE fact_claim_line (
    claim_line_sk       BIGSERIAL PRIMARY KEY,
    claim_id            TEXT,
    member_sk           BIGINT REFERENCES dim_member(member_sk),
    provider_sk         BIGINT REFERENCES dim_provider(provider_sk),
    service_date_sk     INT REFERENCES dim_date(date_sk),
    diagnosis_group_sk  INT NOT NULL REFERENCES bridge_diagnosis_group(diagnosis_group_sk),
    billed_amount       NUMERIC(10,2)
);

CREATE TABLE bridge_diagnosis_group (
    diagnosis_group_sk  INT NOT NULL,
    diagnosis_sk        INT NOT NULL REFERENCES dim_diagnosis(diagnosis_sk),
    weighting_factor     NUMERIC(4,3) NOT NULL,  -- fractions of this claim attributable to this code, summing to 1.0 per group
    PRIMARY KEY (diagnosis_group_sk, diagnosis_sk)
);

One diagnosis_group_sk can resolve to one row in the bridge (a claim with a single diagnosis) or several (a claim with three). Adding a fourth diagnosis to a future claim needs nothing more than another bridge row with the same group key — no schema change, no new column, no query that has to be found and patched.

Why the weighting factor exists: you cannot just join and sum

Here's the trap the bridge table alone doesn't save you from. Say claim line CL_1001 has billed_amount = 500.00 and three diagnoses in its group. Join the fact straight to the bridge and group by diagnosis:

-- Wrong: naive join, no weighting
SELECT d.diagnosis_code, SUM(f.billed_amount) AS total_billed
FROM fact_claim_line f
JOIN bridge_diagnosis_group b ON b.diagnosis_group_sk = f.diagnosis_group_sk
JOIN dim_diagnosis d           ON d.diagnosis_sk = b.diagnosis_sk
WHERE f.claim_line_sk = :cl_1001
GROUP BY d.diagnosis_code;
-- E11.9: $500.00
-- I10:   $500.00
-- Z79.4: $500.00   <- $1,500 conjured out of a $500 claim

The join fans one fact row out into three rows — one per bridge match — and billed_amount comes along unchanged on each of them. Sum across diagnoses and you've invented a thousand dollars that never existed. This is the exact failure mode every source on this pattern warns about, and it's not a hypothetical: it's what happens the first time anyone builds this query without knowing the bridge is there.

The weighting factor exists to fix exactly this — multiply, don't just sum:

-- Correct: multiply by the weighting factor before summing
SELECT d.diagnosis_code, SUM(f.billed_amount * b.weighting_factor) AS allocated_billed
FROM fact_claim_line f
JOIN bridge_diagnosis_group b ON b.diagnosis_group_sk = f.diagnosis_group_sk
JOIN dim_diagnosis d           ON d.diagnosis_sk = b.diagnosis_sk
WHERE f.claim_line_sk = :cl_1001
GROUP BY d.diagnosis_code;
-- E11.9: $250.00  (0.5 * 500)
-- I10:   $150.00  (0.3 * 500)
-- Z79.4: $100.00  (0.2 * 500)

Now the three rows sum back to exactly $500. As long as every group's weighting factors sum to 1.0, this generalizes cleanly to SUM(billed_amount * weighting_factor) across every claim at once, grouped however you like by diagnosis attributes — the allocation math holds up in aggregate, not just for one claim examined by hand.

Where the weighting factor breaks

This is worth stating plainly rather than leaving implicit, because it's the part that isn't obvious until it bites: the weighting factor correctly answers "how much is attributable to diagnosis X," but it does not correctly answer "how much is attributable to claims with both diagnosis X and diagnosis Y." Ask for the combined total across two specific codes together, and the weighted sum double-counts any claim that carries both — because each code's allocated share was computed independently, not jointly. There's no clean fix inside the weighting-factor pattern itself for that specific question; it requires a different query shape (typically: find the claims meeting both conditions first via EXISTS/INTERSECT, then sum their full billed_amount once, unweighted). If your organization asks combination-of-codes questions often, that's a sign the weighting factor alone won't cover everything you need, not that it's implemented wrong.

One more legitimate variant worth knowing: you can deliberately drop the weighting factor if what you actually want is an impact report — "total billed amount touched by any contagious diagnosis," where a claim with two contagious codes intentionally counting twice reflects "this much billing activity involved a contagious condition" rather than "this much money was caused by it." That's a real, valid report shape. It just has to be labeled as one, clearly, so nobody downstream mistakes an intentionally inflated total for a financial figure.

Not every bridge needs a weighting factor

Meadowlark also has group health plans where one policy covers multiple dependents — another genuine many-to-many, member-to-policy this time instead of claim-to-diagnosis. But nobody's summing a dollar amount across dependents the way they sum billed amount across diagnoses; the question is usually just "who's covered under this policy," a membership list, not an allocation.

CREATE TABLE bridge_policy_member (
    policy_sk BIGINT NOT NULL REFERENCES dim_policy(policy_sk),
    member_sk BIGINT NOT NULL REFERENCES dim_member(member_sk),
    relationship TEXT NOT NULL   -- SUBSCRIBER / SPOUSE / DEPENDENT
    -- no weighting_factor: nothing numeric fans out through this bridge
);

The distinction worth keeping straight: a weighting factor is only needed when a numeric measure from the fact table would otherwise double-count as it fans out through the bridge. A bridge used purely to list or filter membership — no fact-table measure passing through it — doesn't need one. Don't add a weighting factor out of habit; add it when there's a SUM() that would otherwise lie.

Kimball's own bank-account example follows the same membership shape as the policy/dependent case — multiple customers jointly owning one account — and it comes with a related wrinkle worth flagging: a bridge table often needs to sit on top of Type 2 dimensions on both sides. If a dependent is added to a policy mid-year, or a diagnosis code's own description gets revised by a coding-standard update, the bridge row needs to point at whichever dimension row (policy_sk, diagnosis_sk) was actually valid on the date the relationship applied — the same "as-of" logic from the SCD2 sections of Parts 1 and 2, just one join further away.

Factless facts, revisited: a bridge without any fact at all

Two earlier articles used factless facts for coverage/eligibility — Bean & Stalk's drink availability, Tabby's feature entitlements. Meadowlark has a version that's also genuinely many-to-many: which providers are in-network for which plan, as of which date. A provider can be in-network for several plans; a plan covers several providers. No dollar amount is attached to the relationship itself — it either holds on a given day or it doesn't:

CREATE TABLE fact_network_coverage (
    coverage_sk       BIGSERIAL PRIMARY KEY,
    provider_sk        BIGINT NOT NULL REFERENCES dim_provider(provider_sk),
    plan_sk            BIGINT NOT NULL REFERENCES dim_plan(plan_sk),
    effective_date_sk  INT NOT NULL REFERENCES dim_date(date_sk),
    UNIQUE (provider_sk, plan_sk, effective_date_sk)
    -- factless: the row's existence is the fact
);

This isn't a bridge table in the strict Kimball sense — it's a factless fact that happens to resolve a many-to-many relationship on its own, because neither side needs to be the fact table's single grain-defining dimension. Worth noticing that "bridge table" and "factless fact" are answers to two different questions — how do I represent a multivalued dimension attached to a fact versus how do I record that something was true without a number attached — and they can combine, as they do here, without either one being a special case of the other.

A real query

Total billed amount by diagnosis category, correctly allocated, for claims in the last quarter:

SELECT
    d.diagnosis_category,
    SUM(f.billed_amount * b.weighting_factor) AS allocated_billed
FROM fact_claim_line f
JOIN bridge_diagnosis_group b ON b.diagnosis_group_sk = f.diagnosis_group_sk
JOIN dim_diagnosis d           ON d.diagnosis_sk = b.diagnosis_sk
JOIN dim_date dt                ON dt.date_sk = f.service_date_sk
WHERE dt.quarter = 3 AND dt.year = 2026
GROUP BY d.diagnosis_category
ORDER BY allocated_billed DESC;

Members covered under a given policy, as of today (membership bridge, no allocation needed):

SELECT m.member_name, bp.relationship
FROM bridge_policy_member bp
JOIN dim_member m ON m.member_sk = bp.member_sk
WHERE bp.policy_sk = :policy_sk;

Notice the second query never touches a weighting factor at all — a plain join is correct here, because nothing numeric is fanning out. That contrast is the whole lesson of this article in two queries.

Common mistakes

Joining fact to bridge and summing without multiplying by the weighting factor. The single most common way this pattern gets implemented wrong — the join looks completely correct, the query runs without error, and the total is simply too large. Nothing about the SQL signals the bug; only the number does.
Adding a weighting factor to a bridge that doesn't need one. A pure membership bridge with a weighting_factor column invites someone to multiply by it out of habit, which — if it doesn't sum to something meaningful per group — introduces a new bug in the other direction.
Asking a combination-of-codes question against a weighted single-code answer. Covered above: "billed for X" and "billed for X and Y together" are different questions, and the weighting factor only correctly answers the first one.
Forgetting the fixed-width hack is the same mistake as shipment_2_carrier. Any time a schema has _1, _2, _3 suffixed columns for "as many as we've seen so far," that's a bridge table that hasn't been built yet.
Pointing a bridge at a Type 1 dimension when history matters. If policy membership or diagnosis descriptions change over time and the bridge points at the current row regardless of when the relationship applied, historical reports quietly use today's data for yesterday's events — the same SCD1-where-you-needed-SCD2 mistake from every earlier article in this series, one hop further away.

Exercises

Hints hidden; full solutions in solutions.sql in the companion repo.

1. A claim line has a single diagnosis. Does it still need a diagnosis_group_sk pointing at the bridge table, or can it point directly at dim_diagnosis?

Hint
Consider what happens to every downstream query if some claim lines use one pattern and others use a different one. A single-diagnosis claim can be modeled as a group of size one — same bridge, same query shape, weighting_factor = 1.0. Consistency usually wins over the minor storage savings of a special case.

2. Write the query for the "impact report" described above: total billed amount touched by any claim carrying a diagnosis in the "contagious" category, intentionally double-counting claims with more than one such diagnosis. Label the output so it can't be mistaken for the allocated total.

Hint
Join fact to bridge to dim_diagnosis, filter to the contagious category, and sum billed_amount unweighted — same shape as the first "wrong" query in this article, except this time the double-counting is the intended output, not a bug. Alias the column something like impact_billed_amount_do_not_reconcile_to_gl to make the intent unmissable.

3. Write the query that correctly answers "total billed for claims carrying both E11.9 and I10," avoiding the weighting-factor trap described in this article.

Hint
Find the set of diagnosis_group_sk values present in the bridge for both codes (an INTERSECT or a self-join with a HAVING COUNT(DISTINCT diagnosis_sk) = 2), then sum the fact table's billed_amount unweighted for claim lines in that set — once per claim, not once per diagnosis.

What's next

Every pattern so far — transaction facts, periodic snapshots, accumulating snapshots, factless facts, bridge tables — has had a textbook-correct answer once you knew which tool fit. Part 5, the last in this series, doesn't. It's a deliberately unresolved case: three stakeholders, three defensible grains, three different numbers, and no answer in any book. That's where the actual judgment this series has been building toward gets tested.

Resources

Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — the original source for the Diagnosis Group / weighting-factor pattern used throughout this article.
Kimball Group — Multivalued Dimensions and Bridge Tables — the canonical short reference, including the Type 2 dimension interaction noted above.
Kimball Group — Design Tip #166: Potential Bridge Table Detours — the usability and over-counting caveats covered in this article, from the source.

Kimball for Order Fulfillment: Milestones, Split Shipments, and Facts That Arrive Late

Nariman Baubekov — Sun, 06 Sep 2026 08:23:19 +0000

An order looks like it has two dates: placed, and delivered. Model it that way and the first split shipment breaks the table, the first late webhook corrupts the status column, and the first "how many orders are in transit right now" question has no good answer. An order isn't an event. It's a process — and a process with branches, delays, and messages that don't always arrive in the order they were sent.

The first article in this series covered the fundamentals through a coffee shop; the second applied them to SaaS subscriptions. Both dealt with processes that are, underneath the complexity, well-behaved: a loyalty journey has one milestone after another, a subscription has one plan at a time. This article is about the less well-behaved case — multi-stage processes where the stages can fork, arrive out of sequence, or simply take a while to all finish. Order fulfillment is the canonical example, and it's where accumulating snapshots earn their keep for real.

The 60-second recap

Fact tables hold events (verbs). Transaction (atomic, append-only), periodic snapshot (regular photos), accumulating snapshot (multi-stage journeys, one row per entity, updated in place as milestones happen), factless (coverage/eligibility, no measures).
Dimension tables hold context (nouns) — who/what/where/when.
Grain = the exact definition of one fact row. State it in a sentence before you build anything. If two real questions need two different grains, build two fact tables — don't force one table to answer both.
SCD Type 2 preserves history: a new row per change, with valid_from/valid_to/is_current, so historical facts join to the dimension row that was true then, not today.

New for this article: what happens when the "multi-stage journey" doesn't stay linear, and what to do when a measure genuinely can't be summed across time.

Meet Crate Expectations

Crate Expectations sells furniture and home goods online. The fulfillment shape that makes this article worth writing:

Orders often contain items stocked in different warehouses — a couch from the East warehouse, a lamp from the West one. One order, two shipments, as a matter of routine, not exception.
Some items are backordered and picked days after the rest of the order.
Shipping is handled by two carriers (FastFreight and RoadRunner Parcel), each with its own webhook API notifying Crate Expectations of pickup, transit, and delivery events.
Carrier webhooks are not reliable messengers: they retry, they queue behind rate limits, and — the part that actually breaks a naive model — they don't always arrive in the order the underlying events happened.

That last point is the spine of this article.

The grain fight: order, shipment, or package?

Before any table, the usual question: what's one row?

One row per order. Matches how the customer thinks about it. Clean for "how many orders did we place last month," "average time from order to delivery." Breaks the moment an order becomes two shipments — which date goes in shipped_date, the first one or the last one? What if one shipment is delivered and the other is still backordered — is the order "delivered"?
One row per shipment. Matches how the warehouse and carriers think about it. A shipment has one warehouse, one carrier, one tracking number, one ship date, one delivery date — no ambiguity. Breaks for order-level questions: "how many orders" now requires COUNT(DISTINCT order_id), and anything about the customer's experience of the order (did they get everything, was anything late) needs to look across all of an order's shipments.
One row per package. Maximally granular — a shipment can itself split into multiple boxes. Overkill for almost every question Crate Expectations actually asks; the operational systems track it, the warehouse doesn't need it in the analytical layer.

Same lesson as the SaaS article, applied again: if order-level and shipment-level questions are both real and both common, build both fact tables. Don't pick one grain and force the other question to contort around it.

Crate Expectations builds the first two. Package-level detail stays in the operational system; nobody's asked a question that needs it in the warehouse.

`fact_order_lifecycle`: the accumulating snapshot

One row per order, milestone columns, updated in place as the order progresses — same pattern as the coffee shop's loyalty journey and Tabby's trial→paid→churn, just with more stages and (as you'll see shortly) a genuine reason those stages can misbehave.

CREATE TABLE fact_order_lifecycle (
    lifecycle_sk           BIGSERIAL PRIMARY KEY,
    order_sk                BIGINT NOT NULL REFERENCES dim_order(order_sk),
    customer_sk             BIGINT NOT NULL REFERENCES dim_customer(customer_sk),
    placed_date_sk          INT NOT NULL REFERENCES dim_date(date_sk),
    payment_confirmed_date_sk INT REFERENCES dim_date(date_sk),
    picked_date_sk          INT REFERENCES dim_date(date_sk),
    packed_date_sk          INT REFERENCES dim_date(date_sk),
    first_shipped_date_sk   INT REFERENCES dim_date(date_sk),
    all_delivered_date_sk   INT REFERENCES dim_date(date_sk),
    current_status          TEXT NOT NULL,   -- see the status ladder below
    shipment_count          INT NOT NULL DEFAULT 0,
    is_split_shipment       BOOLEAN NOT NULL DEFAULT false
);

Notice first_shipped_date_sk and all_delivered_date_sk, not shipped_date_sk and delivered_date_sk. That naming is doing real work: a split order doesn't have a ship date, it has one per shipment, so the order-level fact can only honestly report the first and the last. Anything more precise than that belongs in fact_shipment, not here — a good sign you're respecting the grain fight instead of quietly ignoring it.

Late-arriving facts: when the webhook lies about order

Here's the problem that doesn't show up in a well-behaved pipeline. Crate Expectations' carriers send webhook events — PICKED, PACKED, SHIPPED, DELIVERED — and those events are supposed to update fact_order_lifecycle as they happen. Two things go wrong in practice:

Retries duplicate events. A carrier's webhook fires, Crate Expectations' endpoint is briefly down, the carrier retries the same SHIPPED event six hours later. Applying it twice should be harmless — but only if the update logic is written to expect it.
Events arrive out of the order they occurred. A PICKED event queued behind a rate limit can land after the SHIPPED event for the same order, because the carrier's own systems processed and sent them out of sequence. If the update logic just does "set current_status to whatever the latest webhook says," the order's status can visibly regress from SHIPPED back to PICKED — which is not just wrong, it's wrong in a way that makes the dashboard look broken to whoever's watching it.

The fix isn't to compare arrival time, and it isn't quite enough to compare the event's own timestamp either — carrier clocks skew, and a stale retried event can carry an old timestamp that still looks superficially valid. The robust fix is to stop treating current_status as "whatever the last message said" and start treating it as the furthest point reached in a known, ordered pipeline:

CREATE TABLE fulfillment_status_rank (
    status TEXT PRIMARY KEY,
    rank   INT NOT NULL
);

INSERT INTO fulfillment_status_rank (status, rank) VALUES
    ('PLACED', 1),
    ('PAYMENT_CONFIRMED', 2),
    ('PICKED', 3),
    ('PACKED', 4),
    ('SHIPPED', 5),
    ('DELIVERED', 6);

Then every incoming event does two separate things — fill in its own milestone date unconditionally, but only advance current_status if the event represents genuine forward progress in the pipeline:

-- Applying one incoming event: (order_sk, event_status, event_date_sk)
UPDATE fact_order_lifecycle f
SET
    picked_date_sk        = CASE WHEN :event_status = 'PICKED'
                                  THEN COALESCE(f.picked_date_sk, :event_date_sk)
                                  ELSE f.picked_date_sk END,
    packed_date_sk         = CASE WHEN :event_status = 'PACKED'
                                  THEN COALESCE(f.packed_date_sk, :event_date_sk)
                                  ELSE f.packed_date_sk END,
    first_shipped_date_sk  = CASE WHEN :event_status = 'SHIPPED'
                                  THEN LEAST(COALESCE(f.first_shipped_date_sk, :event_date_sk), :event_date_sk)
                                  ELSE f.first_shipped_date_sk END,
    current_status = CASE
        WHEN (SELECT rank FROM fulfillment_status_rank WHERE status = :event_status)
             > (SELECT rank FROM fulfillment_status_rank WHERE status = f.current_status)
        THEN :event_status
        ELSE f.current_status
    END
WHERE f.order_sk = :order_sk;

Two design decisions worth calling out:

Milestone dates fill in whenever they arrive, regardless of order. A late PICKED event still records when picking actually happened — that's genuinely useful data (it's what a "time from pick to pack" report needs), even though it arrived after SHIPPED already updated the status. COALESCE means the first value to arrive for a given milestone wins and a duplicate retry can't overwrite it with a different date.
current_status only ever moves forward, compared by pipeline rank, not arrival time or event timestamp. A duplicate or a late-arriving earlier-stage event can update its own milestone column without ever being able to drag the visible status backward.

This is the general shape of "late-arriving facts" in Kimball terms: the fix is almost never about buffering or re-ordering events before they land — it's about making the update logic correct regardless of what order things arrive in, because in any system with retries, queues, or multiple upstream senders, you cannot actually guarantee delivery order.

`fact_shipment`: the finer grain the order-level fact can't give you

"Which carrier is slowest?" and "how many packages did warehouse East ship last month?" are shipment-grain questions that fact_order_lifecycle structurally cannot answer once orders split. A second accumulating snapshot, one row per shipment:

CREATE TABLE fact_shipment (
    shipment_sk       BIGSERIAL PRIMARY KEY,
    order_sk          BIGINT NOT NULL REFERENCES dim_order(order_sk),
    warehouse_sk      INT NOT NULL REFERENCES dim_warehouse(warehouse_sk),
    carrier_sk        INT NOT NULL REFERENCES dim_carrier(carrier_sk),
    packed_date_sk    INT REFERENCES dim_date(date_sk),
    shipped_date_sk   INT REFERENCES dim_date(date_sk),
    delivered_date_sk INT REFERENCES dim_date(date_sk),
    package_count     INT NOT NULL DEFAULT 1,
    current_status    TEXT NOT NULL
);

Every shipment event updates fact_shipment with the same rank-based logic above, unconditionally reliable regardless of arrival order. fact_order_lifecycle's first_shipped_date_sk/all_delivered_date_sk and shipment_count are then derived — MIN(shipped_date_sk), MAX(delivered_date_sk), COUNT(*) — from fact_shipment grouped by order_sk, kept in sync by whatever process applies shipment events. Two facts, two grains, one truth, each question answered at the grain that actually fits it.

Semi-additive measures and the question a snapshot alone can't answer

Here's a question in the same shape as "what was MRR on any given day" from the SaaS article, which a plain periodic snapshot can't cleanly answer either: how many orders were in transit — shipped but not yet fully delivered — at the end of each day?

This measure has a name worth knowing: it's semi-additive. A count like "orders in transit" is meaningful to sum across things that exist at the same instant (add up in-transit orders across every warehouse right now, and you get a real number: total orders currently moving). It is not meaningful to sum across time — Monday's in-transit count plus Tuesday's doesn't produce anything interpretable, because it's mostly the same orders being counted twice. Contrast with daily_revenue in the coffee shop's snapshot fact, which is fully additive: summing it across 30 days correctly gives you the month's revenue. Not every measure in a snapshot fact behaves the same way when you aggregate it, and treating a semi-additive measure as if it were fully additive is a quiet, easy-to-miss error — nothing throws an exception, the number just means something different than whoever's reading the dashboard assumes.

There are two honest ways to answer "in transit as of day X":

Reconstruct it from the accumulating snapshot, the same "as-of" logic used for SCD2 attributes in the earlier articles, just applied to a milestone range instead of a validity range:

SELECT COUNT(*) AS orders_in_transit
FROM fact_order_lifecycle
WHERE first_shipped_date_sk <= :as_of_date_sk
  AND (all_delivered_date_sk IS NULL OR all_delivered_date_sk > :as_of_date_sk);

This works, costs nothing to set up, and is exactly right for ad hoc questions or a handful of dates.

Or materialize a periodic snapshot — fact_fulfillment_daily with one row per (date, warehouse, status) and a count — for the same three reasons the coffee shop and SaaS articles gave for building snapshots instead of always recomputing: a dashboard querying 5 years of daily in-transit counts across every warehouse shouldn't reconstruct all of it from milestone ranges on every load; a snapshot freezes what was true as of that day even if fact_order_lifecycle keeps changing; and the query becomes SELECT ... FROM fact_fulfillment_daily WHERE ... instead of a range-comparison scan. Same tradeoff as always — build the snapshot once the reconstruction query gets asked often enough to matter.

Either way: when you build that snapshot, don't add a SUM(daily_in_transit) chart across a date range and call it a meaningful total. That's the single most common way a semi-additive measure gets misused once it exists.

A real query

Average days from order placed to fully delivered, split by whether the order shipped from one warehouse or split across multiple:

SELECT
    f.is_split_shipment,
    COUNT(*)                                                    AS order_count,
    AVG(d_delivered.full_date - d_placed.full_date)             AS avg_days_to_deliver
FROM fact_order_lifecycle f
JOIN dim_date d_placed    ON d_placed.date_sk = f.placed_date_sk
JOIN dim_date d_delivered ON d_delivered.date_sk = f.all_delivered_date_sk
WHERE f.all_delivered_date_sk IS NOT NULL
GROUP BY f.is_split_shipment;

Slowest carrier by average shipment transit time, at the shipment grain where that question actually lives:

SELECT
    c.carrier_name,
    AVG(d_delivered.full_date - d_shipped.full_date) AS avg_transit_days,
    COUNT(*)                                          AS shipment_count
FROM fact_shipment s
JOIN dim_carrier c        ON c.carrier_sk = s.carrier_sk
JOIN dim_date d_shipped    ON d_shipped.date_sk = s.shipped_date_sk
JOIN dim_date d_delivered  ON d_delivered.date_sk = s.delivered_date_sk
WHERE s.delivered_date_sk IS NOT NULL
GROUP BY c.carrier_name
ORDER BY avg_transit_days DESC;

Try either against a raw event log of carrier webhooks directly and you're reconstructing state from scratch every time you run it. Against these two facts, both are a handful of lines.

Common mistakes

Modeling "shipped" and "delivered" as single dates on the order. Fine until the first split shipment, then silently wrong for every split order afterward — usually discovered when someone notices the numbers don't match the carrier's own dashboard.
"Latest webhook wins" status logic. The most natural-looking implementation and the one that lets status visibly move backward the first time an event arrives out of order. Rank the pipeline, compare ranks, not timestamps or arrival order.
Not guarding against duplicate events. Carriers retry. If applying the same SHIPPED event twice can push a date forward a second time or double-count something downstream, the update logic isn't idempotent yet.
Summing a semi-additive measure across time and presenting it as a total. "Orders in transit" summed across 30 days is not "total orders shipped this month" — it's a number that looks plausible and means nothing. If a measure can't be summed across the fact's own grain-defining dimension, say so next to it.
Forcing one fact table to serve both order-level and shipment-level questions. Padding fact_order_lifecycle with shipment_2_carrier, shipment_2_ship_date columns for the second shipment is a fixed-width hack that breaks the moment an order has three shipments. Build fact_shipment.

Exercises

Hints hidden; full solutions in solutions.sql in the companion repo.

1. A DELIVERED webhook arrives for a shipment that has no prior SHIPPED event on file — the shipped notification appears to have been lost entirely, not just delayed. What should the update logic do, and why is this a different case from ordinary out-of-order arrival?

Hint
The rank-based status update still works (DELIVERED outranks whatever's currently on file). But shipped_date_sk would stay NULL forever unless you backfill it — consider inferring a shipped date from context (e.g., the delivered date minus typical transit time) versus just leaving the gap and flagging the row for review. There's a real tradeoff between a clean-looking dataset and an honest one.

2. Write the periodic snapshot version of fact_fulfillment_daily and the query that populates one day's rows from fact_order_lifecycle.

Hint
One row per (date, warehouse, status), COUNT(*) of orders matching the as-of reconstruction query from this article, grouped by warehouse. Populate it once per day as a scheduled job, the same way fact_daily_sales gets populated in Part 1.

3. Why is shipment_count stored directly on fact_order_lifecycle instead of always being computed with COUNT(*) FROM fact_shipment WHERE order_sk = ... at query time?

Hint
Same performance-vs-recompute tradeoff that motivated periodic snapshots in the first place — cheap to store, expensive to keep re-deriving on every query that touches order-level counts. The cost is that it can drift if the process updating fact_shipment doesn't also update the count; consider what would keep them in sync.

What's next

Everything so far has been one-to-one or one-to-many in a clean, hierarchical way: one order to many shipments, one account to many months. Part 4 breaks that assumption with bridge tables — the pattern for genuine many-to-many relationships, using healthcare claims (one claim, several diagnosis codes) as the running example, and the double-counting trap that shows up the moment you fan a dollar amount out across a bridge without thinking about weights.

Resources

Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — the source for late-arriving fact handling as a named, documented pattern, not something this article invented.
Kimball Group — Late Arriving Fact — the canonical short reference for the general pattern this article's out-of-order webhook handling is one instance of.

The companion repo has the schema, seed data, and exercises for this part. Part 4 is next.

Kimball for SaaS: Subscriptions, MRR, and Churn, Modeled Right

Nariman Baubekov — Sun, 06 Sep 2026 08:19:50 +0000

SaaS data is weird.

A customer pays you before they use the thing. Then they use it some unpredictable amount. Then they maybe upgrade. Then they churn. Then — sometimes — they come back three months later and you have to decide whether that's "reactivation" or "new." Finance wants recognized revenue one way, customer success wants NRR another way, and your CEO wants a single number for "ARR" that nobody can quite agree on.

The dimensional modeling fundamentals from the first article in this series (the coffee shop one) still apply. But SaaS breaks them in interesting ways. A transaction fact alone can't answer "what was this account's MRR last March?" — because MRR isn't an event, it's a state that changes over time.

This article is about the patterns SaaS actually needs. We'll meet a fictional company, walk through each pattern, and by the end you'll have a dimensional model that can answer the hard SaaS questions without re-deriving them every time.

The 60-second recap (if you skipped the coffee shop)

Fact tables hold measurable events (verbs). Four flavors: transaction (atomic events), periodic snapshot (regular photos), accumulating snapshot (multi-stage journeys, updated in place), factless (coverage/eligibility).
Dimension tables hold descriptive context (nouns): who/what/where/when.
Grain = the precise definition of what one fact row represents. State it out loud before building.
Star schema (flat dims, one hop from fact) beats snowflake almost always.
SCD Type 2 preserves history by inserting a new row with valid_from/valid_to/is_current when an attribute changes. Type 1 overwrites. Type 2 is the workhorse.

Got it? Good. Now let's apply it to SaaS.

Meet Tabby

Tabby sells IoT collars that track cats' location, activity, and naps. Yes, this is a real product category. The business model:

Subscription plans: Free (1 collar, basic stats), Pro ($12/mo, 3 collars, full history), Enterprise (custom, many collars, API access).
Usage-based add-on: beyond your plan's collar limit, each active collar is $4/mo.
Trial: every account gets 14 days of Pro for free, then auto-downgrades to Free unless they add payment.
Accounts can have workspaces (think: a multi-cat household, or a small vet clinic with several "rooms"), and workspaces have users.
Hierarchies: some Enterprise customers are "organizations" with multiple sub-accounts (a vet chain with 12 locations).

The dimensional model needs to handle all of that, plus reconstruct MRR as-of any historical date. Let's build it.

The subscription grain problem

Before any SQL, the most important decision: what is one row in the subscription fact?

Three candidates:

One row per subscription. A subscription is a billing relationship between an account and a plan. Pros: tiny, matches the source system. Cons: can't represent plan changes over time without a second table — and where does MRR history live?
One row per subscription-month. Each month a subscription is active, it gets a row with that month's MRR. Pros: trivial MRR queries — SUM(mrr) per month. Cons: one row per sub per month means 12× the rows per year per customer, and you have to materialize new rows monthly.
One row per invoice line. Most granular billing event. Pros: ties directly to revenue recognition. Cons: a mid-month upgrade produces two invoice lines, and "what was the MRR?" becomes a rolling calculation.

There is no universally right answer. Tabby uses two of these:

An accumulating snapshot at one row per subscription for the lifecycle (trial → paid → churn — see below).
A periodic snapshot at one row per subscription-month for MRR history.

Why both? Because they answer different questions. The lifecycle fact answers "how long do trials take to convert?" The monthly snapshot answers "what was MRR last March?" Trying to make one table answer both leads to grain mixing — the most common SaaS modeling sin.

The rule: if two questions need different grains, build two fact tables. Don't be a hero.

Account dimension & hierarchy

SaaS entities usually nest. For Tabby: account → workspace → user. Plus an optional parent account for organizations that own multiple sub-accounts (the vet chain case).

Two modeling choices:

Flatten the hierarchy onto dim_account (account_id, parent_account_id, workspace_count, user_count). One table, denormalized. Easier queries, slight staleness on counts.
Snowflake into dim_account, dim_workspace, dim_user with FKs. More normalized, more joins, but the counts are always live.

For Tabby we keep all three as separate Type 1 dimensions — they're genuinely different entities with their own attributes — but we also flatten the parent relationship onto dim_account so org-rollup queries are one hop:

CREATE TABLE dim_account (
    account_sk       BIGSERIAL PRIMARY KEY,
    account_id       TEXT NOT NULL,                  -- natural key
    account_name     TEXT NOT NULL,
    parent_account_id TEXT,                          -- for org rollups (NULL = top-level)
    plan_id          TEXT NOT NULL,                  -- SCD2-tracked (see below)
    plan_name        TEXT NOT NULL,
    signup_date      DATE NOT NULL,
    -- SCD Type 2 columns:
    valid_from       DATE NOT NULL,
    valid_to         DATE,
    is_current       BOOLEAN NOT NULL,
    UNIQUE (account_id, valid_from)
);

Note plan_id and plan_name are on the account dimension with SCD2 columns. This is the canonical SCD2 case — when an account upgrades Pro → Enterprise, we close out the Pro row and open an Enterprise row. More on that next.

dim_workspace and dim_user stay Type 1 (they reference the account but don't carry plan info themselves):

CREATE TABLE dim_workspace (
    workspace_sk BIGSERIAL PRIMARY KEY,
    workspace_id TEXT NOT NULL UNIQUE,
    account_id   TEXT NOT NULL,         -- denormalized for one-hop joins
    workspace_name TEXT NOT NULL,
    created_date DATE NOT NULL
);

CREATE TABLE dim_user (
    user_sk      BIGSERIAL PRIMARY KEY,
    user_id      TEXT NOT NULL UNIQUE,
    workspace_id TEXT NOT NULL,
    email        TEXT NOT NULL,
    role         TEXT NOT NULL,          -- OWNER / ADMIN / MEMBER
    created_date DATE NOT NULL
);

When to conflate, when to separate. If workspaces were purely a UI grouping with no attributes of their own, you'd flatten them onto dim_account. They have their own created-date, name, and (later) usage, so they earn their own dimension. The same logic applies to "do I need a dim_plan?" — Tabby has only three plans and they rarely change, so plan attributes live denormalized on dim_account. If plans had rich attributes (feature flags, rate limits, regional availability), they'd get their own SCD2 dimension.

SCD Type 2 in depth: plan changes are THE case

Here's the central SaaS data problem. An account named "Whisker Labs" was on Pro from January to June, then upgraded to Enterprise in July. You need to answer:

"What was Whisker Labs' MRR in March?" → Pro price.
"What's their MRR now?" → Enterprise price.
"What plan were they on when they hit support ticket #4471?" → Depends on the date.

If dim_account overwrote plan_id in place (SCD1), every historical query would silently use today's plan. That's the bug. SCD2 fixes it.

The data looks like this:

account_sk	account_id	account_name	plan_id	plan_name	valid_from	valid_to	is_current
101	ACC_WL	Whisker Labs	PRO	Pro	2025-01-10	2025-07-14	false
102	ACC_WL	Whisker Labs	ENT	Enterprise	2025-07-15	NULL	true

Two rows, same account_id, different account_sk. The fact tables carry the account_sk that was valid at the time of the event. So a March invoice references account_sk = 101 (Pro), and an August invoice references account_sk = 102 (Enterprise).

The "as-of" query

The pattern you'll use constantly: what plan was this account on as of date X?

SELECT account_id, plan_name, base_mrr
FROM dim_account
WHERE account_id = 'ACC_WL'
  AND DATE '2025-03-15' BETWEEN valid_from
                            AND COALESCE(valid_to, DATE '9999-12-31');
-- Returns the Pro row (sk=101).

The COALESCE(valid_to, '9999-12-31') handles the currently-valid row (where valid_to IS NULL).

The "current only" query

When you only want the live state:

SELECT account_id, account_name, plan_name
FROM dim_account
WHERE is_current = true;

Keep both patterns in muscle memory. You'll write them weekly.

Doing the SCD2 update

When Whisker Labs upgrades, two statements:

-- 1. Close out the old Pro row
UPDATE dim_account
SET valid_to = DATE '2025-07-14', is_current = false
WHERE account_id = 'ACC_WL' AND is_current = true;

-- 2. Insert the new Enterprise row
INSERT INTO dim_account
    (account_id, account_name, parent_account_id, plan_id, plan_name,
     signup_date, valid_from, valid_to, is_current)
VALUES
    ('ACC_WL', 'Whisker Labs', NULL, 'ENT', 'Enterprise',
     '2025-01-10', '2025-07-15', NULL, true);

In production you'd wrap this in a transaction and drive it from a staging table of "accounts whose plan changed today." With dbt, you'd rebuild dim_account incrementally each day from the source, generating new SCD2 rows automatically. The shape is the same.

Accumulating snapshot: the trial → paid → churn lifecycle

Remember from the coffee shop article: an accumulating snapshot is one row per entity, with milestone date columns that get updated in place as the entity progresses through a defined pipeline.

For Tabby, the pipeline is:

trial_started → first_paid → expanded → churned (→ maybe reactivated)

One row per subscription. Updated as milestones happen.

CREATE TABLE fact_subscription_lifecycle (
    lifecycle_sk          BIGSERIAL PRIMARY KEY,
    account_sk            BIGINT NOT NULL REFERENCES dim_account(account_sk),
    subscription_id       TEXT NOT NULL,
    trial_start_date_sk   INT NOT NULL REFERENCES dim_date(date_sk),
    trial_end_date_sk     INT REFERENCES dim_date(date_sk),
    first_paid_date_sk    INT REFERENCES dim_date(date_sk),
    expanded_date_sk      INT REFERENCES dim_date(date_sk),    -- plan upgrade
    churned_date_sk       INT REFERENCES dim_date(date_sk),
    reactivated_date_sk   INT REFERENCES dim_date(date_sk),
    current_status        TEXT NOT NULL,     -- TRIAL / ACTIVE / CHURNED / REACTIVATED
    trial_to_paid_days    INT,
    paid_to_churn_days    INT,
    lifetime_mrr          NUMERIC(12,2)
);

Why this works for SaaS: the funnel questions — "how long do trials take to convert?", "what % of Pro accounts churn within 90 days?", "what's the median trial-to-paid interval by cohort?" — are all SELECT ... FROM fact_subscription_lifecycle WHERE first_paid_date_sk IS NOT NULL. No joins to transaction facts, no re-deriving from invoices. The lifecycle is materialized once, queried forever.

Contrast with a transaction fact (which never updates — you only append) and a periodic snapshot (which inserts a new row every period). The accumulating snapshot is the only one you update. That's the tell: if you're UPDATE-ing a fact row, it's almost certainly an accumulating snapshot.

The update pattern

When Whisker Labs converts from trial to paid:

UPDATE fact_subscription_lifecycle
SET first_paid_date_sk = 20250124,           -- they paid on Jan 24
    current_status     = 'ACTIVE',
    trial_to_paid_days = 14,
    lifetime_mrr       = 12.00               -- Pro monthly
WHERE subscription_id = 'SUB_WL_001';

When they later expand to Enterprise:

UPDATE fact_subscription_lifecycle
SET expanded_date_sk = 20250715,
    lifetime_mrr     = 499.00                -- Enterprise monthly
WHERE subscription_id = 'SUB_WL_001';

Same row, updated twice. That's the accumulating snapshot.

Periodic snapshot: monthly MRR

Now the second grain. We also need "what was every account's MRR at the end of each month?" — for MRR movement (the famous MRR waterfall: starting + new + expansion − contraction − churn = ending), for cohort retention curves, for the finance team's month-end close.

We could recompute this from invoices every time. We don't, for three reasons:

Performance — a 5-year MRR trend across 10,000 accounts shouldn't scan every invoice.
Snapshot truth — if a plan change gets backdated or an invoice is edited tomorrow, last month's reported MRR shouldn't silently change. The snapshot freezes "what we knew then."
Simplicity — the MRR waterfall query becomes a self-join on two adjacent months, not a temporal reconstruction.

CREATE TABLE fact_subscription_month (
    subscription_month_sk BIGSERIAL PRIMARY KEY,
    account_sk            BIGINT NOT NULL REFERENCES dim_account(account_sk),
    subscription_id       TEXT NOT NULL,
    month_sk              INT NOT NULL REFERENCES dim_date(date_sk),  -- first of month
    plan_id               TEXT NOT NULL,
    mrr                   NUMERIC(10,2) NOT NULL,
    quantity              INT NOT NULL,                 -- active collars
    is_active             BOOLEAN NOT NULL,
    UNIQUE (subscription_id, month_sk)
);

One row per subscription per month. The month_sk points to the first day of the month in dim_date (a common convention).

The MRR waterfall

The naive way to find "last month" is month_sk - 100, since month_sk is YYYYMMDD. Don't do this — it's not just imprecise, it's flat-out broken every January. 20260101 - 100 = 20260001, which isn't December 2025 (20251201), it isn't a valid date at all, and it matches nothing in dim_date. Every account's prev_mo join would silently come back NULL for January specifically, once a year, right at the month finance cares about most for year-end close.

The fix is to go through dim_date itself rather than doing arithmetic on the encoded key:

SELECT
    this_mo.month_sk,
    SUM(this_mo.mrr) AS ending_mrr,
    SUM(CASE WHEN prev_mo.account_sk IS NULL THEN this_mo.mrr END) AS new_mrr,
    SUM(CASE WHEN this_mo.mrr > prev_mo.mrr
             THEN this_mo.mrr - prev_mo.mrr END) AS expansion_mrr,
    SUM(CASE WHEN this_mo.mrr < prev_mo.mrr
             THEN prev_mo.mrr - this_mo.mrr END) AS contraction_mrr,
    SUM(CASE WHEN this_mo.is_active = false THEN prev_mo.mrr END) AS churned_mrr
FROM fact_subscription_month this_mo
JOIN dim_date d_this        ON d_this.date_sk = this_mo.month_sk
JOIN dim_date d_prev        ON d_prev.full_date = (d_this.full_date - INTERVAL '1 month')::date
LEFT JOIN fact_subscription_month prev_mo
       ON prev_mo.subscription_id = this_mo.subscription_id
      AND prev_mo.month_sk = d_prev.date_sk
GROUP BY this_mo.month_sk
ORDER BY this_mo.month_sk;

This joins through dim_date twice — once to get the current month's real calendar date, once to look up whatever date_sk actually represents "one calendar month earlier" — so the previous-month lookup is correct at every year boundary, not just the eleven months where subtracting 100 happens to work.

Try writing that against invoices. You can — but it's 5x the SQL and 50x the compute.

Usage / event fact: high-volume, separate from billing

Tabby's collars emit a "ping" every 15 minutes: location, activity score, nap flag. That's millions of events per day. Don't put this in the billing fact — it'll drown your invoice queries.

Separate transaction fact for usage:

CREATE TABLE fact_usage_event (
    usage_event_sk  BIGSERIAL PRIMARY KEY,
    event_ts        TIMESTAMP NOT NULL,
    event_date_sk   INT NOT NULL REFERENCES dim_date(date_sk),
    account_sk      BIGINT NOT NULL REFERENCES dim_account(account_sk),
    workspace_sk    BIGINT NOT NULL REFERENCES dim_workspace(workspace_sk),
    collar_id       TEXT NOT NULL,                -- the device
    event_type      TEXT NOT NULL,                -- LOCATION / ACTIVITY / NAP
    activity_score  INT,
    nap_minutes     INT
);

This is your classic high-volume event fact. Partition it by date in production. Aggregate it nightly into a fact_daily_usage (periodic snapshot) for dashboards. Keep the raw event table for deep-dives.

Why separate from billing. Mixing event-grain with invoice-grain in one fact is a Category 5 anti-pattern. Either you store one row per ping and bloat the billing columns with NULLs on 99.9% of rows, or you aggregate pings and lose the raw event. Two tables, no compromise.

Factless fact table: entitlements

Final pattern. SaaS products gate features by plan: Pro gets "nap history," Enterprise gets "API access." The question "which accounts had API access on June 1st?" is an entitlement lookup.

Model it as a factless fact table — row presence means "this account had this feature as of this date":

CREATE TABLE fact_entitlement (
    entitlement_sk BIGSERIAL PRIMARY KEY,
    account_sk     BIGINT NOT NULL REFERENCES dim_account(account_sk),
    feature_id     TEXT NOT NULL,                  -- 'API_ACCESS', 'NAP_HISTORY', etc.
    effective_date_sk INT NOT NULL REFERENCES dim_date(date_sk),
    UNIQUE (account_sk, feature_id, effective_date_sk)
    -- no measures: the row's existence IS the fact
);

Now "how many accounts had API access in Q2?" is a COUNT(DISTINCT account_sk) with a date filter. No need to reverse-engineer from the plan table — the entitlements are materialized, auditable, and snapshot-stable.

(You could also model entitlements as attributes on dim_plan and infer them from dim_account.plan_id. That works for current entitlements. It fails for historical entitlements when plans or features change. SCD2 on entitlements directly is the robust answer.)

A real query: net revenue retention

Let's put the pieces together. Net Revenue Retention (NRR) — the SaaS north-star metric — compares a cohort's MRR now vs 12 months ago, including expansion and net of churn.

WITH cohort AS (
    -- Accounts that were active and paying 12 months ago
    SELECT DISTINCT account_sk
    FROM fact_subscription_month
    WHERE month_sk = 20250101 AND mrr > 0
),
mrr_then AS (
    SELECT account_sk, SUM(mrr) AS mrr_12mo_ago
    FROM fact_subscription_month
    WHERE month_sk = 20250101
    GROUP BY account_sk
),
mrr_now AS (
    SELECT account_sk, SUM(mrr) AS mrr_current
    FROM fact_subscription_month
    WHERE month_sk = 20260101 AND is_active = true
    GROUP BY account_sk
)
SELECT
    SUM(t.mrr_12mo_ago) AS starting_mrr,
    SUM(COALESCE(n.mrr_current, 0)) AS ending_mrr,
    SUM(COALESCE(n.mrr_current, 0)) / SUM(t.mrr_12mo_ago) AS nrr
FROM cohort c
JOIN mrr_then t ON t.account_sk = c.account_sk
LEFT JOIN mrr_now n ON n.account_sk = c.account_sk;

NRR > 1.0 means your existing customers are growing faster than they're churning. Anything above 1.1 (110%) is healthy; above 1.3 is elite.

That query would be a horror show against invoices. Against the periodic snapshot, it's a two-CTE join. That's the model paying for itself.

Common SaaS-specific mistakes

Double-counting MRR after a mid-month upgrade. If Pro is $12 and Enterprise is $499, and an account upgrades on the 15th, do you count $12 + $499 = $511 for that month? No. You prorate or snapshot once at month-end. Pick a convention (Tabby: month-end snapshot) and document it.
Forgetting SCD2 on accounts. If dim_account.plan_id is Type 1, every historical MRR query is wrong. The plan-changing case is the reason SCD2 exists.
Mixing event-grain with invoice-grain in one fact. Don't put collar pings and invoice lines in the same table. Two facts, two grains.
Storing "current MRR" on dim_account. It'll be stale by the end of today. MRR lives in fact_subscription_month, not on the dimension.
Treating reactivation as a new account. A churned customer who returns is the same account. Track it via reactivated_date_sk on the lifecycle fact, not by creating a new account_id. Otherwise your "new business" metric is inflated.
Not handling org hierarchies. If Whisker Labs is a subsidiary of "PetCo Org," and you query accounts individually, you'll double-count the org's MRR. Either roll up via parent_account_id explicitly or build a dedicated org-rollup snapshot.

Exercises

A few to test yourself. Hints are hidden; full solutions in solutions.sql in the companion repo.

1. Tabby wants to add a "discount percentage" attribute that some accounts negotiate. Should this be SCD Type 1, 2, or 3 on dim_account? Why?

Hint
Does anyone need to know the historical discounts, or just the current one? If finance needs to reconstruct past invoices at the negotiated rate, you need history → Type 2. If it's "what discount do they get today," Type 1 is fine.

2. Write the "as-of" query: what plan was account ACC_WL on as of 2025-05-01?

Hint
SELECT ... FROM dim_account WHERE account_id='ACC_WL' AND DATE '2025-05-01' BETWEEN valid_from AND COALESCE(valid_to, DATE '9999-12-31').

3. An account upgrades Pro → Enterprise mid-month. Explain why the MRR snapshot and the invoice fact might disagree on that month's MRR, and which one finance usually prefers.

Hint
The snapshot shows month-end state ($499). The invoice shows what was actually billed (often a proration: a credit for unused Pro days + a charge for partial Enterprise). Finance usually prefers invoice truth for revenue recognition; the snapshot is for operations/CRM.

4. Write a query using fact_subscription_lifecycle to compute the median number of days from trial start to first paid, for accounts that converted in 2025.

Hint
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY trial_to_paid_days) with a filter on first_paid_date_sk being non-null and in 2025.

5. Why should fact_usage_event (collar pings) be a separate fact table from fact_invoice_line? Name two reasons.

Hint
Different grain (one row per ping vs one row per invoice line) and different volume (millions/day vs dozens/day). Mixing them bloats the smaller table and pollutes the larger with NULL billing columns.

What's next

Everything in this article has been well-behaved: a subscription's lifecycle moves through its milestones in a predictable order, and MRR snapshots update on a fixed monthly cadence. Part 3 is where that good behavior stops — orders that fork into multiple shipments, carrier webhooks that arrive out of order and can make a status silently regress if the update logic isn't written for it, and a measure (orders currently in transit) that genuinely cannot be summed across days the way MRR can be summed across accounts.

Same fundamentals, messier processes. Onward to Part 3.

Resources

Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — chapter 14 (financial services) covers subscription/recurring-revenue patterns well.
dbt Labs — Modeling subscription revenue — MRR, churn, upgrades/downgrades, directly on-topic.
Subscriptions vs. usage modeling on the dbt blog — recurring-revenue patterns.

This is Part 2 of a five-part series — Part 1 has the fundamentals this article builds on. The companion repo has the full schema, seed data, and exercises for this part. Onward to Part 3.

Kimball Dimensional Modeling, Explained Through a Coffee Shop

Nariman Baubekov — Sun, 06 Sep 2026 08:17:50 +0000

If you've ever worked near a data warehouse, you've heard the word Kimball. Maybe you nodded. Maybe you quietly googled it and got a 600-page textbook and closed the tab.

Fair. Ralph Kimball's The Data Warehouse Toolkit is a lot. But the actual ideas behind dimensional modeling are small, friendly, and once you see them in a concrete example, they stick. So we're going to learn them through a coffee shop.

Specifically: Bean & Stalk, a fictional cafe with two locations, a loyalty program, a chalkboard menu that changes seasonally, and — importantly for us — surprisingly messy data.

This is Part 1 of a five-part series. Each part is its own self-contained business scenario, but they build on each other — the vocabulary this article establishes (grain, star schema, SCD2) is what every later part assumes you already have:

Part	Scenario	What it covers
1 — this one	Bean & Stalk, a coffee shop	Fact vs. dimension tables, grain, star vs. snowflake, SCD Types 0–3
2	Tabby, a SaaS cat-tracker company	Subscription grain, account hierarchies, MRR, SCD2 on plan changes
3	Crate Expectations, e-commerce fulfillment	Accumulating snapshots that fork, late-arriving facts, semi-additive measures
4	Meadowlark Health, insurance claims	Bridge tables, real many-to-many relationships, the weighting-factor trap
5	Back to Tabby, a contract dispute	The capstone — a case with no textbook answer, three stakeholders, three defensible numbers

You can read this one on its own; it's the fundamentals every later part leans on. Parts 2 through 5 each stand alone too, but they read best in order — Part 5 in particular assumes you've seen everything before it.

By the end of this article you'll understand:

What fact tables and dimension tables actually are (and all their sub-types)
How to pick a grain and why it's the most important decision you'll make
Star vs. snowflake schemas, and why star almost always wins
Slowly Changing Dimensions (SCD Types 0, 1, 2, and 3) without falling asleep
How to write queries against a dimensional model that are actually pleasant to read

There's a companion repo with the full schema, seed data, and exercises for every example below. We'll point at it as we go.

Why Kimball still matters in 2026

You might be wondering if this is all dated. We have dbt. We have columnar cloud warehouses. We have the lakehouse. People keep declaring the death of the warehouse. Isn't dimensional modeling a relic?

No. Here's why.

The modern data stack changed where and how we store data, but it didn't change how humans think about business questions. When someone asks "how did the oat-milk latte do in the Pacific Northwest stores last quarter, compared to the same quarter last year?" — they are asking for a fact (sales) sliced by dimensions (product, region, time). That question has not changed since 1996. It will not change in 2036.

What dbt gave us is a cleaner way to build those fact and dimension tables. It didn't replace the shape of the model. If anything, it made dimensional modeling more accessible, because now anyone with SQL can build a star schema in a few hours.

So: Kimball isn't a legacy thing. It's a thinking tool for making data legible to humans. Let's learn it.

The core problem: operational DB ≠ analytical DB

Meet Bean & Stalk. Their point-of-sale system runs on a transactional database. Tables like orders, order_items, products, customers, payments, inventory_adjustments. Beautifully normalized. Great for the cash register.

Now the owner, Priya, wants a dashboard. "Top drinks by month. Year-over-year growth. Which baristas upsell the most food. Loyalty members who've gone quiet."

You could run those queries against the POS database directly. The first time. Maybe the second. By the fifth report you'll discover:

The joins are six tables deep.
A historical price change means today's $5.25 oat-milk latte and last year's $4.75 oat-milk latte look like two different products unless you're careful.
Someone updated a customer's email and now you can't reconstruct what was emailed last quarter.
The CEO's "monthly sales" query takes 40 seconds because it's aggregating across years of transaction rows every time.

The operational database is optimized for writing (taking orders fast). The analytical database should be optimized for reading (answering questions fast). Those two workloads want different shapes.

Dimensional modeling is the shape for the analytical side.

Meet Bean & Stalk

Let's set the scene so the schema makes sense.

Two stores: Mission St (the original) and Hayes Valley (the new one).
Drinks and food: espresso drinks, drip coffee, pastries, beans.
Loyalty program: customers sign up, earn stamps, get a free drink after 10.
Seasonal menu: the pumpkin spice situation comes and goes.
Baristas: a small rotating cast.

Now let's model it.

Fact tables are the verbs

A fact table holds measurable, quantitative events. Things that happened. Each row is typically an event at a point in time, expressed as numeric measurements (called measures or facts) plus foreign keys pointing to surrounding dimensions.

If dimensions are the nouns, facts are the verbs. "We sold 2 oat-milk lattes at Mission St on Tuesday." — that's a verb (sold).

There are several common flavors of fact table. Bean & Stalk will eventually have all of them.

1. Transaction fact table

The workhorse. One row per event at its most granular level. For us: one row per line item on an order.

-- Simplified
CREATE TABLE fact_order_line (
    order_line_sk    BIGSERIAL PRIMARY KEY,
    receipt_number   BIGINT,            -- degenerate dimension
    order_date_sk    INT REFERENCES dim_date(date_sk),
    pickup_date_sk   INT REFERENCES dim_date(date_sk),
    product_sk       INT REFERENCES dim_product(product_sk),
    customer_sk      INT REFERENCES dim_customer(customer_sk),
    store_sk         INT REFERENCES dim_store(store_sk),
    barista_sk       INT REFERENCES dim_barista(barista_sk),
    junk_sk          INT REFERENCES dim_junk(junk_sk),
    quantity         INT,
    unit_price       NUMERIC(8,2),
    discount_amount  NUMERIC(8,2) DEFAULT 0,
    line_total       NUMERIC(8,2)
);

Every order line is a row. Two lattes on one receipt? That's one row with quantity = 2. A latte and a muffin on the same receipt? Two rows. Simple.

2. Periodic snapshot fact table

Some questions want a regular photo of the world rather than a stream of events. A periodic snapshot takes a reading at a fixed interval — daily, weekly, monthly.

For Bean & Stalk: a daily snapshot of sales by drink by store. One row per (date, store, product).

CREATE TABLE fact_daily_sales (
    daily_sales_sk   BIGSERIAL PRIMARY KEY,
    snapshot_date_sk INT REFERENCES dim_date(date_sk),
    store_sk         INT REFERENCES dim_store(store_sk),
    product_sk       INT REFERENCES dim_product(product_sk),
    daily_quantity   INT,
    daily_revenue    NUMERIC(10,2),
    transaction_count INT
);

Why bother when we could just recompute from fact_order_line? Three reasons:

Performance — a year-over-year chart on transaction rows scans millions of rows; the snapshot scans thousands.
History of truth — if someone deletes an order tomorrow, the snapshot from yesterday still tells you what was reported then.
Simplicity — the dashboard query becomes SELECT ... FROM fact_daily_sales instead of a 12-way join.

3. Accumulating snapshot fact table

This one is for multi-stage processes that evolve over time. Think: order placed → shipped → delivered. Or for us: loyalty signup → first purchase → 5th purchase → 10th purchase (free drink earned).

One row per entity (a customer's loyalty journey). Columns for each milestone date. You update the same row as milestones are hit, rather than inserting new ones.

CREATE TABLE fact_loyalty_journey (
    journey_sk                BIGSERIAL PRIMARY KEY,
    customer_sk               INT REFERENCES dim_customer(customer_sk),
    signup_date_sk            INT REFERENCES dim_date(date_sk),
    first_purchase_date_sk    INT REFERENCES dim_date(date_sk),
    fifth_purchase_date_sk    INT REFERENCES dim_date(date_sk),
    tenth_purchase_date_sk    INT REFERENCES dim_date(date_sk),
    current_status            TEXT,        -- 'signed_up', 'active', 'reward_earned', 'churned'
    signup_to_first_days      INT,
    first_to_tenth_days       INT
);

Note this is fundamentally different from a transaction fact (which never updates) or a periodic snapshot (which inserts new rows on a schedule). Accumulating snapshots are updated in place. That's the tell.

4. Factless fact table

Sounds like a Zen koan. It's actually simple: a fact table with no measures, only foreign keys. It exists to record that something was possible or happened without a number attached.

Bean & Stalk example: drink availability. The pumpkin spice latte is available on certain dates and not others. That "this drink was offered on this day in this store" is a fact worth recording, even though it has no quantity.

CREATE TABLE fact_drink_availability (
    drink_availability_sk BIGSERIAL PRIMARY KEY,
    product_sk            INT REFERENCES dim_product(product_sk),
    date_sk               INT REFERENCES dim_date(date_sk),
    store_sk               INT REFERENCES dim_store(store_sk)
    -- no measures! the presence of the row IS the fact
);

Now "how many days was the PSL available at Mission St in 2025?" is a trivial COUNT(*).

Quick mental model

Type	When to use	Bean & Stalk example
Transaction	Atomic events	Each line on a receipt
Periodic snapshot	Regular photos	Daily sales by store/product
Accumulating snapshot	Multi-stage journeys	Loyalty signup → 10th drink
Factless	Coverage / eligibility	Drink available on a day

Dimension tables are the nouns

A dimension table holds the descriptive context around the facts — the who, what, where, when, how. Dimensions are how you slice and filter. They tend to be wide (many columns) and short (far fewer rows than fact tables).

Let's meet each kind through Bean & Stalk.

Date dimension (the universal one)

Every dimensional model needs a dim_date. Yes, even in 2026. Yes, even though you could compute EXTRACT(MONTH FROM date) on the fly.

Two reasons. First, business calendar logic (fiscal quarters, holidays, "is this a weekend") is awful to compute and trivial to store. Second, joining on an integer date_sk is faster and more compression-friendly than joining on a timestamp.

CREATE TABLE dim_date (
    date_sk       INT PRIMARY KEY,        -- e.g. 20260813 for 2026-08-13
    full_date     DATE NOT NULL,
    day_of_week   TEXT,                    -- 'Monday'
    day_number    INT,
    month_number  INT,
    month_name    TEXT,
    quarter       INT,
    year          INT,
    is_weekend    BOOLEAN,
    holiday_name  TEXT
);

The classic trick: role-playing dimensions. Bean & Stalk has both an order date and a pickup date (mobile orders). Same dim_date table, two foreign keys in the fact. In the query, you join dim_date twice with different aliases (order_date and pickup_date).

Product dimension with Slowly Changing Dimensions

Here's where it gets interesting. The dim_product looks like a normal dimension:

CREATE TABLE dim_product (
    product_sk       BIGSERIAL PRIMARY KEY,
    product_id       TEXT,                -- natural key, e.g. 'OAT_LATTE'
    product_name     TEXT,
    category         TEXT,                 -- 'ESPRESSO_DRINK', 'DRIPO', 'PASTRY'
    base_price       NUMERIC(8,2),
    recipe_notes     TEXT,
    -- SCD Type 2 columns:
    valid_from       DATE NOT NULL,
    valid_to         DATE,
    is_current       BOOLEAN NOT NULL
);

But Bean & Stalk changes things over time. The oat-milk latte's recipe changed in March (new oat milk vendor). Its price went up in June. How do you model that?

This is the SCD problem. Slowly Changing Dimensions. There are four common strategies. Let's walk through them with the same example.

SCD Type 0 — retain original. Never change the value. The original row is the row. Useful for things that should be immutable, like the date a customer signed up. Bean & Stalk treats product_id this way.

SCD Type 1 — overwrite. Just update the row. Old value is lost. Use this when history doesn't matter. For a typo fix in a product description (Oat Milk Latte → Oat-Milk Latte), Type 1 is fine.

SCD Type 2 — add a new row. Insert a new row with the new values, expire the old row by setting valid_to and is_current = false. This preserves full history. For price and recipe changes, Bean & Stalk uses Type 2.

A Type 2 product history looks like this:

product_sk	product_id	product_name	base_price	valid_from	valid_to	is_current
1	OAT_LATTE	Oat-Milk Latte	4.75	2025-01-01	2025-06-14	false
2	OAT_LATTE	Oat-Milk Latte	5.25	2025-06-15	NULL	true

Now your historical reports use the price that was actually charged at the time, not today's price. This is the whole point.

Laid out on a timeline, the two rows look like this — the fact table always points at whichever product_sk was valid on the day the sale happened, so a March query and an August query silently pick up different rows without needing any date logic of their own:

SCD Type 3 — add a column. Keep the old value in a separate column (previous_base_price, previous_valid_until). Useful when you only care about the previous state, not the full history. Bean & Stalk doesn't bother with Type 3 for products — Type 2 is strictly more powerful — but you'll see it in domains where people genuinely only care about "before vs after" (a plan's previous tier, an employee's previous role).

The takeaway: Type 1 and Type 2 cover ~95% of real-world cases. Don't reach for the others unless you have a specific reason.

Customer dimension

Standard Type 1-ish dimension for Bean & Stalk's loyalty members.

CREATE TABLE dim_customer (
    customer_sk    BIGSERIAL PRIMARY KEY,
    customer_id    TEXT,
    name           TEXT,
    email          TEXT,
    loyalty_number TEXT,
    signup_date    DATE,
    loyalty_tier   TEXT                -- 'BRONZE', 'SILVER', 'GOLD'
);

If we cared about tracking tier changes over time (Bronze → Silver → Gold), we'd make this Type 2 too.

Store and barista dimensions

dim_store is a small, stable dimension. dim_barista holds employee attributes. Both Type 1.

Junk dimension

This is a fun one. Bean & Stalk has several low-cardinality flags: size (small/medium/large), milk_type (whole/oat/almond/soy/none), syrup_flavor (none/vanilla/caramel/hazelnut), extra_shot (true/false). None of these deserve their own dimension. But putting each one as a column directly on the fact table is fine too — except it clutters things.

A junk dimension combines them into one small table of all observed combinations:

CREATE TABLE dim_junk (
    junk_sk       BIGSERIAL PRIMARY KEY,
    size          TEXT,             -- SMALL / MEDIUM / LARGE
    milk_type     TEXT,             -- WHOLE / OAT / ALMOND / SOY / NONE
    syrup_flavor  TEXT,             -- NONE / VANILLA / CARAMEL / HAZELNUT
    extra_shot    BOOLEAN
);

The fact table holds a single junk_sk foreign key. Now all those flags live in one tidy place and you can still slice by them.

Degenerate dimension

A degenerate dimension is a dimension key that has no dimension table — it just lives in the fact. The classic case is a receipt number or order number. There's no dim_receipt; the receipt_number column sits directly on fact_order_line so you can group all the lines of one receipt back together. Simple, useful, slightly weird name.

Choosing the grain

If you take one thing from this article, take this: pick your grain first, before anything else.

The grain is the precise definition of what one row in a fact table represents. Until you can state the grain in one plain sentence, you're not ready to build the table.

For Bean & Stalk's transaction fact, three plausible grains:

One row per order. Pros: tiny table. Cons: can't analyze individual drinks within an order. The oat-lattes-plus-muffin combo question becomes impossible.
One row per order line. Pros: fully flexible — you can always roll up to order level with SUM(...) GROUP BY receipt_number. Cons: bigger table. (This is what we chose.)
One row per drink modification. Pros: maximally detailed. Cons: enormous table, and "how many lattes did we sell?" requires careful un-nesting. Overkill for a cafe.

The right grain is the most granular level at which a business event occurs that you'd want to analyze separately. For Bean & Stalk that's the order line. Lower than that (sub-line modifications) is overkill; higher than that (whole order) loses detail.

Same logic applies everywhere: an e-commerce fact is usually one row per order line; a payments fact might be one row per transaction; a web analytics fact might be one row per page view. State the grain out loud. If it sounds weird, reconsider.

Star vs. snowflake

You have two layout choices for a dimensional model.

Star schema: the fact table sits in the middle, dimensions radiate out, and each dimension is a flat, denormalized table. One hop from fact to any dimension.

Snowflake schema: dimensions are themselves normalized. dim_product references dim_category, which references dim_department. Three hops to get to department.

Snowflake looks cleaner to a normalized-DB brain. It uses less storage. In the 90s, when storage was expensive, that mattered.

In 2026, use star. Almost always. Reasons:

Query simplicity. One join, not three. Your dashboard authors will thank you.
Columnar warehouses love wide, flat dimensions. Compression is excellent. Joins are cheap. Snowflake normalization actively hurts you here.
Human legibility. A star schema can be read and understood by an analyst in 30 seconds. A snowflake requires tracing keys around.
Storage is cheap. The thing snowflake optimized for is no longer scarce.

Normalize your operational database. Denormalize your analytical one. That's the whole game.

A real query

Let's put it together. Top-selling drinks by month, with a barista leaderboard for the top drink.

-- Top drinks by month
SELECT
    d.month_name,
    p.product_name,
    SUM(f.quantity)         AS total_sold,
    SUM(f.line_total)       AS revenue
FROM fact_order_line f
JOIN dim_date   d ON d.date_sk = f.order_date_sk
JOIN dim_product p ON p.product_sk = f.product_sk
WHERE d.year = 2025
GROUP BY d.month_name, p.product_name
ORDER BY d.month_number, revenue DESC;

That's the whole query. Notice how readable it is. Five joins, all one-hop, clear aliases. That's the star schema paying off.

Now the barista leaderboard for the top drink of the year:

WITH top_drink AS (
    SELECT p.product_sk, p.product_name
    FROM fact_order_line f
    JOIN dim_product p ON p.product_sk = f.product_sk
    GROUP BY p.product_sk, p.product_name
    ORDER BY SUM(f.line_total) DESC
    LIMIT 1
)
SELECT
    b.barista_name,
    SUM(f.quantity)   AS drinks_made,
    SUM(f.line_total) AS revenue
FROM fact_order_line f
JOIN dim_barista b ON b.barista_sk = f.barista_sk
JOIN top_drink    t ON t.product_sk = f.product_sk
GROUP BY b.barista_name
ORDER BY revenue DESC
LIMIT 10;

Try writing that against the normalized POS schema. It's possible, but it'll take three times the SQL and ten times the thinking.

The full version (with role-playing date dimensions, SCD2-aware joins, and the "as-of" lookup pattern) is in queries.sql in the companion repo.

Common mistakes

These are common. Avoid them.

Duplicate facts in different tables. If line_total lives in fact_order_line, don't also store a precomputed daily_total next to it in a different fact. Compute it at query time, or build a proper snapshot fact. Two sources of the same number always drift.
Storing aggregates next to base rows. Adding a monthly_total column to fact_order_line is a Category 5 anti-pattern. It will be wrong by the second week.
Forgetting SCD2 on things that change. If your product price changed and you overwrote the row, every historical report is now silently incorrect. Use Type 2.
Putting measures in dimension tables. A "current price" column on dim_product is fine. A "total units sold last quarter" column on dim_product is a trap — it'll be stale and it muddies the dimension's purpose.
Mixing grains in one fact table. One row per order line and one row per daily summary in the same table? No. Either split into two facts or pick the lower grain.
Using natural keys as surrogate keys. product_id is great as a natural key but don't make it the primary key of dim_product — Type 2 means you'll have multiple rows per product_id. Always use a surrogate product_sk.

Exercises

A few quick ones to test your understanding. Hints are hidden — click to expand. Full solutions and more practice are in exercises.sql in the companion repo.

1. Bean & Stalk wants to track which baristas work which shifts. Would you model "shift" as its own fact table, a dimension, or an attribute on the barista? Why?

Hint
Think about whether a shift is an event (verb → fact) or a descriptor (noun → dimension). Could it be both? What questions would each shape let you answer?

2. Write a query that uses role-playing date dimensions to find orders where the pickup date was a different day than the order date (i.e., mobile pre-orders for tomorrow).

Hint
Join dim_date twice with different aliases, once on order_date_sk and once on pickup_date_sk. Then filter where the two full_date values differ.

3. The oat-milk latte's price changed on 2025-06-15. Using the SCD2 product dimension, write a query to compute total revenue for the oat-milk latte where each transaction uses the price that was actually in effect at the time.

Hint
The fact table's product_sk already points at the correct historical row of dim_product (that's the SCD2 magic). You don't need any extra date filtering on the dimension — just sum line_total.

4. Bean & Stalk launches a "drink of the week" promotion. How would you model that using a factless fact table?

Hint
Each row: (product_sk, date_sk, store_sk). No measures. The row's existence is the fact "this drink was promoted on this day at this store."

What's next

This article covered the conceptual fundamentals — the things that show up in every dimensional model. From here, the series gets progressively less textbook and more "here's what actually goes wrong in practice":

Part 2 applies these fundamentals to SaaS subscriptions — MRR reconstructed as-of arbitrary dates, account hierarchies, and SCD2 doing real work on plan changes.
Part 3 covers accumulating snapshots that don't behave — orders that split into multiple shipments, carrier webhooks that arrive out of order, and a measure that genuinely can't be summed across days.
Part 4 is bridge tables — a fact that's legitimately about more than one dimension value at once, and the trap of inventing money the moment you join through one carelessly.
Part 5 is the capstone: a scenario with no textbook answer, where three stakeholders each have a defensible number for the same deal, and the job is building a model honest enough to tell the truth to all three at once.

Same ideas throughout, meaner problems each time. Start with Part 2 whenever you're ready.

Resources

Ralph Kimball & Margy Ross, *The Data Warehouse Toolkit* — the book. Still the best reference.
dbt — Modeling your data — modern take on dimensional modeling with dbt.
Analytics Engineering on the dbt blog — patterns and anti-patterns.
Joseph Machado's Start Data Engineering — solid practical writeups.

If this was useful, the companion repo has the full schema, seed data, and a quiz that tests all of this. Parts 2 through 5 apply everything here to domains that break it in progressively more interesting ways. See you there.

Normalize It, Then Break It On Purpose: 3NF to Star Schema, Explained Through Food Delivery

Nariman Baubekov — Fri, 04 Sep 2026 16:37:43 +0000

Every data engineer eventually runs into the same apparent contradiction: the database design that every textbook, every senior review, and every "how do I avoid duplicate data" instinct insists is correct turns out to be the wrong shape the moment someone asks a real business question about it. That's not a contradiction. It's two different jobs sharing one word — "database" — when they actually want opposite things from how the data is laid out.

This article walks both halves, in order, on one running example: build a properly normalized schema from a genuinely messy starting point, watch it become painful the moment someone wants to ask something of it, and then deliberately undo the normalization — on purpose, for a documented reason — into a star schema built for exactly that question.

Meet Curb Appetite

Curb Appetite is a food delivery app: customers order from local restaurants, a driver picks it up and delivers it, everyone involved generates data. The starting point is the kind of table that actually exists in a lot of early-stage companies — a flat export somebody built to get the app shipped, never designed, just grown:

order_id	order_date	customer_name	customer_email	customer_city	customer_state	customer_zip	restaurant_name	restaurant_cuisine	driver_name	driver_phone	items_ordered	order_total
5001	2026-03-14	Priya Shah	priya@example.com	Austin	TX	78701	Bangkok Nights	Thai	Marcus Webb	512-555-0142	Pad Thai x2, Spring Rolls x1, Thai Iced Tea x1	38.50
5002	2026-03-14	Diego Ruiz	diego@example.com	Austin	TX	78701	Bangkok Nights	Thai	Alicia Nguyen	512-555-0198	Green Curry x1, Thai Iced Tea x2	23.00

This table works, in the sense that it renders a receipt. It's also a small museum of everything normalization exists to fix, and every violation in it will cost someone real time later. Let's fix them in order.

Getting to First Normal Form: atomic values, no repeating groups

First Normal Form (1NF) requires that every column hold a single, atomic value — no lists, no repeating groups crammed into one field — and that every row be uniquely identifiable.

items_ordered fails immediately: "Pad Thai x2, Spring Rolls x1, Thai Iced Tea x1" is three facts wearing one column. Ask "how many Pad Thais did we sell this month" against this table and the honest answer is: you can't, not with SQL — you'd need to parse a string first. That's the tell for a 1NF violation: if answering a normal-sounding question requires string-splitting a column, the column is doing the job of a table.

The fix is to give each item its own row:

-- order_items, one row per item on an order
-- (not yet normalized further — watch what's still duplicated)
CREATE TABLE order_items (
    order_id     TEXT NOT NULL REFERENCES orders(order_id),
    menu_item_id TEXT NOT NULL,
    item_name    TEXT NOT NULL,
    item_price   NUMERIC(6,2) NOT NULL,
    quantity     INT NOT NULL,
    PRIMARY KEY (order_id, menu_item_id)
);

order_id  menu_item_id  item_name       item_price  quantity
5001      MI-101        Pad Thai        14.00       2
5001      MI-102        Spring Rolls    6.50        1
5001      MI-103        Thai Iced Tea   4.00        1
5002      MI-104        Green Curry     15.00       1
5002      MI-103        Thai Iced Tea   4.00        2

items_ordered is gone from orders, replaced by this table. Every value is now atomic, and "how many Pad Thais did we sell" is SUM(quantity) WHERE item_name = 'Pad Thai' instead of a parsing exercise. Technically 1NF-compliant — but look at MI-103 appearing twice, at the same price, on two unrelated orders. That's not a coincidence, and it's not fixed yet.

Getting to Second Normal Form: no partial dependencies

Second Normal Form (2NF) requires 1NF, plus: every non-key column must depend on the entire primary key — not just part of it. This only ever bites when a table has a composite key, which order_items does: (order_id, menu_item_id).

Ask what item_name and item_price actually depend on, and the honest answer is: only menu_item_id. Bangkok Nights' Thai Iced Tea costs $4.00 regardless of which order it's attached to — order_id contributes nothing to that fact. That's a partial dependency, and it's exactly why MI-103 shows up twice with the same price above: the price isn't stored once, it's stored once per order line that happens to include it. Raise the price to $4.50 tomorrow and you have to find and update every historical row that references it, or old and new orders quietly disagree about what a Thai Iced Tea costs.

The fix is to extract what the item actually is from what was ordered:

CREATE TABLE menu_items (
    menu_item_id  TEXT PRIMARY KEY,
    restaurant_id TEXT NOT NULL REFERENCES restaurants(restaurant_id),
    item_name     TEXT NOT NULL,
    item_price    NUMERIC(6,2) NOT NULL
);

CREATE TABLE order_items (
    order_id     TEXT NOT NULL REFERENCES orders(order_id),
    menu_item_id TEXT NOT NULL REFERENCES menu_items(menu_item_id),
    quantity     INT NOT NULL,
    PRIMARY KEY (order_id, menu_item_id)
);

Now Thai Iced Tea's price exists in exactly one row, in menu_items, and order_items just references it. One update, everywhere correct.

Worth a clarifying note here, because it trips people up: orders itself was never at risk of a 2NF violation, because its primary key (order_id) is a single column. 2NF violations are specifically about partial dependency on a composite key — with a single-column key, every non-key column depends on 100% of the key by definition, so there's no "partial" to violate. 2NF only ever does work on tables like order_items, where more than one column makes up the key.

Getting to Third Normal Form: no transitive dependencies

Third Normal Form (3NF) requires 2NF, plus: no non-key column may depend on another non-key column instead of on the primary key directly. This is called a transitive dependency, and orders is full of them.

Look at customer_city and customer_state. They don't actually describe the order — they describe the customer, by way of the customer's zip code. order_id → customer_id → zip → city/state is a chain, and 3NF says a column has to depend on the key directly, not by riding along on another attribute's coattails. The same thing is true of restaurant_name/restaurant_cuisine (they describe the restaurant, not the order) and driver_name/driver_phone (they describe the driver).

Notice something in the sample data above: both orders share zip = 78701, and both store "Austin", "TX" redundantly. That's the anomaly made visible — if a zip code's city assignment ever needs correcting, you're hunting down every order row that happens to reference it, instead of fixing one row in one place.

The fix, applied consistently:

CREATE TABLE zip_codes (
    zip   TEXT PRIMARY KEY,
    city  TEXT NOT NULL,
    state TEXT NOT NULL
);

CREATE TABLE customers (
    customer_id TEXT PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL,
    zip         TEXT NOT NULL REFERENCES zip_codes(zip)
);

CREATE TABLE restaurants (
    restaurant_id TEXT PRIMARY KEY,
    name          TEXT NOT NULL,
    cuisine       TEXT NOT NULL
);

CREATE TABLE drivers (
    driver_id TEXT PRIMARY KEY,
    name      TEXT NOT NULL,
    phone     TEXT NOT NULL
);

CREATE TABLE orders (
    order_id      TEXT PRIMARY KEY,
    order_date    DATE NOT NULL,
    customer_id   TEXT NOT NULL REFERENCES customers(customer_id),
    restaurant_id TEXT NOT NULL REFERENCES restaurants(restaurant_id),
    driver_id     TEXT NOT NULL REFERENCES drivers(driver_id),
    order_total   NUMERIC(8,2) NOT NULL
);

orders has shrunk from thirteen columns to six. Every fact in the schema now lives in exactly one place, and every non-key column depends on nothing but its own table's primary key. Seven tables, zero duplicated facts, zero update anomalies. This is a genuinely good schema — for the job it's designed for.

Here's the full result as an entity-relationship diagram — the thing normalization was building toward the whole time:

This is a good schema. It is not a good answer.

Curb Appetite's normalized schema is exactly right for what it's for: taking an order, charging a card, dispatching a driver, without ever risking two rows disagreeing about a fact that should only exist once. It's optimized for writes — specifically, for writes that can never quietly corrupt themselves.

Now someone in ops asks a completely reasonable question: "What's our revenue by cuisine, by city, by month?"

SELECT
    r.cuisine,
    z.city,
    date_trunc('month', o.order_date) AS month,
    SUM(oi.quantity * mi.item_price) AS revenue
FROM orders o
JOIN order_items oi  ON oi.order_id = o.order_id
JOIN menu_items mi   ON mi.menu_item_id = oi.menu_item_id
JOIN restaurants r   ON r.restaurant_id = o.restaurant_id
JOIN customers c     ON c.customer_id = o.customer_id
JOIN zip_codes z     ON z.zip = c.zip
GROUP BY r.cuisine, z.city, date_trunc('month', o.order_date)
ORDER BY month, revenue DESC;

Six joins across seven tables, for a question that isn't even asking for anything unusual. This isn't a sign the normalization was done wrong — it's the opposite. Every one of those joins exists precisely because the schema is correctly normalized: cuisine lives with the restaurant, city lives with the zip, price lives with the menu item, none of it duplicated anywhere. Correctness for writes and convenience for reads are different design goals, and a schema optimized entirely for the first will always look like this the moment you ask it to do the second.

Denormalizing on purpose: from 3NF to star schema

The fix isn't to loosen the normalized schema — that would reintroduce the exact update anomalies it exists to prevent, in the system that's still taking live orders. The fix is to build a second, derived schema, populated from the normalized one on a schedule, shaped entirely around the read side. This is a Kimball-style star schema: one fact table at a clearly stated grain, surrounded by denormalized dimension tables, one join away from anything.

First, the grain — the single most important decision in the whole exercise, stated as a sentence before any SQL: one row per item on an order. Not one row per order (too coarse — you'd lose the ability to ask "how did Pad Thai do specifically"), not one row per delivery event (too fine — nothing here needs that granularity).

CREATE TABLE fact_order_line (
    order_line_sk   BIGSERIAL PRIMARY KEY,
    order_id        TEXT NOT NULL,          -- degenerate dimension
    order_date_sk   INT NOT NULL REFERENCES dim_date(date_sk),
    customer_sk     BIGINT NOT NULL REFERENCES dim_customer(customer_sk),
    restaurant_sk   BIGINT NOT NULL REFERENCES dim_restaurant(restaurant_sk),
    menu_item_sk    BIGINT NOT NULL REFERENCES dim_menu_item(menu_item_sk),
    driver_sk       BIGINT NOT NULL REFERENCES dim_driver(driver_sk),
    quantity        INT NOT NULL,
    item_price      NUMERIC(6,2) NOT NULL,
    line_total      NUMERIC(8,2) NOT NULL
);

And the dimensions — each one flattening back together exactly what 3NF just spent three sections pulling apart:

CREATE TABLE dim_customer (
    customer_sk BIGSERIAL PRIMARY KEY,
    customer_id TEXT NOT NULL,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL,
    city        TEXT NOT NULL,   -- denormalized back in from zip_codes
    state       TEXT NOT NULL,
    zip         TEXT NOT NULL
);

CREATE TABLE dim_restaurant (
    restaurant_sk BIGSERIAL PRIMARY KEY,
    restaurant_id TEXT NOT NULL,
    name          TEXT NOT NULL,
    cuisine       TEXT NOT NULL
);

CREATE TABLE dim_menu_item (
    menu_item_sk BIGSERIAL PRIMARY KEY,
    menu_item_id TEXT NOT NULL,
    item_name    TEXT NOT NULL,
    restaurant_id TEXT NOT NULL
);

CREATE TABLE dim_driver (
    driver_sk BIGSERIAL PRIMARY KEY,
    driver_id TEXT NOT NULL,
    name      TEXT NOT NULL
    -- phone didn't make the cut: a support agent's tool needs it,
    -- an analyst asking "which drivers deliver fastest" doesn't.
);

city and state are back on dim_customer, duplicated across every customer in the same zip — exactly the redundancy 3NF removed. That's not a mistake here; it's the point. A dimension table is small relative to the fact table and read far more than it's written, so the storage cost of the duplication is negligible and the join it saves is real, on every single query.

The same question, asked of the star schema

SELECT
    r.cuisine,
    c.city,
    d.month_name,
    d.year,
    SUM(f.line_total) AS revenue
FROM fact_order_line f
JOIN dim_restaurant r ON r.restaurant_sk = f.restaurant_sk
JOIN dim_customer c   ON c.customer_sk = f.customer_sk
JOIN dim_date d       ON d.date_sk = f.order_date_sk
GROUP BY r.cuisine, c.city, d.month_name, d.year
ORDER BY d.year, d.month_name, revenue DESC;

Three joins, all one hop, instead of six across seven tables. Nothing about the underlying facts changed — this is the exact same revenue, sliced the exact same way. What changed is which design goal the schema is optimized for, and the query got dramatically simpler because the schema stopped fighting the question.

Common mistakes

Denormalizing the system that's still taking orders. The star schema is a second, derived copy, built by a scheduled job from the normalized source — not a replacement for it. The normalized schema keeps doing what it's good at (safe writes); the star schema is where reads happen.
Treating "denormalized" as "no rules." Grain still has to be chosen and stated as a sentence before any table gets built — "one row per order" instead of "one row per order line" would have silently made the Pad Thai question impossible again, just for a different reason than before.
Forgetting that a price can change. Bangkok Nights raises the Thai Iced Tea price next month. If dim_menu_item just gets updated in place, every historical fact_order_line row that references it will appear, on next query, to have been sold at the new price — which is wrong, and silently wrong, for every report touching last quarter. This is a slowly changing dimension problem, and it needs a real answer, not a shrug.
Rebuilding the star schema by hand, forever. This whole pipeline — normalized source, transform, star schema — is exactly the kind of thing that belongs in a scheduled, tested job, not a one-off script someone reruns when the numbers look stale.

Takeaways

Normalization and denormalization aren't opposing philosophies where one side is right — they're answers to two different questions: "how do I write this safely" and "how do I read this quickly."
1NF, 2NF, and 3NF each fix one specific kind of redundancy — repeating groups, partial dependency on a composite key, transitive dependency on a non-key column — in that order, because each one assumes the last is already fixed.
The joins that make a normalized schema painful to query are the same joins that make it safe to write to. That's not a design flaw to route around; it's the tradeoff, made visible.
A star schema doesn't replace the normalized schema — it's a second, deliberately redundant copy, built for a different job, kept in sync on purpose.

Testing Data Pipelines Like You Mean It: A pytest Crash Course for Data Engineers

Nariman Baubekov — Wed, 02 Sep 2026 06:57:38 +0000

Most data engineers write pipelines the way most people write shell scripts: run it, eyeball the output, ship it. That works right up until a schema changes upstream, a null slips through a join, or someone "fixes" a transformation and silently breaks three downstream tables. By then the bug isn't your problem anymore — it's a bad number in someone's dashboard.

Software engineers solved this problem decades ago with automated testing. Data engineering has been slower to adopt the habit, partly because our code touches messy external reality (files, databases, clusters) in a way a typical web app doesn't. But that's exactly why testing matters more here, not less. This article is a practical, DE-flavored crash course in pytest — the dominant Python testing framework — plus the patterns you actually need for pandas, Polars, and PySpark pipelines.

Why bother testing a data pipeline?

A few concrete failure modes that tests catch before production does:

A column gets renamed upstream and your join silently produces all-null matches instead of erroring.
A "cleaning" function that's supposed to drop duplicates accidentally drops valid rows too.
A date-parsing function works on your local machine's locale and breaks in the CI environment.
A refactor changes an aggregation from sum to mean and nobody notices until finance asks why revenue looks 90% smaller.

None of these require exotic testing techniques. They require the habit of writing small, deterministic checks against small, deterministic inputs — which is exactly what pytest is built for.

Where pytest fits — and where it doesn't

Before diving in, it's worth being precise about scope, because "testing a data pipeline" actually covers two different questions, and conflating them is a common source of confusion:

Is my code correct? Given a known input, does the transformation logic produce the right output? This is a property of your code, and it doesn't change based on what day it is or what a source system decided to send you.
Is today's data correct? Even with perfect code, a source system can start sending nulls, a partner feed can drop 90% of its rows overnight, a foreign key can stop resolving. This is a property of the data currently flowing through the system, and no amount of code testing can catch it, because the code was never wrong.

pytest answers the first question. Tools like dbt test, Great Expectations, and Soda answer the second. They're not competitors — they run at different times, against different inputs, and catch different bugs:

A useful rule of thumb when you're not sure which bucket a check belongs in: if the same check would fail identically on a completely different day's data, it's a code test; if it depends on what actually arrived today, it's a data test. "Does calculate_discount cap at 50%?" is always true or always false regardless of the date — code test. "Did today's order count come in within 20% of the seven-day average?" only means something in the context of today's actual data — data test.

This article is entirely about the first column. If you're looking for the second, dbt's testing docs, Great Expectations, and Soda are the right places to go next — and a mature data platform usually runs both, not one instead of the other.

Part 1: pytest fundamentals

Installing and writing your first test

uv add --dev pytest

This adds pytest as a development dependency — something your project needs to run its own test suite, but not something anyone installing your package needs. uv writes it into a [dependency-groups] table in pyproject.toml (the standardized format from PEP 735), kept separate from your project's real runtime dependencies:

[dependency-groups]
dev = [
    "pytest>=8.3.4",
]

pytest's core idea: a test is just a function whose name starts with test_, living in a file whose name starts with test_ or ends with _test.py. No boilerplate classes required (though you can use them).

# test_transformations.py
def add_tax(price: float, rate: float = 0.1) -> float:
    return round(price * (1 + rate), 2)

def test_add_tax_applies_default_rate():
    result = add_tax(100)
    assert result == 110.0

Run it with uv run, which executes the command inside the project's managed virtual environment without you ever having to activate one by hand:

uv run pytest
# or, more verbosely:
uv run pytest -v test_transformations.py

pytest uses the plain assert keyword — no self.assertEqual(...) ceremony. When an assertion fails, pytest rewrites it under the hood to show you exactly what was compared, which is a big part of why it's more pleasant than the built-in unittest.

Test discovery and project layout

A typical DE repo looks like:

my_pipeline/
├── src/
│   └── my_pipeline/
│       ├── __init__.py
│       ├── extract.py
│       ├── transform.py
│       └── load.py
├── tests/
│   ├── conftest.py
│   ├── unit/
│   │   ├── test_transform.py
│   │   └── test_extract.py
│   └── integration/
│       └── test_pipeline_end_to_end.py
├── pyproject.toml   # dependencies, dev group, and pytest config all live here
└── uv.lock          # exact resolved versions — commit this to the repo

pytest configuration lives in pyproject.toml too, under [tool.pytest.ini_options] — no separate pytest.ini needed:

[tool.pytest.ini_options]
testpaths = ["tests"]
addopts = "-ra"

testpaths tells pytest (and your editor's test runner) where to look without specifying a path on every invocation; addopts bakes in flags you'd otherwise retype constantly — -ra here prints a one-line summary of every non-passing test at the end of the run.

Separating unit/ and integration/ isn't required, but it lets you run fast tests constantly and slow ones less often:

uv run pytest tests/unit          # fast, run on every save
uv run pytest tests/integration   # slower, run before pushing

Fixtures: pytest's dependency injection

Fixtures are reusable pieces of setup, declared with @pytest.fixture and requested by name as a test function argument. This is the single most important pytest feature for DE work, because pipelines need repeatable inputs — sample dataframes, temp directories, mock connections.

import pytest
import pandas as pd

@pytest.fixture
def raw_orders() -> pd.DataFrame:
    return pd.DataFrame({
        "order_id": [1, 2, 3],
        "customer_id": [10, 10, 11],
        "amount": [25.0, 40.0, None],
    })

def test_drop_nulls_removes_incomplete_rows(raw_orders):
    from my_pipeline.transform import drop_null_amounts
    result = drop_null_amounts(raw_orders)
    assert len(result) == 2
    assert result["amount"].isnull().sum() == 0

Fixtures can depend on other fixtures, and pytest resolves the graph for you. They can also have a scope, controlling how often they're recreated:

@pytest.fixture(scope="function")   # default: fresh per test
@pytest.fixture(scope="module")     # once per test file
@pytest.fixture(scope="session")    # once per whole test run

scope="session" matters a lot for expensive setup — like spinning up a local Spark session (more on this below). The tradeoff is isolation versus speed: a fresh fixture per test can never leak state between tests, while a shared one is faster but puts the burden on you to make sure nothing one test does lingers to affect the next.

`conftest.py`: sharing fixtures across files

Fixtures defined in tests/conftest.py are automatically available to every test file in that directory and below, no import needed. This is where you put your "standard" sample datasets, temp-directory helpers, and mock clients so every test file can reuse them without duplication.

Parametrize: one test, many cases

Data pipelines are full of edge cases — empty strings, nulls, negative numbers, weird encodings. @pytest.mark.parametrize lets you run the same test logic against a table of inputs and expected outputs instead of copy-pasting near-identical test functions.

import pytest

@pytest.mark.parametrize("raw,expected", [
    ("2024-01-15", "2024-01-15"),
    ("2024/01/15", "2024-01-15"),
    ("15-01-2024", "2024-01-15"),
    ("", None),
    (None, None),
])
def test_normalize_date(raw, expected):
    from my_pipeline.transform import normalize_date
    assert normalize_date(raw) == expected

This is arguably the highest-leverage pytest feature for DE testing: it forces you to explicitly enumerate the messy input variants you actually expect from real-world data, instead of testing only the happy path.

Marks: skip, xfail, and custom categories

import pytest
import sys

@pytest.mark.skipif(sys.platform == "win32", reason="path handling differs on Windows")
def test_partition_path_format():
    ...

@pytest.mark.slow
def test_full_backfill_pipeline():
    ...

Register custom marks like slow or spark in the same [tool.pytest.ini_options] table from the project layout above, so there's one config file for the whole project instead of two competing ones:

# pyproject.toml
[tool.pytest.ini_options]
testpaths = ["tests"]
markers = [
    "slow: long-running integration tests",
    "spark: tests requiring a SparkSession",
]

uv run pytest -m "not slow"     # skip slow tests during local dev
uv run pytest -m spark          # run only Spark tests

(A standalone pytest.ini file still works if you'd rather keep pytest's config out of pyproject.toml — but there's little reason to when everything else about the project, dependencies included, already lives there.)

Mocking

Not everything a data pipeline needs tested is a transformation. A pipeline also makes requests — HTTP calls to a source API, queries against a database, writes to S3 — and those need their own tests: does the code retry on a 503, does it handle a malformed response body, does it fail loudly on a bad auth token instead of silently returning nothing. This section covers the mechanics; testing retry and backoff logic specifically gets a deeper, dedicated treatment in a companion article, including asserting on retry counts and simulated failure sequences with the responses library.

unittest.mock (built into the standard library) lets you replace a real dependency — an API call, a database connection, an S3 client — with a fake that returns canned data. This keeps unit tests fast and independent of network or infrastructure. The name is a historical artifact of when it shipped as part of the unittest package — pytest itself has no dependency on unittest for its core mechanics (discovery, fixtures, assertions are all pytest's own), and unittest.mock works standalone in plain pytest-style function tests, no TestCase required:

from unittest.mock import patch, MagicMock

def test_fetch_exchange_rate_handles_api_response():
    from my_pipeline.extract import fetch_exchange_rate

    fake_response = MagicMock()
    fake_response.json.return_value = {"rate": 1.08}
    fake_response.status_code = 200

    with patch("my_pipeline.extract.requests.get", return_value=fake_response):
        rate = fetch_exchange_rate("USD", "EUR")

    assert rate == 1.08

pytest-mock wraps the same unittest.mock machinery in a mocker fixture — not a different mocking engine, just a thinner interface that fits pytest's fixture style and cleans up automatically after each test instead of needing a with block or a @patch decorator:

def test_fetch_exchange_rate_handles_api_response(mocker):
    from my_pipeline.extract import fetch_exchange_rate

    fake_response = mocker.MagicMock()
    fake_response.json.return_value = {"rate": 1.08}
    fake_response.status_code = 200
    mocker.patch("my_pipeline.extract.requests.get", return_value=fake_response)

    rate = fetch_exchange_rate("USD", "EUR")

    assert rate == 1.08

Same assertions, same underlying patch mechanism — the difference is entirely ergonomic. mocker.patch(...) calls are automatically undone at the end of the test via pytest's fixture teardown, so there's no with block to wrap your arrange/act/assert around and no risk of a patch leaking into the next test if an exception fires mid-test.

The key discipline, regardless of which style you use: mock at the boundary of your system, not deep inside your own logic. If you find yourself mocking three layers deep to test a transformation function, that's usually a sign the function is doing too much and should be split into a pure part (testable without mocks) and an I/O part (tested with mocks or integration tests).

Part 2: the AAA pattern

Arrange–Act–Assert is a structural convention, not a pytest feature, but it keeps tests readable as your suite grows:

def test_deduplicate_orders_keeps_latest_record():
    # Arrange
    df = pd.DataFrame({
        "order_id": [1, 1, 2],
        "updated_at": ["2024-01-01", "2024-01-05", "2024-01-01"],
        "status": ["pending", "shipped", "pending"],
    })

    # Act
    result = deduplicate_orders(df)

    # Assert
    assert len(result) == 2
    assert result.loc[result.order_id == 1, "status"].iloc[0] == "shipped"

Every test should have exactly one clear "Act" step and assertions that check one behavior, even if that takes multiple assert lines. If a test's Arrange section is enormous and its Assert section is checking five unrelated things, split it — you'll thank yourself when it fails and you need to know why in five seconds, not five minutes.

Part 3: unit vs. integration tests, for pipelines specifically

The unit/integration distinction maps onto DE work a bit differently than it does onto typical application code:

Unit tests — test a single transformation function in isolation, with an in-memory dataframe you constructed by hand. No file I/O, no database, no cluster. These should run in milliseconds and make up the bulk of your suite.

def test_calculate_discount_caps_at_50_percent():
    result = calculate_discount(loyalty_years=20, base_rate=0.05)
    assert result == 0.5

Integration tests — test that multiple pieces work together against something closer to real infrastructure: a real (but local/test) database, a real file read/write, a local Spark session, a mocked-but-realistic S3 bucket (via moto). These are slower and fewer in number, but they catch the bugs unit tests structurally can't — a SQL query that's syntactically valid but returns the wrong join cardinality, a Parquet schema mismatch between writer and reader.

def test_pipeline_writes_expected_row_count(tmp_path, raw_orders):
    from my_pipeline.pipeline import run_pipeline

    input_path = tmp_path / "orders.csv"
    output_path = tmp_path / "output.parquet"
    raw_orders.to_csv(input_path, index=False)

    run_pipeline(input_path, output_path)

    result = pd.read_parquet(output_path)
    assert len(result) == 2

tmp_path is a built-in pytest fixture that gives you a fresh temporary directory per test, auto-cleaned afterward — extremely useful for testing anything that reads or writes files, without polluting your real filesystem or needing manual teardown.

The shape of a healthy suite follows from how expensive each layer is to run and how much of your logic it can realistically cover:

A useful rule of thumb: if you can't explain in one sentence what real-world bug a test would catch, it's probably testing implementation detail rather than behavior — cut it or rewrite it.

Part 4: testing pandas and Polars pipelines

pandas

The standard library ships purpose-built comparison helpers — use them instead of ==, because dataframe equality has edge cases (dtype mismatches, index alignment, float precision) that == handles inconsistently.

import pandas as pd
from pandas.testing import assert_frame_equal, assert_series_equal

def test_aggregate_revenue_by_region():
    input_df = pd.DataFrame({
        "region": ["west", "west", "east"],
        "revenue": [100, 200, 50],
    })

    result = aggregate_revenue_by_region(input_df)

    expected = pd.DataFrame({
        "region": ["east", "west"],
        "revenue": [50, 300],
    })
    assert_frame_equal(
        result.reset_index(drop=True),
        expected.reset_index(drop=True),
        check_dtype=False,
    )

check_dtype=False is worth knowing about: it's common for a groupby-aggregate to return int64 where your hand-built expected frame has int64 too, but small differences (e.g., float64 vs float32) shouldn't fail a test that's really checking values, not storage format — unless dtype correctness is exactly what you're testing, in which case leave it on.

Polars

Polars ships an equivalent testing module:

import polars as pl
from polars.testing import assert_frame_equal

def test_filter_active_customers():
    df = pl.DataFrame({
        "customer_id": [1, 2, 3],
        "is_active": [True, False, True],
    })

    result = filter_active_customers(df)

    expected = pl.DataFrame({
        "customer_id": [1, 3],
        "is_active": [True, True],
    })
    assert_frame_equal(result, expected)

Because Polars encourages a lazy/expression-based style, it's often cleanest to test the underlying expression logic directly (e.g., a function that returns a pl.Expr) separately from the I/O that triggers .collect(). That keeps the fast unit-testable core small and pure.

Property-based testing (optional but powerful)

For transformation logic with many edge cases, hypothesis can generate hundreds of varied inputs automatically instead of you hand-writing each case:

from hypothesis import given
from hypothesis import strategies as st

@given(st.lists(st.floats(allow_nan=False, allow_infinity=False)))
def test_normalize_never_produces_values_outside_zero_one(values):
    if not values:
        return
    result = min_max_normalize(values)
    assert all(0 <= v <= 1 for v in result)

This is worth reaching for once your parametrize list starts feeling like you're guessing at edge cases rather than enumerating known ones.

Part 5: testing PySpark pipelines

Spark's biggest testing challenge is the startup cost of a SparkSession. Solve it with a session-scoped fixture so it's created once for the whole test run, not once per test:

# tests/conftest.py
import pytest
from pyspark.sql import SparkSession

@pytest.fixture(scope="session")
def spark():
    spark = (
        SparkSession.builder
        .master("local[2]")
        .appName("pytest-spark")
        .config("spark.sql.shuffle.partitions", "2")  # keep local runs fast
        .getOrCreate()
    )
    yield spark
    spark.stop()

One gotcha with sharing a session across every test: anything a test leaves behind — a temp view, a changed config, a cached table — is still there for the next test, since they're the same session. If tests start passing or failing depending on execution order, that's usually the tell. A cheap guard is a small autouse fixture that clears temp views between tests, or dropping to scope="module" for the specific test file where isolation matters more than the extra setup cost.

Every test that needs Spark just requests the spark fixture:

def test_flag_high_value_orders(spark):
    df = spark.createDataFrame(
        [(1, 500.0), (2, 50.0)],
        ["order_id", "amount"],
    )

    result = flag_high_value_orders(df, threshold=100.0)

    rows = {r.order_id: r.is_high_value for r in result.collect()}
    assert rows == {1: True, 2: False}

Two libraries make Spark dataframe assertions much less painful than manual .collect() comparisons:

chispa — gives you assert_df_equality(result, expected, ignore_row_order=True) with readable diff output, similar in spirit to pandas.testing.assert_frame_equal. It's the most established option and still the one with the nicest failure messages.
pytest-spark — provides Spark-related fixtures and config out of the box if you don't want to hand-roll the session fixture above.

If you're on Spark 4.0+, it's also worth knowing PySpark now ships a built-in pyspark.testing.assertDataFrameEqual, so you can get row/column-order-insensitive comparisons without a third-party dependency. chispa still has the edge on diff readability, but the native option is a reasonable default if you'd rather not add a dependency.

from chispa import assert_df_equality

def test_join_orders_with_customers(spark):
    orders = spark.createDataFrame([(1, 10)], ["order_id", "customer_id"])
    customers = spark.createDataFrame([(10, "Acme")], ["customer_id", "name"])

    result = join_orders_with_customers(orders, customers)

    expected = spark.createDataFrame([(1, 10, "Acme")], ["order_id", "customer_id", "name"])
    assert_df_equality(result, expected, ignore_row_order=True, ignore_column_order=True)

For Spark specifically, mark these tests (@pytest.mark.spark) and consider keeping them out of the default fast test run — a two-node local session still takes a few seconds to spin up, which adds up across a large suite.

Part 6: mocking external systems

DE pipelines are full of edges that touch the outside world: S3, a warehouse, a REST API, a message queue. You don't want unit tests hitting real infrastructure — it's slow, flaky, and sometimes destructive.

moto mocks AWS services at the boundary so your code calls the real boto3 API but nothing actually leaves your machine:

import boto3
from moto import mock_aws

@mock_aws
def test_upload_writes_expected_key():
    s3 = boto3.client("s3", region_name="us-east-1")
    s3.create_bucket(Bucket="test-bucket")

    from my_pipeline.load import upload_parquet
    upload_parquet(s3, bucket="test-bucket", key="orders/2024-01-01.parquet", data=b"fake-bytes")

    objects = s3.list_objects_v2(Bucket="test-bucket")
    keys = [o["Key"] for o in objects["Contents"]]
    assert "orders/2024-01-01.parquet" in keys

For databases, prefer a real-but-disposable instance over mocking the driver whenever practical — e.g., SQLite in-memory for logic that's DB-agnostic, or a Dockerized Postgres/test schema for integration tests that need to check actual SQL behavior. Mocking a database connection to return canned fetchall() results tests your Python glue code, but it can't catch a broken JOIN or a typo in a column name — only a real query engine can.

The clock is an external dependency too, and it's an easy one to forget. Any pipeline logic that reasons about "today," "yesterday's partition," or "records from the last 24 hours" is implicitly depending on datetime.now() — which means the test's outcome depends on when you happen to run it, unless you pin it down. freezegun (or the newer time-machine, which does the same job faster) fixes this by mocking the clock itself:

from freezegun import freeze_time

def test_partition_path_uses_yesterday():
    from my_pipeline.extract import yesterday_partition_path

    with freeze_time("2024-03-15"):
        assert yesterday_partition_path() == "orders/dt=2024-03-14"

Without this, a test like the one above either hardcodes today's date (and quietly breaks tomorrow) or skips testing the date logic entirely — both worse options than mocking the one dependency that's actually causing the problem.

Part 7: data-quality-specific testing patterns

A few habits particular to data engineering that don't show up in typical backend testing:

Test schemas, not just values. A transformation can return the "right" numbers with the wrong column names or types, and a naive test that only checks a couple of cell values will miss it. Libraries like pandera (for pandas/Polars) let you assert against a schema as part of your test:

import pandera as pa
from pandera import Column, DataFrameSchema

order_schema = DataFrameSchema({
    "order_id": Column(int, unique=True),
    "amount": Column(float, pa.Check.ge(0)),
})

def test_transform_output_matches_schema(raw_orders):
    result = transform_orders(raw_orders)
    order_schema.validate(result)  # raises if schema doesn't match

This is the pandera use case from the code-testing column of the framework earlier in this article: a schema checked against a fixture you built by hand, inside a pytest test, as part of CI. The same order_schema object can just as easily validate a real dataframe pulled from production at pipeline runtime — at that point it's stopped being a code test and become a data test, even though the schema definition didn't change. Worth remembering which hat it's wearing in a given call site.

When the same transformation tests keep breaking from schema drift, that's a signal about which layer is doing the catching — not a sign you need to be more diligent about rewriting tests. A transformation unit test runs against a fixture you built and control. That fixture doesn't spontaneously change. So if a test keeps failing without you having touched it, on a schema that keeps moving underneath you, the test is usually quietly acting as an integration test — running against something closer to live data than a true fixture — which breaks the isolation this article has been arguing for since Part 1's "mock at the boundary" rule.

The fix isn't sturdier tests. It's moving the check to where it belongs:

Separate "I made a mistake" failures from "reality changed" failures. If updating one column definition in a schema fixes five failing tests at once, that's the tell that they were all downstream of the same unvalidated assumption, not five independent bugs.
pytest is the wrong tool for "does today's data match what I expected." That's a data-testing question — the same distinction this article draws early on, in "Where pytest fits — and where it doesn't" — and belongs to a schema/contract check, not a transformation test. Catching drift there means one clear failure ("upstream region column is now nullable") instead of five cryptic transformation-test failures with no obvious common cause.
Reuse the pandera schema as that gate. Define the input schema once, validate it at the pipeline's entry point before any transformation runs, and a schema change becomes one edit to that definition instead of a hunt through every test file that happened to hardcode an assumption about that column.
If it's an upstream team's schema changing, not your own, the durable fix is a data contract, not a better pipeline. A producing team declaring their schema — and a build failing loudly the moment they drift from it — turns "five tests broke on a Tuesday for no visible reason" into a versioned, reviewable change on their side instead of a mystery on yours.

Build small, deliberately ugly fixtures. Real production data is a bad test fixture — it's huge, it changes, and it obscures which specific case you're testing. A handful of hand-built rows that include a null, a duplicate, an empty string, and a negative number will catch more bugs than a 10,000-row sample of "normal" data ever will.

Separate pure transformation logic from I/O. A function like def transform(df: pd.DataFrame) -> pd.DataFrame is trivially unit-testable. A function like def run(): df = pd.read_csv(...); ...; df.to_sql(...) is not — you're forced into slow integration tests for everything. Structure pipelines as thin I/O wrappers around pure, well-tested transformation functions wherever you can.

Test failure paths, not just success paths. What happens when the input file is empty? When a required column is missing? When two upstream systems disagree on a foreign key? These are the tests that actually save you in production, and they're the ones people skip because writing the happy-path test already "felt done."

Use golden/reference datasets for complex aggregations. For a business-logic-heavy transformation (e.g., a multi-step revenue reconciliation), it's sometimes more maintainable to check a small input CSV against a small expected-output CSV committed to the repo, rather than constructing dataframes inline in every test.

Part 8: wiring it into CI

None of this pays off if it only runs on your laptop. A minimal GitHub Actions setup:

# .github/workflows/test.yml
name: tests
on: [push, pull_request]
jobs:
  test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Install uv
        uses: astral-sh/setup-uv@v7
        with:
          python-version: "3.11"
          enable-cache: true
      - run: uv sync --locked --all-extras --dev
      - run: uv run pytest tests/unit -v
      - run: uv run pytest tests/integration -v -m "not slow"

uv sync --locked installs exactly what's pinned in uv.lock and fails the build if the lockfile is out of date with pyproject.toml — the CI equivalent of "works on my machine" actually meaning something. astral-sh/setup-uv is the official action for installing uv itself; it can also pin the Python version the same way actions/setup-python used to, and enable-cache: true caches uv's package store between runs so later builds skip re-downloading dependencies that haven't changed.

Keeping unit and integration runs as separate steps means a failing integration test doesn't hide a failing unit test in the same log, and you get faster feedback from the unit step first.

Putting it together: a minimal but real test suite

tests/
├── conftest.py              # shared fixtures: sample dfs, spark session, tmp helpers
├── unit/
│   ├── test_transform.py    # pure functions, pandas/polars asserts, parametrize-heavy
│   └── test_validation.py   # schema checks, edge cases
└── integration/
    ├── test_spark_jobs.py   # marked @pytest.mark.spark, uses chispa
    ├── test_s3_io.py        # uses moto
    └── test_end_to_end.py   # runs the full pipeline against tmp_path input/output

The core habits worth taking away

Know which kind of testing you're doing. pytest catches bugs in your code, using data you control. It cannot catch a data quality problem that only exists in today's actual data — that's a separate job for dbt test, Great Expectations, or Soda. If a transformation test keeps breaking from schema drift with nothing changed on your end, that's a sign the check belongs one layer earlier, not a sign to rewrite the test again.
Test the transformation logic, not the framework. You don't need to test that pandas' groupby works — you need to test that your aggregation logic does the right thing.
Keep unit tests fast and dependency-free; push anything touching a real file, database, or cluster into a clearly separated, clearly marked integration suite.
Build small, deliberately messy fixtures instead of testing against production-sized samples.
Use the domain-specific assertion helpers (assert_frame_equal, assert_df_equality, pandera schemas) instead of hand-rolled comparisons — they exist because naive equality checks on dataframes are full of footguns.
Mock at the boundary, not inside your own logic — and remember the clock counts as a boundary too. If mocking feels awkward, it's often telling you to refactor, not to mock harder.

None of this requires a big investment up front. Start by putting AAA-structured unit tests around your messiest transformation function, get comfortable with fixtures and parametrize, and expand outward from there. The payoff compounds fast: every bug a test catches before a stakeholder sees a bad number is time you don't spend doing forensic debugging on a Friday afternoon.

Security & Governance for Data Networking

Nariman Baubekov — Tue, 01 Sep 2026 16:34:34 +0000

Security & Governance for Data Networking

Part 6 of 6 in the Networking & VPC Foundations for Data Engineering series

We've built the pieces (Part 1), compared how AWS and GCP implement them (Part 2), assembled a real 3-tier architecture (Part 3), closed the gap to managed data services (Part 4), and connected multiple VPCs together (Part 5). This last part is about a different kind of question: how do you know whether what you built is actually secure — not on day one, but six months later, after a dozen people have touched it?

This is where most real-world network security failures actually happen. Not from someone deliberately building an insecure architecture, but from a correctly designed one slowly drifting as changes accumulate.

Least Privilege, Applied to Networks

Every principle in this section is really one idea wearing different outfits: a resource should only be reachable by exactly what needs to reach it, on exactly the port it needs, and nothing else. A few concrete applications of that, specific to data platforms:

Your data warehouse's security group / firewall rule should name the specific app-tier security group or service account that's allowed in — never a broad CIDR range, and never "allow from anywhere."
Compute clusters (EMR, Dataproc, self-managed Spark) should run in private subnets with no external IP, by default, every time — not as an exception you remember to configure.
Admin access should go through SSM Session Manager or IAP (Part 3), not a standing bastion host with a permanently open port.
If a private-subnet resource doesn't need outbound internet at all — many data-tier resources don't — don't route it through NAT "just in case." No route out is a stronger guarantee than a route out plus a rule that's supposed to block it.

None of this is exotic. The hard part isn't knowing these rules — it's that they quietly stop being true over time unless something is actively checking.

How Configurations Drift

A few patterns account for the overwhelming majority of real incidents:

A rule added for a one-time fix that never gets removed. Someone needed temporary access to debug something, opened up a security group or firewall rule to make it happen, and it's still there a year later.
A rule that's technically correct but way broader than intended. 0.0.0.0/0 on a port that only needed one specific IP range, because it was faster to write during an incident.
Shadowed rules. In rule systems that evaluate in order — GCP firewall rule priorities, AWS NACL rule numbers — a more permissive rule earlier in the sequence silently overrides a more restrictive one added later: the restrictive rule looks like it's protecting you, but it's never actually evaluated. (AWS security groups don't have this problem — every rule is evaluated, with no ordering — which cuts both ways: you also can't neutralize an overly broad SG rule by adding a stricter one.)
Route tables changed for an unrelated reason — one route table edit silently reclassifies a subnet's internet reachability, with zero code changes on the data team's side.

The fix for all four isn't vigilance — humans are bad at noticing things that used to be right and quietly became wrong. The fix is tooling that checks continuously, plus a review cadence that doesn't depend on someone remembering.

Tools That Actually Catch This

AWS:

VPC Flow Logs — captures accepted and rejected traffic at the ENI, subnet, or VPC level. The raw material for almost every other investigation.
VPC Reachability Analyzer — answers "can resource A actually reach resource B?" by tracing the full path (route tables, security groups, NACLs) without sending any real traffic. Genuinely useful for confirming a fix worked before you find out the hard way.
Network Access Analyzer — scans for unintended network access at scale, e.g. "which resources are reachable from the internet that shouldn't be."

GCP:

VPC Flow Logs — same core idea as AWS's version, captured per subnet.
Firewall Insights (part of Network Intelligence Center) — automatically flags shadowed rules, overly permissive rules, and rules that have never matched any real traffic. This is the direct answer to failure mode #3 above, and it's worth running as a matter of routine, not just when something's already gone wrong.
Network Analyzer — continuously monitors your VPC configuration and proactively surfaces misconfigurations and suboptimal routing, correlating failures with recent config changes to point at likely root causes.
Connectivity Tests — GCP's equivalent of Reachability Analyzer: simulate a packet path between two points and get a definitive allowed/blocked answer, with the reason why.

None of these tools are a substitute for the design principles above — they're what catches the moment those principles quietly stop being true.

A Working Review Checklist

When you're checking a data platform's network posture — your own, or reviewing someone else's — this is roughly the order that actually finds problems, largely mirroring the debugging order from Part 1, now run proactively instead of reactively:

Run this quarterly at minimum, and definitely after any significant architecture change — a new VPC connection (Part 5), a new managed service integration (Part 4), or a new team onboarding onto shared infrastructure.

Closing the Loop

Back in Part 1, the whole series opened with a diagram nobody had actually explained, and a job that couldn't reach an external API for a reason nobody could immediately see. Six parts later, that diagram isn't mysterious anymore: it's CIDR blocks and subnets (Part 1), implemented slightly differently depending on which cloud you're on (Part 2), arranged into tiers of decreasing trust (Part 3), with a private path to your data services that skips the public internet entirely (Part 4), connected to whatever other networks your organization actually needs (Part 5) — and now, checked continuously instead of trusted blindly (Part 6).

That's the whole foundation. Everything past this point — Kubernetes networking, service meshes, more exotic hybrid-cloud topologies — is built on exactly these same primitives, just with more layers on top. If this series did its job, none of it should feel like starting from zero.

Multi-VPC and Multi-Project Architectures

Nariman Baubekov — Tue, 01 Sep 2026 16:31:28 +0000

Multi-VPC and Multi-Project Architectures

Part 5 of 6 in the Networking & VPC Foundations for Data Engineering series

Every diagram in this series so far has shown one VPC. Real organizations rarely stay that small — you end up with separate environments (dev, staging, prod), separate teams with their own AWS accounts or GCP projects, sometimes a data platform that's deliberately isolated from everything else for compliance reasons. All of those networks eventually need to talk to each other, and how you connect them matters a lot more than it looks at first glance.

AWS: VPC Peering vs. Transit Gateway

VPC Peering connects two VPCs directly, using AWS's private backbone — no hourly charge, low latency, straightforward to set up. You request the connection, the other side accepts it, you update route tables on both ends, done.

The catch is the same one we flagged back in Part 2: peering isn't transitive. If VPC A peers with B, and B peers with C, A still cannot reach C — B doesn't relay traffic on A's behalf. Connect 4 VPCs with peering and you need 6 individual connections; get to a few dozen VPCs and you're maintaining a genuinely unmanageable mesh of point-to-point links and route table entries.

That's what 4 VPCs fully connected via peering looks like: 6 connections, and it only gets worse from there. This is exactly the problem Transit Gateway solves — a central hub that every VPC attaches to once. Attach 100 VPCs, and you have 100 attachments instead of thousands of pairwise connections, and — critically — routing between them is transitive through the hub.

Transit Gateway isn't free, though — it charges an hourly rate per attachment plus a per-gigabyte processing fee, and it adds a small routing hop of latency compared to peering's direct path. For a data platform specifically: if you're centralizing ingestion from many isolated source VPCs, or connecting a handful of stable environments (dev/staging/prod) that rarely change, peering is often genuinely fine. Once you're managing more than a handful of VPCs, need hybrid on-premises connectivity in the same place, or want centralized routing policy, Transit Gateway earns its cost.

One more peering limitation worth knowing before you design around it: CIDR blocks can't overlap. Two VPCs that both happen to use 10.0.0.0/16 simply cannot be peered — a real problem after a merger or when two teams picked the same "obvious" default range independently. Transit Gateway can at least contain the damage — overlapping attachments can be walled off into separate route table domains — but the two ranges still can't talk to each other, so it's containment, not a fix. The real solution is avoiding overlapping ranges from the start.

GCP: Shared VPC vs. Network Connectivity Center

GCP splits this same problem into two different tools, aimed at two different situations.

Shared VPC isn't primarily about connecting separate networks — it's about one team centrally managing a network that other teams' projects use directly. A "host project" owns the VPC, subnets, and firewall rules; other "service projects" get resources (VMs, GKE clusters, Dataproc jobs) attached directly into those shared subnets, as if they lived in the host project's network all along. There's no peering, no route exchange to configure — because there's really only one network underneath.

This is a genuinely common pattern for data platforms: a central platform team owns the Shared VPC (and its Cloud NAT, its Private Google Access settings, its firewall rules), while individual data and ML teams get their own projects — separate billing, separate IAM boundaries — without needing to reinvent network setup each time.

Network Connectivity Center (NCC), by contrast, is GCP's actual hub-and-spoke connectivity tool — the closer equivalent to Transit Gateway. It connects genuinely separate VPCs (plus on-premises VPN or Interconnect links) through a central hub, with automatic route exchange between spokes.

The rule of thumb GCP itself gives: use plain VPC peering for a simple two-network connection; reach for NCC once you're dealing with more than a handful of networks, need transitive routing between them, or want to fold in on-premises connectivity centrally. Don't reach for Shared VPC as a substitute hub-and-spoke tool — it was built for centralized ownership of one network across projects, not for connecting many independent networks together.

Quick Comparison

	AWS	GCP	Best for
Direct, simple link	VPC Peering	VPC Peering	A few stable, long-lived VPC pairs
Centralized hub, many networks	Transit Gateway	Network Connectivity Center	Dozens of VPCs, hybrid on-prem, transitive routing
One network, many teams	—	Shared VPC	Centralized platform team, decentralized project ownership

Bringing a Data Platform Example Together

A fairly typical shape for a mid-sized data platform: a central ingestion VPC (or Shared VPC on GCP) that owns the NAT/Cloud NAT setup and receives data from various sources, connected via Transit Gateway or NCC to separate processing VPCs per team or environment, each with their own 3-tier layout from Part 3. Isolation where it's needed, shared infrastructure where it isn't — and no team accidentally getting network access they were never supposed to have, because the hub enforces exactly which spokes can reach which.

What's Next

We've now covered the full shape of a multi-VPC data platform — but plenty of the security and cost decisions along the way have been mentioned only in passing: least-privilege network design, common misconfigurations that pass an initial review but fail an audit, and how to actually check a platform's network posture before something goes wrong. Part 6, the final piece of this series, pulls all of that together.

Private Connectivity to Data Services: Closing the Last Gap

Nariman Baubekov — Tue, 01 Sep 2026 16:27:13 +0000

Private Connectivity to Data Services: Closing the Last Gap

Part 4 of 6 in the Networking & VPC Foundations for Data Engineering series

Quick gut check before we start: in Part 3's 3-tier architecture, when your Spark cluster in the private subnet needs to read from S3 or write to BigQuery, where does that traffic actually go?

If your answer is "out through NAT, same as any other internet call" — you're right, and that's the gap this article closes. Even though S3 and BigQuery are the cloud provider's own services, sitting on the same network as your VPC, traffic to them by default still leaves through your NAT Gateway or Cloud NAT and travels over the public internet as far as routing is concerned. It works, but it's not actually private, and it's not free.

Why This Is Worth Fixing

Three concrete reasons this matters for a data platform specifically:

Cost. NAT charges per gigabyte processed. Data pipelines reading and writing large volumes to S3 or GCS through NAT can rack up meaningful NAT data-processing charges for traffic that never needed to leave the provider's network in the first place.
Security posture. "Traffic to our data services goes over the public internet" is exactly the kind of finding that fails a security review, even if the traffic is encrypted in transit.
It's avoidable. Both clouds offer a way to route this traffic entirely within their private backbone — no NAT, no internet exposure, often no cost at all for the most common services.

AWS: Gateway Endpoints vs. Interface Endpoints (PrivateLink)

AWS actually gives you two different mechanisms here, and which one you use depends on the service:

Gateway Endpoints — for S3 and DynamoDB only. These work by adding a route to your route table pointing at the service, with no new network interface, no hourly charge, and no data-processing charge at all. If your data platform's main need is private S3 access, this is the one you want, and there's no real reason not to enable it.

Interface Endpoints — powered by AWS PrivateLink — cover the other 100+ AWS services (and third-party SaaS via the AWS Marketplace) that Gateway Endpoints don't reach. These create an actual network interface (ENI) with a private IP inside your subnet, governed by a security group like any other resource. They carry an hourly charge per AZ plus a data-processing fee — not free, but still typically far cheaper than the NAT charges they replace.

One newer option worth knowing about: AWS introduced Resource Endpoints, which let a VPC privately reach a specific resource — like an RDS instance — in another VPC or on-premises, without needing a Network Load Balancer in front of it. If you're designing a fresh multi-VPC data platform today, it's worth a look, though Gateway and Interface Endpoints still cover the overwhelming majority of day-to-day data engineering needs.

GCP: Private Google Access vs. Private Service Connect

GCP splits this along a similar line, though the mechanics differ:

Private Google Access — a simple, subnet-level setting. Turn it on, and instances with no external IP in that subnet can reach Google APIs (Cloud Storage, BigQuery, Pub/Sub, etc.) using Google's own shared IP ranges for those APIs. No new resource to manage, no direct cost. This is the GCP rough-equivalent of AWS's Gateway Endpoints in spirit — flip a switch, get private access to the common case.

Private Service Connect (PSC) — the more flexible, more deliberate option. Instead of relying on Google's shared public-API IP ranges, PSC creates an actual endpoint with your own internal IP address inside your VPC, which then privately forwards traffic to the target service. PSC covers Google APIs (with more routing/DNS control than Private Google Access gives you) and — this is the part that maps most directly to AWS PrivateLink — lets you privately reach services published by other VPCs or third parties, including your own internal services shared across teams.

The rule of thumb: start with Private Google Access for straightforward private access to Google's own APIs. Reach for PSC when you need your own IP addressing, tighter DNS/routing control, or you're connecting to something that isn't a first-party Google API at all.

Side by Side

	Scope	Cost	Mechanism
AWS Gateway Endpoint	S3, DynamoDB only	Free	Route table entry
AWS Interface Endpoint (PrivateLink)	100+ AWS services, SaaS	Hourly + per-GB	ENI with private IP
GCP Private Google Access	Google APIs	Free	Subnet-level setting
GCP Private Service Connect	Google APIs + third-party/internal services	Varies by target	Endpoint with your own internal IP

The DNS Piece Nobody Mentions Until It Bites You

Here's a detail that trips people up on both clouds: creating the endpoint isn't the whole job. Your application is almost certainly still resolving s3.amazonaws.com or storage.googleapis.com to the service's public IP by default — which means it'll try to reach that public IP, and depending on your routing, that might still mean going out through NAT, or simply failing if there's no route out at all.

To actually route traffic through the endpoint, DNS resolution for that service needs to return the endpoint's private address instead of the public one. AWS Interface Endpoints handle this via private DNS, which — when enabled on the endpoint — overrides the service's standard DNS name within your VPC to resolve privately. GCP's PSC endpoints similarly need appropriate private DNS zone configuration pointing at the endpoint's internal IP.

If you've done everything right — endpoint created, security group/firewall configured — and traffic still isn't using it, DNS resolution is the first thing to check. It's the same lesson from Part 1's DNS section, just showing up again in a new context.

What's Next

We've been assuming a single VPC this whole series. Real organizations rarely stop at one — different environments, different teams, sometimes different AWS accounts or GCP projects entirely, all needing to talk to each other. Part 5 covers how: VPC Peering, Transit Gateway, and Shared VPC.

The 3-Tier VPC: Building a Real Architecture

Nariman Baubekov — Tue, 01 Sep 2026 16:20:00 +0000

The 3-Tier VPC: Building a Real Architecture

Part 3 of 6 in the Networking & VPC Foundations for Data Engineering series

We've spent two articles on pieces: CIDR blocks, subnets, routing, DNS, NAT in Part 1; how AWS and GCP each implement those pieces in Part 2. Time to assemble them into something you'd actually deploy.

The pattern we're building is the one you'll see, in some form, behind nearly every production data platform: the 3-tier VPC. Three layers, each more locked-down than the last.

The Shape of It

Notice the arrows: the internet can reach Tier 1, and only Tier 1. Tier 2 is reachable from Tier 1, but not directly from the internet. Tier 3 is reachable only from Tier 2 — nothing else touches it, ever. Each tier is a stricter version of the last, and that strictness is the entire point: a compromised load balancer doesn't hand an attacker your database, because there's no direct path between them.

Let's build this concretely, tier by tier, on both AWS and GCP.

Tier 1: The Public Subnet

This is the only tier with a route to an Internet Gateway. What lives here is deliberately narrow:

Load balancers (ALB/NLB on AWS, external Load Balancer on GCP) — the front door for real traffic
NAT Gateway / Cloud NAT — so Tier 2 can reach the internet outbound without living here itself
Bastion host (or its modern replacement — more on this below) — the narrow door for human administrative access

Everything else does not belong in Tier 1. Not your database, not your compute, not your internal services — even though it would technically still be protected by security groups/firewall rules, minimizing what's reachable at the network layer is a much stronger guarantee than trusting every instance-level rule to be configured correctly forever.

Tier 2: The Private Subnet — Where Your Compute Actually Lives

This is home for your Spark cluster, Airflow workers, EMR/Dataproc nodes, application servers — the things that need to do work and occasionally reach the internet (installing a package, calling an external API), but should never be reachable from the internet.

AWS: route table sends 0.0.0.0/0 to the NAT Gateway sitting in Tier 1
GCP: instances here simply have no external IP; Cloud NAT (attached via a Cloud Router) handles outbound access

Tier 3: The Data Subnet — Fully Isolated

Your RDS instance, your internal warehouse endpoint, anything holding the data itself. This tier often doesn't even get outbound internet access — there's rarely a legitimate reason for a database to initiate a connection to the public internet.

AWS: often no route to 0.0.0.0/0 at all — not even via NAT
GCP: same idea — no external IP, and you can skip attaching this subnet's range to Cloud NAT entirely

Access into Tier 3 is granted narrowly: only Tier 2's security group (AWS) or Tier 2's service account (GCP) is allowed in, on exactly the port the database needs.

A Concrete AWS Build

Here's what this looks like as an actual subnet layout, spread across two AZs for availability:

Subnet	CIDR	AZ	Tier
Public Subnet 1	`10.0.1.0/24`	AZ 1	Public
Private Subnet 1	`10.0.2.0/24`	AZ 1	App
Public Subnet 2	`10.0.3.0/24`	AZ 2	Public
Private Subnet 2	`10.0.4.0/24`	AZ 2	App

(A data tier would typically add 10.0.10.0/24 and 10.0.11.0/24, one per AZ, following the same pattern.)

And the security group chain that governs traffic between tiers:

Each security group only trusts the one before it — never the internet directly, past the first hop. This is the AWS security group chaining pattern, and it's worth internalizing: it's the mechanism, not just this specific example, that you'll reapply constantly.

The Same Build on GCP

GCP gets you the same three-tier isolation, but remember from Part 2: there's no subnet-level "public" flag here — it comes down to external IPs and firewall rules.

Instead of chaining security group IDs, you chain network tags (or service accounts) through firewall rule sources. The load balancer's backend instances carry an lb tag; the firewall rule for your app tier says "allow traffic tagged lb"; the database tier's rule says "allow traffic tagged app." Same layered-trust outcome, different mechanism — tags and service accounts instead of security group references.

The Narrow Door: Bastion Hosts

Even with three tiers locked down, someone eventually needs to actually log into a Tier 2 or Tier 3 instance — to debug a stuck Spark job, check disk space, whatever. You don't want to open SSH from the internet directly to that instance. The bastion host (sometimes called a jump host) exists to solve exactly this.

A bastion is a single, hardened instance sitting in the public subnet, whose security group allows SSH from a narrow, known set of IPs — your office, your VPN range, nowhere else. Every private-subnet instance's security group, in turn, allows SSH only from the bastion. You hop through it to reach anything private. One door, tightly guarded, heavily audited — rather than every private instance having its own exposed SSH port.

The Modern Alternative: Skip the Bastion Entirely

Both clouds now offer a way to get the same outcome — controlled admin access to private instances — without running a bastion host at all, and without opening an inbound port anywhere:

AWS: SSM Session Manager. No inbound port, no SSH keys to manage, no bastion instance to patch and pay for. Access is governed entirely by IAM policy, and every session is logged automatically.
GCP: Identity-Aware Proxy (IAP) TCP forwarding. Same idea — you connect to the private instance through IAP's proxy, authenticated by your Google identity and IAM role, with no external IP and no open SSH port required on the instance at all.

The trade-off is close to nonexistent at this point — both are the recommended default on their respective clouds, and a real bastion host is increasingly something you'd only stand up for a specific compliance requirement or a legacy workflow that hasn't been migrated yet. If you're designing a new data platform today, start with SSM or IAP and only reach for a traditional bastion if something specifically forces your hand.

Putting the Whole Thing Together

This is, structurally, most of what you need to know to read any production data platform's network diagram — including the one from the intro of Part 1. Three tiers, decreasing trust, one narrow administrative door that increasingly doesn't even need to be a door anymore.

What's Next

So far, every private-subnet resource that needs to reach a managed service — S3, BigQuery, a managed Kafka cluster — has been going out through NAT, over the public internet, even though the traffic never leaves the cloud provider's own network in practice. Part 4 covers how to close that gap entirely: VPC endpoints / PrivateLink on AWS and Private Service Connect on GCP, so that traffic to your data services never touches the public internet at all — not even for a private subnet with NAT egress.

DEV Community: Nariman Baubekov

Parquet, Explained: How One File Format Quietly Won the Data World

Contents

It's 2013, and analytics has a reading problem

Store it sideways

Inside a Parquet file

The life of a query

How the compression sausage gets made

Why it won

The honest cons

When to use it (and when not to)

The trick that sounds fake: sort by cardinality, low to high

Step 1: Meet your columns as Parquet sees them

Step 2: Why lowest cardinality first, specifically?

Step 3: There's a second, hidden prize — data skipping

Step 4: Do it

The fine print on sort order

More lifehacks in the same spirit

Closing thought

Kimball's Last Hard Problem: When There Is No Right Grain

The 60-second recap

Meet the conflict

Why each one is correct, on its own terms

What breaks if you force one grain to answer all three

The resolution: three fact tables, one shared dimension

The three "what's the number" queries

The governance move: name the metrics, don't blend them

A note on the whole series: conformed dimensions were always the point

Common mistakes

Exercises

Closing: the whole series in one paragraph

Resources

Kimball for Many-to-Many: Bridge Tables, Weighting Factors, and the Diagnosis Code Problem

The 60-second recap

Meet Meadowlark Health

The fixed-width hack, again

The bridge table

Why the weighting factor exists: you cannot just join and sum

Where the weighting factor breaks

Not every bridge needs a weighting factor

Factless facts, revisited: a bridge without any fact at all

A real query

Common mistakes

Exercises

What's next

Resources

Kimball for Order Fulfillment: Milestones, Split Shipments, and Facts That Arrive Late

The 60-second recap

Meet Crate Expectations

The grain fight: order, shipment, or package?

fact_order_lifecycle: the accumulating snapshot

Late-arriving facts: when the webhook lies about order

fact_shipment: the finer grain the order-level fact can't give you

Semi-additive measures and the question a snapshot alone can't answer

A real query

Common mistakes

Exercises

What's next

Resources

Kimball for SaaS: Subscriptions, MRR, and Churn, Modeled Right

The 60-second recap (if you skipped the coffee shop)

Meet Tabby

The subscription grain problem

Account dimension & hierarchy

SCD Type 2 in depth: plan changes are THE case

The "as-of" query

The "current only" query

Doing the SCD2 update

Accumulating snapshot: the trial → paid → churn lifecycle

The update pattern

Periodic snapshot: monthly MRR

The MRR waterfall

Usage / event fact: high-volume, separate from billing

Factless fact table: entitlements

A real query: net revenue retention

Common SaaS-specific mistakes

Exercises

What's next

Resources

Kimball Dimensional Modeling, Explained Through a Coffee Shop

`fact_order_lifecycle`: the accumulating snapshot

`fact_shipment`: the finer grain the order-level fact can't give you

`conftest.py`: sharing fixtures across files