We Fixed the Definitions, Not the Queries: Adopting Databricks Metric Views

Hello, I'm Hyunki Park from the Data Intelligence Cell in the Tech Division. In this post, I'll share how we introduced Databricks Unity Catalog's Metric Views into the data pipeline of a newly launched game: how we organized our data into layers so that dashboards and analytics agents read the same metric definitions, the design rules we settled on along the way, and the results.

#Background: Metrics That Were Calculated Differently in Every Query

The period right after a game launches is when data requests are at their most numerous and most urgent. There were many sources — snapshots of the game server DB, analytics logs emitted by the client and server, metadata tables — and the conditions were complicated too: which users to exclude from metrics, how to handle games that included bots, which point-in-time values to use. Meanwhile, as many dashboards were built in a short period and analysis requests poured in, each dashboard, and each ad hoc analysis query, was defining these conditions on its own. Each dashboard's dataset computed the aggregations it needed inside its own query. This was a good way to build quickly, but after a few weeks three problems surfaced.

1. The same metric was defined with a different formula in each dataset. Take win rate alone: the condition for identifying games with a decided outcome differed from dataset to dataset, and the criteria for cleansing abnormal data (excluding operator accounts, excluding abnormal users) were broad in some places and narrow in others. Changing a definition meant editing many places, and the moment we missed one, the numbers across charts stopped matching.
2. The cleansing cost before aggregation was paid again every time a dashboard was opened. Game logs often record multiple participants and items for a single event together in nested fields. To calculate a metric, we had to explode these fields into rows, join data from different sources, and filter out duplicates. Because this ran again with every dashboard dataset query, the cleansing query in front of the metric often cost more money and time than the metric query itself.
3. The analytics agent defined metrics on its own every time. When we asked our in-house analytics agent for "the top 10 cookies by win rate last week," it answered by reading uncleansed log data. In the process, the agent decided on a variety of criteria by itself — whether to exclude churned users, whether to set a minimum sample size, which criteria to cleanse by, and so on. We did write these conditions down in the guide documents (skills) the agent reads. But as the conditions attached to each metric grew, the skills got longer, every new condition meant editing a skill, and even then we couldn't fully prevent the tables and conditions from varying slightly from request to request.

The first and second problems could be solved to some extent with human time and effort, but the third could not be solved with our existing data environment alone.

#What Is a Metric View?

A Metric View is a semantic layer feature provided by Databricks Unity Catalog. Its purpose is to store metric definitions in the catalog as view objects, so that dashboards, SQL, and agents alike query that view and read the same values.

A semantic layer is a layer where you give names to metrics such as "win rate" or "matchmaking success rate" and define in one place what data each metric aggregates and how. Consumers don't write the aggregation expressions themselves; they simply select the defined metric fields. It's a long-established concept, seen in forms like BI tool data models, dbt's Semantic Layer, and LookML, and its purpose is to have everyone read the same value from a single definition instead of recalculating the same metric in each place it's consumed.

Databricks' Metric View is an implementation of this concept inside Unity Catalog. Once you define measures and dimensions in YAML on top of a table, consumers call metrics with the measure() function and can read the same definition at any granularity just by changing the group by. With a regular view, the aggregation and grouping granularity are fixed when the view is created; with a Metric View, only the definition is fixed, and the granularity is decided at query time.

Let's look at the difference between the two using win rate as an example. With a regular view, you have to decide the aggregation granularity when you create the view.

create view win_rate_daily_by_mode as
select base_date, mode_id,
  count(*) filter (where is_win) * 100.0 / count(*) as win_rate
from battle_play
group by base_date, mode_id

This view can only answer daily, per-mode win rates. If you need win rate per cookie, you have to create another view with a different group by, and if you take the avg() of this view's daily win rates to get a monthly win rate, days with many plays and days with few plays are weighted equally, giving you the wrong value.

A Metric View, on the other hand, writes the same definition split into dimensions and measures.

# Metric view definition
version: 1.1
source: battle_play
dimensions:
  - name: base_date
    expr: base_date
  - name: mode_id
    expr: mode_id
  - name: cookie_id
    expr: cookie_id
measures:
  - name: win_rate
    expr: count(*) filter (where is_win) * 100.0 / count(*)

-- Example metric view queries
-- Daily, per mode
select base_date, mode_id, measure(win_rate) as win_rate
from win_rate_metric_view
group by base_date, mode_id

-- Monthly
select date_trunc('month', base_date) as base_month, measure(win_rate) as win_rate
from win_rate_metric_view
group by date_trunc('month', base_date)

The win rate definition lives in one place in YAML, and as the example above shows, you can run queries with different GROUP BY clauses. With the earlier daily view, you couldn't use that view for a monthly query, but with a Metric View, even a monthly query doesn't average the daily win rates; it recalculates the win rate from all rows in that month, so the value is accurate.

As mentioned earlier, the semantic layer concept existed before, but it usually lived inside BI tools. As a result, a definition only worked within that tool, and to see the same metric in another tool, in SQL, or in a notebook, you had to define it again. A Metric View, by contrast, lives not in a BI tool but in the data catalog (Databricks' Unity Catalog), so any tool can query the same definition via SQL, and who can see the metric is managed in the catalog the same way as for any other table. That's why dashboards, SQL, notebooks, external BI tools, and agents can all read the same definition. In fact, in its Metric View launch announcement, Databricks puts particular emphasis on that last consumer, as shown below.

Analysts, engineers, executives and now AI agents often interpret the same data differently, resulting in metric drift, conflicting reports and declining trust. In the era of agentic AI, where agents reason over data and act autonomously, fragmented definitions do not just create confusion, they scale it.

Source: Databricks, "Announcing General Availability and Open Sourcing of Unity Catalog Business Semantics" (2026)

That last consumer is also the reason we wanted to use Metric Views. If dashboard performance had been the only goal, a data mart would have been enough. But when the analytics agent needed to analyze data along a wider variety of dimensions than the groupings used in dashboards, it had to query data at a lower layer than the data mart, and when it did, it read uncleansed log data and defined metrics on its own, producing values that diverged from those shown on the dashboards. The line from Databricks' launch announcement — fragmented definitions "scale" confusion — was playing out exactly in our environment. So we needed a layer from which the agent could read definitions, and that layer had to share the same definitions as the dashboards we were using.

#Design: Definitions in One Layer, Cleansing Beneath It

At Devsisters, we had been managing our data warehouse in layers split into Bronze, Silver, and Gold, following Databricks' Medallion Architecture. So where should Metric Views sit?

In terms of role, a Metric View is a consumption layer like Gold, but it differs in that it holds only definitions without fixing the aggregation granularity in advance. The source data for a Metric View could also be the Bronze layer, but as I mentioned earlier, we didn't want to pay processing costs such as handling nested fields over and over at query time. So we decided to put fully processed data in Silver and use Metric Views in a restricted way: like the Gold layer, they query data in Silver.

So we made Metric Views views that read Silver, not tables, classified them as a separate layer named Semantic, and placed it on top of Silver, alongside Gold. In the end, we defined each layer's role to fit our data structure as follows.

#Reducing Query Cost by Reading Pre-Aggregated Results

A regular view re-aggregates from the source table every time it's queried. When Metric Views first launched, they also stored only the definition and not the results, so their default behavior was the same. Since every query read the definition and re-aggregated, we expected that dashboards repeatedly opening the same results would need separate, precomputed Gold tables. However, the materialization feature added later resolved most of this concern. If you specify frequently used dimension combinations, Databricks precomputes and stores the aggregated results for those combinations. I'll call what is stored this way an aggregated materialization. When a consumer queries with that combination, it reads the stored results instead of recomputing the definition. By registering the combinations our dashboards use here, most dashboards rendered fast enough.

That said, this cache doesn't work for every measure. Just as summing daily play counts gives you monthly play counts, it can only be used for measures whose pre-aggregated values remain correct when combined again. Unique user counts and quantiles don't yield monthly values when you sum daily values, so such measures are recomputed at query time from an unaggregated materialization, which stores all the individual, unaggregated rows. As a result, they get slower the longer the query period, so we built Gold tables only for datasets where such metrics need to be viewed quickly and frequently at a fixed granularity. We'll come back to this distinction under the name additivity in the Rules section, and to the criteria for building Gold tables in the Decision section.

#Rules: Distinguishing Additive from Non-Additive

The thing we had to be most careful about when designing Metric Views was the additivity of measures. An additive measure stays correct when you aggregate values from a finer granularity to a coarser granularity by summing them: summing daily play counts gives you the monthly play count. A non-additive measure becomes wrong if you do that. Summing daily unique user counts double-counts users who logged in on multiple days, so the result exceeds the monthly unique user count, and averaging daily medians doesn't give you the monthly median. This distinction determined whether a measure could be pre-aggregated, whether we had to note a summation warning in the measure's comment, and whether consumers could combine results again.

#Define Averages as a Sum and a Count

As long as it's computed at query time, an average defined with avg() is accurate at any granularity. The problem arises when reusing pre-aggregated results. The aggregated cache in a materialization combines pre-aggregated values into a coarser granularity. But averages can't be combined. If you average two daily averages, days with many samples and days with few are weighted equally, and you get a value different from the monthly average. So avg() measures cannot use the aggregated cache and are recomputed from individual rows on every query. Consumers who take daily averages and average them again outside run into the same problem.

Sums and counts, on the other hand, can each be combined. So we defined an average as a sum measure and a count measure, and added a separate composed measure that divides the sum by the count. Combining the sum and count into a coarser granularity in the cache and then dividing gives an accurate average, and whatever granularity a consumer groups by, the division is redone at that granularity.

measures:
  - name: wait_time_seconds_sum
    expr: sum(case when is_success = 'Y' then wait_time_seconds end)
    comment: Total wait time (seconds) — successful matches
  - name: wait_sample_count
    expr: count(*) filter (where is_success = 'Y')
    comment: Wait time sample count — successful matches
  - name: wait_time_seconds_avg
    expr: measure(wait_time_seconds_sum) / nullif(measure(wait_sample_count), 0)
    comment: Average wait time (seconds)
  - name: wait_time_seconds_p50
    expr: percentile_approx(case when is_success = 'Y' then wait_time_seconds end, 0.50)
    comment: Median wait time (seconds) — successful matches [non-additive]

#Use Window Measures for Shares

For a metric like cookie pick rate — "this cookie's play count ÷ total play count for the mode" — the denominator sums over a wider scope than the numerator. Databricks provides window measures for this. If you exclude the cookie axis from the window definition of the denominator measure, the denominator is computed ignoring cookies no matter what granularity the consumer groups by.

dimensions:
  - name: mode_id
    expr: mode_id
  - name: cookie_id
    expr: cookie_id
measures:
  - name: mode_total_play_count
    expr: count(*)
    window:
      - order: cookie_id
        range: all
        semiadditive: last
    comment: Total play count for the mode — denominator ignoring cookies [LOD:exclude=cookie_id]
  - name: pick_ratio
    expr: count(*) * 100.0 / nullif(measure(mode_total_play_count), 0)
    comment: Cookie pick rate (%) — relative to the mode total [LOD:exclude=cookie_id]

However, if the dimension excluded from the denominator (here, cookie) isn't in the consuming query's group by, the numerator and denominator become equal and the result is silently 100% with no error, so we note that axis in the comment.

#What Goes in a Comment

We decided to write only three things in measure comments: what is being counted, population conditions not evident from the name, and a tag indicating whether it's safe to combine. We don't write the formula, because it's already in the definition itself and repeating it would be redundant, and because a longer comment makes it easier to miss the warnings that actually matter.

The reason we note "whether it's safe to combine" separately is that, unfortunately, Metric Views currently have no mechanism to prevent aggregations that shouldn't be combined. You can group by any defined dimension, and nothing checks whether that combination makes sense for the metric. If you group a unique game count, counted per game, by participants' skill tiers, a single game is counted in multiple tiers and the total exceeds the overall count — yet no error is raised, and you get a plausible-looking number. dbt's Semantic Layer lets you declare measures that become wrong when summed along the time axis with non_additive_dimension, and the engine blocks it. As of YAML spec 1.1, Metric Views don't offer such a declaration for regular measures. So we record this constraint in the comment. People and agents both write queries after reading comments, but this information matters especially for agents. A person can ask the author when the meaning is ambiguous, but an agent writes a query right away based only on the names, types, comments, and formulas of dimensions and measures. The formula alone doesn't tell you that summing a count(distinct ...) by month is wrong, so if it's in the comment, the agent re-groups over the monthly range, and if it isn't, the agent just makes its own call.

We used two tags in comments. [non-additive] means that combining aggregated values again gives the wrong result, and [LOD:exclude=<dim>] means that leaving the axis named in <dim> out of the group by gives the wrong result. We don't tag additive measures or ratios built from them. The absence of a tag is itself meant to signal "safe to combine." We put the same tag descriptions in the guide the analytics agent reads, so that the side building Metric Views and the side querying them follow the same rules.

#Decision: Gold Table or Metric View?

Once you introduce Metric Views, the question naturally follows: "If we're pre-aggregating anyway, how is this different from a Gold table, and which should we build?" We got this question for every metric too, and the criteria we used are summarized below.

As the table shows, metrics whose breakdown dimensions aren't decided in advance, and non-additive metrics that must be recomputed depending on granularity, are a fit for Metric Views. Conversely, for tables with a fixed aggregation granularity where speed matters most, a Gold table is simpler and faster. Looking at dashboards alone, Gold tables are sufficient for most charts. The reason we chose Metric Views anyway was the agent. The agent calls metrics with a different combination of dimensions for every question, so Gold tables with fixed granularity couldn't prepare for that in advance.

This also gives us our criterion for building Gold tables. The default is a Metric View on top of Silver. Only when a query that needs speed includes non-additive measures and computing it from the unaggregated materialization isn't fast enough do we turn that query into a Gold table with a fixed granularity. Queries that use only additive measures are covered well enough by the aggregated materialization, so they don't need Gold tables.

#Results: Comparing Costs Before and After the Migration

After introducing Metric Views, cases of the same metric being calculated with different definitions decreased. But did costs also go down compared to when we read logs directly? Since we had moved our dashboards to Metric Views as decided in the previous section, we compared before and after the move. We ran the original queries and the queries migrated to Metric Views once each, cold, with the same parameters, and compared compute and scan volume. For the heaviest dashboard, summing across its datasets, a single run showed a 41% reduction in compute and a 6.7x reduction in scan volume. The magnitude varied by dataset, though. Where log cleansing had been expensive, compute fell to as little as 30% of its previous level; where queries were already light, it was around 80%.

What about the agent? After restructuring the domain knowledge our in-house analytics agent references around Metric Views, we compared before and after using the same set of questions. The tokens and cost spent producing answers dropped by about half, and the number of turns taken to reach an answer dropped by 30%.

The "top 10 cookies by win rate" question mentioned earlier is a representative case. When reading logs directly, the agent first searched for which tables to use, decided on cleansing criteria and win conditions by itself, wrote a long query, and then, suspicious of the results, checked once more. After switching to Metric Views, it finished with a single call to a defined measure. Both answers got the order of the top cookies right, but in the answer from reading logs directly, the agent had chosen the cleansing criteria and win conditions, so the win rate numbers differed slightly from those shown on the dashboard; with Metric Views, the agent produced the same values as the dashboard.

#Follow-Up: What We Learned Operating Metric Views

Of the things we learned while revising Metric Views over the three months after deployment, here I'll summarize only those related to how Metric Views behave.

#If a Filter Consumers Always Apply Isn't in the Cache, the Cache Isn't Used

Nearly every dashboard dataset applied the normal-user flag in a where clause. However, that flag was missing from the aggregated materialization's dimension list. Databricks' query rewrite considers a materialization as a candidate only if it contains the dimensions used not just in the group by but also in the where. When we checked with explain extended, the pre-aggregated cache wasn't being applied, and everything was going to the unaggregated materialization. We added a rule to include filter dimensions that consumers always apply in the aggregated materialization.

#Changing a Dimension Rebuilds the Entire Cache

When the definition changes, a materialization recomputes the full history. Editing only measures doesn't trigger a recompute, but adding or changing a dimension recomputes the full history, so adding a single dimension costs more than you might think. This is because there's no concept of reloading by date as in a typical data mart. As a result, whether the source is a query that can recompute the full history became a design requirement, and when adding a dimension, we came to first weigh whether that axis is really necessary and what a full recompute would cost.

#Non-Additive Metrics Require a Cache of Individual Rows to Be Queryable

Unique user counts and quantiles must be recomputed from individual rows whatever the group by, so an aggregated cache can't cover them. That's why an unaggregated materialization that stores all individual rows is always needed; a setup with only aggregated materializations and no unaggregated one doesn't work. Any query that calls even one non-additive measure is computed entirely from the unaggregated materialization. So on dashboards, we separated frequently viewed non-additive metrics into different charts from additive ones. Dashboards can be split this way because which measures they call in which combinations is decided in advance, but the agent calls different measures for every question, so we couldn't use the same approach there.

#Embedding a Filter in a Measure Removes Consumer Choice

We embedded the condition "only successful matches with no bots" in the wait time measure. As a result, during periods with a high share of bot matches, we couldn't see wait times including bots. But pulling every condition out into dimensions would scatter the metric definition, so we decided to keep conditions that belong to the metric definition (successful matches only) inside the measure, and expose conditions that consumers may want to change depending on the situation (whether to include bots) as dimensions.

#Remaining Work

Once we finished introducing Metric Views, we found we had far too many measures. There are several measures with similar names, and for some, you can only tell which one to use by opening the definition. Early in the migration, we took the position that every number on a dashboard should be retrievable directly from a Metric View, and used the query results of the existing dashboards as the baseline. Every column in the results had to become a dimension or a measure, and our completion criterion was that values before and after the migration matched column by column. This let us migrate quickly and verify immediately, but as a result, the shape of our Metric Views simply followed the shape of the dashboard results. Starting from a single win rate, we got a new measure for every added condition: "win rate excluding bots," "win rate for a specific mode," "win rate based on first place." Anything derived from other measures, such as an index computed by dividing two measures, also became a measure.

In hindsight, it probably would have been better to keep these derived metrics outside the Metric View. A conditional win rate, after all, can be obtained by applying a filter to the win count and play count. So we plan to clean this up so that Metric Views keep only base measures that serve as building blocks for other metrics, and non-additive measures that must be recomputed depending on granularity.

One more thing: the rules we set in this work (additivity checks, comment tags, materialization placement, verification procedures) are hard for people to remember every time. So we're working on moving the process of creating and editing Metric Views itself into a skill for our coding agent. The goal is to mechanically catch YAML that violates the rules above, and to explicitly request review for items that require human judgment, such as the intent behind a metric definition. If the rules don't just live in a document but are checked automatically by a skill, we hope we can expect the same quality when applying the structure in this post to other games.

#Closing

When metric definitions are written separately in dashboard queries, in analysis queries, and in the skill documents agents read, there's no way to stop them from drifting apart little by little. So we gathered our metric definitions into Metric Views and had both dashboards and agents use those definitions as is. The cases where the numbers on a dashboard and the numbers an agent answered with didn't match, forcing us to check which one was right, dropped noticeably. Consolidating definitions was the goal, but by no longer repeating cleansing and aggregation every time, we were able to cut query costs as well.

If you're running into the problem of the same metric returning different values depending on where it's queried, I'd recommend first looking at how to consolidate your metric definitions in one place before fixing each query. Thank you.

Devsisters is hiring.

We are looking for talented [Tech Division] Analytics Engineer (DW) to join us.

Visit our careers site for details.

View openings