Snowflake
This is a step by step guide to integrate Snowflake and Querio.
Before you start
- A Snowflake admin role with rights to create users/roles and manage grants (typically SECURITYADMIN and USERADMIN).
- The RSA public key you want to associate with the Snowflake service user (2048-bit or stronger). We’ll send you this key, however, if your policy requires, you may generate your own key pair and keep the private key on your side.
1) Create a dedicated service user
What: A non-human user that Querio will use to connect (type SERVICE).
How (as USERADMIN) execute the following SQL in a worksheet :
CREATE USER QUERIO_SVC
LOGIN_NAME = QUERIO_SVC
TYPE = SERVICE
DEFAULT_ROLE = QUERIO_ROLE
DEFAULT_WAREHOUSE = QUERIO_WH
DEFAULT_NAMESPACE = MY_DATABASE.PUBLIC
COMMENT = 'Service user for Querio integration';If the user already exists, you can ALTER USER to set the properties.
https://docs.snowflake.com/en/sql-reference/sql/create-user
Note: TYPE = SERVICE clearly identifies this as an application/service identity.
2) Create a least-privilege role and grants
What: A role with only the privileges needed to read your chosen objects.
How (as SECURITYADMIN or ACCOUNTADMIN) execute the following SQL in a worksheet:
-- Role
CREATE ROLE IF NOT EXISTS QUERIO_ROLE;
-- Warehouse access (query execution)
GRANT USAGE ON WAREHOUSE QUERIO_WH TO ROLE QUERIO_ROLE;
-- If you don't know your current warehouse, run SELECT CURRENT_WAREHOUSE();
-- If you want to see all your warehouses, run SHOW WAREHOUSES;
-- Data access (Give us access to all necessary databases and schemas)
GRANT USAGE ON DATABASE MY_DATABASE TO ROLE QUERIO_ROLE;
GRANT USAGE ON SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;
-- Read-only data privileges
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;
-- Keep future objects covered
GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;
-- Assign role to the service user
GRANT ROLE QUERIO_ROLE TO USER QUERIO_SVC;Important: Follow least privilege and scope grants only to the databases/schemas you intend Querio to read. Use future grants to avoid drift.
https://docs.snowflake.com/en/user-guide/security-access-control-configure
Tip: You typically do not need OPERATE on the warehouse if AUTO_RESUME is enabled (default). Queries with USAGE can trigger auto-resume.
https://docs.snowflake.com/en/sql-reference/sql/alter-warehouse?utm_source=chatgpt.com
3) Associate the RSA public key to the service user
What: Register the public key so Snowflake can verify key-pair logins.
How (as SECURITYADMIN or a role with the proper privilege):
-- Assign the public key (omit PEM header/footer lines)
ALTER USER QUERIO_SVC SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';
-- Verify the fingerprint
DESC USER QUERIO_SVC;
-- Check the RSA_PUBLIC_KEY valueNote: Snowflake requires the base64 content of the public key without the -----BEGIN/END PUBLIC KEY----- lines. Use DESCRIBE USER to confirm the fingerprint.
https://docs.snowflake.com/en/user-guide/key-pair-auth
Optional – you generate the key pair internally
# Encrypted PKCS#8 private key (recommended)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
# Public key (PEM)
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pubStore the private key securely per your policy.
4) Share the connection metadata
Provide the following to complete the connection:
{
"account": "orgname-account_name",
// To find your account run SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();
"user": "QUERIO_SVC",
"warehouse": "QUERIO_WH",
"database": "MY_DATABASE",
"schema": "PUBLIC",
"role": "QUERIO_ROLE"
}