Setting up Cargo on Snowflake

In this tutorial, we will guide you through setting up your Snowflake as a store of records in Cargo. This setup ensures Cargo has the necessary permissions in Snowflake to read and write data efficiently.

Overview

Permissions

What Cargo can do

Read data from schemas and tables, even if they are spread across multiple databases
Write data into new schemas and tables

What Cargo will never do

Overwrite existing schemas and tables (Cargo always creates its own schemas and tables when needed)

Step 1

Create a dedicated database

Create a dedicated database for Cargo where all data managed by Cargo will be stored.

Create a database called "cargo_db"

CREATE DATABASE cargo_db;

Step 2

Create a user for Cargo

Grant the necessary permissions for Cargo to run commands as an authenticated user on the database you just created.

Create a role for Cargo

CREATE ROLE cargo_role;

Grant the cargo database you created above to cargo role

GRANT ALL ON DATABASE cargo_db TO ROLE cargo_role;

Create a warehouse for Cargo

CREATE WAREHOUSE cargo_wh WITH WAREHOUSE_SIZE='XSMALL', WAREHOUSE_TYPE='STANDARD', AUTO_SUSPEND=60, AUTO_RESUME=true;

Grant warehouse to cargo role

GRANT USAGE on WAREHOUSE cargo_wh to role cargo_role;

Create a user for Cargo

CREATE USER cargo_user PASSWORD='abc123' DEFAULT_ROLE=cargo_role DEFAULT_WAREHOUSE=cargo_wh MUST_CHANGE_PASSWORD=false;

Grant role to user

GRANT ROLE cargo_role TO USER cargo_user;

Grant access to all schema tables - Cargo user may need to access data outside the "cargo_db" database

GRANT SELECT ON ALL TABLES IN SCHEMA cargo_db.public TO cargo_user;

Step 3

Check granted privileges

Make sure the Cargo user has access to the following permissions on cargo_db: OWNERSHIP, MODIFY, MONITOR, USAGE, CREATE SCHEMA.

Show granted privileges on cargo_db

SHOW GRANTS ON DATABASE cargo_db;

Step 4

Set up for GCP and Azure

For Snowflake accounts hosted on GCP and Azure, make sure you provide an RSA private key while creating your account.

Generate a private key

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

Generate a public key

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

Assign the public key to your Snowflake user

ALTER USER cargo_user SET RSA_PUBLIC_KEY='YOUR_PUBLIC_KEY';

Step 5

Allowed IP addresses

If you're using Snowflake's Allowed IPs network policy, you'll need to add the Cargo IP addresses to your list. Please contact aurelien@getcargo.io to have this configured for you.

Outcome

Setup completed

You are now ready to use Cargo with Snowflake!