> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Connect to Google BigQuery

> Learn how to connect to BigQuery

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](https://cloud.google.com/bigquery/docs/access-control-basic-roles) in the BigQuery documentation.

## Connection type

Prophecy supports BigQuery in two different roles.

| Connection type               | Description                                                                                                                                                                                                                                     |
| ----------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **SQL Warehouse connection**  | BigQuery 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](/data-analysis/environment/fabrics/prophecy-fabrics). |
| **Ingress/Egress connection** | BigQuery 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.

| Feature                                                                                    | SQL Warehouse | Ingress/Egress |
| ------------------------------------------------------------------------------------------ | ------------- | -------------- |
| Run SQL queries                                                                            | Yes           | No             |
| Read and write data with a [Table gem](/data-analysis/gems/source-target/table/bigquery)   | Yes           | No             |
| Read data with a [Source gem](/data-analysis/gems/source-target/external-table/bigquery)   | Yes           | Yes            |
| Write data with a [Target gem](/data-analysis/gems/source-target/external-table/bigquery)  | Yes           | Yes            |
| Browse data in the [Environment browser](/data-analysis/development/studio/studio#sidebar) | Yes           | Yes            |
| Index tables in the [Knowledge Graph](/data-analysis/ai/knowledge-graph/knowledge-graph)   | No            | 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](https://cloud.google.com/resource-manager/docs/creating-managing-projects).                                                                                                                                                                                                                                                                        |
| Dataset               | The default location for target tables and [temporary tables](/data-analysis/development/runs/execution#external-data-handling). <br />Requires write permissions.                                                                                                                                                                                                                       |
| Authentication Method | The method used to authenticate with BigQuery. <br />See [Authentication methods](#authentication-methods) for details.                                                                                                                                                                                                                                                                  |
| Bucket Name           | A [Google Cloud Storage bucket](https://cloud.google.com/storage/docs/buckets) used for write optimization (recommended). <br />When specified, Prophecy writes data to the bucket, then loads it into BigQuery. <br />Note: Loading data from a bucket offers better performance than writing with the [BigQuery API](https://cloud.google.com/bigquery/docs/reference/rest) (default). |

## Authentication methods

You can authenticate your BigQuery connection using either [OAuth](#oauth-user-to-machine) or a [Private Key](#private-key-machine-to-machine).

Each method grants Prophecy the ability to read and write data in your BigQuery environment based on the [Identity and Access Management (IAM)](https://cloud.google.com/iam/docs/overview) 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](/data-analysis/administration/management/cluster-admin-settings/oauth-setup).

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](https://developers.google.com/identity/protocols/oauth2#expiration) time is 7 days.

<Note>
  For more about OAuth and how it works with Google Cloud, see [Using OAuth 2.0 to Access Google
  APIs](https://developers.google.com/identity/protocols/oauth2).
</Note>

### 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](https://developers.google.com/workspace/guides/create-credentials#service-account) from the Google Cloud console.
2. Paste the full JSON content into a [Prophecy secret](/data-analysis/environment/secrets/secrets) 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.

| BigQuery   | Databricks                          |
| ---------- | ----------------------------------- |
| STRING     | STRING<br />Alias: String           |
| BYTES      | BINARY<br />Alias: Binary           |
| NUMERIC    | DECIMAL128(38,9)<br />Alias: Bigint |
| BIGNUMERIC | DECIMAL256(38,9)<br />Alias: Bigint |
| FLOAT      | DOUBLE<br />Alias: Double           |
| RECORD     | STRUCT<br />Alias: Struct           |
| ARRAY      | ARRAY<br />Alias: Array             |
| INTEGER    | INT<br />Alias: Integer             |
| BOOLEAN    | BOOLEAN<br />Alias: Boolean         |
| DATE       | DATE<br />Alias: Date               |
| DATETIME   | TIMESTAMP<br />Alias: Timestamp     |
| TIME       | STRING<br />Alias: String           |
| GEOGRAPHY  | STRING<br />Alias: String           |
| INTERVAL   | STRING<br />Alias: String           |
| JSON       | STRING<br />Alias: String           |

<Info>Learn more in [Supported data types](/data-analysis/gems/data-types).</Info>