Optimization Engine: KQL join patterns that can inflate counts and double-count cost

[RolandKrummenacher]

🐛 Problem

A repo-wide review of KQL join/lookup usage (see PR #2225) found three join-correctness patterns in the Optimization Engine that can silently inflate counts or double-count cost. They were intentionally left out of that PR because the Optimization Engine is a separately maintained surface and the fixes deserve their own focused review.

1. Anti-joins emulated as leftouter + isempty can inflate every tile (~140 join sites)

src/optimization-engine/views/workbooks/recommendations.json filters suppressed recommendations in ~35 tiles with chains of:

| join kind=leftouter ( ActiveGlobalSuppressions ) on RecommendationSubTypeId_g
| where isempty(RecommendationSubTypeId_g1)
| join kind=leftouter ( ActiveSubscriptionSuppressions ) on RecommendationSubTypeId_g, SubscriptionGuid_g
| where isempty(RecommendationSubTypeId_g2)
...

If more than one suppression row matches the same recommendation (e.g., two rows in ActiveInstanceSuppressions for the same subtype + instance), the leftouter duplicates the recommendation row before the isempty filter runs, and rows that survive earlier stages are already multiplied — inflating every downstream count() and sum(savings).

Fix: join kind=leftanti is semantically the intended operation, cannot fan out, and never materializes the right-side columns. Same defect in Recommend-VMOptimizationsToBlobStorage.ps1 (~L163), Recommend-AADExpiringCredentialsToBlobStorage.ps1 (~L154), and views/workbooks/identities-roles.json.

2. Subscription-name dimension joins without dedup (38 sites)

Every Recommend-*.ps1 runbook enriches results with:

| join kind=leftouter (
    $subscriptionsTableName
    | where TimeGenerated > ago(1d)
    | where ContainerType_s =~ 'microsoft.resources/subscriptions'
    | project SubscriptionGuid_g, SubscriptionName = ContainerName_s
) on SubscriptionGuid_g

None of the 38 subqueries dedup the dimension. Two ingestions inside the 24h window (re-run, catch-up) duplicate every fact row. The same shape (with distinct over the whole selected time range instead of latest snapshot) repeats across views/workbooks/*.json, where a renamed subscription yields ≥2 dimension rows and over-counts every "by subscription" chart.

Fix: | summarize arg_max(TimeGenerated, ContainerName_s) by SubscriptionGuid_g inside the subquery; ideally also switch these fact-to-small-dimension joins to lookup.

3. StorageReplication fan-out double-counts cost

src/optimization-engine/views/workbooks/blockblobstorage-usage.json ("Storage Accounts List" family) joins:

| join kind=leftouter (StorageReplication) on ResourceId   // StorageReplication = ... | distinct ResourceId, Replication

A storage account whose replication changed inside the lookback window (or with meters under two replication types) yields multiple dimension rows, fanning out the fact rows so sum(FullCost) double-counts. The sibling StorageSize/StorageTransactions subqueries are correctly one-row-per-key.

Fix: reduce StorageReplication to one row per ResourceId (e.g., arg_max by ingestion time).

ℹ️ Additional context

• PR Fix KQL join pitfalls and adopt lookup for dimension enrichment #2225 fixed the only two bare joins in the Optimization Engine (Recommend-SqlDbOptimizationsToBlobStorage.ps1) so the new KqlJoinKinds.Tests.ps1 lint (explicit kind= required repo-wide) starts clean for this surface. Everything above uses explicit kinds and is therefore not caught by that lint — these are semantic fixes.
• Full findings list (with per-file line references) is in the review that produced PR Fix KQL join pitfalls and adopt lookup for dimension enrichment #2225.