Snowflake

Write data back to a Snowflake table. Use Zeotap to materialize model results, audience memberships, or enriched data directly into Snowflake.

Prerequisites

• A Snowflake account
• A Snowflake user with CREATE TABLE and INSERT/UPDATE/DELETE privileges on the target schema
• A Snowflake warehouse for compute
• Optional, for staged loading: an account administrator who can create a storage integration (CREATE INTEGRATION requires the ACCOUNTADMIN role or a role with that privilege granted)

Authentication

Snowflake supports two authentication methods:

Username & Password

1. Enter your Snowflake Username and Password

Key Pair

1. Generate an RSA key pair for Snowflake authentication
2. Assign the public key to your Snowflake user: ALTER USER my_user SET RSA_PUBLIC_KEY='...'
3. Paste the Private Key (PEM format) in Zeotap
4. Optionally enter the Passphrase if the private key is encrypted

Configuration

Connection Settings

Staged Loading (Optional)

These two fields switch sync runs from row-by-row INSERT statements to Parquet bulk loading. Leave them blank to use the standard write path. See Staged Loading (GCS) for the full setup.

Target Settings

Supported Operations

Sync Modes: Upsert, Insert, Update, Mirror

Audience Sync Modes: Add, Remove, Mirror, Upsert

Features

• Field Mapping: Yes
• Schema Introspection: Yes — Zeotap reads column metadata from Snowflake’s INFORMATION_SCHEMA

How It Works

Zeotap always writes through a temporary staging table and then applies the sync mode with a single SQL statement against the target table. There are two ways rows reach that staging table.

Direct loading (default). Each batch of rows is inserted into a temporary staging table with bind parameters, then merged into the target:

1. Data is loaded into a temporary staging table
2. A MERGE statement is executed against the target table
3. The staging table is dropped after the sync completes

For Mirror mode, an additional DELETE is performed for records in the target that are not in the staging table.

This runs once per batch, so a large sync issues many MERGE statements — each one a full pass over the target table.

Staged loading (optional). When the destination is configured with a storage integration, Zeotap writes each batch as a Parquet file to a Google Cloud Storage bucket and loads the whole run into Snowflake in one operation at the end of the run. Large syncs get dramatically fewer statements and a single pass over the target table. See Staged Loading (GCS).

Staged Loading (GCS)

Staged loading is the recommended configuration for syncs that move more than a few hundred thousand rows. Instead of sending rows to Snowflake as SQL statements, Zeotap stages them as Parquet files that Snowflake bulk-loads with COPY INTO.

During a run:

1. Each batch of rows is serialized to a Parquet file and uploaded to a run-specific prefix in a Zeotap-managed GCS bucket.
2. When the last batch has been staged, Zeotap creates a temporary table in the target schema and issues one COPY INTO that reads every file for the run through your storage integration.
3. The sync mode is applied once — the same MERGE, INSERT, or DELETE the direct path uses — against that temporary table.
4. The staged files are deleted.

Because the load is terminal, a run either applies completely or not at all: if any batch fails to stage, Zeotap skips the load and leaves the target table untouched. The next run re-delivers the same data.

What you need

Snowflake never receives credentials for the bucket. The storage integration makes Snowflake read GCS as its own generated service account, and Zeotap grants that account read-only access to the one bucket.

Setup

Because the staging bucket is created with the destination, and the storage integration must name that bucket, the order matters: create the destination first, then create the integration, then edit the destination to add the two staging fields.

Step 1 — Create the Snowflake destination

Navigate to Destinations, add a Snowflake destination, and fill in the connection settings and credentials. Leave Storage Integration and GCS Service Account blank for now, and save. Saving provisions your workspace’s staging bucket.

Step 2 — Open the staged-loading instructions

Reopen the saved destination and scroll to Staged Loading (Optional). The bucket Zeotap provisioned for your workspace is shown there, along with the setup SQL below already filled in with that bucket name — copy it straight from the page.

The bucket is per workspace, not per destination: every Snowflake connection and destination in the workspace stages through the same one. If you already set up a storage integration for event staging on a Snowflake warehouse connection here, it covers this destination too — reuse it and skip to Step 5.

Step 3 — Create the storage integration in Snowflake

Run this as ACCOUNTADMIN, substituting the bucket name shown on the destination:

USE ROLE ACCOUNTADMIN;
 
CREATE STORAGE INTEGRATION IF NOT EXISTS ZEOTAP_GCS_STAGING
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://my-project-zeotap-stg-3f9a1c4b7e2d5a80/');

Note the scheme: Snowflake addresses Google Cloud Storage as gcs://, not gs://.

STORAGE_ALLOWED_LOCATIONS must name the bucket. A bucket-less ('gcs://') is not a location that allows everything: Snowflake accepts it when the integration is created, then fails every load that uses the integration with invalid URL: gcs://. If you must create the integration before the bucket name is known, use ('*') and tighten it with the ALTER below once the destination is saved — the integration’s service account does not change.

If the integration already exists, CREATE ... IF NOT EXISTS does nothing — Snowflake reports success and leaves the existing object untouched, allowed locations included. Storage integrations are account-level objects, so this is the normal case as soon as a second workspace, connection, or destination in the same Snowflake account reuses the name. Always follow the CREATE with the ALTER below, which is what actually applies the location and is safe to re-run.

Because the bucket is per workspace, an integration shared across workspaces must list every one of their buckets — SET replaces the whole list, it does not add to it:

ALTER STORAGE INTEGRATION ZEOTAP_GCS_STAGING SET
  STORAGE_ALLOWED_LOCATIONS = (
    'gcs://my-project-zeotap-stg-3f9a1c4b7e2d5a80/',
    'gcs://my-project-zeotap-stg-0d3c7ee650f96be2/'
  );

Prefer one integration per workspace when the workspaces belong to different teams or customers — give each destination its own Storage Integration name and grant USAGE only to that workspace’s role. A shared integration lets any role holding USAGE on it read every bucket in the list.

To widen or correct the allowed locations later without recreating the integration (the service account below stays the same):

ALTER STORAGE INTEGRATION ZEOTAP_GCS_STAGING SET
  STORAGE_ALLOWED_LOCATIONS = ('gcs://my-project-zeotap-stg-3f9a1c4b7e2d5a80/');

Step 4 — Read the GCS service account Snowflake generated

Snowflake creates a managed Google service account for the integration. Describe the integration and copy the STORAGE_GCP_SERVICE_ACCOUNT property value:

DESC STORAGE INTEGRATION ZEOTAP_GCS_STAGING;

property                        property_type  property_value
------------------------------  -------------  ------------------------------------------------------
ENABLED                         Boolean        true
STORAGE_ALLOWED_LOCATIONS       List           gcs://my-project-zeotap-stg-3f9a1c4b7e2d5a80/
STORAGE_GCP_SERVICE_ACCOUNT     String         service-account-id@gcpuscentral1-1dfa.iam.gserviceaccount.com

That email is what goes into the GCS Service Account field. It is generated by Snowflake and belongs to Snowflake — there is no key to download, and you never create a service account yourself. It is stable for the life of the integration.

Step 5 — Grant the sync role usage on the integration

The COPY INTO runs as the role the destination connects with (the Role field, or the user’s default role). That role needs USAGE on the integration:

GRANT USAGE ON INTEGRATION ZEOTAP_GCS_STAGING TO ROLE CDP_ROLE;

Step 6 — Add the staging fields to the destination

Back in Zeotap, open the destination and fill in the Staged Loading (Optional) section:

Click Save. On save, Zeotap grants that service account read access on the staging bucket. This grant only happens when the destination is saved with the service-account email filled in — if a sync later fails with an authorization error on COPY INTO, re-saving the destination re-applies the grant.

Step 7 — Verify

The Staged Loading (Optional) section warns you if the destination still has no staging bucket attached; no warning and both fields filled in means the next run stages. Then run the sync: staged runs apply the whole run in one load at the end rather than a MERGE per batch, so a large sync finishes markedly faster than the same sync did before. If staged loading is not active, the sync still succeeds — it falls back to direct loading (see below).

Fallback behavior

A sync never fails because staged loading is unavailable. Zeotap uses the direct INSERT path whenever:

• Storage Integration is blank, or the destination has no staging bucket attached — save the destination again to attach one; the Staged Loading (Optional) section warns when this is the case
• The staging bucket cannot be reached at the start of the run

Everything else — field mapping, sync modes, the SQL applied to your target table, and how values are cast into typed target columns — is identical on both paths. Switching staged loading on or off does not change the data that lands in Snowflake.

Notes and limits

• Sync modes. All modes are supported. For Upsert, Update, and Mirror, Zeotap first coalesces the run’s staged rows down to one net change per primary key, because Snowflake rejects a MERGE whose join matches a target row more than once. A record can legitimately change more than once inside a single run — a row created and then edited between two syncs arrives as an insert and an update — and coalescing applies the last change, exactly as the direct path does by applying the batches in order. Insert, Add, and Remove do not coalesce, matching the direct path’s behavior.
• Schema drift within a run. Files are matched by column name, so a column that first appears mid-run loads as NULL for earlier files rather than failing the load.
• Malformed files abort the run. The load uses Snowflake’s default ON_ERROR = ABORT_STATEMENT, so a run fails loudly rather than delivering a partial set of rows.
• Data residency. Staged files live in a Zeotap-managed GCS bucket for the duration of the run only. If your compliance posture forbids data transiting Google Cloud, leave staged loading off.
• One bucket per workspace. The staging bucket is shared by every warehouse connection and destination in the workspace, so a single storage integration — allowed on that one bucket — can serve your Snowflake destination and your Snowflake warehouse connection’s event staging. Each of them still needs the integration name entered on its own configuration.

Troubleshooting

Connection failed

Verify the account identifier includes the region (e.g., xy12345.us-east-1). Check that the warehouse is not suspended.

If your Snowflake account enforces a network policy, add Zeotap’s static egress IPs (34.76.7.172, 34.22.225.249) to the ALLOWED_IP_LIST. See Network Configuration for the full setup.

Insufficient privileges

The Snowflake user needs USAGE on the warehouse and database, and CREATE TABLE / INSERT / UPDATE / DELETE privileges on the target schema.

Table does not exist

Zeotap creates the target table automatically on the first sync if it does not exist. The table is created with the primary key column as VARCHAR, and the mapped columns are added as the first batch is written. Ensure the user has CREATE TABLE privileges on the target schema.

The connection test does not require the table to exist — it only checks that the target schema is reachable, and warns that the table will be created on the first sync.

Warehouse suspended

If the warehouse is set to auto-suspend, it may need to resume before queries can run. This can add latency to the first sync run.

Staged loading: sync succeeds but is still slow

Staged loading is inactive and the sync fell back to direct loading. Check, in order:

1. Storage Integration is filled in on the destination.
2. The destination has been saved since those fields were filled in — saving is what attaches the staging bucket and applies the access grant.
3. The Staged Loading (Optional) section is not showing the “staged loading is not active yet” warning. It appears when the destination has an integration configured but no staging bucket attached — a destination created before staged loading was available picks one up the next time it is saved.

Staged loading: the setup SQL ran cleanly but the sync still fails

Check what the integration actually declares before anything else:

DESC STORAGE INTEGRATION ZEOTAP_GCS_STAGING;

If STORAGE_ALLOWED_LOCATIONS does not name this destination’s bucket, the CREATE STORAGE INTEGRATION IF NOT EXISTS was a no-op against an integration that already existed — it reports success without changing anything. Apply the location with ALTER (listing every workspace bucket the integration serves, since SET replaces the list), and re-run the sync.

Staged loading: “SQL compilation error: invalid URL: gcs://”

The storage integration declares a location that is not a location. STORAGE_ALLOWED_LOCATIONS = ('gcs://') — no bucket — is accepted by CREATE STORAGE INTEGRATION and then rejected by every COPY INTO and CREATE STAGE that resolves the integration, quoting the offending entry back at you. The URL in the error is the integration’s, not the one the sync tried to read.

Check what the integration declares, and point it at the staging bucket shown on the destination:

DESC STORAGE INTEGRATION ZEOTAP_GCS_STAGING;
 
ALTER STORAGE INTEGRATION ZEOTAP_GCS_STAGING SET
  STORAGE_ALLOWED_LOCATIONS = ('gcs://my-project-zeotap-stg-3f9a1c4b7e2d5a80/');

ALTER keeps the integration’s service account, so nothing has to be re-copied into the destination and no re-save is needed. Re-run the sync.

Staged loading: “Access Denied” or “not authorized” on COPY INTO

Snowflake could not read the staged files. The usual causes:

• The service-account grant was never applied. Open the destination, confirm GCS Service Account matches the current STORAGE_GCP_SERVICE_ACCOUNT from DESC STORAGE INTEGRATION, and save. Saving re-applies the bucket grant.
• The integration was recreated. Dropping and recreating a storage integration generates a new service account. Re-copy it into the destination and save.
• The bucket is not in STORAGE_ALLOWED_LOCATIONS. Verify with DESC STORAGE INTEGRATION that the allowed location uses the gcs:// scheme and matches the bucket name exactly.

Staged loading: “SQL access control error: Insufficient privileges to operate on integration”

The role the destination connects with does not have USAGE on the storage integration:

GRANT USAGE ON INTEGRATION ZEOTAP_GCS_STAGING TO ROLE CDP_ROLE;

If the destination leaves Role blank, the grant must go to the user’s default role.

Staged loading: “Storage integration does not exist or not authorized”

The Storage Integration value does not match an integration the connecting role can see. Integration names are Snowflake identifiers and are matched uppercase unless they were created quoted — enter the name exactly as SHOW INTEGRATIONS reports it.