Double-Counting in Star Schemas: A Subtle Revenue Bug

56

I spent two days hunting a revenue bug that produced numbers exactly 100 euros off on specific records. The SQL was correct. The data model was correct. The individual fields were correct. The bug was in how I was combining them — a subtle interaction between revision-aware bid fields and delta-based quote adjustment fields in a Unified Star Schema metrics bridge.

This post walks through the bug, why it is easy to miss, and how I restructured the revenue fields to prevent it from happening again.

Background: The Metrics Bridge

Our data warehouse uses the Unified Star Schema (USS) pattern. Instead of traditional star/snowflake schemas with separate fact tables, we have a single metrics bridge table that connects all dimensions and contains all measures. Each row represents a case (think: a service request) at a specific stage of its lifecycle.

Revenue in our domain has four components:

1. Bid revenue — commission on the accepted quote amount
2. Quote adjustment (QA) revenue — commission on price changes after the quote is accepted
3. Non-transactional revenue — fixed fees, platform subscriptions
4. Platform fees — per-transaction charges

Each component lives on a different stage of the metrics bridge. Total revenue is:

SELECT
    SUM(bid_revenue_eur) AS bid_revenue,
    SUM(qa_revenue_eur) AS qa_revenue,
    SUM(non_transactional_revenue_eur) AS non_transactional,
    SUM(platform_fee_revenue_eur) AS platform_fees,
    -- Total = sum of all four
    SUM(total_revenue_all_sources_eur) AS total_revenue
FROM metrics_bridge
WHERE date_revenue_attribution BETWEEN '2025-01-01' AND '2025-01-31'

The important thing about USS is that you never filter on stage. Each measure is NULL on stages where it does not apply. You just SUM() across the whole bridge and the NULLs take care of themselves.

The Setup: Quote Revisions

After a service provider submits a quote and the customer accepts it, the provider can revise the quote — changing line items, adjusting quantities, reclassifying work. When this happens, the system creates a quote adjustment (QA) record that captures the delta between the original quote and the revised quote.

A quote has several price components: base price, services, parts, paint, and so on. A revision might move money between components while keeping the total the same. For example:

• Original quote: base = 400, services = 100, total = 500
• Revised quote: base = 500, services = 0, total = 500
• QA delta: base = +100, services = -100, total = 0

The total did not change, but the composition did. This matters because commission rates can differ by component.

The Bug

Here is where it gets subtle. I had two layers of fields in the metrics bridge:

• Bid-level fields: bid_total_eur, bid_base_eur, bid_services_eur, etc. These come from the accepted bid and are revision-aware — they already reflect the latest revision of the quote.
• QA-level fields: qa_total_eur, qa_base_eur, qa_services_eur, etc. These capture the full delta from original to revised quote.

I had a convenience field called gov_total_eur (gross order value, total) that was computed as:

-- THE BUG: combining revision-aware totals with full deltas
bid_total_eur + qa_total_eur AS gov_total_eur

This looks reasonable. Bid total + QA total = final total including adjustments. But here is the problem. Consider the example above:

Original quote:
  base = 400, services = 100 → total = 500

Revision:
  base = 500, services = 0 → total = 500  (provider reclassified services as base work)

After revision:
  bid_total_eur = 500  (revision-aware: shows current total)
  bid_services_eur = 0  (revision-aware: services are now 0)

  qa_total_eur = 0     (delta of total: 500 - 500 = 0)
  qa_services_eur = -100  (delta of services: 0 - 100 = -100)
  qa_base_eur = +100      (delta of base: 500 - 400 = +100)

So far so good: bid_total + qa_total = 500 + 0 = 500. Correct.

Now consider a different revision where the total does change:

Original quote:
  base = 400, services = 100 → total = 500

Revision:
  base = 500, services = 0 → total = 500  (reclassification)

Second revision (adds work):
  base = 525, services = 0 → total = 525

After second revision:
  bid_total_eur = 525    (revision-aware: latest total)
  bid_base_eur = 525     (revision-aware: latest base)
  bid_services_eur = 0   (revision-aware: services still 0)

  qa_total_eur = 25      (delta: 525 - 500 = 25)
  qa_base_eur = 125      (delta: 525 - 400 = 125)
  qa_services_eur = -100 (delta: 0 - 100 = -100)

Now: bid_total + qa_total = 525 + 25 = 550. But the actual total is 525.

The bid total is already revision-aware (525), but the QA total captures the full delta from the original (25). Adding them double-counts the change.

It gets worse with the component-level fields. Look at services:

bid_services_eur = 0       (revision-aware)
qa_services_eur = -100     (full delta from original)

bid_services + qa_services = 0 + (-100) = -100

But the actual services amount is 0. We are double-subtracting the services change.

Why It Was Hard to Find

Three reasons this bug survived for months:

1. Most quotes have zero or one revision. With one revision, the bid fields equal the original plus the delta, so bid_total + qa_total coincidentally equals the correct value. The bug only manifests with multiple revisions.
2. The total-level fields often mask it. Many revisions reclassify between components without changing the total. When qa_total = 0, the double-counting at the total level disappears. It only shows up in component breakdowns — or when a later revision changes the total.
3. The magnitudes are small. A typical price change in a revision is 25-200 euros. When you are looking at monthly revenue in the hundreds of thousands, a few hundred euros of double-counting does not jump out.

I found it by accident while investigating a specific case where the component-level breakdown did not sum to the total. The services component was showing -100 when it should have been 0.

The Fix

The root cause is mixing two different semantics: revision-aware snapshots (bid fields) with cumulative deltas (QA fields). The fix is to use component-level fields consistently and never sum totals from both layers.

Option A: Component-Level Summation (What I Did)

Instead of computing GOV as bid_total + qa_total, compute it as the sum of individual components where each component adds its bid and QA parts:

-- CORRECT: sum at component level, not total level
(bid_base_eur + COALESCE(qa_base_eur, 0))          -- base component
+ (bid_services_eur + COALESCE(qa_services_eur, 0)) -- services component
+ (bid_parts_eur + COALESCE(qa_parts_eur, 0))       -- parts component
+ (bid_paint_eur + COALESCE(qa_paint_eur, 0))       -- paint component
AS gov_total_eur

Wait — this has the same problem, does it not? The bid fields are revision-aware and the QA fields are cumulative deltas. Adding them at the component level still double-counts.

Not quite. The key insight is that the bid component fields are revision-aware for each component independently. And the QA component fields capture the delta for each component independently. But the total fields are not simply the sum of the components after revision. Here is why.

Actually, let me correct myself. The real fix was to change the QA fields from “delta from original” to “delta from last revision” semantics. This was done in the dbt model that prepares the QA stage of the metrics bridge:

-- In the PBS (bridge preparation) layer
-- OLD: qa_total = final_total - original_total (cumulative delta)
-- NEW: qa_total = final_total - pre_revision_total (incremental delta)

-- For cases with multiple revisions, we now compute the delta
-- relative to the state the bid fields already reflect

WITH revision_chain AS (
    SELECT
        case_id,
        revision_number,
        total_ex_vat_eur,
        base_ex_vat_eur,
        services_ex_vat_eur,
        parts_ex_vat_eur,
        paint_ex_vat_eur,
        LAG(total_ex_vat_eur) OVER (
            PARTITION BY case_id ORDER BY revision_number
        ) AS prev_total,
        LAG(base_ex_vat_eur) OVER (
            PARTITION BY case_id ORDER BY revision_number
        ) AS prev_base,
        -- ... same for other components
    FROM quote_adjustments
),

qa_measures AS (
    SELECT
        case_id,
        -- Use the delta from the PREVIOUS revision, not from the original
        SUM(total_ex_vat_eur - COALESCE(prev_total, 0)) AS qa_total_eur,
        SUM(base_ex_vat_eur - COALESCE(prev_base, 0)) AS qa_base_eur,
        -- ... same for other components
    FROM revision_chain
    WHERE revision_number > 1  -- skip the original quote
    GROUP BY case_id
)

But I realised this still had edge cases with the bid fields being pre-revision-aware. The cleanest fix was actually simpler: do not use bid-level totals at all for GOV. Use the original quote components plus all QA deltas.

-- FINAL FIX in the PBS layer:
-- GOV = original bid components (pre-revision) + sum of all QA deltas

-- Bid fields: use ORIGINAL (pre-revision) values, not revision-aware
bid_original_base_eur,
bid_original_services_eur,
bid_original_parts_eur,
bid_original_paint_eur,
bid_original_total_eur,

-- QA fields: cumulative delta from original (this is fine now because
-- the bid fields are also relative to the original)
qa_base_delta_eur,
qa_services_delta_eur,
qa_parts_delta_eur,
qa_paint_delta_eur,
qa_total_delta_eur,

-- GOV total: safe to sum because both are relative to the same baseline
bid_original_total_eur + COALESCE(qa_total_delta_eur, 0) AS gov_total_eur

Let me verify with the example:

Original: base=400, services=100, total=500
Revision 1: base=500, services=0, total=500  (reclassification)
Revision 2: base=525, services=0, total=525  (adds work)

bid_original_total = 500 (locked to original)
qa_total_delta = 525 - 500 = 25 (cumulative from original)

GOV = 500 + 25 = 525 ✓

Component check:
  bid_original_services = 100
  qa_services_delta = 0 - 100 = -100
  Services GOV = 100 + (-100) = 0 ✓

  bid_original_base = 400
  qa_base_delta = 525 - 400 = 125
  Base GOV = 400 + 125 = 525 ✓

  Total from components: 525 + 0 = 525 ✓

The Broader Restructuring

While fixing the bug, I took the opportunity to reorganise all revenue and GOV fields in the metrics bridge for clarity. The old naming was confusing — some fields were revision-aware, some were not, and there was no naming convention to distinguish them.

The new naming convention:

Prefix conventions:
  bid_original_*     = pre-revision values (locked at acceptance)
  bid_accepted_bid_* = revision-aware values (reflects latest state)
  qa_*_delta_*       = cumulative delta from original

Derived fields:
  gov_*_eur          = bid_original_* + qa_*_delta_* (safe sum)
  revenue_*_eur      = commission on gov_*_eur (uses commission rate)
  total_revenue_all_sources_eur = sum of all four revenue components

I also added explicit comments in the dbt model explaining why certain combinations of fields must not be summed:

-- WARNING: Do NOT sum bid_accepted_bid_total_eur + qa_total_delta_eur
-- bid_accepted_bid_* fields are REVISION-AWARE (latest state)
-- qa_*_delta fields are CUMULATIVE from original
-- Mixing these semantics causes double-counting on multi-revision cases
--
-- For GOV: use bid_original_* + qa_*_delta (both relative to original)
-- For current state: use bid_accepted_bid_* alone (already includes revisions)

The dbt Model Changes

The fix touched three layers of the dbt project:

1. Intermediate model (bizlogic layer): Added bid_original_* fields to the bid analytical base model, sourced from the first revision of each quote.
2. PBS layer (bridge preparation): Updated the GOV and revenue calculations to use bid_original_* + qa_*_delta instead of bid_accepted_bid_* + qa_*.
3. Metrics bridge: Renamed fields to follow the new naming convention. Added the warning comments.

The intermediate model change looked like this:

-- int_bids__analytical_base.sql (excerpt)
-- Add original (pre-revision) bid values alongside the revision-aware ones

WITH bid_revisions AS (
    SELECT
        bid_id,
        case_id,
        revision_number,
        base_price_ex_vat_eur,
        services_ex_vat_eur,
        parts_ex_vat_eur,
        paint_ex_vat_eur,
        total_ex_vat_eur,
        ROW_NUMBER() OVER (
            PARTITION BY case_id
            ORDER BY revision_number ASC
        ) AS rn_first,
        ROW_NUMBER() OVER (
            PARTITION BY case_id
            ORDER BY revision_number DESC
        ) AS rn_last
    FROM {{ ref('stg_bids') }}
    WHERE is_accepted = TRUE
)

SELECT
    latest.bid_id,
    latest.case_id,

    -- Revision-aware (latest state) -- for display, current status
    latest.total_ex_vat_eur AS bid_accepted_bid_total_ex_vat_eur,
    latest.base_price_ex_vat_eur AS bid_accepted_bid_base_ex_vat_eur,
    latest.services_ex_vat_eur AS bid_accepted_bid_services_ex_vat_eur,

    -- Original (pre-revision) -- for GOV calculation with QA deltas
    original.total_ex_vat_eur AS bid_original_total_ex_vat_eur,
    original.base_price_ex_vat_eur AS bid_original_base_ex_vat_eur,
    original.services_ex_vat_eur AS bid_original_services_ex_vat_eur,

FROM bid_revisions latest
LEFT JOIN bid_revisions original
    ON latest.case_id = original.case_id
    AND original.rn_first = 1
WHERE latest.rn_last = 1

Testing the Fix

I wrote a BigQuery validation query that identifies all cases with multiple revisions and compares the old (buggy) and new (fixed) GOV calculations:

-- Find cases where old and new GOV calculations differ
WITH multi_revision_cases AS (
    SELECT case_id
    FROM quote_adjustments
    GROUP BY case_id
    HAVING COUNT(*) > 1
),

comparison AS (
    SELECT
        mb.case_id,
        -- Old calculation (buggy): revision-aware bid + cumulative QA
        mb.bid_accepted_bid_total_eur + COALESCE(mb.qa_total_delta_eur, 0) AS gov_old,
        -- New calculation (fixed): original bid + cumulative QA
        mb.bid_original_total_eur + COALESCE(mb.qa_total_delta_eur, 0) AS gov_new,
        -- Expected: what the final quote actually says
        mb.bid_accepted_bid_total_eur AS expected_total
    FROM metrics_bridge mb
    INNER JOIN multi_revision_cases mrc ON mb.case_id = mrc.case_id
    WHERE mb.stage = 'quote_adjustment'
)

SELECT
    case_id,
    gov_old,
    gov_new,
    expected_total,
    gov_old - expected_total AS old_error,
    gov_new - expected_total AS new_error
FROM comparison
WHERE ABS(gov_old - expected_total) > 0.01
ORDER BY ABS(gov_old - expected_total) DESC

This query found 847 cases (out of ~120,000 total) where the old calculation was wrong. The errors ranged from 1 euro to 3,200 euros. The total impact was about 48,000 euros of overstated GOV — small relative to total volume, but large enough to raise questions in monthly reports.

The General Lesson

The core pattern behind this bug shows up any time you have two sets of fields with different temporal semantics:

• Snapshot fields — represent the current state (revision-aware, mutable)
• Delta fields — represent cumulative change from a baseline (immutable)

You cannot mix these in arithmetic. If you add a snapshot (which already includes all changes) to a cumulative delta (which also captures all changes from the original), you are double-counting everything after the first change.

The fix is to pick one baseline and stick with it:

• For “total including adjustments”: Use original + cumulative delta (both relative to the same baseline)
• For “current state”: Use the snapshot field alone (it already includes everything)
• Never: snapshot + cumulative delta

This is the kind of bug that unit tests on individual fields will not catch. Each field is correct in isolation. The bug only appears when you combine them in a specific way on records with specific revision histories. Integration tests with multi-revision test cases are the only reliable way to catch it.

After this experience, I added a set of dbt tests specifically for multi-revision cases, verifying that GOV at the total level equals the sum of GOV at the component level, and that both equal the final quote total from the source system. These run on every dbt build and have caught two near-misses since.