Snowflake

📘

This article is for data engineers

It walks through giving Attribution access to a Snowflake database so the ETL service can load your export into it.

Complete a data export to Amazon S3, Azure Blob Storage or Google Cloud Storage first; the ETL service for Snowflake loads from that storage.

Create a database, role and user for Attribution

Review and run:

CREATE DATABASE attribution;
CREATE ROLE attribution_etl_role;
CREATE USER attribution;
GRANT ROLE attribution_etl_role TO USER attribution;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE attribution_etl_role; -- 👈 replace COMPUTE_WH with your warehouse name if needed
GRANT OWNERSHIP ON DATABASE attribution TO ROLE attribution_etl_role;
GRANT OWNERSHIP ON SCHEMA attribution.public TO ROLE attribution_etl_role;

Set Attribution's public key on the user

Attribution connects with key-pair authentication. Assign Attribution's public key to the user:

ALTER USER attribution SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAvYccwUpgH/fhB5olKgLoBX6YkEp2k7TirVMxZsaAfJRyEJz/J2VZIsW6AnvoZMir1uoo3O1piLC546Pbs6kjMVCV/vUpUQDmXesKXTKrZnHzZW4d/N6UZ2o2jdYfWOmfz+4N9f3pfAvgSdkH+UXMCAfi4TlSyNXp66tLHZ2tN2PIDabXGIQMEAUpbwgVF5N0QfheRol8THnUrJuEdw2smiEXLDqYKo9nCA66df5vggzVi3SLCH4+yiGRVPASD3pp+7Q2GBKXRdUDDPPiqLKAIPJJRNlKO1fNpZ4j1tUdU2J5INmVFjJG14bcpU6gS7rl5R83wJHutmhNhFHNVt6LwwIDAQAB';

🚧

No static IP address to allow

Attribution has no fixed IP address. Each run connects from a different, one-off host, so a Snowflake network policy cannot be narrowed to an Attribution address. Access rests on the key pair above and on the role's grants.

Give Snowflake access to the export storage

Snowflake reads the export files straight from your cloud storage, through an external stage the ETL service creates. It needs credentials for that storage, entered in the Attribution settings:

• Amazon S3: the bucket URL and the access key ID and secret of a separate AWS user that can read the bucket; see Snowflake's guide.
• Azure Blob Storage: the Blob URL and a SAS token with the permissions Snowflake describes.
• Google Cloud Storage: the service account key file you created for the export; see Snowflake's guide.

Alternatively, create a storage integration in Snowflake. Then Snowflake reaches the storage directly and no storage credentials are shared with Attribution. Grant the role access to it, and enter the integration's name in the Attribution settings instead of credentials:

GRANT USAGE ON INTEGRATION attribution TO ROLE attribution_etl_role; -- 👈 attribution is the integration's name here; use your own

Find the ODBC connection string

In Snowflake:

1. Click the account icon at the bottom left.
2. Select Connect a tool to Snowflake.
3. Open Connectors/Drivers and select ODBC Connection string.
4. Choose the database ATTRIBUTION.PUBLIC and the connection method password.

If you followed the steps above exactly, the copied string works as it is. If you use a custom schema, warehouse or role, edit it to match; the string must be a single line when you paste it into Attribution. A sample, broken across lines for reading:

DRIVER=SnowflakeDSIIDriver;
Locale=en-US;
SERVER=account.us-east-1.aws.snowflakecomputing.com;
PORT=443;
ACCOUNT=account.us-east-1.aws;
DATABASE=ATTRIBUTION;
SCHEMA=PUBLIC;
WAREHOUSE=ATTRIBUTIONAPP_WH;
SSL=on;
QUERY_TIMEOUT=270;
UID=attribution;
ROLE=ATTRIBUTION_ETL_ROLE;
AUTHENTICATOR=SNOWFLAKE_JWT

Do not include PRIV_KEY_FILE or PRIV_KEY_FILE_PWD; the key is handled by Attribution. The parameters are described in Snowflake's ODBC documentation.

Set up the ETL in Attribution

Open Settings → Data Export, choose Snowflake and enter:

1. The bucket or container your export lands in, as yourbucket, s3://yourbucket or s3://yourbucket/path.
2. The storage credentials from the section above: AWS key ID and secret, or the Azure SAS token, or the name of your storage integration.
3. The ODBC connection string, as a single line.
4. A private key, only if you set your own key on the user instead of Attribution's public key above.

Save, and the first load runs after the next daily export.

What a run does

Once a day the ETL service:

• Connects to your Snowflake account.
• Creates the tables of the schema if they are not there yet.
• Creates an external stage pointing at the export in your cloud storage.
• Truncates the tables that are replaced in full and loads them from the stage.
• Merges the day's changes into the tables that are updated in place.
• Runs a set of checks that the data loaded correctly, and writes a log record.

After that the data is ready to use as it is; build your reports and views on top of it. Data schema describes every table.

🚧

Snowpipe is not used

Snowpipe is designed for append-only data, and Attribution data is not append-only: rows loaded once can be updated or deleted later. The ETL service therefore loads through stages and MERGE statements. If you load the export yourself, Snowpipe can work, but you must make sure that only the current version of each row is read, for example with views or a version tag per row. We do not recommend it unless you have done this before.