How a partial payment on invoice 3959 redesigned our warehouse

It started, as these things often do, with a number that didn’t add up — or rather, with 29 rows where I expected four. This is my first post here, so a quick introduction: by day I work in data at Holland Casino; besides that I run KnowV8, where I’m building a BI platform (Snowflake, dbt, Airflow, a FastAPI report portal) for a client in architecture. Today my colleague and I were supposed to “just ship an accounts receivable report.” Instead, one partially paid invoice rewrote our warehouse design. This is the story, because I learned more Kimball in one day of production data than in years of reading about it.

The discrepancy

An accounts receivable (AR) report sounds simple: show every invoice that isn’t fully paid, and how overdue it is. Our source system’s general ledger makes this almost easy — every invoice raises AR with positive postings, every payment lowers it with negative ones. Net per invoice = what’s still owed. Sum everything: the grand total tied out to our client’s spreadsheet to the cent. Great. Ship it?

Then I drilled into one invoice — number 3959 — and got 29 ledger rows where I expected a handful. Amounts appearing three times. A +2,541.00 here, a −2,541.00 there, another +2,541.00 later. Payments in November 2024 that seemed to un-happen. If we were going to let users drill into invoices, this noise had to go.

Following the postings

Staring at the 29 rows long enough revealed three separate mechanisms stacked on top of each other:

• Double-entry accounting. Every event posts at least two legs (debit AR / credit revenue). Our report only looks at the AR leg, so every event is half a story by design.
• The edit trail. The source system never updates a posted line. Edit a draft invoice and it posts a full reversal plus a repost. That’s why one line item showed up three times: original, reversal, final version.
• A flag we didn’t know existed. Superseded versions are marked is_current = false by the system itself. The “noise” was labeled all along — we verified across every invoice that the flagged rows net to exactly zero.

Meanwhile my colleague had independently attacked the same noise with a clever filter: cancel out any two rows in the same invoice line with equal-and-opposite amounts, show what survives. Hand-rolled anti-noise. It worked on the invoices he tested.

What the data told us

Invoice 3959 is where both of our mental models went to die. Its final state: seven line items billed, the client paid six of them in full — and one in part. Billed 14,206.50, paid 7,796.25, open 6,410.25.

A partial payment has no equal-and-opposite twin. Pair-cancellation can’t touch it. And the reverse failure exists too: we found a fully paid invoice (hi, 4179) that was settled in chunks that don’t mirror the billed amounts — nothing pairs, so the filter proudly displays ten rows of a debt that doesn’t exist.

The insight that reorganized everything:

“Open” is never a property of a row. It’s a SUM at a grain you choose.

There is no paid-flag anywhere in the source, and there doesn’t need to be. Sum live postings per invoice: invoice-level openness. Sum per invoice line: line-level openness, partial payments handled for free:

group by line_key
having abs(sum(amount)) >= 0.01   -- open = at least one cent, either direction

The abs matters too: an overpaid line nets negative, and without it those anomalies (we found 15, one at −25,000) silently vanish — and they’re exactly what someone drilling into receivables wants to see.

The redesign

Once “openness is a sum at a grain” clicked, the warehouse layout drew itself — and it turned out to be textbook Kimball, which was the day’s second lesson: his three fact-table types aren’t taxonomy trivia, they’re what you rediscover when production data pushes back.

fct_gl_posting                  ← transaction fact: one row per ledger line,
  │                               immutable events, is_current flag carried
  ├─ fct_ar_open_item_month     ← periodic snapshot: open balance per invoice
  │                               per month-end (+ today) — aging & trend views
  └─ fct_ar_line                ← accumulating snapshot: each line's lifecycle
                                  billed → partially paid → paid (or overpaid)

One atomic fact, read by coarser-grained facts. A few rules fell out of the arguments we had along the way:

• Facts may read facts — but only toward coarser grain. Aggregates and snapshots derive from the atomic fact; never sideways, never finer. Circularity becomes impossible because grain only shrinks one way.
• Dimensions are the scope authority. Our account dimension decides which accounts exist; facts derive their universe from it. At a previous job we duplicated that logic in every fact — a habit from the old ETL-tool era that dbt’s ref() makes unnecessary.
• The report layer is thin. A report model is a select, some joins, a where. The moment a GROUP BY creates a business number in the report layer, a building block is missing one layer down.

And the part I’m proudest of: the reconciliation anchors became dbt tests. The validated month-end total is now pinned by two independent derivations — the invoice-netting path and the account-balance path — that must agree forever. If either pipeline drifts, the tests don’t just fail; they tell us which side moved.

What we learned

Three things I’m taking with me:

1. Awkward downstream code is an upstream design signal. Every weird CTE, every hand-rolled noise filter, every reach into the raw layer was the data model telling us a building block was missing. Promote the logic; don’t multiply the workarounds.
2. The source system usually knows. We nearly shipped a hand-rolled version of a flag the system maintained all along. An hour of asking “what does this column actually do” beats a day of being clever.
3. Model count is the wrong fear. I worried we were breeding models. But each one answers a single sentence — “one row per posting”, “open AR per invoice per month-end”, “one row per line’s payment lifecycle” — and the real explosion risk is the duplicated logic you get by refusing to build them.

Sometimes the most valuable data-modeling lesson starts with an invoice that’s short a few thousand euros.