Skip to content

Snowflake

Murakumo reads Snowflake's catalogue (murakumo import database), generates for it (murakumo generate --warehouse snowflake), and builds and tests the generated project on it (murakumo verify --warehouse snowflake). Everything signs in with a key pair, so no password is stored anywhere.

One-time setup

You need a Snowflake account where you can use ACCOUNTADMIN, for example a 30-day trial.

1. Create a key pair on your machine

mkdir -p ~/.snowflake && chmod 700 ~/.snowflake
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out ~/.snowflake/murakumo_rsa_key.p8 -nocrypt
chmod 600 ~/.snowflake/murakumo_rsa_key.p8
openssl rsa -in ~/.snowflake/murakumo_rsa_key.p8 -pubout -out ~/.snowflake/murakumo_rsa_key.pub
grep -v "PUBLIC KEY" ~/.snowflake/murakumo_rsa_key.pub | tr -d '\n'; echo   # the public key, for step 2

The .p8 file is the private key: it never leaves your machine and is never committed. For production, encrypt it (drop -nocrypt) or keep it in a secrets manager.

2. Create a role, warehouse, database and service user

Run as ACCOUNTADMIN in a Snowsight worksheet, with your public key pasted in:

use role accountadmin;

-- A spending cap: suspend at 20 credits.
create resource monitor if not exists murakumo_trial
  with credit_quota = 20
  triggers on 75 percent do notify
           on 100 percent do suspend;

create warehouse if not exists transforming
  warehouse_size = xsmall auto_suspend = 60 auto_resume = true initially_suspended = true
  resource_monitor = murakumo_trial;

create role if not exists transformer;
create database if not exists commerce;
grant usage on warehouse transforming to role transformer;
grant usage, create schema on database commerce to role transformer;
grant role transformer to role sysadmin;

-- Optional: Snowflake's TPC-H sample data, to test with real volumes.
grant imported privileges on database snowflake_sample_data to role transformer;

create user if not exists murakumo_dbt
  type = service
  default_role = transformer
  default_warehouse = transforming
  rsa_public_key = 'PASTE_PUBLIC_KEY_HERE';
grant role transformer to user murakumo_dbt;

If snowflake_sample_data does not exist, run create database snowflake_sample_data from share sfc_samples.sample_data; first.

3. Set the connection variables

Your account identifier is in Snowsight under your name, then your account, then View account details (it looks like ORGNAME-ACCOUNT).

export SNOWFLAKE_ACCOUNT="ORGNAME-ACCOUNT"
export SNOWFLAKE_USER="MURAKUMO_DBT"
export SNOWFLAKE_PRIVATE_KEY_PATH="$HOME/.snowflake/murakumo_rsa_key.p8"
export SNOWFLAKE_DATABASE="COMMERCE"        # optional; see below

Put them in ~/.zshrc (or your shell's profile) to keep them. None of them is secret.

Optional: SNOWFLAKE_WAREHOUSE (default TRANSFORMING), SNOWFLAKE_ROLE (default TRANSFORMER), and SNOWFLAKE_PASSWORD if you must use a password instead of a key.

Install the Snowflake extras: pip install "murakumo[snowflake,verify]" dbt-snowflake.

Import

murakumo import database snowflake:///SALES_DB --source sales --schema public
murakumo import database snowflake://ORGNAME-ACCOUNT/SALES_DB --source sales

snowflake:///DATABASE (three slashes) takes the account from SNOWFLAKE_ACCOUNT. Columns, types (NUMBER(38,0) becomes bigint), NOT NULL, comments, and primary and foreign keys from SHOW PRIMARY KEYS and SHOW IMPORTED KEYS all carry across, composite keys included.

Snowflake stores unquoted names in upper case. Names that are entirely upper case come back lower case (C_CUSTKEY becomes c_custkey), and Snowflake folds them back to the same column. Names created quoted with lower-case letters keep their spelling and stay quoted in generated SQL.

Generate

murakumo generate --warehouse snowflake     # or set targets.warehouse.type: snowflake

The generated profiles.yml reads the same variables: key-pair login, role SNOWFLAKE_ROLE_<ENV> (SNOWFLAKE_ROLE_PERSONAL for your own builds), warehouse SNOWFLAKE_WAREHOUSE, and for each environment its database from murakumo.yaml. An environment without a database of its own (projects from before 0.7) uses SNOWFLAKE_DATABASE, which defaults to targets.warehouse.database (or the project name in upper case, when the manifest names a DuckDB file).

Environments

A project made with murakumo init --target snowflake has three environments, each a database (TAXI_DEV, TAXI_STAGE, TAXI_PROD), and everyone's own builds go to a schema named after them in MURAKUMO. murakumo env provision prints what to run once, as ACCOUNTADMIN (or SYSADMIN and SECURITYADMIN), to create them: the databases, an x-small warehouse that suspends itself, a deployer role per environment for CI (TAXI_DEPLOYER_PROD...), a developer role for people (TAXI_DEVELOPER: builds in MURAKUMO, reads the environments to copy them), and the grants. Then grant the developer role to each person and each deployer role to the CI user, and set SNOWFLAKE_ROLE_PERSONAL=TAXI_DEVELOPER (people) and SNOWFLAKE_ROLE_PROD=TAXI_DEPLOYER_PROD (CI). See working as a team.

Platform settings

  • Rebuildable layers are transient. Staging and intermediate are transient by default (targets.warehouse.transient_layers): no fail-safe, so no storage paid for it. Marts and snapshots stay permanent.
  • Time travel per layer: time_travel_days: {raw: 7, marts: 30}.
  • A monthly spending cap: monthly_credits: 50 adds a resource monitor to murakumo env provision.
  • Snowpipe: with stack.load: snowpipe, each file that lands in S3 is loaded by a pipe. murakumo stack deploy --env prod --apply creates the integration, stage and pipes.

  • Masking policies: governance.masking: {pii: redact} masks personal and classified columns for every role outside governance.unmasked_roles. dbt sets the policies after each build; see modelling patterns.

All of this is described in stack.md. The replication guide has every step to set it up by hand.

Verify

murakumo verify --warehouse snowflake
murakumo verify --warehouse snowflake \
  --data lineitem=snowflake:SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.LINEITEM \
  --data yellow_tripdata=local-data/tlc/yellow_tripdata_2024-01.parquet

Each run creates its schemas with a unique prefix (VERIFY_20261027_131005_RAW_TPCH_ERP, ..._STAGING, ..._MARTS) in SNOWFLAKE_DATABASE, so it never touches other tables there, and drops them at the end. Add --keep-tables to look at the results in Snowsight first.

Raw tables are loaded through their table stages: local or https:// Parquet and CSV files are uploaded as they are, synthetic rows go up as CSV, and snowflake:DB.SCHEMA.TABLE copies a table that is already in Snowflake. Then dbt build runs twice, as on DuckDB.

On an XS warehouse, the commerce example with TPC-H SF1 (6 million order lines) and a month of NYC taxi trips (3 million) takes about 90 seconds, a few cents of credits.