Pure-SQL cost allocation and fully-loaded segment P&L on DuckDB, showing how a naive revenue-weighted split hid a $1.3M under-allocation and flipped SMB from a marginally-unprofitable segment to a -99% margin unit under real drivers.
-brightgreen){kind=icon}
- Revenue-weighted allocation hides who actually consumes shared cost. On the demo dataset it under-allocates the smallest segment by $1.3M in shared cost, making a heavy-consumer look breakeven and a light-consumer look barely profitable.
- A P&L needs a defensible allocation method, not a plausible one. Every pool here declares its own natural driver (deals, headcount, or cloud consumption) so the allocation logic is explicit and challengeable, not hidden in a formula.
- Cost allocation math without a tie-out is guessing. Every pool must fully allocate; assertions catch missing drivers and dropped rows, and DuckDB's
-bailflag makes those assertions actual gates on the build.
Fully-loading a segment P&L means attributing shared costs (marketing, R&D, G&A, cloud infrastructure) to the segments that consumed them. It is the step where profitability analysis stops being arithmetic and starts being an editorial act: whichever driver you pick determines who looks good. The most common shortcut is to split every shared pool by revenue, which is defensible on the surface (bigger revenue absorbs more cost) and wrong on inspection, because most shared cost is not driven by revenue at all.
This engine implements both methods on a three-segment quarterly dataset ($10.7M revenue, $5.9M shared cost pools) so the choice is measurable rather than theoretical. The naive revenue-weighted method reports Enterprise at 5.3% operating margin, Mid-Market at 3.9%, and SMB at -5.1%. Reading that P&L, a finance team might conclude SMB is a marginal drag and lean in on Enterprise growth.
The driver-based method tells a very different story. Marketing allocates by deal count, R&D and G&A by segment-focused headcount, cloud infrastructure by normalized compute consumption. Enterprise's true margin is 26.0%, Mid-Market is 5.2%, and SMB is -99.4% because it drives 34% of deals and 35% of cloud units on 13% of revenue. The management action changes from "invest in SMB marketing" to "reprice or restructure SMB before adding load." Every figure is produced by make run against the committed SQL and gated by assertions that fail the build if any pool does not fully allocate.
flowchart TD
G["01_generate<br/>segments, direct costs<br/>cost pools, drivers"] --> N["02_allocate<br/>naive: revenue-weighted"]
G --> D["04_allocate_driver<br/>driver-based per pool"]
N --> P1["03_pnl<br/>naive P&L"]
D --> P2["03_pnl<br/>driver-based P&L"]
N --> C["05_comparison<br/>side by side, reallocation delta"]
D --> C
D -. "each pool must fully allocate" .-> T["tests/assertions<br/>gated by duckdb --bail"]
| Technology | Role in this project | Why chosen here |
|---|---|---|
| DuckDB | Executes every step of the pipeline | Zero server, sub-second on small data, linear at scale to 1M segments (see benchmark below) |
| Pure SQL | All allocation and P&L logic | A finance reviewer opens sql/04_allocate_driver.sql and reads the entire driver-based allocation in one screen (see ADR-0001) |
| Makefile | run, test, bench, clean targets |
Explicit dependencies; the pipeline is legible without reading the code |
DuckDB error() + -bail |
Test framework | Assertions become real gates: a broken driver mapping fails CI with a non-zero exit, verified by reintroducing the bug |
Prerequisites: DuckDB CLI 1.1 or later.
git clone https://github.com/Vanithanallamothu/segment-margin-engine.git
cd segment-margin-engine
make run # generate data, run both allocations, print the P&Ls and comparison
make test # run assertions (a failure aborts the build)
make bench # scale test at 1k, 10k, 100k, 1M segmentsSame revenue, same direct costs, dramatically different picture depending on how shared costs get attributed:
| Segment | Revenue | Naive alloc | Driver alloc | Reallocation | Naive margin | Driver margin |
|---|---|---|---|---|---|---|
| Enterprise | 6,200,000 | 3,418,692 | 2,140,783 | -1,277,908 | 5.34% | 25.96% |
| Mid-Market | 3,100,000 | 1,709,346 | 1,668,018 | -41,327 | 3.89% | 5.23% |
| SMB | 1,400,000 | 771,963 | 2,091,198 | +1,319,236 | -5.14% | -99.37% |
SMB is under-allocated by $1.32M under revenue-weighting; Enterprise is over-allocated by roughly the same amount. This is why the naive method looks reasonable: it balances, but it balances against the wrong signal.
The allocation is a set-based join, so it scales linearly with the number of segments. Measured on a laptop via make bench:
| Segments | Rows out | Wall time |
|---|---|---|
| 1,000 | 4,000 | 0.09 s |
| 10,000 | 40,000 | 0.17 s |
| 100,000 | 400,000 | 0.67 s |
| 1,000,000 | 4,000,000 | 5.49 s |
A 1000-times increase in segment count costs about 60 times the runtime because of fixed overhead. The tie-out assertion runs at every scale, so the correctness guarantee holds up to at least a million segments without change.
- ADR-0001: Pure SQL and a Makefile, not Python orchestration or a dbt project
- ADR-0002: Allocate each cost pool by its natural driver, not by revenue
Reciprocal cost allocation (where one shared pool consumes another, for example G&A supporting R&D) is intentionally not implemented. Adding it means solving a small linear system and doubling the model complexity, and it is only worth doing when the shared-team-of-shared-teams effect is material. The trigger to add it is a case where one pool's largest driver is another pool's headcount. Also out of scope: fixed vs variable cost decomposition, activity-based costing at the transaction grain, and time-phased allocation (monthly bridges); each of those is a real project of its own.
- No secrets, no credentials: the engine runs against a local DuckDB file, and the pipeline has no network calls.
- The dataset is fully synthetic and deterministic, so there is no financial data in the repo or in CI logs.
- CI pins DuckDB to 1.1.3 for reproducible builds.
- A pool is entirely missing from the driver-based allocation. A broken
natural_drivervalue (typo, missing driver row) means the join drops the pool silently. Assertion #3 catches it: every pool must appear with every segment. Verified by reintroducing the bug during development. - A pool does not fully allocate. The tie-out assertion fails if any pool's allocated sum differs from its budget by more than one cent. Also verified against a deliberately introduced bug.
- A segment has zero of a driver. With a strictly positive driver total, its share becomes zero; the P&L still ties out. If every segment has zero of a driver, the pool's total is zero (undefined behavior guarded by the tie-out).
- Assertions look like they passed but did not. Fixed during development: DuckDB CLI continues past
error()calls by default and always exits 0. Thetesttarget usesduckdb -bailso a failed assertion aborts, andmakesees a non-zero exit.
The naive allocation table produced numbers that looked internally consistent, so my first instinct was to trust it. The check that broke that trust was the driver data itself: SMB has 13% of revenue but drives 34% of deals and 35% of cloud units. If the allocation is fair, SMB's share of the marketing and cloud pools should look a lot more like 34% than 13%. Under the revenue-weighted method SMB was getting 13% of everything.
The fix was to keep the driver per pool as data (cost_pools.natural_driver) rather than bake it into the SQL, and allocate each pool by its own driver's segment shares. On the demo the reallocation moved $1.32M of shared cost from Enterprise onto SMB, and the P&L rewrote itself: Enterprise's true margin climbed from 5.3 to 26.0 percent, and SMB dropped from -5.1 to -99.4 percent. The finding a management team can act on is not "SMB is a bit unprofitable" but "SMB is loss-making at contribution because it consumes disproportionate shared resources," which is a pricing or product problem, not a marketing one. Fixed in commit e2a9f9e, guarded by the assertions in tests/assertions.sql.
A second lesson lived inside the fix. I wrote the tests file and it printed "all assertions passed" whether the assertions passed or not. DuckDB CLI continues past errors by default. Switching the test target to duckdb -bail was the small change that turned the tests into a real gate, and reintroducing the bug once was the proof.
- Add reciprocal allocation for pools that consume each other (small linear system).
- Allow multiple drivers per pool with weights (for example, R&D 70% headcount plus 30% cloud usage).
- Extend to a time-phased engine that produces a monthly rolling P&L rather than a single quarter.
- Emit the comparison mart as Parquet for direct load into a BI tool.
- Track the driver-allocation share of each pool over time; a pool where the drivers keep shifting is a signal that a business change is invalidating the allocation policy and it needs review.