Measure correctness

A dashboard reported an ARR of $596,348,393. The real figure was $36,384,217. A sixteen-fold overstatement, on a KPI card, in front of a person who was reading it as fact.

Nothing was broken. The SQL was valid, the query ran, every gate passed, and the dashboard was internally consistent: its subtotals reconciled with its totals. The dataset was a lattice, so every cell in it was an exact aggregate computed by the warehouse.

The measure was ending_arr_usd: a point-in-time snapshot of ARR. The dashboard grouped it by tenure month, and the grand-total cell summed it across 24 tenure months. Every customer was counted once per month they existed. Sixteen times.

It was caught only because that author had hand-written a cross-check comparing one total against another.

This page is about the class of error that incident belongs to. It is the most important page on this site, because a wrong number that looks right is the one failure mode Dashies cannot recover from on its own: unlike a query-time BI tool, we ship the only copy of the numbers, so a wrong refresh stays wrong until a human notices.

Additive and non-additive

The property that decides almost everything: can you get the answer for a group of rows by combining the answers for its parts?

• Additive. sum, count, min, max. Revenue for us plus revenue for eu is genuinely revenue for us and eu. These compose over any partition, so they can be re-aggregated safely.
• Non-additive. count(distinct ...), avg, median, percentiles, stddev, variance, mode. Also ratios, and windowed aggregates. Combining the parts gives you a number that is not the answer, and it is usually plausible enough to be believed.

Two measured examples of what non-additive costs when it is re-summed:

• A re-summed distinct count reported 2,503 customers against a true 1,200, because a customer who bought in two regions was counted in both.
• A pre-divided rate re-averaged to 77.5% where the true figure was 38.5%.

What Dashies does about it

A cube dataset re-sums in the browser, so it is only correct for additive measures. Dashies therefore inspects a cube dataset's SQL at publish time and refuses to publish if it finds count(distinct ...), avg, a median, a percentile, stddev, variance, or mode(). The refusal names the construct.

The same check runs earlier, as an advisory, while your AI is validating the query:

For a v4 "cube" dataset this SQL computes non-additive aggregates (avg(...)): a cube dataset re-sums pre-aggregated rows in JS, so these go silently wrong under viewer filters. Use mode "lattice" (exact per cell, no engine to load) when the dimensions are low-cardinality, or mode "rows" (row-level + DuckDB) for row-level detail.

This refusal is the feature, not an obstacle. The other modes exist precisely so you can have the measure anyway. See Datasets and the four modes.

Flow and stock

Additivity is not the whole story, and the $596M incident is the proof: a sum is the most additive aggregate there is, and it was still wrong.

The second distinction is what the number means over time:

• A flow accumulates over a period. New ARR, hires, deposits, tickets opened, orders placed. Summing it across periods is the right thing to do: twelve months of new ARR summed is a year of new ARR.
• A stock is a level measured at one instant. ARR, headcount, an account balance, open tickets, inventory on hand. Summing it across periods counts the same entities once per period and produces a figure that means nothing.

The trap is that no static check can tell them apart. sum(ending_arr) and sum(new_arr) are identical to any analysis of the SQL text. Only you know which column is a level and which one accumulates.

stock: true

Because only the author knows, the author declares it. Mark the measure stock: true in the spec and both the authoring tool and every future publish check it:

measures:
  ending_arr:
    agg: sum
    stock: true
    label: ARR
    unit: { kind: currency, scale: units }

Declaring it during validation produces this, before anything is published:

This cube sums active_count, which is a point-in-time STOCK column (a snapshot of a level at one instant - ARR, headcount, a balance, open tickets) rather than a per-period FLOW. Summing a stock across the time grain recounts the same entities every period, so the total is usually meaningless. For a current value read the latest period instead of sum(); for a trend use a per-period avg/min/max.

It is an advisory, never a rejection, and deliberately so: summing a latest snapshot across a non-time dimension can be exactly what you meant. But it turns an invisible error into a sentence you have to read.

The remedies, in the order you should consider them:

• For a current value, read the latest period rather than summing.
• For a trend, show the stock per period on a chart rather than a rolled-up total.
• If the sum genuinely is intentional because the rows do not overlap, say so and move on.

The displayed-rollup check

There is a second arm, and it exists because of what the $596M dashboard looked like from the inside.

When two datasets compute the same measure with the same aggregate over the same column, and their fully rolled-up values differ, and a tile actually shows the differing one, the publish report says so and gives you four facts per dataset: the aggregate, the column, the scope, and the value.

The display condition is the load-bearing part. In that same playtest, a second dataset had exactly the same fan-out over 18 monthly snapshots and was completely harmless, because every tile bound to it read the measure per month through a date dimension. A rolled-up number nobody renders cannot mislead anybody.

This check reports information and reaches no verdict, because two datasets can legitimately disagree: month-to-date beside year-to-date is the obvious case, where both values are correct and different. Read the scope and decide. The one exception, where the reading is unambiguous and it becomes a warning, is a displayed rolled-up sum of a measure you declared stock: true over a multi-period grain. That is wrong under any scope.

Ratios, not stored averages

The single most common way to bake a wrong number into a dashboard is to store an average.

-- Wrong. Correct at this grain, wrong under every filter.
select region, sum(revenue) / sum(orders) as avg_order_value
from orders
group by region

That column is right for each region as stored. The moment a viewer selects two regions, the browser combines the two stored ratios, and an average of averages is not an average. It is weighted by nothing in particular.

Store the two additive parts and let the ratio be computed at display time:

select region, sum(revenue) as revenue, count(*) as orders
from orders
group by region

measures:
  revenue:
    agg: sum
    unit: { kind: currency, scale: units }
  orders:
    agg: count
    unit: { kind: count }
  aov:
    ratio: { num: revenue, den: orders }
    unit: { kind: currency, scale: units }

A ratio measure is never stored. It is a display of two aggregate measures, so the division happens after filtering, over two numbers that were each summed correctly. The result is exact under any filter.

This is also why an exact average on a cube is a ratio of a sum to a count, literally sum divided by count, rather than an avg column.

If you write the division into the SQL anyway, the semantic layer catches the common shapes:

measure aov divides one aggregate by another, which re-sums wrong under viewer filters (the 77.5%-vs-38.5% class).

What no check can do for you

Every gate above reads the SQL text or the declarations. Two things defeat that completely:

• A ratio or a distinct count hidden inside a CTE or a subquery, where the text check does not see it as the measure's aggregate.
• A fan-out join. If a join duplicates rows, sum(amount) is still a sum, still additive, still passes every rule, and returns a number that is a clean multiple of the truth.

There is no static analysis that fixes this, because whether a join fans out depends on the data rather than the query. So the publish report raises an obligation instead: whenever a cube-family dataset reads more than one row source (a join, a CTE, a comma join, or a derived table), it names the datasets and the construct and asks you to run the check yourself.

The shape of the check is an independent aggregate: the same measure, over the same window, computed against the un-joined base table in your own SQL client, compared against that measure summed over the whole dashboard. If the dashboard is larger, the join fanned out, and every figure on the page is inflated by the same factor.

Read the converse of an obligation carefully, because it is the part people get backwards. An empty obligations list means the dataset reads one row source, so it cannot fan out. It is not a statement that your numbers are right. A mis-declared measure or a stock summed over months on a single-source dataset is still entirely yours to catch.

Two further things worth knowing about what validation does and does not prove:

• Validating the SQL proves it runs, not that it is correct. It proves the statement survives the executor's caps, its confinement, and its timeout, which matters because that executor will run it unattended forever. It says nothing about whether the number means what you think.
• A clean dry run is not a clean bill of health either. It compiles, validates, and seeds every dataset without writing anything, which is a great deal. It does not know what your metrics mean.

One more thing: whose definition is it?

If your company defines ARR in a semantic layer, a dbt metric, or a metrics catalog, that definition is the tested, reviewed, versioned statement of what ARR is. A measure hand-derived from physical tables bypasses all of it.

When the published dashboard then disagrees with a number the business already trusts, the dashboard is the thing that looks wrong, and it will be blamed on Dashies rather than on the definition it quietly reinvented.

So: take the definition from the semantic layer where one exists, per metric rather than once per project, and record where each measure's definition came from. The spec's intent field exists for exactly this and round-trips verbatim, so a later reader can check rather than guess.

Next

Designing the cube is the practical side: writing SQL that stays correct and stays inside the budgets.