Setting up product analytics in Snowflake

1
-- Setting up the warehouse, database, schema, roles, user and such.
2
-- the events table schema we expect
3
4
begin;
5
-- Enter project environment key
6
set project_env_key = 'PROJECT_ENV_KEY_GENERATED_IN_PROCEDURE_ABOVE';
7
-- ===== System Setup Variables (Do not modify) =====
8
set warehouse_name = 'LD_PRODUCT_ANALYTICS_WH'; -- Uppercase required
9
set database_name = 'LD_PRODUCT_ANALYTICS';     -- Uppercase required
10
set role_name = 'LD_PRODUCT_ANALYTICS_ROLE';    -- Uppercase required
11
set schema_name = 'PRODUCT_ANALYTICS_'|| $project_env_key;
12
set user_name = 'LD_PRODUCT_ANALYTICS_USER_'|| $project_env_key;
13
-- ===== Warehouse Setup =====
14
use role sysadmin;
15
create warehouse if not exists identifier($warehouse_name)
16
    warehouse_size = medium
17
    warehouse_type = standard
18
    auto_suspend = 60
19
    auto_resume = true
20
    initially_suspended = true;
21
-- ===== Database Setup =====
22
create database if not exists identifier($database_name);
23
-- ===== Role Setup =====
24
use role securityadmin;
25
create role if not exists identifier($role_name);
26
grant role identifier($role_name) to role SYSADMIN;
27
-- ===== Permissions: Warehouse & Database =====
28
use role sysadmin;
29
grant usage, operate
30
    on warehouse identifier($warehouse_name)
31
    to role identifier($role_name);
32
grant monitor, usage
33
    on database identifier($database_name)
34
    to role identifier($role_name);
35
grant create schema
36
    on database identifier($database_name)
37
    to role identifier($role_name);
38
grant usage
39
    on all schemas in database identifier($database_name)
40
    to role identifier($role_name);
41
grant usage
42
    on future schemas in database identifier($database_name)
43
    to role identifier($role_name);
44
grant create table
45
    on all schemas in database identifier($database_name)
46
    to role identifier($role_name);
47
grant create table
48
    on future schemas in database identifier($database_name)
49
    to role identifier($role_name);
50
grant select
51
    on all tables in database identifier($database_name)
52
    to role identifier($role_name);
53
grant select
54
    on future tables in database identifier($database_name)
55
    to role identifier($role_name);
56
grant select
57
    on all views in database identifier($database_name)
58
    to role identifier($role_name);
59
grant select
60
    on future views in database identifier($database_name)
61
    to role identifier($role_name);
62
-- ===== Schema Setup =====
63
use database identifier($database_name);
64
create schema if not exists identifier($schema_name);
65
grant usage, create table, create view
66
    on schema identifier($schema_name)
67
    to role identifier($role_name);
68
grant create procedure, create task, create stream, create function
69
    on schema identifier($schema_name)
70
    to role identifier($role_name);
71
grant operate
72
    on future tasks in schema identifier($schema_name)
73
    to role identifier($role_name);
74
grant execute task on account
75
    to role identifier($role_name);
76
-- ===== User Setup =====
77
use role securityadmin;
78
create user if not exists identifier($user_name);
79
alter user identifier($user_name)
80
    set default_role = $role_name,
81
        default_warehouse = $warehouse_name,
82
        QUOTED_IDENTIFIERS_IGNORE_CASE = false;
83
grant role identifier($role_name)
84
    to user identifier($user_name);
85
-- ===== Set Public Key =====
86
alter user identifier($user_name)
87
    set rsa_public_key=$$
88
PASTE_PUBLIC_KEY_GENERATED_IN_PROCEDURE_ABOVE
89
$$;
90
commit;