Snowflake

-- 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 role
CREATE ROLE conversion_sync_role;

-- Grant warehouse access
GRANT USAGE ON WAREHOUSE conversion_sync TO ROLE conversion_sync_role;

-- Grant database and schema access
-- 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

-- 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;

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

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

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

SELECT 
  EMAIL AS "email",
  FIRST_NAME AS "first_name",
  LAST_NAME AS "last_name"
FROM users
WHERE UPDATED_AT >= {{last_sync_time}}

SELECT 
  EMAIL AS "email",
  FIRST_NAME AS "first_name",
  LAST_NAME AS "last_name",
  TITLE AS "job_title",
  PHONE AS "phone",
  CITY AS "city"
FROM users
WHERE UPDATED_AT >= {{last_sync_time}}

SELECT 
  ID AS "id",
  NAME AS "name",
  STATUS AS "status",
  PLAN_TIER AS "plan_tier",
  MONTHLY_PRICE AS "monthly_price"
FROM subscriptions
WHERE UPDATED_AT >= {{last_sync_time}}

SELECT 
  -- Object fields
  p.ID AS "id",
  p.NAME AS "name",
  p.SKU AS "sku",
  p.CATEGORY AS "category",
  p.PRICE AS "price",
  
  -- Contact to relate this object to
  u.EMAIL AS "email",
  
  -- Relationship-specific fields
  OBJECT_CONSTRUCT(
    'role', up.ROLE,
    'quantity', up.QUANTITY,
    'purchased_at', DATE_PART(EPOCH_SECOND, up.PURCHASED_AT)
  ) AS "relationshipFields"
  
FROM user_products up
JOIN products p ON up.PRODUCT_ID = p.ID
JOIN users u ON up.USER_ID = u.ID
WHERE up.UPDATED_AT >= {{last_sync_time}}

SELECT 
  u.EMAIL AS "email",
  'purchase_completed' AS "event_name",
  o.CREATED_AT AS "timestamp",
  CAST(o.ID AS VARCHAR) AS "message_id",
  o.TOTAL_AMOUNT AS "total_amount",
  o.PRODUCT_NAME AS "product_name",
  o.CURRENCY AS "currency"
FROM orders o
JOIN users u ON o.USER_ID = u.ID
WHERE o.CREATED_AT >= {{last_sync_time}}

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 >= {{last_sync_time}}

WHERE UPDATED_AT >= {{last_sync_time}}

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

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 >= {{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;