The query runs. The database returns a number. The assistant explains it confidently. Yet the total changes when a customer opens another support ticket, even though no order has changed.
This is join fanout: a fact appears more than once in an intermediate result and contributes repeatedly to an aggregate. Read-only access cannot prevent it. Neither can a syntax check. The practical decision is whether an AI-generated query has preserved the meaning of the records it is counting.
A useful review starts with three questions: what does one row represent, how many matches may each key have, and which records must survive? The documentation below was checked on 20 September 2026. The controls are AIEngine's engineering analysis; the small example is synthetic, not a customer incident or a benchmark of the named products.
Write the grain before writing the join
A table's grain is the meaning of one row: one order, one order line, one customer, or one customer's status during a particular validity interval. Similar column names do not make these grains interchangeable.
Ask the assistant to state the input and output grains alongside its proposed SQL. “Sales by customer” is insufficient. Does sales mean gross order value, shipped lines, recognised revenue or payments received? Which dates, statuses and currencies belong in the calculation?
| Intended operation | Required relationship or rule | Evidence to request |
|---|---|---|
| Add a customer attribute to each order | At most one eligible customer row per order | Uniqueness of the complete lookup key and unmatched-order count |
| Compare order totals with ticket counts | Each input reduced to the same customer grain | Separate fact totals before joining |
| Select orders from customers with tickets | Existence of a qualifying ticket, not one output per ticket | An existence predicate and order-key preservation |
| Allocate one order across several owners | Explicit many-to-many allocation policy | Allocation weights and per-order reconciliation |
Treat keys as scoped identifiers. Customer 42 in one tenant need not be customer 42 in another. A join on the numeric identifier alone can produce both duplication and incorrect attribution. Include the required tenant or source scope; do not merely deduplicate the resulting rows.
Getting the identifier right is also different from deciding whether two records represent the same real entity. Our entity-resolution guide covers that prior decision. Even a correctly resolved identity can have many legitimate transactions.
Inspect match counts, not just the final row count
PostgreSQL's table-expression documentation explains that an inner join produces a row for each matching pair. A left join additionally retains unmatched left-side records with nulls for the right-side fields. It does not promise to retain exactly one output row per left-side record.
For an enrichment intended to preserve orders, inspect matches per order key before aggregation. Separate zero matches, exactly one match and more than one match. A maximum greater than one violates an asserted many-to-one lookup, even when the overall total looks plausible.
Do this against the actual filtered join input. A base table's uniqueness claim is not enough if a preceding transformation expands it. Conversely, a history table may legitimately contain several versions per customer; the query must choose the version appropriate to the question.
Our temporal-correctness guide explains why a current record is not necessarily the correct historical record. Add a test that the selected validity conditions yield at most one eligible version. Ordering versions and keeping an arbitrary first row can hide overlapping intervals.
Total row count alone is a weak safeguard. One missing order and one duplicated order can cancel numerically. Compare key coverage and per-key multiplicity, then reconcile measures within the intended population. For deliberately expanding joins, record the expansion rule instead of enforcing a universal no-growth limit.
Three orders expose two different wrong answers
Consider three orders in a single currency and reporting period. O1 and O2 belong to customer C1 and are worth 100 each. O3 belongs to C2 and is worth 60. C1 has two support tickets, T1 and T2; C2 has none.
The required order total is 260, regardless of ticket activity. Joining orders to tickets on customer identifier creates two copies of each C1 order. The following results can all be produced by executable SQL:
| Calculation | Joined rows | Order total | Interpretation |
|---|---|---|---|
| Sum the original three orders | 3 | 260 | Independent reference |
| Left join orders to tickets, then sum amounts | 5 | 460 | C1 orders counted twice |
| Inner join orders to tickets, then sum amounts | 4 | 400 | Duplicates C1 orders and drops C2 |
| Left join, then sum distinct amounts | 5 | 160 | Collapses two genuine orders sharing the amount 100 |
The last mistake is particularly tempting. SUM(DISTINCT amount) deduplicates values, not business facts. Equal prices are not evidence of duplicate orders. The correct identity for counting each order once is its complete order key, not its amount.
Similarly, COUNT(DISTINCT order_id) can correctly report three while a neighbouring sum remains 460. One repaired measure does not repair every aggregate in the query. A final SELECT DISTINCT over already aggregated output cannot recover contributions that were multiplied earlier.
Make the expected answer independently reviewable. A tiny dataset with repeated amounts, multiple matches and an unmatched customer reveals more about this failure than a large sample whose true total nobody knows.
Put independent facts on a common grain
If the question asks for order value and support-ticket count per customer, aggregate the two fact sets separately before combining them. Apply each measure's eligibility rules in its own input, then join the resulting customer-level summaries.
For the synthetic example, with non-null customer identifiers:
WITH order_totals AS (
SELECT customer_id, SUM(amount) AS order_total
FROM orders
GROUP BY customer_id
),
ticket_totals AS (
SELECT customer_id, COUNT(*) AS ticket_count
FROM tickets
GROUP BY customer_id
)
SELECT o.customer_id, o.order_total,
COALESCE(t.ticket_count, 0) AS ticket_count
FROM order_totals AS o
LEFT JOIN ticket_totals AS t
ON o.customer_id = t.customer_id;C1 now has order total 200 and ticket count two; C2 has order total 60 and ticket count zero. Adding those order totals gives 260. Each summary has one row per customer, so the join cannot multiply one summary by several rows from the other.
This query deliberately defines the output population as customers with orders. If the report must include customers without orders, start from the eligible customer set and attach both summaries. That changes coverage and requires an explicit treatment of missing measures.
Pre-aggregation also loses detail. It cannot answer a later question about individual tickets and individual order lines without revisiting the grain. When the requirement is merely “orders from customers who have a qualifying ticket”, use an existence test rather than attaching every ticket.
For a genuine many-to-many allocation, first agree how much of each fact belongs to each destination. Require weights to reconcile for each source fact, or explicitly label overlapping membership. SQL cannot decide whether an order shared by two teams should count half to each or fully to both.
Keep zero, missing and excluded records separate
An unmatched join row is not itself a ticket. PostgreSQL's aggregate-function documentation distinguishes counting input rows from counting non-null expression values. For C2's single unmatched left-join row, COUNT(*) is one while COUNT(ticket_id) is zero. The latter still overcounts C1's tickets when several orders repeat them.
The same documentation notes that a sum over no input rows returns null rather than zero. Converting missing results to zero is a meaning decision: “no eligible activity” may justify zero; “the source did not arrive” does not. Preserve source-completeness evidence separately from the numeric result.
Filter placement can change coverage too. The PostgreSQL table-expression guide shows why a right-side restriction in WHERE differs from one in a left join's ON condition. A later requirement that the ticket status equal “open” rejects the unmatched row. Decide whether customers without open tickets should disappear or remain with a zero count.
Write that decision in the test expectation. Avoid approving a query merely because it contains the reassuring words LEFT JOIN.
Use library checks without overstating their guarantee
The pandas merge reference provides relationship validation: many_to_one checks uniqueness on the right, while one_to_one checks both sides. Its many_to_many option permits the relationship without performing uniqueness checks. Selecting it is not evidence that a merge is safe.
Pandas also warns that null merge keys can match each other, unlike usual SQL behaviour. Its merge indicator can expose unmatched records, but does not establish the intended business population. When translating generated analysis between SQL and a dataframe, test null-key behaviour explicitly rather than assuming equivalent syntax means equivalent results.
In a maintained data pipeline, combine complementary tests. The dbt data-test reference describes uniqueness, non-null and relationship checks. A relationship test checks that child values have a corresponding parent and excludes nulls; use a separate non-null test when those must fail. Existence of a parent is not proof of exactly one eligible parent. Test the parent key's uniqueness as well, including composite scope where necessary.
A governed semantic layer can handle some aggregate fanout mechanically. Google's Looker symmetric-aggregates guide explains its key-based treatment and its dependence on correct unique primary keys and join relationships. It also notes computation costs. That is a reason to maintain those definitions, not a reason to assume arbitrary generated SQL receives the same protection.
None of these controls decides whether the requested metric is meaningful. A perfectly unique customer lookup can still use the wrong reporting period or combine incompatible currencies.
Challenge the query with changes that should matter
Build a small adversarial fixture suite around the question, with expected answers written before examining the generated result:
- Add another ticket for C1. The order total must remain 260, while the ticket count increases.
- Add a genuine C2 order worth 100. The order total must become 360 despite the repeated amount.
- Remove all tickets. Every eligible order must remain when the question includes customers without tickets.
- Duplicate a supposedly unique customer lookup row. The cardinality check must fail rather than silently choose one.
- Reuse a customer identifier in a second tenant. Records must remain separated by the full key.
- Introduce a null join key. Require the documented rejection, quarantine or unmatched treatment.
- Add overlapping historical versions. The selected-time lookup must reject ambiguous matches.
- Empty the eligible order set. Require the agreed empty-result behaviour, not an unexplained missing number.
Run mutations independently from the original fixture so one case does not accidentally change another's expected answer. Verify exact integer counts; choose numerical tolerances only where the measure requires them. A broad tolerance should not excuse a missing record.
Inspect coverage before comparing totals. Equal positive and negative adjustments can conceal duplication, just as an omission can offset an extra contribution. Keep per-key diagnostic results available to an authorised reviewer without copying sensitive production rows into unrestricted logs.
This is a specific application of our AI output-validation guide: successful execution is evidence about execution, not permission to rely on the answer.
Make the result traceable to its assumptions
For a consequential report, retain a compact query receipt: the question, metric definition, input snapshots or cut-off, complete join keys, input and output grains, SQL digest, engine version and test results. Record the intended population and any approved exclusions. Tie the receipt to the result that was actually delivered.
Measure unmatched-key rates and multiplicity violations separately. Track reconciliation differences by meaningful segment, not only for the grand total. A newly added source, changed history model or revised join predicate should trigger renewed checks even if the model and prompt are unchanged.
Also monitor the cost of validation. Full reconciliation may be appropriate for a released operational total; bounded diagnostic samples can help investigate a problem but cannot establish complete coverage. State which evidence was collected instead of labelling every result “validated”.
The release condition is specific: each included fact contributes according to the agreed rule, every required record is accounted for, and the returned measure answers the defined question. Once those conditions are explicit, an AI assistant can help construct and explain the query without becoming the unexamined authority for its numbers.
