Fix Mixed-Case Join Keys — Solution & ExplanationProblems / Fix Mixed-Case Join Keys / EditorialApproach

Normalize the join key on both sides: lower(trim(account_name)) = lower(trim(company)). Lowercasing alone is not enough — the invoices carry stray spaces (' ACME CORP ') and even the accounts table has one (' Globex Inc').

Use an INNER JOIN so both failure modes fall out naturally: the invoice for 'Vandelay Industries' matches no account (dropped), and 'Stark Industries' has no invoices (dropped).

GROUP BY account_id, account_name and ROUND(SUM(amount), 2).

Order by account_id.

Key InsightDirty join keys fail silently: an equality join on unnormalized strings does not error, it just returns fewer rows, and downstream totals come out too small. The standard defense is to normalize keys at the join boundary — lower(trim(...)) is the minimum viable normalization, applied symmetrically to both tables, while still displaying the original account_name from the trusted side.Hints

Case differences AND stray whitespace both break the raw join — you need lower() and trim() together, and on BOTH sides (one account name has a leading space).

An INNER JOIN does the exclusion work for free: the unmatched invoice ('Vandelay Industries') and the account with zero invoices ('Stark Industries') simply produce no rows.

Group by account_id and account_name (the original casing from accounts) and SUM the amounts. In PySpark the join condition is F.lower(F.trim(accounts['account_name'])) == F.lower(F.trim(invoices['company'])).

Solve Fix Mixed-Case Join Keys yourself →