Why Dashboards Disagree

Why Dashboards Disagree: The Case for a Semantic Layer

Ask three BookNest teams for June 2026 revenue and each writes reasonable SQL:

4153-disagree.sql: one question, three reasonable answersSQL

-- "June 2026 revenue", as three dashboards compute it
SELECT
  (SELECT sum(gross_amount) FROM mart.sales                    -- sales: every line booked
    WHERE order_date BETWEEN '2026-06-01' AND '2026-06-30') AS sales_team,
  (SELECT sum(total) FROM orders                               -- finance: net of coupons,
    WHERE status NOT IN ('cancelled', 'returned')              -- no cancels or returns
      AND order_ts >= '2026-06-01' AND order_ts < '2026-07-01') AS finance,
  (SELECT sum(gross_amount) FROM mart.sales s JOIN orders o USING (order_id)
    WHERE s.status <> 'cancelled'                              -- marketing: Kuala Lumpur
      AND (o.order_ts AT TIME ZONE 'Asia/Kuala_Lumpur')::date  -- calendar days
          BETWEEN '2026-06-01' AND '2026-06-30') AS marketing;

Output

 sales_team |  finance  | marketing
------------+-----------+-----------
  190057.50 | 174415.88 | 178883.56

The figures differ by up to 9%, and none is wrong. Sales counts every line booked; finance counts what was kept, net of coupons and returns; marketing drops only cancellations but uses Kuala Lumpur calendar days, which moves eight hours of orders across each month boundary. A semantic layer defines each metric once, by name, with its measure, filters and time handling, and every tool queries the metric instead of writing its own SQL.

MENU