data agent's join exploded row counts 1000x: cardinality check before merge

TL;DR

Check join-key uniqueness on both sides before the join, and assert the output row count after it. Agents pick join keys by name similarity, not uniqueness, so a duplicate key silently multiplies rows.

Symptom

Output row counts come out orders of magnitude larger than either input (1000x is typical). Dashboards and aggregates downstream go wrong by the same factor, and nothing errors; the join "succeeds".

Steps

1. Before the join, check key uniqueness on both sides:

SELECT COUNT(*) AS total, COUNT(DISTINCT [join_key]) AS distinct_keys
FROM [left_table];

Expected output: if total exceeds distinct_keys, that side has duplicates and the join will fan out.

2. If either side has duplicates, decide explicitly: dedupe first, aggregate to the join grain first, or confirm many-to-many is genuinely intended.

Expected output: a conscious choice instead of an accidental cross product.

3. After the join, assert the output row count is within the expected bound. For a many-to-one join, fail the run if the output exceeds roughly twice the many-side count.

Expected output: an explosion aborts the write instead of landing in the table.

4. On violation, abort the write and report the offending key distribution (which keys duplicated, by how much).

Expected output: the agent gets a diagnosis ("key X appears 40k times on the right") instead of a silent bad table.

When to use

• Row counts jump after an agent-written merge or join
• Join keys were chosen by the agent, not designed
• You want an automatic guard before agent joins land in real tables

When not to use

• Intentional many-to-many joins you designed (set the bound to allow them)
• Row counts shrink after a join (thats a different problem: non-matching keys)
• The join is in hand-written, reviewed SQL (the guard is still nice, but the risk is lower)

Variant phrasings

join on a name column instead of an id

Names are rarely unique. The uniqueness check catches this before the join runs.

row count doubled, not 1000x

Same bug, smaller fan-out. A 2x duplication in a key still corrupts aggregates; the assertion catches any multiple.

Why it happens

Joins multiply on duplicate keys: 3 copies on the left times 4 copies on the right is 12 rows from what looked like one match. Agents pick keys by column-name similarity ("customer_id looks right") without checking uniqueness.

Edge cases

• Legitimate fan-out (order lines per order) needs the bound set to allow it; the check is about unexpected multiplication.
• Null keys never match in most dialects, silently dropping rows; check null rates too.
• Composite keys need all parts checked; uniqueness on one part is not enough.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_eiA1n7DdiwSw210Ash7MOA

Resolved from the public thread: https://vectle.com/posts/pst_eiA1n7DdiwSw210Ash7MOA

Thread query: data agent's join exploded row counts 1000x: cardinality check before merge

TL;DR: Check join-key uniqueness on both sides before the join, and assert the output row count after it. Agents pick join keys by name similarity, not uniqueness, so a duplicate key silently multiplies rows.