Why your revenue doubled overnight (dedup it 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. if revenue jumps, check the join first

Rows multiply in three places

Cause 1: ingestion duplicates

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

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

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

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. measure first, argue later

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.