Skip to main content
This guide explains how to connect Coalesce Quality to BigQuery securely.We need this information so we can collect relevant data about your tables.To be able to finish this guide, you’ll need the following:
→ Access to modify your BigQuery configuration⏱️ Estimated time to finish: 10 minutes.

Data we collect

We read metadata from your project using the service account described below:
  • Catalog: datasets, tables, views, materialized views, table snapshots and clones (with the table they copy), external tables, columns, descriptions, dataset and table labels, and column policy tags.
  • SQL definitions: the DDL of tables, views and materialized views, from INFORMATION_SCHEMA.TABLES. Used for lineage.
  • Routines: user-defined functions, table functions and stored procedures with their arguments and definition, from INFORMATION_SCHEMA.ROUTINES, PARAMETERS and ROUTINE_OPTIONS.
  • Constraints: primary and foreign keys, partitioning and clustering columns.
  • Table metrics (on by default): the number of rows, size and time of the last change of every table in the metrics scope.
  • Query logs (opt-in): INFORMATION_SCHEMA.JOBS, for usage, cost and lineage.
  • Platform users (opt-in): the project’s service accounts and the members bound to it in IAM, to tell people from service accounts in query history.
To provide out-of-the-box monitors for volume and freshness, we don’t require access to your actual data. For custom monitors, however, access to query your raw data is required. Permission overview:

Setup BigQuery access

Create a dedicated Coalesce Quality role

  1. Select the project with your BigQuery instance in the project picker.
  2. Go to IAM and Admin → Roles → Create Role.
  3. Fill in:
    • Title: Coalesce Quality Monitoring
    • Description: Coalesce Quality Monitoring role
    • Role launch stage: General Availability
  4. Click Add permissions and add:
    If you turn on Read policy tag names (see Column policy tags), also add datacatalog.taxonomies.get.
    Create role
  5. Click Create.

Create a service account

  1. Go to IAM and Admin → Service Accounts → Create Service Account.
  2. Fill in:
    • Service account name: coalesce-quality
    • Service account description: Coalesce Quality Monitoring Service Account
    Create service account
  3. Click Create and continue.
  4. In section Grant this service account access to the project, select the previously created Coalesce Quality Monitoring role, then click Done.

Keyless authentication

Coalesce Quality can connect without a service account key. No key leaves your project, the tokens it uses expire after one hour, and every token shows in your project’s audit logs. It also works where your organization blocks key creation (the iam.disableServiceAccountKeyCreation policy). Coalesce Quality creates one service account for your workspace, the workspace service account. Only your workspace connects through it. The integration form shows its email, ready to copy. You can give it access in two ways:
  • Impersonate your service account (recommended). The permissions stay on the service account you created above, in your project. Grant the workspace service account the Service Account Token Creator role on it:
    To revoke the access, remove this one binding.
  • Grant the BigQuery roles directly. Skip creating a service account, and grant the Coalesce Quality Monitoring role to the workspace service account instead:
The workspace service account belongs to the region that serves your workspace (EU, US or AU). A service account of another region can not use your grant. If your workspace moves to another region, grant the new region’s workspace service account.
Keyless authentication is in alpha. If you do not see the Authentication choice, ask support to enable it for your workspace, or use a service account key. Use Test connection in the form after you grant the access. If the token creator grant is missing, the test says which service account needs it. If you choose keyless authentication, skip the next step.

Create a service account key

  1. Open your newly created service account and switch to the Keys tab.
  2. Click Add key → Create new key, choose JSON, and click Create.
    Create JSON key
  3. Store the downloaded JSON key file securely.

Platform users

With Fetch users and service accounts turned on (it shows once query logs are on), resourcemanager.projects.getIamPolicy and iam.serviceAccounts.list let Coalesce Quality list who can query the project once a day, so it can tell people from service accounts among the logins in your query history. They read IAM metadata only, never data. Both calls go through Google APIs that have to be enabled in the project the service account belongs to: Cloud Resource Manager API and Identity and Access Management (IAM) API (APIs & Services → Library). Without them everything else works, and the integration shows a recommendation naming what is missing.

Column policy tags

Coalesce Quality reads the policy tags on your columns along with the rest of the table’s metadata. By default a policy tag is named by its taxonomy’s resource name (projects/<project>/locations/<location>/taxonomies/<id>), with the policy tag’s resource name as the value. Policy tags on fields nested inside a RECORD column are not read. Dataset and table labels are read as labels with their key and value, and table labels also become annotations named bigquery.label.<key>. Policy tags are shown by name only when you turn on Read policy tag names in the integration, or set fetch_policy_tags: true on a BigQuery connection of the on-premise agent. Without it, Coalesce Quality never calls the Data Catalog API, and each policy tag keeps its resource name.
With the opt-in, Coalesce Quality reads their taxonomies through the Data Catalog API, and a policy tag is named by its taxonomy, with the path to the tag inside the taxonomy as the value, for example Sensitivity = High:Email. This needs datacatalog.taxonomies.get added to the role above and the Data Catalog API enabled in the project the service account belongs to (APIs & Services → Library). A taxonomy in another project needs the permission there too. A taxonomy that cannot be read keeps its policy tags’ resource names, and everything else works.

Personal access (user OAuth)

In addition to the workspace service account above, you can let each Coalesce Quality member authenticate as themselves against BigQuery. Queries then run under the member’s own Google identity and respect the IAM grants on that user. See Data Warehouse Access for the full model and when to enable it. To enable personal access, a Google Cloud admin creates a single OAuth 2.0 Client ID once, then pastes the client ID and secret into the BigQuery integration’s edit page in Coalesce Quality.

Configure branding

Skip this step if your project’s Google Auth Platform is already configured.
  1. In the Google Cloud console for the project that will host the OAuth app, go to Google Auth Platform → Branding.
  2. Fill in the app name (e.g. Coalesce Quality), user support email, and developer contact.
    Branding configuration
  3. Save.

Choose the audience

  1. Go to Google Auth Platform → Audience.
  2. User type: pick Internal if every Coalesce Quality member has a Google account in your Workspace organisation, otherwise External.
If you use External and stay in Testing mode, Google caps refresh tokens at 7 days, so members need to consent again every week. Promote the publishing status to Production (or use Internal) for long-lived access.

Create the OAuth client

  1. Go to Google Auth Platform → Clients → Create client.
  2. Application type: Web application.
  3. Name: e.g. Coalesce Quality.
  4. Under Authorised redirect URIs, add the URI(s) for the Coalesce Quality region(s) your members use, with no trailing slash:
    • EU: https://app.synq.io/auth/dwh-oauth/callback
    • US: https://app.us.synq.io/auth/dwh-oauth/callback
    • AU: https://app.au.synq.io/auth/dwh-oauth/callback
    Create OAuth client
  5. Click Create. Copy the Client ID and Client secret.

Enable the BigQuery API

In the same project, go to APIs & Services → Library, search for BigQuery API, and click Enable (already enabled if you scrape BigQuery from this project).
BigQuery API library page

Paste credentials into Coalesce Quality

In Settings → Integrations → (your BigQuery integration) → Warehouse access → Personal credentials, paste the Client ID and Client secret. Members will then see this BigQuery integration listed under Settings → Warehouse access → My credentials and can connect their own Google account.
Coalesce Quality requests two OAuth scopes during member consent: https://www.googleapis.com/auth/bigquery, to run queries, which BigQuery executes as jobs, and https://www.googleapis.com/auth/userinfo.email, to label the connected account in the UI. Each member’s IAM grants still apply on top of the scope, so a member with read-only IAM permissions can only read.

Network Configuration

If your BigQuery project has firewall rules or VPC configurations that restrict access, you may need to whitelist our IP addresses. See Coalesce Quality IP Whitelist for the complete list of IP addresses by region.
Once you’ve successfully completed these steps, input the following data in the platform.

Input data in the Coalesce Quality UI

Integration name

For example BigQuery

Project ID

Open the project picker at the top of the Google Cloud console and copy the ID column value for your project. One integration connects one project. To monitor more projects, create one integration for each project. All of them can use the same service account or the same keyless setup, if it has the roles in each project. You cannot change the Project ID after you create the integration. To connect a different project, create a new integration.

Authentication

How Coalesce Quality connects: impersonate your service account, grant the roles to the workspace service account, or a service account key. See Keyless authentication. When you switch an integration from a key to keyless authentication, Coalesce Quality deletes the stored key, and you can delete the key in Google Cloud.

Your service account

With impersonation only: the email of the service account you created earlier in the guide, for example coalesce-quality@<your-project-id>.iam.gserviceaccount.com.

Service account key

With a service account key only: the content of the JSON file you created earlier in the guide

Region

Location of your BigQuery instance (typically US or EU)

Datasets (optional)

Comma-separated list of dataset names to scrape explicitly (e.g. analytics, mart_core). When set, only these datasets are queried and the service account does not need bigquery.datasets.get on the whole project to list them, which lets you grant the Coalesce Quality Monitoring role on individual datasets instead of the whole project. Leave empty to auto-discover every visible dataset in the project.

Catalog scope and Metrics scope (optional)

Include and exclude rules at the dataset and table level. Catalog scope limits which datasets and tables are ingested into the catalog. Metrics scope limits which tables are scanned for metrics. Empty means everything.

Read policy tag names (optional)

Shows each column policy tag by its taxonomy and name. See Column policy tags.

Audit table FQN (optional)

Fully-qualified table name (e.g. my-project.analytics.quality_sql_test__audit) where Coalesce Quality writes SQL test execution results. The integration creates the table automatically if it does not yet exist. Leave empty if you do not run SQL tests. Using this field requires two extra permissions on the target dataset that are not in the default role above: bigquery.tables.create and bigquery.tables.updateData. Add them to the Coalesce Quality Monitoring role (or grant them just on the audit dataset) before enabling the feature. Reads query history from INFORMATION_SCHEMA.JOBS. Coalesce Quality uses it to train anomaly monitors, show how tables are used, find unused tables and report costs. Expect a small additional query cost, usually a few dollars per month. With query logs on, Fetch users and service accounts also shows. See Platform users.

Fetch table metrics (required for automatic monitors)

On by default. Every 30 minutes, reads the row count, size and last change time of each table. Automatic freshness and volume monitors stop working when it is off.