Case Study 2: The Before Image That Was Only a Key

"We built the entire Type 2 dimension, tested it, shipped it, and it produced one row per customer. Which is what a Type 1 dimension produces."

Executive Summary

Kestrel migrated dim_customer from a nightly Type 1 rebuild to a CDC-fed Type 2 dimension, so that revenue-by-region would reflect where a customer lived at the time of the order rather than where they live now (Chapter 6 §6.5).

The pipeline was built, tested against a fixture, reviewed, and deployed. It ran for nine weeks producing exactly one row per customer — no history at all — while every test passed and no error was raised.

The cause was REPLICA IDENTITY, whose PostgreSQL default puts only the primary key in a change event's before image. The change-detection logic compared before to after to decide whether a tracked attribute had changed, and before contained only customer_id, so nothing ever looked like it had changed.

This case study is §14.4's ⚠️ callout as an incident, and it is here for a specific reason: it is the most common Debezium misconfiguration, its symptom is not an error, and the test that would have caught it is one assertion.

Skills applied: REPLICA IDENTITY (§14.4); the Debezium envelope (§14.4); SCD Type 2 (Chapter 6 §6.5); test fixtures that are not representative (Chapter 10, Case Study 1).

Background

The requirement, from Chapter 6 §6.8's fifth resolved definition: a customer belongs to their region at the time of the order. That needs a Type 2 dimension, which needs to know when a region changed, which needs the previous value.

The design. Consume kestrel.customers.cdc.v1, and on each update, compare the tracked attributes in before against those in after:

TRACKED = ("region", "segment", "marketing_opt_in")

def is_tracked_change(event: dict) -> bool:
    """Did any Type 2 attribute change? If so, close the current row and open
    a new one; otherwise update in place (Type 1)."""
    before, after = event["before"], event["after"]
    if before is None:                     # an insert
        return True
    return any(before.get(k) != after.get(k) for k in TRACKED)

That function is correct, given a complete before image. It is the standard shape and it would pass any review.

The test, which also passed:

def test_region_change_opens_new_row():
    event = {
        "op": "u",
        "before": {"customer_id": 8841, "region": "CO", "segment": "retail",
                   "marketing_opt_in": True},
        "after":  {"customer_id": 8841, "region": "OR", "segment": "retail",
                   "marketing_opt_in": True},
    }
    assert is_tracked_change(event) is True

The fixture was hand-written, with a complete before image, because that is what the Debezium documentation's examples show and it is what a person writing a fixture would naturally produce.

The Problem

Production events looked like this:

{
  "op": "u",
  "before": {"customer_id": 8841},
  "after":  {"customer_id": 8841, "region": "OR", "segment": "retail",
             "marketing_opt_in": true, "email_hash": "...", ...}
}

before is only the primary key, because customers had REPLICA IDENTITY DEFAULT, which is PostgreSQL's default and which writes only the key into the WAL for an update.

Now trace is_tracked_change:

before.get("region")            # None  — the key is not None; "region" is absent
after.get("region")             # "OR"
None != "OR"                    # True

Wait — that returns True. Every update looks like a tracked change, so the dimension should have produced too much history rather than none.

It did, initially. And a second piece of code, added during development to suppress an obvious flood of spurious rows, is where the failure actually landed:

# Added in week 2 of development: "we were getting a new dimension row on
# every single update, including ones that changed nothing we track."
def is_tracked_change(event: dict) -> bool:
    before, after = event["before"], event["after"]
    if before is None:
        return True
    # Only compare keys that are PRESENT in before -- otherwise a partial
    # before image makes everything look changed.
    comparable = [k for k in TRACKED if k in before]
    if not comparable:
        return False                      # ← nothing comparable: assume no change
    return any(before[k] != after[k] for k in comparable)

The return False is the bug, and the comment above it explains exactly why a reasonable engineer wrote it. They had observed the flood, correctly diagnosed it as a partial before image, and worked around the symptom instead of fixing the cause.

With REPLICA IDENTITY DEFAULT, comparable is always empty. The function always returns False. No customer ever gets a second row.

⚠️ Failure Mode — The workaround that encodes the misconfiguration

This is the shape worth studying, because it is more common than the naive version.

The engineer saw a real symptom (a flood of spurious dimension rows), diagnosed it correctly (the before image is partial), and then wrote code that tolerates the partial image rather than code that fixes it.

The workaround is defensible in isolation — "only compare fields that are present" is a reasonable defensive stance — and it converted a loud, obviously-wrong output into a silent, plausibly-wrong one. Chapter 2's fail-loudly principle, violated by someone trying to make things work.

The tell they missed: the fallback branch. if not comparable: return False is a decision about what to do when the data is not what you expect, and a fallback that returns a plausible answer is a fallback that will hide a misconfiguration forever.

The version that would have surfaced it:

python if not comparable: raise IncompleteBeforeImage( f"before image for customer {after['customer_id']} contains only " f"{sorted(before)} — none of the tracked attributes {TRACKED}. " f"Check REPLICA IDENTITY on the source table; the PostgreSQL default " f"puts only the primary key in the before image.")

Same three lines. It fails on the first event, names the cause, and names the fix. When you write a fallback, ask whether it produces a plausible answer — and if it does, raise instead.

The Analysis

Detection at nine weeks, from an analyst rather than a monitor.

They were computing revenue by region for a regional expansion analysis and noticed that a customer who had relocated in March showed all of their 2024 orders under their 2025 region. Which is exactly the thing the Type 2 dimension had been built to prevent.

Diagnosis took under an hour, once someone looked, and the sequence is the standard one:

-- 1. Does the dimension have any history at all?
SELECT COUNT(*) AS rows, COUNT(DISTINCT customer_id) AS customers
  FROM gold.dim_customer;
-- 1,904,221 | 1,904,221    ← one row each. There is no history.

-- 2. Is the source producing changes?
SELECT op, COUNT(*) FROM bronze.customers_cdc
 WHERE ingest_date >= CURRENT_DATE - 30 GROUP BY 1;
-- c:  12,441
-- u: 208,930    ← plenty of updates
-- d:      82

-- 3. What is IN those updates?
SELECT before, after FROM bronze.customers_cdc
 WHERE op = 'u' LIMIT 1;
-- before: {"customer_id": 8841}          ← there it is

🔎 Read the Plan — Assert the shape of an event, not just its presence

Every check the team had asked whether events were arriving. None asked what was in them.

sql -- The assertion that would have failed on day one. SELECT COUNT(*) AS updates_with_incomplete_before FROM bronze.customers_cdc WHERE op = 'u' AND ingest_date = CURRENT_DATE AND NOT (before ? 'region' AND before ? 'segment'); -- expect 0

The general pattern: for any structured event you consume, assert the presence of the fields you depend on — not the fields the schema permits, the fields your code reads.

This is Chapter 12's GROUP BY uniformity check in a different form, and it belongs next to it in the standard diagnostics:

sql -- what fields actually appear in before images? SELECT jsonb_object_keys(before) AS field, COUNT(*) FROM bronze.customers_cdc WHERE op = 'u' GROUP BY 1 ORDER BY 2 DESC;

Ten seconds, and it makes the misconfiguration impossible to miss.

The Decision

Four changes.

1. REPLICA IDENTITY FULL on the tables feeding Type 2 dimensions.

ALTER TABLE customers REPLICA IDENTITY FULL;
ALTER TABLE products  REPLICA IDENTITY FULL;

And it was measured, not assumed. WAL generation on customers rose 18%; on orders, when they later enabled it there, 31%. Both were accepted; order_items was deliberately left on DEFAULT because nothing needs its before-image.

2. The fallback raises. The return False became the exception in the ⚠️ callout above, with the message naming REPLICA IDENTITY explicitly. The message is the point — a person meeting this error at 03:00 gets the diagnosis and the fix in one line.

3. Fixtures are captured from production, not hand-written.

# platform/ingest/cdc/capture_fixtures.py
# Reads N real events per (table, op) from bronze and writes them to
# tests/fixtures/, redacting PII. Regenerated quarterly.

This is the change with the widest reach, and it is Chapter 10's Case Study 1 recurring: a hand-written fixture represents what the engineer believes the data looks like, which is exactly the belief that is wrong when there is a bug.

4. A shape assertion on every consumed event type, running in the pipeline and in CI, asserting that the fields the consumer reads are present.

📐 Design Decision — REPLICA IDENTITY FULL everywhere, or per table?

The obvious response is to set FULL on every captured table and stop thinking about it.

The case for: one rule, no per-table decision, no possibility of this class of bug. Simplicity has real value and a per-table decision is a thing someone will get wrong.

The case against, which won: FULL writes the entire old row into the WAL on every update, and on wide, high-update tables that is substantial. Kestrel's measured 31% on orders is a 31% increase in WAL volume, which flows into replication bandwidth, archive storage, and — relevantly — how fast an abandoned slot fills a disk (Case Study 1).

The rule adopted: FULL where a downstream model reads the before image, DEFAULT elsewhere, recorded in the same document as the dimension design so the two decisions sit together. A new Type 2 attribute is a prompt to check REPLICA IDENTITY.

What that gives up: the per-table decision can be got wrong, silently, in exactly the way this case study describes. The mitigation is change 4 — the shape assertion — which catches a wrong decision on the first event rather than in week nine.

What Happened

The nine weeks of history were not recoverable as history. Bronze had every event, so the current state was correct throughout — no customer's data was wrong — but the before images were never captured, so the intermediate states are permanently gone.

The team reconstructed what they could from a different source: the nightly Type 1 snapshots that the old pipeline had produced and that nobody had deleted. Those gave daily granularity rather than per-change, which was good enough for the regional analysis and not good enough in general.

That recovery was luck, and the team recorded it as such: they had kept the old pipeline's output for two months out of caution, and it happened to cover the window.

Since then:

  • The shape assertion has fired twice. Once when a new table was added to the connector without REPLICA IDENTITY being considered — caught on the first event. Once when a source column was renamed and the consumer's expected field list was not updated.
  • Fixtures are regenerated quarterly from production. The first regeneration invalidated four existing tests, all of which had been passing against unrealistic data.
  • No Type 2 dimension has silently produced Type 1 output.

Lessons

  1. REPLICA IDENTITY DEFAULT puts only the primary key in before. It is the PostgreSQL default, it is the most common Debezium misconfiguration, and its symptom is not an error.

  2. A workaround that tolerates a misconfiguration encodes it. The engineer diagnosed the partial before image correctly and then wrote code that accepted it.

  3. A fallback that produces a plausible answer hides the cause forever. When you write one, ask whether it is plausible — and if it is, raise instead.

  4. The error message is the deliverable. "before image contains only [customer_id]; check REPLICA IDENTITY" is the difference between an hour and nine weeks.

  5. Assert the shape of events, not just their arrival. Every check asked whether events were coming; none asked what was in them.

  6. Capture fixtures from production. A hand-written fixture encodes what the engineer believes the data looks like, which is the belief that is wrong when there is a bug. The first regeneration invalidated four passing tests.

  7. Decide REPLICA IDENTITY per table and record it beside the dimension design, so a new Type 2 attribute prompts a check. FULL cost 18% and 31% more WAL on two tables — real, and it also affects how fast an abandoned slot fills a disk.

  8. Intermediate states not captured are permanently gone. The current state was always correct; the history could not be reconstructed except by luck.

Questions for Discussion

  1. The workaround was added in week 2 of development with a comment explaining exactly why. What should a reviewer seeing that comment have asked?

  2. The test passed against a hand-written fixture with a complete before image. Whose responsibility is fixture realism — the test author, the reviewer, or a process? Design the process.

  3. Nine weeks of history are gone, recovered partially by luck. What would a deliberate safety net look like during a migration from one dimension strategy to another, and what does it cost?

  4. REPLICA IDENTITY FULL raised WAL by 31% on orders. Trace the downstream effects of that: which other systems and costs does it touch? Case Study 1 names one.

  5. The shape assertion caught a renamed column that the consumer's field list had not tracked. Is that assertion doing too many jobs? What would you split out?

  6. This case study and Chapter 12's vector-index one share a shape: derived data silently produced without the property it was built for. Name the general check that catches both.

  7. The team recorded that their recovery was luck. How often do you think incident reviews name luck as a factor? What would change if they did it more?