Skip to main content

Snowflake

Snowflake is an enterprise-ready cloud Data Warehouse. As a Meroxa destination, you can capture CDC events from any source and populate your warehouse in realtime.

To add a Snowflake resource to Meroxa, you need to perform the following steps:

Create New Snowflake User

Meroxa uses Key Pair Authentication to access your Snowflake account. The following steps guide you through creating a private key, public key, a meroxa_user user, and associating the public key appropriately.

  1. Generate a private key:
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

For more details, see: Snowflake Docs: Generate the Private Key

  1. Generate a public key:
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

For more details, see: Snowflake Docs: Generate the Private Key

  1. Create a new user within Snowflake with the username meroxa_user.

  2. Using a Snowflake Worksheet, assign the public key to the user:

-- Set public key for authentication to user
ALTER USER meroxa_user SET RSA_PUBLIC_KEY = "$SNOWFLAKE_PUBLIC_KEY"

$SNOWFLAKE_PUBLIC_KEY is the public key you created in step #2.

When adding the public key, exclude the public key delimiters (-----BEGIN PRIVATE KEY|-----END PRIVATE KEY) in the SQL statement. For example:

ALTER USER meroxa_user SET RSA_PUBLIC_KEY ='MIIBIjANBgkqh...';

Create and Configure Snowflake Database

Next, using Snowflake Worksheet, we can run the following commands:

-- Use a role that can create databases
USE ROLE accountadmin;
-- Create new database
CREATE DATABASE meroxa_db;
USE meroxa_db;
CREATE SCHEMA stream_data;
-- Create a Snowflake role with the privileges to work with the connector.
CREATE ROLE meroxa_platform_role;
-- Grant privileges on the database.
GRANT USAGE ON DATABASE meroxa_db TO ROLE meroxa_platform_role;
GRANT USAGE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
GRANT CREATE TABLE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
-- Grant access to stage data
GRANT CREATE STAGE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
GRANT CREATE PIPE ON SCHEMA stream_data TO ROLE meroxa_platform_role;
-- Grant the custom role to an existing user.
GRANT ROLE meroxa_platform_role TO USER meroxa_user;
ALTER USER meroxa_user SET DEFAULT_ROLE = meroxa_platform_role;

The above does the following:

  • Creates a new Snowflake Database called meroxa_db.
  • Creates a new Snowflake Schema called stream_data.
  • Creates a new Snowflake Role called meroxa_platform_role.
  • Grants privileges to the above for meroxa_platform_role.
  • Set's the role of meroxa_user to be meroxa_platform_role.

Add to Catalog

To add a Snowflake resource to your Meroxa Resource Catalog, you can run the following command:

meroxa resource add snowflake --type snowflakedb --url snowflake://$SNOWFLAKE_URL/meroxa_db/stream_data --username meroxa_user --password "$SNOWFLAKE_PRIVATE_KEY"

snowflake is a human-friendly name to represent the resource within Meroxa. Feel free to change as desired.

In the command above, replace the following variables with valid credentials from your Postgres environment:

When adding the private key, exclude the public key delimiters (-----BEGIN PRIVATE KEY|-----END PRIVATE KEY) in the SQL statement. For example:

meroxa resource add sfdb --type snowflakedb --url snowflake://$SNOWFLAKE_URL/meroxa_db/stream_data --username meroxa_user --password "MIOEvQ..."

Destination Configuration

To configure Snowflake as a destination:

meroxa connector create to-snowflake --to snowflake --input $STREAM_NAME

The command above creates a new connector called to-snowflake, sets the destination to a resource named snowflake, and configures the Input Stream .