Cross-fact query shape

Grain: what one row means

Aggregate: each fact, same headers

Align: merge aggregates, not details

Fan-out: a join copies measures

Check: totals match single-fact queries

Aggregate first, then align the row headers

Sales facts and inventory facts should not be joined and then summed. A row on each side usually represents a different event, so a detail join copies one measure once for every row on the other side. Summing after that join treats the copies as additional business events. The sound shape is to aggregate each fact to the same dimension attributes, then align those two result sets on the row headers.

That separate-query-then-align pattern is often called drill-across. Some tools call the same step stitch or a multipass query. The join keys are the aggregated headers, not the detail keys of the two fact tables. Whether those attributes were modeled so they can be compared is a prior decision. This note is only about the query shape after that choice, and about the fan-out that appears when the shape is wrong.

Fan-out is a one-to-many join before aggregation

The failing shape joins sales and inventory on product and date at detail grain, then sums amount and quantity together. If either side has more than one row for the key, the join is not one-to-one. The sum runs on the multiplied rows, so both measures can be wrong, while the query still returns a single number.

The working shape is two aggregations followed by an alignment. Sales are grouped to the requested headers, inventory is aggregated to those same headers, and the results are combined by a sort-merge. In SQL that is a full outer join of the aggregated sets, not an inner join of the fact tables. After the merge, the original fact rows are not summed again.

A made-up example shows which side is copied

The figures in this section are an example, not operating results. On one day, product P1 has sales rows of 10 and 30 and one inventory snapshot of 5. Product P2 has inventory 7 and no sales. An inner join on product, followed by a sum, turns P1 inventory into 10 and drops P2. Sales remain 40 only because the inventory side had a single row and did not copy the sales rows.

Product P3 has one sales row of 40 and warehouse quantities 3 and 2. Joining before the sum turns sales into 80 while quantity stays 5. Aggregating each fact to product-day and then full-outer-joining keeps P1 at sales 40 and inventory 5, P2 at null sales and inventory 7, and P3 at sales 40 and inventory 5. The sales total is 80 and the inventory total is 17, matching each fact aggregated alone.

Both facts must support the common grain

Before the separate aggregations, declare the grain of each fact and the row headers of this request. A sales line can be one product on one sale. An inventory row can be one warehouse, product, and date. If the headers are product plus date, inventory is rolled across warehouses under an additive rule, then aligned with sales. Declaring the grain means stating what measurement event one row represents.

If the requested grain is finer than one of the facts, the join cannot split that measure. Sales that have no warehouse cannot be aligned to inventory at warehouse grain unless an approved allocation already exists. Without that rule, reject the grain or leave sales at the product grain they support. Dividing a joined result by the row count is not an allocation policy.

Outer joins, nulls, and semi-additivity are separate rules

An inner join drops headers that exist on only one side, so the other total shrinks. Alignment should keep one-sided members. Null means that side has no fact. Replacing null with zero is a display policy and must be stated on its own. A silent zero turns “no sales” into “sales of zero.”

On-hand quantity is usually not additive across dates. Drill-across prevents fan-out; it does not make a semi-additive measure additive. Inventory for a period should be the ending or stated-day snapshot, then aligned with sales for that period, not the sum of daily balances. Sales may still be additive from product to category. Both rules apply at once.

Two implementations, one order of operations

Path one writes two aggregate queries and sort-merges them on the same headers. Keys, attribute grain, and empty members must match, and each total is checked against the fact queried alone. The SQL is easy to read, but a later edit can push the join below the aggregation to scan less data. Review should ask whether the join inputs are already aggregated.

Path two asks a semantic layer for a staged plan: each metric aggregates on its own fact and grain, then an align node receives only those results. A dbt semantic model describes entities, dimensions, and measures separately, and metrics reference measures. The plan should show aggregation before any fact-to-fact join. If an optimizer fuses the stages into a detail join, the failure is the same as on path one. Reuse is useful only when the plan nodes are checked, not when a single displayed number looks plausible.

Counterexamples and acceptance checks

Counterexample one aligns on product name instead of a stable key, so identical names collapse or split. Counterexample two uses an inner join and drops inventory days that have no sales. Counterexample three sums daily inventory after a correct alignment and produces a period balance that cannot be explained. Counterexample four treats a distinct row count as success while the amount has already doubled.

Acceptance requires an independent aggregate for each fact, placed before alignment; each aligned measure equal to that fact aggregated alone; one-sided members retained under a stated null policy; and a hard failure when either fact cannot support the requested grain. Check P1, P2, P3, and the totals in the example above, key by key.

BuildTable.ai boundary

Start with one sales fact and one inventory snapshot. Write both grains and the shared headers, run a multi-row fan-out fixture, and inspect whether the real plan aggregates before it aligns. Add a third fact only after that check. Watch the row-count ratio across the align step and the difference of each measure from its single-fact total.

BuildTable.ai can be considered as an entry point for semantic modeling. This article does not show that the current release implements cross-fact aggregation before alignment, filter precedence, or semantic version pins for saved queries. Compare the actual semantic model, the generated query plan, and the acceptance fixtures before relying on that shape.

Why should sales and inventory facts be aggregated before they are combined?