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 warehouse_name = 'SET_WAREHOUSE'; -- all letters must be uppercase
7
set database_name = 'SET_DATABASE'; -- all letters must be uppercase
8
set schema_name = $database_name || '.PUBLIC';
9
set network_rule = $schema_name || '.SET_NETWORK_RULE_NAME'; -- all letters must be uppercase
10
set network_policy = 'SET_NETWORK_POLICY_NAME'; -- all letters must be uppercase
11
set profile_table = $schema_name || '.KLAVIYO_PROFILE';  -- DO NOT CHANGE
12
set event_table = $schema_name || '.KLAVIYO_EVENT'; -- DO NOT CHANGE
13
set metric_table = $schema_name || '.KLAVIYO_METRIC'; -- DO NOT CHANGE
14
15
-- change role to sysadmin for warehouse / database steps
16
use role sysadmin;
17
18
-- create a warehouse for data transfer service
19
create warehouse if not exists identifier($warehouse_name)
20
warehouse_size = xsmall
21
warehouse_type = standard
22
auto_suspend = 60
23
auto_resume = true
24
initially_suspended = true;
25
26
-- create database for data transfer service
27
create database if not exists identifier($database_name);
28
29
create or replace TABLE identifier($profile_table) (
30
    ID VARCHAR(32) NOT NULL,
31
    EXTERNAL_ID VARCHAR(255),
32
    EMAIL VARCHAR(255),
33
    PHONE_NUMBER VARCHAR(255),
34
    FIRST_NAME VARCHAR(255),
35
    LAST_NAME VARCHAR(255),
36
    TITLE VARCHAR(255),
37
    ORGANIZATION VARCHAR(255),
38
    PROPERTIES OBJECT,
39
    IMAGE VARCHAR(255),
40
    CREATED TIMESTAMP_NTZ(9),
41
    UPDATED TIMESTAMP_NTZ(9),
42
    LOCATION_ADDRESS1 VARCHAR(255),
43
    LOCATION_ADDRESS2 VARCHAR(255),
44
    LOCATION_CITY VARCHAR(255),
45
    LOCATION_COUNTRY VARCHAR(255),
46
    LOCATION_LATITUDE VARCHAR(255),
47
    LOCATION_LONGITUDE VARCHAR(255),
48
    LOCATION_REGION VARCHAR(255),
49
    LOCATION_ZIP VARCHAR(255),
50
    primary key (ID)
51
);
52
create or replace TABLE identifier($event_table) (
53
    ID VARCHAR(32) NOT NULL,
54
    METRIC_ID VARCHAR(255) NOT NULL,
55
    PROFILE_ID VARCHAR(255),
56
    EVENT_PROPERTIES OBJECT,
57
    DATETIME TIMESTAMP_NTZ(9),
58
    UUID VARCHAR(255),
59
    primary key (ID)
60
);
61
create or replace TABLE identifier($metric_table) (
62
    ID VARCHAR(6) NOT NULL,
63
    NAME VARCHAR(255) NOT NULL,
64
    INTEGRATION_ID VARCHAR(50) NOT NULL,
65
    INTEGRATION_NAME VARCHAR(255) NOT NULL,
66
    INTEGRATION_CATEGORY VARCHAR(255) NOT NULL,
67
    CREATED TIMESTAMP_NTZ(9),
68
    UPDATED TIMESTAMP_NTZ(9),
69
    primary key (ID)
70
);
71
72
-- change role to securityadmin for user / role steps
73
use role securityadmin;
74
75
-- create network rule and policy for database
76
grant usage on database identifier($database_name) to role securityadmin;
77
grant usage, create network rule on schema identifier($schema_name) to role securityadmin;
78
79
-- whitelist klaviyo ip ranges
80
create network rule if not exists identifier($network_rule)
81
type = IPV4
82
value_list = ('184.72.183.187/32', '52.206.71.52/32', '3.227.146.32/32', '44.198.39.11/32', '35.172.58.121/32', '3.228.37.244/32', '54.88.219.8/32', '3.214.211.176/32')
83
comment = 'Klaviyo IP Ranges as of April 2025';
84
create network policy if not exists identifier($network_policy)
85
allowed_network_rule_list = ($network_rule);
86
87
-- create role for data transfer service
88
create role if not exists identifier($role_name);
89
grant role identifier($role_name) to role SYSADMIN;
90
91
-- create a user for data transfer service
92
create user if not exists identifier($user_name)
93
type = SERVICE
94
network_policy = $network_policy
95
default_role = $role_name
96
default_warehouse = $warehouse_name
97
rsa_public_key = 'GENERATE_PUBLIC_KEY'; -- replace with generated public key
98
grant role identifier($role_name) to user identifier($user_name);
99
100
-- grant service role access to warehouse
101
grant usage
102
on warehouse identifier($warehouse_name)
103
to role identifier($role_name);
104
105
-- grant service access to database
106
grant CREATE SCHEMA, MONITOR, USAGE
107
on database identifier($database_name)
108
to role identifier($role_name);
109
grant usage on schema identifier($schema_name) to role identifier($role_name);
110
111
grant SELECT,INSERT,UPDATE,DELETE on table identifier($profile_table) to identifier($role_name);
112
grant SELECT,INSERT,UPDATE,DELETE on table identifier($event_table) to identifier($role_name);
113
grant SELECT,INSERT,UPDATE,DELETE on table identifier($metric_table) to identifier($role_name);
114
 
115
commit;
116