Setting up Cargo on Snowflake


Cover image

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!