Connecting Google BigQuery to Omni with workload identity federation

​

How it works

1. In your Google Cloud project, you create a workload identity pool with an AWS provider that trusts the identity Omni presents.
2. When Omni queries BigQuery or refreshes the connection’s schema, it exchanges that role’s credentials for a short-lived Google Cloud access token.
3. The token acts either as the federated identity itself, or as a service account you allow Omni to impersonate.

​

Requirements

• Organization Admin permissions in Omni
• Google Cloud admin permissions, which are required to create workload identity pools and grant IAM roles
• The gcloud CLI
• The following details about the default dataset in your BigQuery project:
  • The name of the default dataset
  • Its data location
  • Its timezone
    
    
  Need help locating the dataset?

  1. Navigate to your BigQuery console.
  2. Select the project your dataset resides in. Use the top middle drop-down menu to toggle between projects.
  3. Select a dataset and the info will appear in the Dataset info menu. In this example:
     • The dataset name is products
     • The Data location is US
     • The timezone is UTC-7, as indicated in the Last modified field

  

​

Setup

1

Retrieve Omni's AWS identity information

1. In Omni, click Settings > Connections.
2. Click the BigQuery option.
3. In the Authentication Type menu, select Workload Identity Federation.
4. Copy the Omni Workload Identity value. It will contain a value like the following:
   arn:aws:iam::123456789012:role/Prod-ProdModelTaskRole
   

This identifies Omni to Google Cloud. Note that:

• It contains Omni’s AWS account ID. This is the 123456789012 segment. You’ll need this when creating the provider in the next step.
• It contains a prefix, not the full role name. The actual role has a random suffix appended by Omni’s infrastructure, so your attribute condition must use a starts-with comparison. An exact-match condition will never match.

2

Create a workload identity pool and AWS provider

• Console
• gcloud CLI

1. In the Google Cloud Console, navigate to IAM & Admin > Workload Identity Federation.
2. Click Create pool.
3. Enter a name, such as omni, and continue.
4. Add a provider to the pool:
   • Provider type - AWS
   • Provider name - Enter a name, such as Omni
   • AWS account ID - The account ID from the value you copied in
5. Configure the provider attribute mappings. AWS presents Omni’s identity in a different format than the value shown in Omni, so the mappings reconstruct the role ARN before comparing it.
   1. Click Edit mapping.
   2. For google.subject, paste the following into the corresponding AWS field:
      assertion.arn.extract('assumed-role/{role}/')
      
   3. For attribute.aws_role_arn, paste the following into the corresponding AWS field:
      'arn:aws:iam::' + assertion.account + ':role/' + assertion.arn.extract('assumed-role/{role}/')
      
   The mappings should look like the following when finished:
   
6. Configure the provider attribute conditions:
   1. Click Add condition.
   2. In the text field, add the attribute condition. Replace with the value you copied from Omni in :
      attribute.aws_role_arn.startsWith('OMNI_WORKLOAD_IDENTITY')
      
      For example:
      attribute.aws_role_arn.startsWith('arn:aws:iam::123456789012:role/Prod-ProdModelTaskRole')
      
      The attribute conditions should look like the following when finished:

7. Click Save.

Using the gcloud CLI, run the following to create a workload identity pool:

gcloud iam workload-identity-pools create omni \
  --project=GCP_PROJECT_ID \
  --location=global \
  --display-name="Omni"

Replace GCP_PROJECT_ID with the ID of your GCP project.Then, create the provider:

gcloud iam workload-identity-pools providers create-aws omni-aws \
  --project=GCP_PROJECT_ID \
  --location=global \
  --workload-identity-pool=omni \
  --account-id=OMNI_AWS_ACCOUNT_ID \
  --attribute-mapping="google.subject=assertion.arn.extract('assumed-role/{role}/'),attribute.aws_role_arn='arn:aws:iam::'+assertion.account+':role/'+assertion.arn.extract('assumed-role/{role}/')" \
  --attribute-condition="attribute.aws_role_arn.startsWith('OMNI_WORKLOAD_IDENTITY')"

Replace the following values:

Placeholder	Replace with
GCP_PROJECT_ID	The ID of your GCP project
OMNI_AWS_ACCOUNT_ID	The account ID from the value you copied in
The entire value you copied in

3

Grant BigQuery access

Choose how Omni’s federated identity accesses BigQuery:

Option 1 - Grant direct access

Grant the pool the BigQuery roles directly on your project.

Grant the BigQuery roles directly to the pool’s identities, replacing GCP_PROJECT_ID with the ID of your GCP project:

gcloud projects add-iam-policy-binding GCP_PROJECT_ID \
  --role=roles/bigquery.jobUser \
  --member="principalSet://iam.googleapis.com/projects/GCP_PROJECT_ID/locations/global/workloadIdentityPools/omni/*"

gcloud projects add-iam-policy-binding GCP_PROJECT_ID \
  --role=roles/bigquery.dataViewer \
  --member="principalSet://iam.googleapis.com/projects/GCP_PROJECT_ID/locations/global/workloadIdentityPools/omni/*"

Optional. Grant the metadataViewer role directly to the pool’s identities:

gcloud projects add-iam-policy-binding GCP_PROJECT_ID \
  --role=roles/bigquery.metadataViewer \
  --member="principalSet://iam.googleapis.com/projects/GCP_PROJECT_ID/locations/global/workloadIdentityPools/omni/*"

The metadataViewer role is only required if you want to give Omni access to all views within a dataset (e.g., project.*) when setting up the connection in Omni. If you explicitly list specific datasets without wildcards in the connection setup step, Omni will skip the project-level metadata scan and work with dataset-level permissions only.When you configure Omni, leave the Service account impersonation URL field blank.

Option 2 - Impersonation through a service account

Have the pool impersonate a named service account that holds the required BigQuery roles.

1. Create a service account (for example, omni-bigquery) and grant it the following BigQuery roles on your project:
   • BigQuery Job User
   • BigQuery Data Viewer
2. Allow the pool’s identities to impersonate the service account by granting the Workload Identity User role on the service account to the pool:
   gcloud iam service-accounts add-iam-policy-binding omni-bigquery@GCP_PROJECT_ID.iam.gserviceaccount.com \
     --project=GCP_PROJECT_ID \
     --role=roles/iam.workloadIdentityUser \
     --member="principalSet://iam.googleapis.com/projects/GCP_PROJECT_ID/locations/global/workloadIdentityPools/omni/*"
   
   Replace GCP_PROJECT_ID with the ID of your GCP project.

Keep the service account email handy - you’ll need it to complete the setup in Omni.

4

Set up a table upload dataset

This step is optional. However, we recommend completing it as part of the initial set up or you won’t be able to use uploaded files like CSVs in joins.

In this step, you’ll create a dedicated dataset to use for table uploads. Before you create the dataset, note that:

• This dataset can’t be used for other modeled tables
• This dataset must be in the same region as other projects and schemas in the connection, or you may encounter cross-region errors

To create a dataset for table uploads:

1. In the Google Cloud Console, navigate to your BigQuery project.
2. Create the dataset.
3. After the dataset is created, open it so that its details display.
4. Click Share > Manage permissions, located next to the Copy option near the top right corner of the page.
5. In the dialog that displays, click Add principal.
6. For the New principals field, the value you enter depends on the access method you selected in step 3:
   • Direct access - Enter the pool identity, including the principalSet://. This is the member value from the command in step 3 and will look like the following:
     principalSet://iam.googleapis.com/projects/GCP_PROJECT_ID/locations/global/workloadIdentityPools/omni/*
     
   • Service account - Enter the email address of the service account you created in step 3. This will look like the following:
     omni-bigquery@GCP_PROJECT_ID.iam.gserviceaccount.com
     
7. Add the BigQuery Data Editor role.
8. Click Save.

5

Create the connection in Omni

1. In Omni, click Settings > Connections.
2. Click the BigQuery option.
3. On the connection setup page, fill in the connection details:
   When you explicitly list datasets without wildcards (no * characters), Omni skips the project-level metadata scan and only requires dataset-level permissions (BigQuery Data Viewer role). Using wildcards or leaving this field empty requires project-level permissions (BigQuery Metadata Viewer role).
   ​

   Authentication Type

   required

   The type of authentication to use. Select Workload Identity Federation.

   Hide Workload Identity Federation settings

   ​

   Workload Identity Pool Provider

   required

   The full resource name of the provider you created in step 2, prefixed with //iam.googleapis.com/. For example:

   //iam.googleapis.com/projects/YOUR_PROJECT_NUMBER/locations/global/workloadIdentityPools/omni/providers/omni-aws
   

   The Google Cloud console doesn’t display this value for AWS providers — build it from the pool and provider names, or run gcloud iam workload-identity-pools providers describe and prefix the returned name with //iam.googleapis.com/.

   ​

   Service Account to Impersonate

   Required for service account impersonation. The service account email from step 3. Otherwise, leave blank for direct access.

   ​

   Billing Project ID

   required

   The Google Cloud project queries run in and are billed to. The federated identity or impersonated service account must be able to run BigQuery jobs in it.
   ​

   Region

   required

   The region the dataset resides in. Refer to the Requirements section if you need help finding this information.
   ​

   Max Billing Bytes

   default:"1TB"

   The maximum bytes billed for queries run on this connection.
4. When finished, click Create connection.

​

Troubleshooting

​

What’s next?