A finance report double-counts revenue after a new fact table is added. What is the likely modelling error?

The likely error: a fan-out join between fact tables at different grains

The classic mechanism. You have an order_lines fact at line grain and a shipments fact at shipment grain. An order with three lines shipped in two shipments, joined through the shared order dimension, produces six rows — and summing line_revenue across them counts each line twice.

Nothing errors. The join is valid SQL, the numbers look plausible, and the discrepancy is found by someone in finance rather than by a test.

The underlying rule

Never join two fact tables directly, and never sum a measure across a join that changes its grain.

The correct pattern is to aggregate each fact to a common grain first and then join the aggregates — or to query them separately and combine the results, which is what UNION-based "drilling across" does.

What should have prevented it

Declared grain. Every fact table states, explicitly and in its documentation, what exactly one row represents: "one row per order line per order". If the grain is not written down, the modelling error is invisible.

Additivity classification per measure. Fully additive (revenue — sums across every dimension), semi-additive (account balance — sums across accounts, not across time), non-additive (a ratio or percentage — never sum, always recompute from components). Semi-additive and non-additive measures summed carelessly are the second most common cause of wrong numbers.

Conformed dimensions, so the two facts genuinely relate to the same customer and the same date and the comparison means something.

How to find it now

Compare the report's total against the single-fact query. Check the row count before and after each join — a join that increases row count while you are summing a measure is the fan-out. And look for COUNT(DISTINCT ...) used to paper over duplication, which is a strong signal that the grain is wrong rather than a legitimate technique.

What a strong answer adds

Proposing a test rather than a fix: a reconciliation check in the pipeline that compares total revenue in the warehouse against the source system on every load, alerting on divergence beyond a threshold. Grain errors are systematic and silent, and a reconciliation catches the whole class of them rather than this instance.