Klaviyo 및 스노우플레이크 연결

이 문서에서는 '테이블'이라는 용어를 사용하지만 뷰, 구체화된 뷰 및 테이블은 모두 가져올 수 있는 유효한 눈송이 개체입니다. Klaviyo 가 개체에서 SELECT col1 FROM table_name을 실행할 수 있는 한, 원하는 것을 자유롭게 사용할 수 있습니다. 

이 문서에서 "MUST", "MUST NOT", "필수", "SHALL", "SHALL NOT", "SHOULD", "SHOULD NOT", "권장", "MAY", "선택적" 키워드는 RFC 2119에 설명된 대로 해석해야 합니다.

눈송이 관리자 설정

이 섹션에서는 Klaviyo 에서 데이터를 가져오기 위해 스노우플레이크 환경에서 따라야 하는 단계를 간략하게 설명합니다. 

1. 로컬 터미널에서 다음 명령을 실행하여 개인 키를 생성합니다:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

2. 터미널에서 다음 명령을 실행하여 개인 키를 참조하는 공개 키를 생성합니다:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

3. rsa_key.pub를 복사하여 아래 스크립트에 붙여넣어 user_rsa_public_key의 자리 표시자'GENERATE_PUBLIC_KEY' 값을 대체합니다. 아래 스크립트는 Mac 사용자에게 적합하며, 원하는 경우 IDE에서 rsa_key.pub를 열고 파일의 전체 내용을 복사할 수도 있습니다. 

# Mac 터미널 명령어를 사용하여 터미널에 키를 쓰고 클립보드에 복사
cat rsa_key.pub | tee /dev/tty | pbcopy

4. 스노우플레이크 환경에서 다음 스크립트를 실행하여 Klaviyo 사용할 서비스 사용자를 만듭니다. 아래 설정을 완료하려면 보안 관리자 및 시스템 관리자 권한이 있어야 합니다. 어떤 역할이 있는지 확인하려면 SHOW GRANTS TO USER <your_username> 를 실행하여 두 역할이 모두 나열되어 있는지 확인합니다. 도달률 역할 조정이 필요한 경우 시스템 관리자에게 문의하세요.
   1. 스크립트 시작 부분에 설정된 변수를 자유롭게 업데이트할 수 있습니다.
   2. 요약하면 다음과 같습니다:
      1. 기존 창고를 선택하거나 새 창고를 만듭니다.
      2. 기존 데이터베이스를 선택하거나 새 스키마를 저장할 새 데이터베이스를 만듭니다.
      3. 두 개의 새 스키마 KLAVIYO_TMP 및 KLAVIYO_IMPORT_FROM_DWH를생성합니다.
      4. 새 네트워크 정책을 만들고 목록 Klaviyo IP를 허용합니다.
      5. 다음에 대한 사용자 및 역할 만들기 Klaviyo
   3. 이 스크립트는 무력하지만(여러 번 안전하게 실행할 수 있음) 이름이 충돌하는 기존 개체를 덮어쓰지는 않습니다. 

BEGIN;

-- 사용자 / 비밀번호 / 역할 / 웨어하우스 / 데이터베이스에 대한 변수를 만듭니다. 
-- 원하는 대로 변경할 수 있습니다.
SET role_name = 'KLAVIYO_DATA_TRANSFER_ROLE'; -- 모든 문자는 대문자여야 합니다(예. 'KLAVIYO_DATA_TRANSFER_ROLE'
SET user_name = 'KLAVIYO_DATA_TRANSFER_USER'; -- 모든 문자는 대문자여야 합니다(예. 'KLAVIYO_DATA_TRANSFER_USER'
SET warehouse_name = 'KLAVIYO_데이터_전송_웨어하우스'; -- 모든 문자는 대문자여야 합니다(예. 'KLAVIYO_DATA_TRANSFER_WAREHOUSE'
SET database_name = 'KLAVIYO_DATABASE'; -- 모든 문자는 대문자여야 합니다(예. '클라비요_데이터베이스'. 이 데이터베이스가 존재하지 않으면 새 데이터베이스가 생성됩니다.
SET network_policy = 'KLAVIYO_DATA_TRANSFER_NETWORK_POLICY'; -- 모든 문자는 대문자여야 합니다(예. 'KLAVIYO_NETWORK_POLICY'
SET network_rule = 'KLAVIYO_DATA_TRANSFER_NETWORK_RULE'; -- 모든 문자는 대문자여야 합니다(예. 'KLAVIYO_NETWORK_RULE'
/* 아래 GENERATE_PUBLIC_KEY를 생성된 공개 키로 대체 */

-- DO NOT CHANGE
SET schema_name_tmp = $database_name || '.KLAVIYO_TMP'; -- DO NOT CHANGE
SET schema_name_import = $database_name || '. KLAVIYO_IMPORT_FROM_DWH'; -- DO NOT CHANGE
SET full_network_rule_tmp = $schema_name_tmp || '.' || $network_rule; -- DO NOT CHANGE
SET full_network_rule_import = $schema_name_import || '.' || $network_rule; -- DO NOT CHANGE


-- warehouse/데이터베이스 단계에 대한 역할을 sysadmin으로 변경
USE ROLE sysadmin;

-- 데이터 전송 서비스를 위한 웨어하우스 생성
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($warehouse_name)
 warehouse_size = xsmall
 warehouse_type = standard
 auto_suspend = 60
 auto_resume = true
 initially_suspended = true;

-- 데이터 전송 서비스를 위한 데이터베이스를 생성합니다
CREATE DATABASE IF NOT EXISTS IDENTIFIER($database_name);

-- 데이터 전송 서비스를 위한 스키마를 생성합니다
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($schema_name_tmp);
CREATE SCHEMA IF NOT EXISTS IDENTIFIER($schema_name_import);

-- 사용자/역할 단계에 대한 역할을 securityadmin으로 변경
USE ROLE securityadmin;

-- 네트워크 생성  데이터베이스에 대한 규칙 및 정책
데이터베이스 식별자($database_name)에 대한 사용 권한을 보안 관리자 역할에 부여;
사용 권한 부여, 스키마 식별자($schema_name_tmp)에 대한 네트워크 규칙 만들기 보안 관리자 역할에 부여;
GRANT USAGE, CREATE NETWORK RULE ON SCHEMA IDENTIFIER($schema_name_import) TO ROLE securityadmin;

-- whitelist Klaviyo ip ranges, for KLAVIYO_TMP schema 
CREATE NETWORK RULE IF NOT EXISTS IDENTIFIER($full_network_rule_tmp)
 type = IPV4
 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'
 )
 comment = 'Klaviyo 2025년 4월 기준 IP 범위';
CREATE NETWORK POLICY IF NOT EXISTS IDENTIFIER($네트워크_정책)
 allowed_network_rule_list = ($풀_네트워크_rule_tmp);

-- whitelist Klaviyo ip ranges, for KLAVIYO_IMPORT_FROM_DWH schema 
CREATE NETWORK RULE IF NOT EXISTS IDENTIFIER($full_network_rule_import)
 type = IPV4
 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'
 )
 comment = 'Klaviyo 2025년 4월 기준 IP 범위';
CREATE NETWORK POLICY IF NOT EXISTS IDENTIFIER($네트워크_정책)
allowed_network_rule_list = ($풀_네트워크_rule_import);


-- 데이터 전송 서비스를 위한 역할 생성
CREATE ROLE IF NOT EXISTS IDENTIFIER($role_name);
GRANT ROLE IDENTIFIER($role_name) TO ROLE sysadmin;

-- 데이터 전송 서비스를 위한 사용자 만들기
CREATE USER IF NOT EXISTS IDENTIFIER($user_name)
 type = SERVICE
 network_policy = $network_policy
 default_role = $role_name
 default_warehouse = $warehouse_name
 rsa_public_key = 'GENERATE_PUBLIC_KEY';
GRANT ROLE IDENTIFIER($role_name) TO USER IDENTIFIER($user_name);
ALTER USER IDENTIFIER($user_name) SET NETWORK_POLICY = $네트워크_정책;

-- warehouse에 서비스 역할 액세스 권한 부여
GRANT USAGE
 ON WAREHOUSE IDENTIFIER($warehouse_name)
 TO  ROLE IDENTIFIER($role_name);

-- 데이터베이스에 서비스 액세스 권한 부여
GRANT 모니터, USAGE
 ON DATABASE IDENTIFIER($database_name)
 TO ROLE IDENTIFIER($role_name);

-- KLAVIYO_TMP에 대한 권한 부여 
GRANT USAGE ON SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE IDENTIFIER($role_name);
GRANT 모니터, 사용, 테이블 생성, 뷰 생성, 시퀀스 생성, 함수 생성, 프로시저 생성
 ON SCHEMA IDENTIFIER($schema_name_tmp)
 TO ROLE IDENTIFIER($role_name);
GRANT ALL ON FUTURE TABLES IN SCHEMA IDENTIFIER($schema_name_tmp) TO ROLE IDENTIFIER($role_name);

-- 부여 KLAVIYO_IMPORT_FROM_DWH
GRANT USAGE ON SCHEMA IDENTIFIER($schema_name_import) TO ROLE IDENTIFIER($role_name)에 대한 권한을 부여합니다;
GRANT SELECT
 ON FUTURE TABLES
 IN SCHEMA IDENTIFIER($schema_name_import)
 TO ROLE IDENTIFIER($role_name);

COMMIT;

눈송이 데이터 설정

위에서 두 개의 새 스키마를 만들었습니다. 

• KLAVIYO_TMP는 Klaviyo 에서 독점적으로 사용합니다. 이 스키마에서 생성된 테이블을 수정해서는 안 됩니다. Klaviyo 는 더 이상 필요하지 않은 테이블을 삭제합니다.
• Klaviyo 에서 가져올 최종 테이블을 온라인 스토어에 저장해야 합니다. 동기화 생성 프로세스를 진행하면 이 스키마의 모든 테이블이 나열되어 선택할 수 있습니다. 따라서 설정 중 혼란을 피하기 위해 가져오려는 최종 테이블만 온라인 스토어에 저장해야 합니다. 

Klaviyo 으로 가져오려는 모든 요금제 테이블은 다음 기준을 충족해야 합니다. 

타임스탬프 요구 사항 

1. 테이블에는 행이 생성되거나 업데이트된 시점을 나타내는 타임스탬프 필드가 포함되어야 합니다. 종종 삽입된 위치 또는 업데이트된 위치가 됩니다. 동기화 생성 과정에서 각 테이블에 대해 이 값을 설정합니다.
   1. 타임스탬프 필드는 반드시 단조롭게 증가해야 합니다(즉, 항상 커지거나 동일하게 유지되어야 하며 절대 작아지지 않아야 합니다).
   2. 동기화 생성 후 행의 타임스탬프 값을 과거의 시간으로 설정하지 않으면 Klaviyo 에서 해당 행을 가져오지 못할 수 있습니다.
   3. 위의 요구 사항을 준수하는 한 Klaviyo 에서 이 특정 필드의 표준 시간대는 중요하지 않습니다.
   4. Klaviyo 현재_타임스탬프() 또는 이와 동등한 함수를 사용하여 타임스탬프 필드를 설정할 것을 권장합니다. 여러 행에 동일한 타임스탬프가 있을 수 있습니다. 아래 예시를 참조하세요. 

INSERT INTO table_name AS 

SELECT ... 

, CURRENT_TIMESTAMP() AS inserted_at

... 

2. 타임스탬프는 반드시 UTC 기준이거나 표준 시간대 정보를 포함해야 합니다. 표준 시간대 정보가 누락된 경우 Klaviyo 은 UTC로 가정합니다. 사용자 지정 속성의 경우 이러한 타임스탬프는 문자열 형식으로 유지되므로 원하는 표준 시간대로 해석할 수 있습니다.

테이블 구조 

3. 테이블은 추가 전용(일명 삽입 전용)으로 처리해야 합니다.
   1. 대신 행을 제자리에서 업데이트하려면 Klaviyo 에서 변경 사항을 식별할 수 있도록 타임스탬프 필드를 업데이트해야 합니다.
4. 타임스탬프 열에 테이블을 정렬해야 합니다. 스노우플레이크는 삽입 순서에 따라 클러스터링 및 파티셔닝을 처리합니다. 이렇게 하면 Klaviyo의 가져오기 쿼리를 최적화하여 Snowflake의 컴퓨팅 비용을 낮추는 데 도움이 됩니다.

프로필 고유성 및 일관성 

5. 각 프로필 속성은 반드시 하나의 데이터 소스(테이블)에서만 가져와야 합니다. Klaviyo 를 사용하면 동기화 생성 중에 다른 테이블에서 동일한 속성을 선택할 수 없으므로 이 요구 사항이 간소화됩니다.
6. 모든 가져오기 테이블에서 동일한 프로필 식별자(이메일, 전화번호, 외부 ID 등)를 사용하여 중복 프로필이 생성될 위험을 최소화해야 합니다.
   1. Klaviyo 는 사용자가 제공한 프로필 식별자가 Klaviyo 내의 기존 프로필과 일치하지 않는 경우 새 프로필을 생성합니다.
   2. 예시 예: 표1(이메일, fav_color) + 표2(전화, 생일)
      1. 프로필이 현재 존재하지 않는 경우 동일한 사용자에 대해 2개의 프로필이 생성될 수 있습니다. 프로필이 존재하는 경우 Klaviyo 에서 프로필 확인 및 업데이트를 내부적으로 처리합니다. 
   3. 이 문제를 방지하는 한 가지 방법은 모든 프로필에 하나의 가져오기 테이블만 사용하는 것입니다. 

순환 가져오기-내보내기 루프 방지 

7. 가져오기 및 내보내기 기능을 모두 사용하는 시나리오를 주의 깊게 관리하여 순환 가져오기-내보내기 루프를 방지해야 합니다. 현재 Klaviyo 에서는 이 시나리오를 감지하지 못하므로 내보내기 프로세스가 가져오기 테이블의 업스트림에 있는 테이블에 데이터를 다시 공급하지 않도록 하세요.
   1. Klaviyo 에는 아직 이 시나리오를 감지하는 로직이 없습니다.
   2. 다음과 같이 보일 것입니다:
      1. 내보내기 동기화 주기마다 Klaviyo 에서 모든 프로필을 내보냅니다.
      2. 그런 다음 일련의 변환을 통해 내보낸 모든 프로필을 가져오기 테이블에 추가합니다.
      3. 가져오기 동기화 주기마다 Klaviyo 에서 가져오기 테이블의 모든 프로필을 읽은 후 다시 내보냅니다.
   3. 이것이 안전할 것 같은 시나리오 
      1. 가져오기 테이블에 추가되는 행을 제한하기 위해 내보내기 테이블만 사용하는 경우
      2. 내보내기 테이블이 가져오기 테이블에 행을 추가하지 않는지 확인합니다.
   4. 순환 가져오기-내보내기 루프의 결과는 무엇인가요?
      1. 이렇게 하면 사용자와 Klaviyo 모두에게 불필요한 컴퓨팅 비용이 발생하게 됩니다.