Capstone: returns and refunds

The ladder is the deliverable as much as the tables are. Every time a team has skipped a rung on a subject area this size, the same thing happens: the DDL gets written first, the conceptual question ("is a return an entity?") gets answered implicitly by whoever typed fastest, and the answer turns out to be wrong three months later when somebody asks for return reasons by merchant.

• Conceptual before columns. Eight minutes with no SQL editor open. Write the entity list and the grain sentence on paper.
• Keys before types. Decide what identifies a return and what it points at before you decide whether a timestamp is TEXT or INTEGER.
• OLTP before dimensional. The star is derived from the source. If the source cannot record the reason, no dimension can invent it.
• Serve last, and only what someone asked for. One mart, one view, three metrics. A capstone that ships eleven views has misunderstood the exercise.

A return is an entity. It has its own identity (a return reference the shopper quotes in support tickets), its own lifecycle (requested, then approved or rejected, then received), its own timestamps (requested at, decided at), its own actor (whoever approved it), and - decisively - it can be rejected. A status value on the order line cannot be rejected, because rejecting it would mean setting the line back to a state that erases the fact that anyone ever asked. The request itself is information the business needs to keep.

The grain is one row per returned order line, not one per order. The data above shows why: a shopper who bought three things and sent one back has produced one return against one line. If your grain were the order, you would have to store which line came back somewhere else, and you would be modelling a line-level fact at order level - the same grain error session b5 diagnosed on the flat export, in a new costume.

• returns : order_items is many-to-one. One line can be returned more than once (a partial return in April, another unit in May), and each is its own event.
• returns : products is many-to-one, carried on the return the same way order_items carries merchant_id: a point-in-time capture, so the return still reports the right product if the catalogue is reorganised.
• returns : transactions is many-to-one and optional. A rejected return has no refund. An order refunded in one payment can settle three approved return lines.

The shortcut is order_items.is_returned INTEGER DEFAULT 0. It takes ten seconds, it makes "how many returns" answerable, and it destroys four things at once:

• The reason. "Damaged" and "changed mind" are the same flag, so merchant fault and shopper preference become one number - and the merchant-quality conversation is over before it starts.
• The approver and the decision. A flag has no actor and no outcome. Rejected requests vanish entirely, so the rejection rate cannot be measured and disputes cannot be audited.
• The timing. One flag has one implicit date at best. "How long do we take to decide a return" needs two timestamps, and "returns by request week" needs the return's own date, not the order's.
• Partial and repeat returns. A boolean cannot hold "one of the two units came back", so quantity returned is unrecoverable.

Notice the shape of that list. The flag is not wrong because it is small. It is wrong because it answers exactly one question and forecloses every follow-up, and follow-ups are the entire reason anyone builds a model.

The refund transaction is at payment-attempt grain. It knows an order was refunded 137.12 and it has no idea which of that order's three lines came back, or why, or whether one unit or four were sent back. So the money is recorded and the event is not, which is why this subject area needs modelling at all rather than a view over transactions.

That gives you two defensible designs, and one indefensible one:

• Defensible - the return points at the refund. returns.refund_txn_id references transactions(txn_id), nullable because a rejected return has none. The amount lives once, on the transaction. Return value is then derived by joining, and a partial refund covering several return lines needs an allocation rule you write down.
• Defensible - the return carries its own allocated amount. returns.refund_amount holds this line's share, and refund_txn_id holds the reconciliation key. Line-level return value becomes a simple SUM, at the cost of a copy of the money that can drift.
• Indefensible - both, with nothing that reconciles them. The amount on the return and the amount on the transaction, no key linking them and no check comparing them. Two numbers for the same money, and the day they disagree nobody knows which one finance quoted.

Take the second option, because line-level return value is a metric the business will ask for weekly - but only if you also ship the reconciliation. That means the key (refund_txn_id) plus a validation check asserting that the return lines under one refund sum to that refund's amount. Checkpoint 5 runs exactly that check, and it either agrees to the cent or your model is lying.

Four codes: damaged, wrong_item, changed_mind, not_as_described. Snake case, singular concept per code, no spaces, no capitals - the naming standard from session a5, applied without exception because the moment one code is Damaged you own a cleaning step forever.

• A constrained set is a contract with the future. Free text gives you "damaged", "Damaged", "arrived broken", "DMG" and forty singletons, and every one of them is a row that falls out of your GROUP BY.
• Free text has a place, beside the code, not instead of it. Add reason_note TEXT if support needs the shopper's own words. It is never grouped by, and nothing downstream depends on it.
• Adding a fifth code is a modeling event. It changes a CHECK constraint, a dimension row and a metric's meaning. That is a feature: the friction is what stops the set silently sprawling to thirty.
• Never allow a NULL reason. A return with no reason is a row that cannot be grouped, cannot be attributed to merchant fault or shopper choice, and quietly disappears from every reason breakdown. If the source genuinely does not know, that is a code - unknown - not an absence.

Also decide the status set now: requested, approved, rejected, received. Four values, one lifecycle, and a rejected return that stays in the table forever. If you find yourself wanting a fifth status called refunded, stop - that is the transaction's state, not the return's, and putting it here is how two tables start disagreeing about the same fact.

Both queries above found a violation, and the database accepted both rows without complaint. Be precise about why, because "add a constraint" is the wrong instinct here and knowing that is the point of the checkpoint.

• A CHECK constraint sees one row of one table. "Returned units must not exceed units sold" needs order_items.quantity, which is a different table. In standard SQL the tool for this is an assertion, and essentially no engine implements assertions. So the constraint cannot be written.
• The cumulative version is harder still. Even with a magic cross-table CHECK, the real rule is about the sum of every approved return on that line, which is a set, not a row. Returns 1 and 3 above are each individually plausible and jointly impossible.
• Where it goes, in descending order of preference: (1) the application, inside the same transaction that inserts the return, holding a lock on the line - the only place it can be genuinely prevented; (2) a database trigger, which is closer to the data but easy to forget and awkward to test; (3) a validation check that runs on every load and fails loudly, which detects rather than prevents.
• Ship the detection either way. Option 3 is not a substitute for option 1, and option 1 is not an excuse to skip option 3 - the source of bad rows is usually a backfill or a support tool that never went through the application.

The DDL above adds two, and both are justified by a named access path rather than a hunch:

• (order_id, line_no) - support opens an order and asks "has anything here been returned?" This is also the join every load and every validation check uses, so it earns its write cost several times over.
• requested_ts - the reporting scan. "Returns requested last week" is a range predicate on this column, and without an index it is a full table scan every time the mart rebuilds.

What is deliberately not indexed: reason_code and status, both of which have four values. A four-value column matches roughly a quarter of the table, so the planner will usually scan anyway and you have paid for an index nobody uses. Low cardinality plus a filter that is not selective equals a write tax with no read benefit - which is the whole of session b5's index argument, applied to your own table for the first time.

fact_return is a new fact table at return-line grain. Any one of these three would settle it; you have all three:

• Different grain. One order line can produce two returns, so return rows do not divide evenly into sales rows. Putting them together fans the sale out, which the diagram above prices at 37.00 instead of 18.50 - the same arithmetic as session b9's fact-to-fact double count.
• Different date. fact_order_item.date_key is when the sale happened; fact_return.date_key is when the return was requested, usually days later and sometimes in a different month. Two dates in one row means one of them is not the row's date, and every time-series query has to guess which.
• Different lifecycle, including a state with no money. A return can be rejected. A rejected return is a real event with a real reason that the business must be able to count, and it is not a sale event at all. Return 3 above is that row: units returned, zero approved, zero money.

Dimensions reused, not rebuilt: dim_date (on the request date), dim_time_of_day, dim_user, dim_merchant and dim_product. Five of the six existing dimensions carry straight over, which is the whole return on the conformance investment made in b6 - "returns by merchant tier" and "returns by product category" work the moment the fact lands, with no new dimension and no new join rules. Only dim_return_reason is new, because only the reason is new vocabulary.

On the product and merchant keys: use the same surrogate the sale used, so a return reports the merchant tier and list price that were true at the time of sale. Resolving them as at the return date instead would attribute an April sale's return to June's tier, and the two facts would stop agreeing about the same merchant.

A return has a lifecycle with milestones: requested, decided, received, refunded. Kimball has a fact type built for exactly that shape - the accumulating snapshot, one row per process instance with a date key per milestone and a lag measure between them, updated in place as the process advances.

It is a genuinely good fit and it is the right answer for some organisations. It is not the answer here, for reasons worth being able to state:

• It is an update-in-place fact. Rows mutate as milestones land, which is a loading pattern (and a cost model) that belongs to learn-data-warehouse, not to a modeling capstone.
• The transaction-grain fact is a strict prerequisite anyway. Build the event fact first; the snapshot is derived from it. Doing it the other way round is how teams end up unable to answer anything the snapshot did not anticipate.
• Bazaar has two milestones, not six. Requested and decided. An accumulating snapshot earns its complexity at five or six milestones with real lag questions between each pair, and pays for itself in shipping-and-fulfilment pipelines rather than here.

Write the decision down either way. "We considered an accumulating snapshot and chose a transaction-grain fact because the lifecycle has two milestones" is the kind of sentence that stops the same debate being reopened every six months - and it belongs in semantic/contract.yaml under known limitations, not in somebody's memory.

The prototype above joins returns to the mart on the sale date, not the request date, and that is a modeling decision you have to make explicitly rather than by accident:

• By sale date (cohort attribution). "Of the units we sold on 3 April, what share came back?" The numerator and the denominator describe the same units, so the ratio means something. The cost is that the number for recent days keeps rising as returns arrive - it is not final for weeks.
• By request date (operational load). "How many returns landed on the desk on 9 April?" Final the moment the day ends, and the right number for staffing a returns team. But dividing it by the same day's sales is meaningless, because those are different units.

Both are legitimate metrics. Shipping one and calling it "return rate" without saying which is how two teams end up with two numbers and a meeting. Pick the cohort version for return_rate because it is the one leadership means, keep the request-date count as a separate measure, and put the distinction in the caveat column where an agent and a new analyst will both read it.

The golden set is a test suite for your model's answerability, so a good new question is one that fails without the artifacts you just shipped. Add these two to semantic/golden_questions.jsonl as q21 and q22, each with the SQL you believe is correct and the wrong answer you expect from an ungrounded agent:

Then extend semantic/schema_card.md with v_return_line and its grain sentence, and semantic/contract.yaml with the new forbidden join, the three metric definitions, the synonyms the business actually says ("RMA", "refund rate", "send-backs") and the known limitation you wrote down in checkpoint 4. Grade with execution match, exactly as b9 did: run both queries, compare result sets to the cent.

The 27 checks in python/validate_model.py are grouped by the order things actually go wrong, and every group has an obvious returns equivalent. Write yours to the same shape - one row, one column, zero means pass - and add them to the CHECKS list:

• grain - no duplicate return_id in fact_return; no duplicate (activity_date, merchant_id) in mart_returns_daily.
• keys - dim_return_reason.reason_code unique and never NULL. This is the check that catches a free-text reason sneaking back in.
• integrity - every fact_return.return_reason_key resolves in dim_return_reason; every merchant_sk, product_sk and date_key resolves too. An orphan return silently disappears from every inner join.
• additivity - approved_units + rejected units = units_returned; resellable_units + write_off_units never exceeds approved_units; refund_amount = 0 whenever is_rejected = 1; no row both approved and rejected.
• reconcile - the row count matches returns in the source, and the sum of refund_amount per refund_txn_id equals that transaction's amount. This is the checkpoint-2 promise, enforced.
• the cross-table rule - approved units returned per order line never exceed units sold. The rule no CHECK could express, detected on every load.
• nulls - no NULL attributes in dim_return_reason, and no NULL reason_code anywhere.
• gauge - unknown-member rate on fact_return. It should be 0.0%, the same as every other fact.

Then re-run the whole file. All 27 existing checks must still pass, because you did not touch anything they cover - and if one of them broke, that is the most useful failure of the whole session: it means the new subject area reached into the old model somewhere it should not have.

Four failures account for almost every version of this exercise that goes wrong. Each one runs cleanly, produces numbers, and is discovered months later by an argument in a meeting.

• Return as a flag. order_items.is_returned. Loses the reason, the approver, the timing, the quantity and every rejected request. Cheap to write, impossible to unwind once six dashboards read it.
• The refund amount stored twice with nothing reconciling it. An amount on the return and an amount on the transaction, no key between them and no check comparing them. The day they disagree, both numbers are suspect and neither can be defended.
• return_rate stored as a ratio. A stored rate cannot be re-aggregated: averaging daily rates to a month weights a Tuesday with four sales the same as a Saturday with four hundred. Store the two counts and divide at read time, every time.
• A NULL reason. One nullable column, and every reason breakdown quietly drops rows while still looking complete. unknown is a code with a dimension row and a surrogate key; NULL is an absence that inner joins delete.