Connecting BigQuery to BI and Analytics Tools

BigQuery BI Engine (BI Engine) (opens in a new tab) is a fast, in-memory analysis service built into BigQuery that accelerates interactive dashboard queries to sub-second response with high concurrency. BI Engine intelligently caches the columns and partitions that are queried most frequently, so repeated dashboard queries read from memory instead of re-scanning storage. Combined with table partitioning, clustering, and materialized views, BI Engine minimizes both query latency and analytical cost for reporting workloads.

1.1How BI Engine Accelerates BI Queries

How BI Engine accelerates BI queries is by sitting behind the BigQuery API and serving cached data from memory without application changes. Traffic flows from the BI tool to the BigQuery API, then to the BI Engine in-memory layer when the data is cached, while unaccelerated stages fall back to standard BigQuery slots without failing the query. Supported tools include Looker Studio (opens in a new tab), Looker, Tableau, Power BI, the BigQuery console, client libraries, and ODBC/JDBC connectors through standard REST API and JDBC and ODBC interfaces. BI Engine uses a vectorized runtime that operates on batches of data to use modern CPUs efficiently, plus dictionary run-length encoding to further compress data in memory. BI Engine integrates with BigQuery governance, including authorized views, column-level security, and data masking.

1.2Memory Reservations and Preferred Tables

BI Engine reservations are separate in-memory capacity allocations managed per project and region. A billing administrator reserves capacity with a supported BigQuery edition, and BI Engine caches only the queried parts of columns and partitions rather than whole tables. Administrators can let BigQuery choose what to cache from usage patterns or explicitly designate preferred tables to protect high-visibility dashboards from other traffic. To guarantee acceleration for a critical workload, create a separate project with a dedicated BI Engine reservation, estimate the required capacity, and list only its essential tables as preferred. When memory is full, BI Engine offloads partitions and columns that have not been accessed recently to free memory for new accelerated queries.

BI Engine preferred tables restrict acceleration to a defined set of tables, while all other tables use regular BigQuery slots:

• BI Engine preferred tables support tables only; logical views cannot be added to the reservation list.
• Queries to materialized views are accelerated only when both the materialized view and its base tables are in the preferred tables list.
• Queries that access multiple tables are accelerated only when every table is preferred; if one table in a JOIN is missing, the query cannot use BI Engine.
• Partition-level or column-level selection is not supported, and JSON type columns are not accelerated.
• Public datasets cannot be added as preferred tables in the Google Cloud console and require the API or DDL.

1.3Data Layouts That Reduce Memory and Scans

Data layout controls how much data BI Engine must load into memory, which directly controls latency and cost. Partition tables by time when dashboards filter by recency, so only the latest partitions are loaded; for example, a last-quarter dashboard loads only those partitions instead of full history. Cluster large tables to further improve pruning and scan efficiency, and use materialized views to pre-join and pre-aggregate data into a single large, flat table so each dashboard query avoids repeating the same joins. Stale materialized views are recommended for optimal query performance because they avoid recomputation on every refresh.

1.4Query Execution and Join Design

Query execution in BI Engine divides a query plan into subqueries that scan, filter, compute, aggregate, or sort data. BI Engine selectively optimizes leaf-level subqueries that scan storage and perform filtering, computation, aggregation, sorting with ORDER BY, and supported joins. Simpler dashboard queries benefit most because most of their time is spent processing raw data in those leaf stages. BI Engine works best for pre-joined or pre-aggregated data with few joins, especially a large fact table joined to much smaller dimension tables. Acceleration applies to leaf-level INNER and LEFT OUTER joins where one large fact table joins up to four smaller dimension tables.

Queries that depend on unsupported features do not benefit from BI Engine, including wildcard tables, external tables, row-level security, and non-SQL user-defined functions. Other unsupported cases include BigQuery ML queries, recursive queries using RECURSIVE, search and vector search functions, search and vector indexes, transactions, change data capture upserts, and queries returning more than 1 GiB of data, with less than 1 MiB recommended for latency-sensitive applications. To measure BI Engine impact accurately, monitor INFORMATION_SCHEMA.BI_CAPACITIES and INFORMATION_SCHEMA.BI_CAPACITY_CHANGES or Cloud Monitoring, and disable the Use cached results option so BigQuery result caching does not mask acceleration. If a performance-critical query requires an unsupported feature, materialize a workaround table:

1. Write the complex query in BigQuery.
2. Save the query results to a table.
3. Schedule the query to refresh the table hourly or daily, since per-minute refresh can invalidate the cache too frequently.
4. Reference the materialized table in performance-critical dashboard queries.

1.5Connecting and Tuning BI Tools for Scale

Connecting BI tools to BigQuery uses public APIs over the internet or private paths that keep traffic on Google networks. BI and analytics tools connect to BigQuery through the public BigQuery REST API and RPC-based BigQuery Storage Read API over the internet when hosts have external IP addresses. Tools on Compute Engine virtual machines with only internal IP addresses use Private Google Access, enabled subnet-by-subnet in a Virtual Private Cloud (VPC) network, to reach BigQuery without external addresses. On-premises tools use Private Google Access for on-premises hosts over Cloud VPN or Cloud Interconnect with internal RFC 1918 addresses, which requires DNS, firewall, and route configuration in both networks.

Looker Studio reports on BigQuery improve when filters are pushed as WHERE clauses, complex views are materialized, and BI Engine capacity is reserved where queries execute. Connecting Looker Studio to a view that computes at run time can time out on large datasets, so materialize the result with a materialized view or a table refreshed by scheduled queries, then connect the report to that table and accelerate it with BI Engine. For paginated results, Looker Studio automatically uses the Storage Read API when it improves runtime, which requires bigquery.readsessions.create and bigquery.readsessions.getData for the connected user. Concurrency has no hard Looker Studio limit because the architecture is serverless; BigQuery concurrency depends on slot availability, query complexity, data size, and caching, so use partitioning, clustering, BI Engine, data-freshness caching, phased rollouts for thousands of users, and monitoring of slots and quotas. Deploying a self-managed BI instance on Compute Engine uses the Google network backbone to minimize latency between the tool and BigQuery.

Secure authentication and access control for visualization is the system that proves which tool or user is calling, grants only the permissions that tool needs, and keeps query traffic inside trusted network boundaries. In Google Cloud, BigQuery (opens in a new tab) enforces this through OAuth 2.0 access tokens for authentication, Identity and Access Management (IAM) roles granted to users, groups, and service accounts for authorization, and private network paths plus service perimeters for isolation. Row-level and column-level policies on governed tables then filter what an authorized connection can actually return to a dashboard.

2.1Authentication From BI Tools to BigQuery

Authentication from BI tools to BigQuery uses OAuth 2.0 access tokens (opens in a new tab) that grant temporary access to an API. Google's OAuth 2.0 server issues the token with an associated scope, and the visualization tool presents that token when it calls the public BigQuery REST API and the RPC-based BigQuery Storage API over the internet. Google BI products such as Looker Studio, Looker, Managed Service for Apache Spark, and Vertex AI Workbench instances, plus third-party tools such as Tableau, offer direct BigQuery integration using the BigQuery API. For tools and custom applications that do not integrate with the BigQuery API, Google collaborated with Magnitude Simba to provide ODBC and JDBC drivers that let existing tooling connect to BigQuery without custom API code.

2.2IAM Roles and Service Accounts for Least Privilege

Identity and Access Management (IAM) is the centralized control that decides who can run jobs and who can read or modify data. The BigQuery resource hierarchy places datasets as child resources of projects, and a table inherits permissions from the dataset that contains it, so organization and project roles affect the ability to run jobs or manage the project while dataset roles affect the ability to access or modify data inside the project. To grant access, an administrator assigns one or more roles to a user, group, or service account, and a service account is a non-interactive Google Account used by an application or compute workload rather than a person. At scale, administrators create functional groups for each user role, grant only required roles to the groups, and follow least-privilege and IAM best practices so membership changes automatically change access.

IAM provides three categories of roles for this model:

• Predefined roles are managed by Google Cloud and provide granular access for a specific service and common use cases.
• Basic roles include Owner, Editor, and Viewer and existed before IAM at dataset level.
• Custom roles provide granular access according to a user-specified list of permissions.

For external data queried through connections, BigQuery Connection adds two connection-specific roles: roles/bigquery.connectionUser enables users to use connections to connect with external data sources and run queries on them, while roles/bigquery.connectionAdmin enables users to manage connections. IAM controls access to the connection or agent resource itself, while access to the underlying data that is queried is controlled separately by permissions on the data source. In production environments, minimize the use of basic roles unless there is no alternative and use predefined IAM roles instead.

2.3Protecting Fine-Grained Data With Governed Tables

BigLake tables are governed BigQuery tables over Cloud Storage files that enforce policies such as row-level and column-level security at query time. The secure pattern depends on the BigQuery connection's service account rather than on each dashboard user holding direct file access, so the connection service account receives roles/storage.objectViewer or roles/storage.objectUser on the bucket while end users receive only BigQuery query rights. Data flows from Cloud Storage to BigQuery through the connection identity, and BigQuery then applies the table governance policies before data flows from BigQuery to the visualization tool. When users are migrated to BigLake tables, remove their permissions to directly access Cloud Storage because direct file access lets users bypass governance policies set on BigLake tables.

2.4Private Connectivity and Service Perimeters

Private connectivity controls which network path a visualization or middleware host uses to reach BigQuery APIs. Hosts with external IP addresses reach BigQuery over the internet, while hosts without external addresses must use Private Google Access controls that keep traffic on Google or private networks. Deploying a self-managed BI instance on Compute Engine takes advantage of Google's network backbone and minimizes latency between the instance and BigQuery, and pushing report filters as WHERE clauses reduces the amount of data that comes back over the network even though it does not reduce the amount BigQuery scans.

VPC Service Controls adds a virtual boundary around API use even when IAM would otherwise allow the call. A service perimeter defines the virtual boundaries from which a service can be accessed and restricts the use of import and export APIs, such as the Cloud SQL Admin API or Cloud Storage API pattern, so data cannot be moved outside the selected network locations. The perimeter applies even if the user is authorized by the IAM policy, which separates network containment from identity permission.

Deploying hybrid and multi-cloud integration architectures means connecting BigQuery (opens in a new tab) to visualization and analytics tools that run outside Google Cloud while keeping the path secure, fast, and scalable. Traffic flows from the tool to BigQuery through public APIs, private network paths, or standard database drivers, and identity flows from OAuth 2.0 tokens, service accounts, or Application Default Credentials to BigQuery. Federated connections extend the same model in the other direction, letting BigQuery query data that remains in Amazon S3, Azure Blob Storage, Cloud SQL, Spanner, and other external sources.

3.1Secure Network Paths for External and On-Premises Tools

Secure network paths determine how a business intelligence (BI) tool reaches the BigQuery REST API for query jobs and the RPC-based BigQuery Storage API for high-throughput reads. Tools deployed with external IP addresses access both APIs over the internet, while tools without public access must use private paths that keep traffic on internal addresses and Google's network backbone. Choosing the path depends on where the tool runs and whether a Virtual Private Cloud (VPC) network, Cloud VPN, or Cloud Interconnect link already exists.

• Hosts and services with external IP addresses send requests from the BI tool to the BigQuery REST API and BigQuery Storage API over the internet.
• Compute Engine virtual machine (VM) instances with only internal IP addresses use Private Google Access to reach BigQuery, enabled on a subnet-by-subnet basis in a VPC network.
• On-premises hosts with internal RFC 1918 addresses use Private Google Access for on-premises hosts, which carries traffic from the data center to Google Cloud over Cloud VPN or Cloud Interconnect and requires DNS, firewall rules, and routes in both networks.

When you manage your own BI instance, deploying it on Compute Engine places the tool close to BigQuery on Google's network backbone to minimize latency. If the BI solution supports it, set report or dashboard filters so they are pushed to BigQuery as WHERE clauses. The pushed filter does not reduce bytes scanned by BigQuery, but it reduces the result data that flows back from BigQuery to the tool over the network.

3.2BigQuery APIs and Client Libraries for Direct Integration

Direct integration uses BigQuery APIs when the visualization tool already knows how to call BigQuery natively. Requests flow from the tool or custom application to the BigQuery service, and authentication flows from the caller's credentials to BigQuery with every request. Google BI products such as Looker and Looker Studio, plus third-party tools such as Tableau, use this direct BigQuery API path instead of a generic database driver.

BigQuery exposes REST and gRPC interfaces for its services:

• BigQuery API for jobs, datasets, tables, and queries
• BigQuery Storage API for RPC-based bulk reads
• BigQuery Connection API for federated connections to external sources
• BigQuery Reservation API, Migration API, Data Transfer Service API, Analytics Hub API, and Data Policy API for capacity, migration, sharing, and governance operations

BigQuery client libraries reduce the code needed to call those APIs and are supported for C#, Go, Java, Node.js, PHP, Python, and Ruby. The most common developer credential is Application Default Credentials (ADC), which automatically finds credentials based on the environment. All BigQuery API requests use OAuth 2.0 access tokens, which are temporary strings granted by Google's OAuth 2.0 server and associated with a scope.

3.3JDBC and ODBC Drivers for Partner Visualization Tools

The Java Database Connectivity (JDBC) (opens in a new tab) driver and Open Database Connectivity (ODBC) driver let existing tools and infrastructure work with BigQuery without integrating with the BigQuery API directly. Both drivers are built with Magnitude Simba to translate standard database calls from the tool into BigQuery requests. Data flows from the partner tool through the driver to BigQuery, and query results flow back through the driver to the tool.

The JDBC driver is specific to BigQuery and cannot be used with other products or services. The INTERVAL data type is not supported with the BigQuery Storage Read API path, and all data manipulation language (DML) limitations apply. The driver itself is free to download, but standard BigQuery pricing applies to the queries and scans it generates.

3.4Federated Connections to Hybrid and Multi-Cloud Data

Federated connections let BigQuery query data that stays outside BigQuery, which is central to hybrid and multi-cloud visualization. A connection is created through the BigQuery Connection API (opens in a new tab), encrypted and securely stored in the BigQuery connection service, and shared by granting BigQuery connection Identity and Access Management (IAM) roles. Queries then flow from BigQuery to the external source through the stored connection, so database credentials are never sent as cleartext.

• Amazon S3 connections support external tables, queries, result exports, and datasets based on AWS Glue databases.
• Azure Blob Storage connections support external tables, queries, and result exports.
• Cloud SQL, Spanner, AlloyDB for PostgreSQL, and SAP Datasphere connections support federated queries that can join external operational tables with BigQuery sales or analytics tables.
• Google Cloud resource connections authorize access to Agent Platform remote models, remote functions on Cloud Run, BigLake tables, and object tables while retaining BigQuery access control.
• Spark connections connect to Managed Service for Apache Spark to run Python stored procedures for Spark.

No connection is needed to query Bigtable or Google Drive data. Usage and management requests for connections are recorded in BigQuery audit logs, which preserves ownership and accountability for cross-cloud access.

3.5Identity and Perimeter Controls for Visualization Access

Identity and perimeter controls decide which principal can reach which BigQuery resource from which network. Identity flows from a service account or user OAuth consent to BigQuery, where IAM enforces permissions at the project, dataset, and table levels, with access control lists (ACLs) for granular dataset and table sharing. Looker illustrates the tradeoff: a service account provides one shared identity for all queries and is required for persistent derived tables (PDTs), while OAuth passes each Looker user’s own Google identity to BigQuery for per-user auditing and limits.

Network controls add the boundary around that identity. VPC Service Controls restricts access to BigQuery resources from specific networks, and Private Google Access lets Compute Engine or on-premises hosts reach BigQuery without using public IPs. Encryption applies at rest and in transit by default, with options for customer-managed encryption keys (CMEK) and governance through data policies and Data Loss Prevention. Together, private connectivity plus per-identity authentication lets partner, on-premises, and multi-cloud visualization tools query BigQuery without exposing data to the public internet or sharing long-lived database passwords.

Key Facts

• BigQuery BI Engine is an in-memory service that accelerates dashboard queries by caching frequently queried columns and partitions, with uncached stages falling back to standard BigQuery slots.
• A join or materialized-view query uses BI Engine only when every table it touches is in the preferred tables list, including both the view and its base tables.
• BigLake tables enforce row-level and column-level security only when users query through BigQuery, so the connection service account holds storage access while direct Cloud Storage access is removed from users.
• Private Google Access lets Compute Engine VMs with only internal IPs and on-premises hosts over Cloud VPN or Cloud Interconnect reach BigQuery without external IP addresses.
• Partner tools without native BigQuery integration connect through Magnitude Simba JDBC drivers for Java applications and ODBC drivers for non-Java applications.