MainTargetsSnowflake

Snowflake Setup

Prerequisites

  • Snowflake on AWS or Azure.
  • Privileges to create roles, users, and warehouses. This may require ACCOUNTADMIN, SYSADMIN, or SECURITYADMIN.

GCP

Snowflake on GCP is not yet supported.

Setup

Create a Role

begin;

set supermetal_role = 'SUPERMETAL_ROLE';

-- create Supermetal role
use role ACCOUNTADMIN;
create role if not exists identifier($supermetal_role);
grant role identifier($supermetal_role) to role SYSADMIN;

-- grant permissions to create database
grant CREATE DATABASE ON ACCOUNT TO ROLE identifier($supermetal_role);

commit;

Create a User

begin;

set supermetal_role = 'SUPERMETAL_ROLE';
set supermetal_username = 'SUPERMETAL_USER';

-- create Supermetal user
use role ACCOUNTADMIN;
create user if not exists identifier($supermetal_username)
default_role = $supermetal_role
type = SERVICE;

grant role identifier($supermetal_role) to user identifier($supermetal_username);

commit;

Create a Key Pair

Follow the Snowflake documentation to:

  • Generate a private key and a public key
  • Assign the key pair to the Supermetal user ALTER USER SUPERMETAL_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Create a User

begin;

set supermetal_role = 'SUPERMETAL_ROLE';
set supermetal_username = 'SUPERMETAL_USER';
set supermetal_password = 'strong-password';

-- create Supermetal user
use role ACCOUNTADMIN;
create user if not exists identifier($supermetal_username)
default_role = $supermetal_role
password = $supermetal_password;

grant role identifier($supermetal_role) to user identifier($supermetal_username);

commit;

Password security

Replace 'strong-password' with a unique password and store it securely. You will enter it when configuring the target in Supermetal.

Password deprecation

Snowflake is deprecating password authentication. Prefer key pair authentication.

(Optional) Create a Warehouse

begin;

set supermetal_role = 'SUPERMETAL_ROLE';
set supermetal_username = 'SUPERMETAL_USER';
set supermetal_warehouse = 'SUPERMETAL_WAREHOUSE';

-- change role to sysadmin to create warehouse
use role sysadmin;

-- create Supermetal warehouse
create warehouse if not exists identifier($supermetal_warehouse)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- uncomment to use Generation 2 Warehouse if supported
-- ALTER WAREHOUSE identifier($supermetal_warehouse) SET RESOURCE_CONSTRAINT = STANDARD_GEN_2;

-- grant Supermetal warehouse access
grant USAGE
on warehouse identifier($supermetal_warehouse)
to role identifier($supermetal_role);

commit;

Transactions

Transactional DML is disabled by default. When enabled, Supermetal applies changes through Snowflake transactions and preserves multi table transactions from the source. Changes from a single source transaction apply fully or not at all.

Last updated on

On this page

PrerequisitesSetupCreate a RoleCreate a UserCreate a Key PairCreate a User(Optional) Create a WarehouseTransactions