Snowflake Setup Guide

Prerequisites

• A Snowflake account with the ACCOUNTADMIN role. If you don’t have an account with the ACCOUNTADMIN role, contact your Snowflake administrator to set one up for you.

Setup Guide

In Matia, we adopt a least-privileged approach, enabling us to monitor changes in mirrored Snowflake tables and segregate our operations using distinct roles, warehouses, and databases.

1. Log into your Snowflake account.
2. Edit the following script to change the password to a more secure password and to change the names of other resources if you so desire.

CREATE ROLE IF NOT EXISTS MATIA_RETL_ROLE;

-- Ensure the sysadmin role inherits any privileges the Matia role is granted. Note that this does not grant sysadmin privileges to the matia role
GRANT ROLE MATIA_RETL_ROLE TO ROLE SYSADMIN;

-- Create a warehouse for the Matia role, optimizing for cost over performance
CREATE WAREHOUSE IF NOT EXISTS MATIA_WAREHOUSE WITH WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = FALSE;
GRANT USAGE ON WAREHOUSE MATIA_WAREHOUSE TO ROLE MATIA_RETL_ROLE;
GRANT OPERATE ON WAREHOUSE MATIA_WAREHOUSE TO ROLE MATIA_RETL_ROLE;
GRANT MONITOR ON WAREHOUSE MATIA_WAREHOUSE TO ROLE MATIA_RETL_ROLE;

-- Create the matia user
-- Do not set DEFAULT_WORKSPACE, this will impact which tables are visible to MATIA_RETL
CREATE USER IF NOT EXISTS MATIA_RETL WITH DEFAULT_ROLE = MATIA_RETL_ROLE TYPE = LEGACY_SERVICE DEFAULT_WAREHOUSE = MATIA_WAREHOUSE PASSWORD = '<strong, unique password>';
ALTER USER MATIA_RETL SET PREVENT_UNLOAD_TO_INLINE_URL = FALSE;
GRANT ROLE MATIA_RETL_ROLE TO USER MATIA_RETL;

-- Let the matia user read the data you want to sync
GRANT USAGE ON DATABASE "<your database>" TO ROLE MATIA_RETL_ROLE;
GRANT USAGE ON SCHEMA "<your database>"."<your schema>" TO ROLE MATIA_RETL_ROLE;
GRANT SELECT ON ALL TABLES IN SCHEMA "<your database>"."<your schema>" TO ROLE MATIA_RETL_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA "<your database>"."<your schema>" TO ROLE MATIA_RETL_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA "<your database>"."<your schema>" TO ROLE MATIA_RETL_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA "<your database>"."<your schema>" TO ROLE MATIA_RETL_ROLE;
GRANT USAGE ON ALL FUNCTIONS IN SCHEMA "<your database>"."<your schema>" TO ROLE MATIA_RETL_ROLE;
GRANT USAGE ON FUTURE FUNCTIONS IN SCHEMA "<your database>"."<your schema>" TO ROLE MATIA_RETL_ROLE;

--  Create a private bookkeeping database where Matia stores sync state,
--  perform unloads, and keep Warehouse Writeback logs

CREATE DATABASE IF NOT EXISTS "MATIA_AUDIT";
GRANT ALL PRIVILEGES ON DATABASE "MATIA_AUDIT" TO ROLE MATIA_RETL_ROLE;
CREATE SCHEMA IF NOT EXISTS "MATIA_AUDIT"."RETL";
GRANT ALL PRIVILEGES ON SCHEMA "MATIA_AUDIT"."RETL" TO ROLE MATIA_RETL_ROLE;
GRANT CREATE STAGE ON SCHEMA "MATIA_AUDIT"."RETL" TO ROLE MATIA_RETL_ROLE;

General Settings

After the commands above were performed you will be able to go ahead and provide the parameters of you newly existing resources.

• Enter your Snowflake account identifier in the format organization_id-account_id (e.g., xy12345-ab67890). Both fields can be found in your Snowflake URL.

• Enter the Username for the snowflake source.

• Enter the Password for the snowflake source.

• Enter the Warehouse for the snowflake source.

• Enter the Database name; where the data you want to sync is stored.

• Enter a Asset Name of your preference.

• (Optional) Enter a Description of your preference.

• Click Connect.