Repository navigation
Reservation savings underreported due to min() price selection on duplicated reservation price keys #2176
Description
While validating reservation savings, I found cases where savings are lower than expected, and some rows that should be positive appear as zero.
Root cause appears to be in reservation price lookup aggregation:
For the same lookup key, there can be duplicate price rows (for example Production and Dev/Test variants).
Current logic uses min for unit price selection.
In these duplicates, min tends to pick the lower Dev/Test price.
That underestimates the on-demand baseline and leads to underreported reservation savings.
👣 Repro steps
Compare outcomes with:
current aggregation using min
alternative aggregation using max
Observe that min picks the lower Dev/Test variant and produces lower savings totals plus more zero rows.
🤔 Expected
When duplicate price variants exist, reservation savings should use the correct production baseline price for comparison, not the lower Dev/Test variant.
🔧 Environment
FinOps hub version: affected in v1_0 and v1_2 transformation paths
Billing account type: observed in MCA scenario
ℹ️ Additional context
Proposed fix:
In reservation reference price summarize:
change ListUnitPrice from min to max
change ContractedUnitPrice from min to max
Why this fix:
Duplicate rows include Dev/Test and Production variants.
min can select Dev/Test (lower) and distort savings.
max selects the expected production baseline in those duplicate scenarios.
Activity
RolandKrummenacher commentedon Jun 16, 2026
Hi josearsa (@josesarsa), thanks for the detailed write-up and the proposed fix — this is a thoughtful report.
We think we know the direction, but one decisive detail needs confirming from your data. The on-demand price lookup key already includes the offer ID (
x_SkuOfferId), and prices are aggregated per (billing profile + month + meter ID + offer ID). In EA, Production and Dev/Test are different offers (e.g. MS-AZR-0017P vs MS-AZR-0148P), so they'd land under different keys andmin()would never combine them. Since you're on MCA, our leading theory is thatx_SkuOfferIdcomes through blank in your Price Sheet, which collapses the key and lets multiple Consumption price rows for the same meter aggregate together — at which pointmin()picks the lowest.To confirm this is what you're seeing, could you check, for one affected meter in your
Pricesdata:x_SkuOfferIdempty/blank on those Consumption price rows?x_SkuMeterId(+ that blank offer ID) but have differentListUnitPrice/ContractedUnitPrice, and note which column actually distinguishes them (e.g.x_EffectivePeriodStartwithin the same month,PricingUnit,x_PricingBlockSize, or something else).Please scrub any tenant/subscription/resource identifiers first. This tells us whether the right fix is
min→maxor refining the lookup key so the rows stop colliding —maxwould be the wrong call if, for instance, the duplication comes from an in-month price change rather than a Production/Dev-Test split.#needs-info
RolandKrummenacher commentedon Jun 16, 2026
🔍 Root-cause analysis (engineering)
Status: Leading hypothesis identified; unconfirmed pending reporter data (the deciding field is export-driven, not determinable from code).
What's verified in code: Missing on-demand prices are backfilled via a leftouter join aggregated with
min(ListUnitPrice), min(ContractedUnitPrice)grouped bytmp_ReservationPriceLookupKey, x_PricingBlockSize, PricingUnit(IngestionSetup_v1_2.kql:425; sameIngestionSetup_v1_0.kql:420). Key =tolower(strcat(x_BillingProfileId, substring(ChargePeriodStart,0,7), x_SkuMeterId, x_SkuOfferId))(cost:419, price:423). Recovered prices feed ListCost/ContractedCost recompute (:459–486) and the discount percentages, so a non-representative recovered price distorts savings — that downstream path is real.Leading hypothesis (MCA blank offer ID):
x_SkuOfferIdis pass-through from the export in the FOCUS path (Prices:187; only the EA transforms map it fromOfferId, annotated// Azure EA,:246/:1280/:1501). The reporter is on MCA. If MCA Price Sheets emit a blankx_SkuOfferId, the key degenerates toprofile + YYYY-MM + meterId + '', so multiple Consumption price rows for one meter collapse andmin()selects the lowest — matching the symptom. Whether the field is actually blank is not determinable from code (export-driven) → must be confirmed from reporter data.Why the reporter's "Prod vs Dev/Test" framing doesn't hold literally: distinct EA offer IDs would already separate those into different keys;
min→maxwould be a no-op there. The collapse requires an identical (likely blank)x_SkuOfferId.Scope/impact: FinOps hubs ingestion KQL (v1_0 & v1_2), only rows entering the missing-price backfill (
tmp_MissingPrices,:412–418); MCA most exposed if offer ID is blank. Magnitude not reproduced (no sample data).Fix options:
min→max(:425/ v1_0:420): minimal but a blind heuristic — overstates savings if the real cause is an in-month price change; no-op if offer IDs already differ.x_EffectivePeriodStartproperly rather thanminover the month); more correct, larger change.Note: if blank MCA offer ID is confirmed, option 3 (effective-period-aware selection) is likely more robust than a blanket
max.Recommendation:
needs-info. Confirm the blankx_SkuOfferId+ the distinguishing column before committing a fix; that determinesmaxvs. a key/effective-period refinement.josesarsa commentedon Jun 20, 2026
Hi Roland Krummenacher (@RolandKrummenacher),
From price sheet, every single Consumption row has a blank x_SkuOfferId
Both are exactly the same except List and Contracted Unit price
KR,
JA
docs(changelog): reframe as general robustness fix, not an MCA fix
RolandKrummenacher commentedon Jun 22, 2026
🔧 Update: confirmed root cause + why
min→maxalone isn't enough for MCAThanks josearsa (@josesarsa) — your screenshots are decisive. They confirm the colliding
Pricesrows are identical in every identifier (samex_SkuMeterId, blankx_SkuOfferId, sameSkuPriceId, samex_EffectivePeriodStart/End) and differ only in unit price. That rules out a Prod/Dev-Test split and an in-month price change, and it means the collision can't be resolved by refining the key (evenSkuPriceIdis identical).However, on closer review the proposed
min→maxchange does not, on its own, fix the MCA case — and I want to be transparent about that rather than ship a no-op:tmp_MissingPrices, which requiresisnotempty(x_SkuOfferId)on the cost row (IngestionSetup_v1_2.kql:416,IngestionSetup_v1_0.kql:411).:419/:414) and the price side (:423/:418) includesx_SkuOfferId.x_SkuOfferIdis blank on the cost rows too (FOCUS pass-through,:187), so those rows are filtered out before the join and never reach themin()/max()collapse.So the
min→maxchange is correct general hardening for genuine duplicate collisions, but the MCA rows you're seeing don't currently flow through this path at all. I've reframed that change as a general robustness fix in #2189 and kept this issue open for the real MCA fix.Proposed MCA fix (design)
isnotempty(x_SkuMeterId)only (drop theand isnotempty(x_SkuOfferId)requirement at:416/:411), and update the adjacent// mapping ... requires meter ID and offer IDcomment.profile + YYYY-MM + meter + offer). For MCA the offer ID is blank, so the key naturally degrades to meter-grain — which is the correct grain since MCA has no offer split.max()from fix(hubs): use max() when duplicate on-demand price rows collapse under one lookup key #2189 then selects the genuine on-demand baseline.Risk to validate before committing: relaxing the gate lets meter-grain recovery run for any blank-offer tenant. We need to confirm
profile + month + meteris specific enough that it can't pull in a price from a different offer for billing types that do split by offer but occasionally emit a blank one. For pure-MCA (offer always blank) this is safe.One question to close this out: on your cost/usage rows (not the price sheet) for an affected meter, is
x_SkuOfferIdalso blank? That confirms gate-relaxation is the right lever.josesarsa commentedon Jun 23, 2026
Yes, indeed the x_SkuOfferId is blank in Costs/Usage table
By the way,
unit price and also contracted price differs. I did not include it to avoid exposing commercial details
flanakin commentedon Aug 17, 2026
Reopening — this was auto-closed by PR #2189's
Fixes #2176commit trailer, but that PR was explicitly reframed mid-review as generalmin()→max()hardening only, not a fix for the MCA case (see the PR's own "Scope / relationship to #2176" section and Roland Krummenacher (@RolandKrummenacher)'s last comment above proposing the actual gate-relaxation fix, which was never implemented).Picking up the design from that comment: relaxing the
tmp_MissingPricesgate to drop theisnotempty(x_SkuOfferId)requirement (keepingx_SkuOfferIdin the lookup key unchanged, since it's a no-op suffix for MCA and still differentiates EA rows). PR incoming, will link here.This is the same root cause reported independently in #1769 (empty Reservations tab for MCA).
flanakin commentedon Aug 17, 2026
Update: verified the core premise (`OfferId`/`x_SkuOfferId` is blank for MCA, not just a data-quality gap) against Microsoft's own schema documentation before finalizing the fix — see the "Verification" section in #2263. Short version: the MCA cost/usage details schema has no `offerId` field in any of its five published versions, while every EA schema version has it; Microsoft's cost-details field reference scopes `OfferId` to "EA, pay-as-you-go" only; and the EA→MCA migration guide lists it as retired/not-applicable for MCA. Structural, not incidental.
docs: finalize v15 changelog, what's new blurbs, and hubs docs