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.
Connection typeDescription
SQL Warehouse connectionBigQuery acts as the compute engine for a fabric. Prophecy generates SQL and BigQuery executes the transformations. To learn more about SQL Warehouse connections, see Prophecy fabrics.
Ingress/Egress connectionBigQuery is used only as a data source or target. Prophecy reads data from or writes data to BigQuery, but pipeline transformations run in another warehouse.

​
When BigQuery is a fabric

Pipeline
   ↓
Fabric
   ↓
BigQuery executes SQL
   ↓
Tables updated in BigQuery

​
When BigQuery is ingress/egress

Pipeline
   ↓
Warehouse executes SQL
(Databricks / Prophecy / Snowflake)
   ↓
Prophecy Automate
   ↓
BigQuery tables

​
Feature support

The table below outlines whether the connection supports certain Prophecy features.
FeatureSQL WarehouseIngress/Egress
Run SQL queriesYesNo
Read and write data with a Table gemYesNo
Read data with a Source gemYesYes
Write data with a Target gemYesYes
Browse data in the Environment browserYesYes
Index tables in the Knowledge GraphNoYes

​
Connection parameters

To create a connection with BigQuery, enter the following parameters.
ParameterDescription
Connection NameA unique name to identify the connection.
Project IDThe ID of your Google Cloud project.
DatasetThe default location for target tables and temporary tables.
Requires write permissions.
Authentication MethodThe method used to authenticate with BigQuery.
See Authentication methods for details.
Bucket NameA 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 (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.
BigQueryDatabricks
STRINGSTRING
Alias: String
BYTESBINARY
Alias: Binary
NUMERICDECIMAL128(38,9)
Alias: Bigint
BIGNUMERICDECIMAL256(38,9)
Alias: Bigint
FLOATDOUBLE
Alias: Double
RECORDSTRUCT
Alias: Struct
ARRAYARRAY
Alias: Array
INTEGERINT
Alias: Integer
BOOLEANBOOLEAN
Alias: Boolean
DATEDATE
Alias: Date
DATETIMETIMESTAMP
Alias: Timestamp
TIMESTRING
Alias: String
GEOGRAPHYSTRING
Alias: String
INTERVALSTRING
Alias: String
JSONSTRING
Alias: String
Learn more in Supported data types.