Connecting Snowflake to Golden Suite

Set up the Snowflake connector with key-pair auth and warehouse routing.

The Snowflake connector pulls rows from any table or query in your warehouse. Key-pair auth is preferred over password (more secure, no rotation friction).

Prereqs

  • A Snowflake account on any edition
  • A read-only user with USAGE on the warehouse, USAGE on the database + schema, SELECT on the target tables
  • Ideally: a dedicated warehouse for Golden Suite's queries (XS or S size is plenty for incremental ingest)

Setup

  1. /golden/sources → Add source → Snowflake
  2. Pick auth method:
    • Key-pair (recommended) — upload your private key; paste the public key into Snowflake's user object
    • Password — username + password
  3. Connection params:
    • Account identifier (e.g., abc12345.us-east-1 — without .snowflakecomputing.com)
    • Warehouse (e.g., GOLDEN_SUITE_WH)
    • Database + schema
    • Role (e.g., GOLDEN_SUITE_READER)
  4. Test connection
  5. Provide the query — same shape as the Postgres guide

Sample SQL setup (Snowflake side)

-- Run as ACCOUNTADMIN once
CREATE WAREHOUSE GOLDEN_SUITE_WH WITH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60;
CREATE ROLE GOLDEN_SUITE_READER;
CREATE USER goldensuite_user
    LOGIN_NAME = 'goldensuite_user'
    DEFAULT_ROLE = GOLDEN_SUITE_READER
    DEFAULT_WAREHOUSE = GOLDEN_SUITE_WH
    RSA_PUBLIC_KEY = '<paste-public-key>';
GRANT ROLE GOLDEN_SUITE_READER TO USER goldensuite_user;
GRANT USAGE ON WAREHOUSE GOLDEN_SUITE_WH TO ROLE GOLDEN_SUITE_READER;
GRANT USAGE ON DATABASE PROD TO ROLE GOLDEN_SUITE_READER;
GRANT USAGE ON SCHEMA PROD.ANALYTICS TO ROLE GOLDEN_SUITE_READER;
GRANT SELECT ON ALL TABLES IN SCHEMA PROD.ANALYTICS TO ROLE GOLDEN_SUITE_READER;
GRANT SELECT ON FUTURE TABLES IN SCHEMA PROD.ANALYTICS TO ROLE GOLDEN_SUITE_READER;

Common gotchas

  • Account identifier format. Snowflake's account URL is abc12345.us-east-1.snowflakecomputing.com but the connector wants just abc12345.us-east-1 (no domain, no https://).
  • Auto-suspend the warehouse. With AUTO_SUSPEND = 60, an idle Golden Suite WH stops billing within a minute. The cost of running ingest on a dedicated XS warehouse is typically pennies per day.
  • Cold-start latency. First query of the day might take 30 seconds to wake the WH. Subsequent queries within the auto-suspend window are immediate.
  • SHOW statements aren't supported. The connector executes one SELECT per ingest; don't put SHOW TABLES or stored-procedure calls in the query.
  • Result set size. Snowflake can return huge result sets; the connector batches at 1000 rows per page but the initial result-set materialization still happens server-side. For 10M+ row tables, narrow the query.
  • Time travel. Don't use AT (TIMESTAMP => ...) in the query unless you've thought through what happens on incremental re-runs.

Cost considerations

A dedicated XS warehouse running 1-2 minutes/day for incremental ingest costs ~$0.01-0.05/day depending on edition. Initial full-table ingests on 10M-row tables cost more — typically $0.10-1.00 — depending on column count and warehouse size.

Next steps

← Connecting Postgres to Golden SuiteConnecting BigQuery to Golden Suite →