Set up a Snowflake target with a least-privilege role

Create a small warehouse that suspends itself, and a role that can use it:

CREATE WAREHOUSE IF NOT EXISTS DOCSF_WH
  WAREHOUSE_SIZE = XSMALL AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;
CREATE ROLE IF NOT EXISTS DOCSF_ROLE;
GRANT USAGE ON WAREHOUSE DOCSF_WH TO ROLE DOCSF_ROLE;

Create the database and let the role use it. CREATE SCHEMA lets the agent create the staging schema when it needs one:

CREATE DATABASE IF NOT EXISTS DOCSF_DB;
GRANT USAGE, CREATE SCHEMA ON DATABASE DOCSF_DB TO ROLE DOCSF_ROLE;

Create the schema that receives the data, and the staging schema. Give the role the right to read the schema and create tables in both:

CREATE SCHEMA DOCSF_DB.DOCSF;
GRANT USAGE, CREATE TABLE ON SCHEMA DOCSF_DB.DOCSF TO ROLE DOCSF_ROLE;

CREATE SCHEMA DOCSF_DB.GLUESYNC;
GRANT USAGE, CREATE TABLE ON SCHEMA DOCSF_DB.GLUESYNC TO ROLE DOCSF_ROLE;

Create the user and give it the role:

CREATE USER DOCSF_USER PASSWORD = '<a strong password>' MUST_CHANGE_PASSWORD = FALSE
  DEFAULT_ROLE = DOCSF_ROLE DEFAULT_WAREHOUSE = DOCSF_WH DEFAULT_NAMESPACE = DOCSF_DB;
GRANT ROLE DOCSF_ROLE TO USER DOCSF_USER;

Create a key pair. The private key is unencrypted PKCS#8, which is the format of the .p8 file that the target accepts:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Register the public key on the user. Paste the lines of rsa_key.pub without the BEGIN and END lines:

ALTER USER DOCSF_USER SET RSA_PUBLIC_KEY = '<contents of rsa_key.pub without the header and footer>';

Create the PAT, restricted to the role of the agent. Snowflake requires a network policy for a user with a PAT. In a lab without one, the MINS_TO_BYPASS_NETWORK_POLICY_REQUIREMENT option lets you create the token. In production, attach a network policy that allows the address of Core Hub, and leave the option out:

ALTER USER DOCSF_USER ADD PROGRAMMATIC ACCESS TOKEN GLUESYNC_TOKEN
  ROLE_RESTRICTION = 'DOCSF_ROLE' DAYS_TO_EXPIRY = 90
  MINS_TO_BYPASS_NETWORK_POLICY_REQUIREMENT = 480;

In the Control Plane, select Create your first pipeline, or the + button of the pipeline list if you already have pipelines. The Agents setup wizard opens;

In 1. Setup source, choose the PostgreSQL CDC agent tile and fill Base settings: Host name / Address, Port, Database name, Username and Password of your source database. Select Test and continue, then Next;

In 2. Setup target(s), choose the Snowflake tile and fill Base settings:

Select Test and continue. The target appears with its user and host. Select Next, enter a Pipeline name and select Finish.

Open the pipeline and select *Entities *. The object browser lists the schemas of the source. Open `+docsf` and tick events;

The Field mapping dialog opens. Set Target Schema to DOCSF. In Target Table, type EVENTS and select Create table "EVENTS". Gluesync proposes a type for each column;

Fix what the dialog marks. The note column of events is text, and the dialog shows Missing required field until you give it a length. Snowflake text can hold up to 16,777,216 bytes: enter 16777216 in Length;

Select Confirm. Gluesync shows the CREATE TABLE statement. Read it, then select Create table EVENTS. Gluesync runs it as your role;

Do the same for typed, with the types of the Data types section below, and untick tags. An array column cannot be loaded (step 8);

Select Confirm and continue. The entities appear in the pipeline, on hold.

In the row of docsf.events, select the start icon. The Sync changes (CDC) dialog opens;

Under Start from, keep Full copy, which the dialog recommends for the first start. Under Data already in the target, keep Keep and merge. Under Speed, keep Standard;

Select Start entity. Do the same for docsf.typed.

Open docsf.events and select Count both under Inspect tables;

In Snowflake, compare the counts and read a few rows:

SELECT COUNT(*) FROM DOCSF_DB.DOCSF.EVENTS;
SELECT * FROM DOCSF_DB.DOCSF.TYPED ORDER BY ID;

Read the query history of the user to see the statements of the flushes:

SELECT query_type, LEFT(query_text, 120) AS statement
FROM TABLE(DOCSF_DB.INFORMATION_SCHEMA.QUERY_HISTORY_BY_USER(USER_NAME => 'DOCSF_USER', RESULT_LIMIT => 200))
WHERE query_type IN ('PUT_FILES', 'COPY', 'MERGE', 'INSERT', 'TRUNCATE_TABLE')
ORDER BY start_time DESC;

In the source database, insert, update and delete rows:

INSERT INTO docsf.events VALUES (200001, 'click', 1, 1.5, now(), 'cdc insert');
UPDATE docsf.events SET val = -1, note = 'cdc update' WHERE id = 5;
DELETE FROM docsf.events WHERE id = 6;
UPDATE docsf.events SET kind = 'bulkupd' WHERE id BETWEEN 1000 AND 1999;

After a few seconds, check Snowflake:

SELECT COUNT(*) FROM DOCSF_DB.DOCSF.EVENTS;
SELECT id, val, note FROM DOCSF_DB.DOCSF.EVENTS WHERE id IN (5, 6, 200001);
SELECT COUNT(*) FROM DOCSF_DB.DOCSF.EVENTS WHERE kind = 'bulkupd';

Sign in as a new user with a password. The target fails to save with Host Credentials Update Failed and Failed to initialize pool: Multi-factor authentication is required for this account. Log in to Snowsight to enroll. Fix: create a PAT (step 2) and paste it into Password;

Use a wrong password or token. The message is Failed to initialize pool: Incorrect username or password was specified. In the tests, the target always signed in with the value of Password: a valid key, registered on the user and uploaded, did not replace a password that MFA refused;

Set Warehouse to a name that does not exist. The connection is saved, and the page shows Host Credentials Update Failed: The connection was saved, but agent <id> of pipeline <id> did not accept the new credentials: No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command. Fix: use the name of a warehouse that the role can use. Select Test and continue again after the fix;

Set Database name to one that does not exist. The message is ... did not accept the new credentials: Cannot perform SELECT. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.;

Set Host name / Address to an account that does not exist. The message is ... did not accept the new credentials: Failed to initialize pool: JDBC driver encountered communication error. Message: HTTP status=404.;

Open the Field mapping dialog when the role sees no schema. The Target Schema and Target Table boxes stay disabled, and the Schemas Fetch answer is empty. Fix: create the schema and grant USAGE and CREATE TABLE on it (step 1);

Map a text column and leave the length at 0. The dialog shows Missing required field, and Confirm stays disabled. The same holds for varchar, variant and binary columns that the dialog proposes with a length of 0: enter a length. The maximum is 16,777,216 for text and VARIANT, and 8,388,608 for binary;

Map a PostgreSQL array column (text[]) to array or to varchar. The dialog shows Incompatible data types and does not let you confirm. Only binary is accepted, with a warning. Then the snapshot of the entity stops with Target write failed: The following string is not a legal hex-encoded value: 'a, b' plus the file, line and column. Fix: open the entity menu, select Edit mapping, untick the array column, and select Confirm and then Confirm and continue. Start the entity again;

Add entities when the staging schema does not exist. The page shows The entity could not be saved and the notification Entity Upsert Failed: SQL compilation error: Schema 'DOCSF_DB.GLUESYNC' does not exist or not authorized. Your primary role DOCSF_ROLE must have USAGE or any other privilege granted on SCHEMA DOCSF_DB.GLUESYNC. The agent creates the staging schema only when an entity has bulk operations switched on, and the Control Plane has no such switch for Snowflake. Fix: create the schema and grant it to the role (step 1), or the same with the name that you set in Gluesync schema name (necessary for bulk operations).