BigQuery Integration
Connect BigQuery to KPI Tree and build the accountability layer your warehouse was never designed to hold.
You have the warehouse. You have the data. What you do not have is a system that maps how metrics drive each other, names who owns each one, routes the work when a number moves, and proves what actually worked. KPI Tree connects to BigQuery with a project ID and a choice of two auth methods, runs one query per metric on a schedule you set, and computes every comparison, rollup, correlation and significance test off-warehouse in its own engine. Each query returns the bytes it processed and a deep link straight to the BigQuery console, so you always know exactly what ran and what it cost.
Connected in under an hour
A project ID, three predefined IAM roles, and a guided wizard that validates the connection live before it saves. No agents to install, no data extraction, no firewall changes.
Choose your authentication method
KPI Tree supports two ways to authenticate against BigQuery. With the managed service account, KPI Tree authenticates as its own GCP service account through workload identity and you simply grant that account access to your datasets, so there is no JSON key to download, store or rotate. Alternatively, create your own GCP service account, download its JSON key, and paste it into KPI Tree, where it is encrypted at rest. Either way the permission surface is identical, and the next step covers all of it.
Grant three predefined IAM roles
Grant BigQuery Data Viewer for read-only access to only the datasets you name, BigQuery Job User so KPI Tree can run queries, and BigQuery Read Session User so results stream back efficiently through the Storage Read API as Apache Arrow. No custom roles, no project-level admin, no data export or write grants. Your security team can read the whole surface in one screen.
Provide your project ID and connect
Enter your GCP project ID. KPI Tree validates the connection in real time, confirming it can authenticate, resolve your datasets, and run a query, before anything is saved. From there, write SQL against your tables and views to define each metric, map how they drive each other, assign RACI ownership, and start closing the loop between the number and the action.
Your BigQuery investment, with an accountability layer on top
KPI Tree reads BigQuery with the minimum grants, keeps the warehouse bill flat as your team grows, and then builds the layer no warehouse or BI tool provides: a causal metric tree, named ownership, routed action, and proof the action moved the number.
One query per metric, everything else off-warehouse
Each metric is a single query that aggregates raw data down to a daily series. That query runs on the schedule you set, and every comparison period, week or quarter rollup, correlation, regression and outlier test runs off-warehouse in KPI Tree's own engine. KPI Tree also sets BigQuery's query cache on every job, so an unchanged scheduled query is served from cache and billed at zero bytes. Adding a hundred viewers adds no BigQuery queries.
Bytes processed and a console deep link on every query
Every query KPI Tree runs records the bytes it processed and returns a deep link to that exact job in the BigQuery console, scoped to the job's location and id. You can open the SQL, the execution details and the byte count for any sync without touching a billing export, so on-demand cost stays visible per metric rather than buried in an aggregate line.
Two auth methods, zero stored passwords
Use the managed service account and manage no key material at all, or bring your own service account JSON key for full control. Both paths use GCP's standard IAM model, so your VPC Service Controls, Cloud Audit Logs and organisation policies keep enforcing exactly as configured. KPI Tree reads through those controls, it does not sit beside them.
Two authentication methods. Pick the one your security team prefers.
Not every team wants to manage service account keys, so KPI Tree offers a managed option: it brings its own GCP service account and reaches your datasets through workload identity, meaning no key file ever changes hands. Teams that prefer to hold the credential themselves can create a service account, paste its JSON key into KPI Tree, and rely on encryption at rest. Whichever you choose, the permission surface is identical and deliberately small, so the review is short and the answer is easy to give.
- Managed service account authenticates via workload identity, no JSON key to hold
- Custom service account: paste your own JSON key, encrypted at rest
- Both methods use only BigQuery Data Viewer, Job User, and Read Session User
- No project-level admin, and nothing that can export or write data
Warehouse connection
ConnectedRead-only access · credentials never leave the encrypted store
A causal metric tree that shows how the business actually moves.
A dashboard shows you what happened. KPI Tree shows why it happened, who is accountable, and what to do next. Map how each metric drives the ones above it, so when a number moves you can trace the tree to the drivers that changed. Every driver edge carries a statistical significance and confidence level, computed nightly with proprietary ML models and statistical tests including Pearson correlation, lagged cross-correlation, partial correlation and Granger causality with Benjamini-Hochberg correction, so you see which relationships are real and how sure to be, not just which lines happen to move together. Automated root cause traces an anomaly down the tree to the driver that caused it.
- Map how every metric drives the ones above it, from inputs to revenue
- Statistically significant driver edges with confidence levels, recomputed nightly
- Automated root cause traces an anomaly to the driver that moved it
- Business model values (budgets, forecasts, targets) run through the same pipeline
One query per metric. The warehouse bill stays flat as the team grows.
KPI Tree runs one query per metric on the schedule you set and returns the result as Apache Arrow through the Storage Read API. Comparison periods, correlations, regressions, outlier detection and causal analysis all run in KPI Tree's own compute engine, so none of them pushes a further query back to BigQuery. Results are cached and per-metric interactions are served locally in the browser, which means filtering, comparing periods and drilling into drivers never re-hit the warehouse. Your on-demand cost becomes a function of how many metrics you track and how often they sync, not how many people are looking.
- One scheduled query per metric, streamed back as Arrow via the Storage Read API
- Comparisons, rollups, correlations and outlier tests all run off-warehouse
- Cached results and in-browser interaction mean drilling never re-queries BigQuery
- BigQuery query cache set on every job, so unchanged syncs bill zero bytes
Ownership, routing, and proof the action worked.
Every metric carries full RACI, so a named person is Accountable and it is clear who is Responsible, Consulted and Informed, tied to their team, department and manager. When a metric breaks its expected pattern, KPI Tree pushes the alert to the Accountable owner across Slack, email, WhatsApp and SMS, escalating up the org chart if it is not picked up. Tasks and subscriptions attach the work to the exact metric it is meant to move, and when the work lands KPI Tree checks whether the number actually shifted. That closed loop, from anomaly to owner to verified impact, is the part a warehouse, a catalogue and a dashboard all leave open.
- Full RACI per metric, tied to team, department and manager
- Anomaly-triggered push to the Accountable owner with org-chart escalation
- Tasks and subscriptions attach action to the metric it targets
- Verified impact confirms whether the action actually moved the number
What KPI Tree adds on top of BigQuery
Keep BigQuery, keep whatever governed definitions you have in dbt or Looker. KPI Tree reads them as a source and adds the layer above: your semantic layer tells the warehouse how a metric is calculated, and KPI Tree adds how metrics drive each other, who owns them, and what is being done about it. The same tree can carry metrics from BigQuery and another warehouse side by side, which a warehouse-native graph cannot.
Every source resolves onto one causal tree.
A causal metric tree, not a structural map
A warehouse graph maps how tables and entities relate and walks those relationships. KPI Tree maps how metrics drive each other and puts a statistical significance and confidence level on every edge, so when revenue drops you can trace the tree to whether it was traffic, conversion or average order value, and know how sure to be. Structural graph-walking never tells you which driver moved the number.
RACI ownership, then the alert goes to a person
Catalogue owner metadata names a contact and stops there. KPI Tree makes RACI a first-class primitive on every metric, then routes anomalies to the Accountable owner across Slack, email, WhatsApp and SMS with org-chart escalation. Ownership that triggers action, not a field nobody reads.
Closed-loop verified impact
Everyone claims metrics-to-action and none of them closes the loop. When someone acts on a metric, KPI Tree checks whether the number actually moved and records it. Diagnosis of why a metric changed is now table stakes everywhere. Verified, owned, routed action is the part no warehouse, catalogue or dashboard closes.
Related integrations. More sources that work with KPI Tree.
Common questions
What connection details does KPI Tree need?
What IAM roles does KPI Tree need?
What is the difference between the two auth methods?
How does KPI Tree affect my BigQuery bill?
Can I see what queries KPI Tree runs and what they cost?
How does KPI Tree decide how a metric rolls up across days?
Does KPI Tree work with dbt on BigQuery?
Does KPI Tree copy data out of BigQuery?
Related guides. Frameworks and metrics in depth.
Deep dives into the frameworks and metrics that work with Google BigQuery.
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
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.
Connect BigQuery in under an hour.
One project ID, three predefined IAM roles, and your choice of auth method. Off-warehouse analytics, a flat warehouse bill, and full cost visibility from the first sync.
