Snowflake
Install dlt
with Snowflakeโ
To install the dlt
library with Snowflake dependencies, run:
pip install "dlt[snowflake]"
Setup guideโ
1. Initialize a project with a pipeline that loads to Snowflake by running:
dlt init chess snowflake
2. Install the necessary dependencies for Snowflake by running:
pip install -r requirements.txt
This will install dlt
with the snowflake
extra, which contains the Snowflake Python dbapi client.
3. Create a new database, user, and give dlt
access.
Read the next chapter below.
4. Enter your credentials into .dlt/secrets.toml
.
It should now look like this:
[destination.snowflake.credentials]
database = "dlt_data"
password = "<password>"
username = "loader"
host = "kgiotue-wn98412"
warehouse = "COMPUTE_WH"
role = "DLT_LOADER_ROLE"
In the case of Snowflake, the host is your Account Identifier. You can get it in Admin/Accounts by copying the account URL: https://kgiotue-wn98412.snowflakecomputing.com and extracting the host name (kgiotue-wn98412).
The warehouse and role are optional if you assign defaults to your user. In the example below, we do not do that, so we set them explicitly.
Set up the database user and permissionsโ
The instructions below assume that you use the default account setup that you get after creating a Snowflake account. You should have a default warehouse named COMPUTE_WH and a Snowflake account. Below, we create a new database, user, and assign permissions. The permissions are very generous. A more experienced user can easily reduce dlt
permissions to just one schema in the database.
-- create database with standard settings
CREATE DATABASE dlt_data;
-- create new user - set your password here
CREATE USER loader WITH PASSWORD='<password>';
-- we assign all permissions to a role
CREATE ROLE DLT_LOADER_ROLE;
GRANT ROLE DLT_LOADER_ROLE TO USER loader;
-- give database access to new role
GRANT USAGE ON DATABASE dlt_data TO DLT_LOADER_ROLE;
-- allow `dlt` to create new schemas
GRANT CREATE SCHEMA ON DATABASE dlt_data TO ROLE DLT_LOADER_ROLE;
-- allow access to a warehouse named COMPUTE_WH
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO DLT_LOADER_ROLE;
-- grant access to all future schemas and tables in the database
GRANT ALL PRIVILEGES ON FUTURE SCHEMAS IN DATABASE dlt_data TO DLT_LOADER_ROLE;
GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE dlt_data TO DLT_LOADER_ROLE;
Now you can use the user named LOADER
to access the database DLT_DATA
and log in with the specified password.
You can also decrease the suspend time for your warehouse to 1 minute (Admin/Warehouses in Snowflake UI).