Databricks Unity Catalog integration
Your Unity Catalog metric view defines the metric. KPI Tree explains the movement, names the owner and proves the fix.
Pick a Unity Catalog metric view on your Databricks connection.Pick a Unity Catalog metric view on your Databricks connection. KPI Tree reads every measure it defines, queries each one with MEASURE() so Databricks computes the value, and creates a child metric for every dimension value you care about. Nothing is re-declared. Above the semantic layer it adds what Unity Catalog never carried: which metrics drive which, with statistical confidence, who is accountable when one moves, and whether the last action worked.
sales_mv
main.sales.sales_mv · metric view
version: 1.1
source: main.sales.fct_orders
fields:
- name: region
expr: region
- name: order_date
expr: order_date
measures:
- name: revenue
expr: SUM(order_total)
comment: Gross revenue, completed orders
Revenue
Synced from Unity Catalog£1.28m
Month to date · vs £1.51m last month
Rollup
sum · day, week, month, quarter
Primary driver
Conversion rate · lag 3d
Accountable
Last action
Checkout rollback · verified
What Unity Catalog metric views are, and where they stop.
A Unity Catalog metric view is a governed object that declares business measures over a source table: the dimensions you can group by and the measures, each an aggregation expression, queried with the MEASURE() function so that every consumer computes the same number. It is Databricks' semantic layer. Genie Agents, AI/BI dashboards and any SQL client read the same definition, under the same Unity Catalog grants.
It settles what Revenue is. It does not say why Revenue fell, who owns the driver, or whether the fix worked. Those questions live in a layer above the semantic layer, and that is the layer this integration adds. The definition stays in Unity Catalog and Databricks keeps doing the maths. The causal tree, the owners and the verified outcomes live in KPI Tree, and Canopy hands all of it to your people and your agents.
Governed in Unity Catalog. Explained in KPI Tree. One metric view, the whole loop.
The sync runs on your existing Databricks connection. KPI Tree lists the metric views in the catalog you chose, reads the one you pick with DESCRIBE TABLE EXTENDED AS JSON, and turns every measure into a tracked metric queried with MEASURE() at day grain. Databricks computes every value, so the number here is the number your metric view gives Genie Agents and every other consumer. dbt metrics that build into Databricks can sit on the same tree.
What your metric view does not know is why Revenue fell, who owns Conversion, or whether Monday's release moved it. That is the layer KPI Tree adds: a causal metric tree re-tested every night, RACI ownership on every node, action plans measured against a forecast, and Canopy, the business context layer that hands all of it to your people and to the agents they already use over MCP. Your metric view stays the single source of truth for how a metric is calculated. KPI Tree becomes the source of truth for what to do about it.
Every measure, as defined, nothing re-declared
Measures come across with their expressions and comments, and the dimensions the view declares. The rollup for weeks and months is inferred from each expression, window measures take their latest value, and you can change any of it in KPI Tree without touching the view.
Sync a Unity Catalog metric view
Create Dimension Metrics · onDESCRIBE TABLE EXTENDED main.sales.sales_mv AS JSON
Databricks computes every value. Rollup inferred from each expression.
One query per measure, everything else in memory
Each measure is one MEASURE() query at day grain, returned as Arrow and cached. Comparisons, rollups, breakdowns and the nightly correlation and causality sweep run in KPI Tree’s own engine, so the warehouse only works during a sync.
Warehouse queries · last sync
4 total0 warehouse queries for
period comparisons, weekly and monthly rollups, dimension breakdowns, nightly correlation and causality tests. All computed in memory from those four results.
Dimension metrics, created automatically
Switch on Create Dimension Metrics and every dimension you chose becomes a child metric per value: Revenue (Region: EMEA), Revenue (Region: APAC), and so on, filtered in the MEASURE() query so Databricks still does the maths. Each child gets its own owner, baseline and nightly driver tests.
Revenue
Create Dimension Metrics · ondimensions: region, channel · 5 child metrics created
Each child has its own owner, baseline and nightly driver tests. No YAML changes.
Re-sync as a diff, trees and owners untouched
Every sync is compared with the last. New measures appear, changed ones update in place, removed ones retire with their history kept. Ownership, trees, action history and any rollup you set by hand stay exactly where you left them.
Sync summary
Completed 06:1442
Found
3
Created
38
Updated
1
Archived
0
Skipped
customer__customer_id skipped: 48,211 values, above the 50 value limit
Trees, owners and action history preserved · gross_margin renamed in place
Connected in three steps. The connection you already have, the view you already govern.
Connect a SQL warehouse
- SQL warehouse, or Runtime 16.2 or later
- Read access only, Unity Catalog grants apply
- Enabled per workspace, so ask us to switch it on
Databricks SQL warehouse
Connected · SELECT 1 okPick a view and sync
- Views discovered from the information schema, chosen from a list
- Selective sync: pick measures and dimensions one at a time
- Runs as a background job, so large views complete reliably
Sync a Unity Catalog metric view
Create Dimension Metrics · onDESCRIBE TABLE EXTENDED main.sales.sales_mv AS JSON
Databricks computes every value. Rollup inferred from each expression.
Databricks does the maths
- Native MEASURE() queries, one per measure
- Day grain from the view’s DATE or TIMESTAMP dimension
- Results returned as Arrow and cached
-- One query per measure, at day grain, run by Databricks
SELECT CAST(order_date AS DATE) AS metric_date,
MEASURE(revenue) AS metric_value
FROM main.sales.sales_mv
GROUP BY 1
ORDER BY 1;
What comes across from a metric view. And what stays exactly where it is.
Measures with expressions and comments
Dimensions child metrics created for you
Rollups inferred, then yours
Ask an agent the same question twice. Once on Genie, once on Canopy above it.
You already have a Genie Agent over this metric view, and it comes back with a governed number. The fair question is what Canopy adds. The pair below shows the shape of the difference, and the benchmark beneath it measures it on eight real business questions. It ran on Snowflake; the Cortex Analyst arm is the closest stand-in for Genie on a metric view.
The semantic layer alone
A correct number, and a dead end.
A Genie Agent over a metric view gets the definition right every time. It does not get the cause, the owner or the follow-up, so the question goes back to the data team.
Canopy on top of dbt
The driver, the owner, the build check and the next step.
Canopy reads the causal tree, the RACI, the build history from your dbt artifacts and the open tasks, so the first answer is the one people act on.
We measured it. Eight business questions toOpus 5, three ways in: Snowflake over MCP, raw SQLSnowflake Cortex Analyst, a governed semantic modelCanopy withSnowflake Semantic Views
One shared dataset, the same model, the same questions. The Cortex Analyst arm is the closest stand-in for an agent on the dbt Semantic Layer or the dbt MCP server: governed definitions and correct numbers, but no causal model, no owners and no record of what was tried. All three arms say what moved. Only the Canopy arm says why, who owns it and what is already being done, first time, without a week of follow-up questions.
What happened in the business in August that explains the numbers?
CanopyHad some of the contextAugust was a good month. The alarming numbers on the dashboard right now are September-to-date, not August — the health report's "EBITDA -21.8% MoM" is 1–9 Sep vs 1–9 Aug. Read on the full month, August looks like this:
| Aug 2026 | Jul 2026 | Aug 2025 | |
|---|---|---|---|
| Revenue | £378,621.99 | £364,535.26(+3.9%) | £368,140.79(+2.8%) |
| EBITDA | £111,351.63 | £99,888.10(+11.5%) | £114,655.61(-2.9%) |
| Gross margin % | 60.2 | 59.7 | 68.5 |
| COGS % | 39.8 | 40.3 | 31.5 |
| Labour cost % | 22.1 | 23.5 | 14.5 |
The finding: August sold more than last August and earned less. Revenue was up £10,481.20 year on year, yet EBITDA was down £3,303.98. That is not a demand problem — it is entirely a cost-structure problem. COGS is running 8.3 points higher than last August and labour 7.6 points higher.
I'm quoting those as percentage points rather than converting to a cash bridge: the margin metrics aggregate daily rates and don't reconcile cleanly against the P&L lines, so a pounds figure would be spurious.
KPI Tree's Canopy business context layer explains both halves of that.
Why August beat July:
- The King's Head kitchen refit finished ahead of schedule, so the venue reopened a full week early and caught the bank holiday trade that had been written off.
- The Sunday roast menu relaunched across all London venues with sharing boards and a fixed-price family option.
- At Shoreditch, moving quiz night from Tuesday to Thursday lifted revenue per labour hour — Thursdays were dead and the kitchen was already staffed.
Why the year-on-year margin is gone:
- Menu re-costing came back showing produce inflation at about 9%, steeper than budgeted, with beef and dairy the worst offenders.
- The draught supplier raised keg prices 6%, affecting every venue on the same contract.
- Leadership chose to re-engineer the menu rather than raise prices — protecting the headline price going into a soft quarter, but absorbing the inflation into margin. August is what that decision costs.
Two other recorded items shape the read: the area manager is on leave through the bank holiday fortnight (cover split between two senior venue managers, until 22 Sep), and the new till system is blocked on payment provider approval, so the faster table-ordering flow is still switched off at the pilot sites.
All four venues beat July. Against last August, only one is ahead:
- Canary Wharf £24,832.15 — +3.9% (+£934.15) — the only venue up
- Liverpool Street £26,658.84 — -3.9% (-£1,085.55)
- Paddington £26,977.50 — -4.4% (-£1,241.53)
- Shoreditch £32,883.14 — -5.5% (-£1,911.05)
Canary Wharf being the outlier lines up with the recorded decision to hold its staffing flat through the quarter despite the labour cost target, on the grounds that the lunch trade is too volatile to cut cover. On these numbers that call is paying.
Both funded initiatives are aimed squarely at this and both are working month-on-month:
- Menu engineering programme (Rachel Morrison, £45,000 budget, £18,500 spent, 33% progress) targets COGS % down — COGS fell 40.3 → 39.8 July to August.
- Rota optimisation rollout (Michael Patel, £30,000 budget, £12,000 spent, 33% progress) targets labour cost % down — labour fell 23.5 → 22.1. Canopy confirms it went live at the pilot venue with demand-based templates.
So the levers are moving in the right direction; they just haven't clawed back the year-on-year gap yet. Two departmental objectives sit on this — "Reduce labour cost percentage to below 28%" (Oliver Blackwood) is at risk, and "Improve gross margin to 68% across all venues" (Thomas Williams) is nominally on track at 34% outcome progress but only 16.7% execution progress.
One thing August does not explain. The quarterly EBITDA goal of £950,000 (Thomas Williams and Oliver Blackwood, due 30 Sep) stands at £239,950.11 — that is July £99,888.10 + August £111,351.63 + Sep 1–9 £28,710.38, and it reconciles exactly. With 21 days left and August's best-in-quarter month at £111,351.63, that target cannot be reached. Q3-to-date is 44.3% below the same span of Q2 (£430,770.18), so Q2 ran at roughly £143k/month against Q3's ~£105k. That gap is seasonal or structural and predates August; it is not caused by anything in the August numbers.
- Take the keg and produce increases to the P&L, not just the menu. Rachel Morrison owns COGS % and the menu engineering programme. The decision to re-engineer rather than re-price was made before produce inflation came back at 9% — that's a materially different input than when the call was made, and it's Thomas Williams' and Oliver Blackwood's decision to revisit as EBITDA owners.
- The one check that settles it: whether the draught contract is open to renegotiation or a second supplier. Every venue is on the same contract, so it's a single conversation with a group-wide 6% effect. Nobody in Canopy is recorded as owning that — Rachel Morrison is the right person to open it.
- Unblock the till rollout. The payment provider approval is holding the faster table-ordering flow off the pilot sites. That is a phone call to the provider, not an analysis, and it's the one recorded blocker with no owner attached.
- Reset the £950k quarterly target. It is arithmetically unreachable and it is currently making a company objective read as on-track at 29.3%. Thomas Williams and Oliver Blackwood are Responsible.
- Look again on 1 October, when September closes and gives a clean full-month read on whether the two initiatives keep pulling COGS and labour down.
What would change this conclusion: if the August revenue lift was mostly the King's Head reopening early rather than the Sunday roast relaunch, then the underlying like-for-like trade is flatter than +2.8% and the cost story is worse than it looks. I can't separate those — there's no King's Head metric in the tree; the EBITDA search returned only Canary Wharf, Shoreditch, Liverpool Street and Paddington. That's a measurement gap worth closing.
Two things I can do on your say-so: Canopy has a pointer saying the full autumn menu push context — promo dates and first-wave venues — is in the #marketing channel, and I can pull that thread in. I can also record the "revenue up, EBITDA down year on year, cost-driven" finding against EBITDA so it's there next month. Both need your go-ahead — ingesting reads the whole channel and can adopt the Slack workspace as a context source.
Each surface is judged on whether its answer drew on the business context recorded for this question. The judge saw the answers unlabelled, and saying plainly that the data is not there counts in a surface's favour. The judge's own term and specifics count sit under each answer.
Part of the Databricks integration. SQL metrics on the same connection, dbt beside it.
The Databricks page covers the connection, authentication, SQL warehouses and SQL metrics. If your definitions live in dbt and build into Databricks, the dbt Semantic Layer integration reads them and runs the SQL on this same warehouse.
Common questions
What is a Unity Catalog metric view?
A governed object in Databricks that declares business measures over a source table: the dimensions you can group by and the measures, each an aggregation expression, queried with the MEASURE() function so every consumer computes the same number. It is Databricks’ semantic layer, read by Genie Agents, AI/BI dashboards and any SQL client under the same Unity Catalog grants.
What do I need before I can sync a metric view?
A Databricks connection whose principal can read the view, pointed at a SQL warehouse or a cluster on Runtime 16.2 or later, because discovery reads each view with DESCRIBE TABLE EXTENDED AS JSON. If the compute cannot serve the JSON description, KPI Tree says so rather than guessing. The connector is enabled per workspace, so ask us to switch it on.
How does the sync turn a metric view into metrics?
KPI Tree lists the metric views in the catalog and schema you chose from the information schema, reads the one you pick with DESCRIBE TABLE EXTENDED AS JSON, and turns every measure with a date dimension into a tracked metric queried with MEASURE() at day grain. A timestamp dimension is cast to a date. Databricks computes every value.
How does KPI Tree decide whether to sum or take the last value across a week or a month?
A metric view carries no rollup rule, so KPI Tree infers one from each measure’s expression: sums and counts add up across weeks and months, averages, ratios and distinct counts take the latest value, min and max carry through, and a window measure takes its latest value. The sync report shows every inference, and a rollup you set by hand in KPI Tree is never overwritten by a later sync.
What are dimension metrics?
With Create Dimension Metrics on, KPI Tree probes the distinct values of each dimension you chose and creates a child metric per value, filtered in the MEASURE() query, so Revenue with a Region dimension gains Revenue (Region: EMEA), Revenue (Region: APAC) and so on, each with its own owner, baseline and nightly driver tests. Up to five dimensions per measure are expanded, and a dimension with more distinct values than the limit, 15 by default and adjustable between 2 and 200, is skipped and reported rather than truncated.
Are comments, joins and lineage read?
Measure comments come across as metric descriptions. Joins, the source table and Unity Catalog lineage are not read, and Genie Agents and AI/BI dashboards are not used. Descriptions and relationships can be added in KPI Tree and survive re-syncs.
What happens when the metric view changes?
Sync it again from the connection page. The run is compared with the previous one and the report lists what was created, updated, retired and skipped. Trees, ownership, action history and any rollup you set by hand are preserved.
How does this affect Databricks costs?
One MEASURE() query per measure on your schedule, returned as Arrow and cached for a TTL you set. Every comparison, rollup, correlation and drill-down runs in KPI Tree’s own engine, so the warehouse only works during a sync.
Can metric view measures share a tree with dbt or SQL metrics?
Yes. Measures from metric views, SQL metrics on the same Databricks connection, dbt metrics and metrics from other warehouses all sit on the same tree, each keeping its own calculation source.
Related guides. Frameworks and metrics in depth.
Semantic layer vs business context layer
A semantic layer settles what a metric is. It cannot settle how metrics drive each other, who owns them, or what happens when one moves.
How to build a metric tree
A step-by-step metric tree and KPI tree template from North Star to daily levers
dbt semantic layer and metric trees: how they fit together
dbt is the plumbing, the metric tree is the application above it
Related integrations. More sources that work with KPI Tree.
You governed the definition. Now govern the decision.
Sync a Unity Catalog metric view and see your own metrics as a causal tree with named owners and verified actions, in a demo on your data.
The guide
Semantic layer vs business context layer: what a definition settles, and what it cannot.
Read the guide
