1
begin;
2
 
3
   -- create variables for user / password / role / warehouse / database
4
   set role_name = 'SET_ROLE_NAME'; -- all letters must be uppercase
5
   set user_name = 'SET_USER_NAME'; -- all letters must be uppercase
6
   set user_password = 'SET_PASSWORD'; -- alphanumeric only, special characters are not allowed
7
   set warehouse_name = 'SET_WAREHOUSE'; -- all letters must be uppercase
8
   set database_name = 'SET_DATABASE'; -- all letters must be uppercase
9
   set profile_table = $database_name || '.PUBLIC.KLAVIYO_PROFILE';  -- DO NOT CHANGE
10
   set event_table = $database_name || '.PUBLIC.KLAVIYO_EVENT'; -- DO NOT CHANGE
11
set metric_table = $database_name || '.PUBLIC.KLAVIYO_METRIC'; -- DO NOT CHANGE
12
13
   -- change role to securityadmin for user / role steps
14
   use role securityadmin;
15
 
16
   -- create role for data transfer service
17
   create role if not exists identifier($role_name);
18
   grant role identifier($role_name) to role SYSADMIN;
19
 
20
   -- create a user for data transfer service
21
   create user if not exists identifier($user_name)
22
   password = $user_password
23
   default_role = $role_name
24
   default_warehouse = $warehouse_name;
25
 
26
   grant role identifier($role_name) to user identifier($user_name);
27
 
28
   -- change role to sysadmin for warehouse / database steps
29
   use role sysadmin;
30
 
31
   -- create a warehouse for data transfer service
32
   create warehouse if not exists identifier($warehouse_name)
33
   warehouse_size = xsmall
34
   warehouse_type = standard
35
   auto_suspend = 60
36
   auto_resume = true
37
   initially_suspended = true;
38
 
39
   -- create database for data transfer service
40
   create database if not exists identifier($database_name);
41
 
42
   -- grant service role access to warehouse
43
   grant USAGE
44
   on warehouse identifier($warehouse_name)
45
   to role identifier($role_name);
46
 
47
   -- grant service access to database
48
   grant CREATE SCHEMA, MONITOR, USAGE
49
   on database identifier($database_name)
50
   to role identifier($role_name);
51
52
grant usage on schema identifier('PUBLIC') to role identifier($role_name);
53
54
create or replace TABLE identifier($profile_table) (
55
    ID VARCHAR(32) NOT NULL,
56
    EXTERNAL_ID VARCHAR(255),
57
    EMAIL VARCHAR(255),
58
    PHONE_NUMBER VARCHAR(255),
59
    FIRST_NAME VARCHAR(255),
60
    LAST_NAME VARCHAR(255),
61
    TITLE VARCHAR(255),
62
    ORGANIZATION VARCHAR(255),
63
    PROPERTIES OBJECT,
64
    IMAGE VARCHAR(255),
65
    CREATED TIMESTAMP_NTZ(9),
66
    UPDATED TIMESTAMP_NTZ(9),
67
    LOCATION_ADDRESS1 VARCHAR(255),
68
    LOCATION_ADDRESS2 VARCHAR(255),
69
    LOCATION_CITY VARCHAR(255),
70
    LOCATION_COUNTRY VARCHAR(255),
71
    LOCATION_LATITUDE VARCHAR(255),
72
    LOCATION_LONGITUDE VARCHAR(255),
73
    LOCATION_REGION VARCHAR(255),
74
    LOCATION_ZIP VARCHAR(255),
75
    primary key (ID)
76
);
77
78
create or replace TABLE identifier($event_table) (
79
    ID VARCHAR(32) NOT NULL,
80
    METRIC_ID VARCHAR(255) NOT NULL,
81
    PROFILE_ID VARCHAR(255),
82
    EVENT_PROPERTIES OBJECT,
83
    DATETIME TIMESTAMP_NTZ(9),
84
    UUID VARCHAR(255),
85
    primary key (ID)
86
);
87
create or replace TABLE identifier($metric_table) (
88
    ID VARCHAR(6) NOT NULL,
89
    NAME VARCHAR(255) NOT NULL,
90
    INTEGRATION_ID VARCHAR(50) NOT NULL,
91
    INTEGRATION_NAME VARCHAR(255) NOT NULL,
92
    INTEGRATION_CATEGORY VARCHAR(255) NOT NULL,
93
    CREATED TIMESTAMP_NTZ(9),
94
    UPDATED TIMESTAMP_NTZ(9),
95
    primary key (ID)
96
);
97
grant SELECT,INSERT,UPDATE,DELETE on table identifier($profile_table) to identifier($role_name);
98
grant SELECT,INSERT,UPDATE,DELETE on table identifier($event_table) to identifier($role_name);
99
grant SELECT,INSERT,UPDATE,DELETE on table identifier($metric_table) to identifier($role_name);
100
 
101
commit;
102