Snowflake

USE ROLE ACCOUNTADMIN

-- Create a dedicated warehouse (optional - you can reuse an existing one)
CREATE WAREHOUSE CONVERSION_SYNC
  WITH WAREHOUSE_SIZE = 'XSMALL'
  WAREHOUSE_TYPE = 'STANDARD'
  AUTO_SUSPEND = 300  -- 5 minutes
  AUTO_RESUME = TRUE;

-- Create the role
CREATE ROLE CONVERSION_SYNC_ROLE;

-- Allow the role to use the warehouse from Step 1
GRANT USAGE ON WAREHOUSE CONVERSION_SYNC TO ROLE CONVERSION_SYNC_ROLE;

-- Allow the role to read everything (current + future) inside the database
-- Replace MY_DATABASE with the database you want Conversion to sync from
GRANT USAGE  ON DATABASE MY_DATABASE                     TO ROLE CONVERSION_SYNC_ROLE;
GRANT USAGE  ON ALL    SCHEMAS IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;
GRANT USAGE  ON FUTURE SCHEMAS IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON ALL    TABLES  IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON FUTURE TABLES  IN DATABASE MY_DATABASE   TO ROLE CONVERSION_SYNC_ROLE;

-- Create the role and grant warehouse access (same as above)
CREATE ROLE CONVERSION_SYNC_ROLE;

GRANT USAGE ON WAREHOUSE CONVERSION_SYNC TO ROLE CONVERSION_SYNC_ROLE;

-- Replace with your database, schema, and table names
GRANT USAGE  ON DATABASE MY_DATABASE                          TO ROLE CONVERSION_SYNC_ROLE;
GRANT USAGE  ON SCHEMA   MY_DATABASE.my_schema                TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON TABLE    MY_DATABASE.my_schema.users          TO ROLE CONVERSION_SYNC_ROLE;
GRANT SELECT ON TABLE    MY_DATABASE.my_schema.products       TO ROLE CONVERSION_SYNC_ROLE;
-- Add more tables as needed

USE ROLE CONVERSION_SYNC_ROLE;
SHOW DATABASES LIKE 'MY_DATABASE';   -- expect 1 row
USE DATABASE MY_DATABASE;            -- expect success

-- Create user
CREATE USER CONVERSION_SYNC_USER
  DEFAULT_ROLE = CONVERSION_SYNC_ROLE
  DEFAULT_WAREHOUSE = CONVERSION_SYNC;

-- Assign role to user
GRANT ROLE CONVERSION_SYNC_ROLE TO USER CONVERSION_SYNC_USER;

-- 1. Find your existing policy
SHOW NETWORK POLICIES;
-- 2. See what IPs it currently allows; copy these, you'll need them in the next step
DESC NETWORK POLICY <EXISTING_NETWORK_POLICY>;
-- 3. Re-set the list with your existing IPs plus Conversion's IPs
ALTER NETWORK POLICY <EXISTING_NETWORK_POLICY> SET ALLOWED_IP_LIST = (
  '<existing_ip_1>',
  '<existing_ip_2>',
  -- ... all of your existing entries
  '35.239.90.161',
  '35.188.167.166',
  '34.56.101.43',
  '34.122.97.230',
  '34.29.176.66',
  '35.226.154.44'
);

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

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

sed '1d;$d' rsa_key.pub

ALTER USER CONVERSION_SYNC_USER SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

SELECT 
  EMAIL AS "email",
  DATE_PART(EPOCH_SECOND, CREATED_AT) AS "created_at",
  DATE_PART(EPOCH_SECOND, LAST_LOGIN) AS "last_login"
FROM users
WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}})

WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}})

WHERE DATE_PART(EPOCH_SECOND, UPDATED_AT) >= {{last_sync_time}}

WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}} - 300)  -- 5 minute buffer

OBJECT_CONSTRUCT(
  'role', ROLE,
  'quantity', QUANTITY,
  'started_at', DATE_PART(EPOCH_SECOND, STARTED_AT)
) AS "relationshipFields"

SELECT 
  EMAIL AS "email",
  IFF(IS_ACTIVE, 'true', 'false') AS "is_active",
  IFF(IS_ADMIN, 'true', 'false') AS "is_admin"
FROM users
WHERE UPDATED_AT >= TO_TIMESTAMP({{last_sync_time}})

SELECT 
  EMAIL AS "email",
  COALESCE(FIRST_NAME, '') AS "first_name",
  NVL(PHONE, '') AS "phone"
FROM users

SELECT 
  CAST(USER_ID AS VARCHAR) AS "id",
  SCORE::INTEGER AS "lead_score"
FROM users

ALTER USER CONVERSION_SYNC_USER
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;

ALTER ROLE CONVERSION_SYNC_ROLE
  SET QUOTED_IDENTIFIERS_IGNORE_CASE = FALSE;