Snowflake
Snowflake is a popular cloud data warehouse that separates compute and storage.
This guide walks you through configuring your Snowflake warehouse to work seamlessly with Supermetal.
Features
| Feature | Notes |
|---|---|
| Bulk Insert | |
| Transactionally Consistent | |
| Schema Evolution | |
| Soft Delete(s) |
Prerequisites
Before you begin, ensure you have:
- Supported Snowflake Implementations:
- AWS
- Azure
GCP
Snowflake on GCP is not yet supported.
- Database Admin Access: Sufficient privileges to create users, grant permissions. This might require
ACCOUNTADMIN/SYSADMIN/SECURITYADMINprivileges.
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';
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 secure, unique password for the supermetal_user. Store this password securely, as you'll need it when configuring the target in Supermetal.
Password Deprecation
Snowflake is deprecating password authentication. Please use other authentication methods instead.
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...';
(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
When enabled, Supermetal uses Snowflake's transactions to apply changes atomically across multiple tables. This preserves transaction boundaries from your source database when possible, ensuring that all changes within a single source transaction are either fully applied or not applied at all.
Data Types Mapping
| Apache Arrow DataType | Snowflake Type | Notes |
|---|---|---|
Int8, Int16, Int32, Int64, UInt8, UInt16, UInt32, UInt64 | NUMBER | |
Float16, Float32, Float64 | FLOAT | |
Decimal128(p,s) | NUMBER(p,s) | if p <= 38 and s <= 37 |
Decimal256(p,s) | VARCHAR | if p > 38 |
| Apache Arrow DataType | Snowflake Type | Notes |
|---|---|---|
Date32, Date64 | DATE | |
Time32(ms) | TIME(0) | |
Time64(us) | TIME(6) | |
Time64(ns) | TIME(9) | |
Timestamp(s, [tz]) | TIMESTAMP_NTZ(0) | |
Timestamp(ms, [tz]) | TIMESTAMP_NTZ(3) | |
Timestamp(us, [tz]) | TIMESTAMP_NTZ(6) | |
Timestamp(ns, [tz]) | TIMESTAMP_NTZ(9) | |
Duration, Interval | VARCHAR |
| Apache Arrow DataType | Snowflake Type | Notes |
|---|---|---|
Utf8, LargeUtf8 | VARCHAR |
| Apache Arrow DataType | Snowflake Type | Notes |
|---|---|---|
Boolean | BOOLEAN |
| Apache Arrow DataType | Snowflake Type | Notes |
|---|---|---|
Struct | VARIANT | |
Map | VARIANT | |
Utf8 JSON Extension (arrow.json) | VARIANT |
| Apache Arrow DataType | Snowflake Type | Notes |
|---|---|---|
Binary, LargeBinary, FixedSizeBinary | BINARY |
| Apache Arrow DataType | Snowflake Type | Notes |
|---|---|---|
List, LargeList, FixedSizeList | VARIANT |
Maximum Length
VARCHAR & BINARY data types are limited to 16MB and might be truncated (TRUNCATECOLUMNS option).
Last updated on