Connect to Bigquery

This guide contains the necessary steps to connect a BigQuery environment to your Elementary account.

Choose an authentication method

Elementary supports two authentication methods for BigQuery. Pick the one that fits your security model:

• Service account — create a service account, download its JSON key, and upload the key to Elementary. Simplest to set up.
• Workload Identity Federation (WIF) — Elementary authenticates from its AWS role through a federated identity. No long-lived credentials are stored in Elementary.

Select a tab below and follow the steps for your chosen method.

Service account

Create service account:

1. In the Cloud Console, go to: IAM & Admin > Service Accounts

2. Click on ‘CREATE SERVICE ACCOUNT’

3. Fill in the service account name (‘elementary’) and account description (‘Elementary Data’) and click ‘CREATE AND CONTINUE’:

4. Now we need to configure the relevant permissions for this new service account. Select the following role: BigQuery Job User (you will need to grant read access to specific datasets later).

5. The last step is optional, skip it and press done.

6. Press on the dots icon to the right of your screen for your new service account and select ‘Manage keys’:

7. Press on ‘ADD KEY’ and select ‘Create new key’:

8. Use the ‘JSON’ option radio button and press ‘CREATE’:

9. This will automatically generate and download a JSON file with your private key information for this service account. This JSON file provides the credentials to programmatically connect and work with your BigQuery environment.

Grant service user access to specific datasets

In order for a service user to work with Elementary cloud, it requires the following permissions:

• Role “BigQuery Data Viewer” on your Elementary dataset.
• Roles “BigQuery Metadata Viewer”, “BigQuery Resource Viewer” on the entire project and any external sources it is referencing.

To grant a role on a specific dataset, follow these steps:

1. Go to your project in BigQuery console

2. In the “Explorer” tab, find your desired dataset.

3. Click on the three dots icon next to the dataset name, then Share.

4. Click the “ADD PRINCIPAL” button on the top right corner.

5. Fill out the form:
   • In the “New principals” textbox, write the email address of your user.
   • In the “Select a role” dropdown menu, choose the desired role (BigQuery Data Viewer for your Elementary dataset, BigQuery Metadata Viewer, BigQuery Resource Viewer for your dbt dataset).
   • Click “Save”.

Make sure to grant the correct access to your Elementary dataset and your dbt dataset.

Workload Identity Federation

Create a Workload Identity pool and provider:

1. In the Cloud Console, go to: IAM & Admin > Workload Identity Federation. Click ‘CREATE POOL’ (or select an existing pool), give it a name (e.g. elementary-identity-pool), and continue.

2. On the pool, click ‘ADD A PROVIDER’ and choose ‘AWS’. Set the provider name to Elementary AWS and the AWS account ID to 743289191656 (Elementary’s AWS account), then continue.

3. In the provider’s attribute conditions, add the following condition. This is the Elementary role for GCP — only Elementary can authenticate through this provider. Then save the provider:

   attribute.aws_role == "arn:aws:sts::743289191656:assumed-role/elementary-gcp-wif-role-prod-elementary-cloud"
   

Grant BigQuery permissions to the identity pool principal:

4. Go to IAM & Admin > IAM and click ‘GRANT ACCESS’. In ‘New principals’, paste the following principalSet. Replace PROJECT_NUMBER with your GCP project number and POOL_NAME with the pool you created above:

   principalSet://iam.googleapis.com/projects/PROJECT_NUMBER/locations/global/workloadIdentityPools/POOL_NAME/attribute.aws_role/arn:aws:sts::743289191656:assumed-role/elementary-gcp-wif-role-prod-elementary-cloud
   

5. Assign the following project-level roles to the principalSet and save. You will grant BigQuery Data Viewer at the dataset level in the next step:

   • BigQuery Job User
   • BigQuery Metadata Viewer
   • BigQuery Resource Viewer

Grant principal access to specific datasets:

6. In the BigQuery console, find your Elementary dataset in the ‘Explorer’ tab. Click the three dots next to it > Share > ADD PRINCIPAL. Paste the same principalSet from step 4, select BigQuery Data Viewer, and save. If your dbt dataset is in a different project, also grant it BigQuery Metadata Viewer and BigQuery Resource Viewer.

Download the credential configuration file:

7. Back in IAM & Admin > Workload Identity Federation, select your pool and click ‘GRANT ACCESS’ at the top.

8. Choose ‘Grant access using federated identities (Recommended)’ and click ‘DOWNLOAD CONFIG’.

9. In the ‘Configure your application’ panel, select the provider you created (e.g. Elementary AWS) from the Provider dropdown, then click ‘DOWNLOAD CONFIG’ to download the JSON configuration file.

10. Upload this JSON file to Elementary Cloud in the connection form below.

Permissions and security

Elementary cloud doesn’t require read permissions to your tables and schemas, but only the following:

• Read-only access to the elementary schema.
• Access to read metadata in information schema and query history, related to the tables in your dbt project.

It is recommended to create a user using the instructions specified above to avoid granting excess privileges. For more details, refer to security and privacy.

Fill the connection form

Use the Authentication method toggle at the top of the form to select either Service account or Workload Identity Federation, matching the method you set up above. The credentials upload field changes based on your selection:

• Service account file (Service account method): the service account JSON key file you downloaded.
• WIF credential file (Workload Identity Federation method): the external account JSON configuration file you downloaded from the identity pool.

Then provide the remaining fields:

• Project: The name of your BigQuery project.
• Elementary dataset: The name of your Elementary dataset. Usually [dataset name]_elementary.
• Location: Use this field to configure the location of BigQuery datasets as per the BigQuery documentation.

Add the Elementary IP to allowlist

Elementary IP for allowlist: 3.126.156.226

Need help with onboarding?

We can provide support on Slack or hop on an onboarding call.