Snowflake

Snowflake is a managed cloud Data Warehousing platform. It separates compute and storage resources, providing a managed data infrastructure that automatically scales based on needs.

Snowflake is cross-cloud compatible. Meaning you can choose to host on any of the cloud providers such as AWS, GCP, and Microsoft Azure.

In addition to its role as a destination, Snowflake can also be used as an ELT source. You can connect to any Snowflake account and sync tables from one or more databases directly into Weld.

Features

🔧 Setup Guide

For Weld to be able to connect to Snowflake, you need a user with the right credentials.

New to Snowflake? No worries, in order to use Weld for Snowflake, you just need to know the basics. Here is a good place to start: https://docs.snowflake.com/en/user-guide-getting-started.html

To facilitate this, you need to create a Snowflake user for Weld, as well as a default role for that user. This role needs read access to whichever databases, schemas and tables you want Weld to sync. It is important that you create a new user, and a new role for this, and only attach the required privileges to that role. This ensures that Weld can only access what is required, and helps secure your Snowflake account.

Key-Pair Authentication

Generate Key Pair

1. Generate Private Key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes256 -inform PEM -out rsa_key.p8

2. Generate Public Key from Private Key:

   openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
   

   CopyCopied!

3. Store the keys securely and ensure the private key is not shared publicly.

Assign Public Key to Snowflake User

1. Log in to Snowflake as an account administrator with SYSADMIN or SECURITYADMIN role.

2. Run the following SQL command in a Snowflake worksheet to assign the public key to the Weld user:

   ALTER USER WELD_USER SET RSA_PUBLIC_KEY='<PUBLIC_KEY>';
   

   CopyCopied!

   Replace <PUBLIC_KEY> with the key content of the rsa_key.pub file.

Grant Read Access

The default role of the Weld user needs USAGE on the warehouse, as well as USAGE and SELECT privileges on the databases, schemas and tables you want to sync from.

-- change role to securityadmin for user / role steps
use role securityadmin;

-- create role for weld
create role if not exists WELD_SOURCE_ROLE;

-- create user for weld
create user if not exists WELD_SOURCE_USER
  default_role = WELD_SOURCE_ROLE
  type = 'SERVICE';

grant role WELD_SOURCE_ROLE to user WELD_SOURCE_USER;

-- change role to a role that can grant privileges on the databases you want to sync
use role sysadmin;

grant usage on warehouse COMPUTE_WH to role WELD_SOURCE_ROLE;

grant usage on database SOURCE_DATABASE to role WELD_SOURCE_ROLE;
grant usage on all schemas in database SOURCE_DATABASE to role WELD_SOURCE_ROLE;
grant select on all tables in database SOURCE_DATABASE to role WELD_SOURCE_ROLE;

-- ensure future tables are also readable
grant select on future tables in database SOURCE_DATABASE to role WELD_SOURCE_ROLE;

Repeat the grant statements for every database you want Weld to be able to discover and sync from.

Step 1 - Connector Configuration

During setup, Weld discovers the databases your Snowflake user can access and presents them in a multi-select dropdown. Provide the following fields:

• Name (The name you want to refer to regarding this connection in the Weld app)
• Account (see Where's my credentials? below)
• User name
• Warehouse
• Key-pair authentication (private key file and passphrase, if encrypted)
• Databases (select one or more databases to make available for syncing)

All tables from the selected databases become available as individual source streams, identified as database.schema.table.

Step 2 - Data To Sync

1. Select the tables you wish to include in the sync.
   You can view the schema, remove columns or hash sensitive information.
2. Update Sync mode, which can be set to:
   • Incremental Sync
   • Full Sync
   • Append Only

Incremental Sync

Incremental syncs are supported via a configurable pointer column (for example, an updated_at timestamp or an auto-incrementing id). Weld reads WHERE <pointer> >= <last_pointer> and records the new MAX(<pointer>) after each run so only changed rows are fetched on subsequent syncs.

Full Sync

Each sync fully replaces the destination table with the current contents of the source table. This ensures deleted rows are reflected in the destination, at the cost of higher compute usage on larger tables.

Append Only

Every row returned by the query is appended to the destination table rather than merged. This is useful for event or log style tables where rows are never updated.

Step 3 - Configure Sync

1. Select how often you would like the data to sync.
2. Provide a unique destination table name.

Weld will take over from here and commence syncing data from your Snowflake account.

Where's my credentials?

Account

Can be found in the Account URL in the following format:
https://{account_identifier}.snowflakecomputing.com/

So in case of this URL:
https://ab12345.eu-central-1.snowflakecomputing.com/

You need to provide the following account identifier:
ab12345.eu-central-1

The easiest way to find your Account URL is by going to the login page of Snowflake, where it asks for your Username and Password. The URL of the page will be something like this:
https://{account_identifier}.snowflakecomputing.com/...

If you are already logged in to Snowflake (Snowsight UI), hover over your account in the list of the logged in accounts in bottom left corner, then click on the small link icon with the Copy account URL tooltip text.

If you are already logged in to the Classic Console, you should find the account url in the browser URL bar.

Warehouse
Any warehouse the Weld user's role has been granted USAGE on, for example COMPUTE_WH.

Key-pair authentication:
Select your previously generated private key file rsa_key.p8 and enter the passphrase if you encrypted your private key.

Something we overlooked?

We are the happiest when you reach out with any comments or questions. It helps us stay in the loop and make the documentation and the product better. Don't hesitate to reach out!