Databricks integration
Your Databricks lakehouse holds the numbers. KPI Tree explains the movement, names the owner and proves the fix.
Connect a Databricks SQL warehouse with a personal access token or a service principal.Connect a Databricks SQL warehouse with a personal access token or a service principal. Write a metric in SQL against your Unity Catalog tables, or sync a Unity Catalog metric view so Databricks computes every measure. Each metric is one query at day grain, and everything else runs in KPI Tree’s own engine. Above the lakehouse it adds the layer Databricks 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
Governed in Unity Catalog. Explained in KPI Tree. One SQL warehouse, two ways in.
A Databricks connection is a SQL warehouse endpoint and a principal that can only read. On it, a metric can be written as SQL against any table Unity Catalog lets that principal see, or synced from a Unity Catalog metric view so that Databricks computes the measure with MEASURE(). Either way KPI Tree treats the lakehouse as the source of truth: one aggregation query per metric at day grain, returned as Arrow, with every comparison, rollup and driver test computed in memory rather than as another warehouse query. dbt metrics that build into Databricks run on the same connection.
What your lakehouse 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 lakehouse 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.
Two ways to define a metric, one read-only principal
Write the SQL yourself against your Unity Catalog tables, or sync a metric view and let Databricks compute each measure. Both end in the same tracked metric with the same owner and the same nightly driver tests.
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 metric, everything else in memory
Each metric is one aggregation query at day grain, returned as Apache Arrow and cached for a TTL you set. 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
Sync a metric view with Create Dimension Metrics on and every dimension you chose becomes a child metric per value: Revenue (Region: EMEA), Revenue (Region: APAC), and so on. Each child gets its own owner, baseline and nightly driver tests, within a cardinality limit you control.
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.
Unity Catalog decides what we can read, and nothing else
Every query runs under your principal through the SQL warehouse, so Unity Catalog grants, row filters and column masks apply as written. A personal access token or an OAuth service principal, read access only, credentials encrypted at rest.
Databricks SQL warehouse
Connected · SELECT 1 okConnected in three steps. A hostname, an HTTP path, a principal that can read.
Choose an authentication method
- Personal access token or OAuth M2M service principal
- Read access only, no write and no admin scope
- Credentials encrypted at rest, never logged
Databricks SQL warehouse
Connected · SELECT 1 okPoint it at a SQL warehouse
- Serverless, pro or classic SQL warehouses
- Cold starts retried automatically
- Results returned as Arrow and cached for a TTL you set
SELECT 1;
-- Metric views the principal can see
SELECT table_catalog, table_schema, table_name
FROM main.information_schema.tables
WHERE table_type IN ('VIEW', 'METRIC_VIEW');
-- Measures, dimensions and comments, as JSON
DESCRIBE TABLE EXTENDED main.sales.sales_mv AS JSON;
DESCRIBE … AS JSON needs a Databricks SQL warehouse or Runtime 16.2 or later.
Write SQL or sync a metric view
- SQL metrics against your Unity Catalog tables
- Metric view sync with native MEASURE() queries
- Dimension metrics created automatically for the dimensions you choose
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.
SQL metrics, or a Unity Catalog metric view. Same connection, same layer above. Start where your definitions live.
SQL metrics
This pageMetrics you write against any table or view the principal can read.
- Definition
- A SQL query per metric
- Runs
- On your SQL warehouse, one query per metric at day grain
- Rollups
- Chosen per metric in KPI Tree
- Needs
- A SQL warehouse and a principal with SELECT
Unity Catalog metric views
Governed measures synced from a metric view, computed by Databricks with MEASURE().
- Definition
- The metric view, read with DESCRIBE TABLE EXTENDED AS JSON
- Runs
- On your SQL warehouse, one MEASURE() query per measure
- Rollups
- Inferred from each expression, adjustable
- Needs
- A SQL warehouse or Runtime 16.2 or later
What a Databricks metric is. One query at day grain, however it was defined.
SQL metrics your query, your tables
Metric view sync Databricks computes every measure
Governance Unity Catalog, as written
Unity Catalog governs the calculation. KPI Tree adds causation, ownership and proof.
Above your governed definitions sits one causal model of the business, with the org chart, the recorded context and the outcome of every action attached. People read it as a tree. Agents read the same graph over MCP.
Data warehouses
The numbers
Semantic layers
The definitions
Business apps
Launches, decisions, blockers
Org structure
Directory-synced
For humans
Answers, briefings and pushes
@Read and write over MCP
Anthropic: 21% → ~95% with curated context
Canopy Agentic Workflows the metrics start them, humans gate them
Canopy Agents four out of the box, any model, your keys
Canopy
The business context layer
Structural context The causal tree, the org chart
Temporal context What was true when, plan against actual
Behavioural context What people did, what worked
Operational context RACI, workflows, approvals
Strategic context Objectives, key results, initiatives
Map & Measure
Metric Trees
Business Models
Prove & Act
Root Cause
Ownership
Verified Impact
Strategy
Grounded in proven causality
Verified proof
Meta spend → Revenue, +£32k vs forecast, q < 0.01
Personalised Action Plans 31 experiments running
hypothesis → a real person’s plan → measure the change
Causation the test cascade
ADF p 0.02 → Granger p 0.003 → q < 0.05, 87 of 1,204 survive
Correlation
22,791 pairs tested nightly, NPS – Churn r 0.71
Data warehouses
The numbers
Semantic layers
The definitions
Business apps
Launches, decisions, blockers
Org structure
Directory-synced
Causal drivers, tested every night
RACI ownership, on every metric
Conversion rate
Marketing · daily
RACVerified impact, not self-reported
Fix checkout flow
Verified · +£32kLinked to Revenue · measured by the actuals pipeline
Pause underperforming ad sets
Measuring…Ask an agent the same question twice. Once on the lakehouse, once on Canopy above it.
You already point an agent at your SQL warehouse, or ask a Genie Agent over a 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.
An agent on the SQL warehouse writes SQL from scratch every time. A Genie Agent over a metric view gets the definition right. Neither gets 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.
Read-only by design. Your principal, your grants and your data stay where they are.
SOC 2 Type II, continuously monitored
Controls independently examined for Security, Availability and Confidentiality and monitored every day, with CREST penetration testing and the Type II report in the Trust Center.
Unity Catalog decides what we can read, on every query
KPI Tree connects through your SQL warehouse as a read-only principal, so grants, row filters and column masks are enforced by Databricks, not trusted to us. A personal access token or an OAuth service principal, encrypted at rest.
Warehouse connection
ConnectedRead-only access · credentials never leave the encrypted store
We do not store your data, calculations run in memory
Each metric's daily series is fetched with read-only credentials, computed on in KPI Tree's own encrypted in-memory engine and cached only briefly. Nothing is copied into a lake and nothing sits at rest.
Common questions
What connection details do I need?
The workspace hostname, the HTTP path of a SQL warehouse, a personal access token or a service principal’s client ID and secret, and optionally a default catalog and schema. The connection is tested with a live SELECT 1 before it is saved.
What authentication is supported?
A personal access token, or a service principal through OAuth machine-to-machine with a client ID and secret. The service principal path suits workspaces that disallow personal tokens, and the Databricks SDK mints and refreshes the token for each pooled connection. Browser-based user OAuth is not used.
Can KPI Tree sync Unity Catalog metric views?
Yes. Pick a metric view and every measure becomes a tracked metric queried with MEASURE(), so Databricks computes each value. Discovery reads DESCRIBE TABLE EXTENDED AS JSON, which needs a SQL warehouse or Runtime 16.2 or later. The Unity Catalog page covers the sync in detail.
Is the Databricks connector generally available?
It is enabled per workspace rather than on for every account by default. Ask us and we will switch it on for yours.
Does KPI Tree respect Unity Catalog governance?
Yes. Every query runs under your principal through the SQL warehouse, so grants, row filters and column masks are enforced by Databricks. There is no parallel permission model.
How does KPI Tree affect Databricks costs?
One aggregation query per metric 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 and its own auto-stop setting keeps it idle between them.
Which SQL warehouse should I use, and does serverless work?
Any SQL warehouse, serverless, pro or classic. A small one handles hundreds of metrics because each is one lightweight query. A serverless warehouse that is still waking up is retried rather than failed.
Does KPI Tree work with dbt on Databricks?
Yes. Connect dbt Core or dbt Cloud through the dbt Semantic Layer integration and link this Databricks connection as the execution warehouse. Definitions stay in dbt and the SQL runs here.
Does KPI Tree use Genie or the Databricks MCP servers?
Not at present. KPI Tree reads Databricks through SQL under your principal and syncs Unity Catalog metric views natively. Genie One, Genie Agents, AI/BI dashboards, Unity Catalog lineage and the Databricks MCP servers are not read.
Does KPI Tree copy data out of Databricks?
No. Each metric’s daily series is fetched, computed on in KPI Tree’s encrypted in-memory engine and cached only briefly. Raw rows are never persisted outside your workspace.
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
Metric decomposition
Break any business metric into the components that drive it
Related integrations. More sources that work with KPI Tree.
You built the lakehouse. Now build the layer above it.
Connect Databricks 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
