The collector from the previous article re-reads a rolling seven day window of cost data every morning, because Azure revises spend figures for days that have already happened. That is the right behaviour and it leaves us with a table where the same day’s cost, for the same service, appears seven times on purpose.

This article is about the layer that turns that back into a number you can put on a wall. It is one saved search, it is about six lines of KQL, and I would argue it is the most important piece of the whole design. Every panel on the FinOps dashboard depends on it, and none of them know it exists.

The problem, stated plainly

Our cost table holds rows like this, simplified:

TimeGenerated CostDate ServiceName Cost
Sep 20 06:00 Sep 18 Azure Kubernetes Service 41.20
Sep 21 06:00 Sep 18 Azure Kubernetes Service 41.55
Sep 22 06:00 Sep 18 Azure Kubernetes Service 41.55

Three rows describing one day’s AKS spend. The first is what Azure believed on the twentieth, the second is a restatement, the third is the same figure re-read and unchanged.

The correct answer for AKS on September the eighteenth is 41.55. Not 124.30.

If you write the obvious query, you get 124.30:

AzureCost_CL
| where CostDate == datetime(2026-09-18)
| summarize sum(Cost)

And here is the thing that makes this a design problem rather than a coding problem. That query does not fail. It returns a number. The number is roughly seven times too big, but it is the right order of magnitude for a cloud bill, it moves in the right direction when spend goes up, and every chart drawn from it has a plausible shape. On a dashboard nobody is auditing against the invoice, it could sit there for a very long time.

The fix, and why it goes in a function

The deduplication itself is not clever:

AzureCost_CL
| summarize arg_max(TimeGenerated, *) by CostDate, SubscriptionId, ResourceGroup, ServiceName, MeterCategory, Environment
| project CostDate, SubscriptionId, SubscriptionName, ResourceGroup, ServiceName, MeterCategory, Environment, Cost, Currency

arg_max(TimeGenerated, *) keeps the whole row belonging to the most recent TimeGenerated within each group. The group is the natural key of the fact: a given day, in a given subscription, in a given resource group, for a given service and meter and environment tag. Whatever the most recent collection said about that combination is the truth.

The question is where that lives. There are three options and only one of them is any good.

Three places the deduplication could live, and why only the saved function works

You could put it in every panel. Twelve panels, twelve copies of the same six lines, and the day you add a dimension to the key you have twelve places to change. Worse, the thirteenth panel that somebody adds next year will not have it, and will be silently wrong.

You could put it in the collector, deduplicating before you write. That does not work, because the duplicates are the point. Tomorrow’s run needs to be able to supersede today’s without deleting anything, and Log Analytics has no delete.

Or you register it as a saved search with a function alias, and panels call it like a table:

resource "azurerm_log_analytics_saved_search" "cost_latest" {
  name                       = "AzureCostLatest"
  log_analytics_workspace_id = data.azurerm_log_analytics_workspace.shared.id
  category                   = "FinOps"
  display_name               = "Azure cost — latest value per day"
  function_alias             = "AzureCostLatest"

  query = <<-KQL
    AzureCost_CL
    | extend Environment = iff(Environment in ("environment", ""), "(untagged)", Environment)
    | summarize arg_max(TimeGenerated, *) by CostDate, SubscriptionId, ResourceGroup, ServiceName, MeterCategory, Environment
    | project CostDate, SubscriptionId, SubscriptionName, ResourceGroup, ServiceName, MeterCategory, Environment, Cost, Currency
  KQL

  depends_on = [azapi_resource.cost_table]
}

A panel then writes AzureCostLatest() | where ... | summarize ... and physically cannot get it wrong. That is the property I want. Not “documented so people remember”, but “the wrong query is not reachable from the dashboard”.

The line that is not obvious

There is one line in there that is not about deduplication and it took a production bug to earn its place:

| extend Environment = iff(Environment in ("environment", ""), "(untagged)", Environment)

This normalises the environment label before the summarize, and the ordering is the entire point.

Recall from two articles ago that the tag handling in the collector was wrong at first, and untagged spend was being written with the literal label environment or with an empty string. We fixed the collector, but the rolling lookback means the fixed collector re-ingests the same days that the broken one already wrote.

Now think about what happens if you normalise after the summarize. The old rows carry Environment = "environment". The new rows carry Environment = "(untagged)". Because Environment is part of the dedup key, those are different groups. arg_max keeps one row from each, and you have double counted every untagged dollar for the entire lookback window. Then you rename them at the end and they merge into one series, at twice the value.

Normalising first gives the legacy and corrected rows the same dedup key, so the re-ingested row supersedes the mislabelled one exactly as intended.

Environment stays in the key otherwise, and that is deliberate too. A resource group holding both tagged and untagged resources legitimately produces two rows for one day, one service and one meter. Dropping the tag from the key would merge two real facts.

This is the kind of detail I would not have thought about in advance. It only showed up because the fix and the historical data overlapped in the same window.

The sharp edge: no let statements

Here is the one that cost me a deploy, and it is worth knowing before you write your first function rather than after.

A saved search used as a function has to be a single tabular expression. If the body opens with a let statement, Terraform will save it perfectly happily. The apply is green. And then every panel that calls it fails at query time:

Function 'GitHubDealsLatest' could not be parsed at 'latest' on line [1,4]

I wrote three functions this way on a later collector. The deploy looked clean, and the dashboards were entirely broken. The error names the function and points at a token inside its body, so at first glance it looks like the function is corrupted rather than that functions cannot start with let.

The pattern I wanted was “find the newest snapshot, then filter to it”:

let latest = toscalar(SomeTable_CL | summarize max(SnapshotDate));
SomeTable_CL
| where SnapshotDate == latest

The version that works inlines the scalar:

SomeTable_CL
| where SnapshotDate == toscalar(SomeTable_CL | summarize max(SnapshotDate))

It costs an extra scan of one column and it keeps the function callable, which is not a difficult trade.

Two related traps in the same area, both of which produce runtime failures from a clean apply.

# starts a comment in HCL everywhere except inside a heredoc, where it is ordinary text. I put explanatory # comments inside a KQL heredoc and they went into the query verbatim. KQL comments start with //.

And a function whose body is fine on its own may still fail when you compose it. Calling a let-prefixed function inside a union leg fails differently from calling it at the top of a query, which sent me looking in the wrong place for a while.

Because all three of those are only wrong at runtime, I ended up writing a check that runs over the function bodies before I plan. It is crude and it has caught two real bugs:

bad = [alias for alias, body in functions
       if any(l.strip().startswith("let ") for l in body.splitlines())
       or any(l.strip().startswith("#") for l in body.splitlines())]
assert not bad

The second time it fired, it caught a function I had written months earlier and never noticed was broken, on a dashboard I had not opened.

What else belongs in a function

Deduplication is the reason these exist, but it is not the only thing worth putting in them, and this is the part I would most encourage you to copy.

Any definition that more than one panel depends on belongs in the function. On our later dashboards that includes things like what counts as a stale deal, what counts as work in progress, and whether an account nobody has ever touched is a follow-up failure or just an import nobody has started on.

The argument is the same as for deduplication. If three panels each decide for themselves what “stuck” means, you have three different answers to one question and no way to tell from the screen which one you are looking at. When somebody asks why the headline number says fourteen and the table below it has nineteen rows, you want the answer to be a bug rather than a philosophical difference between two panels.

So our functions tend to end with a block of extend lines that compute flags, and the panels are mostly where and summarize over those flags:

| extend LastTouch = coalesce(LastActivityDate, CreateDate)
| extend DaysSinceActivity = datetime_diff('day', now(), LastTouch)
| extend IsStale = IsOpen and DaysSinceActivity >= 14

A panel says | where IsStale. The definition of stale is in one place and changing it is one apply.

Proving it works

The thing I like about this design is that it is testable without a dashboard. During one of our later collections a job failed partway through and retried twice, so a table ended up holding four copies of every row. One query against the function:

HubSpotCompaniesLatest | count

returned exactly the number of companies in the CRM. That is the whole contract, and you can check it in ten seconds from a terminal.

For the cost table the equivalent check is to sum a settled month through the function and compare it against the invoice. If those agree you know the deduplication key is right. If they do not, the key is missing a dimension or has one too many, and the direction of the error tells you which.

Conclusion

Append-only storage forces a decision that a database with an UPDATE statement lets you avoid: you have to choose a place where the duplicates get resolved. Doing it in the collector is impossible, doing it in the panels is a slow-motion bug, and doing it in a saved function costs six lines and takes the question off the table permanently.

The part I did not anticipate is how much else wanted to live there once the boundary existed. It started as a deduplication trick and turned into the place where the dashboard’s vocabulary is defined. If you build one of these, spend the extra hour to put your definitions in it rather than in the panels, and write the little check that refuses to let a let statement into the body. The next article is the dashboard that sits on top of this one, which after all this is almost anticlimactic: eight panels, all of them one line long, because the function already did the hard part.