Snowflake & Snowflake External + GCS Integration

Enable seamless integration between Google Cloud Storage (GCS) and Snowflake using an automated Openbridge setup. This process simplifies Snowflake’s standard GCS integration steps.

✅ Prerequisites

Google Cloud account with permission to:
- Create GCS buckets
- Create and manage service accounts
Snowflake account with ACCOUNTADMIN role

🚀 Step 1: Create a Google Cloud Storage Bucket

This step must be done manually before proceeding.

Go to Google Cloud Console → Cloud Storage
Click “Create bucket”
Enter a unique bucket name (e.g., openbridge-snowflake-bucket)
Keep all settings default (no special permissions)
Click Create (Save the Bucket name for the later steps.)

⚙️ Step 2: Run Openbridge Snowflake Setup Script

This script automates most Snowflake configuration, including:

Storage integration
OAuth integration
Role/user/schema setup
External stage creation

⚙️ Before You Run the Script

Make sure to replace all placeholder values with those specific to your Snowflake environment:

YOUR_OPENBRIDGE_DATABASE
YOUR_OPENBRIDGE_SCHEMA
YOUR_OPENBRIDGE_USERNAME
YOUR_OPENBRIDGE_PASSWORD
YOUR_OPENBRIDGE_ROLE
YOUR_OPENBRIDGE_STAGE
YOUR_OPENBRIDGE_WAREHOUSE
YOUR_GCS_BUCKET_NAME (exact bucket name from GCS)

🔤 Naming Guidelines:

Use UPPERCASE for all Snowflake object names
Use exact casing for:
- openbridge_gcs_bucket_name (must match bucket name in GCS)
- openbridge_password (can be mixed-case)

📝 Save These Values — You’ll Need Them Later in Openbridge

🔐 For OAuth Login (during Openbridge authorization step):

openbridge_username
openbridge_password

📥 For Snowflake Destination Configuration (Step 3 in UI):

Database
Schema
Warehouse
Stage
Role
GSC Bucket Name (created in earlier steps)

Update the variables and run the script in your Snowflake worksheet:

-- Set variables (Replace placeholders with your actual values)
SET openbridge_database = 'YOUR_OPENBRIDGE_DATABASE';       -- e.g., 'BUXZZ_DATABASE'
SET openbridge_schema = 'YOUR_OPENBRIDGE_SCHEMA';           -- e.g., 'BUXZZ_SCHEMA'
SET openbridge_username = 'YOUR_OPENBRIDGE_USERNAME';       -- e.g., 'BUXZZ_USER'
SET openbridge_password = 'YOUR_SECURE_PASSWORD';           -- e.g., '12BUXZZ!@'
SET openbridge_role = 'YOUR_OPENBRIDGE_ROLE';               -- e.g., 'BUXZZ_ROLE'
SET openbridge_stage = 'YOUR_OPENBRIDGE_STAGE';             -- e.g., 'BUXZZ_STAGE'
SET openbridge_warehouse = 'YOUR_OPENBRIDGE_WAREHOUSE';     -- e.g., 'BUXZZ_WAREHOUSE'

SET openbridge_warehouse_type = 'STANDARD';                 -- Snowflake warehouse type (e.g., 'STANDARD')
SET openbridge_warehouse_size = 'XSMALL';                   -- Snowflake warehouse size (e.g., 'XSMALL', 'SMALL', etc.)

SET openbridge_gcs_bucket_name = 'YOUR_GCS_BUCKET_NAME';    -- e.g., 'openbridge-snowflake-bucket'

SET openbridge_namespace = $openbridge_database || '.' || $openbridge_schema;
SET openbridge_gcs_uri = 'gcs://' || $openbridge_gcs_bucket_name || '/';

-- Use the ACCOUNTADMIN role to ensure sufficient privileges
USE ROLE ACCOUNTADMIN;

-- Step 1: Setup
-- Create the Openbridge role if it doesn't exist
CREATE ROLE IF NOT EXISTS IDENTIFIER($openbridge_role);

-- Create the Openbridge user if it doesn't exist
CREATE USER IF NOT EXISTS IDENTIFIER($openbridge_username)
  PASSWORD = $openbridge_password
  DEFAULT_ROLE = $openbridge_role
  DEFAULT_WAREHOUSE = $openbridge_warehouse
  DEFAULT_NAMESPACE = $openbridge_namespace; -- Use pre-built namespace

-- Grant the role to the user
GRANT ROLE IDENTIFIER($openbridge_role) TO USER IDENTIFIER($openbridge_username);

-- Assign the current user's name to a variable
SET current_username = CURRENT_USER();

-- Grant the role to your current user (for testing purposes)
GRANT ROLE IDENTIFIER($openbridge_role) TO USER IDENTIFIER($current_username);

-- Create the warehouse if it doesn't exist
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($openbridge_warehouse)
  WAREHOUSE_SIZE = $openbridge_warehouse_size
  WAREHOUSE_TYPE = $openbridge_warehouse_type
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

-- Grant usage on the warehouse to the role
GRANT USAGE ON WAREHOUSE IDENTIFIER($openbridge_warehouse) TO ROLE IDENTIFIER($openbridge_role);

-- Create the database if it doesn't exist
CREATE DATABASE IF NOT EXISTS IDENTIFIER($openbridge_database);

-- Grant usage on the database to the role (without CREATE SCHEMA privilege)
GRANT USAGE ON DATABASE IDENTIFIER($openbridge_database) TO ROLE IDENTIFIER($openbridge_role);

-- Use the new database
USE DATABASE IDENTIFIER($openbridge_database);

-- Create the OAuth2 integration
CREATE SECURITY INTEGRATION IF NOT EXISTS
  openbridge_oauth2
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://oauth.api.openbridge.io/oauth/callback'
  ENABLED = TRUE
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
  COMMENT = 'Openbridge OAuth';

-- Create a schema
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($openbridge_schema); 

-- Grant privileges on the schema to the role
-- GRANT USAGE ON SCHEMA IDENTIFIER($openbridge_schema) TO ROLE IDENTIFIER($openbridge_role);
GRANT ALL PRIVILEGES ON SCHEMA IDENTIFIER($openbridge_schema) TO ROLE IDENTIFIER($openbridge_role);
GRANT CREATE TABLE, CREATE VIEW, CREATE STAGE, CREATE FILE FORMAT ON SCHEMA IDENTIFIER($openbridge_schema) TO ROLE IDENTIFIER($openbridge_role);
USE SCHEMA IDENTIFIER($openbridge_namespace);

CREATE STORAGE INTEGRATION IF NOT EXISTS openbridge_gcs
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ($openbridge_gcs_uri);

CREATE STAGE IF NOT EXISTS IDENTIFIER($openbridge_stage)
  URL = $openbridge_gcs_uri
  STORAGE_INTEGRATION = openbridge_gcs
  FILE_FORMAT = (TYPE = 'PARQUET');

GRANT ALL PRIVILEGES ON STAGE IDENTIFIER($openbridge_stage) TO ROLE IDENTIFIER($openbridge_role);

GRANT USAGE ON INTEGRATION openbridge_gcs TO ROLE IDENTIFIER($openbridge_role);

-- Step 2: Switch to the Openbridge role for testing
USE ROLE IDENTIFIER($openbridge_role);

-- Set the context to use the Openbridge warehouse, database, and schema
USE WAREHOUSE IDENTIFIER($openbridge_warehouse);
USE DATABASE IDENTIFIER($openbridge_database);
USE SCHEMA IDENTIFIER($openbridge_schema);

-- Step 3: Test Operations

-- Test 1: Create a table
CREATE OR REPLACE TABLE test_table (
  id INT AUTOINCREMENT PRIMARY KEY,
  name STRING,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);

-- Test 2: Insert data into the table
INSERT INTO test_table (name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

-- Test 3: Query data from the table
SELECT * FROM test_table;

-- Test 4: Update data in the table
UPDATE test_table
SET name = 'Alice Updated'
WHERE name = 'Alice';

-- Test 5: Delete data from the table
DELETE FROM test_table
WHERE name = 'Bob';

-- Test 6: Drop the table
DROP TABLE test_table;

📄 What’s Next: Follow Snowflake’s Official Documentation

Now that you've completed the Openbridge setup script, continue the process using Snowflake’s official guide for GCS integration.

✅ However, you can skip Step 1 and Step 4 from Snowflake docs — these are already handled in the Openbridge setup script.

🔧 Steps You Still Need to Complete

These steps must be completed manually via the Snowflake UI and GCS Console:

🔍 Step 3.1: Retrieve Snowflake’s GCS Service Account Email

Corresponds to Step 2 in Snowflake docs

Run this command in Snowflake:

DESC STORAGE INTEGRATION openbridge_gcs;

Copy the value under STORAGE_GCP_SERVICE_ACCOUNT.

🔐 Step 3.2: Grant GCS Bucket Access to Snowflake

Follow Step 3 in the Snowflake docs to grant access using the retrieved service account.

🔧 Step 3.3: Create a Google Cloud Service Account

After completing Step 3.1 and 3.2 from the Snowflake documentation:

Log in to Google Cloud Console
Navigate to IAM & Admin → Service Accounts.
Click "Create Service Account"
Give it a name such as openbridge-snowflake-access.
Assign Required Permissions
During creation, assign the same role you granted to Snowflake in Step 3.2 (typically Storage Object Viewer) to this service account.
Generate a JSON Key File
- Go to the "Keys" tab of the created service account.
- Click “Add Key” → “Create new key”
- Choose JSON format and click Create
- Download and save this file — you’ll need it in the Openbridge UI when setting up the destination.

🗂️ This JSON file allows Openbridge to access your GCS bucket when transferring data via Snowflake.

🪪 Step 3.4: Retrieve Openbridge OAuth Credentials

Run this in Snowflake to retrieve the OAuth credentials (client ID and secret):

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('openbridge_oauth2');

This returns a JSON object like:

{  
  "OAUTH_CLIENT_SECRET_2": "YOUR-CLIENT-SECRET-2",  
  "OAUTH_CLIENT_SECRET": "YOUR-CLIENT-SECRET",  
  "OAUTH_CLIENT_ID": "YOUR-CLIENT-ID"  
}

✅ You only need the following values for Openbridge:

OAUTH_CLIENT_ID
OAUTH_CLIENT_SECRET

Ignore OAUTH_CLIENT_SECRET_2 — it is not used in the setup process.

🌐 How to Find the Authorization URL

Snowflake does not explicitly display an "Authorization URL." Instead, you will find your Account URL (also referred to as Server URL or Account Locator) in the Account Details section of the Snowflake UI.

It typically looks like this:

<your-account>.snowflakecomputing.com

To construct the Authorization URL required for Openbridge:

👉 Simply prefix it with https://

https://<your-account>.snowflakecomputing.com

🌐 Allow Openbridge IPs via Network Policy (Optional but Recommended)

Some Snowflake accounts restrict external access through network policies. If your account has a policy or requires one, you must allow Openbridge’s IP addresses to ensure successful authorization and data delivery.

Openbridge IP addresses:

52.2.68.68/32  
52.54.227.22/32

You can configure this in one of two ways:

Option 1: Simple Network Policy (Legacy Method)

-- Use ACCOUNTADMIN for required privileges
USE ROLE ACCOUNTADMIN;

-- Create a new network policy with Openbridge IPs
CREATE NETWORK POLICY openbridge_ip_whitelist 
  ALLOWED_IP_LIST = ('52.2.68.68/32', '52.54.227.22/32');

-- OR, modify your existing policy
ALTER NETWORK POLICY <your_current_network_policy_name> 
  SET ALLOWED_IP_LIST = ('52.2.68.68/32', '52.54.227.22/32');

Option 2: Using Network Rule + Policy (Recommended)

-- Create a network rule for Openbridge IPs
CREATE OR REPLACE NETWORK RULE openbridge_ip_rule 
  TYPE = IPV4 
  MODE = INGRESS 
  VALUE_LIST = ('52.2.68.68/32', '52.54.227.22/32');

-- Link the rule to a new policy
CREATE OR REPLACE NETWORK POLICY openbridge_access_policy
  ALLOWED_NETWORK_RULE_LIST = ('openbridge_ip_rule');

✅ Verify Your Settings

-- Check your policy's allowed IPs
DESC NETWORK POLICY openbridge_access_policy;

-- Verify which policy is active on your account
SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;

For more info, see Snowflake Network Policies documentation

✅ Once Setup is Complete

In the Openbridge UI:

Step 1: Confirm your Snowflake setup is ready.
Step 2:
- ➕ Option A: Use Existing Authorization
  If you have a saved authorization, select it from the list.
- 🔄 Option B: Create New Authorization
  Choose Authorize → Select "Bring Your Own App" → Enter Client ID, Secret, and Authorization URL → Authorize via direct Snowflake login.
  (Once complete, your Snowflake identity will appear and can be selected.)
Step 3: Fill in:
- Snowflake Account Identifier (can be found in your Snowflake account details)
- Database
- Schema
- Warehouse
- Stage
- Role
- GCS Bucket
- Google Service Account
Step 4: Name your destination and click Save.

That’s it! Your Snowflake destination is now fully integrated with Google Cloud — securely and ready for scalable data ingestion.