Pending refunds inflated marketplace revenue by 4% — 2023

Cost: ~4% revenue overstatement in weekly board pack for one quarter; restatement to the operating committee and rebuild of the affected dashboard · Time-to-detect: ~67 days (model deploy to finance-team reconciliation) · Root cause class: T4 (cardinality-bound — unexpected row multiplication on a join)

What happened

A two-sided marketplace operator owned a fct_revenue model that aggregated gross merchandise value, fees, and refunds per seller per day. A senior analyst refactored the refunds branch to use a new fct_refunds table that the platform team had introduced, replacing an older derived view. The new table held one row per refund state transition — requested, approved, pending_settlement, completed, failed — rather than one row per refund.

The refactor LEFT JOINed fct_refunds to fct_orders on order_id and subtracted refund_amount. It did not include WHERE refund_status = 'completed'. For most orders this was harmless; for the ~14% of orders with multi-state refund histories, every transition row got subtracted. A typical refunded order produced 3-5 transition rows, so its refund amount was subtracted 3-5 times. Net effect: gross revenue understated, refunds overstated, and a downstream "net revenue retention" derived metric that depended on gross - refunds ended up ~4% high because the sign error in refunds inverted the magnitude of the impact.

The discrepancy went undetected through two months of weekly board packs because the absolute revenue trend looked normal — the marketplace was growing fast enough that the 4% bias sat inside week-to-week variance. Finance caught it during quarter-end reconciliation against the payment processor's own statement.

The pattern

A join multiplied row counts in a way the analyst did not expect, because the joined table's grain had silently changed from "one row per entity" to "one row per entity-state-transition". Every aggregation downstream of the join silently double-counted. The schema was unchanged, the column types matched, and dbt test's unique and not_null tests on the join keys passed because they were on the order-side, not the refund-side.

Any pipeline where a join's right-hand-side cardinality per join-key is not declared and verified, and downstream aggregations assume ≤ 1, has this exposure: SCDs joined as if they were Type 1, event tables joined as if they were entity tables, append-only audit tables joined for "current state".

How veric would catch it

veric's T4 tier carries a declared and inferred cardinality bound on every column in the model graph, including the multiplicity a join introduces. With fct_refunds.order_id : cardinality(many_per_order) declared on the new table — or inferred from the table's row-count vs distinct-order_id-count at build time — the verifier flags: "join fct_orders ⨝ fct_refunds on order_id produces n × m rows where m > 1 for 14% of join keys; downstream aggregation sum(refund_amount) does not project to a unique-per-order grain — T4 cardinality-bounded VIOLATED, declare a WHERE filter or aggregate refunds before the join."

The same primitive catches the family: any join whose right-side multiplicity quietly inflates a downstream sum, count, or average.

Try it: open the example below and watch the verdict change as you toggle the offending pattern on and off.

Sources

Anonymised; pattern reflects the well-documented "fan-out join" failure class. Public references include Kimball's The Data Warehouse Toolkit (3rd ed., chap. 4) on grain mismatches and the recurring dbt-community pattern documented in dbt Labs' "Joining Tables" guidance.