Snowflake Setup

-- ********** SET UP THE FOLLOWING WAREHOUSE PERMISSIONS **********

-- Update the following variables 
SET segment_connection_username = 'SEGMENT_LINKED_USER';
SET segment_connection_password = 'my-safe-password';
SET segment_connection_warehouse = 'SEGMENT_LINKED_WH';
SET segment_connection_role = 'SEGMENT_LINKED_ROLE';

-- The DB  used for Segment's internal bookkeeping.
-- Note: Use this DB in the connection settings on the Segment app. This is the only DB that Segment requires write access to.
SET segment_connection_db = 'SEGMENT_LINKED_PROFILES_DB';

-- ********** [OPTIONAL] UNCOMMENT THE CODE BELOW IF YOU NEED TO CREATE A NEW WAREHOUSE **********

-- CREATE WAREHOUSE IF NOT EXISTS identifier($segment_connection_warehouse)
-- WITH WAREHOUSE_SIZE = 'XSMALL'
--   WAREHOUSE_TYPE = 'STANDARD'
--   AUTO_SUSPEND = 600 -- 5 minutes
--   AUTO_RESUME = TRUE;

-- ********** RUN THE COMMANDS BELOW TO FINISH SETTING UP THE WAREHOUSE PERMISSIONS **********

-- Use admin role for setting grants
USE ROLE ACCOUNTADMIN;

-- Create a role for the Data Graph
CREATE ROLE IF NOT EXISTS identifier($segment_connection_role)
COMMENT = 'Used for Segment Data Graph';

-- Create a user for the Data Graph
CREATE USER IF NOT EXISTS identifier($segment_connection_username)
MUST_CHANGE_PASSWORD = FALSE
DEFAULT_ROLE = $segment_connection_role
PASSWORD = $segment_connection_password
COMMENT = 'Segment Data Graph User'
TIMEZONE = 'UTC';

-- Grant permission to the role to use the warehouse
GRANT USAGE ON WAREHOUSE identifier($segment_connection_warehouse) TO ROLE identifier($segment_connection_role);

-- Grant role to the user
GRANT ROLE identifier($segment_connection_role) TO USER identifier($segment_connection_username);

-- Create and Grant access to a Segment internal DB used for bookkeeping. This is the only DB that Segment requires write access to. This is also the DB you will use in the "Database Name" config while setting up the connection in the Segment app. 
CREATE DATABASE IF NOT EXISTS identifier($segment_connection_db);
GRANT USAGE ON DATABASE identifier($segment_connection_db) TO ROLE identifier($segment_connection_role);
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($segment_connection_db) TO ROLE identifier($segment_connection_role);
GRANT CREATE SCHEMA ON DATABASE  identifier($segment_connection_db) TO ROLE identifier($segment_connection_role);

SET segment_connection_role = 'SEGMENT_LINKED_ROLE';

-- ********** REPEAT THE SQL QUERY BELOW FOR EACH DATABASE YOU WANT TO USE FOR THE DATA GRAPH **********
-- Change this for each DB you want to grant the Data Graph read-only access to
SET linked_read_only_database = 'MARKETING_DB';

GRANT USAGE ON DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT USAGE ON ALL SCHEMAS IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL TABLES IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE TABLES IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL VIEWS IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE VIEWS IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL EXTERNAL TABLES IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE EXTERNAL TABLES IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL MATERIALIZED VIEWS IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN DATABASE identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);

-- [Optional] Further restrict access to only specific schemas and tables 
SET db = 'MY_DB';
SET schema = 'MY_DB.MY_SCHEMA_NAME';
SET segment_connection_role = 'SEGMENT_LINKED_ROLE';

-- View specific schemas in database
GRANT USAGE ON DATABASE identifier($db) TO ROLE identifier($segment_connection_role);
GRANT USAGE ON SCHEMA identifier($schema) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL TABLES IN SCHEMA identifier($schema) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE TABLES IN SCHEMA identifier($schema) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL VIEWS IN SCHEMA identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE VIEWS IN SCHEMA identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL EXTERNAL TABLES IN SCHEMA identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE EXTERNAL TABLES IN SCHEMA identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA identifier($linked_read_only_database) TO ROLE identifier($segment_connection_role);

set segment_connection_role = 'SEGMENT_LINKED_ROLE';
set linked_read_only_database = 'YOUR_DB';
set table_name = 'YOUR_DB.SCHEMA.TABLE';

USE ROLE identifier($segment_connection_role);
USE DATABASE identifier($linked_read_only_database) ;
SHOW SCHEMAS;
SELECT * FROM identifier($table_name) LIMIT 10;

-- If you want to use an existing database that already has Segment Reverse ETL schemas, you’ll need to run some additional steps below to grant the role access to the existing schemas.

SET retl_schema = concat($segment_connection_db,'.__segment_reverse_etl');
GRANT USAGE ON SCHEMA identifier($retl_schema) TO ROLE identifier($segment_connection_role);
GRANT CREATE TABLE ON SCHEMA identifier($retl_schema) TO ROLE identifier($segment_connection_role);
GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA identifier($retl_schema) TO ROLE identifier($segment_connection_role);