Data Analytics · Evidence-led case study

When payment totals and order values disagree

A dashboard can look convincing even when two measures that appear equivalent tell different stories. In this independent e-commerce analysis, I kept payment totals separate from the value of items and shipping, then investigated where they did not reconcile.

The business question

Can a reporting team treat recorded payment value and item value plus freight as the same financial measure? If not, how many orders differ, by how much, and what patterns deserve investigation?

How the comparison was made

Item and payment records were each summarized at one row per order before joining. This matters: directly joining multiple items to multiple payments can multiply rows and create misleading totals. The reconciliation model preserves missing summaries as missing, rather than counting them as financial matches or mismatches.

Of 99,441 modeled orders, 98,665 had both summaries; 98,362 matched and 303 did not. The remaining orders cannot be compared on both measures: 775 lack an item summary and one lacks a payment summary.

What the investigation found

The differences were not limited to tiny rounding effects. Among mismatching orders, the median absolute difference was BRL 5.90 and the maximum was BRL 182.81. Of the 303 mismatches, 299 concerned delivered orders.

A more specific pattern emerged for credit-card orders with exactly one payment record: mismatch incidence increased across installment groups. This is a filtered comparison, not a statement about every order or payment method.

On smaller screens, scroll the table horizontally to inspect all columns.

What the evidence does not prove

The association with installment count does not establish why the differences occurred. The available data do not identify whether they reflect interest, processing fees, adjustments or source-system errors. I therefore kept the two financial measures distinct and left discrepancies visible for further investigation instead of silently correcting them.

What was tested

The broader Snowflake/dbt pipeline includes 28 models and a documented regression result of 249 passing dbt checks, covering structural and business-rule integrity. Those checks are not equivalent to 249 independent scientific experiments, nor do they establish the economic cause of the differences. Incremental loading and idempotent reruns were also documented in the project.

While the original Snowflake environment is unavailable for execution, I have extended the project's reproducible checks using DuckDB and fabricated data. A GitHub Actions run on 3 November 2026 passed 11 Python validation scripts. One connects SQL from 14 of the original 28 dbt models, from synthetic RAW inputs through staging, both the order and item-grain fact tables, and three monthly marts (financial reconciliation, customer reviews and delivery operations), with explicit SQL-dialect adaptations. Other scripts check incorrect JOIN grain, missing values, local incremental updates, late corrections, rollback and a limited two-connection DuckDB conflict scenario.

The delivery test includes punctual, late and missing timestamps, plus an intentionally impossible event sequence. The original delivery mart still counts that anomalous order as on time; its temporal anomaly is reported separately. In a fabricated February example the original definition yields 2/3 on time (66.67%), versus 1/2 (50%) if the anomalous order is excluded from both numerator and denominator. That alternative is a business-rule illustration, not an adopted correction. These synthetic tests do not rerun the historical 249 dbt checks, execute Snowflake or its original data, establish a complete dbt migration, or measure warehouse performance. The local DuckDB MERGE and concurrency tests do not establish production safety.

Inspect the evidence

Technical reviewers can trace the numbers to the underlying SQL model, diagnostic queries and written findings. Readers without a technical background can start with the findings report.

Independent portfolio analysis of a public e-commerce dataset. No client engagement, financial-loss estimate or production intervention is claimed.