Search

Claravine + Snowflake Integration


Integrate Claravine with Snowflake

Use our Snowflake outbound connector to export data from The Data Standards Cloud into a table in a Snowflake database to apply data standards within the Snowflake Marketing Data Cloud.

How you can use the Snowflake outbound connector:

  • If you're building exception tables in Snowflake, this can serve as a user interface for teams to manage Snowflake dimension tables.
  • If you're working across the content supply chain, you can use this to connect creative metadata from a DAM to media campaigns for richer analytics in Snowflake.
  • If you're managing a migration from Datorama or a similar system, you can recreate harmonization rules with the Split and Extract feature and template definition.
  • If you have image and video assets, Content Comprehension powered by Claravine AI can enrich these with additional content and creative metadata for use in analysis in a Snowflake table.

The key benefits of this connector include:

  • Makes data available faster in Snowflake: The direct connector (compared to connecting via S3) removes a step and bypasses needing to work with a data engineering team. This reduces the time it takes to evaluate performance and ROI and make necessary changes.
  • Improves marketing metadata capabilities: With a single source of truth for all metadata, you can minimize time and resources cleaning up data so your data teams can spend more time on insights and results and less time fixing and re-processing data.
  • Provides Snowflake dimension table management: If you're building exception tables in Snowflake, Claravine provides a user interface for teams to manage this data.
  • Proves business value of marketing campaigns: Gain campaign intelligence by breaking down data silos and unifying advertising and marketing data fast in a single, governed, and scalable platform.

Begin by generating a key pair, then run the setup script in Snowflake.

Generate a key pair

Claravine connects to Snowflake with key pair authentication. Run the following commands in a terminal to generate an encrypted private key and a matching public key.

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

The first command prompts you to create a passphrase. Save it, because you will need it to set up the connector in Claravine. The second command prompts for the same passphrase.

These commands create two files:

  • rsa_key.p8 (private key): You will enter this in Claravine. Store it securely and do not share it.
  • rsa_key.pub (public key): You will add this to the Snowflake user in the setup script below.

Run the setup script

A dedicated warehouse, database, and schema are required to house data from The Data Standards Cloud in your Snowflake account. If you don’t already have these configured, you must run a script to create them. Run the following script in a SQL Worksheet in your Snowflake account.

Notes:

  • The values for the warehouse, database, schema, and role can be customized if needed.
  • Replace the public key placeholder in the script with the contents of your rsa_key.pub file. Do not include the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines.
-- set variables (these need to be uppercase)
set claravine_role = 'CLARAVINE_ROLE';
set claravine_username = 'CLARAVINE_USER';
set claravine_warehouse = 'CLARAVINE_WAREHOUSE';
set claravine_database = 'CLARAVINE_DATABASE';
set claravine_schema = 'CLARAVINE_SCHEMA';

-- set user public key (contents of rsa_key.pub, without the BEGIN/END lines)
set claravine_public_key = 'PASTE_PUBLIC_KEY_HERE';

begin;

-- create claravine role
use role securityadmin;
create role if not exists identifier($claravine_role);
grant role identifier($claravine_role) to role SYSADMIN;

-- create claravine user
create user if not exists identifier($claravine_username)
rsa_public_key = $claravine_public_key
default_role = $claravine_role
default_warehouse = $claravine_warehouse;

grant role identifier($claravine_role) to user identifier($claravine_username);

-- change role to sysadmin for warehouse / database steps
use role sysadmin;

-- create claravine warehouse
create warehouse if not exists identifier($claravine_warehouse)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- create claravine database
create database if not exists identifier($claravine_database);

-- grant claravine warehouse access
grant USAGE
on warehouse identifier($claravine_warehouse)
to role identifier($claravine_role);

-- grant claravine database access
grant OWNERSHIP
on database identifier($claravine_database)
to role identifier($claravine_role);

commit;

begin;

USE DATABASE identifier($claravine_database);

-- create schema for claravine data
CREATE SCHEMA IF NOT EXISTS identifier($claravine_schema);

commit;

begin;

-- grant claravine schema access
grant OWNERSHIP
on schema identifier($claravine_schema)
to role identifier($claravine_role);

commit;

Set Up the Snowflake Outbound Connector

  1. To configure the Snowflake integration in your Claravine account, navigate to Settings > Integrations > Accounts.
  2. Click Add connector, and select Snowflake in the Select connector type window,
  3. Select Key Pair Authentication.
  4. Enter the Role, Warehouse, Database, and Schema you created in the setup script.

  5. Enter your Snowflake locator URL and Username. The URL must start with https:// and end in .snowflakecomputing.com, for example https://xy12345.us-east-1.snowflakecomputing.com. Enter it in lowercase, with no trailing slash. Snowflake Locator URL format is based on the region in which your Snowflake account is hosted.

     Refer to the Snowflake documentation to determine the correct locator URL to use.

  6. In Private Key, paste the full contents of your rsa_key.p8 file, including the BEGIN and END lines.
  7. In Passphrase, enter the passphrase you created when you generated your key pair.
    Claravine stores the private key and passphrase securely and never displays them after you save. Entering a new value later replaces the existing one.
  8. Click Save connector.

     

Configure the Snowflake Outbound Integration

  1. Add the Snowflake outbound integration as an outbound connector in the template configuration Connect step. In outbound connectors, select the Snowflake tile.
  2. Enter an Integration Name.
  3. Select the Snowflake Account (created in the section above).
  4. Select the Template fields to be delivered to Snowflake.
    Outbound data selection is similar to Email Send configuration. Select the fields to be delivered to your Snowflake table.

Data will automatically deliver to Snowflake each time a submission is completed.

 

Watch a Video

Watch a live outbound integration demo: https://www.claravine.com/snowflake-webinar-series/

Updated

Comments

0 comments

Article is closed for comments.