Introduction

In this article, we're going to show you how to connect your Astrato workspace to a data source in Google BigQuery.

Data connection details required
Creating a connection to Google BigQuery

Data connection details required

Before you can set up your data connection, you'll need the following:

Data source: data source name, project, and dataset.

User account: Enter your username and password when asked. If you are using a Google account, when you log in, Google will prompt you to consent to Astrato accessing your Google Workspace (Sheets and Drive).
If using a JWT Token, you'll need the token you've created. If you'd prefer to use the Google console to generate the token, please
Any user who uses the connection needs these permissions in BigQuery:
- bigquery.jobs.create Permission on the project: This permission allows creating and running query jobs. It is included in predefined roles such as BigQuery Job User.
- bigquery.tables.getData Permission on all tables and views referenced in the query: This allows reading the data being queried. It is included in the BigQuery Data Viewer role.

Creating a data connection to Google BigQuery

Select Data in the Astrato sidebar and click on Create Data to open the Create New Data Source.
Click the Google BigQuery icon to start.

Select either OAuth or JWT Token for access.

Select either OAuth or JWT Token for access.

OAuth - Allow Astrato permission to access external data

Astrato checks if you already have an integration with Google BigQuery, if not, the message displays that permission is required.

Click Confirm to continue.

The Google authentication page displays, select the account you want to use, review the permissions required, and click Allow to continue.

Google authentication page displays, select the account you want to use to access the dataset

If the user account authentication is successful, Astrato displays the message that permissions are granted. Click OK to continue.

Google BigQuery permissions have been granted.

OAuth - Enter data connection and project details

Enter the details (project, dataset, data source name) and click Connect. This completes the data connection process. The Data View Editor in Astrato displays once the connection is complete.

Enter the project, dataset and data source name in Google BigQuery.

JWT - Enter data connection details

Once you've picked the data connection, slide the toggle to the right to select the option for JWT Token.

Enter the details (JSON key, dataset, data connection name) and click Connect.
The Data View Editor in Astrato displays once the connection is complete.

JWT- Dynamic Data Connection

Dynamic data connection is good when connecting users to different projects but using one data model.

The main use case for dynamic connections is combining it with embedded analytics.

To serve OEM solutions.

Setting up a dynamic data connection is similar to setting up a JWT connection.

Follow these steps:

Create a new BigQuery connection (check the use dynamic connection checkbox)
Paste the JWT key
Select the default project (this will be the default project to build the data model and define measures and dimensions)
Select the dataset to use. This part is not dynamic, and for all users, the connection will use the same dataset for all users.
Give the data connection a name.
Click next
Add more users (users need to be part of the Astrato tenant) to the connection settings.
Define the key to authenticate and the project to use in the connection for each user.
Click next
Define who has the right to use the connection.
Click connect
Known Limitation
- Only users that use the same JWT key and project as the defaults will be able to edit and see the data in the Data View Editor
- The dynamic data connection is only used in published workbooks.
- Users that aren't defined in the dynamic connection table won't be able to see data in the workbook.

Setting up JWT Authentication in Google BigQuery for use in Astrato

This article explains how to configure a BigQuery connection using the JWT authentication method. With this approach, a Google Cloud service account is used to authenticate and establish a secure connection to BigQuery.

The JWT method is recommended for server-to-server integrations, as it avoids manual user authentication and ensures reliable, automated access to your BigQuery datasets.

Create a Service Account
1. In the Google Cloud Console, navigate to the project where your BigQuery instance is located.
2. Go to IAM & Admin → Service Accounts, then click Create Service Account.
- Click Create and Continue to proceed with the service account setup.
1. Grant the service account the following minimum set of permissions:
  - BigQuery Connection Service Agent
  - BigQuery Data Viewer
  - BigQuery Job User
- Click Done to complete the setup.
Create a Service Account Key
The service account key will be used by Astrato to establish the connection with Google BigQuery.
1. In the Google Cloud Console, go to IAM & Admin → Service Accounts.
2. Select the service account created in the previous step.
3. Navigate to the Keys tab, then click Add Key → Create New Key. Ensure the Key type is set to JSON.
The key file will be generated and automatically downloaded to your computer. Store this file securely, as it will be required for the Astrato connection.
1. Add the connection in Astrato
2. Add the connection in Astrato
3. In Astrato, navigate to the Data + AI tab on the left panel, then go to Data Connection → New Connection.
4. Select Google BigQuery as the connection type.
Under Authentication Type, choose JWT Token. Paste the JSON key from the service account you created earlier. Astrato will use this key to establish the connection, allowing you to access the datasets the service account has permission to.
Enter a name for your connection, then click Next.
Select which roles should be able to use the newly created BigQuery connection.
Complete the setup by clicking Connect.