DEV Community: Nariman Baubekov

PyArrow, Explained: How Zero-Copy Actually Works, and Why It's Everywhere

Nariman Baubekov — Tue, 08 Sep 2026 09:11:37 +0000

Every time data moves between two tools in a modern Python data stack — a DuckDB query result becoming a pandas DataFrame, a Polars frame getting handed to a PySpark job, a warehouse client returning query results to a notebook — something has to decide how that data is laid out in memory on both ends, and whether crossing that boundary means copying it. Most of the time, historically, it did: pickle it, walk every value, rebuild it on the other side. Apache Arrow exists to make that copy unnecessary, and PyArrow is the Python implementation of it. This article is about what Arrow actually is, the specific mechanism that makes "zero-copy" a literal, checkable claim rather than a marketing phrase, and where it shows up across a real data engineering stack — including a real benchmark, not an assumed one.

What Arrow actually is

If you've read the companion piece on Parquet, you already have the right instinct for this: Parquet is a file format, specifying how columnar data should be laid out on disk. Apache Arrow is a memory format — a precise specification for how columnar data should be laid out in RAM, inside a running process. Different problem, same underlying idea: lay data out by column, not by row, because it's faster to scan and compress that way.

The detail that makes Arrow a genuinely different thing from Parquet isn't cross-language support — Parquet has that too, just as much as Arrow does. It's what each format is actually optimized for:

Parquet trades compute-readiness for compact storage. It's compressed and encoded (dictionary encoding, run-length encoding, general-purpose compression) to take up as little space as possible on disk. You can't compute on it directly — it has to be decoded first, every time, by whatever's reading it.
Arrow trades storage compactness for direct computability. It's already the decoded, ready-to-compute-on layout — uncompressed, fixed-width buffers a CPU can operate on directly, with no decode step standing between "here's the memory" and "here's usable data."

That's specifically what makes sharing Arrow between processes free, in a way sharing Parquet isn't: there's no decode step to pay on the way in.

The actual memory layout — buffers, not objects

Here's the detail that explains almost everything else in this article. A Python list of strings, or a NumPy array with dtype=object (which is what pandas falls back to for strings), doesn't actually store the strings contiguously. It stores an array of pointers, each one referencing a separate Python string object sitting wherever the memory allocator happened to put it:

An Arrow array of the same three strings is built from a small, fixed number of contiguous memory buffers instead:

A validity bitmap — one bit per value, marking nulls. (Arrow's null handling is a bitmap sitting alongside the data, not a sentinel value mixed into it — this is also why Arrow's nullable-integer support is cleaner than NumPy's, which has no way to represent a null integer without upcasting the whole column to float.)
An offsets buffer — for variable-length types like strings, an array of integers marking where each value starts and ends inside the values buffer.
A values buffer — the actual bytes, laid out back-to-back, with no per-value object overhead and no pointer to chase to reach the next one.

This matters for two independent reasons, and it's worth keeping them separate:

It's faster to compute over, even within one process. Contiguous, uniformly-typed memory is exactly what lets a CPU vectorize (process several values per instruction) and stay cache-friendly (reading value n+1 doesn't mean jumping somewhere else in RAM the way following a pointer does). This is the same columnar-layout argument the Parquet article makes for reading off disk, just one level closer to the CPU.
It's what makes the memory shareable between processes and languages at all. A Python object array is full of language-specific state (Python's own object headers, reference counts) that only Python's runtime understands. A block of Arrow buffers is just bytes with a schema — nothing in it is specific to any language's runtime, which is the precondition for the next section.

Why it's called zero-copy — the actual mechanism

"Zero-copy" gets thrown around loosely enough that it's worth being precise about what specifically makes it true. The mechanism is called the Arrow C Data Interface, and it's smaller than you'd expect: a handful of plain C struct definitions (ArrowArray, ArrowSchema, and a streaming variant ArrowArrayStream) that any project can copy directly into its own source tree. There's no library to link against and no shared build dependency — two completely independently-compiled programs, in two different languages, can exchange an Arrow array at runtime just by agreeing on the layout of these structs and passing a pointer to one. Non-C/C++ languages participate through their own FFI layer — Python via ctypes/cffi, similarly for Rust, Go, and Julia.

Compare that to what pickle, JSON, or protobuf actually do: walk every value in the source structure, transform it into a different byte layout entirely, send that stream across, and have the receiving side parse it back into a new, freshly-allocated structure. That's real, unavoidable work that scales with the size of the data. Arrow's C Data Interface skips all of it — when two Arrow-aware libraries hand data to each other within the same process, they're not converting anything; they're agreeing to both look at the same buffers.

One nuance worth having precisely right: this specific mechanism is for sharing memory within a single process — the pointer only means something as long as both sides can address the same memory. The moment data genuinely needs to leave the process (over a network, to disk, to a separate machine), Arrow uses a different piece of the spec, the Arrow IPC format — but even that is barely a serialization step by the standards of pickle or JSON: IPC is close to writing the same in-memory buffers out sequentially with a thin framing header, not reshaping the data into a different structure. Feather files are just Arrow IPC written to disk. Arrow Flight, covered below, is Arrow IPC streamed over gRPC.

Proving it, not just asserting it

Rather than take the "zero-copy is fast" claim on faith, here's a real, reproducible comparison: handing a table from DuckDB to Polars via Arrow, versus a genuine serialization roundtrip (materializing rows, json.dumps, json.loads, then rebuilding a DataFrame) — timed at increasing row counts.

import time, json
import duckdb
import polars as pl

def bench_zero_copy(n_rows):
    con = duckdb.connect()
    con.execute(f"""
        CREATE TABLE t AS
        SELECT i AS id, (i % 100) AS bucket, CAST(i AS DOUBLE) * 1.5 AS amount
        FROM range({n_rows}) t(i)
    """)
    start = time.perf_counter()
    arrow_table = con.execute("SELECT * FROM t").arrow()   # DuckDB -> Arrow
    df = pl.from_arrow(arrow_table)                          # Arrow -> Polars
    return time.perf_counter() - start

def bench_serialize_roundtrip(n_rows):
    con = duckdb.connect()
    con.execute(f"""
        CREATE TABLE t AS
        SELECT i AS id, (i % 100) AS bucket, CAST(i AS DOUBLE) * 1.5 AS amount
        FROM range({n_rows}) t(i)
    """)
    start = time.perf_counter()
    rows = con.execute("SELECT * FROM t").fetchall()
    parsed = json.loads(json.dumps(rows))                   # the actual reformatting work
    df = pl.DataFrame(parsed, schema=["id", "bucket", "amount"], orient="row")
    return time.perf_counter() - start

Run on this machine — after one warm-up call to absorb import/connection-setup costs, and taking the median of 5 runs at each row count, since a single cold run swings around enough to be misleading on its own:

Rows	Zero-copy median (s)	Serialize roundtrip median (s)	Ratio
10,000	0.0009	0.0138	15x
100,000	0.0038	0.1428	38x
1,000,000	0.0133	2.0589	154x
5,000,000	0.1698	11.7037	69x

Two honest notes on this table, since fabricated-looking precision is worse than useful imprecision: these are wall-clock numbers from one machine under median-of-5 conditions, so absolute values — and even the exact ratio at each row count, which bounces around here rather than climbing smoothly — will vary with hardware, load, and how DuckDB's query planner happens to behave at each size. Don't treat 15x/38x/154x/69x as a formula. What's consistent and worth trusting is the shape: the zero-copy path stays under two-tenths of a second even at 5 million rows, while the serialization path grows to nearly 12 seconds over the same range, because it's doing real per-value work — building a byte stream, then rebuilding objects from it — that the zero-copy path simply never does.

Where this shows up in Spark and Databricks

This is the part that's easy to undersell as "Arrow makes Spark faster" without saying how. A plain Python UDF in PySpark serializes data row-by-row across the JVM↔Python process boundary — real, per-row overhead, the same category of cost as the JSON roundtrip above. Pandas UDFs (vectorized UDFs) fix this by batching rows and moving them across that boundary as Arrow data instead, which is where the commonly-cited "up to 100x faster than row-at-a-time UDFs" figure comes from — it's the same mechanism this article just demonstrated, applied specifically to the JVM/Python boundary.

Even Pandas UDFs still pay one small cost worth knowing about: converting an Arrow batch into a pandas Series isn't always free, particularly around null handling, so there's a real Arrow→pandas conversion step in between. Databricks introduced a further evolution in 2026: native Arrow UDFs, which operate directly on pyarrow.Array / RecordBatch objects and skip the pandas conversion entirely — one less hop between "data arrives as Arrow" and "your function runs on it."

The rest of the ecosystem

pandas 2.0+. You can back an entire DataFrame with Arrow dtypes instead of NumPy (dtype_backend="pyarrow") — better nullable-type support, and less silent type coercion than NumPy's object-dtype fallback for strings.
Parquet I/O. PyArrow is one of pandas' two Parquet engines (engine="pyarrow"), and generally the more spec-complete of the two — the direct link back to the companion article on this profile.
DuckDB. Genuinely tight integration: duckdb.sql(...).arrow() hands back a PyArrow table, and DuckDB can query a PyArrow table in memory as if it were a SQL table, no loading step in either direction.
Polars — worth being precise here: Polars doesn't depend on the pyarrow package internally; it has its own Rust-native Arrow implementation. But it speaks the same Arrow format, which is why pl.from_arrow() on a PyArrow table is cheap rather than a real conversion.
Arrow Flight / Flight SQL. A gRPC-based transport built specifically to move Arrow data between systems fast — increasingly the transport layer for BI tools and newer database drivers, instead of row-oriented ODBC/JDBC.
ADBC (Arrow Database Connectivity). An emerging standard positioning itself as ODBC/JDBC's Arrow-native successor. Snowflake's and BigQuery's Python connectors can already hand back query results as Arrow tables directly, rather than a row-by-row cursor.

When to reach for the `pyarrow` API directly

Most of the time, Arrow is working underneath a tool you're already using and you never touch the pyarrow package by name. A few situations where reaching for it directly is the right call:

pyarrow.compute — a library of vectorized functions (filtering, string operations, aggregations) that operate straight on Arrow arrays, useful when you want columnar-speed operations without pulling in the rest of pandas or Polars.
Writing Parquet with fine control — pyarrow.parquet exposes the row-group size, compression codec, and encoding options the Parquet article covers, when a higher-level df.to_parquet() call doesn't expose the knob you need.
Arrow Flight, if you're building (not just consuming) a system that needs to move large columnar results between processes or machines fast.

Where the "zero-copy" story has real edges

In the spirit of not overselling this: not everything touching Arrow is actually zero-copy, and it's worth knowing where that breaks down before you assume it everywhere.

Casting between incompatible types still copies. Converting an Arrow int32 array to int64 has to build a new buffer at the new width — there's no way around that being real work.
Combining chunked arrays can copy. Arrow tables are often stored as multiple chunks (e.g., one per batch read from a file); an operation that needs a single contiguous array sometimes has to concatenate chunks first, which is a real copy, not a pointer handoff.
Crossing into pandas' legacy NumPy object dtype for strings still copies, because that representation is fundamentally different from Arrow's buffer layout — this is exactly why pandas 2.0's Arrow-backed dtype option exists, to avoid needing to make that crossing at all.

None of these are bugs — they're places where the data genuinely has to change shape, and Arrow doesn't pretend otherwise. The claim isn't "nothing ever copies." It's "moving the same logical data between two systems that already agree on its layout doesn't have to," which is a much stronger and much more specific claim — and, per the benchmark above, a true one.

Takeaways

Arrow is a memory format, not a library trick — the same byte layout works across languages, which is the precondition for zero-copy to mean anything.
The mechanism is the Arrow C Data Interface: a tiny, stable set of C structs that let independently-built libraries hand off a pointer instead of re-serializing data.
"Zero-copy" is a real, measurable claim, not a marketing word — the benchmark above shows the gap growing, not shrinking, as data gets larger.
PyArrow shows up almost everywhere in a modern Python data stack — pandas, Polars, DuckDB, Spark/Databricks, warehouse connectors — usually invisibly, which is exactly the point.
It's not infinitely free: type casts, chunk concatenation, and legacy pandas string handling are real, known places where a copy still happens.

If this is useful background, the Parquet article on this profile covers the disk-format half of this exact story — the two are designed to be read as a pair.

From Detection to Production: A PII-Safe Pipeline in Python and DuckDB

Nariman Baubekov — Mon, 07 Sep 2026 07:21:54 +0000

Part 1 gave you a map: every PII-bearing column, tier-tagged. Part 2 armed you: HMAC for direct identifiers, tokens for what must be reversible, masking for display, generalization for quasi-identifiers. Now the part where articles usually wave their hands and your actual weekend disappears: putting it together.

Everything in this post runs from the companion repo with uv sync — no warehouse, no Docker, no cloud account. DuckDB stands in for your storage layers so the mechanics survive the translation; the article flags every spot where production wants something heavier.

The architecture: three zones and a key

Zone	File	What lives there	Access story
Raw	`raw.duckdb`	everything, unmasked, as landed	restricted by access, short-lived by policy
Curated	`curated.duckdb`	pseudonymized join keys, masked phones, generalized quasi-identifiers	the analytics surface
Vault	`vault.duckdb`	token → identity, for authorized reversal	the crown jewels; audit everything

And floating beside them: PII_HMAC_KEY, from your secrets manager to the pipeline's environment. It never touches disk in the repo.

The raw zone argument

First, the uncomfortable design decision. Can't you just protect PII before it lands anywhere — mask at ingestion, skip the raw zone entirely? In a perfect world, yes. In this one: sources drift, transforms have bugs, and the day you need to reprocess last month you'll want the original bytes. Most pipelines keep a raw zone.

The point this series argues for is smaller and sharper: once raw PII lands in cheap, copyable storage, you've inherited a governance problem — so treat the raw zone like the liability it is. Concretely, in the repo: it's a separate database file, separate from anything an analyst or BI tool touches, and it's the one zone where you'd set a retention timer (the demo skips implementing it; your warehouse's lifecycle policies shouldn't). Protection then happens at the first boundary out of raw — not "eventually, in some dashboard."

The transform: policy-driven, not vibes-driven

The pipeline reads Part 1's classifications.yaml and applies Part 2's decision per tier — direct → pseudonymize, quasi → generalize, free_text → keep-restricted, non_pii → pass-through. The heart of it is ~15 lines:

# src/pii/pipeline.py (trimmed)
curated_rows = [
    (
        row["customer_id"],
        pseudonymize(row["email"], key),        # direct -> HMAC join key
        pseudonymize(row["contact_ref"], key),  # direct -> same key for the same person
        mask_phone(row["phone"]),               # direct -> display mask
        row["zip"][:3],                         # quasi   -> generalize
        int(row["dob"][:4]),                    # quasi   -> generalize
        row["support_notes"],                   # free_text -> kept, restricted at serving
    )
    for row in rows
]

$ uv run python -m pii.pipeline
warning: PII_HMAC_KEY not set — using the dev-only key. Fine for the
warning: demo, a firing offence in production.
raw      : 300 rows landed (restricted zone, add retention)
curated  : 300 rows, 291 with user_pseudo_id = contact_token
vault    : 300 reversible tokens (guard this file)
joins survive pseudonymization: email and contact_ref -> same token

That 291 is the Part 2 payoff, measured: 97% of contact_ref values are the same email as the email column (Part 1 planted that lie), and HMAC's determinism means both columns collapse onto one join key per person. Names never make it into curated at all — they live only in the vault.

The serving layer: roles, not tables

DuckDB has no native masking policies — it's an embedded database, roles aren't its job. So the repo simulates the pattern with views and says so honestly:

-- src/pii/serve.py: what analysts get
CREATE OR REPLACE VIEW v_customers_analyst AS
SELECT
    customer_id,
    user_pseudo_id,
    phone_masked,
    zip3,
    birth_year,
    NULL AS support_notes      -- redact_on_read, from classifications.yaml
FROM curated_customers;

$ uv run python -m pii.serve
analyst sees:
  ('0822e8f3-...', '0138f3a7b71bff72', '***-***-9935', '044', 1942, None)

support sees:
  ('47378190-...', '6814444dac9bd257', '***-***-7873',
   "Hi, this is James Santos — order #3615 never arrived. I'm at williamjohnson@...")

In production this exact shape becomes a warehouse-native policy attached to the column, evaluated per query:

-- the Snowflake version of the same idea
CREATE MASKING POLICY mask_notes AS (val VARCHAR) RETURNS VARCHAR ->
  CASE WHEN CURRENT_ROLE() = 'SUPPORT' THEN val ELSE NULL END;

ALTER TABLE curated_customers
  MODIFY COLUMN support_notes SET MASKING POLICY mask_notes;

The lesson isn't "use views" — it's that redaction lives at the serving boundary, declared once, instead of being remembered separately by every downstream consumer.

Erasure: "delete the user" is three deletes

A GDPR erasure request arrives for one customer. Where do they live? Everywhere (Part 2 explained why pseudonyms don't exempt you):

$ uv run python -m pii.erasure johnsonjoshua@example.com
vault    deleted 1 row(s)
curated  deleted 1 row(s)
raw      deleted 1 row(s)

backups: not covered here — that's what crypto-shredding is for.

The trick worth stealing: you don't need the vault to find the person in curated — hmac(key, email) recomputes the pseudonym deterministically, so erasure fans out to every zone from the request itself. And the honest footnote the script prints: DELETE doesn't touch backups and snapshots. That's why erasure-ready pipelines either keep backup windows short or encrypt per-customer and destroy keys on request (crypto-shredding).

The guardrail: fail the PR, not the incident

Everything above is one refactor away from silently leaking PII again. So the repo's five tests do the remembering for you:

# tests/test_no_leaked_pii.py (trimmed)
def test_curated_zone_has_no_direct_pii(tmp_path):
    # run the pipeline into temp DBs, then:
    for column in columns:                    # every curated column...
        for pattern in (EMAIL_RE, PHONE_RE):  # ...must match zero PII patterns
            hits = con.execute(
                f"SELECT count(*) FROM curated_customers "
                f"WHERE regexp_matches(CAST({column} AS VARCHAR), '{pattern}')"
            ).fetchone()[0]
            assert hits == 0, f"{column} matches PII pattern {pattern} — leak!"

Worth a pause on that f-string, given the subject matter: column and pattern are interpolated straight into the SQL. That's safe here specifically because both come from a closed, hardcoded list defined in the test file, not from anything a user or upstream system controls — there's no injection surface. But copy this pattern into anything where column could originate from a config file, an API response, or a contract someone else edits, and you've built the exact kind of hole this series spends three articles closing. Parameterize or validate against an allow-list the moment that input stops being something you typed yourself.

$ uv run pytest -v
tests/test_no_leaked_pii.py::test_curated_zone_has_no_direct_pii PASSED [ 20%]
tests/test_no_leaked_pii.py::test_analyst_view_exposes_no_free_text PASSED [ 40%]
tests/test_no_leaked_pii.py::test_pseudonymization_preserves_joins PASSED [ 60%]
tests/test_no_leaked_pii.py::test_every_column_is_classified PASSED      [ 80%]
tests/test_no_leaked_pii.py::test_validate_contract_passes PASSED        [100%]

5 passed in 5.40s

Read those test names again — each one is a failure mode from Parts 1 and 3 that now cannot happen silently: PII leaking into curated, free text reaching analysts, join keys breaking, a new column appearing without classification, a contract drifting from reality. This is the whole series in one command: detection became a pipeline stage, not an audit.

(If the unit/integration split and the mocking pattern above look familiar, they're the same ones from my pytest crash course for data pipelines — this test suite is that article's advice applied to a real problem instead of a toy one.)

What this repo deliberately doesn't do

Honesty section, no charge:

No orchestration — nothing schedules detection scans or key rotation. Put Presidio on a nightly sample, not in CI (too slow) and not never (that's how drift wins).
No catalog or lineage — tier tags belong in your data catalog too, so governance sees what engineering sees.
No access auditing — the vault should log every reversal; DuckDB won't do that for you.
DuckDB views ≠ policies — production wants Snowflake masking policies or BigQuery column-level security, where the database enforces what the view merely promises.
Single key, single rotation story, unimplemented — versioned tokens (v2:...) are designed for, not built.

The series, in one diagram

Part 1 argued detection is a pipeline stage, not an audit. Part 2 argued technique choice follows from what the data must still do. Part 3's argument is the last one: protection is architecture — zones, boundaries, and a guardrail that fails the build — because any PII decision that lives only in someone's memory is a future incident.

The full repo is here — clone it, break it, add a column of undeclared emails to the CSV and watch the contract test fail. That's the fastest way to make this series yours.

Mask, Hash, Tokenize, or Encrypt? Choosing the Right PII Protection for Your Pipeline

Nariman Baubekov — Mon, 07 Sep 2026 07:20:46 +0000

In Part 1 you built a map: every column that holds PII, tagged with a tier. Feels like the hard part is over. Then someone in #data-platform asks the reasonable question:

"OK so... do we hash the emails? Or mask them? I feel like hashing. Let's hash them."

"Hash it" is the two-word answer that's almost always wrong in an interesting way. Not because hashing is bad — because the question wasn't "which algorithm," it was "what do we still need this data to do?" This article is about answering that question deliberately. All code is in the companion repo — every claim below has a runnable file behind it.

One map before the weapons

Every technique fits somewhere on one axis that matters more than people admit: can the original value be recovered?

The rule of thumb hiding in this diagram: if a key or vault can recover the person, you are still holding personal data. Encryption and tokenization buy you access control. Masking, hashing, and generalization buy you distance. Sometimes you need one, sometimes the other, sometimes both on the same column in different zones. Let's earn that sentence.

Masking: for when nobody needs the original

# src/pii/masking.py
def mask_email(value: str) -> str:
    """jane.doe@example.com -> j***@example.com (irreversible, display-safe)."""
    user, _, domain = value.partition("@")
    return f"{user[:1]}***@{domain}" if user and domain else "***"

Masking is display logic. Partial masks keep just enough for humans to recognize ("yeah that's my email"), and the original value is gone — there is no key. Static masking rewrites the data before it lands somewhere broad (analytics, test databases); dynamic masking evaluates per-user at query time (Snowflake masking policies, BigQuery column-level security — the warehouse layer, not your Python).

Great for: support agents verifying identity by last-4 digits, dashboards, test data.
Wrong for: anything downstream that needs to join, count distinct, or reach back out to the person.

Hashing: the trap everyone walks into

Hashing feels like privacy alchemy: deterministic, irreversible, join-friendly. And for high-entropy secrets (passwords) it genuinely is. PII is not high-entropy. A US phone number has 10 billion possibilities — precomputable. An SSN: a billion. A gender-plus-ZIP combo: forget it. When the input space is small, an attacker doesn't reverse your hash; they hash the whole dictionary and look it up.

This is not hypothetical — the repo demonstrates it (uv run python -m pii.hashing_demo):

leaked: 5 phone numbers, each appearing twice (as if in two tables)
attacker: precomputed dictionary of 20,000 candidate phones

1. sha256(phone)
   cracked by dictionary : 5/5
   same phone, same digest (joins work): YES — for the attacker too

2. sha256(salt + phone)
   cracked by dictionary : 0/10
   same phone, same digest (joins work): NO — broken for everyone

3. hmac(key, phone)
   cracked by dictionary : 0/5 (key unknown)
   same phone, same digest (joins work): YES — attacker needs the key

Three lines, one entire decision framework.

The join-key dilemma

Here's the tension that actually decides your architecture:

Analytics needs deterministic identifiers — same email in the events table and the orders table must produce the same key, or joins, distinct counts, and funnels all die. Security needs outputs nobody can precompute. Plain hashing gives you the first and forfeits the second. The textbook fix — a random salt per row — gives you the second and forfeits the first: the same phone now hashes differently everywhere it appears, and COUNT(DISTINCT user) quietly becomes nonsense.

HMAC: the workhorse

# src/pii/masking.py
def pseudonymize(value: str, key: bytes) -> str:
    """HMAC-SHA256, truncated. Deterministic (joins survive), keyed."""
    return hmac.new(key, value.encode(), hashlib.sha256).hexdigest()[:16]

HMAC is hashing with a secret key baked into the function. Same input + same key → same digest, so your joins live. But an attacker with a dictionary and no key gets nothing — their precomputed table is useless against a keyed construction. It's not magic, it's a trade-off with one new obligation: key management. The key lives in a secrets manager, reaches the pipeline as PII_HMAC_KEY, and — the part teams forget — needs a rotation story. Version your tokens (v2:a1b2c3...) so rotating the key doesn't orphan every join key you've ever minted. The trade: anyone holding the key can map every pseudonym back — so the key's blast radius is your privacy posture.

For most analytics pipelines this is the default answer for direct identifiers: deterministic, irreversible without the key, and good enough to build on.

Tokenization: when the business needs to reach back

Sometimes "irreversible" is a deal-breaker — support genuinely needs to email the customer back. Tokenization swaps the PII for a token and stores the mapping in a vault:

token          email
──────────────────────────────────────
0138f3a7b71b…  jane.doe@example.com     ← vault.duckdb, max lock-down

The pipeline and analytics only ever see the token; reversal is an explicit, audited vault lookup behind an authorization boundary. Tokens can be deterministic (same input → same token → joins survive, same trade as HMAC) or random (unlinkable, but unjoinable). And this is the shape PCI-DSS expects for card data, because "we never store the PAN, only the token" is a sentence auditors enjoy.

The honest cost: the vault becomes your crown-jewel database. Compromise it and every token in every table becomes plaintext. Guard, audit, and back it up like it's the one file with everyone's names in it — because it is.

Encryption: access control, not anonymity

Encryption is non-negotiable and overrated, in the same way locks are: essential, but nobody claims a locked house contains no furniture. If a key can decrypt the column — and at query time something usually can — then GDPR still sees personal data, your breach blast radius still includes those columns, and your erasure obligations still apply. Encrypt everything at rest and in transit, use column-level encryption for the spicy fields, and file it under securing PII, not reducing it. The techniques above reduce; encryption just locks the door.

The legal line: pseudonymized vs. anonymous

The distinction that ends arguments in incident reviews:

Under GDPR, a hashed email is pseudonymous, not anonymous — it's still personal data, because possession of the key (or a matching hash elsewhere) re-links it. Retention limits and right-to-erasure apply in full. The only road to "anonymous" runs through the quasi-identifiers from Part 1: generalize zip → 941, dob → 1974, suppress rare values, until no combination singles anyone out (k-anonymity). That's why Part 1's tier tags mattered: direct identifiers get HMAC or tokens; quasi-identifiers get generalization. Different tiers, different weapons.

One caveat worth knowing before you treat k-anonymity as a finish line: it protects against singling someone out, not against learning something about them. If every one of the k records sharing a generalized ZIP+birth-year bucket happens to have the same sensitive attribute — the same diagnosis, say — an attacker doesn't need to identify the individual to learn the fact, because the whole bucket shares it (the textbook "homogeneity attack"). k-anonymity is the regulatory bar GDPR actually recognizes, and it's the right target for this series, but if a quasi-identifier bucket is going to sit next to a Tier 3 sensitive attribute, it's worth checking the bucket isn't accidentally uniform on that attribute — that's what extensions like l-diversity exist to catch.

The decision table

Screenshot this one:

Requirement driving the choice	Masking	HMAC / hashing	Tokenization	Encryption	Generalization
Analytics must join / count distinct on it	no	yes	yes (deterministic tokens)	no	partially
Business must recover the original	no	no	yes (vault)	yes (key)	no
Survives without key/vault management	yes	no	no	no	yes
Format must stay valid (sort keys, regex)	partial	no	yes (FPE tokens)	no	no
Removes GDPR obligations on the dataset	no	no	no	no	yes (k-anonymity)
Typical home	display & serving layer	direct identifiers in analytics	payment & operational data	storage & transit	quasi-identifiers

Most real pipelines are a composition: encrypt the raw zone, HMAC the direct identifiers into curated, tokenize what support must reach, mask at the serving layer, generalize the quasi-identifiers. Part 3 builds exactly that.

Takeaways

"Hash it" is a question about what the data still needs to do, not an algorithm choice.
Low-entropy PII (phones, SSNs, ZIPs) falls to dictionary attacks unless the hash is keyed — use HMAC.
Salts protect passwords, not join keys. Per-row salts and analytics joins are mutually exclusive.
Reversibility is access control, not anonymity. The key and the vault are personal data by proxy.
GDPR calls everything except k-anonymized data "personal data." Plan retention and erasure accordingly.

Next in the series: we take the tier tags from Part 1 and the technique choices from Part 2, and build the actual pipeline — raw zone, curated zone, vault, role-based serving views, GDPR erasure across all of them, and the CI test that fails your build when PII leaks. All runnable, all local.

You Can't Protect What You Can't Find: Detecting and Classifying PII in Data Pipelines

Nariman Baubekov — Mon, 07 Sep 2026 07:18:48 +0000

It's 4:47 PM on a Friday. A stakeholder pings you: "Hey, can we get the support tickets table into the warehouse? The CS team wants to chart ticket volume by region."

Sounds harmless. One table, one SELECT *, done before standup Monday.

You glance at the schema. Eighteen columns of pure innocence — ticket_id, status, created_at — and then column nineteen: support_notes. Free text. Years of customers writing things like:

Hi, this is Jane Doe, my order #4311 never arrived. I'm at jane.doe@example.com, or call me on 555-010-8899. Billing zip is 94110. Also my card ending in 4242 keeps failing??

Congratulations: your "harmless analytics table" is now one of the most sensitive assets in the company.

The part every PII article skips

Google "how to handle PII as a data engineer" and you'll get the same tidy list everywhere: masking, tokenization, hashing, encryption, anonymization. All correct. All useful. And all of them answer a question you can only ask after you know where your PII lives. MASK THE EMAIL COLUMN is a one-liner. Knowing that contact_ref is full of emails, that the metadata JSON blob hides shipping addresses, and that support_notes contains entire identities — that's the actual job.

PII is a gas. It expands to fill whatever container you give it, and it escapes through the same few cracks every time:

Cryptic names — x_field_7, courtesy of an upstream team you've never met
Lying names — a column called contact_ref that is, in fact, emails
Free text — customers volunteering their whole identity into a textbox
Schema drift — a vendor feed that grew three columns overnight, silently. Nobody opens a ticket titled "FYI: new column of passport numbers."

So this series spends its first article on the unfashionable part: finding and classifying PII. Part 2 will pick the protection technique (mask vs hash vs tokenize vs encrypt — with the trade-offs that actually decide it). Part 3 puts the whole thing into a runnable pipeline.

Parts 2 and 3 build on the two stages this post covers — the ones everyone else skips.

The toy dataset (no real humans involved)

Everything below runs against a deliberately messy customers.csv that ships with the companion repo. It's generated with Faker, so it contains zero real people — the only thing harmed in writing this article was the CSV's dignity:

Column	Looks innocent?	Actually contains
`customer_id`	yes	a UUID. Genuinely boring
`email`	no	emails, honestly labeled
`phone`	no	phone numbers
`first_name`, `last_name`	no	names
`zip`, `dob`	sort of	quasi-identifiers (this gets uncomfortable later)
`contact_ref`	yes	emails. The column name is a lie
`support_notes`	yes	free text with full identities

Setup is two commands, no warehouse, no Docker, no cloud account:

git clone --depth 1 --sparse --filter=blob:none https://github.com/nbaubek/devto-articles-repo
cd devto-articles-repo
git sparse-checkout set pii-pipeline
cd pii-pipeline
uv sync

Now let's climb.

Rung 1 — trust column names (a little)

The cheapest detection you'll ever write: regex over schema names.

# src/pii/heuristics.py
import re

PATTERNS = {
    "email": r"e[-_]?mail",
    "phone": r"\bphone\b|\bmobile\b|\btel\b",
    "ssn":   r"\bssn\b|social",
    "dob":   r"\bdob\b|birth",
    "name":  r"first[-_ ]?name|last[-_ ]?name|full[-_ ]?name",
}

def scan_column(name: str) -> list[str]:
    return [
        label
        for label, pattern in PATTERNS.items()
        if re.search(pattern, name, flags=re.IGNORECASE)
    ]

$ uv run python -m pii.heuristics
customer_id    → []
email          → ['email']
phone          → ['phone']
first_name     → ['name']
last_name      → ['name']
dob            → ['dob']
contact_ref    → []        # emails live here. Rung 1 is blind.
support_notes  → []        # ...and here.

It catches every honestly-labeled column in about twenty lines, and it's fast enough to run in CI on every schema change. Column-name heuristics are like checking luggage by reading the name tags: quick, cheap, and completely defeated by anyone who labels their suitcase "definitely not bombs."

Rung 2 — profile the actual content

Fine. Don't ask the schema — ask the data. For each column, compute the fraction of non-null values that look like PII:

-- src/pii/profile_scan.sql (single-column version)
SELECT
  round(avg(
    CASE WHEN regexp_matches(contact_ref, '^[\w.+-]+@[\w-]+\.[\w.]+$')
         THEN 1.0 ELSE 0.0 END
  ), 2) AS email_ratio
FROM 'data/customers.csv'
WHERE contact_ref IS NOT NULL;

The repo loops this over every column and pattern (email, phone, SSN, credit card). The verdicts from our CSV:

Column	email_ratio	Verdict
`email`	1.00	flagged — well yes
`contact_ref`	0.97	flagged — rung 2 catch!
`support_notes`	0.08	not flagged — rung 2 miss

contact_ref finally gets caught, because content doesn't lie. But support_notes walks free: only some rows contain emails, they're buried mid-sentence, and a threshold-based regex over prose is brittle by nature.

Two honest caveats. First, profiling means actually reading the data — run it as a controlled job with proper access, not as an ad-hoc query in the BI tool (that would be detecting PII by leaking PII, a bold strategy). Second, regexes only recognize formatted identifiers. "Jane Doe of 94110" contains no @ and no dashes. For that, you need something that reads.

Rung 3 — read the free text (Presidio)

Presidio is Microsoft's open-source PII detector: named-entity recognition plus pattern recognizers, scoring each hit:

# src/pii/presidio_scan.py (trimmed)
from presidio_analyzer import AnalyzerEngine
from presidio_analyzer.nlp_engine import NlpEngineProvider

def get_analyzer() -> AnalyzerEngine:
    # Presidio's default wants the ~800 MB spacy model; we wire the small
    # one so `uv sync` is all the setup a reader needs.
    provider = NlpEngineProvider(nlp_configuration={
        "nlp_engine_name": "spacy",
        "models": [{"lang_code": "en", "model_name": "en_core_web_sm"}],
    })
    return AnalyzerEngine(nlp_engine=provider.create_engine())

analyzer = get_analyzer()

def scan_text(text: str) -> list[tuple[str, str, float]]:
    return [
        (hit.entity_type, text[hit.start:hit.end], round(hit.score, 2))
        for hit in analyzer.analyze(text=text, language="en")
    ]

Run it over a real row of our support_notes and the buried identity surfaces:

$ uv run python -m pii.presidio_scan
Hi, this is James Santos — order #3615 never arrived. I'm at williamjohnson@...
  EMAIL_ADDRESS  williamjohnson@example.com   1.0
  PERSON         James Santos                 0.85
  PHONE_NUMBER   555-658-7873                 0.4
  UK_NHS         555-658-7873                 1.0    <- same digits, confidently wrong

The whole identity in one paragraph, found — and the output above is real, which is exactly why you should read it twice. The email is found at 1.0. The person at 0.85. But the phone number limps in at 0.4, and the same digits are simultaneously reported as a UK NHS number at 1.0. These scores are a model's opinion, not a fact: recognizers want tuning for your domain, and this is by far the most expensive rung. In practice you don't Presidio-scan every row of every table nightly. You scan new tables, schema changes, and a sample of free-text columns, on a schedule.

Each rung catches what escaped the previous one — and all of them react after the fact.

Finding it ≠ knowing how dangerous it is

So the ladder tells you where PII lives. Classification decides what it means — and this is the step that actually determines your protection strategy later. Four tiers:

Tier 0 — not PII. customer_id, timestamps. Breathe.
Tier 1 — direct identifiers. Email, SSN, phone, name. Each one identifies a person on its own. These get the heavy treatment in Part 2.
Tier 2 — quasi-identifiers. ZIP, birth date, gender. Each is boring alone; together they're a fingerprint. Latanya Sweeney's classic result: ZIP + birth date + gender uniquely identify about 87% of Americans.
Tier 3 — sensitive attributes. Health conditions, salary, credentials. Not identifiers, but the payload — the thing you're afraid of revealing about whoever you re-identify. Our toy customers.csv doesn't happen to carry one (support tickets rarely do), but the moment a plan_tier column turns into annual_income or a notes field turns into diagnosis, the classification step is identical — it's the protection strategy in Part 2 that changes, because a salary figure has no format to mask and nothing to tokenize; it mostly wants access control and aggregation limits, not a transformation.

Tier 2 deserves a diagram, because this is the one that bites teams who think they're done:

"We stripped all names and emails" is not the same sentence as "nobody can be identified."

Two uncomfortable truths fall out of this, and both shape the rest of the series: stripping direct identifiers from a table does not make it anonymous, and — preview for Part 2 — even a hashed email is still personal data under GDPR. The output of all our detection work is just this file:

# src/pii/classifications.yaml
version: 1
columns:
  customer_id:   { tier: non_pii }
  email:         { tier: direct }
  phone:         { tier: direct }
  first_name:    { tier: direct }
  last_name:     { tier: direct }
  zip:           { tier: quasi }            # dangerous in combination
  dob:           { tier: quasi }
  contact_ref:   { tier: direct }           # caught by profiling, not by its name
  support_notes: { tier: free_text, action: redact_on_read }

Detection produced a map; this file turns the map into policy. Part 3's pipeline will consume it directly.

Stop detecting. Start declaring.

One last twist — and the fourth escape route from the top of this article, the one the ladder itself never catches. Rungs 1 through 3 handle cryptic names, lying names, and free text; schema drift needs a different kind of defense, because a scanner only tells you about columns that already exist. Everything on the ladder is also reactive — PII gets found after it arrives, maybe months after. Data contracts flip the burden: the producer declares PII tiers as part of the schema, and your pipeline enforces the declaration.

# contracts/customers.v1.yaml — owned by the producing team
dataset: raw.customers
version: 1
owner: cs-analytics
columns:
  email:         { pii_tier: direct }
  phone:         { pii_tier: direct }
  first_name:    { pii_tier: direct }
  last_name:     { pii_tier: direct }
  zip:           { pii_tier: quasi }
  dob:           { pii_tier: quasi }
  contact_ref:   { pii_tier: direct }
  support_notes: { pii_tier: free_text, redact: true }

A small validator (validate_contract.py in the repo) reconciles declaration against reality and has exactly three outcomes:

Declared and detected — fine, proceed.
Declared but not detected — for direct identifiers, that's a stale (or optimistic) contract; warn loudly. For quasi and free_text tiers, the declaration is the control — the scanner can't check it for you.
Detected but not declared — someone added a PII column without declaring it. Fail the build.

That flips the conversation from "Legal found customer emails in the BI tool" (a genuinely terrible Friday) to "Your PR adds an undeclared column that's 97% emails" (a merely awkward Friday, and it happens at review time, not breach time).

Takeaways

Protection is a solved problem once you know where PII lives. Detection is where pipelines actually fail.
Climb the ladder in order — names, then content, then language — because each rung's cost grows by an order of magnitude.
Classify into tiers, because "direct identifier" and "quasi-identifier" demand completely different handling. Stripping names is not anonymization.
Contracts turn detection from an audit into an enforcement point. Shift it left.

The full code for this post — messy CSV included — is in the companion repo.

Next in the series: you have a tier-tagged map of your PII. Now, which weapon? Masking, tokenization, hashing, or encryption — the join-key dilemma (why analytics-friendly hashes are exactly the brute-forceable ones), why your salt doesn't save you, and why a hashed email is still personal data under GDPR.

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.

DEV Community: Nariman Baubekov

PyArrow, Explained: How Zero-Copy Actually Works, and Why It's Everywhere

What Arrow actually is

The actual memory layout — buffers, not objects

Why it's called zero-copy — the actual mechanism

Proving it, not just asserting it

Where this shows up in Spark and Databricks

The rest of the ecosystem

When to reach for the pyarrow API directly

Where the "zero-copy" story has real edges

Takeaways

From Detection to Production: A PII-Safe Pipeline in Python and DuckDB

The architecture: three zones and a key

The raw zone argument

The transform: policy-driven, not vibes-driven

The serving layer: roles, not tables

Erasure: "delete the user" is three deletes

The guardrail: fail the PR, not the incident

What this repo deliberately doesn't do

The series, in one diagram

Mask, Hash, Tokenize, or Encrypt? Choosing the Right PII Protection for Your Pipeline

One map before the weapons

Masking: for when nobody needs the original

Hashing: the trap everyone walks into

The join-key dilemma

HMAC: the workhorse

Tokenization: when the business needs to reach back

Encryption: access control, not anonymity

The legal line: pseudonymized vs. anonymous

The decision table

Takeaways

You Can't Protect What You Can't Find: Detecting and Classifying PII in Data Pipelines

The part every PII article skips

The toy dataset (no real humans involved)

Rung 1 — trust column names (a little)

Rung 2 — profile the actual content

Rung 3 — read the free text (Presidio)

Finding it ≠ knowing how dangerous it is

Stop detecting. Start declaring.

Takeaways

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

When to reach for the `pyarrow` API directly

`fact_order_lifecycle`: the accumulating snapshot

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