Where the extra rows come from
A single order record joined to two matching line records produces two result rows. Neither source table needs duplicate data for this to happen. The multiplication comes from the relationship between them: one left-side row matches more than one right-side row.
That distinction matters because a LEFT JOIN guarantees that every left-side row remains represented, not that it appears exactly once. If an analyst joins orders to order items, then sums an order-level total after the join, each order total repeats once per item. A dashboard can look internally consistent while its revenue, customer count, or conversion denominator is wrong.
This effect is called fan-out. The fix is rarely a reflexive DISTINCT: establish the grain of each input, decide what grain the output should have, and make the join preserve it.
Table grain determines join cardinality
Grain is the real-world entity represented by one row. An orders table may have one row per order. An order_items table may have one row per order line. Joining them produces a result at order-item grain, because each order is repeated for every associated item.
For a left-side table with one row per order, the row-count rule is:
rows after LEFT JOIN = sum of max(1, matching right-side rows) for each left row
The max(1, ...) accounts for unmatched left rows, which remain in a left join with NULL values on the right. An order with two items contributes two rows. An order with no items contributes one. The engine is doing exactly what the join asked for.
The dangerous case is a join that looks one-to-one by column name but is one-to-many in the data. A customer_id in both tables says nothing about uniqueness. Before joining, ask which side is allowed to contain repeated values of the join key and whether the downstream query can safely operate at the resulting grain.
A hypothetical two-table join
The following hypothetical data has two orders and three order items. Order 101 has two lines, while order 102 has one.
WITH orders (order_id, customer, order_total) AS (
VALUES
(101, 'Ava', 100.00),
(102, 'Ben', 80.00)
),
order_items (order_id, item_id, line_amount) AS (
VALUES
(101, 1, 60.00),
(101, 2, 40.00),
(102, 1, 80.00)
)
SELECT
o.order_id,
o.customer,
o.order_total,
oi.item_id,
oi.line_amount
FROM orders AS o
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id;
-- Hypothetical result
-- 101 | Ava | 100.00 | 1 | 60.00
-- 101 | Ava | 100.00 | 2 | 40.00
-- 102 | Ben | 80.00 | 1 | 80.00
The result has three rows because the join output is one row per item, not one row per order. This is correct if the report needs item-level detail. It is incorrect if a downstream model labels the output as one row per order and uses order_total as an additive order-level measure.
A common source of confusion is that the left table did not lose rows. It gained repeated representations of a parent entity. A LEFT JOIN can do this even when every foreign key is valid and every parent key is unique.
Why SUM and COUNT overstate results
In the hypothetical result, SUM(order_total) returns USD 280.00: USD 100.00 + USD 100.00 + USD 80.00. The true sum at order grain is USD 180.00. The extra USD 100.00 is order 101's total counted a second time, carried along by its second line item.
COUNT(*) and COUNT(order_id) also return three, while there are only two orders. COUNT(DISTINCT order_id) returns two and works as a diagnostic, but it is not a repair. It cannot restore a distorted SUM(order_total), and used out of habit it hides an unintended relationship. SUM(DISTINCT order_total) fails too: it adds distinct values, not distinct orders, so two different orders that both total USD 80.00 would be counted once.
Aggregation is valid only for measures whose grain matches the result. In this example, SUM(line_amount) is valid at order-item grain because each line amount occurs once. A later join to shipments, promotions, or payments can multiply those lines again, so measure safety has to be rechecked after every one-to-many join in the chain.
Detect fan-out before trusting metrics
Start with a row-count comparison, then inspect the number of distinct left keys. A larger output count is not automatically a problem. It becomes a problem when the model or metric claims to remain at the left table's grain.
WITH joined_orders AS (
SELECT
o.order_id,
oi.item_id
FROM orders AS o
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id
)
SELECT
(SELECT COUNT(*) FROM orders) AS left_rows,
COUNT(*) AS joined_rows,
COUNT(DISTINCT order_id) AS distinct_left_keys,
COUNT(*) - COUNT(DISTINCT order_id) AS repeated_left_rows
FROM joined_orders;
For the hypothetical data, left_rows is two, joined_rows is three, and distinct_left_keys is two. That gap tells you that at least one order has expanded. To locate it, group by the left key and count a non-null right-side primary key. Avoid counting a nullable descriptive column, since a genuine match with a null value can look like no match.
SELECT
o.order_id,
COUNT(oi.item_id) AS matching_item_rows
FROM orders AS o
LEFT JOIN order_items AS oi
ON o.order_id = oi.order_id
GROUP BY o.order_id
HAVING COUNT(oi.item_id) > 1;
Check the join in the production filter context. Date windows, status filters, late-arriving records, and joins to history tables can change cardinality. A query may appear safe in a development sample yet fan out after a backfill introduces a second effective-dated record for the same key.
Aggregate, rank, or use EXISTS deliberately
Aggregate before the join when the output needs one row per parent but the right table contributes child-level measures. The aggregation reduces the child table to the intended parent key before it can repeat parent columns.
WITH item_rollup AS (
SELECT
order_id,
COUNT(*) AS item_count,
SUM(line_amount) AS item_revenue
FROM order_items
GROUP BY order_id
)
SELECT
o.order_id,
o.customer,
o.order_total,
COALESCE(ir.item_count, 0) AS item_count,
COALESCE(ir.item_revenue, 0.00) AS item_revenue
FROM orders AS o
LEFT JOIN item_rollup AS ir
ON o.order_id = ir.order_id;
-- Keep one right-side record only when a business rule selects it.
WITH ranked_status AS (
SELECT
customer_id,
status,
updated_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY updated_at DESC
) AS rn
FROM customer_status_history
)
SELECT
c.customer_id,
rs.status
FROM customers AS c
LEFT JOIN ranked_status AS rs
ON c.customer_id = rs.customer_id
AND rs.rn = 1;
-- Filter for the existence of children without returning child rows.
SELECT
o.order_id,
o.customer,
o.order_total
FROM orders AS o
WHERE EXISTS (
SELECT 1
FROM order_items AS oi
WHERE oi.order_id = o.order_id
);
ROW_NUMBER() is appropriate for a history table only when the business definition is clear, such as latest status by updated_at. It should not be used to hide unexplained duplicates. If two records claim to be the current customer status, ranking selects one but leaves a data-quality failure unresolved. Add a tie-breaker to ORDER BY, such as a record ID, when two rows can share the same updated_at; otherwise the chosen row can change between runs. Keep rs.rn = 1 in the ON clause: moved to WHERE, it drops customers without any status history and quietly turns the left join into an inner join.
Use EXISTS for a semi-join: the question is whether at least one related row exists, not which related row to display. Each order is returned at most once, however many items match, so fan-out cannot occur. WHERE EXISTS also removes orders without items, though. If every order must stay in the output, move the check into the select list as CASE WHEN EXISTS (...) THEN 1 ELSE 0 END AS has_items. Both forms are cleaner than joining and then applying DISTINCT to recover the left-side entity.
Make grain a tested data contract
A model description should state its grain in plain language: one row per order, one row per customer-day, or one row per customer and current status. That declaration gives reviewers a standard for judging every future join. Without it, a model can shift from customer grain to customer-event grain with no syntax error and no obvious visual clue.
In dbt, use unique and not_null tests on keys that are meant to identify one row. Use a relationships test to verify that child keys point to existing parent keys. Referential integrity is useful, but it does not prove that a child table has one row per parent. A right-side key must also be unique if a join is expected to be one-to-one or many-to-one from the left.
version: 2
models:
- name: orders
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: order_items
columns:
- name: order_id
data_tests:
- not_null
- relationships:
to: ref('orders')
field: order_id
- name: customer_current_status
columns:
- name: customer_id
data_tests:
- unique
- not_null
The data_tests: key is the current name since dbt 1.8; older projects use tests:, which still works. For high-value reporting models, add a custom test for expected output grain. A test that groups by the declared key and fails when COUNT(*) > 1 catches a fan-out close to where it was introduced. For a compound grain such as customer-day, dbt_utils.unique_combination_of_columns does the same job without custom SQL. Also test the row count or distinct key count across important incremental runs, because a key can remain unique while the meaning of a record changes.
A join defect can contaminate experiment analysis before it reaches statistical review. Check data grain before diagnosing an apparent experiment allocation anomaly, and before translating A/B lift into P&L. The same discipline applies to feature datasets: feature parity checks between training and serving can expose a join that duplicated training records.
Check one join before publishing numbers
Choose the model behind your next revenue report or experiment readout, for example one measuring delayed retention effects of an onboarding change. Write down its declared grain, then run the pre-join and post-join row-count query on its most consequential LEFT JOIN. If the output has more rows than distinct left keys, identify whether that expansion is intentional. Aggregate the child table, select one governed record, or replace the join with EXISTS before publishing a metric that treats repeated parent rows as independent facts.