Integrate data from Google Cloud Spanner to Snowflake using Matillion

Creating an Identity (User) in Google Cloud Spanner

Google Cloud Spanner does not manage users inside the database in the traditional sense (unlike MySQL or PostgreSQL). Instead, it relies on Google Cloud IAM (Identity and Access Management) to control access at the instance or database level. However, it is common to create a user table within Spanner to manage application-level users.

Below are instructions for both methods: - Granting access to a user via Google Cloud IAM - Creating an application-level user table within a Spanner database

1. Grant Access to a User via Google Cloud IAM

1. Identify the user’s email address.

2. Assign a Spanner role.

3. Open the Google Cloud Console.
4. Navigate to IAM & Admin > IAM.
5. Click +Grant access.
6. Enter the email address of the user.
7. Assign one of the following roles, depending on the required level of access:
   • Cloud Spanner Viewer (

     roles/spanner.viewer

     )
   • Cloud Spanner Database User (

     roles/spanner.databaseUser

     )
   • Cloud Spanner Database Admin (

     roles/spanner.databaseAdmin

     )
   • Cloud Spanner Owner (

     roles/spanner.admin

     )
8. Optional: Restrict the scope to a specific Spanner instance or database by specifying a resource.
9. Click Save.

Note: Permissions granted via IAM cannot be controlled within Spanner SQL.

2. Create an Application-Level User Table

You can also create a table to store application-level users and their information. This is not tied to authentication/authorization, but is useful for application logic.

Example: Create a

Users

table

CREATE TABLE Users (
  UserId       STRING(36) NOT NULL,    -- UUID or unique ID
  UserName     STRING(64) NOT NULL,    -- User’s display name
  Email        STRING(128) NOT NULL,   -- User’s email address
  CreatedAt    TIMESTAMP NOT NULL OPTIONS (allow_commit_timestamp=true),
) PRIMARY KEY(UserId);

Example: Insert a User

INSERT INTO Users (UserId, UserName, Email, CreatedAt)
VALUES (
  'b1a63b10-1c0c-4c53-a7f3-01dc96f95824',
  'Jane Doe',
  '[email protected]',
  PENDING_COMMIT_TIMESTAMP()
);

Note: Authentication to Spanner itself is still managed by IAM as described in step 1. The

Users

Installing the JDBC driver

At the time of writing, the Google Cloud Spanner JDBC driver is not included by default in Matillion Data Productivity Cloud. This is due to licensing or redistribution restrictions, which require users to obtain the driver directly from the vendor and handle installation manually. Follow these instructions to locate, download, and add the appropriate driver.

Step 1: Acquire the JDBC driver

You can download the Google Cloud Spanner JDBC driver from the official repository:

https://github.com/GoogleCloudPlatform/cloud-spanner-jdbc

When downloading, ensure you obtain a Type 4 JDBC driver, as this version is fully implemented in Java and does not require additional software or native libraries, maximizing compatibility and ease of deployment in cloud or containerized environments.

Step 2: Upload the driver to Matillion Data Productivity Cloud

Because the Spanner JDBC driver is not bundled within Matillion, you need to manually upload it. Matillion provides instructions for uploading external JDBC drivers on this page:

Uploading external drivers — Matillion documentation

Follow these instructions closely to ensure the driver is recognized by your Matillion agent and is available for use in orchestration jobs or database connectivity setup.

Step 3: Use the driver in Matillion

Once the driver is uploaded and registered, you can begin using it within your orchestration and transformation jobs in the Matillion Designer. Configuration and usage details for establishing database connections are available here:

Database Query usage instructions — Matillion documentation

Following these steps will enable your Matillion deployment to connect to and interact with Google Cloud Spanner using the official, up-to-date JDBC driver.

Checking network connectivity

To ensure successful connectivity between Matillion Data Productivity Cloud and your Google Cloud Spanner database, you need to allow incoming connections from the appropriate sources depending on your deployment configuration:

• Full SaaS Agent Configuration:
  Allow incoming database connections from the IP addresses listed in the Matillion documentation. You can find the up-to-date list of required IP addresses here: https://docs.matillion.com/data-productivity-cloud/security/docs/allowing-ip-addresses/.

• Hybrid SaaS Deployment:
  Allow incoming connections from your own virtual private cloud (VPC) where the Matillion agent is deployed. To help verify connectivity and permissions, you may find utilities and resources at https://exchange.matillion.com/data-productivity-cloud/pipeline/check-network-access/.

If your Google Cloud Spanner instance is being accessed using a DNS hostname, make sure that the Matillion Full SaaS or Hybrid SaaS agent can resolve that DNS address. This may require appropriate DNS configuration or rules depending on your network setup.

Querying Data from a Google Cloud Spanner Database

This guide provides instructions for querying data from a Google Cloud Spanner instance via SQL, and details best practices for extracting data to external systems (such as Snowflake), including adopting once-off initial loads followed by incremental loads.

Example: SQL SELECT Statements for Google Cloud Spanner

Execute queries using SQL syntax. Common examples are shown below.

Simple Table Selection

SELECT *
FROM Users;

Filtered Query

SELECT user_id, email, signup_timestamp
FROM Users
WHERE is_active = TRUE
ORDER BY signup_timestamp DESC
LIMIT 100;

Aggregate Query

SELECT country, COUNT(*) AS user_count
FROM Users
WHERE signup_timestamp >= '2024-01-01'
GROUP BY country;

Join Across Tables

SELECT u.user_id, u.email, o.order_id, o.amount
FROM Users AS u
INNER JOIN Orders AS o
ON u.user_id = o.user_id
WHERE o.created_at > '2024-06-01';

Datatype Conversion Considerations

When transferring data from Google Cloud Spanner to other database systems such as Snowflake, note that datatype conversions may be required.
Some examples include:

Consult official Spanner to Snowflake documentation for the latest details.

Extracting Data: Initial and Incremental Load Pattern

A recommended approach to load data from Spanner into analytics platforms like Snowflake is to:

1. Perform a once-off initial load (full dataset).
2. Follow with incremental loads to ingest only new or changed data.

Both steps typically use the same Database Query component (e.g., ETL tool or connector).

1. Initial Load

The query for an initial load does not include a filter clause — all data is extracted.

SELECT * FROM Orders;

2. Incremental Load

The query for incremental loads includes a filter, often based on a timestamp or unique ID column.

SELECT *
FROM Orders
WHERE updated_at > @last_loaded_timestamp;

The variable (

@last_loaded_timestamp

See the Incremental Load & Data Replication Strategy article for deeper guidance.

Both the initial and incremental loads leverage the same connection/component—optimization depends on the query clauses alone.

Data Integration Architecture

One of the key strengths of the Extract, Load, and Transform (ELT) architecture is its ability to effectively handle complex data integration challenges by decomposing the process into manageable steps. By loading data into Snowflake in advance, organizations can divide and conquer the integration problem: data is ingested first, leaving the transformation and integration tasks for a subsequent stage. This staged approach streamlines operations and ensures that raw data is immediately available for post-processing. Crucially, data integration often relies on comprehensive transformation workflows, and the optimal way to achieve this is through configurable transformation pipelines. With ELT, these pipelines are executed directly within the target Snowflake database. This design provides several significant benefits: transformations can be performed rapidly, on-demand, and at scale, leveraging Snowflake’s high performance and elasticity. Furthermore, because all processing occurs natively within the Snowflake environment, there is no need for separate data processing infrastructure—reducing both cost and operational complexity.