Case Study 1: The Dimension That Grew Six Times in Five Months

"Every row in that table is true. Not one of them means anything."

Executive Summary

Kestrel's dim_customer grew from 1,904,221 rows to 11,355,581 in five months — a 5.96× increase against a customer base that grew by 3%.

The cause was one word. The dbt snapshot was configured check_cols: all, and the source customers table carries a last_login_at column. Every login created a new dimension version.

Nothing in the dimension was false. Every row recorded a genuine difference between two states of the source. And the dimension could no longer answer the question it exists to answer — "what changed, and when?" — because 99.7% of its versions differed only in a timestamp that means nothing to any analysis Kestrel performs.

The failure surfaced as a 3.4× revenue overstatement in a segment report, five months in, when a point-in-time join that had been safe at one version per customer began fanning out at six versions per week.

Skills applied: SCD2 mechanics (§20.9); check_cols and the check strategy (§20.10); the point-in-time join and its fan-out (§20.9); deciding SCD type per column rather than per dimension (§20.8).

Background

The dimension. dim_customer, Type 2, built by a dbt snapshot against the application's customers table. 1,904,221 current customers at the start of the period.

Its purpose, as recorded in the model's description: "Point-in-time customer attributes. A fact joined on ordered_at BETWEEN valid_from AND valid_to gets the customer as they were when the order was placed." That is the whole reason for the complexity of Type 2, and it matters for the same reason Chapter 6 §6.7 gives: an order placed from Ohio was placed from Ohio, permanently, and the source system will only ever tell you where the customer lives now.

The configuration, written during the project's first week:

snapshots:
  - name: scd_customers
    relation: source('kestrel_app', 'customers')
    config:
      unique_key: customer_id
      strategy: check
      check_cols: all        # ←

Why all and not a list. The person who wrote it considered enumerating the columns and chose all deliberately, for a defensible reason: enumerating means a new column in the source is silently not tracked. They wanted the dimension to capture everything, so that a future analytical question about a column nobody had thought of would have history available.

That reasoning is not stupid. It is the same instinct that produces "log everything" and "keep all the data," and it is wrong for the same reason.

The source table, at the time:

customers
  customer_id        region        segment       tier
  email              is_active     created_at    updated_at
  last_login_at   ←  changes every time the customer visits
  session_count   ←  changes every time the customer visits
  last_seen_ip    ←  changes when they change network

Three of eleven columns are heartbeats. They describe activity, not attributes, and no report at Kestrel groups by any of them.

The Problem

Five months in, the finance team's monthly revenue-by-segment report did not reconcile:

Revenue by segment, April 2026
──────────────────────────────────────────
  retail          $ 31,884,207.15
  wholesale       $ 12,712,441.83
  enterprise      $  6,258,240.52
                  ───────────────
  total           $ 50,854,889.50

  Actual monthly revenue: $14,957,467.50     ← 197,250 orders x $75.83

$50.9M against a true $15.0M. 3.4×.

The report's join was point-in-time, which is correct and is the reason the dimension exists:

  FROM gold.fct_order_item f
  JOIN gold.dim_customer   d ON d.customer_id = f.customer_id
                            AND f.ordered_at >= d.valid_from
                            AND f.ordered_at <  d.valid_to

⚠️ Failure Mode — a correct join that only fans out at high version density

That join is right. It is the join this book teaches, and it returns exactly one row per fact — provided the dimension's validity ranges do not overlap and are fine-grained relative to the fact's timestamp precision.

The report joined on f.ordered_at, a timestamp. The dimension's valid_from was a date.

With one version per customer per year, ordered_at >= '2026-03-10' AND ordered_at < '2026-09-01' matches exactly one row and nobody notices the precision mismatch. With six versions per customer per week — all with valid_from truncated to the same date — an order placed that day matches every version created that day.

Three logins on the 12th means three versions dated the 12th, means three rows for one order.

The join did not become wrong. The data became dense enough for a latent wrongness to show.

Which is the general shape worth carrying: a precision mismatch between a fact's timestamp and a dimension's validity grain is harmless at low version density and catastrophic at high density, and the transition is invisible. The overlap assertion from §20.9 catches it — versions on the same date with the same valid_from are, formally, overlapping — and Kestrel did not have it.

The Analysis

Step 1: count the dimension.

SELECT COUNT(*) AS versions, COUNT(DISTINCT customer_id) AS customers
  FROM gold.dim_customer;

versions     customers
11,355,581   1,961,348

5.79 versions per customer, in a dimension whose attributes change perhaps twice in a customer's lifetime.

Step 2: find out what is actually different between consecutive versions.

WITH pairs AS (
    SELECT customer_id, region, segment, tier, is_active, last_login_at,
           LAG(region)        OVER w AS p_region,
           LAG(segment)       OVER w AS p_segment,
           LAG(tier)          OVER w AS p_tier,
           LAG(is_active)     OVER w AS p_active,
           LAG(last_login_at) OVER w AS p_login
      FROM gold.dim_customer
    WINDOW w AS (PARTITION BY customer_id ORDER BY valid_from))
SELECT
    COUNT(*)                                                    AS n,
    SUM(CASE WHEN region  IS DISTINCT FROM p_region  THEN 1 END) AS region_chg,
    SUM(CASE WHEN segment IS DISTINCT FROM p_segment THEN 1 END) AS segment_chg,
    SUM(CASE WHEN tier    IS DISTINCT FROM p_tier    THEN 1 END) AS tier_chg,
    SUM(CASE WHEN is_active IS DISTINCT FROM p_active THEN 1 END) AS active_chg,
    SUM(CASE WHEN last_login_at IS DISTINCT FROM p_login THEN 1 END) AS login_chg
  FROM pairs WHERE p_login IS NOT NULL;

n            region_chg  segment_chg  tier_chg  active_chg  login_chg
9,394,233         8,117       21,904    11,286      19,730  9,394,233

Every single one of the 9,394,233 version transitions was a login. Fewer than 61,000 of them — 0.65% — involved any change to an attribute anyone analyses. And those 61,000 are not extra versions; they are a subset of the 9.4 million, because a customer who changed segment on a day they also logged in produced one row, not two.

$$\text{useful versions} \approx 61{,}037 \quad\text{out of}\quad 9{,}394{,}233 \quad\Rightarrow\quad \mathbf{0.65\%}$$

Step 3: confirm the mechanism, and confirm it is not something else. Roughly 62,000 customers log in on an average day; the dimension gained about 62,000 rows on an average day. The two series track each other to within 2% across 152 days. The dimension's row count is a login counter.

🔎 Read the Plan — the diagnostic is "what differs between consecutive versions?"

Run that query against every Type 2 dimension you own, today. It takes one window function and it answers a question nobody asks: are the versions we are storing versions of anything?

The output has three shapes:

  • A roughly even spread across tracked columns — healthy. Versions record changes.
  • One column accounting for nearly everything — you are tracking a heartbeat. This case study.
  • Many transitions where nothing differs — a bug in the change detection itself, usually a floating-point or whitespace comparison, and rarer but worth knowing about.

The second shape is common and essentially never noticed, because a Type 2 dimension is supposed to grow, and "the dimension got bigger" reads as the feature working.

The Decision

The immediate fix is one line, and it is the easy part:

      check_cols: [region, segment, tier, is_active]

The hard part is what to do with 9.4 million existing rows, and the discussion is the reason this is a case study.

Option A: leave the history. It is true. It is expensive but not ruinous — 9.4 million rows is small storage — and deleting data is irreversible.

Option B: collapse it. Merge consecutive versions that differ only in untracked columns, extending the earlier version's valid_to to the later one's. This reconstructs the dimension that would have existed with the right configuration.

Option C: rebuild from the raw snapshots, if the underlying source history exists. It did not — which is the point §20.10 makes about snapshots being irreplaceable.

They chose B, and the reasoning was not primarily about storage:

📐 Design Decision — history you cannot interpret is worse than no history

The argument for keeping the 9.4 million rows is that they are true, and true data should not be destroyed.

The argument that won: a dimension is an interface, and this one now lies about its own semantics. Its contract — stated in its description, and relied on by every analyst — is "a new row means something changed." After five months of check_cols: all, a new row means somebody visited the website, and there is no way for a consumer to tell the two apart without running the diagnostic query above.

Every future query against this table would have to know about the five-month window, and knowledge that has to be carried by consumers is knowledge that will be lost. The next analyst will count versions and get a login count, and will not know to be suspicious.

The general principle: data whose meaning changed silently in a date range is not an asset. It is a trap with a timestamp on it. Either repair it, or — if you cannot — put the caveat somewhere it cannot be missed, which in practice means renaming the table.

What collapsing cost: the login history, which nobody wanted, and which is available in the clickstream anyway at better fidelity. That last clause is what made the decision easy, and it is worth checking before any collapse: is this information available elsewhere? Here it was, in silver.events, with the actual session rather than a timestamp.

The collapse ran as a one-off:

-- Merge runs of versions that differ only in untracked columns. This is
-- Chapter 18 section 18.8's gaps-and-islands, applied to a dimension:
-- flag the REAL changes, running-sum the flag, then take one row per island.
WITH flagged AS (
    SELECT *, CASE WHEN (region, segment, tier, is_active) IS DISTINCT FROM
                        (LAG(region) OVER w, LAG(segment) OVER w,
                         LAG(tier) OVER w, LAG(is_active) OVER w)
                   THEN 1 ELSE 0 END AS is_real_change
      FROM gold.dim_customer
    WINDOW w AS (PARTITION BY customer_id ORDER BY valid_from)),
islands AS (
    SELECT *, SUM(is_real_change) OVER (PARTITION BY customer_id
                                        ORDER BY valid_from
                                        ROWS UNBOUNDED PRECEDING) AS island
      FROM flagged)
SELECT customer_id, MIN(valid_from) AS valid_from, MAX(valid_to) AS valid_to,
       MIN_BY(region, valid_from) AS region, ...
  FROM islands GROUP BY customer_id, island;

Three more changes, all of them structural:

The three §20.9 invariants became tests, including the overlap assertion that would have caught the same-date versions in week one.

valid_from and valid_to became timestamps, not dates, removing the precision mismatch independently of the density.

A dimension-growth assertion: new versions per day must be under 500. Kestrel's genuine attribute change rate is about 400 a day; a broken check_cols produces 62,000. The threshold sits in a gap two orders of magnitude wide, which is what makes it a good assertion rather than an alerting nuisance.

What Happened

Before After collapse
dim_customer rows 11,355,581 2,022,385
Versions per customer 5.79 1.03
New versions/day ~62,000 ~400
Snapshot build time 41 min 6 min
Revenue by segment 3.4× overstated reconciles
SCD invariants tested 0 3

The build time is the finding nobody expected. At 41 minutes the snapshot was the longest step in the nightly DAG and had twice caused a near-miss on the 6am SLA, both of which had been investigated as scheduling problems. The cost of a modelling mistake showed up in the operations budget, and was being addressed there.

The direct compute cost was small and worth stating honestly: 35 extra minutes a night on a Snowflake Medium is $4.67, or $709 over the five months. The money was never the issue. The issue was five months of a dimension that could not answer its question, and a segment report that had been wrong for an unknown fraction of that time — unknown because nobody had reconciled it before April.

An audit of the other three Type 2 dimensions found one more: dim_product tracked inventory_count, which changes on every sale. It had 4.2 versions per product and had never fanned out, because product versions are dense but order timestamps are precise and the products dimension used timestamps rather than dates. The same bug, one precision decision away from the same catastrophe.

Lessons

  1. check_cols: all is the worst default available. It converts a dimension into a change-data capture log keyed on whichever source column changes most.

  2. The instinct behind it is reasonable and still wrong. "Capture everything so future questions have history" is the same instinct as "log everything," and it fails the same way: the signal is still there and can no longer be found.

  3. Enumerate the tracked columns. The list is a genuinely useful artifact — the explicit statement of what your organization considers a change worth remembering.

  4. Run the diagnostic on every Type 2 dimension you own: what differs between consecutive versions? One column accounting for nearly everything means you are tracking a heartbeat.

  5. A dimension is supposed to grow, so growth reads as the feature working. That is why this is never noticed.

  6. A precision mismatch between a fact's timestamp and a dimension's validity grain is harmless at low version density and catastrophic at high density. The transition is invisible.

  7. The overlap assertion catches it — same-date valid_from values are formally overlapping — and it costs one window function.

  8. Data whose meaning changed silently in a date range is a trap with a timestamp on it. Repair it, or rename the table so the caveat cannot be missed.

  9. Before collapsing history, check whether the information exists elsewhere. Here the login history lived in the clickstream at better fidelity, which made the decision easy.

  10. A modelling mistake can present as an operations problem. Forty-one minutes on the critical path was investigated as scheduling, twice.

  11. A good threshold sits in a gap two orders of magnitude wide. 400/day normal, 500 threshold, 62,000 broken.

Questions for Discussion

  1. The engineer chose all to avoid silently missing future columns, and that concern is real. What is the right way to address it? Where should the notice come from?

  2. Collapsing destroyed five months of real login history. Argue the other side — construct the organization for which Option A is correct.

  3. dim_product had the same configuration bug and no symptom, purely because of a timestamp precision choice. How would you find defects whose symptom depends on an unrelated decision?

  4. The 41-minute build was investigated twice as a scheduling problem. What would have redirected that investigation toward the model?

  5. The growth threshold is 500/day against 400 normal and 62,000 broken. Would you set it at 500, 1,000, or 5,000? What does your answer say about what you are optimizing?

  6. The dimension's description said "a new row means something changed" and, for five months, that was false. Should a model's description be testable? What would that even look like?

  7. This case study and Chapter 19's Case Study 2 are both about a correct pattern creating an obligation nobody took on. Is there a general way to make an obligation travel with a pattern?