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 userALTER 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 databasesUSE 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_plaform_role;-- Grant privileges on the database.GRANT USAGE ON DATABASE meroxa_db TO ROLE meroxa_plaform_role;GRANT USAGE ON SCHEMA stream_data TO ROLE meroxa_plaform_role;GRANT CREATE TABLE ON SCHEMA stream_data TO ROLE meroxa_plaform_role;-- Grant access to stage dataGRANT CREATE STAGE ON SCHEMA stream_data TO ROLE meroxa_plaform_role;GRANT CREATE PIPE ON SCHEMA stream_data TO ROLE meroxa_plaform_role;-- Grant the custom role to an existing user.GRANT ROLE meroxa_plaform_role TO USER meroxa_user;ALTER USER meroxa_user SET DEFAULT_ROLE = meroxa_plaform_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_plaform_role.
  • Grants privileges to the above for meroxa_plaform_role.
  • Set's the role of meroxa_user to be meroxa_plaform_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:

\nmeroxa 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 .