Case Study 1: The Week Spent on One Question

"We thought we were arguing about a bridge table. We were arguing about what a discount is, and three departments had different answers."

Executive Summary

While modeling fct_order_item, Kestrel's data team hit a question that looked clerical: does a promotion apply to an order, or to an order line? The answer determines whether the model needs a bridge table, which is a modeling decision worth perhaps an hour.

It took a week, because the question turned out to be a proxy for a business disagreement that had been latent for three years. Merchandising, finance, and growth each had a working definition of "discount," each was internally consistent, and each produced a different revenue figure. The existing reports had been quietly using all three.

This case study is about modeling as an instrument for finding organizational disagreement. The schema at the end is unremarkable — a bridge table with an allocation weight, exactly as §6.7 describes. The value is in how the disagreement surfaced, how it was resolved, and what the team did so the resolution would stick.

Skills applied: the four-step process and grain declaration (§6.3); bridge tables and allocation weights (§6.7); the fan trap (§6.9); definitional resolution (§6.8).

Background

The trigger. The team was at step 3 of the four-step process for order placement — identifying dimensions. dim_promotion was on the list and looked routine. The question was where the key goes.

If a promotion applies to an order, then promotion_key belongs on fct_order, one per row, and fct_order_item gets it by inheritance.

If a promotion applies to a line, then promotion_key belongs on fct_order_item. And if more than one promotion can apply to one line, you need a bridge table.

What the source data said. They looked. order_items.discount_cents was a single number per line, with no reference to any promotion. promotions existed as a table. There was no join between them anywhere in the source schema.

The discount had been applied by the checkout service and the promotion that caused it had not been recorded.

⚠️ Failure Mode — The number without its reason

discount_cents records that a discount was applied and not why. The source system had no reason to record why: the checkout service computes a price, charges it, and moves on. The reason was operationally irrelevant to the system that produced the number.

This is one of the most common shapes of data problem, and it is genuinely nobody's fault. A source system records what it needs to do its job, and analytics almost always needs one more column. The checkout service was correct not to store what it did not use.

The consequence is that the question "which promotions actually drive revenue" was unanswerable from historical data and would remain so until the source started recording it. Three years of promotional history existed only as an undifferentiated discount_cents.

Two things fall out of this, and the second is the one people miss:

1. The fix is upstream. The checkout service must emit the promotion identifier. That is a product engineering change, it takes a quarter to land, and there is no data engineering substitute for it.
2. Ask early, for every metric you expect to want. The cost of adding a column to a source system's event is small at design time and enormous retroactively, because the past cannot be regenerated. Chapter 17's data contracts are the mechanism for having this conversation before rather than after.

The Problem

With the source unable to answer, the team went to the business. That is where the week went.

Merchandising's definition. A promotion is a campaign — "20% off outerwear, November 3–10." It applies to products, so its effect is naturally per line. They wanted to know: for each campaign, what was the incremental revenue on the products it covered?

Finance's definition. A discount is a reduction in the amount collected, full stop. It applies to an order, because that is what the customer is invoiced for and what the payment processor settles. Splitting an order-level discount across lines is, in their words, "making up a number."

Growth's definition. A promotion is anything that changed the customer's decision — including a personal code, a free-shipping threshold, a first-order incentive, and a loyalty tier discount. They wanted attribution per customer per campaign, and they considered free shipping a promotion, which neither of the other teams did.

Three definitions, three grains, three scopes. And every one of them was in use somewhere:

That last row is the one that made the meeting go badly. The executive summary was computing a discount rate by dividing merchandising's discount by finance's revenue — two different scopes over two different grains — and the resulting percentage had been quoted in a board deck.

The Analysis

The team did three things, in an order worth copying.

1. They stopped arguing about the schema

Two days in, the discussion was about whether to use a bridge table. The manager stopped it and pointed out that the schema question could not be answered until the definition was.

This is the single most useful intervention in the case study. Schema arguments are comfortable — they are technical, bounded, and everyone can participate. Definitional arguments are uncomfortable and require someone to concede. Teams drift from the second to the first without noticing, and the tell is that the technical discussion keeps going in circles.

2. They enumerated discount types from the data

Rather than asking teams what promotions existed, they measured what the data contained. One query against three years of orders, bucketing by discount characteristics:

59% of discounts apply at order level and 41% at line level. Neither definition was a minority position, which is exactly why the disagreement had persisted — both sides had most of a case.

That measurement reframed the conversation from "who is right" to "we clearly have two things and have been calling them one."

3. They separated the model from the metric

The resolution came from noticing that the three teams needed different metrics, not different models. A model that records what happened at the finest available grain can serve all three; a model that bakes in one team's definition can serve only that team.

The Decision

The model:

fct_order_item                 bridge_order_item_promotion            dim_promotion
  order_item_id  ─────────────▶  order_item_id                          promotion_key
  ...                            promotion_key  ◀─────────────────────  code
  discount_cents                 allocation_weight   DECIMAL(9,8)       kind
                                 allocated_discount_cents               scope: 'line'|'order'
                                                                        starts_at, ends_at

In words: order lines join to promotions through a bridge table that carries an allocation weight and the allocated discount amount. dim_promotion records the promotion's scope — whether it was natively a line-level or order-level promotion — which is the column that lets each team filter to the definition it means.

The allocation rule, written down, for order-level promotions split across lines:

An order-level discount is allocated to lines in proportion to each line's extended_price_cents as a share of the order's total extended_price_cents, rounded to the cent, with any rounding remainder assigned to the largest line so that allocated amounts sum exactly to the order-level discount.

That last clause — the remainder rule — is not decoration. Without it, allocated line discounts sum to within a cent or two of the order discount, and a reconciliation to the cent fails on roughly one order in three.

Three metrics, defined once each:

Each is a named metric in the semantic layer. Nobody says "discount" without a prefix, and the executive summary was rebuilt to use total_discount_cents over net_revenue_cents, both from the same grain.

📐 Design Decision — Allocate, or refuse to allocate?

The team seriously considered not allocating order-level discounts to lines, keeping them only in fct_order, and telling merchandising that line-level discount analysis simply excludes order-level promotions.

The case for refusing: an allocation is a fabricated number. Finance's objection was substantive — nobody decided that this line bore $2.14 of the $10 order discount; a rule decided it. Once allocated, that fabricated number will be summed, charted, and quoted by people who have no idea a rule produced it.

The case for allocating: 59% of discounts are order-level. Excluding them from line analysis means merchandising's campaign view is missing most of the discounting, which makes it misleading in a different and less visible way.

What decided it: allocation was chosen and made explicit. The bridge table carries allocation_weight as a visible column rather than only the allocated amount, so anyone inspecting a row can see that a rule was applied and what it was. dim_promotion.scope lets any consumer exclude allocated amounts entirely.

What that costs: every consumer now has a choice to make, and some will make it wrong. The mitigation is that the three named metrics make the common cases correct by default — a consumer has to go out of their way to build the wrong thing.

The generalizable rule: if you must fabricate a number, make the fabrication visible in the data rather than only in the documentation. A weight column beside the allocated amount is worth more than a paragraph nobody reads.

What Happened

The model shipped in three days once the definitions were settled. The week was the definitions.

The upstream fix took two quarters. Product engineering added promotion_id to the checkout event and to order_items, which meant that from 2025-09 onward, promotions were recorded rather than inferred. Everything before that date uses a heuristic reconstruction — matching discount patterns to promotion validity windows — which is documented, imperfect, and flagged in the model with a promotion_attribution_method column reading 'recorded' or 'inferred'.

That flag column is the most-praised part of the design, according to the team's own retrospective. Analysts can exclude inferred attribution when precision matters. Nobody has to remember which date the change happened.

The executive summary's discount rate changed by 4.2 percentage points when it was rebuilt on consistent definitions. That conversation was uncomfortable and it was had once, rather than being had implicitly every quarter.

Two follow-on effects:

The allocation remainder rule was tested (SUM(allocated_discount_cents) per order equals fct_order.order_discount_cents exactly) and the test failed twice in the first month — both times on orders with a single line where the code took a shortcut, and both times caught before anyone saw a wrong number.

Six months later, a new promotion type — a buy-two-get-one offer — did not fit the allocation rule, because the discount is conceptually on a specific line rather than proportional. The scope column made room for a third value, 'line_specific', without restructuring anything. A model that records a rule's identity can accommodate a new rule; a model that has a rule baked into a number cannot.

Lessons

1. Modeling surfaces organizational disagreement. The question was "bridge table or not." The answer required three departments to agree on what a discount is, and they had not for three years.

2. When a technical argument goes in circles, check whether it is a proxy. Schema debates are comfortable; definitional debates require someone to concede. Teams drift from the second to the first without noticing.

3. Measure the data before asking the business. 59/41 was the fact that reframed the argument from "who is right" to "we have two things and one word."

4. A source system records what it needs, and analytics always needs one more column. The promotion that caused a discount was never recorded because checkout did not need it. Ask early; the past cannot be regenerated.

5. Separate the model from the metric. One model at the finest grain serves three definitions. A model with one definition baked in serves one.

6. If you must fabricate a number, make the fabrication visible in the data. The allocation_weight column and the scope column are worth more than documentation.

7. Write the remainder rule. Allocation without an explicit rounding rule fails a reconcile-to-the-cent test on a large fraction of rows.

8. A method flag beats institutional memory. promotion_attribution_method means nobody has to remember which date the upstream fix landed.

Questions for Discussion

1. The executive summary divided merchandising's discount by finance's revenue. How would you find other instances of this pattern — a metric assembled from two incompatible sources — across twenty-two dashboards?

2. The team spent two days on the schema before the manager redirected them. What would have caught it sooner? Is there a question that reliably distinguishes a technical disagreement from a definitional one?

3. Finance objected that allocation is "making up a number," and they were right. Construct the strongest version of the refuse-to-allocate position and say what merchandising would have had to accept.

4. The pre-2025-09 promotion attribution is inferred by matching discount patterns to validity windows. Design that heuristic. Then estimate its error rate and say how you would measure it.

5. promotion_attribution_method distinguishes recorded from inferred data. Where else in a data platform would a method flag like this be valuable? What is the cost of adding them liberally?

6. The upstream fix took two quarters. What could the data team have offered product engineering to make it faster, and at what point does pushing for upstream change become someone else's prioritization problem rather than yours?

7. The discount rate in the board deck changed by 4.2 points. Write the two paragraphs explaining that change to an executive audience. What do you lead with?