Two genuine orders have the same date and the same total. An analyst combines store exports, sums the result, and finds less revenue than either source-system reconciliation predicts. Nothing failed to load. The query removed rows because the selected values looked identical.
EcomToolkit’s recommendation is to define an order’s identity before combining sales sources. This article examines append operations, full-row duplicate removal and source-aware reconciliation. All amounts and records below are illustrative, use one reporting currency, and demonstrate query behaviour rather than accounting policy or a merchant’s results.
Table of Contents
- Understand what UNION actually removes
- Carry source identity through the append
- Distinguish repeated exports from separate orders
- Reconcile before and after every transformation
- Separate record identity from metric definition
- Make the reporting contract easy to inspect
- The EcomToolkit view
Understand what UNION actually removes
SQL UNION combines compatible query results and removes duplicate output rows unless ALL is specified. Crucially, the comparison concerns the columns selected by the query. If order identifiers and source names have already been discarded, the database cannot use them to recognise two different purchases.
The PostgreSQL combining-queries documentation describes this distinction. UNION ALL retains the rows from both inputs. Neither choice understands the business meaning of a purchase, a refund or an export snapshot. You must supply that meaning through the data model and transformation rules.
Suppose Store A has order A17 for 80 units of reporting currency and Store B has order B92 for the same amount on the same date. A projection containing only date and amount makes those rows identical. UNION returns one row and a total of 80; UNION ALL returns two rows and a total of 160.
| Input record | Business identity | Selected date | Selected amount |
|---|---|---|---|
| Store A purchase | A / A17 | 2026-09-20 | 80 |
| Store B purchase | B / B92 | 2026-09-20 | 80 |
| UNION of date and amount | Identity absent | One retained date row | Total 80 |
| UNION ALL of date and amount | Identity still absent | Two retained date rows | Total 160 |
This is why an apparently harmless cleanup step can bias reports towards lower counts. Common basket totals, standard subscription charges and fixed-price bundles create legitimate repetition. Matching values are not sufficient evidence that the same business event occurred twice.
Carry source identity through the append
Preserve a source namespace, the source order identifier and the intended reporting grain. An identifier such as 1001 may be unique inside one store while existing independently in another. Concatenating source tables without a namespace creates collisions that are easy to mistake for duplicates.
An illustrative append can make that namespace explicit:
SELECT 'store_a' AS source_store, order_id, order_date, amount
FROM store_a_orders
UNION ALL
SELECT 'store_b' AS source_store, order_id, order_date, amount
FROM store_b_orders;This pattern assumes each input already represents one row per order and compatible amount definitions. It does not deduplicate historical snapshots, align currencies, or exclude cancelled records. Those decisions need separate, inspectable transformations rather than being hidden inside the set operator.
Check column order as well as types. Set operations align corresponding output positions; a compatible numeric column can still contain the wrong business measure. Explicit column lists are safer to review than SELECT star when upstream exports may add or reorder fields.
Distinguish repeated exports from separate orders
The opposite failure occurs when two files contain the same order. Yesterday’s export and today’s export might both include the full month. Appending them with UNION ALL can count the overlapping orders twice. Switching blindly to UNION only helps if the selected rows are exactly identical.
If the second export contains an updated status, amount or extraction timestamp, the full rows differ and both remain. The dataset needs a business-key reconciliation rule: identify the same source order, choose the appropriate version, and retain an audit trail explaining which observation won.
A practical pipeline separates immutable raw inputs from the reporting table. Raw records preserve file name, extraction time and source identifiers. The reporting layer then resolves versions according to a documented source timestamp and a deterministic tie-breaker. Unresolved conflicts should be visible rather than silently selected at random.
Decide whether the report describes the latest known state or the state known at an earlier cutoff. Those are different datasets. Keeping the latest version is useful for a current order dashboard, but it can rewrite a historical report if there is no snapshot policy. Coordinate that choice with the people reconciling the totals.
Reconcile before and after every transformation
Begin with source-level row counts and amount totals for a clearly defined period. Record the number of distinct business keys, missing keys and conflicting versions. After the append, compare the totals to the sum of the inputs before any intentional removals or status filters.
Then account for every movement between raw and reporting totals. Separate repeated-file removal, order-version selection, currency conversion, cancellation filtering and refunds. A single unexplained adjustment line is difficult to audit and makes later discrepancies look like new bugs when they may be known transformations.
For the two-order example, the accepted output must contain two identities and total 160. Add a replay of A17 with the same values: raw appended revenue becomes 240, but the resolved order view should still total 160. Add a newer A17 version of 85, and the latest-state total should become 165 under that specific version policy.
These tiny fixtures expose different errors. A test containing only unique amounts may never reveal projection-based row loss. A test containing only identical replays may falsely suggest that UNION is a complete deduplication strategy. Include both legitimate repetition and changed versions.
| Validation fixture | Desired reporting outcome | Mistake it detects |
|---|---|---|
| Different orders with equal totals | Keep both orders | Deduplication on projected amounts |
| Same identifier in different stores | Keep both namespaces | Cross-store key collision |
| Replayed identical export record | Keep one business event | Uncontrolled append duplication |
| Same order with newer amount | Follow documented version rule | Full-row dedup mistaken for versioning |
| Missing source identifier | Quarantine or explicit exception | Accidental merging of unknown orders |
Separate record identity from metric definition
Even a perfectly reconciled order table can produce misleading revenue if inputs measure different things. One source may report item subtotal and another the paid total including shipping. Identical currency codes do not make those fields interchangeable. Label the amount basis before unifying them.
Keep refunds as their own events unless the reporting contract explicitly requires a latest net-order amount. Otherwise a refund feed and an already-netted order export can reduce revenue twice. Document whether the date dimension follows order placement, payment settlement or refund processing.
The PostgreSQL SELECT reference is useful for checking DISTINCT and set-operation semantics, but it cannot settle these business definitions. Ask the report owner to approve the grain and amount contract before optimising the query.
For adjacent problems, see our guides to SQL join fanout and NULL reporting errors. A join can multiply records and a filter can lose unknown values; this guide concerns what happens when result sets are appended and deduplicated.
Make the reporting contract easy to inspect
Publish a concise dataset note beside the dashboard: one row represents which entity, the complete key, the selected version policy, the amount basis, the reporting currency and the cutoff time. Include a contact or owner for unresolved conflicts. Analysts should not need to reverse-engineer a long query to discover these facts.
Preserve counts for excluded and quarantined rows. A report that totals correctly after dropping all difficult records may still be incomplete. Compare exception volume over time and investigate changes after export updates, connector upgrades or the addition of another store.
Review performance only after correctness is established. Duplicate elimination can require work that an append does not, but choosing UNION ALL solely because it runs faster can create expensive business errors. Examine query plans and real volumes after deciding which rows are supposed to survive.
When replacing a production transformation, run old and new outputs side by side over a closed period. Explain differences at order level and sample high-frequency basket values. Keep the old result available until the responsible analyst can reconcile the changed totals and downstream reports.
The EcomToolkit view
Duplicate rows and duplicate orders are different concepts. Preserve business identity, append deliberately, and make version selection a named transformation. Revenue should change because the underlying business facts changed, not because a useful identifier disappeared from a SELECT list.
Bring one mismatched export and its reporting query to an EcomToolkit analytics review. The useful deliverable is a reconciled path from source records to the metric your team actually uses.
