How do you combine measures from two fact tables in one report without inflating totals?
answer
- never join two fact tables to each other
- count the rows each side contributes per key
- aggregate first, join second
- the join surface is the conformed attributes
- inner join here loses whole categories
basics
~20 sQuery each fact table separately, aggregating each to the same conformed dimension attributes, then join the two result sets on those attributes. This is a drill-across. Joining the fact tables directly to each other multiplies rows and inflates every measure.
solid answer
~50 sNever join two fact tables to each other. If a product had 3 sales rows and 2 return rows in a month, a direct join on the shared keys produces 6 combined rows, so sales are counted twice and returns three times - a fan trap. The correct pattern is **drill-across**: run one aggregate query per fact table, each grouped by the *same* conformed dimension attributes, then join the two small result sets on those attributes. Use a full outer join so a month with returns and no sales, or the reverse, still appears, and coalesce the labels and the measures. This works only because the dimensions are conformed - the labels on both sides are drawn from the same domain, so lining them up is meaningful. If the fact tables sit at different grains, roll the finer one up to the common grain first.
code
sql · 10 lines-- 3 sales rows x 2 returns rows for one product/month = 6 rows
SELECT d.year_month,
SUM(s.sales_amount) AS sales, -- counted 2x
SUM(r.return_amount) AS returns -- counted 3x
FROM fact_sales s
JOIN fact_returns r
ON r.date_key = s.date_key
AND r.product_key = s.product_key
JOIN dim_date d ON d.date_key = s.date_key
GROUP BY d.year_month;go deeper
Remember the rule and the reason: two fact tables are never joined to each other, because each has many rows per dimension key and the join multiplies them.
Be able to write the pattern - one aggregate per fact table grouped by the same conformed attributes, then a full outer join of the summaries - and explain the fan-out arithmetic that makes the naive join wrong.
Show how you would spot this in production: a total that changed when a second fact table joined the report, an inflation factor that varies by row, and the differing-grain case where you must roll up before aligning.
Own the platform angle: if analysts keep hand-writing cross-process queries, the fan trap will recur, so the drill-across shape belongs in a shared, reviewed model that consumers query rather than in each person's SQL.
## Why the obvious query is wrong The instinct when asked for sales and returns side by side is to join the two fact tables on their shared dimension keys. It produces a number, and the number is wrong. Fact tables hold many rows per dimension key - that is what a fact table is. If in March a given product has 3 sales rows and 2 return rows, joining on `(date_key, product_key)` yields 3 x 2 = 6 rows. Every sale is now paired with every return, so `SUM(sales_amount)` counts each sale twice and `SUM(return_amount)` counts each return three times. This is the classic **fan trap**: a join fans out one side's rows by the cardinality of the other, and the aggregate silently multiplies. The inflation factor is not constant - it depends on how many rows each side happens to have for each key - so the totals cannot even be corrected after the fact by dividing. The two rescues people reach for both fail. `SELECT DISTINCT` removes only rows that are byte-identical, and two legitimately different sales of the same product on the same day are not identical, so distinct throws away real data while leaving other duplication in place. Dividing by a count is arithmetic guesswork that breaks as soon as one side has zero rows. ## The drill-across pattern The correct technique aggregates first and joins second: 1. Query fact table A alone, grouped by the conformed dimension attributes you want on the report. 2. Query fact table B alone, grouped by **exactly the same** attributes. 3. Join the two *result sets* on those attributes. Each fact table is now reduced to at most one row per label combination before any joining happens, so there is nothing left to fan out. Kimball calls this drill-across, and it is the payoff that conformed dimensions exist to deliver. ```sql WITH s AS ( SELECT d.year_month, p.category, SUM(f.sales_amount) AS sales FROM fact_sales f JOIN dim_date d ON d.date_key = f.date_key JOIN dim_product p ON p.product_key = f.product_key GROUP BY d.year_month, p.category ), r AS ( SELECT d.year_month, p.category, SUM(f.return_amount) AS returns FROM fact_returns f JOIN dim_date d ON d.date_key = f.date_key JOIN dim_product p ON p.product_key = f.product_key GROUP BY d.year_month, p.category ) SELECT COALESCE(s.year_month, r.year_month) AS year_month, COALESCE(s.category, r.category) AS category, COALESCE(s.sales, 0) AS sales, COALESCE(r.returns, 0) AS returns FROM s FULL OUTER JOIN r ON s.year_month = r.year_month AND s.category = r.category; ``` ## Why a full outer join, and why coalesce An inner join here is a data-loss bug. A category with returns but no sales in a month - a discontinued line, say - would vanish from the report entirely, and nobody would notice because the remaining rows look fine. A left join has the same problem in one direction. The full outer join keeps every label that appears on either side, and the `COALESCE` on the join columns rebuilds a single label column from the two nullable ones. Coalescing the measures to zero rather than leaving nulls matters too, because `sales - returns` is null if either operand is null. ## Where conformance enters Drill-across is only sound if the dimensions are conformed. Joining the two summaries on `category` assumes `Footwear` denotes the same set of products in both stars. If the returns mart classifies the same items as `Shoes`, the join produces one row with sales and no returns and another with returns and no sales - structurally valid, semantically nonsense. This is why the technique and the conformed dimension are taught together: the shared, identically-defined labels are the join surface. ## Differing grains If one fact is daily and the other monthly, you cannot align on a day attribute the monthly fact does not have. Aggregate the finer fact up to the coarser common grain - group the daily sales by `year_month` - and drill across on the attributes both sides genuinely carry. The same rule applies on any dimension: the join surface is the intersection of the conformed attributes available at both grains. ## When a union is the better tool If both fact tables share the same grain and you want the measures stacked rather than side by side, `UNION ALL` with a source tag and a single aggregation afterwards is simpler and equally correct. It does not fan out, because nothing is joined. Choose union when you want one measure column labelled by source; choose drill-across when you want separate measure columns you will subtract, divide or chart together.
- Why use a full outer join between the two aggregated result sets instead of an inner join?Because a label may exist on one side only - a month with returns but no sales, or a new category with sales and no returns yet. An inner join silently drops those rows and the report looks plausible while being incomplete. Full outer join keeps both sides, and you coalesce the label columns and default the missing measures to zero.
- When is a UNION ALL of the two fact tables an acceptable alternative to drill-across?When both facts sit at the same grain and you want the measures stacked in one column rather than side by side. Tag each row with its source, union, then aggregate once. Nothing is joined so nothing fans out. It stops working when the grains differ or when you need the two measures as separate columns to divide or subtract.
- What changes if one fact table is at day grain and the other at month grain?Roll the finer fact up to the coarser common grain before joining - group the daily fact by the month attribute - and drill across only on attributes both sides actually carry. You cannot align on a day attribute the monthly fact has no value for, so the join surface is the intersection of the conformed attributes present at both grains.
- Does adding SELECT DISTINCT to the direct fact-to-fact join fix the inflated totals?No. DISTINCT removes only fully identical rows, so two genuine sales of the same product on the same day collapse into one - destroying real data - while pairings that differ in any column survive and keep inflating the sum. It converts an over-count into an unpredictable mix of over- and under-counting.
saying these in an interview costs you the question
- Joins two fact tables directly on shared dimension keys
- Believes conformed dimensions make fact-to-fact joins safe
- Adds SELECT DISTINCT to fix inflated sums
- Uses an inner join and silently drops labels present on one side only
- Divides the total by a row count to undo the fan-out