Google BigQuery Reverse ETL Source

Google 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.

Grant permissions

Before you set up BigQuery as a source, you must grant certain permissions on your BigQuery warehouse for RudderStack to access data from it.

RudderStack can authenticate to BigQuery with a service account key (described in the steps below) or through workload identity federation, which doesn’t require you to create or share a key.

Choose your authentication method

• Service Account Key: Step 1 > Step 2 > Step 3 > Step 4
• Workload identity federation with service account impersonation: Step 1 > Step 2 > Set up workload identity federation > Step 4
• Workload identity federation with federated identities: Step 1 > Set up workload identity federation > Step 4

Step 1: Create role and grant permissions

1. Go to the Roles section of Google Cloud Platform dashboard and click CREATE ROLE.

2. Fill in the details as shown:

3. Click ADD PERMISSIONS and add the following permissions individually:

Read-only:

bigquery.datasets.get
bigquery.jobs.list
bigquery.tables.get
bigquery.tables.getData
bigquery.tables.list
bigquery.routines.get
bigquery.routines.list

Read-write:

bigquery.jobs.create
bigquery.tables.create
bigquery.tables.update
bigquery.tables.updateData
bigquery.tables.delete

4. Click CREATE after adding the permissions.

Click here to see how the above options are seen in the Google Cloud Console.

Step 2: Create service account and attach role

Complete this step if you’re using a Service Account Key or workload identity federation with service account impersonation. If you’re granting access directly to federated identities, skip creating a service account and assign the role from step 1 directly to the federated principal in the workload identity federation setup.

1. Go to Service Accounts and select the project which has the dataset or the table that you want to use.
2. Click CREATE SERVICE ACCOUNT.

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

4. Under Grant this service account access to project, select the role you created in Step 1: Creating a role and granting permissions section above.

5. Click DONE to move to the list of service accounts.

Note down the service account ID. You will need this ID while creating the RudderStack schema and granting the required permissions to it. If you’re using workload identity federation with service account impersonation, the service account email is also the value you enter in Target Service Account.

Step 3: Create and download JSON key

Complete this step only if you’re using a Service Account Key. Workload identity federation doesn’t require a key.

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

2. Click ADD KEY, followed by Create new key:

3. Select JSON and click CREATE.

A JSON file is downloaded to your system. This file is required while setting up the BigQuery source with Service Account Key authentication in RudderStack.

Step 4: Create RudderStack schema and grant permissions

1. In your BigQuery SQL workspace, go to the project associated with the Project ID you specify in RudderStack, then run the following command to create a dedicated schema rudderstack_.

The rudderstack_ schema stores Reverse ETL sync state, snapshots, and related tables. Do not change this name.

See _rudderstack Schema Reference for more details.

create schema rudderstack_;

The rudderstack_ schema must be in the same BigQuery location as the dataset you sync from — RudderStack creates snapshot tables with a single statement that reads your source tables, and BigQuery cannot query across locations. create schema uses your project’s default region, so if your source dataset is elsewhere, set the location explicitly. For example, for a source dataset in europe-west3, run:

sql Copy

create schema rudderstack_ OPTIONS (location = "europe-west3");

2. Grant full access to the rudderstack_ schema according to your authentication method:

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

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

Set up workload identity federation for RudderStack

With workload identity federation, RudderStack accesses BigQuery through a workload identity pool in your Google Cloud project, so you don’t create or share a service account key.

1. In the Google Cloud console, enable the Security Token Service API. If you plan to use service account impersonation, also enable the IAM Service Account Credentials API. Then, go to IAM & Admin > Workload Identity Federation and click Create pool. Enter a name and ID for the pool.
2. Add an AWS provider to the pool. Enter a name and ID for the provider, and enter 422074288268 as the AWS account ID.

The pool/provider ID must be 4–32 characters using lowercase letters, numbers, and hyphens, and cannot start with gcp-.

3. Under Attribute mapping, add the following mappings:

4. Under Attribute conditions, add the following condition, replacing <WORKSPACE_ID> with your workspace ID. Then, save the provider.

attribute.workspace == '<WORKSPACE_ID>'

This condition scopes the provider to a single RudderStack workspace. If you connect BigQuery sources from multiple workspaces, add a separate provider for each workspace or extend the condition to list every workspace ID.

5. Grant your workspace access to BigQuery, either directly or through a service account:

principalSet://iam.googleapis.com/projects/<PROJECT_NUMBER>/locations/global/workloadIdentityPools/<POOL_ID>/attribute.workspace/<WORKSPACE_ID>

6. In the RudderStack dashboard, set Authentication Method to Workload Identity Federation and enter the required warehouse credentials.

If you’re also configuring a BigQuery destination, see Set up workload identity federation for the BigQuery destination.

Changes to IAM grants, attribute mappings, and attribute conditions can take several minutes to take effect. Until then, credential verification can fail with a permission error.

Set up BigQuery source in RudderStack

1. Log in to your RudderStack dashboard.
2. On the Connections page, click Add source.
3. Under Sources, click Reverse ETL and select BigQuery.

Configure warehouse credentials

You can choose to proceed with your existing warehouse credentials if you have configured them in the RudderStack dashboard previously. Otherwise, click Add new credentials to add new credentials for your warehouse.

Click Verify. RudderStack then verifies and validates your credentials. Once verified, click Continue to proceed.

Specify name and source type

Specify the source name and type in this step.

• Source name: Assign a name to uniquely identify the source in the RudderStack dashboard.
• Select your source type: RudderStack lets you set up a Reverse ETL source from a warehouse Table, Model, or Audience.

Use warehouse table as source

Under Select your source type, choose Table and specify the below fields:

• Schema: Select the warehouse schema from the dropdown.
• Table: Choose the required table from which RudderStack syncs the data.
• Primary key: Select the column from the above table that uniquely identifies your records in the warehouse.

RudderStack uses the primary key column for diffing in case of incremental syncs. You can generate it by:

• Generating your table with a primary key, OR
• Creating a table view

You can use a composite key in cases where one column cannot be considered as a primary key. For example, you can a declare a composite key of user_id and timestamp by creating a view on your warehouse table.

Finally, review and complete your source setup.

Use model as source

Under Select your source type, choose Model and click Continue.

To configure a model as source:

1. Enter an optional description and specify the custom SQL query in Query section.
2. Click Run Query to fetch the data preview.
3. Select the Primary key to use a column that uniquely identifies your warehouse records.

You can set a primary key only after you run the SQL query successfully using the Run Query option.

RudderStack uses the primary key column for diffing in case of incremental syncs. You can generate it by:

• Generating your table with a primary key, OR
• Creating a table view

You can use a composite key in cases where one column cannot be considered as a primary key. For example, you can a declare a composite key of user_id and timestamp in SQL query of the model.

Finally, review and complete your source setup.

Use audience as source

This Audiences feature leverages the Reverse ETL workflow.

To build targeted, no-code audience segments from warehouse data and activate them downstream, use Rudder Lookout instead.

Under Select your source type, choose Audience and follow these steps:

1. Configure your audience source by specifying the below fields:

   • Schema: Select the warehouse schema from the dropdown.
   • Table: Choose the required table from which RudderStack syncs the data.
   • Primary key: Select the column from the above table that uniquely identify your records in the warehouse.

RudderStack uses the primary key column for diffing in case of incremental syncs. You can generate it by:

• Generating your table with a primary key, OR
• Creating a table view

You can use a composite key in cases where one column cannot be considered as a primary key. For example, you can a declare a composite key of user_id and timestamp by creating a view on your warehouse table.

2. Set your audience conditions.
3. Click Preview to see the resulting data. Then, click Continue to proceed.

Finally, review and complete your source setup.

Review and complete setup

To make any changes to the warehouse credentials or source configuration, click the edit icon present next to those sections.

Review your configuration and click Create source to complete the setup.

Connect destination

To start using the newly-created source, you can connect it to:

• A new destination, or
• An existing destination that is not already connected to any other source.

To connect to a destination later, click Done on the top right:

You will then be redirected to the Overview page of the source where you will see the option of connecting it to a new or existing destination.

See Set up Reverse ETL Connection section for more information.

Update source configuration and settings

Go to the Configuration tab of your Reverse ETL source to update the configuration depending on your source type:

You cannot change the source type on this page.

The below table lists the options you can update:

After updating the configuration, the next sync will be a full sync.

Go to the Settings tab to:

• Get your source ID.
• Change your warehouse credentials.
• Set up custom alerts for your Reverse ETL source.
• Delete the source permanently.

You cannot delete a source that is connected to any destination.

FAQ

What do the three validations under Verifying Credentials imply?

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

These options are explained below:

• Verifying Connection: This option indicates that RudderStack is trying to connect to the warehouse with the provided warehouse credentials.

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

• Able to List Schema: This option checks if RudderStack is able to fetch all schema details by using the provided credentials.
• Able to Access RudderStack Schema: This option implies that RudderStack is able to access the _rudderstack schema you have created by running all commands in the User 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.

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

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