Data Export to Snowflake: setup step by step
How to create the role, key pair and external stage AdShift needs to COPY exported partitions into your Snowflake account.
Snowflake receives exported partitions as COPY INTO loads. The files themselves sit in AdShift's staging bucket; you create an external stage over it, and we copy from that stage into tables in your schema.
That arrangement is deliberate. You own the stage, so you can drop it and the loading stops, without anyone touching a credential.
This article is the setup walkthrough. For what gets exported and on what schedule, see Data Export.
What you need
- A Snowflake account where you can create a role, a user and a stage (
ACCOUNTADMIN, orSECURITYADMINplusSYSADMIN). - AdShift's staging bucket URL and the storage integration details for your environment. Support provides them — they differ between our regions.
opensslon your machine, for the key pair.- About fifteen minutes.
1. Create a key pair
Snowflake is retiring single-factor password authentication for programmatic access, and AdShift only supports key-pair auth. Generate an encrypted key:
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes-256-cbc -inform PEM -out adshift_key.p8
openssl rsa -in adshift_key.p8 -pubout -out adshift_key.pubYou will be asked for a passphrase. Keep it — you paste it into the connection alongside the key.
An unencrypted key also works (-nocrypt instead of -v2 aes-256-cbc), in which case leave the passphrase field empty.
2. Create the role, user and schema
Run this as ACCOUNTADMIN, substituting your warehouse and the public key from step 1 (the base64 body only, without the -----BEGIN PUBLIC KEY----- lines):
CREATE DATABASE IF NOT EXISTS ADSHIFT;
CREATE SCHEMA IF NOT EXISTS ADSHIFT.EXPORT;
CREATE ROLE IF NOT EXISTS ADSHIFT_EXPORT_ROLE;
GRANT USAGE ON WAREHOUSE MY_WAREHOUSE TO ROLE ADSHIFT_EXPORT_ROLE;
GRANT USAGE ON DATABASE ADSHIFT TO ROLE ADSHIFT_EXPORT_ROLE;
GRANT USAGE, CREATE TABLE, CREATE STAGE ON SCHEMA ADSHIFT.EXPORT
TO ROLE ADSHIFT_EXPORT_ROLE;
CREATE USER ADSHIFT_EXPORT_USER
DEFAULT_ROLE = ADSHIFT_EXPORT_ROLE
DEFAULT_WAREHOUSE = MY_WAREHOUSE
RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';
GRANT ROLE ADSHIFT_EXPORT_ROLE TO USER ADSHIFT_EXPORT_USER;CREATE TABLE is needed because each exported dataset becomes its own table, created on first delivery. The connection test proves the grant by creating a temporary table and letting it fall away with the session.
Use a dedicated schema. Exported tables are created and loaded automatically, and mixing them with tables your own jobs write makes the two hard to tell apart.
3. Create the external stage
The stage points at AdShift's staging bucket. Ask support for the bucket URL and the storage integration values for your environment, then:
USE SCHEMA ADSHIFT.EXPORT;
CREATE STAGE ADSHIFT_EXPORT_STAGE
URL = 's3://adshift-data-export-staging-prod/'
STORAGE_INTEGRATION = ADSHIFT_EXPORT_INTEGRATION;
GRANT USAGE ON STAGE ADSHIFT_EXPORT_STAGE TO ROLE ADSHIFT_EXPORT_ROLE;Verify it before going further — an empty listing is success:
LIST @ADSHIFT.EXPORT.ADSHIFT_EXPORT_STAGE;A 403 means the storage integration is not yet trusted on our side; contact support with the integration name before continuing.
Note the stage name. You enter it in the connection, and a connection without a working stage saves fine but cannot deliver.
4. Connect it in AdShift
Go to Data Export → Connections → New connection, pick Snowflake and fill in:
| Field | What to enter |
|---|---|
| Connection name | Lowercase letters, digits and hyphens, e.g. snowflake-analytics |
| Snowflake account | Your account identifier, e.g. ab12345.eu-central-1 |
| User | ADSHIFT_EXPORT_USER |
| Role | ADSHIFT_EXPORT_ROLE |
| Warehouse | The warehouse you granted usage on |
| Database / Schema | ADSHIFT / EXPORT |
| External stage name | ADSHIFT_EXPORT_STAGE |
| Private key (PEM) | The contents of adshift_key.p8, whole file including the -----BEGIN… lines |
| Private key passphrase | From step 1, or empty for an unencrypted key |
The key is stored encrypted and is never shown back to you; the connection form displays [redacted] and leaving the field blank on an edit keeps the stored value.
Click Test connection. Three checks run: the key pair authenticates, SELECT 1 succeeds (which also resumes a suspended warehouse), and a temporary table can be created in the schema. A key problem is reported separately from a grant problem, so a paste error does not send you looking through SHOW GRANTS.
Then click Save, open Reports on the connection, and pick your datasets.
What you get in Snowflake
One table per exported dataset, named <connection>_<dataset> in upper case, with hyphens in the connection name translated to underscores. A connection called snowflake-analytics exporting clicks produces ADSHIFT.EXPORT.SNOWFLAKE_ANALYTICS_CLICKS.
Columns are matched by name, case-insensitively, so a column added to a dataset later lands in the right place once you add it to the table.
Loads use Snowflake's load metadata, which skips files it has already ingested for a table. An Airflow retry after a partial load resumes rather than duplicating rows.
Re-deliveries append. When late data reopens an hour, AdShift recomputes it and delivers it again — for bucket destinations that is a new version folder, but a COPY INTO has no equivalent. Deduplicate on read, keeping the latest row per key, or delete the affected partition before re-loading. The delivery history marks re-deliveries with a version badge so you can see when it happened.
If something goes wrong
The key-pair authentication check fails — the PEM was truncated on paste, the passphrase is wrong, or the public key registered on the user does not match the private key you pasted. Confirm with DESC USER ADSHIFT_EXPORT_USER and compare the RSA_PUBLIC_KEY_FP fingerprint.
SELECT 1 fails with "Role not found" or "Warehouse not found" — the role exists but is not granted to the user, or the user cannot use the warehouse. Both are GRANT statements from step 2.
The schema write access check fails — CREATE TABLE is missing on the schema. It is granted on the schema, not the database.
Deliveries fail with "Stage not found" — the stage name in the connection does not match, or USAGE ON STAGE was not granted to the export role.
Tables are created but stay empty — check the report's filters, and look for Skipped runs in the delivery history, which mean the partition had no rows after filtering.
Revoking access
Disable or delete the connection in AdShift first, then drop the stage, the user and the role. Dropping the stage alone is enough to stop loading immediately, which makes it the fastest thing to reach for. Tables already loaded are in your account and are unaffected.
See also
- Data Export — datasets, schedule, delivery history
- Data Export to BigQuery — the other warehouse destination
Data Export to Google Cloud Storage: permissions step by step
How to prepare a GCS bucket and issue a service-account key scoped to it, so exported partitions land in your own storage.
Data Export: reports and schedules
What each Data Export report contains, when it arrives, and how to choose between raw, aggregated, cohort, partner, cost, subscription and fraud datasets.