Malek Damiri

Check row counts before you trust a join

·

The most expensive bug in revenue reporting is a join that quietly duplicates rows. Nothing errors, the totals just get bigger. Here's a small version, and the two checks I run before trusting any join.

Three invoices become four

create table invoices as select * from (values
  (1, 'acme', 1000.00), (2, 'globex', 500.00), (3, 'initech', 750.00)
) t(invoice_id, account, amount);

create table account_owners as select * from (values
  ('acme',    'dana', date '2026-01-01'),
  ('acme',    'lee',  date '2026-08-01'),  -- territory change
  ('globex',  'sam',  date '2026-01-01'),
  ('initech', 'dana', date '2026-03-01')
) t(account, owner, effective_from);

select sum(i.amount) as total
from invoices as i
join account_owners as o on o.account = i.account;

Invoices total 2,250.00. The joined total is 3,250.00, because acme has two owner rows and its invoice now counts twice.

Check 1: row counts

select
  (select count(*) from invoices) as invoice_rows,
  (select count(*) from invoices as i join account_owners as o on o.account = i.account) as joined_rows;
invoice_rows   joined_rows
           3             4

If a join is supposed to add columns, not rows, these two numbers should match.

Check 2: duplicate keys on the "one" side

select account, count(*) as owners
from account_owners
group by account
having count(*) > 1;
account   owners
acme           2

The fix: decide which row wins

The table isn't wrong; it's a history. The query has to choose. Here, the current owner gets the revenue:

with current_owner as (
  select account, owner
  from account_owners
  qualify row_number() over (partition by account order by effective_from desc) = 1
)
select o.owner, sum(i.amount) as revenue
from invoices as i
join current_owner as o on o.account = i.account
group by o.owner
order by o.owner;
owner   revenue
dana     750.00
lee     1000.00
sam      500.00

Back to 2,250.00. Whether the old owner or the new one should get credit is a business rule, not a SQL question, so write the rule down next to the query.

If you use dbt, a unique test on the key of every table you join to turns check 2 into something that runs on every build.

Every query here ran in DuckDB 1.5.1 and returned the output shown. This note was written with AI assistance.