Articles in this section

Configuring Your BigQuery Destination

The BigQuery integration uses role-based access. Use Iterable's service account name to grant access to this configuration.

# In this article

# Understanding role-based authentication in BigQuery

There are two service accounts involved in authenticating users in BigQuery destinations:

  • A destination service account: You create this service account in your GCP project, with BigQuery and Cloud Storage permissions–this identity is used to perform work inside your project.

  • Iterable’s service account: This account is provided to you, and doesn’t have direct permissions to BigQuery or Cloud Storage. Instead, this account has permission to “assume” the other service account role (using short-lived tokens via Service Account Token Creator/User), enabling least-privilege, auditable access without sharing keys.

# Step 1: Create a service account in the BigQuery project

  1. In the GCP console, navigate to the IAM & Admin menu, click the Service Accounts tab, and click Create Service Account at the top of the menu.

  2. Name the user and click Create and Continue.

  3. Next, set Role to BigQuery User. This allows you to create datasets, submit load/query jobs, and access required metadata during setup.

NOTE

Alternatively, if you're working with a pre-provisioned dataset, use the following values of least-privilege:

  • Project: Grant bigquery.jobs.create to the destination service account.
  • Dataset: Grant BigQuery Data Owner OR a custom role that includes at least: bigquery.tables.create, bigquery.tables.delete, bigquery.tables.get, bigquery.tables.getData, bigquery.tables.list, bigquery.tables.update, bigquery.tables.updateData, bigquery.routines.get, and bigquery.routines.list.

  1. Click Done to finish creating the account.

  2. In Service Accounts, click the newly created Destination service account to view its details, and note the email, which is different from Iterable's service account email.

  3. Navigate to the Principals with access tab, click Grant Access, and add:

    • Principal: The provided Iterable service account (see prerequisites)
    • Roles to grant: Service Account Token Creator, Service Account User

NOTE

Alternatively, if your policy requires key-based authentication (not recommended, unless required, due to the increased security risk), generate and use a JSON key to authenticate the destination service account for your project. To do this:

  1. From IAM + Admin > Service Accounts, open the destination service account.

  2. Select Actions > Manage keys and add/create a new key with a JSON type. Be sure to securely store this key for future reference.

# Step 2: Create a staging bucket

IMPORTANT

We strongly recommend that you create a staging bucket that's dedicated solely to data transfers. Doing so enables data isolation and simplifies permissions management.

  1. Log into Google Cloud Console, navigate to Cloud Storage, and click Create.

  2. Choose a name for the bucket and click Continue.

TIP

The location you choose for your staging bucket must match the location of your destination dataset in BigQuery. When creating your bucket, be sure to choose a region where BigQuery is supported (see BigQuery regions).

  • If the dataset does not exist yet, it's created for you in the same region where you created your bucket.
  • If the dataset already exists, the dataset region must match the location you choose for your bucket.

  1. Select a Location for the staging bucket. Note both the name and the location (region)–you'll need this information later.

  2. Click Continue and select the following options based on your preferences.

  3. To ensure that the bucket is not public, enable Uniform bucket-level access and keep all public access blocked.

  4. On the Bucket Details page, click the Permissions tab, and click Add.

  5. In the New Principles field, add the Service Account created earlier, select the Storage Admin role, and click Save.

NOTE

Alternatively, to reduce privileges while preserving required functionality, use a new bucket that's dedicated solely to data transfers–this will isolate your data and simplify permissions management.

If your policy requires tighter scope than Storage Admin, grant only the following minimum actions to the destination service account (using a custom role, or by providing Storage Legacy Bucket Reader and Storage Object User roles): storage.buckets.get, storage.objects.list, storage.objects.get, storage.objects.create, storage.objects.delete

  1. Optionally, you can add a short retention lifecycle policy on the staging bucket that automatically deletes objects older than two days, because the bucket isn't used to persist data. Transfer logic automatically cleans up files after completing a transfer, so there's no need to add a rule to delete the objects.

# Step 3: Find your project ID

  1. Log into the Google Cloud Console and select the projects list dropdown.

  2. Make note of the BigQuery project ID.

TIP

This connection supports Google Cloud organization policies that restrict identities by domain. If your organization enforces domain-restricted sharing, allowlist our principal following Google's guidance on restricting identities by domain. See the Google Cloud documentation: Restricting identities by domain. Contact Iterable to receive the customer ID to add to your allow list.

# Step 4: Create a new destination

Create a new destination for data writing.

  1. Log in to Iterable as a user with the Manage Integrations project permission and open the project you're working on.

  2. Go to Integrations > Data Sync.

  3. Select the name of the destination that you're connecting to, then click Continue.

  4. Provide the following details:

    • Destination name – A name for the destination.
    • Project ID – Your BigQuery project ID (from the Google Cloud Console projects list).
    • Dataset – The target dataset name.
    • Auth Method – Choose one of the following:
      • IAM Role – Recommended. Uses service account impersonation. Enter the provider service account email (Iterable's service account) and the destination service account email (the service account you created in your GCP project with BigQuery and Cloud Storage permissions).
      • User/Password – Key-based authentication. Use the JSON key for your destination service account.
    • Provider service account email – Iterable's service account (for IAM Role auth). This value is different for Iterable's US and EU environments.
    • Destination service account email – Your GCP service account that has BigQuery and Cloud Storage permissions (for IAM Role auth).
    • GCS bucket name – The staging bucket for data transfers. Create a dedicated bucket in the same region as your BigQuery dataset.
    • GCS bucket region – The region where your staging bucket is located. Must match your BigQuery dataset region.

  5. Click Create Destination.

# Permissions checklist

Verify that the following permissions are applied in your project:

  • Project: A destination service account exists in your project with BigQuery User role. If the dataset is pre-created, instead, the project can have bigquery.jobs.create and dataset-level Data Owner role (or a custom role with the minimum table/routine permissions listed above).

  • Destination service account: Iterable's service account has the Service Account Token Creator and Service Account User roles.

  • Staging bucket: Non-public in the same region as the BigQuery dataset and a destination service account with the Storage Admin role. If your policy requires tighter scope than Storage Admin, ensure that minimal object and bucket permissions are granted.

# FAQ

Q: Why is a GCS bucket required?

A: We use staging-assisted load to use BigQuery's native bulk-upload path, maximizing throughput to your destination.

Q: How long does data remain in the GCS bucket?

A: Data is not persisted in the staging bucket and is deleted after each transfer. Optionally, you can add a lifecycle rule to auto-delete objects after ~2 days.

Q: Is BigQuery supported across regions?

A: Yes. BigQuery is supported across all GCP-supported regions. Ensure your BigQuery dataset and staging bucket are located in the same region.

Q: I've updated permissions–why am I still seeing errors?

A: GCP IAM services can often take up to 10 minutes to propagate. Wait a few minutes and try again.

Q: Why are two service accounts involved?

A: You create one service account in your project with BigQuery/Storage permissions, and we use our service account to impersonate yours. This means we never handle your private keys–all operations appear in your audit logs, and you can revoke access anytime through your own IAM permissions.

Related articles