Quick answer: For row-wise pandas concat, verify_integrity checks the resulting index, not business-key columns. With ignore_index=True, pandas builds a fresh row index, so repeated order IDs can pass that check. Audit the documented business key separately, or set it as the index before concatenation if index uniqueness is the intended rule.

By Prakhar Shrivastava · October 25, 2026

Why did a checked concatenation still add $100 twice?

Two fictional exports contain US and UK orders with amounts already normalized to USD. The first batch has US order 1 for $100 and US order 2 for $50. The second repeats US order 1 and includes UK order 1 for $80. In this exercise, order IDs are local to each source; the pair of source and order ID identifies an order.

US order 1 and UK order 1 are different orders even though their local ID text matches. The repeated US order is deliberately included as a replay candidate. Concatenation stacks records; it does not decide whether a repeated business key represents a replay, a correction or an error.

Run the counterexample

import pandas as pd
first = pd.DataFrame({
 'source': ['US','US'], 'order_id': ['1','2'], 'amount_usd': [100,50]
})
second = pd.DataFrame({
 'source': ['US','UK'], 'order_id': ['1','1'], 'amount_usd': [100,80]
})
combined = pd.concat([first, second], ignore_index=True, verify_integrity=True)
key = ['source','order_id']
suspects = combined.loc[combined.duplicated(key, keep=False)]
print(combined.to_string(index=False))
print('Rows:', len(combined), 'USD:', combined.amount_usd.sum())
print('Suspected repeated keys:')
print(suspects.to_string(index=False))

source order_id  amount_usd
    US        1         100
    US        2          50
    US        1         100
    UK        1          80
Rows: 4 USD: 330
Suspected repeated keys:
source order_id  amount_usd
    US        1         100
    US        1         100

The call succeeds with a unique index of 0, 1, 2 and 3. Yet the business-key audit finds both appearances of US order 1. The combined amount is $330, including the replay candidate. Under this fixture’s explicit assumption that the repeated US row is an identical replay, one copy per order would total $230.

What does verify_integrity actually protect?

For the default axis=0 concatenation, it rejects overlapping labels on the new row index. It does not search order_id or source for duplicates. A generated index can uniquely identify positions in an output table while saying nothing about whether the same business event appears twice.

Using keys to add batch labels to the index can also make index tuples unique while preserving repeated orders across batches. Batch provenance is useful for investigation, but it is not a substitute for checking the order identity.

Can you check the business key through the index?

key = ['source', 'order_id']
pd.concat([first.set_index(key), second.set_index(key)],
          verify_integrity=True)
# Raises ValueError because ('US', '1') appears more than once.

Here the index represents the intended business identity, so overlap rejection is meaningful. Validate missing keys, source namespaces and within-batch uniqueness too. An index design is only as reliable as the key definition placed into it.

If downstream code needs a normal row index, keep a separate explicit duplicated(key, keep=False) audit before resetting the index. The important requirement is checking business identity, not preserving a particular DataFrame layout.

Should you automatically drop the duplicate?

No. The matching amounts in this small fixture are consistent with its stated replay assumption, but equal amounts alone do not prove that real records are identical. Compare the relevant payload, source version, extraction metadata and documented update policy. A repeated order may carry a corrected amount or status.

Quarantine conflicting copies or resolve them with an approved version rule. If an exact replay is confirmed, retain lineage showing which copy was kept and why. Blindly keeping the first or last row makes file order an undocumented business decision.

What should a reconciliation report contain?

Report input rows by batch, output rows, repeated-key groups, unresolved conflicts and amounts before and after approved resolution. For this example, four received rows become three unique business keys, and the hypothetical replay removal changes $330 to $230. Those figures describe the synthetic data, not a production correction.

When combining European, UK and US feeds, define source-local identifiers and normalize currency and reporting periods before comparing amounts. Do not infer that matching numeric IDs across regions are the same order, and do not assume a fresh numeric index establishes uniqueness of the underlying events.

Verification and sources

Five assertions passed in pandas 2.2.3: concatenation keeps four rows with a unique generated index, the raw sum is $330, the repeated-key audit isolates the two US records, indexing by the business key rejects the overlap, and the fixture’s replay assumption gives $230. Rerun these cases against your installed pandas version and data types.

The official concat reference documents ignore_index and verify_integrity. Continue with auditing repeated order keys before deletion, source-specific order identity, and merge cardinality checks.