Snowflake

This guide covers how to configure Snowflake as a warehouse in Zeotap, including account setup, authentication options, and required permissions.

Prerequisites

• A Snowflake account with at least one warehouse
• A Snowflake user with read access to the data you want to sync
• Network access from Zeotap to your Snowflake account (IP allowlisting may be required)

Connection Configuration

Required Fields

Account Identifier

Enter your organization name and account name, joined by a hyphen — for example myorg-my_account. Run this in a Snowflake worksheet to get it:

SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();

It is also the first part of your account URL, so you can paste that URL in as-is and Zeotap will take the identifier out of it:

The older <account_locator>.<region>.<cloud> form (xy12345.us-east-1, xy12345.us-central1.gcp) still works if that is what your account uses. Add PrivateLink and client-redirect suffixes if they apply to your connection: xy12345.us-east-1.privatelink, myorg-my_account.global.

The identifier is canonicalized when the warehouse is saved, so the stored value is the one in the right-hand column above — lowercased, with any URL wrapper removed. Values that can’t be interpreted as an account identifier — anything containing spaces, a port, or credentials — are rejected at save time rather than failing later as a connection error.

PrivateLink and client-redirect suffixes are part of the identifier and should be kept: xy12345.us-east-1.privatelink, myorg-my_account.global.

Warehouse

Specify the virtual warehouse Zeotap should use for running queries. The warehouse must be accessible to the configured user and should have auto-resume enabled.

Recommendations:

• Use a dedicated warehouse (e.g., ZEOTAP_WH) to isolate query costs
• X-Small or Small size is sufficient for most workloads
• Enable auto-suspend (5 minutes) and auto-resume to control costs

-- Create a dedicated warehouse for Zeotap
CREATE WAREHOUSE ZEOTAP_WH
  WAREHOUSE_SIZE = 'X-SMALL'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

Authentication

Zeotap supports two authentication methods for Snowflake.

Username and Password

The simplest option. Provide a Snowflake username and password.

Key Pair Authentication

For stronger security, use RSA key pair authentication. This eliminates the need to store a password and is recommended for production environments.

Step 1: Generate a key pair

# Generate a 2048-bit RSA private key (encrypted)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes256 \
  -inform PEM -out rsa_key.p8
 
# Generate the public key from the private key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Step 2: Register the public key with Snowflake

ALTER USER CDP_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Copy the public key contents (without the BEGIN/END headers) when running this command.

Step 3: Configure in Zeotap

Required Permissions

Create a dedicated user and role for Zeotap with minimal required permissions:

-- Create a role for Zeotap
CREATE ROLE CDP_ROLE;
 
-- Grant warehouse usage
GRANT USAGE ON WAREHOUSE ZEOTAP_WH TO ROLE CDP_ROLE;
 
-- Grant database access
GRANT USAGE ON DATABASE ANALYTICS TO ROLE CDP_ROLE;
 
-- Grant schema access
GRANT USAGE ON SCHEMA ANALYTICS.PUBLIC TO ROLE CDP_ROLE;
 
-- Grant read access to all current tables
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE CDP_ROLE;
 
-- Grant read access to future tables (so new tables are automatically accessible)
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.PUBLIC TO ROLE CDP_ROLE;
 
-- Grant read access to views
GRANT SELECT ON ALL VIEWS IN SCHEMA ANALYTICS.PUBLIC TO ROLE CDP_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA ANALYTICS.PUBLIC TO ROLE CDP_ROLE;
 
-- Let Zeotap create its operational schemas on first use
-- (as their creator the role owns them, so no further grants are needed)
GRANT CREATE SCHEMA ON DATABASE ANALYTICS TO ROLE CDP_ROLE;
 
-- Create the user
CREATE USER CDP_USER
  PASSWORD = 'strong-password-here'
  DEFAULT_ROLE = CDP_ROLE
  DEFAULT_WAREHOUSE = ZEOTAP_WH
  MUST_CHANGE_PASSWORD = FALSE;
 
-- Assign the role
GRANT ROLE CDP_ROLE TO USER CDP_USER;

Operational Schemas

Zeotap creates and writes to six schemas in the connection’s database. With GRANT CREATE SCHEMA (above) it creates them on first use. If you’d rather not grant CREATE SCHEMA, pre-create each schema and grant write access explicitly:

CREATE SCHEMA IF NOT EXISTS ANALYTICS.CDP_PLANNER;
GRANT ALL ON SCHEMA ANALYTICS.CDP_PLANNER TO ROLE CDP_ROLE;
GRANT ALL ON FUTURE TABLES IN SCHEMA ANALYTICS.CDP_PLANNER TO ROLE CDP_ROLE;
-- Repeat for: CDP_AUDIT, CDP_JOURNEY, CDP_IDENTITY, "cdp_raw", "audit_logs"

• CDP_PLANNER — plan tables used for incremental sync change detection
• CDP_AUDIT — sync run and audit history
• CDP_JOURNEY — journey orchestration member state and event log tables
• CDP_IDENTITY — identity resolution output (default schema; configurable per identity graph)
• "cdp_raw" — landing schema for loader (inbound ELT) runs (default schema; configurable per loader)
• "audit_logs" — observability events and event delivery logs

Note the last two are created as case-sensitive lowercase identifiers — keep the double quotes in any statement that references them, or Snowflake resolves the name to a different (uppercase) schema.

The connection test verifies write access to every one of these schemas and reports the exact statements to run if a step fails.

Multiple Schemas

If your data spans multiple schemas, grant access to each:

GRANT USAGE ON SCHEMA ANALYTICS.MARKETING TO ROLE CDP_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA ANALYTICS.MARKETING TO ROLE CDP_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA ANALYTICS.MARKETING TO ROLE CDP_ROLE;

Models can query across schemas using fully-qualified table names (e.g., MARKETING.CAMPAIGNS), even if the source’s default schema is set to PUBLIC.

Network Configuration

If your Snowflake account has a network policy restricting access by IP address, you must add Zeotap’s egress IP addresses to the allowlist. All Zeotap connections to your warehouse originate from these static IPs:

-- Add Zeotap IPs to your network policy
ALTER NETWORK POLICY my_policy SET
  ALLOWED_IP_LIST = ('existing-ip', '34.76.7.172', '34.22.225.249');

These addresses are stable — Zeotap does not rotate them. If the list ever changes, this page is updated first.

Staged Loading (GCS)

When Zeotap writes data into Snowflake — event forwarding, loaders, and Reverse ETL or audience syncs to a Snowflake destination — it can stage the records as Parquet or Avro files in a Google Cloud Storage bucket that Snowflake bulk-loads with COPY INTO, instead of sending them as SQL statements. This is substantially faster for high-volume writes.

Staged loading requires a storage integration: a Snowflake account object that lets Snowflake read the Zeotap staging bucket as its own generated service account. No cloud credentials are shared with Snowflake, and no service-account key is downloaded.

Configure it in two fields on the connection, under Bulk Data Loading (optional):

USE ROLE ACCOUNTADMIN;
 
CREATE STORAGE INTEGRATION IF NOT EXISTS ZEOTAP_GCS_STAGING
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  -- The bucket is required. A bucket-less ('gcs://') is accepted here and then
  -- fails every load with "invalid URL: gcs://"; use ('*') if you must defer it.
  STORAGE_ALLOWED_LOCATIONS = ('gcs://<staging-bucket>/');
 
-- Read the service account Snowflake generated for the integration.
DESC STORAGE INTEGRATION ZEOTAP_GCS_STAGING;
 
-- The role Zeotap connects with must be able to use it.
GRANT USAGE ON INTEGRATION ZEOTAP_GCS_STAGING TO ROLE CDP_ROLE;

The staging bucket is provisioned by Zeotap, one per workspace, and the same bucket serves every Snowflake connection and destination in that workspace — so one integration can cover all of them. A saved connection shows the bucket name, with the SQL above pre-filled, under Bulk Data Loading (optional); a saved Snowflake destination shows it under Staged Loading (Optional).

For the full walkthrough and the setup order, see Staged Loading (GCS) on the Snowflake destination page.

Identifier Casing

Snowflake treats unquoted identifiers as uppercase. Zeotap normalizes all identifiers (database, schema, table, column names) to match Snowflake’s conventions. When writing models, you can use any casing in your SQL — Zeotap handles the translation.

-- These are all equivalent in Snowflake
SELECT customer_id FROM customers;
SELECT CUSTOMER_ID FROM CUSTOMERS;
SELECT Customer_Id FROM Customers;

If you have identifiers that were created with double-quote casing (e.g., "myColumn"), you’ll need to use the exact quoted form in your model SQL.

Example Configuration

Here’s a complete example using the API:

curl -X POST https://composable.zeotap.com/api/v1/sources \
  -H "Authorization: Bearer $API_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Production Snowflake",
    "type": "snowflake",
    "config": {
      "account": "myorg-production",
      "warehouse": "ZEOTAP_WH",
      "database": "ANALYTICS",
      "schema": "PUBLIC",
      "username": "CDP_USER",
      "password": "your-password"
    }
  }'

Troubleshooting

Next Steps

• Create a model using your Snowflake warehouse
• Configure column types for your model
• Set up a Reverse ETL sync to activate your data