This route decides where the definition of revenue lives, and therefore which number is right when two reports disagree. It is the last foundation decision and the one most often made by accident — by a report author, in a hurry, in DAX. The technical content is one YAML file; the expensive part is deciding that there is exactly one of them.
Stand 29.07.2026
Defining revenue once
A metric view is a Unity Catalog object carrying dimensions and measures but no grain. It aggregates at whatever grain the query asks for, which is why one definition serves a monthly board pack and a per-customer drill-down.
The metric view sits over gold.fct_order_line and is deployed as DDL from the repository, like every other object. Business rule 1 — revenue excludes cancelled lines — is enforced once, in the filter, rather than in each of the eleven reports that would otherwise each have to remember it.
sqlgold/mv_sales.sql — the whole object
CREATE OR REPLACE VIEW ${catalog}.gold.mv_sales
WITH METRICS
LANGUAGE YAML
COMMENT 'Sales metrics over gold.fct_order_line. No grain: measures aggregate at query time.'
AS $$
version: 1.1
source: ${catalog}.gold.fct_order_line
filter: status <> 'CANCELLED' # business rule 1, enforced once
joins:
- name: customer
source: ${catalog}.gold.dim_customer
on: source.customer_sk = customer.customer_sk AND customer._tech_is_current
cardinality: many_to_one
rely:
at_most_one_match: true
fields:
- name: country
expr: customer.country
display_name: Land
comment: 'DE | AT | CH | NL'
- name: customer_segment
expr: customer.customer_segment
display_name: Kundengruppe
comment: 'A | B | C'
- name: region
expr: source.region_sk
display_name: Vertriebsregion
comment: 'surrogate key on the fact - the label lives in gold.dim_sales_region and is deliberately not joined'
- name: order_date
expr: CAST(order_ts AS DATE)
display_name: Auftragsdatum
- name: month
expr: DATE_TRUNC('MONTH', order_ts)
display_name: Monat
measures:
- name: revenue
expr: SUM(amount_eur)
display_name: Umsatz (EUR)
- name: order_count
expr: COUNT(DISTINCT order_id)
display_name: Auftraege
- name: average_order_value
expr: SUM(amount_eur) / NULLIF(COUNT(DISTINCT order_id), 0)
display_name: Durchschnittlicher Auftragswert
- name: active_customers
expr: COUNT(DISTINCT CASE WHEN source.customer_sk <> 'UNKNOWN'
THEN source.customer_sk END)
display_name: Aktive Kunden
$$;
Three details there are not cosmetic. order_count is COUNT(DISTINCT order_id), not COUNT(1), because the source grain is the order line. active_customers excludes the UNKNOWN member that business rule 7 guarantees will be present. And region_sk and customer_sk exist on both the fact and the dimension, so they carry the source namespace — unqualified they are ambiguous.
Identifiers stay ASCII lower_snake_case; the German the business actually says lives in display_name. One model, two vocabularies, no umlauts in a column name.
sqlReading it — measures come out through MEASURE()
-- monthly revenue by country, from a workspace bound to one catalog
SELECT
country,
month,
MEASURE(revenue) AS revenue,
MEASURE(order_count) AS orders,
MEASURE(average_order_value) AS aov
FROM gold.mv_sales
WHERE month >= '2026-01-01'
GROUP BY country, month
ORDER BY country, month;
-- same definitions, different grain, no second object
SELECT customer_segment, MEASURE(active_customers) AS customers
FROM gold.mv_sales
GROUP BY customer_segment;
Why here
The failure this prevents is the one every warehouse eventually has: four reports, four revenue numbers, no arbiter. One excludes cancelled lines, one excludes them only after the shipping date, one forgot. Each was defensible when it was written. Together they cost the warehouse its credibility, which is the entire product.
The second reason is Genie. A metric view is a data source Genie can read, and its measures hand Genie a fixed, correct expression instead of an invitation to write SUM(amount_eur) over everything including the cancellations. Natural-language questions are where an undefined metric does most damage, because nobody reviews the SQL.
Doing it
Deploy the metric view as DDL from the repo, in the same bundle as the tables it reads — one edited in the UI is invisible to code review.
Write business rule 1 into the filter, then delete the equivalent WHERE clause from every dashboard query that carries one.
Add the metric view, not gold.fct_order_line, as the Genie data source, so 'what was revenue in March' cannot be answered by an ad-hoc SUM.
Point one existing report at MEASURE(revenue) and diff it against today's number. That difference is your first real conversation with the business.
Embedding it
Ask the team to produce the current definition of revenue from the BI tool before you write any YAML. Watching them fail to find one, or find three, is the argument — you do not have to make it.
Have an analyst, not an engineer, write the second measure. If the YAML is only writable by the data team, the semantic layer has become another ticket queue.
In review, ask 'which report changes when this merges?' A measure nobody can name a consumer for is a measure nobody is using.
Let them add a measure that is subtly wrong and catch it with the SQL oracle. The oracle is more convincing after it has caught something.
Defaults
One metric view per business process, not per report. Sales is one; procurement is another.
Measures always through MEASURE(); never a materialised copy of a measure in a table.
Business rules live in the filter, once, with the rule number in a comment.
display_name carries the local-language label; the identifier stays ASCII.
Gotchas
Joining the fact to an SCD2 dimension without pinning to the current version. customer_sk is sha2(customer_id), so it repeats once per validity interval in gold.dim_customer. Drop the _tech_is_current predicate and every line fans out to that customer's version count — revenue does not look broken, it looks good, and it grows every month as more customers acquire a second version.
rely: at_most_one_match: true is a promise to the optimizer, not a check. If the join does match more than once you get no error, only the wrong number faster.
COUNT(1) where you meant COUNT(DISTINCT order_id). gold.fct_order_line is one row per line, so the two differ by the average lines per order — 'orders' silently becomes 'lines' in every KPI tile downstream, and because the ratio is stable month to month the number looks plausible rather than broken. You catch it by counting rows in silver.sales_order and comparing, not by looking at the tile.
Counting the UNKNOWN member as a customer. Rule 7 sends every unresolvable line to customer_sk = 'UNKNOWN', so active_customers gains one phantom customer permanently and the drill-down shows a customer with no name.
NULL in the filter column. status <> 'CANCELLED' is three-valued: a row whose status is NULL is neither kept nor reported as excluded, it silently leaves revenue altogether. If status is nullable, write status IS DISTINCT FROM 'CANCELLED' — and reconcile MEASURE(revenue) against a plain SUM(amount_eur) over the fact, because the gap is the only place the dropped rows show up.
Grouping by region and getting surrogate keys. region is source.region_sk: the fact carries the key, not the name, and this view deliberately does not join gold.dim_sales_region for the label. A slicer built on it lists surrogate keys and the business assumes the report is broken. Either denormalise the region name onto gold.fct_order_line in gold, or let the report join the dimension — what you must not do is add a second join here, which is what closes the BI-compatibility path below.
Editing the YAML in Catalog Explorer at 17:00. It works, so nobody redeploys — and either the next deployment silently reverts it, or it does not and the repo no longer describes production.
Four limits decide whether a metric view can carry your BI layer at all. None is a defect; all of them are things a client discovers in week eight if you do not say them in week two.
Databricks-only. The definition is evaluated by the Databricks SQL engine, so any consumer reading a copy of the data — an Excel extract, a Tableau extract, a Fabric model over a shortcut — is outside the semantic layer for good.
Join limits. Joins are LEFT OUTER and many-to-one. A single aggregate expression may only reference columns from one source, so a measure that multiplies source.quantity by a column of the joined gold.dim_customer cannot be written at all — that arithmetic has to happen in gold.fct_order_line first. Joined tables cannot carry MAP columns.
Every time grain is explicit. There is no free date hierarchy: month exists because it is written above, quarter and week do not exist until someone writes them, and filtering on month filters the truncated value, not order_ts.
Measures are not columns. You cannot GROUP BY one, put one in a slicer, or reference one without MEASURE(); a measure in a GROUP BY returns METRIC_VIEW_MEASURE_IN_GROUP_BY.
The last limit pays for itself. average_order_value is non-additive — the AOV of Germany plus the AOV of Austria is not the AOV of both. Because it is a measure and not a column, nothing can sum it: the engine re-evaluates SUM(amount_eur) / COUNT(DISTINCT order_id) at whatever grain was asked for. Materialise that figure into a table and the protection is gone.
Why here
Stated early, the limits are design inputs. 'Every grain is explicit' sounds like a footnote until a controller asks for a rolling 13-week view and discovers that week was never defined — at which point the semantic layer looks unfinished rather than deliberate.
The Databricks-only limit decides architecture. If the finance team's real reporting surface is an Excel workbook refreshed from an Import model, the metric view can be perfect and not be in the path of a single number anyone reads.
Doing it
Define every time grain the business asks for by name, and refuse to add one that has no named consumer.
Keep the join count as low as the fact allows; region_sk is denormalised onto gold.fct_order_line precisely so the common case needs none.
Establish what the actual reporting surface is before committing to metric views — if it is an extract, write that into the concept rather than discovering it at UAT.
Benchmark the slowest realistic dashboard query before promising response times; materialization is a preview and cannot be assumed.
Embedding it
Give the team the four limits and ask which one bites this client first. The answer tells you where the risk is, and it comes from them.
Have someone try to sum average_order_value and watch the engine refuse. A limit that has been felt is remembered; a limit that was read is not.
When a request needs a grain that does not exist, make the team add the field rather than adding it for them. It is a four-line pull request and it establishes that the semantic layer is theirs to extend.
Defaults
Non-additive figures — ratios, averages, distinct counts — exist only as measures, never as columns in a table.
Time grains are declared explicitly and reviewed once a quarter against what reports actually use.
Joins in a metric view stay under three; beyond that, conform in gold instead.
Gotchas
Materialising a ratio into gold.agg_revenue_month so a dashboard is faster. Someone shows it across four regions and the tile sums four averages: plausible, always wrong, and wrong by more the more rows are on screen.
Assuming a date hierarchy exists. A BI tool that offers drill-down on a date column offers nothing on a metric view field called month — there is no year above it unless you defined one.
Adding a join to reach one label. Every join narrows which BI paths can consume the view later, and a metric view carrying joins is rejected outright by BI compatibility mode with METRIC_VIEW_JOIN_NOT_SUPPORTED.
Quoting materialization in a sizing estimate. It is Public Preview; if it is not enabled on the client's workspace, the dashboard you promised at two seconds runs at twelve.
SQL, notebooks, AI/BI dashboards, Genie, any tool that sends SQL to a warehouse
Power BI reports and Excel pivots on that model
everything, inconsistently
Tested by
a SQL oracle in CI
nothing, in practice
the half that has a test
Cost of a new measure
one PR, live for every consumer at once
one file, live for that model's consumers
twice — and the second one is forgotten
Breaks when
a tool cannot emit MEASURE()
a second tool needs the same number
the two numbers differ and nothing can arbitrate
Both is not the safe middle. It is the only option with no answer to 'which one is right'. Two definitions agree on the day they are written and diverge on the first exception — a returns rule, a change to how cancellations are dated, a new country. Six weeks later finance reports 4.31 M, the AI/BI dashboard says 4.29 M, and nobody knows which is correct because there is no third definition to check them against.
Genie makes this concrete: Genie reads the metric view and cannot read DAX. The moment revenue is defined in the BI model, every natural-language question is answered from a different definition than the reports use — fluently, in a sentence, with no SQL for anyone to review.
One version of 'both' is legitimate: the BI model defines zero measures and exists only as a passthrough — connectivity, not semantics. Write that into the developer concept in those words, because the drift always starts with one harmless local measure.
Why here
The choice is usually made by default and downward. Nobody decides, so the first report author defines revenue in DAX because that is the tool in front of them, and by the time anyone asks there are forty measures and a migration nobody will fund — DAX does not translate mechanically into SQL. Deciding explicitly in week two costs one meeting, and it determines whether the warehouse is the source of truth or merely the source of data.
Doing it
Make the decision in writing, in the developer concept, with a date and a named owner.
If metric views win: state that the BI model defines no measures, and check it — an unexpected DAX measure is a review finding, not a preference.
If the BI model wins: say so honestly, stop building metric views, and accept that Genie and notebooks will not see those definitions.
Count the measures already living in DAX before choosing. The number decides whether this is a decision or a migration.
Embedding it
Run the divergence live: compute one KPI from the metric view and from the existing report, side by side, in front of both teams. Whatever the difference turns out to be, it ends the debate faster than any slide.
Have the BI developer, not the data engineer, present the decision to the business. Ownership of the semantic layer sits with whoever answers for the numbers.
Put 'no new DAX measures' on the review checklist and let the team enforce it on each other. The first time an analyst declines a colleague's DAX measure, the decision has landed.
Defaults
One definition per measure, in one place, owned by a named person.
If both layers exist, one of them defines nothing or is generated from the other.
The choice is written down with its date — a decision nobody can find gets remade quarterly.
Gotchas
'We will keep them in sync.' Nobody has ever kept two semantic layers in sync, because divergence happens through an exception added under time pressure in whichever layer's owner is in the room.
A Power BI model that starts as a passthrough and adds 'just one' local measure. That measure is the fork, and it is the one nobody documents, because it was meant to be temporary.
Choosing metric views while the business's real reporting surface is an Excel pivot on an Import model. The semantics are perfect and reach nobody, and you find out at UAT.
Leaving the decision to the BI team by not raising it. It gets made in DAX by default within two sprints, and reversing it is a reconciliation project rather than a refactor.
applied once, to the identity that ran the refresh — every reader then sees that snapshot
applied per query, per reader
Warehouse cost
one load per refresh
every page render and every slicer click
Metric views
only as a fixed Native query at one grain, which discards the point of the metric view
the documented path: Native query with MEASURE()
Fits
a stable monthly report over a small model
a governed model where who-sees-what matters
The governance row is the one that gets missed. Import copies data out of Unity Catalog: the copy is produced under one identity, so the mask that hides email from support staff is applied at refresh time to the refreshing principal and never again — whatever lands in the cache is visible to anyone who can open the report. DirectQuery keeps every read inside UC, where the policy is evaluated per query. Dual mode covers the middle: with DirectQuery as the default, small dimensions like gold.dim_date come from cache while the fact stays live.
For tools that can run initial SQL — Tableau and Sigma among them — BI compatibility mode is the documented path, with a rule that reads like a joke and is not: leave every measure column's aggregation set to SUM. All aggregation functions are rewritten to the measure definition, so SUM, MIN and MAX return the same correct value, while AVG, distinct count, median, variance and standard deviation produce patterns the rewrite cannot handle.
That path is closed to gold.mv_sales as written, and it is worth saying out loud rather than discovering in a Tableau workbook: BI compatibility mode does not accept a metric view carrying a join, and this one joins gold.dim_customer to reach country and segment. Tableau and Sigma get these numbers through a plain SQL query with MEASURE(), or country and segment get conformed onto gold.fct_order_line so the join disappears. Which of those you pick is a modelling decision, not a connector setting.
Why here
Storage mode is chosen once, by whoever builds the first report, usually as Import because it demos faster. Changing it later means rebuilding every visual that assumed cached data — a foundation decision disguised as a checkbox. The failure it causes is a governance one that surfaces in an audit rather than in a test: a column is genuinely masked in the warehouse and genuinely readable in a file three hundred people can open.
Doing it
Default to DirectQuery for anything carrying personal data from gold.dim_customer, and justify each Import model in writing.
Mark small dimensions Dual once the model is DirectQuery; leave gold.fct_order_line live.
Inventory the reports still using BI compatibility mode and re-point them at Native query rather than waiting for a connector upgrade to break them.
Run one realistic report session and read the cost off system.billing.usage before agreeing a storage mode.
Embedding it
Have the BI developer open a masked column in an Import model themselves. One demonstration retires the argument that Import is fine because the report is only shared internally.
Ask the team to price a week of DirectQuery from query history rather than estimating it. The number is theirs then, and they will defend it.
Make the storage-mode justification a field on the report intake form, so the decision is made by someone who has to write a sentence.
Defaults
DirectQuery by default where UC policies must hold; Import by exception, in writing.
Dual for small dimensions, never for facts.
Native query plus MEASURE(), in DirectQuery, is the metric-view path from Power BI to design for — write it down before someone rebuilds the measures in DAX out of frustration.
Gotchas
Import mode as a governance hole: masks and row filters are evaluated at refresh time under the refreshing identity, so the cached model holds data the reader was never allowed to query. Nothing errors, nothing is logged as a violation, and it surfaces during an access review.
Assuming the Power BI connector still offers BI compatibility mode. Where it has gone the report does not degrade, it stops returning rows, and the connector error names the query rather than the missing option — so it reads as a permissions problem and gets escalated to the wrong team. Check the connector version in the client's own report before promising a date.
Setting a measure column's aggregation to anything but SUM, MIN or MAX in a BI-compat tool. The tool rewrites the aggregation to the measure definition, so AVG, distinct count, median, variance and standard deviation return a number that is not the thing they name, and nothing errors. You notice it only by reading the same measure through MEASURE() in SQL and finding a different figure.
Dragging a measure onto an axis, legend or slicer. The query fails with METRIC_VIEW_MEASURE_IN_GROUP_BY, which is correct behaviour and reads as a broken connector to whoever hit it.
DirectQuery over an unclustered fact table. Every slicer click is a full scan and the report gets blamed; the fix is CLUSTER BY on gold.fct_order_line.
A scheduled refresh at 06:00 asserts that the data is ready at 06:00. Nothing enforces that. The correct trigger is the pipeline finishing successfully, expressed as a task dependency inside the same Lakeflow job.
yamlresources/jobs/dwh_daily.yml — refresh is a dependency, not a schedule
The Power BI task publishes table and column metadata — new columns, comments, relationships — into the semantic model, and in Import mode can additionally trigger the data refresh via refresh_after_update. It needs a Power BI connection in Unity Catalog and USE CONNECTION on it, plus rights on the tables and the warehouse.
Note where the dependency points: at the assertion task, not at the pipeline. A pipeline can succeed and still have loaded a day of wrong numbers. Publishing on green tests rather than on green jobs is the difference between orchestration and confidence.
Why here
Clock-triggered refresh has one failure mode and it is silent. The pipeline runs late, the refresh fires on time, and the report renders perfectly with yesterday's data under today's date. Nothing is red; the incident is found by a human noticing that a number looks familiar. The inverse is as bad: the pipeline fails halfway and the scheduled refresh publishes a partial load — a week's revenue missing a country, presented as complete.
Doing it
Delete the scheduled refresh in the Power BI service once the job task exists. With two triggers, the one you forgot about is the one that runs during an incident.
Point the Power BI task at the assertion task, not at the pipeline task.
Create the Power BI connection in Unity Catalog and grant USE CONNECTION to the job's service principal in the bundle, not by hand in prod.
Alert when the timestamp on the report page disagrees with the latest _tech_loaded_at on gold.fct_order_line.
Embedding it
Have the team break it deliberately: fail the assertion in dev and confirm the refresh does not run. That is the guarantee they will be asked about, and now they have watched it hold.
Let them own the message that goes to the business when a refresh is skipped. Writing it once turns the skip into a process rather than an outage.
Ask in review where each downstream consumer's trigger comes from. Any answer containing a time of day is a finding.
Defaults
One job owns ingest, transform, assert and publish. Downstream refreshes are tasks, never separate schedules.
Publish on assertions passing, not on the pipeline succeeding.
Connections and grants are bundle resources like anything else.
The report shows the source-data timestamp, not the refresh timestamp — different facts, and only one of them matters.
Gotchas
A leftover scheduled refresh in the Power BI service alongside the job task. It fires mid-pipeline and publishes a half-loaded model; the job's own refresh corrects it twenty minutes later, so the incident is unreproducible.
Editing the semantic model in the Power BI service while the task is updating it. The model sticks on 'Pending changes' and the next run fails with an error pointing at the model rather than at the person editing it.
Refreshing after a green pipeline instead of after green tests. Expectations that only warn do not fail a pipeline — the run is green, the data is wrong, and you have just published it.
Missing USE CONNECTION in prod only, because the connection was created by hand in dev. It works everywhere it was tested and fails on the first production run.
An Import refresh timed against a warehouse with aggressive auto-stop. It pays the cold start every run and the report is late by exactly that much, every day, blamed on the network.
Straight to the documentation
The generic platform material lives in the official docs and is not restated here. These are the pages worth having open while you work through this route.
Unity Catalog metric views — the overview and the current status of the Business Semantics area — read before promising a client anything about portability
Metric view YAML syntax reference — field-by-field spec; check the version key and the rely semantics before copying any example, including this one
Query metric views in Power BI — the Native-query-in-DirectQuery path and the list of things that break — check it before every Power BI estimate