How Apache Superset connects to BigQuery with a service account
Dashboards need BigQuery data, but the connection has to respect project, dataset and cost boundaries from the first day. The supported route is the BigQuery SQLAlchemy dialect, sqlalchemy-bigquery, installed into the Superset image and authenticated with a Google Cloud service account whose key is stored in the database connection's Secure Extra field. Superset does not ship this driver in its base image, so it must be added at build time, and the image version must be pinned so the driver stays compatible with the Superset release you run. The project is self-hosted, so the Superset containers, the metadata database and the place where the service account key is held all sit in infrastructure you control, and the integration work is carried out inside your own environment rather than in a vendor's hosted service.
Before anyone creates a connection, decide which service account Superset will use and what it may read. Give it BigQuery data viewer access only on the datasets the dashboards need, and job user rights only in the project that will bill the queries. Confirm with your Google Cloud administrators how the service account key is issued, rotated and revoked, because the key lives inside the Superset metadata database, so access to that database becomes access to BigQuery. The SECRET_KEY setting that protects stored credentials must be set and backed up, as the Superset configuration documentation requires a user-specified key to start. Check the exact sqlalchemy-bigquery version that matches your Superset release in the project's own documentation, and test the connection in staging before it reaches production dashboards. Review the Apache Superset configuration documentation for the credential handling settings that apply to your version.
What you need before connecting Superset to BigQuery
Connecting Superset to BigQuery touches the release you run, the image it runs in, the Google Cloud identity it uses and the network path between them. Each item below is a decision to settle or a fact to confirm before anyone enters a connection string, because a gap in any one of them tends to surface later as a failed connection or an unexpected bill.
A supported Superset release and metadata database
Start by choosing the Superset release you will run and confirming its metadata database. The architecture and configuration documentation test PostgreSQL and MySQL for production, and list supported versions of each, so check your chosen release against those lists. SQLite and the bundled Docker Compose PostgreSQL container are not recommended for production. Settle this first, because the upgrade path and the driver requirements both depend on the release.
A Python environment that accepts the BigQuery driver
BigQuery access in Superset depends on the sqlalchemy-bigquery dialect being installed into the same image or environment that runs Superset. Confirm that your build pipeline can add the package and that it resolves cleanly with the Superset dependencies already pinned in that image. Do not assume a version pairing. Check the exact driver and dialect versions against your Superset release in the official documentation, and record the result in your image build notes.
A GCP project and dataset access plan
Decide which Google Cloud project holds the datasets Superset will query, and whether billing for query work sits in that project or elsewhere. Map each dataset and table that dashboards need to a named group, so access is granted to specific datasets rather than to the whole project. Write down which datasets are sensitive and which are reference data. This plan determines both the service account scope and the list of datasets you will later test in staging.
A dedicated service account with read-only roles
Create a separate service account for Superset rather than reusing a personal or developer credential. Grant it only the read roles it needs on the agreed datasets, and avoid project-wide editor or owner roles. Plan who holds the key material, how it will be stored in Superset, and how it will be rotated. Keeping this account narrow limits what a compromised Superset instance could read and makes audit logs easier to interpret.
Network egress from Superset to Google APIs
Superset must reach Google APIs over the network to run queries. If your Superset hosts sit behind a restrictive proxy, a private network without internet egress, or an egress firewall, confirm the required Google endpoints are allowed from the worker and web tiers. Also confirm that any proxy or certificate inspection in your environment is compatible with Google's TLS endpoints. Test this from the actual hosts that will run queries, not from a workstation.
A staging Superset for testing
Build and test the connection in a staging Superset that mirrors production's release, image and metadata database before any production change. Use it to confirm the driver loads, the service account can read only the intended datasets, queries return results and the chosen caching settings behave as expected. Upgrades and configuration changes follow the same rule, so the staging instance also becomes the place where you rehearse them.
Configuration sequence for the BigQuery connection
The sequence takes Superset from a clean image to a BigQuery database connection that authenticates with a scoped service account, tests cleanly and exposes only the datasets the team needs. Each step ends with a check so problems surface before dashboards depend on the connection.
Add the BigQuery driver to the Superset image
The platform team adds sqlalchemy-bigquery to the Python environment that builds the Superset image, then rebuilds and redeploys the web and worker containers so every process loads the same driver. Superset does not bundle this driver by default, so the image must carry it before any connection can be tested. Done means the installed package version is recorded in the image build output and a shell inside the running container can import the BigQuery dialect without error. Confirm the driver version against the Superset release you run, since compatible versions depend on the release.
Create a scoped service account
A cloud administrator in your Google Cloud project creates a dedicated service account for Superset, separate from any human user or other application. The account receives only the roles needed to run queries, such as job creation, and read access granted on the specific datasets that dashboards will use, not on the whole project. Done means the IAM policy shows the account bound to dataset-level roles, and a review confirms no broad project-level data viewer or editor role was added. Ask your Google Cloud team to confirm the exact role names for your query pattern before granting them.
Generate and store the key securely
The administrator creates a JSON key for the service account and moves it straight into your secrets manager, then deletes any local copy and removes the download from shared folders. Access to the secret is restricted to the team that operates Superset. Done means the key exists only in the secrets store with a named owner and an access log, and the original file is gone from workstations and tickets. Where your organization allows it, prefer short-lived or workload-based credentials and confirm with Google Cloud documentation whether that option suits your hosting model.
Add the database with a project-level URI
An administrator opens Database Connections in Superset and adds a BigQuery database using a SQLAlchemy URI that names the Google Cloud project, not a dataset, so that Superset can list every dataset the service account is allowed to see. Display name and any descriptive metadata are set at this point. Done means the database record saves and appears in the connection list with the project identifier visible. Keep the URI free of key material, because the credentials are supplied in the next step.
Paste key JSON into Secure Extra
In the same database form, the administrator pastes the service account key JSON into the Secure Extra field, which stores it encrypted in the metadata database rather than in the plain-text extra settings. The field should hold only the credentials object, and the pasted text should be removed from any editor history on the admin workstation. Done means the connection record saves without the key appearing in exported configuration or in the database list view. Review your Superset version's documentation to confirm how Secure Extra is handled before relying on it for your compliance evidence.
Test the connection
The administrator selects Test connection in the database form, which confirms that Superset can authenticate to Google Cloud and reach the project. A failure usually points to a missing role, a malformed key, a wrong project identifier or a blocked network path, so the team checks those in that order. Done means the test reports success and the Superset logs show an authenticated session with no credential errors. Record the test result and the service account name in the change ticket.
Register datasets and run a bounded query
An analyst opens the SQL Lab with the new connection, runs a query against one approved table with an explicit LIMIT clause, and checks that the row count returned matches a figure obtained directly in the BigQuery console. Once the result is confirmed, the team registers only the datasets that dashboards need as Superset datasets. Done means the bounded query returns the expected rows and the scan size reported by BigQuery is recorded as a baseline before any dashboard is built. Confirm with your analytics owners which tables are approved before registration.
How service account roles and Superset roles map to BigQuery data
Your data team decides which BigQuery projects and datasets Superset should expose, then creates one Superset database connection for each scope that needs its own boundary. BigQuery decides what the service account can read, while Superset decides which connection, dataset and chart a logged-in user can open. Map each Superset database to a project or a set of datasets that share the same sensitivity, and name the connections so analysts can tell them apart.
- Project-level scope: Put the project ID in the connection string so the database entry maps to one billing and data boundary.
- Dataset grouping: Group datasets with similar sensitivity under one connection, and split sensitive datasets into their own.
- Dataset visibility in Superset: Confirm which datasets appear under each database and remove any that were added by default.
- Naming convention: Use names such as the project and environment in the Superset database label so reviewers can read the mapping.
- Change records: Keep a written list of which projects and datasets each Superset database exposes.
Before release, confirm that each Superset database shows only the datasets your data owners approved.
Your security team should treat the service account as the ceiling for what Superset can ever read, regardless of what a Superset role grants. Grant the service account the narrowest BigQuery IAM roles that let queries run against approved datasets, and review them as you would any privileged machine identity. Superset roles then narrow what each user can reach, but they cannot widen what BigQuery allows.
- Least-privilege IAM: Assign data viewer roles at dataset level where possible, and avoid project-wide roles unless a specific need is documented.
- Job permissions: Confirm which roles allow the service account to create query jobs, since queries are billed and recorded under that identity.
- Superset role mapping: Use Superset roles and row level security to restrict users to the datasets they are approved for.
- Separate identities: Use different service accounts for production and non-production connections.
- Key handling: Confirm how the service account key is stored and rotated, and who can read it.
Review the IAM bindings and the Superset role assignments together, because a gap in either layer can expose data.
When every Superset user queries through one shared connection, BigQuery sees one identity for all of them. BigQuery audit logs will show the service account, not the analyst who opened the dashboard, so per-user attribution has to come from Superset's own logs or from a separate design. This matters for investigations, cost allocation and audit evidence, and it should be settled before rollout rather than discovered afterward.
- Shared identity: Confirm that BigQuery audit records identify the service account and not the Superset user.
- Superset logging: Check which Superset event and audit logs record who ran a query and which dashboard was open.
- Cost attribution: Shared connections make per-team cost reports harder, so decide whether separate connections per team are needed.
- Impersonation options: Ask whether your deployment can pass user identity to BigQuery, and confirm the supported method with the vendor documentation for your versions.
- Retention: Confirm how long Superset and BigQuery logs are kept against your audit requirements.
Ask your auditors which attribution evidence they expect before you choose between shared and per-team connections.
Mapping BigQuery projects, datasets and identities to Superset objects
This table maps each Google Cloud object (project, dataset, service account) to the Superset object that consumes it, with the attribute to confirm at each step. Read it top to bottom when designing access, because a mistake at the connection level usually means every dashboard built on it inherits the same data access. Row level security and per-user impersonation depend on the connection method you choose, so verify those against your Superset version before assuming them.
| Object | Role in the integration | Attribute to confirm |
|---|---|---|
| GCP project | Billing and query scope for every job run through the connection | Project ID in the connection string, and which project is billed for query bytes |
| BigQuery dataset | Schema Superset introspects to build datasets and charts | Dataset location and whether it is shared across projects you do not own |
| Service account | Identity Superset uses to submit queries to BigQuery | Role grants limited to the datasets needed, with no project-wide editor role |
| Service account key | Credential stored in the database connection's Secure Extra field | Who can read the key, where it is stored, and the rotation procedure you will follow |
| Superset database connection | Binds one project, one service account and one SQLAlchemy URI | Whether a separate connection exists per project and per trust boundary |
| Superset dataset | Virtual table over a BigQuery table or view, the unit charts use | Whether the dataset points to a view that enforces filters you rely on |
| Superset role | Controls which databases, datasets and dashboards a user can see | Whether roles grant access to the connection, to individual datasets, or both |
| Per-user identity at BigQuery | Lets BigQuery audit logs show the real analyst rather than the shared service account | Whether your Superset version and chosen connection method support per-user impersonation; verify before relying on it |
Securing the service account key and query access
A BigQuery connection from Superset is only as safe as the service account behind it. The controls below cover where the key lives, what the account can reach, how it is rotated and what Superset users can run, so that a leaked key or a careless query has a small blast radius.
Store the key in Secure Extra
Paste the service account JSON into the database connection's Secure Extra field rather than into images, shared config files or repositories. Superset encrypts stored database credentials with its SECRET_KEY, so the key is protected at rest in the metadata database. Confirm who holds the Database connection permission in Superset, because users with that permission can view or edit connection settings and may be able to read the stored credentials. Restrict that role to a small set of administrators.
Plan the SECRET_KEY as a credential
The SECRET_KEY that protects stored BigQuery credentials is also a production secret that Superset requires at startup. Losing it makes stored connection secrets unreadable, and changing it without a plan can break connections. Use PREVIOUS_SECRET_KEY during a rotation so existing values can be re-encrypted, back up the key in your secrets manager, and rehearse the rotation in staging before production.
Grant roles on datasets, not projects
Create the service account in a dedicated GCP project or folder and grant it BigQuery Data Viewer on the specific datasets Superset needs, not at the project level. Add BigQuery Job User at the project where queries run, since query jobs are billed and executed there. Ask your cloud team to confirm the exact role names in current Google Cloud documentation, and avoid editor or owner roles, which let the account change data or manage other resources.
Keep the account read-only
The service account should only read. Grant no write, delete or table-creation permissions, so a compromised Superset admin cannot alter source tables through the SQL Lab interface. If teams need derived tables, create them in a separate dataset owned by a different pipeline account. Verify the grants by attempting a DML statement with the key in a test connection and confirming it is denied.
Limit SQL execution in Superset
Restrict SQL Lab access to the users who need ad hoc queries, and keep chart builders on governed datasets. Decide whether to enable asynchronous query execution, set row limits and query timeouts, and confirm that dataset-level restrictions apply to the sessions that Superset opens with the service account. Because every query is billed by bytes scanned, a single wide SELECT from an unrestricted user can be expensive, so review the limits with the warehouse owner.
Rotate and delete keys on a schedule
Set a rotation schedule for the service account key and document the steps: create the new key, update Secure Extra, test the connection, then delete the old key in Google Cloud. Remove keys that are unused, and check audit logs for the account before deletion. Ask whether your organization can use keyless options such as workload identity for the Superset host, since they remove long-lived key files entirely.
Control egress and keep keys out of images
Allow outbound traffic from the Superset workers only to the Google APIs the connection needs, and log that traffic at the firewall or proxy. Keep the key out of Docker images, build arguments and Helm values files committed to Git, and inject it at runtime from a secrets store. Scan images and repositories for accidental key material before each release.
Why each dashboard refresh can cost money in BigQuery
On-demand BigQuery queries are billed by the amount of data they scan, so every chart load, filter change and dashboard refresh in Superset can trigger a billable scan. A dashboard with twelve charts that each run a query against a wide table is not one query, it is twelve, and each filter change can re-run all of them. The cost is driven by what the query reads, not by how long it takes or how many rows appear on screen. Before go-live, ask your data team which pricing model applies to each project and region, and confirm current rates on the Google Cloud pricing page for BigQuery rather than relying on any figure in this guide, because rates and included allowances can change and differ by edition and commitment.
Most of the scan volume is decided in the data model and the dataset design, not in Superset itself. Partitioning and clustering determine whether a filter on a date or a category lets BigQuery skip blocks it does not need. Superset datasets built on SELECT * or on wide tables force scans of columns no chart uses, so limiting each dataset to the columns its charts need is one of the cheapest reductions available. Default date ranges matter as much: a dashboard that opens on all-time data scans the full history on every load, while a default window of recent days keeps routine views small. Dataset-level design also decides whether several charts share one curated, pre-aggregated table or each chart reads raw events.
The dashboard itself multiplies the effect. Count the charts per dashboard, the default filters that run on load, and how many users open the dashboard during a typical day, because each open repeats the work. Superset's caching and async query settings can reduce repeat scans, and a later section covers how to cache dashboards to limit scans. Ask your team to estimate scan volume per dashboard view in a test project using BigQuery's query cost preview, and to set project-level query limits or budget alerts before users receive access. These checks are inexpensive to run during build and expensive to discover on the first monthly invoice.
Common BigQuery connection failures in Superset and how to fix them
This table lists the failures teams most often hit when connecting self-hosted Superset to BigQuery, with the likely cause and the fix for each. Read it when a connection test fails or a dashboard query runs longer or costs more than expected. Most failures trace to credentials, project selection, the driver in the image, or query design, so check those in that order.
| Failure | Likely cause | Fix |
|---|---|---|
| Invalid service account JSON | The key was pasted into Secure Extra with line breaks, smart quotes or a truncated private key, so the JSON does not parse. | Re-paste the full key file as a single valid JSON object in the credentials field, then test the connection again. Confirm the private key line breaks are preserved as escaped newlines. |
| Missing BigQuery read permissions | The service account has no read role on the target project or datasets, so metadata or table queries are refused. | Grant the service account read access on the datasets Superset must query, and the job permission needed to run queries. Confirm the exact roles with your BigQuery administrator and test with a non-sensitive dataset first. |
| Wrong project in the connection URI | The project ID in the URI points to a project the service account cannot bill or query, or to a different project than the datasets used in charts. | Set the project that holds the datasets and the billing project the service account can use. Check the project ID spelling against the Google Cloud console. |
| Dataset location mismatch | A query joins or references tables stored in different BigQuery locations, which BigQuery does not allow in one job. | Keep the datasets used by a single dashboard in the same location, or copy data into one location before building the chart. Ask your data team which location each dataset uses. |
| Driver missing after an image rebuild | The sqlalchemy-bigquery package was installed into a running container by hand and was not written into the image build, so a rebuild removes it. | Add the BigQuery driver package to the Superset image definition and rebuild from that file. Confirm the driver version against the Superset release you run before deployment. |
| Network egress blocked to Google APIs | Firewall, proxy or egress rules in the Superset network block outbound calls to Google endpoints, so authentication or query submission times out. | Allow outbound HTTPS from the Superset workers and web servers to the Google endpoints your BigQuery client uses, or route them through an approved proxy. Test from inside the same container or pod that runs Superset. |
| Unbounded queries driving scan costs | Charts select all columns or full history without partition filters, so each refresh scans large tables and bills for the bytes read. | Use partitioned and clustered tables, require date filters in datasets, and set dashboard caching so repeat views do not rescan. Ask your data team to review the most used charts before they go to all users. |
Questions buyers ask before connecting Superset to BigQuery
What drives the cost of connecting Superset to BigQuery?
The cost is driven mostly by the scope of the work, not by a license, because Apache Superset is released under the Apache License 2.0 and has no paid edition. The main cost components are the time to set up the service account and IAM roles, build the Superset image with sqlalchemy-bigquery, configure credentials in Secure Extra, design datasets and caching, and test access controls. Ask for a quote that lists each component, who performs it, and which parts are one-time and which recur, such as upgrades and patching. Cost also rises with the number of projects, datasets and dashboards, and with the amount of role mapping your identity provider requires.
How long does it take to connect Superset to BigQuery?
The duration depends on the environment and the scope agreed in writing, so no fixed timeline applies to every connection. A single project with one service account and a few datasets is a much smaller task than a multi-project setup with row-level access rules and single sign-on. Before estimating, confirm the Superset version you will run, whether the metadata database and caching layer already exist, and how many BigQuery projects and datasets must be exposed. DeployOpen provides a timeline in the written scope once those inputs are known.
What does our team need to provide for the BigQuery connection?
Your team needs to provide access to the Google Cloud projects that Superset will query, the names of the datasets and the tables that analysts should see, and an owner who can approve IAM roles for a dedicated service account. You will also need the target Superset environment, including where it runs, which metadata database it uses (the Superset configuration documentation lists supported PostgreSQL 10 to 17 and MySQL 5.7 and 8.x), and how users sign in. Confirm with your cloud team whether billing export and query cost controls are already enabled, because these affect how you monitor spend.
Can our team connect Superset to BigQuery without a partner?
Yes, a capable platform team can connect Superset to BigQuery without an outside partner, provided it has experience with Superset, Google Cloud IAM and Python packaging. The work involves installing sqlalchemy-bigquery into the image, creating a service account with the least privileges needed, storing the key correctly and testing queries against real datasets. The harder parts are usually the security decisions: which datasets are exposed, how roles map to data, and how query cost is limited. An external team is most useful when those decisions need independent review or when your internal team lacks time to test them.
How does caching reduce BigQuery cost in Superset?
Caching reduces BigQuery cost because repeated dashboard views can be answered from the cache instead of scanning the same tables again. Superset's optional caching layer stores query results for a configured period, and it works together with the Celery workers that power alerts, reports and async queries. The cache lifetime is a trade-off: longer caching lowers scan cost but shows older data, so business owners should decide acceptable freshness per dashboard. Confirm how your team will measure scans before and after enabling caching, and ask any partner to document cache settings per dataset.
What does a fixed-scope BigQuery engagement cover?
A fixed-scope engagement covers the activities, deliverables and timelines written down before work starts. For this integration, that typically means agreeing the Superset environment, the BigQuery projects and datasets in scope, the service account and IAM design, how credentials are stored in Secure Extra, the caching approach, the verification steps, and handover documentation. It does not automatically include ongoing support, upgrades or changes to data models, which can be covered by a retainer. Ask for the written scope to name each exclusion so you can see what remains with your team.
What should we check before upgrading Superset after the BigQuery connection is live?
Before upgrading, back up the metadata database, test the upgrade in staging, and read UPDATING.md for breaking changes between versions, as the Superset upgrade documentation advises. Then run superset db upgrade and superset init in the staging environment and rerun your BigQuery test queries. Check that the sqlalchemy-bigquery version in your image is compatible with the new Superset release, because driver requirements can change between versions. Keep the service account and Secure Extra configuration under version control so that a rebuilt image restores the connection.
How DeployOpen scopes and delivers the BigQuery integration
DeployOpen plans, builds, documents and supports self-hosted Apache Superset inside your own infrastructure, including its connection to Google BigQuery. Engagements start from a written scope that sets out the projects, service accounts, datasets and access rules involved before any work begins.
How we scope
- Inventory the BigQuery projects, datasets and tables Superset dashboards will read, and the owners who approve access.
- Agree which service account roles, Secure Extra settings and caching layers are in the written scope.
- Record cost controls, such as query limits and cache settings, and the data sources that stay out of scope.
How we build
- Install the sqlalchemy-bigquery driver into the Superset image and verify the connection in a staging environment first.
- Store service account credentials in Secure Extra or your secrets store, and test that they are not exposed in the interface.
- Configure caching and dashboard design choices, then measure query behavior against representative dashboards before go-live.
How we hand over
- Document the connection setup, service account scope, driver version and rotation steps in a runbook you own.
- Walk your platform and data teams through cost-per-query drivers and the checks to run before adding new datasets.
- Deliver configuration in a form your team can review, version and reproduce in your own environment.
How we support
- Provide ongoing support and upgrades as a retainer, scoped in writing with your team.
- Review Superset upgrade notes and driver compatibility before each planned version change.
- Run continuous security testing of the Superset deployment and its BigQuery access path when retained.
