Why Your Revenue Doubled Overnight: Deduplicating Orders and Events in SQL

Revenue doubled overnight. Nobody celebrated, because nobody had doubled anything — a query had. Somewhere between the events table and the dashboard, rows multiplied: a fan-out join, an untracked duplicate webhook, a UNION that overlapped. The business spent a week debating a growth miracle that never happened.

Duplicate counting is the most common data-correctness bug in marketing warehouses, and the hardest to notice, because every wrong number looks like a plausible right one. This post catalogues the three ways rows multiply, the diagnostic queries that catch each, and the dedup patterns that prevent them.

The short version: rows multiply in three places

Cause 1: ingestion duplicates — the same event stored twice

Webhooks retry. Pixels fire on back-button revisits. A backfill overlaps the streaming pipeline by a day. Each produces two rows describing one reality, and SUM(revenue) happily counts both.

Diagnosis: find the doubles

Spikes on specific days point at backfills and retries; a constant low rate points at a double-firing tag. Either way, the fix belongs upstream (idempotent loads, MERGE on event ID) — but reporting must be robust to it regardless.

Prevention: deduplication as a view, not a hope

-- Canonical orders: one row per order_id, latest record wins.
-- Every revenue report reads this view, never the raw table.

CREATE VIEW v_orders_deduped AS
SELECT *
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY _ingested_at DESC
    ) AS _rn
  FROM raw_orders o
)
WHERE _rn = 1
  AND order_status = 'completed';

Cause 2: fan-out joins — correct tables, multiplied results

This is the famous one, covered briefly in the ROAS-reconciliation post and worth a full treatment because it bites every team exactly once. Joining a 1,000-row orders table to a 10-row daily-spend table on date produces up to 10,000 rows: each order repeated per campaign. SUM(revenue) then reports 10x reality — and the more campaigns you run, the richer you look.

-- THE PATTERN: aggregate each side to the join grain FIRST.
WITH daily_spend AS (
  SELECT spend_date, SUM(cost) AS spend
  FROM raw_ad_spend GROUP BY 1
),
daily_revenue AS (
  SELECT DATE(order_created_at, 'Asia/Kolkata') AS day,
         SUM(net_total) AS revenue
  FROM v_orders_deduped GROUP BY 1
)
SELECT day, spend, revenue
FROM daily_revenue FULL OUTER JOIN daily_spend
  ON daily_revenue.day = daily_spend.spend_date;

Cause 3: grain confusion — counting events as orders

An events table has one row per event; a purchase funnel has many events per order (view, add-to-cart, checkout, purchase). COUNT(*) where you meant COUNT(DISTINCT order_id), or summing a value column that repeats on every event of the same order, inflates by the events-per-order ratio — typically 3–8x, varying by funnel length, which makes it look like a trend rather than a bug.

The dedup checklist for every new table

Frequently Asked Questions

Our revenue is only off by 2–3%. Is that still duplicates?

Possibly — but at that scale also suspect refunds timing, timezone edges and FX rounding. Run the duplicate detector: it costs one query and converts suspicion into a measured duplicate rate. Fix what you can measure; the residual 1–2% is usually definitional (gross vs net) rather than duplication.

Should dedup happen in dbt/models or in views?

Wherever your team actually maintains it. The principle is location-independent: raw stays immutable, exactly one blessed deduped object exists per table, and every report reads the blessed object. A beautiful dbt model nobody runs and a view everybody reads — the view wins.

How does this relate to bytes scanned and cost?

Dedup views with ROW_NUMBER() OVER (PARTITION BY ...) scan the full table on every read. On large event tables, materialise the deduped result on a schedule (the incremental pattern from the no-dbt post) instead of recomputing it per query — correctness and cost optimised together.

Summary & Next Steps

Rows multiply at ingestion (same event twice), at join time (fan-out), and at counting time (wrong grain). Diagnose with the duplicate-rate query, prevent with per-table dedup views keyed on business identity, and make aggregate-before-join an absolute rule.

• Use deduplication views as the only readable surface for revenue tables.
• Use schema mapping to record each table's grain and business key where analysts will find it.
• Use partitioning plus scheduled materialisation so correctness does not become a cost problem.