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 still the documented path, and it is Beta. It needs a SQL warehouse, or a cluster on DBR 18.0 or later, and it is a per-session config rather than a workspace setting: every new connection has to set it again, so it belongs in the tool's initial-SQL box and nowhere else.
Why
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.
How
sqlInitial SQL — re-sent on every connection, because the setting does not survive one
-- on a SQL warehouse (Tableau / Sigma "initial SQL")
SET metric_view_bi_compatibility_mode = true;
-- on a cluster, which must be DBR 18.0 or later
SET spark.databricks.sql.metricView.bi.compatibilityMode.enabled = true;
-- the view then reads like a table: the tool emits its own aggregation,
-- and the engine rewrites it to the measure definition
SELECT country, month, SUM(revenue) AS revenue
FROM gold.mv_sales
GROUP BY country, month;
Hence the rule that reads like a joke and is not: leave every measure column's aggregation set to SUM. SUM, COUNT, MIN and MAX are all rewritten to the underlying measure definition and therefore all return the same, correct value — while AVG, distinct count, median, variance and standard deviation produce patterns the rewrite cannot handle.
That path is not closed to gold.mv_sales by its gold.dim_customer join, and this is the single most misread restriction in the area. METRIC_VIEW_JOIN_NOT_SUPPORTED is about the consuming query joining the metric view to something else — not about the view's own joins. Country and segment arrive through a join declared inside the definition, and BI compatibility mode consumes them. What fails is a Tableau data-source relationship between mv_sales and a second table, and — less obviously — a Top-N filter, a set, or an LOD expression, all of which compile to a join. The documented remedy for every one of them is the same: model the join in the metric view definition itself.
Doing it
Default to DirectQuery for anything carrying personal data from gold.dim_customerJustify each Import model in writing.
Mark small dimensions Dual once the model is DirectQuery; leave gold.fct_order_line live
Inventory the Power BI reports still bound to the removed BI compatibility mode optionRe-point them at a Native query with MEASURE(), or at an AI/BI dashboard. Those reports do not function — this is a repair list, not a backlog item.
Put the BI compatibility mode SET into Tableau's or Sigma's initial SQL, never into a runbook stepIt dies with the session.
Model any join a Tableau workbook needs into the metric view YAML before the workbook is builtSo nobody reaches for a data-source relationship, a Top-N filter or an LOD to get there.
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 themselvesOne 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 itThe number is theirs then, and they will defend it.
Make the storage-mode justification a field on the report intake formSo 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 forWrite it down before someone rebuilds the measures in DAX out of frustration.
BI compatibility mode is Beta and per-sessionThe SET lives in the tool's initial SQL, and nothing in a signed design depends on it.
Gotchas
Import mode as a governance holeMasks 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.
Planning a Power BI report around BI compatibility modeMicrosoft removed the option from the connector and reports using it no longer function — 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 is escalated to the wrong team.
Setting the BI compatibility mode config once, by hand, and calling it configuredIt is per-session: the next connection — a scheduled extract, a second workbook, a colleague opening the same data source — has it off, and the metric view comes back unqueryable to everyone except the person who tested it.
Running BI compatibility mode against a cluster below DBR 18.0The SET is accepted and the queries still fail, so the investigation goes to the metric view rather than to the runtime version.
Setting a measure column's aggregation to anything but SUM, COUNT, MIN or MAX in a BI-compat toolThose four are all rewritten to the measure definition and all return the same correct value; 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.
A Tableau Top-N filter, set or LOD expression on a metric viewEach compiles to a join, and a query that joins a metric view fails — so the workbook breaks on a filter nobody classified as a join. The fix is in the YAML, not in the workbook.
Dragging a measure onto an axis, legend or slicerThe 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 tableEvery 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.
Why
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.
How
yamlresources/jobs/dwh_daily.yml — refresh is a dependency, not a schedule
resources:
jobs:
dwh_daily:
name: dwh_daily
schedule:
quartz_cron_expression: "0 0 4 * * ?"
timezone_id: Europe/Zurich
tasks:
# ingest -> gate -> publish is the two-pipeline split from the ingestion
# route; the BI tasks hang off the end of it, they do not replace it.
- task_key: ingest
pipeline_task:
pipeline_id: ${resources.pipelines.dwh_ingest.id}
- task_key: gate
depends_on:
- task_key: ingest
sql_task:
warehouse_id: ${var.gate_warehouse_id}
parameters:
catalog: ${var.catalog}
file:
path: ../../tests/sql/gate_silver_to_gold.sql
source: WORKSPACE
- task_key: publish
depends_on:
- task_key: gate
pipeline_task:
pipeline_id: ${resources.pipelines.dwh_publish.id}
- task_key: assert_gold
depends_on:
- task_key: publish
sql_task:
warehouse_id: ${var.gate_warehouse_id}
parameters:
catalog: ${var.catalog}
file:
path: ../../tests/sql/revenue_reconciliation.sql
source: WORKSPACE
- task_key: refresh_power_bi
depends_on:
- task_key: assert_gold # green tests, not merely a green pipeline
power_bi_task:
warehouse_id: ${var.warehouse_id} # required - the task runs on a SQL warehouse
connection_resource_name: pbi_prod
power_bi_model:
workspace_name: Sales
model_name: dwh_sales
storage_mode: DIRECT_QUERY # model default
refresh_after_update: false
tables:
- name: fct_order_line
catalog: ${var.catalog}
schema: gold
storage_mode: DIRECT_QUERY # the fact stays live
- name: dim_date
catalog: ${var.catalog}
schema: gold
storage_mode: DUAL # small dimension, cached
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. warehouse_id is not optional: the Power BI task requires a SQL warehouse. It also needs a Power BI connection in Unity Catalog and USE CONNECTION on it, plus rights on the tables and the warehouse.
Storage mode is declared twice on purpose. power_bi_model.storage_mode is the model default; each entry in tables[] carries its own, and that per-table field is where the route's Dual for small dimensions rule is actually expressed — gold.dim_date goes in as DUAL while gold.fct_order_line stays DIRECT_QUERY. Set only the model default and the advice stays a slide.
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. Note too that the chain in front of it is unchanged — dwh_ingest, the SQL gate, then dwh_publish, exactly as the ingestion route defines it. The BI tasks extend that job; they never collapse it back into one pipeline.
Doing it
Delete the scheduled refresh in the Power BI service once the job task existsWith 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 taskKeep the ingest / gate / publish split in front of it exactly as the ingestion route defines it.
Give the Power BI task a warehouse_id, and give every entry in tables[] its own storage_modeThe model-level default alone does not produce Dual dimensions.
Create the Power BI connection in Unity Catalog and grant USE CONNECTION to the job's service principalDo it 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 runThat 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 skippedWriting it once turns the skip into a process rather than an outage.
Ask in review where each downstream consumer's trigger comes fromAny answer containing a time of day is a finding.
Defaults
One job owns ingest, gate, publish, assert and refresh — two pipelines with tasks between them, never one pipelineDownstream 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 timestampDifferent facts, and only one of them matters.
Gotchas
A leftover scheduled refresh in the Power BI service alongside the job taskIt 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 itThe 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 testsExpectations that only warn do not fail a pipeline — the run is green, the data is wrong, and you have just published it.
A power_bi_task with no warehouse_idThe task requires a SQL warehouse, and because it is the last task in the chain the failure arrives after a fully green load — so it is read as a Power BI or permissions problem rather than a missing field in the bundle.
Setting storage_mode only on power_bi_model and expecting Dual dimensionsEach entry in tables[] carries its own storage_mode; leave it off and every table inherits the model default, so gold.dim_date is served by DirectQuery and every slicer render pays for a dimension that changes once a day.
Missing USE CONNECTION in prod only, because the connection was created by hand in devIt works everywhere it was tested and fails on the first production run.
An Import refresh timed against a warehouse with aggressive auto-stopIt pays the cold start every run and the report is late by exactly that much, every day, blamed on the network.