Concepts
Connectors
- Sources
- Destinations
Snowflake
Required settings
- Account identifier
- Virtual warehouse
- Service account
- Database name
- Schema name
You can find your account identifier at the bottom left corner of your Snowflake console.
BEGIN TRANSACTION;
USE ROLE ACCOUNTADMIN; -- This combines both SYSADMIN and SECURITYADMIN
-- IMPORTANT, PLEASE FILL THIS OUT AND SAVE THIS --
SET ROLE_NAME = 'ARTIE_TRANSFER_ROLE';
SET SERVICE_USER = 'ARTIE';
SET SERVICE_PW = 'PASSWORD';
SET DWH_NAME = UPPER('DWH');
SET DB_NAME = UPPER('DB_NAME');
SET SCHEMA_NAME = UPPER('public');
-- END IMPORTANT --
SET DB_SCHEMA_NAME = CONCAT($DB_NAME, '.', $SCHEMA_NAME);
CREATE ROLE IF NOT EXISTS identifier($ROLE_NAME);
CREATE USER IF NOT EXISTS identifier($SERVICE_USER)
password = $SERVICE_PW
default_role = $ROLE_NAME;
GRANT ROLE identifier($role_name) to USER identifier($SERVICE_USER);
CREATE WAREHOUSE IF NOT EXISTS identifier($DWH_NAME)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 10
auto_resume = true
initially_suspended = true;
CREATE DATABASE IF NOT EXISTS identifier($DB_NAME);
CREATE SCHEMA IF NOT EXISTS identifier($DB_SCHEMA_NAME);
GRANT USAGE ON WAREHOUSE identifier($DWH_NAME) TO ROLE identifier($ROLE_NAME);
GRANT USAGE ON DATABASE identifier($DB_NAME) TO ROLE identifier($ROLE_NAME);
GRANT ALL PRIVILEGES ON SCHEMA identifier($DB_SCHEMA_NAME) TO ROLE IDENTIFIER($ROLE_NAME);
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA identifier($DB_SCHEMA_NAME) TO ROLE IDENTIFIER($ROLE_NAME);
GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA identifier($DB_SCHEMA_NAME) TO ROLE IDENTIFIER($ROLE_NAME);
COMMIT;
To use key-pair authentication, you will want to generate an RSA key pair.
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
Then upload the public key to Snowflake
ALTER USER ARTIE SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
Once this is done, provide us the private key to authenticate with.
Snowflake eco mode
Snowflake eco mode is a great option if you only need faster data syncs during business hours. Click here to learn more!
- Edit your pipeline
- Go into the
Destinationstab - Open
Advanced settingsand select+ New schedule - Define your peak hours and your time zone
- Define your flush rules for off-peak hours
- Save schedule
- Deploy your changes
Troubleshooting
To start, We would recommend you to go with a x-small and decide whether or not you need to scale based off of our Snowflake insights metrics from our Analytics portal.
Generally speaking, if your overall query execution time is high, then you may want to scale to a larger instance. If your queued overload time is high, you may want to have more virtual warehouse nodes to increase your parallelizable slots.
Snowflake’s native RBAC makes it so that the account that created the resource is the native owner. To change this, assign the ARTIE service account’s role to your account and you will be able to operate on the table. See the GIF below on how to fix this problem.