Snowflake integration
Your Snowflake warehouse holds the numbers. KPI Tree explains the movement, names the owner and proves the fix.
Connect Snowflake with a role that can only read. Write a metric in SQL, draft one from plain English with Cortex Analyst, or sync a Semantic View so Snowflake computes every measure.Connect Snowflake with a role that can only read. Write a metric in SQL, draft one from plain English with Cortex Analyst, or sync a Semantic View so Snowflake computes every measure. Each metric is one query at day grain, and everything else runs in KPI Tree’s own engine. Above the warehouse it adds the layer Snowflake never carried: which metrics drive which, with statistical confidence, who is accountable when one moves, and whether the last action worked.
sales_sv
CREATE SEMANTIC VIEW analytics.marts.sales_sv
CREATE SEMANTIC VIEW sales_sv
TABLES (orders AS marts.fct_orders
PRIMARY KEY (order_id))
DIMENSIONS (orders.region, orders.channel,
orders.order_date)
METRICS (orders.revenue AS SUM(order_total),
orders.orders AS COUNT(order_id));
Revenue
Synced from Snowflake£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 Snowflake. Explained in KPI Tree. One read-only role, three ways in.
A Snowflake connection is a role with USAGE and SELECT and nothing more. On it, a metric can be written as SQL, drafted from a question by Cortex Analyst against one of your Semantic Views, or synced straight from a Semantic View so that Snowflake itself computes the measure. Whichever way in, KPI Tree treats Snowflake 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 Snowflake run on the same connection.
What your warehouse 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 warehouse 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.
Three ways to define a metric, one governed connection
Write the SQL yourself, ask Cortex Analyst to draft it from plain English against a Semantic View, or sync a Semantic View and let Snowflake compute each measure. Every route ends in the same tracked metric with the same owner and the same nightly driver tests.
New metric
SELECT order_date AS date,
SUM(order_total) - SUM(refund_total) AS value
FROM marts.fct_orders
WHERE status = 'complete' GROUP BY 1
Drafted by Cortex, run on your warehouse, reviewed here before it is saved as a metric.
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 credit bill stays flat as the tree grows.
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 Semantic 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.
Read-only by construction, your controls stay on
The wizard writes the SQL for a dedicated user, a least-privilege role and an optional network policy that admits one static IP. Row access policies, masking policies and RBAC apply to every query because KPI Tree reads through your governance, never around it.
Snowflake
Connected · SELECT 1 okConnected in three steps. The wizard writes the SQL. You review it and run it.
Run the generated setup SQL
- Password or RSA key-pair authentication, your choice
- Least-privilege role with future grants, so new tables are covered
- Optional network policy scoped to one static IP
-- Generated by the wizard, reviewed by you, run in a worksheet
CREATE USER IF NOT EXISTS KPITREE_USER
RSA_PUBLIC_KEY = 'MIIBIjANBg…';
CREATE ROLE IF NOT EXISTS KPITREE_ROLE;
GRANT ROLE KPITREE_ROLE TO USER KPITREE_USER;
GRANT USAGE, MONITOR ON WAREHOUSE KPITREE_WH TO ROLE KPITREE_ROLE;
GRANT USAGE, MONITOR ON DATABASE ANALYTICS TO ROLE KPITREE_ROLE;
GRANT SELECT ON ALL TABLES IN DATABASE ANALYTICS TO ROLE KPITREE_ROLE;
GRANT SELECT ON FUTURE TABLES IN DATABASE ANALYTICS TO ROLE KPITREE_ROLE;
GRANT SELECT ON ALL SEMANTIC VIEWS IN DATABASE ANALYTICS TO ROLE KPITREE_ROLE;
-- Optional: admit one static IP and nothing else
CREATE OR REPLACE NETWORK RULE KPITREE_ALLOW_RULE MODE = INGRESS TYPE = IPV4 VALUE_LIST = ('34.89.0.12');
CREATE OR REPLACE NETWORK POLICY KPITREE_POLICY ALLOWED_NETWORK_RULE_LIST = ('KPITREE_ALLOW_RULE');
ALTER USER KPITREE_USER SET NETWORK_POLICY = KPITREE_POLICY;
Connect and validate
- Validated with a live query before it is saved
- Credentials encrypted at rest, never logged
- Every sync query deep-links to Snowflake’s query history
Snowflake
Connected · SELECT 1 okWrite, draft or sync
- SQL metrics against any table or view the role can read
- Cortex Analyst drafts from a question, against a Semantic View you choose
- Semantic View sync turns every metric in the view into a tracked metric
New metric
SELECT order_date AS date,
SUM(order_total) - SUM(refund_total) AS value
FROM marts.fct_orders
WHERE status = 'complete' GROUP BY 1
Drafted by Cortex, run on your warehouse, reviewed here before it is saved as a metric.
SQL and Cortex, or a Semantic View. Same connection, same layer above. Start where your definitions live.
SQL metrics and Cortex Analyst
This pageMetrics you write, or draft from a question, against any table or view the role can read.
- Definition
- A SQL query per metric, written or drafted by Cortex
- Runs
- On your warehouse, one query per metric at day grain
- Rollups
- Chosen per metric in KPI Tree
- Needs
- A role with USAGE and SELECT
Snowflake Semantic Views
Governed metrics synced from a Semantic View, computed by Snowflake through SEMANTIC_VIEW().
- Definition
- The Semantic View, read with DESCRIBE SEMANTIC VIEW
- Runs
- On your warehouse, one SEMANTIC_VIEW() query per metric
- Rollups
- Inferred from each metric’s expression, adjustable
- Needs
- SELECT on the view, in the connection’s default database
What a Snowflake metric is. One query at day grain, however it was defined.
SQL metrics your query, your tables
Cortex Analyst drafts from a question, on a Semantic View
Semantic View sync Snowflake computes every measure
Snowflake 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 Snowflake, once on Canopy above it.
You already point an agent at Snowflake over MCP, or ask Cortex Analyst, 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 Snowflake, on eight real business questions.
The semantic layer alone
A correct number, and a dead end.
An agent on Snowflake over MCP writes SQL from scratch every time. Cortex Analyst on a semantic model 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 role, your policies 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.
A role that can only read, and a policy that admits one IP
The generated SQL grants USAGE and SELECT and nothing else, and the optional network policy allows a single static address. Row access policies, masking policies and RBAC apply to every query KPI Tree runs, because it runs as your role.
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 does the setup wizard generate?
SQL for a dedicated user with a password or an RSA public key, a role with USAGE on the database, schemas and warehouse and SELECT on current and future tables, views and Semantic Views, and, if you want it, a network rule and policy that admit KPI Tree’s single static IP. You review the SQL in a worksheet before running it, and the connection is validated with a live SELECT 1 before it saves.
What permissions does KPI Tree need?
USAGE and MONITOR on the database, its schemas and the warehouse, and SELECT on tables, views and Semantic Views, with future grants so new objects are covered. That is the whole grant surface, and it is read-only. Row access policies, masking policies and RBAC apply to every query because they run as your role.
Password or RSA key-pair authentication?
Either. The wizard generates a password by default and can generate the RSA statements instead. Snowflake supports two public keys at once, so a key pair can be rotated with no downtime. Whichever you choose is encrypted at rest and never logged.
How does Cortex Analyst work in KPI Tree?
The metric editor has a Cortex mode. Choose a Semantic View, describe the metric in plain English, and Cortex Analyst drafts the SQL. KPI Tree runs it on your warehouse under your role and shows you the query and the result, which you review before saving the metric. Cortex cross-region inference must be enabled on your account. KPI Tree is a consumer of Cortex here, not a replacement.
How do Semantic Views work in KPI Tree?
Pick a Semantic View from the ones in the connection’s default database and every metric in it becomes a tracked metric, queried through SEMANTIC_VIEW() so Snowflake computes the value. Dimension metrics are created for the dimensions you choose. The Snowflake Semantic Views page covers the sync in detail.
How does KPI Tree affect Snowflake 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 exploration adds no Snowflake load. A small dedicated warehouse keeps the sync traffic on its own line of the bill, and its own auto-suspend setting keeps it idle between syncs.
Does KPI Tree work with dbt on Snowflake?
Yes. Connect dbt Core or dbt Cloud through the dbt Semantic Layer integration and link this Snowflake connection as the execution warehouse. Definitions stay in dbt and the SQL runs here.
Does KPI Tree copy data out of Snowflake?
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 account, and every Snowflake control stays enforced on each query.
Does KPI Tree use Snowflake Horizon, Snowflake CoWork or the Snowflake MCP server?
Not at present. KPI Tree reads Snowflake through SQL under your role, uses Cortex Analyst to draft SQL against a Semantic View, and syncs Semantic Views natively. Horizon metadata, Snowflake CoWork and the Snowflake-managed MCP server are not read.
Which agents can use the Snowflake metrics?
Any MCP-capable assistant such as Claude, ChatGPT, Gemini or Copilot, through Canopy, KPI Tree’s business context layer. An agent sees each metric with its drivers, owners and recorded context and, with editor access, can run read-only queries on this connection to test an explanation.
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 warehouse. Now build the layer above it.
Connect Snowflake 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
