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

Connection 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. Two more are used only when a per-workspace feature is enabled (see below): CDP_PREP for Data Prep, and CDP_METADATA on the source chosen to hold the metadata export. 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.

CDP_PREP — only if Data Prep is enabled for the workspace

Data Prep is a per-workspace entitlement, off by default. A workspace that has it builds prepared tables into one more schema, CDP_PREP, and its connection test then carries one more write probe (write_prep). Until Data Prep is enabled nothing creates or reads this schema, so there is nothing to grant.

CREATE SCHEMA IF NOT EXISTS ANALYTICS.CDP_PREP;
GRANT ALL ON SCHEMA ANALYTICS.CDP_PREP TO ROLE CDP_ROLE;
GRANT ALL ON FUTURE TABLES IN SCHEMA ANALYTICS.CDP_PREP TO ROLE CDP_ROLE;

CDP_PREP is uppercase and unquoted, like CDP_PLANNER and unlike "cdp_raw" — it is created through the same identifier quoting the planner schema is, so a quoted lowercase "cdp_prep" names a different schema that nothing will ever write to.

A prepared table is not written once and read; it is rebuilt, and when it is incremental it is updated in place. GRANT ALL covers what that needs — CREATE TABLE for the table and its transient <slug>__next / <slug>__delta siblings, INSERT/UPDATE/DELETE for the merge and the delete_when predicate, SELECT for the delta computation and for the Models that read the result.

Ownership matters here in a way it does not for the other six. The atomic swap a full rebuild ends with is ALTER TABLE … SWAP WITH, and Snowflake requires OWNERSHIP on both tables for it — a role holding every other privilege but not ownership fails at the last step of an otherwise successful build. Because the platform creates both tables itself, the role owns them by construction, so this is normally automatic. It stops being automatic if the schema is managed with MANAGED ACCESS and ownership is reassigned, or if the tables are recreated by another role: in that case grant the role ownership of the schema’s tables, e.g. GRANT OWNERSHIP ON ALL TABLES IN SCHEMA ANALYTICS.CDP_PREP TO ROLE CDP_ROLE COPY CURRENT GRANTS; and GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA ANALYTICS.CDP_PREP TO ROLE CDP_ROLE;.

Revoking the entitlement does not drop the schema or its tables, so this grant can be withdrawn at your convenience rather than urgently.

CDP_METADATA — only if the metadata export is enabled for the workspace

The metadata export is a per-workspace entitlement, off by default, that keeps the workspace’s audience catalogue as the table CDP_METADATA.AUDIENCES. When it is enabled for a workspace, the connection test of the source chosen to hold the table carries one more write probe (write_metadata); the workspace’s other sources are tested exactly as before. Until then nothing creates or reads this schema, so there is nothing to grant.

CREATE SCHEMA IF NOT EXISTS ANALYTICS.CDP_METADATA;
GRANT ALL ON SCHEMA ANALYTICS.CDP_METADATA TO ROLE CDP_ROLE;
GRANT ALL ON FUTURE TABLES IN SCHEMA ANALYTICS.CDP_METADATA TO ROLE CDP_ROLE;

Like CDP_PREP, CDP_METADATA is uppercase and unquoted. Each export builds AUDIENCES__NEXT and publishes it with ALTER TABLE … SWAP WITH, so the same OWNERSHIP rule applies as for CDP_PREP above: the role must own both tables, which it does by construction unless ownership is reassigned.

To let other roles read the table, grant them USAGE on the schema and SELECT on FUTURE TABLES IN SCHEMA ANALYTICS.CDP_METADATA as well as on the current table.

Revoking the entitlement does not drop the schema or its table.

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://agentic.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