How to connect BigQuery to a BI tool: a practical guide

Connecting Google BigQuery to a BI tool is straightforward, because BigQuery is already a SQL warehouse and almost every BI product speaks SQL. The real work is three things: setting up a scoped service account so the connection is secure, controlling cost (BigQuery bills by the bytes a query scans, not by how long it runs), and deciding where your metrics live so dashboards stay consistent. Get those right and the connection itself takes minutes.

This guide is for analytics engineers, data leads, and founders who already have data in BigQuery and want dashboards without surprises on the Google Cloud bill. It covers the ways BI tools connect to BigQuery, how to set up access correctly, the governance features BigQuery gives you, and the cost controls that separate a predictable setup from a runaway one. It is the practical companion to our comparison of the best BI tools for BigQuery, which ranks the tools themselves.

TL;DR

• BigQuery is a SQL warehouse, so most BI tools connect to it directly with a live connection. You rarely need to extract data into the BI tool’s own engine.
• Set up a dedicated service account with two narrow roles (BigQuery Data Viewer on specific datasets and BigQuery Job User to run queries). Do not reuse a personal account or grant project-wide editor.
• The biggest risk is cost, not connectivity. BigQuery on-demand pricing charges $6.25 per TiB scanned after a 1 TiB free monthly tier, so a careless dashboard can scan terabytes per refresh (Google Cloud pricing).
• The four cost levers that matter most: partition and cluster your tables, avoid SELECT *, set a maximum bytes billed limit, and add BI Engine or materialized views for hot dashboards.
• Govern access in BigQuery with authorized views, row-level security, and column-level policy tags, then layer your BI tool’s permissions on top.

Why BigQuery is easy to connect but easy to overspend on

Most warehouses are pleasant to put a BI tool on top of. There is no document-flattening problem like MongoDB, no risk of overloading a production database like Postgres or MySQL, and the data is already columnar and built for analytical scans. A live connection from Looker, Tableau, Power BI, Metabase, Sigma, or Basedash is usually a matter of pasting a service account key and picking a dataset.

The catch is the pricing model. By default, BigQuery uses on-demand pricing, where you pay for the number of bytes each query reads. The first 1 TiB per month is free per billing account, and after that the rate is $6.25 per TiB in US and EU regions (Google Cloud pricing). That model rewards careful queries and punishes lazy ones. A single SELECT * against a wide, unpartitioned table can scan hundreds of gigabytes every time a dashboard refreshes, and a dashboard that auto-refreshes for fifty viewers multiplies that scan by fifty.

This is why connecting BigQuery is the easy part and the rest of this guide spends most of its time on cost and access. The connection works on day one. The bill is what surprises teams in month two.

The ways to connect BigQuery to a BI tool

There are four practical patterns. Most teams use the first.

1. Direct live connection via a service account

This is the default for almost every BI tool. You create a Google Cloud service account, give it read access to the datasets you want exposed and permission to run jobs, download a JSON key (or use workload identity federation), and paste the credentials into your BI tool’s BigQuery connector. The tool then pushes SQL straight to BigQuery’s engine and renders the results.

Use this when you want live dashboards on current data, which is almost always. Because BigQuery is built for analytical scans, there is no production database to protect, so live querying is the normal mode rather than a risk.

2. Google-native tools (Looker and Looker Studio)

Looker and Looker Studio are Google products and integrate with BigQuery through Google authentication and IAM rather than a service account key. Looker Studio is free and connects with a few clicks, which makes it the fastest way to get a chart on BigQuery data. Looker (the enterprise product) adds a governed modeling layer in LookML on top of the same connection. Both push queries down to BigQuery, so the same cost rules apply.

3. BI Engine acceleration

BigQuery BI Engine is an in-memory analysis layer that caches frequently queried data and serves dashboard queries with sub-second latency. You reserve capacity in GB, and BI Engine accelerates compatible queries automatically without changing how the BI tool connects. It works with Looker Studio, Looker, and other tools that query through the standard interface. One caveat: BI Engine does not accelerate queries on tables that use row-level security (Google Cloud), so plan governance and acceleration together.

4. Extract into the BI tool’s own engine (rarely needed)

Some tools can import a snapshot of BigQuery data into their own in-memory engine (Power BI Import mode, Tableau extracts). This made sense when warehouses were slow and expensive to query repeatedly. With BigQuery, partitioning, caching, and BI Engine usually make live (DirectQuery) the better choice. Reach for extracts only when you have a specific reason: a fixed dataset that rarely changes, offline access, or a tool that performs noticeably better on extracts for your particular workload.

How to set up the connection securely

The connection should be a narrow, auditable boundary, not a copy of your own credentials.

• Create a dedicated service account for BI access, named for its purpose (for example [email protected]). Never reuse a personal account or an application’s service account.
• Grant the two roles that matter. BigQuery Data Viewer lets the account read data, scoped to specific datasets rather than the whole project. BigQuery Job User lets it run queries. That combination is enough to power dashboards and nothing else.
• Scope to datasets, not the project. Grant Data Viewer at the dataset level on the datasets analysts actually need. Avoid project-wide grants that quietly expose every future dataset.
• Prefer a reporting dataset. Point the BI tool at a curated dataset of views or modeled tables rather than at raw ingestion tables. This keeps dashboards stable and limits what the service account can see.
• Rotate keys or use federation. JSON keys are long-lived secrets. Rotate them, or use workload identity federation so your BI tool authenticates without a downloadable key at all.

The same read-only, least-privilege principles apply to any source. If you also connect a production database, see how to safely connect a BI tool to your production database for the equivalent setup on Postgres and MySQL.

Keep BigQuery costs predictable

This is the section that decides whether your setup is a quiet success or a recurring finance conversation. BigQuery’s bytes-scanned model means cost control is a query-design problem, not a licensing problem. The levers below are ordered by impact.

Partition your tables. Partitioning by a date or timestamp column lets BigQuery prune irrelevant partitions, so a query filtered to the last 30 days scans 30 days of data instead of three years. Unpartitioned tables are the single most common cause of bloated BI bills.

Cluster within partitions. Clustering sorts data by the columns you filter on most (customer ID, region, status), which further reduces bytes scanned for selective queries. Partitioning plus clustering covers most dashboard query patterns.

Stop selecting columns you do not use. BigQuery is columnar, so it only scans the columns a query references. SELECT * scans every column; selecting the five you actually chart can cut the scan by an order of magnitude. Model your reporting views to expose only what dashboards need.

Set a maximum bytes billed limit. BigQuery lets you cap the bytes a query is allowed to scan. If the estimate exceeds the limit, the query fails before running and you are not charged (Google Cloud best practices). Set it as a guardrail on the BI service account or per query so one bad dashboard cannot scan a terabyte by accident. BigQuery also supports user-level and project-level custom cost controls.

Use BI Engine or materialized views for hot dashboards. A handful of dashboards usually drive most of the query volume. BI Engine caches them in memory; materialized views pre-aggregate expensive rollups so the BI tool reads a small summary instead of scanning the base table each time. Both cut both latency and cost for repeated queries.

Lean on the result cache. BigQuery caches query results for 24 hours, and cached results are free. Identical queries (same SQL, same data) served from cache cost nothing, which is why scheduled refreshes that reuse the same query are cheaper than they look.

Consider editions for predictable heavy usage. On-demand pricing is best for sporadic, exploratory workloads. If BI usage is heavy and steady, capacity-based BigQuery editions (slot reservations with autoscaling) can make the bill predictable and often cheaper at volume. The crossover depends on your monthly scan volume; on-demand is usually cheaper below roughly 10 TiB of unpredictable scanning per month.

For a deeper treatment of warehouse spend driven by dashboards, see how to cut cloud data warehouse costs from BI dashboards.

Permissions and governance in BigQuery

BigQuery gives you native, query-time access controls. Use them as the source of truth and let your BI tool’s permissions sit on top, rather than relying on the BI tool alone.

• Authorized views. An authorized view lets you share the results of a view without granting access to the underlying tables. This is the cleanest way to expose a curated reporting layer: analysts query the views, never the raw data. Google recommends authorized views when flexibility and performance matter.
• Row-level security. Row-level access policies filter which rows each principal can see, based on identity. This is how you let several teams share one dashboard while each sees only its own region, account, or tenant. Note again that BI Engine and materialized views are not accelerated on tables with row-level security.
• Column-level security. Policy tags restrict access to sensitive columns (email, salary, PII). Users need the Data Catalog Fine-Grained Reader role to read protected columns; everyone else sees the column masked or inaccessible.
• Layer the BI tool on top. Define dataset access, row policies, and column policies in BigQuery, then map your BI tool’s roles to the same model. For the application-level design, see how to design a BI permission model for a SaaS team.

The principle: enforce data access in the warehouse where it cannot be bypassed, and use the BI tool for workspace, dashboard, and editing permissions.

Handle BigQuery’s schema quirks

BigQuery supports nested and repeated fields (STRUCT and ARRAY), which are common in event data and exports from tools like Google Analytics 4. Two things to plan for:

• Nested STRUCTs are addressed with dot notation (user.address.country). Most BI tools handle this, but some flatten awkwardly. Expose a flattened view if your tool struggles.
• Repeated ARRAY fields need UNNEST to become rows. Decide whether to unnest in a reporting view (so the BI tool sees clean tabular data) or rely on the tool to handle arrays. Doing it once in a view is almost always cleaner than reinventing it per dashboard.

Wide, denormalized tables are idiomatic in BigQuery and usually fine, but they make SELECT * expensive. Model reporting views that select only the columns dashboards use.

Live query vs extract: which to choose

For BigQuery, live (DirectQuery) is the default and usually the right answer. The warehouse is built for analytical scans, BI Engine and caching handle latency, and live data is what most dashboards need.

Choose live when:

• You want current data and dashboards that reflect the latest loads.
• You have partitioned and clustered tables, plus cost guardrails in place.
• You want a single source of truth without managing extract refresh schedules.

Choose an extract when:

• The dataset is small and changes rarely, and you want to eliminate per-refresh query cost entirely.
• A specific tool performs materially better on extracts for your workload.
• You need offline or air-gapped access.

When in doubt, start live with partitioning, a maximum bytes billed limit, and BI Engine on the busiest dashboards. Add extracts only if a real cost or performance problem appears.

Tool options for BigQuery

How the major BI tools connect to and behave on BigQuery:

No tool removes the need for partitioning and a bytes-billed cap. They differ mainly in how much governance and AI sit on top of the same BigQuery connection.

A short setup checklist

Before you share a BigQuery dashboard with the team, confirm:

1. A dedicated service account exists with only BigQuery Data Viewer (on specific datasets) and BigQuery Job User.
2. The BI tool points at a curated reporting dataset of views, not raw tables.
3. Base tables are partitioned by date and clustered on common filters.
4. Reporting views select only the columns dashboards use (no SELECT *).
5. A maximum bytes billed limit is set as a guardrail.
6. BI Engine or materialized views are enabled for the busiest dashboards.
7. Row-level and column-level security are applied where data is sensitive.
8. Someone owns the monthly BigQuery cost and reviews INFORMATION_SCHEMA.JOBS for the most expensive queries.

FAQ

Do I need a data warehouse setup before connecting a BI tool to BigQuery?

No. BigQuery is the warehouse. If your data is already in BigQuery, you connect a BI tool directly. The only modeling worth doing first is a curated set of reporting views so dashboards are stable and scoped.

What is the cheapest way to put dashboards on BigQuery?

Looker Studio is free and connects natively, which makes it the cheapest tool. But tool cost is rarely the issue; query cost is. The cheapest setup is partitioned and clustered tables, reporting views that avoid SELECT *, a maximum bytes billed cap, and BI Engine on the few dashboards people actually use.

Should I use a service account or personal credentials?

Always a dedicated service account with least-privilege roles. Personal credentials break when the person leaves, are hard to audit, and usually carry more access than a dashboard needs.

How do I stop a dashboard from scanning terabytes?

Partition and cluster the tables it reads, expose only needed columns through a view, and set a maximum bytes billed limit so oversized queries fail before running rather than after. For repeated heavy queries, pre-aggregate with a materialized view.

Can different users see different rows on the same BigQuery dashboard?

Yes. Apply row-level security policies on the underlying tables so each user sees only their permitted rows, then build one shared dashboard on top. Remember that BI Engine will not accelerate tables that use row-level security.

Where should I define metrics when my source is BigQuery?

In one layer above the raw data: SQL views, dbt models, or a semantic layer, never re-derived in every dashboard. The reasoning is the same as in where to define business metrics: pick one home for each metric so every dashboard reads the same definition.