The most dangerous error in an amount query is often a well-formatted, plausible number that counts the same fact repeatedly. This article addresses join fanout in AI-assisted analytics: establish the fact to which each measure belongs, constrain its join path, then test the result against independent totals and counterexamples. All orders and numbers are constructed. No database experiment was performed, and none of the examples represents IDENIFE customer data.
State what a row represents before deciding which tables can join
Fact grain means the event or object represented by one row. An orders table may contain one row per order, an items table one row per order line, and a payments table one row per successful payment. Sharing an order identifier does not make it safe to join them and sum every amount. PostgreSQL 18 documents that an inner join produces a result for every pair of left and right rows satisfying its condition.[1] This is the mechanism behind join fanout.
Give each metric a minimum calculation contract: business meaning, fact key, amount source, status conditions, time attribution, currency, supported dimensions and the uniqueness that must survive its joins. Order value and cash receipts may both be called 'sales,' but one belongs to an order and the other to payment events. A catalog containing only column names gives the query generator insufficient information to detect repeated contributions.
This is one part of reliable enterprise Text-to-SQL. The broader approach covers intent, permissions, retrieval and execution; this article narrows join paths into verifiable calculation constraints. A model can help identify the requested metric, but 'these tables share a field' is not proof that their business grains are compatible.
Two items times three payments produce six rows, not five facts
Suppose order O201 has an order value of CNY 300, two item lines worth CNY 120 and CNY 180, and three successful payments of CNY 100 each. Joining both detail sets to the order using only its identifier creates six combinations. Each item appears three times, each payment twice, and the order six times.
Summing order value then gives CNY 1,800; summing item amounts gives CNY 900; summing payments gives CNY 600. In this example, the correct total under each original definition is CNY 300. Equality across the three measures is an illustrative assumption: there are no tax or fee differences, refunds or unpaid balances. It is not a universal reconciliation identity for real systems.
Record item and payment counts per order before the join and inspect intermediate row counts. With this pair of inner joins, m matching items and n matching payments produce m×n rows for an order. Left joins do not eliminate multiplication where matches exist; they change how unmatched records are retained. Inspecting only the final number of output rows is inadequate: grouping by customer may return a single row while hiding inflation inside the sum.
Why DISTINCT on the amount is not a repair
A common patch deduplicates the monetary value inside the aggregate. PostgreSQL's aggregate-expression documentation specifies that DISTINCT operates on expression values, not business-event identity.[2] Add another order, O202, also worth CNY 300. The two orders should contribute CNY 600, whereas summing distinct amounts retains only CNY 300.
The same patch collapses O201's three different CNY 100 payments into a single value, producing CNY 100. Conversely, deduplicating complete result rows may accomplish nothing because the item and payment identifier combinations are already different. The required rule is 'each valid fact contributes once,' not 'each numeric value appears once.'
If duplicate keys carry conflicting amounts, arbitrarily choosing one row per key is not a valid repair either. Investigate versions, reversals, duplicate ingestion or the business-key definition. Establish the approved latest-state or event-accounting rule and retain evidence of the conflict. Taking a maximum or arbitrary value can disguise a data-governance problem as query optimization.
Pre-aggregation depends on the target grain, not a universal template
For successful-payment totals by order or customer, first aggregate payments by order identifier into at most one row per order. If item totals are genuinely needed, aggregate them independently to order grain. Join those summaries to unique order records, then aggregate by customer. Check actual uniqueness at every stage rather than trusting a declared one-to-many relationship in a semantic model.
Apply the metric's status, time and authorization scope to the relevant facts and aggregate to the approved grain. Place dimension filters where they preserve the intended meaning; they cannot all be moved mechanically before or after aggregation. Retaining counts of payment events and non-null amounts helps distinguish no payment, missing amounts and genuine zero amounts. Whether to replace null with zero is a business-definition decision, not a universal transformation.
Aggregation to orders does not support arbitrary product analysis. If a user asks for cash receipts attributable to product A while a payment covers multiple products, the calculation needs payment-to-item attribution or an explicit allocation policy. Without that information, order-level cash receipts cannot be copied onto each product. Return the order-level result with its limitation, or obtain an agreed definition.
A proportional allocation based on item value must define discounts, shipping, refunds, negative values, zero denominators and rounding residuals. Weights for each payment must sum to one. Such a method reports 'receipts allocated under a rule,' not individually recorded product-level cash collection. Pre-aggregation can reduce intermediate rows while discarding detail; supporting a new breakdown may require another controlled calculation path.
Details used only for filtering should not multiply the amount path
'What are total receipts for orders containing product A?' differs from 'How much of the receipts belongs to product A?' The first uses an item condition to select orders and then counts those orders' complete receipts. The second needs attribution or allocation. Confusing them yields a wrong answer even when nothing is double counted.
For the first question, use an existence condition on the order population and then join the order-grain payment summary. PostgreSQL defines EXISTS as checking whether a subquery returns any row. This filtering pattern does not create additional copies of an outer record merely because several details match.[3] The outer order population must still satisfy its own uniqueness requirement.
Historical dimensions are another hidden source of fanout. A customer identifier can have multiple effective-dated versions. If the metric uses the region at transaction time, select one valid version using the business key and event time, and check for overlapping validity intervals. Joining only on customer identifier can assign the same order to several versions; selecting only the current version can silently change historical attribution instead.
Use five counterexample groups, not just one successful query
First, reconcile independently. Obtain a control total directly from payment facts under the same status, currency, time and authorization scope. Compare it with the order-level aggregate and final output. Require conservation only when output groups are complete and mutually exclusive and filters describe the same population. Totals across overlapping customer groups need not sum to the overall population total.
Second, perturb unrelated detail. Without changing order or payment facts, or the selected order population, add an item line that does not participate in the metric. Order-level cash receipts should remain unchanged. Then add a legitimate payment: the result should change by its eligible amount. The first test checks stability under irrelevant detail; the second prevents an attempted repair from deduplicating valid facts away.
Third, add two different orders with equal amounts and separate payment identifiers carrying equal values to expose amount-based DISTINCT. Fourth, include orders with no items or no payments to test inner versus outer joins and the agreed treatment of null and zero. Fifth, introduce overlapping historical dimension versions or missing allocation weights. The system should expose ambiguity or block a certified answer rather than silently absorb the anomaly.
Record the amount difference, conflicting-key count, unmatched-fact count, maximum join multiplicity per fact and allocation-weight deviation. Define amount difference as query output minus the independent control total; state the grain and scope of the other measures too. An illustrative test may require exact agreement for integer amounts in cents. Where exchange rates, precision conversions or allocation rounding apply, use an approved business tolerance and retain the explanation for any residual rather than inventing a universal percentage.
Enforce approved calculation paths while retaining tool boundaries
For established metrics, keep an approved join graph, fact grain and supported dimension set, and let a controlled query builder implement the calculation path. A new many-to-many relationship or undefined allocation should enter human confirmation or an explicitly uncertified exploration path. Counterexamples retained as regression checks for metric-version changes are more testable than repeatedly instructing a model not to double count.
Semantic tools can perform part of this work. Looker's symmetric aggregates documentation describes using a fact's unique primary key to avoid duplicated aggregation after joins, relying on correct keys and relationship configuration.[4] That is not equivalent to applying DISTINCT to amounts, and it cannot invent missing business-allocation rules. Evaluate the actual SQL dialect, configuration and operating cost against the same counterexamples before adopting a tool.
Controlled paths also have costs: additional aggregation, uniqueness checks and materialized-data maintenance consume resources, while pre-aggregated results need refresh handling and data-cutoff information. Infrequent exploration can compute on demand; frequent, stable metrics may justify maintained summaries with consistent refreshes. Deliver the metric definition, data cutoff, filter scope and material attribution limits alongside the answer so that its numerical correctness can be reviewed.