BigQuery Reverse ETL Setup

To set up your BigQuery source with Reverse ETL, you must construct a BigQuery role and service account and create a BigQuery source in the Segment app.

Constructing your own role or policy

There are two approaches you can take when granting Segment access to your BigQuery resources:

• Grant Full Access: This option allows Segment to automatically complete the setup for you after you provide Segment with all the necessary permissions. This option requires less time and engineering effort on your part.
• Grant Limited Access: This option is more secure, as it restricts the permissions Segment has access to. However, due to the limited access, you must complete a few additional setup steps. These are one-time steps, and the documentation provides you with the information required to complete this process.

You can choose the approach that best suits your needs.

Grant Full Access

With this approach, use BigQuery predefined roles to create a service account for Segment to assume.

1. In BigQuery, navigate to IAM & Admin > Service Accounts.
2. Click + Create Service Account to create a new service account.

3. Enter your Service account name and a description of what the service account will do.

4. Click Create and Continue.
5. Click + Add another role and add the BigQuery User role.
6. Click + Add another role and add the BigQuery Data Editor role.
7. Click Continue, then click Done.

Grant Limited Access

With this approach, you can set up a custom role with the following permissions:

1. In BigQuery, navigate to IAM & Admin > Roles.
2. Click + CREATE ROLE to create a new role.
3. Add Title and Description as you like.
4. Click ADD PERMISSIONS and add the permission listed in the above tables. Repeat this step until you’ve added all required permissions.
5. Click CREATE.
6. Navigate to IAM & Admin > Service Accounts.
7. Click + Create Service Account to create a new service account.
8. Enter your Service account name and a description of what the account will do.
9. Click Create and Continue.
10. In the Grant this service account access to project section, select the role you just created.
11. Click Continue.
12. Click Done. Copy and keep the Service Account email handy for the next steps.
13. Navigate to the BigQuery SQL editor and create a dataset that will be used by Segment:

    CREATE SCHEMA IF NOT EXISTS `__segment_reverse_etl`;
    

14. Grant limited access to the Segment Reverse ETL dataset

    GRANT `roles/bigquery.dataEditor` ON SCHEMA `__segment_reverse_etl` TO "serviceAccount:<YOUR SERVICE ACCOUNT EMAIL>";
    

BigQuery resource location

When connecting your BigQuery warehouse to Segment, you’ll need to know the location of your resources.

You can find the location of your BigQuery resources using the following method:

1. In the BigQuery console, navigate to your dataset. In the explorer panel on the left, expand the project and dataset to view the tables.
2. Click on the name of the dataset, and it opens a page showing its details.
3. The Location of the dataset (like US or EU) is displayed in the Dataset Info.

Set up BigQuery as your Reverse ETL source

1. In the BigQuery console, search for the service account you created.
2. When your service account pulls up, click the 3 dots under Actions and select Manage keys.
3. Click Add Key > Create new key.
4. In the pop-up window, select JSON for the key type and click Create. The file will be downloaded.
5. Copy all the content in the JSON file you created in the previous step.
6. Open the Segment app and navigate to Connections > Sources.
7. On the My sources page, click + Add source.
8. Search for “BigQuery” and select the BigQuery source from the sources catalog. On the BigQuery overview page, click Add Source.
9. On the Set up BigQuery page, enter a name for your source and paste all the credentials you copied from previous step into the Enter your credentials section.
10. Enter the location of your BigQuery warehouse in the Data Location field.
11. Click Test Connection to test to see if the connection works. If the connection fails, make sure you have the right permissions and credentials and try again.
12. If the test connection completes successfully, click Add source to complete the setup process.

After you’ve added BigQuery as a source, you can add a model and follow the rest of the steps in the Reverse ETL setup guide.