The report can double without gaining an order

Your data agent imports an orders file correctly, enriches it with customer regions and produces a convincing chart. The source file contains two orders totaling 100. The chart reports 200.

The join, not the CSV parser, may be responsible. Before enrichment, define what one row represents and how many rows on the other side may match it. This guide is for teams using agents to prepare recurring operational reports, where a plausible total is not sufficient evidence of correctness.

Methodology: inspect the relationship, not the chart

We reviewed the pandas merge reference, its merging guide and PostgreSQL's table-expression documentation on September 16, 2026. They establish matching and filtering behavior. The workflow and small example below are our proposed review method, not a benchmark or an executed test.

This article begins after ingestion. Our CSV reconciliation guide covers parsing, types and row dispositions. Here the question is narrower: did combining two otherwise usable datasets change the population or multiply a measure?

A manually worked example

Suppose the orders table contains:

Order O1: customer A, amount 40.
Order O2: customer A, amount 60.

The region lookup contains two records for customer A: one labeled North and one labeled South. Joining on customer alone matches each order twice. The four resulting amounts are 40, 40, 60 and 60, totaling 200.

This is arithmetic on an invented fixture, not a measured production incident. The pandas merging guide explains that repeated matching keys on both sides produce the Cartesian product of their associated rows. Choosing a left join does not by itself guarantee preservation of the left table's row count.

The right response is not automatically to delete a lookup row. The two records could be a data error, a history of region changes or a legitimate multi-region relationship. Those interpretations need different reporting rules.

Write the contract before the merge

For an order-enrichment task, our recommended contract is one row per order before and after enrichment, with at most one eligible region record per customer at the relevant reporting time.

Record these decisions before implementation:

The business grain: one order, not one customer or one region assignment.
The explicit matching keys and their meaning.
The allowed relationship: many orders to one eligible customer record.
The reporting time and any effective-date rule.
The treatment of missing keys and unmatched orders.
Which measures must remain unchanged through enrichment.

If a customer can legitimately belong to several regions, decide whether the report needs separate membership counts, an approved allocation rule, or a different grain. Repeating the entire order amount under every region may be useful for a particular question, but it must not be mislabeled as an additive total across regions.

Ask the data owner to resolve ambiguous history rather than having the agent select whichever lookup row appears first.

Turn cardinality into a check

The pandas merge reference documents validate="many_to_one" to check uniqueness of the right-hand merge keys. It also documents indicator=True for identifying matched and unmatched rows. In contrast, validate="many_to_many" permits the relationship without performing uniqueness checks.

For the proposed order-enrichment contract, use the many-to-one check after selecting the eligible lookup records. A failed check is a useful stop signal, not an obstacle to bypass by switching to many-to-many.

Keep a diagnostic showing the conflicting keys and the selection rule that produced them. Use non-sensitive samples when sharing the problem. The agent should return that evidence before making a chart from an unresolved relationship.

The same API reference warns that pandas matches null join keys to each other, unlike usual SQL join behavior. Set a missing-key policy explicitly. Do not assume that moving an existing SQL workflow into pandas preserves every matching assumption.

Check for disappearance as well as multiplication

A report can also lose valid orders after a left join. For example, filtering the result to require a particular right-hand region value removes unmatched rows whose region is absent.

PostgreSQL's table-expression guide explains that WHERE filters the derived rows and discards conditions that are false or null. For an outer join, putting a restriction in ON is not equivalent to putting it in WHERE.

Our recommendation is to review eligibility and enrichment separately. Does the request mean all orders, annotated with a region when known, or only orders with a confirmed region? Write that answer in the report contract before deciding where the filter belongs.

After each transformation, compare the number of distinct order identifiers, total rows, unmatched orders and the order-level amount. A single grand-total check can conceal offsetting mistakes, so also inspect whether an individual order occurs more often than the contract allows.

Limitations and an acceptance exercise

Cardinality checks cannot prove that a customer was assigned the correct region or that the source export was complete. They test a relationship, not every business fact. Intentional many-to-many analysis needs its own measures and acceptance criteria.

Before adopting a reporting skill, propose a tiny fixture containing a unique match, a duplicate lookup key, an unmatched order and missing keys. Add a separate history case if the workflow uses effective dates. Write expected outcomes with the data owner before execution.

The handoff should include source identities, the declared grain, eligibility rules, conflict diagnostics and before-and-after reconciliation. If any of these remain unresolved, deliver a diagnostic rather than an apparently finished report.

Browse the skill directory for implementation candidates, but evaluate the data contract first. A reporting workflow should explain why its totals survived the join before asking you to admire the chart.