Integrate data from Google BigQuery to Snowflake using Matillion

Creating an Identity in Google BigQuery

BigQuery itself does not have a concept of local, standalone database “users” like traditional relational database systems (for example, no

CREATE USER

To enable a user, group, or service account to work with BigQuery datasets or resources, you assign IAM roles at the project or dataset level. Below, you'll find step-by-step instructions for granting a user access using both the Google Cloud Console and the

bq

Granting User Access to BigQuery

1. Identify the User

You’ll need the user’s Google account email address, a Google group email, or the email address for a service account, e.g.:

• User:

  [email protected]

• Group:

  [email protected]

• Service account:

  [email protected]

2. Choose the Appropriate IAM Role

Scope roles as necessary (more info here). Common predefined BigQuery roles:

• BigQuery Data Viewer:

  roles/bigquery.dataViewer

  (read access)
• BigQuery Data Editor:

  roles/bigquery.dataEditor

  (read and write access)
• BigQuery User:

  roles/bigquery.user

  (run jobs, create datasets)
• BigQuery Admin:

  roles/bigquery.admin

  (full control)

You may also custom-create roles for finer control.

Using Google Cloud Console

1. Navigate to the Google Cloud Console.
2. Select your project at the top of the page.
3. In the navigation menu, go to IAM & Admin > IAM.
4. Click Grant Access.
5. In the New principals field, enter the user’s email address.
6. In the Select a role dropdown, select BigQuery > choose the appropriate role.
7. Click Save.

Using the

bq

Command-Line Tool

Below is an example for granting the

BigQuery Data Viewer

[email protected]

```ell bq show --format=prettyjson --project_id=[PROJECT_ID] # (Optional) See your project's current IAM policy

gcloud projects add-iam-policy-binding [PROJECT_ID] \ --member='user:[email protected]' \ --role='roles/bigquery.dataViewer' ```

To grant access to a dataset specifically:

ell
bq update --dataset --source <(echo '{
  "access": [
    {
      "role": "READER",
      "userByEmail": "[email protected]"
    }
  ]
}') [PROJECT_ID]:[DATASET_ID]

Replace

[PROJECT_ID]

[DATASET_ID]

Granting Access to a Service Account with SQL (for Authorized Views)

You may occasionally need to grant dataset access for a service account—often for authorized views. Example (using the Cloud Console, not regular SQL):

1. Go to the BigQuery page in the Console.
2. Click on your dataset.
3. In the Details pane, click Sharing > Permissions.
4. Click Add principal, set the service account email in New principals, assign an appropriate BigQuery role, and click Save.

Note: All access to BigQuery resources requires an existing Google identity (user, service account, or group). BigQuery and Google Cloud do not create or manage these identities—they must already exist in your Google Cloud environment.

Installing the JDBC driver

To enable connectivity between Matillion Data Productivity Cloud and Google BigQuery, you will need to install the BigQuery JDBC driver manually. At the time of writing, this driver is not bundled with Matillion Data Productivity Cloud by default due to licensing and redistribution restrictions imposed by the driver’s vendor. The following instructions provide a step-by-step guide to downloading the correct driver and installing it within the Matillion environment.

1. Obtain the BigQuery JDBC Driver

1. Visit the official Simba Drivers for Google BigQuery download page:
   https://cloud.google.com/bigquery/providers/simba-drivers/

2. Look for the JDBC driver version designated as a Type 4 driver (also referred to as a pure Java implementation). This is preferred for cloud-based integrations such as with Matillion.

3. Download the appropriate JDBC driver packaged as a

   .jar

   file. Ensure you comply with all licensing terms as specified by Google and Simba.

2. Upload the Driver to Matillion Data Productivity Cloud

1. Follow the instructions provided by Matillion to upload external JDBC drivers to your cloud agent:
   https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

2. Make sure the uploaded driver is accessible to the agent that will be running your database integrations.

3. After uploading, restart the agent if required to ensure the new driver is recognized by Matillion.

3. Configure BigQuery Connections in Matillion

With the driver installed, you can now proceed to set up and use BigQuery as a data source or destination within Matillion Data Productivity Cloud:

1. Follow the official usage instructions to configure and utilize BigQuery connections:
   https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

Refer to Matillion’s documentation for any parameter settings, authentication methods, or connection property requirements specific to your BigQuery setup.

Checking network connectivity

To ensure that Matillion Data Productivity Cloud can communicate with your Google BigQuery database, you must configure network access according to your chosen deployment method:

• Full SaaS agent configuration:
  You need to allow incoming connections from the Matillion Data Productivity Cloud by whitelisting the IP addresses specified at this link. Ensure that all relevant IP addresses are included in your Google BigQuery network configuration to allow access from the Matillion agent.

• Hybrid SaaS deployment:
  In this scenario, connections to Google BigQuery will originate from your organization’s own virtual private cloud (VPC). Make sure your Google BigQuery instance allows incoming connections from your VPC. If you need to verify network access, or for additional connectivity utilities, refer to the tools available at this link.

Additionally, if your Google BigQuery database is referenced using a DNS hostname, ensure that the Full SaaS or Hybrid SaaS agent can successfully resolve the DNS address. This may require that appropriate DNS servers or forwarders are accessible from the environment where the agent is running.

Querying Data from Google BigQuery

This guide explains how to query data from a Google BigQuery database, with sample SQL queries, notes on datatype conversions (especially relevant when integrating with Snowflake), and best practices for initial and incremental loads using a Database Query component.

1. Basic SELECT Query Examples

The standard syntax for querying BigQuery is SQL-based. Below are sample queries:

``
-- Select all columns from a public dataset
SELECT *
FROM

-- Select specific columns with filters SELECT name, gender FROM

project.dataset.users

-- Aggregate with GROUP BY SELECT state, COUNT(*) AS num_births FROM

bigquery-public-data.samples.natality

2. Notes on Datatype Conversion: BigQuery vs Snowflake

When migrating or integrating between BigQuery and Snowflake, keep in mind:

• STRING (BigQuery) maps to VARCHAR (Snowflake)
• BYTES (BigQuery) maps to BINARY (Snowflake)
• INTEGER (BigQuery) can be either NUMBER or BIGINT (Snowflake)
• FLOAT (BigQuery) maps to FLOAT (Snowflake)
• BOOLEAN (BigQuery) maps to BOOLEAN (Snowflake)
• TIMESTAMP and DATETIME types require specific attention to time zone semantics

Tip: Always cast columns explicitly when datatype discrepancies may arise.

-- Example: Explicit casting
SELECT CAST(user_id AS STRING) AS user_id_str
FROM `project.dataset.users`

3. Initial vs Incremental Load Patterns

The recommended approach is: - Perform a one-off initial load that captures all existing data, with no filter clause. - Follow up with incremental loads that retrieve only new/changed data since the last load, using a filter clause.

The Matillion exchange article – Incremental Load: Data Replication Strategy provides an in-depth explanation of this approach.

Initial Load Example

-- Loads the entire table: no filter clause required
SELECT *
FROM `project.dataset.sales_orders`

Incremental Load Example

-- Loads only records modified since the last batch
SELECT *
FROM `project.dataset.sales_orders`
WHERE modified_at > TIMESTAMP('2024-06-01 00:00:00')

Both queries use the same Database Query component, but only the incremental load includes a filter (e.g., on

modified_at

updated_at

For detailed implementation steps and filter patterns, always refer to the relevant ELT tool and consult Google BigQuery documentation as well as the Matillion exchange article linked above.

Data Integration Architecture

Loading data into Snowflake in advance of integration—a core aspect of the Extract, Load, Transform (ELT) architecture—enables organizations to divide and conquer the integration challenge by breaking it into two manageable steps: first loading the raw data, and then performing integrations and transformations as needed. This separation simplifies development and troubleshooting, and also unlocks greater flexibility for managing evolving data requirements. To effectively transform and integrate this data, modern solutions use scalable data transformation pipelines, which allow for the coordinated execution of complex data processing tasks. A further advantage of the ELT approach is that transformation and integration workloads run directly inside the target Snowflake database. This design ensures fast, on-demand processing that can automatically scale with your needs, eliminating the need to provision or maintain separate processing infrastructure, and reducing both operational complexity and total cost of ownership.