Why did the AI query find twice as many customers?
Fieldgate builds a dealer service dashboard. An AI-generated query joins orders to customer records by customer_id alone.
The question
You must explain a mismatch before the customer sees the dashboard. Customer identifiers are local to each dealer.
| Dealer | Customer ID | Value (£) | |
|---|---|---|---|
| Order A | North | 17 | 100 |
| Order B | South | 17 | 200 |
Authored data. Each order belongs to one dealer.
| Dealer | Customer ID | |
|---|---|---|
| Customer A | North | 17 |
| Customer B | South | 17 |
Each dealer may see only its own records.
“Show the bad join’s rows and total. Repair the identity and test dealer access.”
Not saved by this site. Your browser may restore text when you navigate back.
A strong answer, step by step
Open each step in turn. This is one strong answer, not the only one.
Step 1: Trace the join
What would you do here? Open the move you’d make.
Trust the generated total
Tempting, but it misses something. The same local identifier matches two customer records and duplicates each order.
Trace each order’s matches
The strong move. A trace shows the duplicate amplification.
See how a strong candidate says it
“Each order matches both customer rows when the join uses 17 alone. The result has four rows and sums to £600 rather than the two orders’ £300. I would inspect the grain before changing the chart.”
Why it works: A trace shows the duplicate amplification.
Step 2: Repair the identity
What would you do here? Open the move you’d make.
Add DISTINCT to every output
Tempting, but it misses something. DISTINCT can conceal the wrong grain and can remove legitimate equal-valued orders.
Join on the validated composite identity
The strong move. DISTINCT can hide duplication or discard legitimate equal-valued orders.
See how a strong candidate says it
“Use the validated composite identity dealer_id plus customer_id. Confirm the business key with the data owner and enforce customer uniqueness at that grain. Do not merge people because their local identifiers match.”
Why it works: DISTINCT can hide duplication or discard legitimate equal-valued orders.
Step 3: Preserve access
What would you do here? Open the move you’d make.
Fix the sum and ignore access
Tempting, but it misses something. Correct totals do not prevent a North user from seeing South’s restricted data.
Check row permissions as well as totals
The strong move. A numerically right answer can still disclose another dealer’s data.
See how a strong candidate says it
“The corrected join must still run within the viewer’s dealer scope. I would test North and South separately and test that a North user cannot retrieve South records.”
Why it works: A numerically right answer can still disclose another dealer’s data.
Step 4: Show the evidence
What would you do here? Open the move you’d make.
Replace the dashboard without a repro
Tempting, but it misses something. Without the small failing fixture, the customer cannot check what was actually repaired.
Show the failing and repaired fixture
The strong move. The customer can check the diagnosis and its boundary.
See how a strong candidate says it
“I would retain the two-order fixture: four rows and £600 before, two rows and £300 after. Then test orphaned and repeated customer keys. I would explain the defect before demoing the repaired dashboard.”
Why it works: The customer can check the diagnosis and its boundary.
The answer, in short
- Decision
- Repair the data grain and verify access before the demo.
- Because
- The original key produces a many-to-many match.
- What would change it
- A verified globally unique customer identifier could support a different join.
Common traps
- Treating a local identifier as globally unique.
- Using DISTINCT as a substitute for a valid join key.
Check your answer
- Did you show four joined rows and the incorrect £600 total?
- Did you validate dealer plus customer as the identity?
- Did you test that North cannot retrieve South records?
- Did the repaired fixture retain two orders totaling £300?
Last reviewed Nov 10, 2026