How we introduced Databricks Unity Catalog Metric View into a new game's data pipeline, separating data cleansing from metric definitions so dashboards and analytics agents read the same metrics.
Hello, I’m Hyungi Park from the Data Intelligence Cell in the Technology Division. In this article, I’ll share how we separated our data structure so that dashboards and analytics agents could read the same metric definitions while introducing Databricks Unity Catalog’s Metric View into a new game’s data pipeline, along with the design rules we established and the outcomes.
Immediately after a game launches is when data requests are most numerous and urgent. There are many sources, including game server DB snapshots, analytics logs emitted by clients and servers, and metadata tables. Conditions are also complex: which users to exclude from metrics, how to handle games containing bots, and which point-in-time values to use. Yet as multiple dashboards were built and analysis requests poured in over a short period, these conditions were being defined and queried independently for each dashboard and each ad hoc analysis query. Each dashboard dataset calculated the aggregations it needed within its own query. This was a good approach for building quickly, but after a few weeks, three problems became clear.
The first and second problems could be addressed to some extent with people’s time and effort, but the third could not be solved with our existing data environment alone.
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 all query the view and read the same values.
A semantic layer is a layer that assigns names to metrics such as “win rate” and “match success rate,” and defines in one place which data each metric aggregates and how. Consumers can simply select defined metric fields instead of writing aggregation formulas themselves. This is a long-standing concept used in forms such as BI tool data models, dbt’s Semantic Layer, and LookML. Its purpose is to let everyone read the same once-defined value rather than recalculate the same metric separately at every consumption point.
Databricks Metric View implements this concept inside Unity Catalog. Once measures and dimensions are defined in YAML over a table, consumers can call metrics with the measure() function and change only group by to read the same definition at any granularity. A regular view fixes both the aggregation and grouping level when it is created, whereas a Metric View fixes only the definition and determines the level at query time.
Let’s look at the difference using win rate as an example. With a regular view, you must choose the aggregation level when creating 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 answer only daily, mode-level win rates. If you need win rates by cookie, you must create another view with a different group by. And if you calculate monthly win rate by applying avg() to this view’s daily win rates, days with many plays and days with few plays receive equal weight, producing an incorrect value.
A Metric View, by contrast, separates the same definition 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, by 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 is written once in YAML, and as the examples show, queries can use different group by clauses. The earlier daily view could not be used for monthly queries, but a Metric View recalculates win rate over all rows in the month rather than averaging daily win rates, so monthly results are accurate.
As mentioned earlier, semantic layers existed before, but they usually existed as concepts within BI tools. As a result, definitions applied only within that tool, and the same metric had to be redefined to view it in another tool, in SQL, or in a notebook. A Metric View, however, resides not in a BI tool but in the data catalog, Databricks Unity Catalog. This means any tool can query the same definition through SQL, while the catalog manages who can view the metric in the same way it manages other tables. Therefore dashboards, SQL, notebooks, external BI tools, and agents can all read the same definition. In fact, Databricks particularly emphasizes this last consumer in its Metric View launch announcement:
Analysts, engineers, executives—and now AI agents—frequently interpret the same data differently, resulting in metric drift, conflicting reports, and a loss of trust. In an era where agents reason over data and act autonomously, dispersed definitions do more than create confusion; they amplify it.
Source: Adapted from Databricks, "Announcing General Availability and Open Sourcing of Unity Catalog Business Semantics" (2026)
Our reason for using Metric View also lies with this last consumer. If dashboard performance had been our only goal, a data mart would have been sufficient. But analytics agents sometimes need to analyze data using criteria more varied than the group by criteria used in dashboards, so they need to query data at a lower layer than a data mart. When agents read uncleansed log data and define metrics themselves, the values diverged from those shown in dashboards. The launch announcement’s statement that “dispersed definitions amplify confusion” was being reproduced exactly in our environment. We therefore needed a layer where agents could read definitions, and that layer had to share definitions with the dashboards in use.
At Devsisters, we had been managing our data warehouse in Bronze, Silver, and Gold layers following Databricks’ Medallion Architecture. So where should Metric View reside?
By function, Metric View is a consumer layer like Gold, but differs in that it contains only definitions without fixing aggregation granularity in advance. Meanwhile, Metric View source data could be in the Bronze layer. However, as noted above, we did not want to repeatedly pay processing costs such as handling nested fields at query time. We therefore placed fully processed data in Silver and decided to use Metric View in a limited form that queries Silver data, just like the Gold layer.
So we treated Metric View not as a table but as a view that reads Silver, classified it separately as a layer named Semantic, and placed it alongside Gold above Silver. Ultimately, we defined each layer’s role to suit our data structure as follows.
| Layer | Role |
|---|---|
| Bronze | Raw source data as-is. We do not modify it. |
| Silver | Data consistency. Expand nested fields, combine records left in different sources, remove duplicates, and add cleansing flags. All values read by measure logic are created here. Metrics are not calculated here. |
| Gold | Consumption optimization. Physical tables precomputed at fixed aggregation levels. We create them only when metrics that cannot be made from pre-aggregated caches, such as unique user counts or quantiles, must be viewed quickly at fixed levels. Other metrics belong in Metric View. |
| Semantic (Metric View) | Business meaning. Define measure formulas and dimensions in one place. |
A regular view re-aggregates from the source table every time it is queried. When Metric View was first released, it likewise stored only definitions rather than results, so its default behavior was the same. Since it reread definitions and recalculated aggregations on every query, we expected to need separate, precomputed Gold tables for dashboards that repeatedly opened the same results. But the subsequently added materialization feature resolved most of this concern. If you specify frequently used dimension combinations, Databricks precomputes and stores aggregation results for those combinations. We will call these stored results aggregated materialization. When consumers query using that combination, they read the stored results instead of recalculating the definition. Registering the combinations used by dashboards here made most dashboards render sufficiently quickly.
This cache does not apply to every measure, however. It can be used only for measures whose pre-aggregated values remain correct when summed again at a higher level, just as summing daily play counts produces monthly play counts. Unique user counts and quantiles do not become monthly values when daily values are summed, so such measures are recalculated at query time from unaggregated materialization, which stores all unaggregated individual rows. They therefore become slower as the query period grows longer. We created Gold tables only for datasets where these metrics had to be viewed frequently and quickly at a fixed level. We revisit this distinction under the name additivity in the Rules section, and the criteria for creating Gold tables in the Decision section.
The most important consideration when designing Metric Views was the additivity of measures. An additive measure remains correct when values at smaller levels are summed to produce a larger-level value. Summing daily play counts produces monthly play counts. A non-additive measure does not work this way. Summing daily unique user counts double-counts users who logged in on multiple days and produces a value larger than the monthly unique user count; averaging daily medians does not produce a monthly median either. This distinction determines whether a measure can be pre-aggregated, whether a note about summation needs to be included in the measure comment, and whether consumers may recombine results.
| Type | Example metrics | Example formulas | Can it be summed? | Can it be pre-aggregated? |
|---|---|---|---|---|
| Additive | Play count, win count, total wait time | count(*), sum(...), count(*) filter (where ...) | Safe to sum at any level | Yes. Put it in aggregated materialization |
| Non-additive | Unique user count, median wait time | count(distinct ...), percentile_approx(...) | Incorrect when summed. Must be recalculated from individual rows | No. Calculated every time from unaggregated materialization |
As long as an average is calculated at query time, defining it with avg() is accurate at any level. The problem arises when pre-aggregated results are reused. An aggregated materialization cache combines pre-aggregated values into larger levels. But averages cannot be combined. Averaging two daily averages gives days with large and small sample sizes equal weight, producing a value different from the monthly average. For this reason, an avg() measure does not use the cache and is calculated from individual rows every time. The same problem occurs if consumers receive daily averages and average them again externally.
Sums and counts can each be combined. Therefore, we defined averages as a sum measure and a count measure, then added a separate combined measure that divides the two. Summing the sum and count to a larger level in the cache and then dividing produces an accurate average, and whatever level consumers group by, the division is performed again at that level.
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]
For a metric such as cookie pick rate, defined as “this cookie’s play count ÷ total play count for the mode,” the denominator is aggregated over a broader scope than the numerator. Databricks provides window measures for this. If you exclude the cookie axis in the denominator measure’s window definition, the denominator is calculated while ignoring cookies regardless of how the consumer groups the query.
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 mode play count — 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 mode total [LOD:exclude=cookie_id]
However, if the dimension excluded from the denominator—cookie in this case—is absent from the consumer query’s group by, the numerator and denominator become the same and silently produce 100% without an error. We therefore document that axis in the comment.
We decided to include only three things in measure comments: what is counted, population conditions not evident from the name, and tags indicating whether values may be summed. The formula is already in the definition, so repeating it would be redundant. We also avoid lengthy comments because they can obscure the warnings that actually need attention.
We document “whether it may be summed” separately because Metric View currently has no feature that prevents invalid aggregation of measures that must not be combined. You can group by any defined dimension, and it does not check whether that combination matches the metric’s meaning. If you group a unique game count, which is counted at game level, by participant skill tier, one game can be counted in multiple tiers and the sum may exceed the total. No error occurs, and the result can look plausible. dbt’s Semantic Layer lets you declare measures that become invalid when summed across a time axis as non_additive_dimension, allowing the engine to prevent this. As of YAML specification 1.1, Metric View does not offer such a declaration for regular measures. We therefore note this constraint in comments. Both people and agents read comments before writing queries, but this information is especially important for agents. People can ask the creator when meaning is unclear, whereas agents immediately write queries based only on dimension and measure names, types, comments, and formulas. A formula alone does not tell the agent that summing count(distinct ...) across months is incorrect. If the comment says so, it regroups over the monthly range; if it does not, the agent makes its own judgment.
We used two comment tags. [non-additive] means that summing already aggregated values is incorrect, while [LOD:exclude=<dim>] means that removing the axis specified by <dim> from group by is incorrect. We do not tag additive measures or ratios built from them. The absence of a tag itself is intended to signal that combining them is safe. We also added explanations of these tags to the guide read by our analytics agent so that Metric View creators and consumers follow the same rules.
Once Metric View is introduced, a natural question follows: “If both pre-aggregate data anyway, how is this different from a Gold table, and which should we build?” We were asked this for each metric as well, and the criteria we used are summarized below.
| Criterion | Gold table | Metric View |
|---|---|---|
| Query speed | Fastest because it is already aggregated at a fixed level | Comparable when aggregated materialization fits; otherwise re-aggregates from unaggregated materialization |
| Dimension flexibility | Can be viewed only at the level chosen during creation. A new axis requires a new table | Can be viewed with any combination of defined dimensions |
| Non-additive metrics (distinct, quantiles) | Accurate only at the fixed level. Cannot be re-aggregated to another level | Recalculated from individual rows regardless of grouping level |
| Consumers | Suitable for queries with the same aggregation level. Multiple dashboards can share it when their levels match | Suitable when multiple dashboards, analysts’ ad hoc queries, and agents need to share the same definition regardless of level |
| Cost of adding metrics | Adding a column requires reloading that column for the entire period; a new level requires a new table | Adding a measure to YAML makes it immediately available without reloading. Changing dimensions recalculates the entire cache |
| Relationship with agents | Since columns are already calculated values, formulas must be separately written into the agent guide | Agents directly read Metric View definitions, including formulas and comments, so formulas do not need to be copied into guides |
As the table shows, Metric View is the right fit for metrics whose dimensional breakdowns are not determined in advance and for non-additive metrics that must be recalculated depending on granularity. Conversely, when aggregation level is fixed and speed is paramount, a Gold table is simpler and faster. Looking only at dashboards, Gold tables are sufficient for most charts. We chose Metric View nevertheless because of agents. Because agents call metrics using different dimension combinations for every question, we could not prepare in advance with Gold tables that fix the level.
This also establishes the criteria for creating Gold tables. The default is a Metric View over Silver. We make a query into a Gold table with a fixed level only when it needs speed, includes non-additive measures, and is not fast enough when calculated from unaggregated materialization. Queries using only additive measures do not need Gold tables because aggregated materialization is sufficient.
After introducing Metric View, instances of calculating the same metric with different definitions decreased. But did costs also decline compared with directly reading logs? We moved dashboards to Metric View according to the criteria from the previous section, then compared before and after. We ran the existing queries and their Metric View versions cold once each with the same parameters, comparing compute and scan volume. For the heaviest dashboard, the combined dataset results showed a 41% reduction in compute and a 6.7x reduction in scan volume per execution. The magnitude varied by dataset, however. Compute fell to 0.3x where log-cleansing costs had been high, while it was around 0.8x where workloads had already been light.
What about the agent? After rebuilding the domain knowledge consulted by our internal analytics agent around Metric Views, we compared before and after using the same question set. Tokens and cost required to answer were roughly halved, while the number of turns needed to reach an answer fell by 30%.
The previously mentioned question, “Which 10 cookies had the highest win rates?” is representative. When directly reading logs, the agent first had to find a table to use, choose its own cleansing criteria and win-determination conditions, write a long query, and then verify the result once more because it seemed uncertain. After using Metric View, it was finished with a single call to a defined measure. Both answers correctly ordered the leading cookies, but the direct-log answer had win-rate figures that differed slightly from dashboard values because the agent selected the cleansing and determination criteria. With Metric View, it could produce the same values as the dashboard.
Of the things we learned while modifying Metric Views during the three months after deployment, the following are limited to how Metric View works.
Almost every dashboard dataset applied the normal-user flag in where. But this flag was missing from the dimensions list of aggregated materialization. Databricks query rewrite requires a candidate materialization to contain not only dimensions used in group by, but also dimensions used in where. We confirmed with explain extended that the pre-aggregated cache was not being used and everything was going to unaggregated materialization. We added a rule to include in aggregated materialization every filter dimension that consumers always apply.
When a definition changes, materialization recalculates the entire period. Editing only a measure does not trigger recalculation, but adding or changing a dimension does, making the cost of adding even one dimension larger than expected. This is because it does not have the notion of reloading data by date like a typical mart. Therefore, whether the source is a query capable of recalculating the entire period became a design condition. When adding dimensions, we began to first consider whether the axis was truly necessary and what the full recalculation cost would be.
Distinct user counts and quantiles must be recalculated from individual rows at any grouping level, so aggregated caches cannot cover them. An unaggregated materialization that stores all individual rows is therefore always required, and a configuration that omits it and has only aggregated materialization is not viable. Any query that calls even one non-additive measure is calculated entirely from unaggregated materialization. For dashboards, we therefore separated frequently viewed non-additive metrics into charts separate from additive metrics. Since dashboards have predetermined measure combinations, this division is possible. Agents, whose requested measures vary by question, cannot use the same approach.
We fixed the condition “only matches without bots among successful matches” into the wait-time measure. This meant that, during periods with a high proportion of bot matches, we could not view wait times including bots. Moving every condition into dimensions would scatter metric definitions, so we decided to leave conditions inherent to the metric definition, such as only successful matches, inside the measure and expose conditions consumers may change depending on circumstances, such as whether bots are included, as dimensions.
After completing the Metric View rollout, we found that we had too many measures. Several have similar names, and some require opening the definition to know which one to use. At the start of the migration, we believed every figure in the dashboard needed to be retrievable directly from Metric View, so we used existing dashboard query results as the basis. Every column in the results had to become a dimension or measure, and we defined completion as matching values column by column before and after migration. This made it possible to migrate quickly and validate immediately, but it also caused the shape of our Metric Views to directly follow the shape of dashboard results. For each win rate, a new measure was created whenever a condition was added: “win rate excluding bots,” “win rate for a specific mode,” or “win rate based on first place.” Values derived from other measures, such as an index dividing two measures, also all became measures.
It would likely have been better to keep these derived metrics outside Metric View. A conditional win rate can be produced by applying filters to win counts and play counts. We are therefore planning to streamline Metric Views so that they retain only foundational measures that serve as ingredients for other metrics, along with non-additive measures that must be recalculated depending on granularity.
Another challenge is that it is difficult for people to remember all the rules established in this work, including additivity decisions, comment tags, materialization placement, and validation procedures. We are therefore moving the procedure for creating and modifying Metric Views itself into a coding-agent skill. Our goal is to mechanically catch YAML that violates the rules, while explicitly requesting review for items requiring human judgment, such as the intent of a metric definition. If rules are not merely documented but automatically checked by a skill, we expect to be able to apply this article’s structure to other games with the same quality.
When metric definitions are written separately in dashboard queries, analysis queries, and skill documents read by agents, it is impossible to prevent definitions from gradually diverging. We therefore consolidated metric definitions in Metric View and had both dashboards and agents use those definitions directly. Cases where we needed to check which value was correct because the dashboard figure and the agent’s answer differed have noticeably decreased. Consolidating definitions was our goal, but query costs also fell because cleansing and aggregation no longer had to be repeated each time.
If you are experiencing the problem of receiving different values for the same metric depending on where it is queried, we recommend first considering how to consolidate metric definitions in one place before fixing each individual query. Thank you.
Please visit our careers site for details!