Snowflake-Labs/google-sheets-udtf

Snowflake-Labs/google-sheets-udtf

Description: UDTF for accessing Google Sheets within Snowflake

Language: PLpgSQL

License: Apache-2.0

Stars: 0

Forks: 0

Open issues: 0

Created: 2026-01-12T03:06:45Z

Pushed: 2026-01-22T23:30:09Z

Default branch: main

Fork: no

Archived: no

README:

Query Google Sheets Directly in Snowflake

If you have ever worked in BigQuery, you know the feeling of pure power that comes from the "External Table" feature for Google Sheets. You can write a SQL query that joins a massive 10TB clickstream table against a manually updated Google Sheet maintained by the marketing team, all without moving a single byte of data yourself.

For a long time, Snowflake users have looked at this feature with envy.

Data Analysts live in a world of "official" data and "shadow" data. The official data lives in the warehouse. The shadow data—sales targets, ad-hoc campaign mappings, manual overrides, and event rosters—lives in Google Sheets.

Historically, getting that Sheet data into Snowflake involved one of three painful options:

1. The "CSV Shuffle": Download Sheet \-\> Upload to Stage \-\> COPY INTO \-\> Repeat every time the sheet changes. 2. The "Ticket Queue": Ask a Data Engineer to build a Fivetran/Airbyte connector (ETA: 2 weeks). 3. The "Python Script": Run a local pandas script to push data, which breaks when your laptop goes to sleep.

With the release of **Snowflake External Network Access** and **Python UDTFs** (User Defined Table Functions), the gap can finally be bridged natively.

Here is how you can build a BigQuery-style Google Sheet integration in Snowflake, entirely within SQL.

The Architecture: Secure, Direct, and Live

The process is not simply a file download. A secure tunnel is being built.


The solution utilizes modern Snowflake features:

1. Network Rules: An allowlist that tells Snowflake, "It is okay to talk to sheets.googleapis.com." 2. Role-Based Credentials: A credentials table with Row Access Policy ensuring each role uses its own Google Service Account. 3. Row-Level Security: Snowflake's Row Access Policy ensures roles can only access their own credentials. 4. Python UDTFs: A Python function that runs inside Snowflake, handles the OAuth handshake, fetches the data, and pipes it out as rows.

The result? You type this:

SELECT
*
FROM
TABLE(read_google_sheet(
'1IWYLRuWf90Qsa0s0wAuddMiFUTm9W6eT5mWa7nbQE',
'Sheet1!A1:Z'));

And you get live data, instantly—using the service account configured for your current role.

Multi-Role Security Model

This solution implements role-based credential isolation, allowing different Snowflake roles to access Google Sheets using their own dedicated service accounts.

Why Role-Based Credentials?

| Benefit | Description | |---------|-------------| | Isolation | Each team/role has its own Google Service Account with access only to their sheets | | Auditability | Google Cloud logs show which service account accessed which sheet | | Least Privilege | Marketing's service account can't access Finance's sheets, and vice versa | | Easy Rotation | Rotate one role's credentials without affecting others | | Self-Service | Teams can share sheets with their own service account email |

How It Works

flowchart TB
subgraph Snowflake
subgraph Roles["Snowflake Roles"]
SYSADMIN["SYSADMIN
runs query"]
DATA_ANALYST["DATA_ANALYST
runs query"]
end

subgraph CredTable["role_credentials table"]
direction LR
Row1["SYSADMIN | {...sysadmin creds}"]
Row2["DATA_ANALYST | {...analyst creds}"]
Row3["MARKETING | {...marketing creds}"]
end

RLS[["🔒 Row Access Policy
filters to CURRENT_ROLE()"]]

subgraph UDTF["read_google_sheet() UDTF"]
Step1["1. Query credentials for CURRENT_ROLE()"]
Step2["2. Sign JWT with role's private key"]
Step3["3. Get OAuth token from Google"]
Step4["4. Fetch sheet data"]
end
end

GoogleAPI[("Google Sheets API
sheets.googleapis.com")]

SYSADMIN --> CredTable
DATA_ANALYST --> CredTable
RLS -.-> CredTable
CredTable --> UDTF
Step1 --> Step2 --> Step3 --> Step4
UDTF --> GoogleAPI

The Use Case: The "Sales Targets" Nightmare

Let's look at a classic scenario. You are an analyst calculating sales commissions.

• Fact Table: SALES\_TRANSACTIONS (Live in Snowflake).
• Dimension: SALES\_REP\_TARGETS (Live in a Google Sheet, edited by the VP of Sales every Monday).

Without this integration, every Monday morning is a fire drill of exporting and importing. With this UDTF, your query looks like this:

WITH live_targets AS (
-- Read the live sheet
SELECT
row_data:Rep_ID::STRING as rep_id,
row_data:Target_Amount::FLOAT as target,
row_data:Month::STRING as month
FROM TABLE(read_google_sheet('abc12345...', 'Targets!A:C'))
)
SELECT
t.rep_id,
SUM(s.amount) as actual_sales,
MAX(t.target) as target_sales,
(SUM(s.amount) / MAX(t.target)) * 100 as pct_attainment
FROM SALES_TRANSACTIONS s
JOIN live_targets t
ON s.rep_id = t.rep_id
AND s.month = t.month
GROUP BY 1;

When the VP updates the target in the Sheet, you re-run the query. The numbers update. No CSVs. No engineering tickets.

How It Works Under the Hood

The secret sauce is the Python handler. Because Snowflake's environment is secure, installing google-auth via pip and expecting it to work seamlessly in every environment (especially GovCloud) is not possible.

Instead, a lightweight, robust approach is used, utilizing the standard requests and cryptography libraries to manually sign a JWT (JSON Web Token).

1. The Lookup: The Python function queries the role_credentials table for the current role's service account JSON (Row Access Policy ensures isolation). 2. The Sign: It signs a JWT using the private key from the credentials. 3. The Exchange: It sends that JWT to Google's OAuth endpoint to get a temporary Access Token. 4. The Fetch: It calls the Google Sheets API and streams the results back to your SQL query as a variant (JSON) object.

Setting It Up

This solution requires the following roles:

1. Account Admin: Sets up the Network Rule, credentials table, Row Access Policy, and External Access Integration (one-time security configuration). 2. Account Admin: Adds credentials for each role…