Snowflake.

Connect Orchid to your Snowflake account. The connector supports password, key-pair, OAuth, and external-browser SSO, and lets you pick a warehouse, database, and role per connection.

Requirements

• Account identifier — e.g. xy12345.us-east-1 or orgname-accountname (find it in your Snowflake URL).
• Username.
• Authentication — password, RSA key pair, OAuth, or external browser (SSO).
• Default warehouse, database, role.

Connect

1. Open Integrations → + Add connection → Snowflake.
2. Fill in account, username, warehouse, database, role.
3. Pick an auth method (see below).
4. Click Test connection → save.

Authentication methods

Password

Paste it. Stored in your OS keychain. Fine for individual accounts; many orgs block password auth for SSO-managed identities.

Key pair

Upload or paste the private key in PKCS#8 format. Set the public key on the user once:

-- In Snowflake, register the public key (ACCOUNTADMIN or USERADMIN)
ALTER USER your_user SET RSA_PUBLIC_KEY='MIIBIjANBg...';
      

OAuth setup

Snowflake supports OAuth via external IdPs (Okta, Azure AD) or its own built-in OAuth. Built-in OAuth setup, run as ACCOUNTADMIN:

CREATE SECURITY INTEGRATION orchid_oauth
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'PUBLIC'
  OAUTH_REDIRECT_URI = 'http://localhost:54321/oauth/callback'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 86400;

-- Fetch the client_id Orchid will need
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('ORCHID_OAUTH');
      

In Orchid, pick OAuth, paste the client ID. A browser tab opens the Snowflake consent screen; the granted token is stored in your OS keychain and refreshed automatically.

External browser (SSO)

Pick External browser. Clicking Test opens a browser tab for your IdP's Snowflake login. Sessions are typically valid for 4–8 hours depending on your IdP policy.

Optional settings

Role hierarchy

Snowflake roles inherit privileges. Build a dedicated role for Orchid that grants only what you need — and parent it under the role you actually want it to inherit from.

-- A read-only role scoped to analytics
USE ROLE SECURITYADMIN;
CREATE ROLE analytics_reader;
GRANT USAGE ON WAREHOUSE analytics_wh TO ROLE analytics_reader;
GRANT USAGE ON DATABASE analytics TO ROLE analytics_reader;
GRANT USAGE ON ALL SCHEMAS IN DATABASE analytics TO ROLE analytics_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics.marts TO ROLE analytics_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA analytics.marts TO ROLE analytics_reader;

-- Make it the user's default role
GRANT ROLE analytics_reader TO USER your_user;
ALTER USER your_user SET DEFAULT_ROLE = analytics_reader;
      

Granting on FUTURE TABLES means new tables added to the schema are automatically readable — important for analytics schemas that grow over time.

Warehouse sizing

Snowflake bills per second of warehouse time. Get the warehouse right for your workload:

• X-Small — best for interactive notebooks. Fits a single analyst doing ad-hoc queries on small/medium tables. 60-second auto-suspend.
• Small / Medium — for queries that scan tens of GB and you need under 10 seconds latency.
• Large+ — only if your workload genuinely benefits from parallelism (large joins, sorts). Most notebook queries don't.

If you find yourself bumping warehouse size to make queries faster, look first at partitioning (clustering keys), pruning columns, and adding filters. Sizing up is the last lever, not the first.

Semi-structured (VARIANT) data

Snowflake stores JSON, XML, and similar in the VARIANT type. Query it like any other column with path notation.

-- A table 'events' with a VARIANT column 'payload'
SELECT
  event_id,
  payload:user_id::string  AS user_id,
  payload:action::string   AS action,
  payload:meta.referrer::string AS referrer
FROM events
WHERE payload:source::string = 'web'
  AND event_ts > current_date - 7;
      

-- Flatten a nested array into rows
SELECT e.event_id, f.value:sku::string AS sku, f.value:qty::int AS qty
FROM events e,
     LATERAL FLATTEN(input => e.payload:items) f
WHERE e.event_ts > current_date - 7;
      

Common gotchas

• "Cannot perform CREATE" — your role lacks privileges. Switch to a role with USAGE on the database/schema. The role picker is in the connection settings.
• Account identifier wrong — the right format is <orgname>-<accountname> for newer accounts, or <account>.<region>.<cloud> for older ones. Use what's in the URL when you're logged into Snowflake.
• SSO loop — if external-browser auth keeps reopening, your IdP session may be stuck. Sign out of the IdP and retry.
• Slow first query — warehouse cold start. Run a quick SELECT 1 to warm it up.
• "Compilation error: invalid identifier" — Snowflake folds unquoted identifiers to uppercase. SELECT id FROM Users looks for USERS.ID. Quote case-sensitively or stick to lowercase + uppercase consistency.

Example queries

-- Current warehouse / role / db
SELECT CURRENT_WAREHOUSE(), CURRENT_ROLE(), CURRENT_DATABASE();
      

-- Tables in the current schema with row estimates
SHOW TABLES;
SELECT "name", "rows", "bytes"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "rows" DESC;
      

Where to go next

For more on writing SQL cells, see SQL cells.