Skip to main content

Snowflake: set up a data warehouse

Introduction​

Welcome to the Snowflake on Outerbounds journey!

📈 Learning objectives​

The goal of this self-contained lesson is to configure your Outerbounds account to work with Snowflake. In this journey you will:

  • set up test databases and users in Snowflake, and
  • move data between Snowflake, Outerbounds workstations, and Metaflow tasks.

Snowflake setup​

To get started, you need to create a Snowflake user and role that can read and write to a database. You can use an existing user, role, and database if desired. Otherwise, you can expand this component and use it as a template to build the minimal components.

Minimal Snowflake setup

You can use this query as a template to work from:

-- 1. Create a role
CREATE ROLE IF NOT EXISTS <YOUR_ROLE>;

-- 2. Create a user and set default DB/schema
CREATE USER IF NOT EXISTS <YOUR_USER>
  PASSWORD = 'StrongPasswordHere1!'
  LOGIN_NAME = '<YOUR_USER>'
  MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_ROLE = <YOUR_ROLE>
  DEFAULT_WAREHOUSE = <YOUR_WAREHOUSE>
  DEFAULT_NAMESPACE = <YOUR_DB>.<YOUR_SCHEMA>
  COMMENT = 'User for Demo';

-- 3. Grant the role to the user
GRANT ROLE <YOUR_ROLE> TO USER <YOUR_USER>;

-- 4. Grant usage on a warehouse to the role
GRANT USAGE ON WAREHOUSE <YOUR_WAREHOUSE> TO ROLE <YOUR_ROLE>;

-- 5. Create a test database and schema for the user to read/write

CREATE DATABASE IF NOT EXISTS <YOUR_DB> COMMENT = 'Database for Demo';
CREATE SCHEMA IF NOT EXISTS <YOUR_DB>.<YOUR_SCHEMA> COMMENT = 'Schema for Demo';

-- 6. Grant privileges on the new database/schema to the role
GRANT ALL PRIVILEGES ON DATABASE <YOUR_DB> TO ROLE <YOUR_ROLE>;

-- 7. Grant privileges on the schema
GRANT ALL PRIVILEGES ON SCHEMA <YOUR_DB>.<YOUR_SCHEMA> TO ROLE <YOUR_ROLE>;

-- (Optional) Grant privileges on future objects in the schema 
-- so that the role automatically gets read/write privileges
-- for newly created tables or views
GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA <YOUR_DB>.<YOUR_SCHEMA> TO ROLE <YOUR_ROLE>;
GRANT ALL PRIVILEGES ON FUTURE VIEWS IN SCHEMA <YOUR_DB>.<YOUR_SCHEMA> TO ROLE <YOUR_ROLE>;

Outerbounds and Snowflake integration​

If you are an Outerbounds admin, configure your Snowflake resource integration. If you are not an admin, you can still follow by manually setting your key, or ask an admin to create the resource integration for you. The form will include another query to run on Snowflake to establish the security integration.

Download the content to your workstation​

Once your web IDE opens, open a terminal and run the outerbounds tutorials pull --url https://outerbounds-journeys-content.s3.us-west-2.amazonaws.com/main/journeys.tar.gz --destination-dir ~/learn command from the previous step. This will download the content to your workstation. Click Next once you see Tutorials pulled successfully.

Validate setup​

Open the 00-setup notebook and ensure you can access the role from an Outerbounds workstation.

Move data between a notebook and Snowflake​

Open the 01-nb notebook to learn how to create a table in Snowflake and move data between the notebook and Snowflake.

Move data between a workflow and Snowflake​

Open the 02-flow file to learn how to create a table in Snowflake and move data between a Metaflow workflow and Snowflake.

Next steps​

You have completed the primary steps of this journey, showing how you can use Snowflake features from Outerbounds. With these skills, there are many more patterns and integrations you can build on with Snowflake and Outerbounds - you can find more information in this article.