> ## Documentation Index
> Fetch the complete documentation index at: https://zenskar.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# BigQuery

## Concepts

### BigQuery

This document explains what a BigQuery data source is, how its data project and billing project separate where usage lives from who pays for queries, which dataset it reads, when queries need fully qualified table names, and how Zenskar reads BigQuery as a service account.

> [accordion] Map of concepts
>
> | Concept | Details |
> | - | - |
> | [Data project](#data-project-and-billing-project-where-usage-lives-and-who-pays) | Each BigQuery data source has one data project, which holds its dataset. |
> | [Billing project](#data-project-and-billing-project-where-usage-lives-and-who-pays) | Each BigQuery data source has one billing project, which can be the same project as its data project. |
> | | [Fully qualified table name](#fully-qualified-table-names): If the billing project differs from the data project, each table in a query needs a fully qualified table name. |
> | [Dataset](#datasets-where-zenskar-looks-for-tables) | Each BigQuery data source names one dataset of its data project. |
> | | [Location](#datasets-where-zenskar-looks-for-tables): Each dataset has one location. |
> | [Service account](#service-accounts-how-zenskar-reads-bigquery) | Each BigQuery data source has one service account, which needs a role on the dataset and a role on the billing project. |
> | | [Service account key](#service-accounts-how-zenskar-reads-bigquery): Each service account key lets Zenskar act as one service account. |
>
> A BigQuery data source names a data project, a billing project, a dataset of the data project, and the key of a service account. Zenskar acts as the service account to run each query in the billing project. The query reads tables of the dataset in the data project. When the two projects differ, each table in the query has a fully qualified table name. With on-demand pricing, the billing project pays for the queries.

> [accordion] Related modules
>
> The following modules use BigQuery:
>
> | Module | Details |
> | - | - |
> | [Data sources](https://zenskar.com/docs/20260301/zenskar-modules/usage/concepts/data-sources.md#data-source-connectors-how-zenskar-reads-a-data-source) | A data source can use the BigQuery data-source connector. |
> | [Billable metrics](https://zenskar.com/docs/20260301/zenskar-modules/usage/concepts/billable-metrics.md) | A billable metric can query a BigQuery data source through an SQL query. |

### BigQuery data source: usage in your BigQuery tables

A **BigQuery data source** is a [data source](https://zenskar.com/docs/20260301/zenskar-modules/usage/glossary.md#data-source) that identifies a BigQuery system in your data infrastructure, and that Zenskar uses with the BigQuery [data-source connector](https://zenskar.com/docs/20260301/zenskar-modules/usage/glossary.md#data-source-connector) to connect to it.

A BigQuery data source holds a [data project](#data-project-and-billing-project-where-usage-lives-and-who-pays), a [billing project](#data-project-and-billing-project-where-usage-lives-and-who-pays), a [dataset](https://docs.cloud.google.com/bigquery/docs/datasets-intro), and the key of a [service account](https://docs.cloud.google.com/iam/docs/service-account-overview).

A BigQuery data source is always a [remote data source](https://zenskar.com/docs/20260301/zenskar-modules/usage/glossary.md#remote-data-source). Zenskar queries the BigQuery tables in place. Each query reads the current data.

Zenskar acts as the service account in every query. A query can read only the tables that the service account can read.

A [billable metric](https://zenskar.com/docs/20260301/zenskar-modules/usage/glossary.md#billable-metric) reads a BigQuery data source through an SQL query.

Zenskar does not test the connection when a BigQuery data source is saved. A wrong value shows as an error only when a query runs.

The values of a BigQuery data source cannot change after it is saved. Different values need a new BigQuery data source.

> [card] [Add a BigQuery data source](#add-a-bigquery-data-source)
>
> This guide shows how to add a BigQuery data source with its projects, dataset, and service account key.

### Data project and billing project: where usage lives and who pays

The **data project** of a BigQuery data source is the Google Cloud [project](https://docs.cloud.google.com/bigquery/docs/resource-hierarchy#projects) that holds its dataset.

The **billing project** of a BigQuery data source is the Google Cloud project that runs its queries and pays for them.

A BigQuery data source names both projects. In most setups, the data project and the billing project are the same project.

With on-demand pricing, BigQuery bills each query to the project that runs it. The project that holds the data pays for its storage. With capacity-based pricing, BigQuery bills reserved slots to the administration project that bought them. See [Projects](https://docs.cloud.google.com/bigquery/docs/resource-hierarchy#projects).

A project in a table name does not change which project pays. Only the billing project decides it.

A separate billing project moves the cost of the queries away from the data project.

When the billing project differs from the data project, every table in a query needs a [fully qualified table name](https://docs.cloud.google.com/bigquery/docs/tables-intro). See [Fully qualified table names](#fully-qualified-table-names).

### Datasets: where Zenskar looks for tables

A dataset is a top-level container in BigQuery that organizes tables and views and controls access to them.

A BigQuery data source names one dataset of its data project. Zenskar looks for tables and views in that dataset.

A BigQuery data source names the dataset by its name alone, without the project. A dataset name can contain only letters, numbers, and underscores. See [Name datasets](https://docs.cloud.google.com/bigquery/docs/datasets#name-datasets).

Each dataset has a [location](https://docs.cloud.google.com/bigquery/docs/locations), which is a region or a multi-region. The location is set when the dataset is created, and it cannot change.

A BigQuery data source can also hold the location of its dataset. The location is optional.

### Fully qualified table names

A fully qualified table name is a table name that includes its project and its dataset. In GoogleSQL, it has the form `projectname.datasetname.tablename`.

A query of a BigQuery data source can always use fully qualified table names. When the billing project differs from the data project, every table name in the query must be fully qualified. The project in each name is the data project.

A project ID can contain dashes. GoogleSQL accepts dashes in the project ID of a table name in a `FROM` or `TABLE` clause. Backticks around the whole name make it valid in every clause. See [Table names](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/lexical#table_names).

```sql theme={null}
SELECT COUNT(*)
FROM `my-data-project.my_dataset.my_table`
```

### Service accounts: how Zenskar reads BigQuery

A service account is a special kind of Google Cloud account that an application uses. Zenskar reads BigQuery as the service account of the BigQuery data source.

A [service account key](https://docs.cloud.google.com/iam/docs/keys-create-delete) lets Zenskar act as the service account. The BigQuery data source holds the key in JSON format. Zenskar stores the key as a secret.

A service account key is a security risk if it is not managed carefully. See [Best practices for managing service account keys](https://docs.cloud.google.com/iam/docs/best-practices-for-managing-service-account-keys).

The service account needs two roles:

| Role | Granted on | What it allows |
| - | - | - |
| BigQuery Data Viewer (`roles/bigquery.dataViewer`) | The data project, or only the dataset | It lets the service account read the tables of the dataset. |
| BigQuery Job User (`roles/bigquery.jobUser`) | The billing project | It lets the service account run queries in the billing project. |

When the data project and the billing project are the same, the service account has both roles on that project. Neither role lets the service account change data. See [BigQuery IAM roles](https://docs.cloud.google.com/bigquery/docs/access-control).

## How-to guides

### Prepare Google Cloud

Prepare a service account in Google Cloud that Zenskar can act as for a BigQuery data source.

#### Prerequisites

* You need the IDs of the data project and the billing project. Use the project IDs, not the project names. A project ID is unique and permanent. A project name is a display name that can change. Use one project for both unless the query costs should go to another project. See [Data project and billing project](#data-project-and-billing-project-where-usage-lives-and-who-pays).
* You need Google Cloud permissions to create a service account and its key, and to grant roles on both projects. This task needs no Zenskar permission.
* Your organization must allow service account keys. The `iam.disableServiceAccountKeyCreation` policy blocks them, and Google enforces it by default for organizations created on or after May 3, 2024. An organization policy administrator can exempt a project. See [Create and delete service account keys](https://docs.cloud.google.com/iam/docs/keys-create-delete).

#### Enable the BigQuery API

1. In the Google Cloud console, select the billing project.
2. Enable the BigQuery API. See [Enable the BigQuery API](https://console.cloud.google.com/flows/enableapi?apiid=bigquery.googleapis.com).

#### Create a service account

1. Open the **Create service account** page, and select the billing project.
2. Enter a service account name.
3. Click **Done**.
4. Copy the email address of the service account. You need it to grant the roles.

See [Create service accounts](https://docs.cloud.google.com/iam/docs/service-accounts-create).

#### Grant the roles

Grant BigQuery Job User on the billing project:

1. Go to the **IAM** page, and select the billing project.
2. Click **Grant Access**.
3. Enter the email address of the service account.
4. In **Select a role**, select **BigQuery Job User**.
5. Click **Save**.

Grant BigQuery Data Viewer on the dataset:

1. Go to the **BigQuery** page.
2. In the **Explorer** pane, expand the data project, click **Datasets**, and select the dataset.
3. Click **Sharing** > **Permissions**.
4. Click **Add principal**.
5. In **New principals**, enter the email address of the service account.
6. In **Select a role**, select **BigQuery Data Viewer**.
7. Click **Save**.

> [!NOTE]
>
> You can grant BigQuery Data Viewer on the whole data project instead, on the **IAM** page of the data project.

See [Control access to resources with IAM](https://docs.cloud.google.com/bigquery/docs/control-access-to-resources-iam).

#### Create a JSON key

1. Go to the **Service accounts** page, and select the billing project.
2. Click the email address of the service account.
3. Click the **Keys** tab.
4. Click **Add key** > **Create new key**.
5. In **Key type**, select **JSON**, and click **Create**.

The key file downloads to your computer. You cannot download it again. Keep the file until you add the BigQuery data source. Then delete the file, or keep it in a secrets manager. Never commit the file to source control.

#### Test the service account

Run the following commands in Cloud Shell, or on a computer with the Google Cloud CLI and the key file. Replace the values in capitals with your own.

```bash theme={null}
gcloud auth activate-service-account --key-file=KEY_FILE
bq --project_id=BILLING_PROJECT_ID query --nouse_legacy_sql \
  'SELECT COUNT(*) FROM `DATA_PROJECT_ID.DATASET.TABLE`'
```

The query runs in the billing project and reads a table of the dataset. A count shows that the service account has both roles.

> [card] [Add a BigQuery data source](#add-a-bigquery-data-source)
>
> This guide shows how to add a BigQuery data source with the projects, the dataset, and the key.

### Add a BigQuery data source

Add a BigQuery data source so that billable metrics can query your BigQuery tables.

#### Prerequisites

* You need a role with the **Can Create Data Source** and **Can Read Data Source** permissions. See [Permissions](#permissions).
* You need the IDs of the data project and the billing project, the name of the dataset, and the JSON key file of the service account. See [Prepare Google Cloud](#prepare-google-cloud).

#### Steps

> [!CAUTION]
>
> You cannot delete a data source, and you cannot change its values after you save it. Check the values before you save.

1. Go to **Usage** > **Data Sources**.
2. Click **ADD DATA SOURCE**. The **Add New Data Source** page opens.
3. Under **Set Up Source**, in **Source Name**, enter a name. The name can contain letters, digits, underscores, hyphens, and spaces.
4. In **Source Type**, select **BigQuery**.
5. Under **Source Config**, fill in the fields:
   * In **Project ID**, enter the ID of the data project.
   * In **Billing Project ID**, enter the ID of the billing project. For one project, enter the same ID as in **Project ID**.
   * In **Dataset ID**, enter the name of the dataset, without the project.
   * Optional: in **Location**, enter the location of the dataset, such as `US` or `EU`.
   * In **Credentials JSON**, paste the whole contents of the key file.
6. Under **Source Access**, keep **Directly from Source**. It is the only option for BigQuery.
7. Click **SAVE SOURCE**.

The page confirms the save. Click **BACK** to return to **Data Sources**.

Zenskar does not test the connection when you save. A wrong value shows as an error when a billable metric first queries the data source.

> [card] [Write a billable-metric query for a BigQuery data source](#write-a-billable-metric-query-for-a-bigquery-data-source)
>
> This guide shows how to query the tables of a BigQuery data source in a billable metric.

#### Fix a failed save

* Zenskar does not save the data source when **Project ID**, **Billing Project ID**, or **Dataset ID** is empty. Fill in each marked field.
* Zenskar does not save the data source when **Credentials JSON** is empty or does not hold a JSON object. Paste the whole contents of the key file.

### Write a billable-metric query for a BigQuery data source

Write the SQL query of a billable metric so that it reads the tables of a BigQuery data source.

#### Prerequisites

* To create a billable metric, you need a role with the **Can Create Billable Metric** and **Can Read Billable Metric** permissions. To change one, you need **Can Update Billable Metric** and **Can Read Billable Metric**. See [Permissions](#permissions).
* The BigQuery data source must exist. See [Add a BigQuery data source](#add-a-bigquery-data-source).

#### Write the query

1. Open a new or existing billable metric in the SQL builder. See [Create a billable metric](https://zenskar.com/docs/20260301/zenskar-modules/usage/how-to-guides/billable-metrics.md#create-a-billable-metric).
2. Under **SOURCE OF DATA**, select **Remote Data**. In **Remote Datasource**, select the BigQuery data source.
3. In **Data Navigator**, hover over a table and click its copy icon. Paste the name into the query.
4. Check the pasted name. When the billing project differs from the data project, the name must be a fully qualified table name. If it is not, add the data project and the dataset in front of it.
5. Write the rest of the query. The query returns a column named `quantity`.
6. Run the query for a customer and a period. See [Preview a billable metric](https://zenskar.com/docs/20260301/zenskar-modules/usage/how-to-guides/billable-metrics.md#preview-a-billable-metric).

The following query counts the API calls of a customer in a period, from a table in the data project `my-data-project`:

```sql theme={null}
SELECT COUNT(*) AS quantity
FROM `my-data-project.my_dataset.api_calls`
WHERE customer_id = {{customer.external_id}}
  AND DATE(timestamp) >= DATE({{start_date}})
  AND DATE(timestamp) <= DATE({{end_date}})
```

#### Fix a failed preview

* When the billing project differs from the data project, a query with a table name that is not fully qualified fails. Write each table name in full.
* When the service account is missing a role, the query fails. See [Grant the roles](#grant-the-roles).
* When a value of the BigQuery data source is wrong, every query fails. Examples are a typo in a project ID, or a dataset name with the project in front. Add a new BigQuery data source with the correct values.

### Move to a separate billing project

Move the queries of an existing BigQuery data source to a separate billing project. The values of a data source cannot change, so the move needs a new BigQuery data source. Changing a billable metric can affect [contracts](https://zenskar.com/docs/20260301/zenskar-modules/contracts/glossary.md#contract) and invoices.

#### Prerequisites

* You need a role with the **Can Create Data Source**, **Can Read Data Source**, **Can Update Billable Metric**, and **Can Read Billable Metric** permissions. See [Permissions](#permissions).
* The service account has BigQuery Job User on the new billing project. See [Grant the roles](#grant-the-roles).

#### Add the new data source

1. Add a BigQuery data source with the same **Project ID** and **Dataset ID** as the old one, and the new billing project in **Billing Project ID**. See [Add a BigQuery data source](#add-a-bigquery-data-source).

#### Move each billable metric

1. Go to **Usage** > **Billable Metrics**.
2. Click a billable metric that queries the old data source. The billable metric opens in the editor.
3. In **Remote Datasource**, select the new BigQuery data source.
4. Write each table name in the query as a fully qualified table name.
5. Run the query for a customer and a period. See [Preview a billable metric](https://zenskar.com/docs/20260301/zenskar-modules/usage/how-to-guides/billable-metrics.md#preview-a-billable-metric).
6. Click **SAVE CHANGES**.

Repeat these steps for each billable metric that queries the old data source. The old data source stays in **Data Sources**, because a data source cannot be deleted.

Check that the new billing project pays for the queries. See [Check which project pays for the queries](#check-which-project-pays-for-the-queries).

### Check which project pays for the queries

Check in Google Cloud that the queries of a BigQuery data source run in the billing project.

#### Prerequisites

* The BigQuery data source has run a query in the last hour, for example in a billable-metric preview. See [Preview a billable metric](https://zenskar.com/docs/20260301/zenskar-modules/usage/how-to-guides/billable-metrics.md#preview-a-billable-metric).
* In Google Cloud, you need the BigQuery User and BigQuery Resource Viewer roles on the billing project and on the data project. This task needs no Zenskar permission.

#### Check the jobs

1. In the Google Cloud console, go to the **BigQuery** page, and select the billing project.

2. Run the following query. Replace `region-us` with the region of the dataset, such as `region-eu`.

   ```sql theme={null}
   SELECT
     job_id,
     project_id,
     total_bytes_billed,
     ARRAY(
       SELECT FORMAT('%s.%s.%s', t.project_id, t.dataset_id, t.table_id)
       FROM UNNEST(referenced_tables) AS t
     ) AS tables_read,
     creation_time
   FROM `region-us`.INFORMATION_SCHEMA.JOBS
   WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
     AND job_type = 'QUERY'
   ORDER BY creation_time DESC
   ```

3. Find the row whose `tables_read` names the table of the billable metric. In that row, `project_id` is the billing project.

4. Select the data project, and run the same query. The job is not listed.

The job is listed only in the billing project, so the billing project pays for the query.

> [!NOTE]
>
> A query that BigQuery answers from its cache has an empty `tables_read`. The query that you run in step 2 is also listed, with an empty `tables_read`.

See [JOBS view](https://docs.cloud.google.com/bigquery/docs/information-schema-jobs).

## Reference

### Add New Data Source page

The **Add New Data Source** page creates a BigQuery data source. Open it from **Usage** > **Data Sources** > **ADD DATA SOURCE**.

#### Set Up Source

| Field | Required | Description |
| - | - | - |
| **Source Name** | Yes | The name of the data source. It can contain letters, digits, underscores, hyphens, and spaces. |
| **Source Type** | Yes | **BigQuery**. |

#### Source Config

| Field | Required | Description |
| - | - | - |
| **Project ID** | Yes | The ID of the data project. |
| **Billing Project ID** | Yes | The ID of the billing project. It is the same ID as **Project ID** when one project is both. |
| **Dataset ID** | Yes | The name of the dataset, without the project. |
| **Location** | No | The location of the dataset, such as `US` or `EU`. |
| **Credentials JSON** | Yes | The whole contents of the JSON key file of the service account. It must be a JSON object. |

#### Source Access

| Option | Description |
| - | - |
| **Directly from Source** | It creates a remote data source. It is the only option for BigQuery, and the page selects it. |

#### Actions

| Action | Description |
| - | - |
| **SAVE SOURCE** | It saves the BigQuery data source without testing the connection. The page then confirms the save and shows **BACK**. |
| **BACK** | It returns to **Data Sources**. |

The values cannot change after you save the data source.

### Google Cloud roles

| Role | Granted to | Granted on | Needed for |
| - | - | - | - |
| BigQuery Job User (`roles/bigquery.jobUser`) | The service account | The billing project | It lets the service account run the queries of the BigQuery data source. |
| BigQuery Data Viewer (`roles/bigquery.dataViewer`) | The service account | The dataset, or the whole data project | It lets the service account read the tables of the dataset. |
| BigQuery User (`roles/bigquery.user`) and BigQuery Resource Viewer (`roles/bigquery.resourceViewer`) | You | The billing project and the data project | They let you check which project pays for the queries. |

### Permissions

| Action | Required permissions in the roles UI |
| - | - |
| Add a BigQuery data source | **Can Read Data Source** and **Can Create Data Source** |
| Create a billable metric that queries a BigQuery data source | **Can Read Billable Metric** and **Can Create Billable Metric** |
| Change a billable metric that queries a BigQuery data source | **Can Read Billable Metric** and **Can Update Billable Metric** |
| Move to a separate billing project | **Can Read Data Source**, **Can Create Data Source**, **Can Read Billable Metric**, and **Can Update Billable Metric** |
| Prepare Google Cloud, or check which project pays for the queries | No Zenskar permission. These tasks need Google Cloud roles. See [Google Cloud roles](#google-cloud-roles). |

The default **Owner** role has every permission. The default **User** role has read permissions only.

### API reference

| Operation | API reference |
| - | - |
| List data sources | [List data-source connectors](https://zenskar.com/docs/api-reference/data-sources/list-data-source-connectors.md) |
| Get a data source | [Get data-source connector](https://zenskar.com/docs/api-reference/data-sources/get-data-source-connector.md) |
| Create a BigQuery data source | [Create data-source connector](https://zenskar.com/docs/api-reference/data-sources/create-data-source-connector.md) |

To create a BigQuery data source, set `connector_type` to `BigQuery` and `remote_conn` to `true`. The `connector_config` object holds the values of **Source Config**:

| Field | `connector_config` key |
| - | - |
| **Project ID** | `project_id` |
| **Billing Project ID** | `billing_project_id` |
| **Dataset ID** | `dataset_id` |
| **Location** | `location` |
| **Credentials JSON** | `credentials_json`, with the contents of the key file as a string |

### Troubleshooting

| Problem | Cause | Solution |
| - | - | - |
| Zenskar does not save the data source. | **Project ID**, **Billing Project ID**, or **Dataset ID** is empty. | Fill in each marked field. |
| Zenskar does not save the data source. | **Credentials JSON** is empty or does not hold a JSON object. | Paste the whole contents of the key file. |
| Google Cloud does not let you create a key. | The `iam.disableServiceAccountKeyCreation` policy blocks service account keys in your organization. | Ask your organization policy administrator to allow keys for the project. |
| A query fails, but other queries work. | The billing project differs from the data project, and a table name in the query is not fully qualified. | See [Write a billable-metric query for a BigQuery data source](#write-a-billable-metric-query-for-a-bigquery-data-source). |
| Every query fails. | The service account is missing a role. | See [Grant the roles](#grant-the-roles). |
| Every query fails, and the service account has its roles. | A value of the BigQuery data source is wrong, for example a typo in a project ID, or a dataset name with the project in front. | Add a new BigQuery data source with the correct values. |
| The job check lists no job for the table. | The query ran more than an hour ago, or the query uses a region other than the region of the dataset. | See [Check which project pays for the queries](#check-which-project-pays-for-the-queries). |


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.