---
description: Step-by-step guide to ingest your data from Google BigQuery into RudderStack.
---

# Google BigQuery

[Google BigQuery](https://cloud.google.com/bigquery) is an industry-leading, fully-managed cloud data warehouse that lets you store and analyze petabytes of data in no time.

RudderStack supports Google BigQuery as a source from which you can ingest data and route it to your desired downstream destinations.

## Granting permissions

RudderStack requires you to grant certain user permissions on your BigQuery warehouse to successfully access data from it.

Perform the following steps in the **exact** order to grant these permissions:

### Step 1: Creating a role and granting permissions

1. Go to the [Roles](https://console.cloud.google.com/iam-admin/roles) section of Google Cloud Platform dashboard and click on **CREATE ROLE**.

![Google Cloud Platform dashboard create role](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-989b91f004e591820de99127052e912e2aaa8d5e%2FGCP-create-role.png?alt=media)

1. Fill in the details as shown:

![GCP role details](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-adaa7beaf9f886816342b67e390a7340e35f9ea5%2Fgcp-role-details.png?alt=media)

1. Click on **ADD PERMISSIONS** and add the following permissions:

```
bigquery.datasets.get
bigquery.jobs.create
bigquery.jobs.list
bigquery.tables.create
bigquery.tables.get
bigquery.tables.getData
bigquery.tables.list
bigquery.tables.update
bigquery.tables.updateData
```

1. Finally, click on **CREATE**.

### Step 2: Creating a service account and attaching the role to it

1. Go to [Service Accounts](https://console.cloud.google.com/iam-admin/serviceaccounts) and select the project which has the dataset or the table that you want to use.
2. Click on **CREATE SERVICE ACCOUNT**.

![Create service account in GCP](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-acd79619e6906737f94ac174d46cd83b62722a35%2Fgcp-create-service-account.png?alt=media)

1. Fill in the **Service Account details** as shown below, and click on **CREATE AND CONTINUE**:

![](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-994c3a139729ea8a6631c4df85794624922807b6%2Fgcp-service-account-details.png?alt=media)

Note down the **Service account ID**. This ID is required while creating the RudderStack schema and granting the required permissions to it.

1. Fill in the **Role** details as shown below, and click on **CONTINUE**:

![Service account role details](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-7713f9a40097a1b20d61d6cd4f35e3bf6be3ed38%2Fgcp-service-account-role-details.png?alt=media)

1. Click on **DONE** to move to the list of service accounts.

### Step 3: Creating and downloading the JSON key

1. Click on the three dots icon under **Actions** in the service account that you just created and select **Manage keys**, as shown:

![Managing keys in GCP](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-e74aabe9fa9f61ad0e8aab86311b8ea14df32969%2Fmanage-keys.png?alt=media)

1. Click on **ADD KEY**, followed by **Create new key**, as shown:

![GCP Adding a new key](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-b455e5e58e1a6a00c75736135e6d5d5254339fb2%2Fgcp-add-key.png?alt=media)

1. Select **JSON** and click on **CREATE**.

![](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-dbedee486ebef43b01c782de118cbfb653599a95%2Fcreate-new-key.png?alt=media)

A JSON file will be downloaded on your system. This file is required while creating a BigQuery warehouse source in RudderStack, explained further in this document.

### Step 4: Creating the RudderStack schema and granting permissions

1. From your [BigQuery SQL workspace](https://console.cloud.google.com/bigquery), run the following command to create a dedicated schema `rudderstack_`.

```sql
create schema rudderstack_;
```

The `rudderstack_` schema is used by RudderStack for storing the state of each data sync. **This name should not be changed**.

1. Grant full access to the `rudderstack_` schema for the RudderStack service account you created above. Replace `<SERVICE_ACCOUNT_ID>` with the service account ID you specified in [Step 2: Creating a service account and attaching role to it](#step-2-creating-a-service-account-and-attaching-the-role-to-it).

```sql
GRANT `roles/bigquery.dataOwner`
     ON SCHEMA rudderstack_
     TO "serviceAccount:<SERVICE_ACCOUNT_ID>";
```

## Setting up the BigQuery source in RudderStack

To set up BigQuery as a source in RudderStack, follow these steps:

### Naming the source

1. Log into your [RudderStack dashboard](https://app.rudderstack.com/).
2. From the left navigation bar, go to **Source** > **New Source** > **Reverse ETL**. Then, select **BigQuery**, as shown:

![Select Reverse ETL source in RudderStack](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-9e487644a76d866f455d988b20744be087ac602a%2Fselect-source.png?alt=media)

1. Assign a name to your source.

### Configuring the connection credentials

1. Choose the relevant option from **Table** or **Model** to use the source to sync data from either a warehouse table or a model.

For more information on the difference between the **Table** and **Model** options when creating a Reverse ETL source, refer to the [FAQ](#faq) section below.

If you have chosen the **Model** option, skip the next steps and refer to the [Schedule settings](#schedule-settings) section directly.

1. Enter the relevant settings in the **Connection Credentials** section as listed below:

![](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-52be8d98429e08c6d1fd1fc873a3ab50764b0e04%2Fbigquery-connection-settings.png?alt=media)

* **Credentials** - Enter the GCP Service Account credentials JSON.
* **Project ID** - Enter the `project_id` from the GCP Service Account credentials JSON.
* **Service account** - Enter the `client_email` from the GCP Service Account credentials JSON.

If you've configured BigQuery as a source before, you can select the existing credentials under the **Use existing credentials** option.

1. Click on **Continue** to verify your credentials. RudderStack will then verify and validate your credentials.

For more information on these validation steps, refer to the [FAQ](#faq) section.

1. Once verified, click on **Continue** to proceed.

### Schedule settings

1. Specify the **Schedule Settings** to schedule the data syncs from your BigQuery instance.

RudderStack lets you schedule data syncs for your Reverse ETL sources and specify how and when the syncs will run. For more information on the **Basic**, **CRON**, and **Manual** schedule types, refer to the [Sync Schedule Settings](https://www.rudderstack.com/docs/reverse-etl/common-settings/sync-schedule-settings/) guide.

1. After specifying the schedule type and run settings, click on **Continue** to finish the setup.

BigQuery is now successfully configured as a source in your RudderStack dashboard. You can further connect this source to your preferred destination by clicking on **Add Destination** button, as shown:

![Add destination in RudderStack](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-7ceee14ababcfb5fdad67f135f4c2ef4fcecfaa8%2Fdestination.png?alt=media)

If you have already configured a destination in RudderStack, choose the **Use Existing Destination** option which will take you to the **Schema** tab in the source settings. To add a new destination from scratch, select the **Create New Destination** option which will take you to the destination configuration page.

## Specifying the data to import

While connecting a destination to your Reverse ETL source, you can use the default JSON mapping or the [Visual Data Mapping](https://www.rudderstack.com/docs/reverse-etl/features/visual-data-mapper/) feature.

Based on the option(Table/Model) you chose while setting up the Reverse ETL source, follow the relevant guide for detailed steps:

* [Importing Data using Tables](https://www.rudderstack.com/docs/reverse-etl/common-settings/importing-data-using-tables/)
* [Importing Data using Models](https://www.rudderstack.com/docs/reverse-etl/common-settings/importing-data-using-models/)

## FAQ

### What is the difference between the Table and Model options when creating a Reverse ETL source?

When creating a new Reverse ETL source, you are presented with the following two options from which RudderStack will sync the data:

![](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-4dde50492994645e418c239b199578d0d174c3f7%2Ftable-model-options.png?alt=media)

* When you choose **Table**, RudderStack imports all the data associated with the specified table during the sync.
* When you choose **Model**, RudderStack imports the data by running the query specified in the connected model, during the sync.

### What do the three validations under Verifying Credentials imply?

When setting up a Reverse ETL source, once you proceed after entering the connection credentials, you will see the following three validations under the **Verifying Credentials** option:

![](https://876606571-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-Lq5Ea6fHVg3dSxMCgyQ%2Fuploads%2Fgit-blob-af3c8dc7d9a57bd215a380b4a4fea19cfce12301%2Fvalidations.png?alt=media)

These options are explained below:

* **Verifying Connection**: This option indicates that RudderStack is trying to connect to the warehouse with the information specified in the connection credentials.

If this option gives an error, it means that one or more fields specified in the connection credentials are incorrect. Verify your credentials in this case.

* **Able to List Schema**: This option checks if RudderStack is able to fetch all the schema details using the provided credentials.
* **Able to Access RudderStack Schema**: This option implies that RudderStack is able to access the `rudderstack_` schema that you have created by successfully running all the commands in the [**Creating the RudderStack schema and granting permissions**](#step-4-creating-the-rudderstack-schema-and-granting-permissions) section.

If this option gives an error, verify if you have successfully created the `rudderstack_` schema and given RudderStack the required permissions to access it. For more information, refer to [Creating the RudderStack schema and granting permissions](#step-4-creating-the-rudderstack-schema-and-granting-permissions) section.

## Contact us

For queries on any of the sections covered in this guide, you can [**contact us**](mailto:%20docs@rudderstack.com) or start a conversation in our [**Slack**](https://rudderstack.com/join-rudderstack-slack-community) community.
