Snowflake

Run read-only SQL on a Snowflake warehouse through Snowflake’s SQL API.

Connect it in the console under Integrations > Connectors. See Connectors for how connectors work and who can use them.

Create a read-only credential

Create a role that can only read, a service user with a key pair, and give the user the role. Generate the key pair first:

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

CREATE ROLE VAIF_READER;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE VAIF_READER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE VAIF_READER;
GRANT USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS TO ROLE VAIF_READER;
GRANT SELECT ON ALL TABLES IN DATABASE ANALYTICS TO ROLE VAIF_READER;
GRANT SELECT ON FUTURE TABLES IN DATABASE ANALYTICS TO ROLE VAIF_READER;
CREATE USER VAIF_READER TYPE = SERVICE DEFAULT_ROLE = VAIF_READER RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';
GRANT ROLE VAIF_READER TO USER VAIF_READER;

Paste the public key without its -----BEGIN and -----END lines. Paste the private key (the .p8 file) into VAIF Studio. A programmatic access token also works; Snowflake may require a network policy before it accepts one.

Settings

Setting		Notes
Account identifier	Required	For example myorg-myaccount.
User	Required	For example VAIF_READER.
Sign-in	Optional	One of: Key pair (private key), Programmatic access token. Default: Key pair (private key).
Private key (PEM)	Optional	Stored encrypted and never shown again.
Private key passphrase	Optional	Stored encrypted and never shown again.
Programmatic access token	Optional	Stored encrypted and never shown again.
Warehouse	Optional
Database	Optional
Schema	Optional
Role	Optional	A role that can only SELECT.

The account identifier is ORGNAME-ACCOUNTNAME (in Snowsight: your account menu, Copy account identifier) or an account locator. Warehouse, Database, Schema and Role are defaults for every statement; set Database to list its tables.

Tables

Table	Filters	Returns
tables	None	Tables in the configured database.
sql	query (required)	Run one read-only SQL statement. Needs the developer role.

Query from the API

curl -s -X POST "https://api.vaif.studio/database/connector/$VAIF_PROJECT_ID/query" \
  -H "Authorization: Bearer $VAIF_TOKEN" -H "Content-Type: application/json" \
  -d '{"wrapperId":"snowflake_fdw","table":"sql","filters":{"query":"SELECT region, SUM(amount) AS revenue FROM sales GROUP BY region"},"limit":50}'

$VAIF_TOKEN is a console access token. See Connectors for the routes.

How it stays read-only

Snowflake has no read-only session setting, so the role you connect with is what keeps the connection read-only: give it SELECT and nothing else. VAIF Studio also refuses statements that write, sends one statement per request (MULTI_STATEMENT_COUNT = 1), and stops a statement after 30 seconds. Queries use your warehouse and are billed to your Snowflake account.