Google BigQuery
A BigQuery connection allows Prophecy to access tables and compute resources in your BigQuery project. This page explains how to use and configure a Google BigQuery connection in Prophecy.
Prerequisites
Prophecy connects to BigQuery using the credentials you provide. These credentials are used to authenticate your session and authorize all data operations during pipeline execution, including reading from and writing to tables. To use a BigQuery connection effectively, your user or service account should have:
OWNERdataset role to be able to read, insert, update, and delete datasets.
To learn more, visit Basic roles and permissions in the BigQuery documentation.
Connection type
Prophecy supports BigQuery as both a SQL Warehouse connection and an Ingress/Egress connection. To learn more about these different connection types, visit Prophecy fabrics.
Feature support
The table below outlines whether the connection supports certain Prophecy features.
| Feature | Supported |
|---|---|
| Run SQL queries | Yes — SQL Warehouse Connection only |
| Read and write data with a Table gem | Yes — SQL Warehouse Connection only |
| Read data with a Source gem | Yes |
| Write data with a Target gem | Yes |
| Browse data in the Environment browser | Yes |
Connection parameters
To create a connection with BigQuery, enter the following parameters.
| Parameter | Description |
|---|---|
| Connection Name | A unique name to identify the connection. |
| Project ID | The ID of your Google Cloud project. |
| Dataset | The default location for target tables and temporary tables. Requires write permissions. |
| Authentication Method | The method used to authenticate with BigQuery. See Authentication methods for details. |
| Bucket Name | A Google Cloud Storage bucket used for write optimization (recommended). When specified, Prophecy writes data to the bucket, then loads it into BigQuery. Note: Loading data from a bucket offers better performance than writing with the BigQuery API (default). |
Authentication methods
You can authenticate your BigQuery connection using either OAuth or a Private Key.
Each method grants Prophecy the ability to read and write data in your BigQuery environment based on the Identity and Access Management (IAM) roles assigned to the authenticated identity.
OAuth (User-to-Machine)
OAuth is a user-based authentication method best suited for interactive pipeline development. It allows each user to sign in with their own Google account, which ensures that data access is governed by their individual IAM roles and permissions.
When your fabric is configured to use OAuth, the following occurs when a user attaches the fabric to their project:
- The user is prompted to sign in with their Google account.
- Prophecy uses the user's credentials to authenticate the connection.
- The connection operates with the user's IAM roles and permissions.
- Token management, including refresh, is handled automatically by Google. The default refresh token expiration time is 7 days.
For more about OAuth and how it works with Google Cloud, see Using OAuth 2.0 to Access Google APIs.
Private Key (Machine-to-Machine)
Use a Service Account when you want a non-user identity for authentication. This is ideal for automated or shared processes that require stable, long-term access without re-authentication interruptions.
- Create and download a Service Account Key from the Google Cloud console.
- Paste the full JSON content into a Prophecy secret as text. Binary upload is not supported.
- Use this secret in the Service Account Key field of the BigQuery connection setup.
This method allows all team members with access to the fabric to use the connection in their projects. Those users inherit the access and permissions of the Service Account, as defined in its IAM roles.