Skip to main content
A BigQuery connection allows Prophecy to access tables in your BigQuery project. When configured as a SQL Warehouse connection, BigQuery also provides the compute engine used to run pipeline transformations. 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:
  • OWNER dataset 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 in two different roles.

​
When BigQuery is a fabric

​
When BigQuery is ingress/egress

​
Feature support

The table below outlines whether the connection supports certain Prophecy features.

​
Connection parameters

To create a connection with BigQuery, enter the following parameters.

​
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 (U2M) 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. To leverage user-based OAuth:
  1. Under Authentication method, select OAuth.
  2. Under App Registration, select the correct app registration or use the default.
If no app registrations appear, an admin must configure an OAuth app registration. When your connection is configured to use OAuth, the following occurs when a user attaches the fabric to their project:
  1. The user is prompted to sign in with their Google account.
  2. Prophecy uses the user’s credentials to authenticate the connection.
  3. The connection operates with the user’s IAM roles and permissions.
  4. 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 (M2M). This is ideal for automated or shared processes that require stable, long-term access without re-authentication interruptions.
  1. Create and download a Service Account Key from the Google Cloud console.
  2. Paste the full JSON content into a Prophecy secret as text. Binary upload is not supported.
  3. Open a BigQuery connection.
  4. Under Authentication method, select Private Key.
  5. Use the Prophecy secret in the Service Account Key field.
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.

​
Data type mapping

When Prophecy processes data from Google BigQuery using an external SQL warehouse, it converts BigQuery data types to a compatible type.
Learn more in Supported data types.