Capstone: The Revenue Report That Didn't Match

Scenario

Example company: an e-commerce company's board report shows monthly revenue from the warehouse. Finance reconciles it with the payment provider's settlement files: the warehouse is 4% higher in most months and 11% higher in the month of a two-day outage.

The pipeline:

• Every 15 minutes, extracts orders and order_items incrementally with updated_at > watermark and appends them to raw tables.
• Payment webhooks (at-least-once delivery) are stored in raw_payments.
• A dbt model joins orders, order items and payments and sums orders.total per month.
• During the outage, an engineer re-ran two days of extraction by hand.

A quick reconciliation against the sample shop data shows what a per-day check looks like:

WITH source AS (
  SELECT o.order_date AS day, ROUND(SUM(i.quantity * i.unit_price), 2) AS revenue
  FROM orders o JOIN order_items i ON i.order_id = o.order_id
  GROUP BY o.order_date
),
order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total FROM order_items GROUP BY order_id
),
report AS (   -- the buggy model: order totals joined back to lines
  SELECT o.order_date AS day, ROUND(SUM(t.total), 2) AS revenue
  FROM orders o JOIN order_totals t ON t.order_id = o.order_id
  JOIN order_items i ON i.order_id = o.order_id
  GROUP BY o.order_date
)
SELECT s.day, s.revenue AS source, r.revenue AS report, ROUND(r.revenue - s.revenue, 2) AS difference
FROM source s JOIN report r ON r.day = s.day
WHERE r.revenue <> s.revenue
ORDER BY s.day
LIMIT 5;

day | source | report | difference
2026-01-05 | 53.99 | 107.98 | 53.99
2026-01-12 | 113.99 | 227.98 | 113.99
2026-02-14 | 408.9 | 817.8 | 408.9
2026-02-28 | 114.98 | 229.96 | 114.98
2026-03-17 | 114.99 | 344.97 | 229.98

Requirements

• Find every mechanism that can inflate or deflate revenue in this design, and estimate which explains the steady 4% and which the outage month.
• Redesign extraction, loading and modelling so revenue matches settlements within an agreed tolerance.
• Add tests and monitoring that would catch a regression within a day.

Constraints

• The production database can't take additional heavy queries during business hours.
• Board numbers already published must not change silently; corrections need a documented adjustment.

Deliverables

1. A root-cause list with evidence you would gather for each (queries, counts).
2. The new design: extraction method, load semantics, table grains and the revenue model.
3. The tests and reconciliations, with thresholds and owners.
4. A plan to correct historical months, and how to communicate the adjustment.

Hints

• Look for fan-out (order totals joined to lines and payments), duplicate webhook deliveries, appends of rows re-read by overlap or manual reruns, and cancelled or refunded orders.
• Settlements are net of refunds and are dated by settlement date, not order date.
• Daily reconciliation against an independent source is the strongest guard.