Snowflake
How to set-up your Dust Snowflake connection
Overview
Dust does not synchronize or store any of your Snowflake data. We only store metadata, such as databases, schemas or tables names.
You can connect Dust to your Snowflake data warehouse to enable your assistants to perform Table queries on your Snowflake tables.
Dust assistants are able to use the output from these queries to answer quantitative questions:
The assistants can also leverage the results to create visualizations, such as charts:
The "Tools inspection" button allows to view the reasoning and SQL query used by the assistant to retrieve the data:
Setting up the Connection
Key Snowflake Concepts
Let's review some essential Snowflake concepts:
- Role: A collection of permissions that can be assigned to users.
- User: An account for accessing Snowflake. In this case, we're creating a service account for programmatic access, not for logging into the Snowflake web UI.
- Warehouse: A cluster of compute resources for running queries.
- Database: A container for schemas and other objects.
- Schema: A container for tables and other database objects.
- Table: A structure that stores data in rows and columns.
Step-by-Step Guide
Setup on Snowflake's side
We recommend creating a dedicated Warehouse, User and Role for Dust.
Setup the warehouse, role and user for Dust
Create a new role
First, create a role to manage the permissions:
CREATE ROLE dust_role;
Create a new user
Now, create a new service account for Dust:
CREATE USER dust_user PASSWORD = 'strong_password' DEFAULT_ROLE = dust_role;
Replace 'strong_password' with a secure, unique password. This account is for programmatic access only, not for logging into the Snowflake web UI.
Create a new warehouse
Set up a dedicated warehouse for Dust:
CREATE WAREHOUSE dust_warehouse
WITH WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;
XSMALL is the smallest available size, suitable for many use cases. AUTO_SUSPEND is set to 300 seconds (5 minutes) to optimize costs. You can adjust these values as needed.
Grant warehouse usage to the role
Allow the role to use the warehouse:
GRANT USAGE ON WAREHOUSE dust_warehouse TO ROLE dust_role;
Grant the new role to the new user
Finally, assign the new role to the user:
GRANT ROLE dust_role TO USER dust_user;
Grant USAGE
on databases and schemas to the role
USAGE
on databases and schemas to the roleπ‘ Granting USAGE for databases and schemas does not mean granting access to the tables inside.
It simply means that the Dust role on Snowflake will be aware of the existence of these databases & schemas, which is a pre-requisite if you wish to query some of the tables they contain.
For each Snowflake database that you wish to use through Dust, you will need to grant USAGE
to the role:
GRANT USAGE ON DATABASE MY_DATABASE TO ROLE dust_role;
For each Snowflake schema that you wish to use through Dust, you will need to grant USAGE
to the role:
GRANT USAGE MY_DATABASE.MY_SCHEMA TO ROLE dust_role;
β οΈ If you want to use Table T
which is in schema S
which is itself in database D
, you will need to grant USAGE
on both database D
and schema S
Alternatively, you may decide to grant USAGE
on all schemas inside of a database:
GRANT USAGE ON ALL SCHEMAS IN DATABASE MY_DATABASE TO ROLE dust_role;
Grant SELECT
on tables to the role
SELECT
on tables to the roleπ‘ Granting SELECT
on tables is what enables the Dust Snowflake role to execute SELECT
SQL queries on the tables.
Our Snowflake integration will reject any role that has grants other than SELECT
grants (it is very important that the Snowflake connection remains read-only, in order to fully prevent Dust assistants from mutating your data)
You have the flexibility to grant access at two different levels: whole schemas, or individual tables. Choose the appropriate level based on your security requirements and Dust's needs.
a. Grant access to whole schemas
For each schema you want to give access to:
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DATABASE.MY_SCHEMA
TO ROLE dust_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_DATABASE.MY_SCHEMA
TO ROLE dust_role;
b. Grant access to specific tables
For each specific table you want to grant access to:
GRANT SELECT ON TABLE MY_DATABASE.MY_SCHEMA.MY_TABLE TO ROLE dust_role;
Setup on Dust's side
Once the Snowflake access has been setup, navigate to Dust's "Connection Admin" (in the Knowledge tab), click on "Add Connections" and select Snowflake.
Fill-in the details (using the Snowflake user you have created for Dust), and press "Connect and select tables".
Once this is done, you can finally select the tables you want to add to connect with Dust.
Updated 23 days ago