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: the number of rows, size and time of the last change of every table in the monitored datasets.
  • 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 fetch_policy_tags (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: synq-monitoring
    • 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.

​
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 opt in with 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 — members will need to re-consent weekly. 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 (BigQuery access — required to run queries, which BigQuery executes as jobs) and https://www.googleapis.com/auth/userinfo.email (to label the connected account in the UI). Member queries are also bounded by each member’s IAM grants — the OAuth scope is the outer limit, not the policy, so a member with read-only IAM permissions still 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.

​
Service account key

The content of the JSON file you created earlier in the guide

​
Region

Location of your BigQuery instance (typically US or EU)

​
Blacklisted datasets (optional)

Comma-separated list of dataset name patterns to exclude from ingestion. Use * to match any string, e.g. dbt_pr_*.

​
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.

​
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. Enables ingestion of query history from INFORMATION_SCHEMA.JOBS, which powers anomaly-monitor training, data-usage insights, unused-table detection, and cost signals. Expect a small additional query cost (typically a few dollars per month).