Google BigQuery Reverse ETL Source

Send data from Google BigQuery to your entire stack.

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.

Follow the steps below in the exact order to grant these permissions:

Step 1: Create role and grant permissions

  1. Go to the Roles section of Google Cloud Platform dashboard and click CREATE ROLE.
Google Cloud Platform dashboard create role
  1. Fill in the details as shown:
GCP role details
  1. 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
bigquery.tables.delete

Read-write:

bigquery.jobs.create
bigquery.tables.create
bigquery.tables.update
bigquery.tables.updateData
bigquery.tables.delete
  1. Click CREATE after adding the permissions.
BigQuery role permissions

Step 2: Create service account and attach role

  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.
Create service account in GCP
  1. Fill in the Service Account details as shown below, and click CREATE AND CONTINUE:
Service account role details
  1. Under Grant this service account access to project, select the role you created in Step 1: Creating a role and granting permissions section above.
Service account role connection
  1. Click DONE to move to the list of service accounts.
info

Note down the service account ID. You will need this ID while creating the RudderStack schema and granting the required permissions to it.

Service account ID

Step 3: Create and download JSON key

  1. Click the three dots icon under Actions in the service account that you just created and select Manage keys:
Managing keys in GCP
  1. Click ADD KEY, followed by Create new key:
GCP Adding a new key
  1. Select JSON and click CREATE.
Select Reverse ETL source in RudderStack

A JSON file will be downloaded on your system. This file is required while setting up the BigQuery source in RudderStack.

Step 4: Create RudderStack schema and granting permissions

  1. From your BigQuery SQL workspace, run the following command to create a dedicated schema rudderstack_.
danger
The rudderstack_ schema is used by RudderStack for storing the state of each data sync. Do not change this name.
create schema rudderstack_;
warning

The rudderstack_ schema is created in the default region of your BigQuery instance.

If the GCP cloud storage bucket used as a staging bucket by RudderStack is in a different region, you will need to adjust the above SQL statement to match that region. For example, to create the schema in the europe-west-3 region, run the following statement:

create schema rudderstack_ OPTIONS (location = "europe-west3");
  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.
info
The <SERVICE_ACCOUNT_ID> takes the form of name@your-gcp-project.iam.gserviceaccount.com. You can also find it in the client_email key of the service account credentials JSON file downloaded in Step 3: Creating and downloading the JSON key.
GRANT `roles/bigquery.dataOwner`
     ON SCHEMA rudderstack_
     TO "serviceAccount:<SERVICE_ACCOUNT_ID>";

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.

  • Credentials: Add the contents of the GCP service account credentials JSON downloaded above.
  • Project ID: Specify your GCP project ID where your BigQuery database is located.
  • Service account: Specify your GCP service account in this field.

Click the Verify button on the top right. RudderStack will then verify and validate 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.
Source typeDescription
TableUse an existing warehouse table as a data source.

See Use warehouse table as source for detailed setup.
ModelUse custom SQL queries to fetch specific warehouse data and send them to your destinations.

See Use model as source for detailed setup.
AudienceFilter data in your warehouse tables to create target customer lists and send them to downstream destinations.

See Use audience as source for detailed setup.

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

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.

Use table as source

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.
warning
You can set a primary key only after you run the SQL query successfully using the Run Query option.
info

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.

Model configuration

Finally, review and complete your source setup.

Use audience as source

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.
Use audience as source
info

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.

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

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.

Edit source configuration

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

Connect destination

You can connect this source to an existing or new destination to start using it. To connect to a destination later, click Done on the top right.

Next steps

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

Add 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:

warning
You cannot change the source type on this page.
Update source configuration

The below table lists the options you can update:

Source typeConfigurable options
TableSchema, Table, Primary key
ModelNote: You can set the primary key only after the SQL query runs successfully.
Audience
info
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.
warning
You cannot delete a source that is connected to any destination.
Edit source settings

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:

Validating 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.
warning
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.
warning
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:

Source typeDescription
TableRudderStack uses an existing warehouse table as a data source.

See Use warehouse table as source for detailed setup.
ModelRudderStack uses custom SQL queries to fetch specific warehouse data and sends them to your destinations.

See Use model as source for detailed setup.
AudienceRudderStack filters data in your warehouse tables to create target customer lists and sends them to downstream destinations.

See Use audience as source for detailed setup.

Questions? Contact us by email or on Slack