Docs / Snowflake

Connect Snowflake

Cloud data platform

Snowflake connects with account credentials and a warehouse. Its permissions are hierarchical, which is the one thing that catches people out: access has to be granted at four separate levels, and missing any one of them fails differently.

Credentials

Account identifier
Everything before .snowflakecomputing.com in your Snowflake URL, for example xy12345.us-east-1.
Warehouse
The compute warehouse to run queries on, for example COMPUTE_WH.
Username
The read-only user created below.
Password
That user’s password.
Database
Optional. Sets the default database.
Schema
Optional. Sets the default schema, usually PUBLIC.
Role
Optional, but set it if you use a custom role such as analytics_reader.

Finding your account identifier

Log in and read the URL. Everything before .snowflakecomputing.com is the identifier, in whichever of these forms your account uses:

  • xy12345 — bare account locator
  • xy12345.us-east-1 — locator with region
  • orgname-accountname — organization format
  • xy12345.us-east-1.privatelink — AWS PrivateLink

The four permission levels

All four are required. Each produces a different error when missing, which is why a partly-granted role looks like an unrelated problem.

USAGE ON WAREHOUSE
Needed to run any query. Missing: “No active warehouse selected”.
USAGE ON DATABASE
Needed to reach the database. Missing: “Object does not exist”.
USAGE ON SCHEMA
Needed to see objects in the schema. Missing: no tables appear.
SELECT ON TABLES
Needed to read data. Missing: “Insufficient privileges” on every query.

Setup in SQL

Run as ACCOUNTADMIN or SECURITYADMIN, replacing my_database, PUBLIC and COMPUTE_WH with your own.

-- 1. A dedicated role for read-only analytics
CREATE ROLE IF NOT EXISTS analytics_reader;

-- 2. Warehouse — required to run any query
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE analytics_reader;

-- 3. Database
GRANT USAGE ON DATABASE my_database TO ROLE analytics_reader;

-- 4. Schema, including ones added later
GRANT USAGE ON SCHEMA my_database.PUBLIC TO ROLE analytics_reader;
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE my_database TO ROLE analytics_reader;

-- 5. Tables, including ones added later
GRANT SELECT ON ALL TABLES IN SCHEMA my_database.PUBLIC TO ROLE analytics_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA my_database.PUBLIC TO ROLE analytics_reader;

-- 6. Views, if you use them
GRANT SELECT ON ALL VIEWS IN SCHEMA my_database.PUBLIC TO ROLE analytics_reader;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA my_database.PUBLIC TO ROLE analytics_reader;

-- 7. The user
CREATE USER IF NOT EXISTS analytics_user
  PASSWORD = 'your_secure_password_here'
  DEFAULT_ROLE = analytics_reader
  DEFAULT_WAREHOUSE = COMPUTE_WH
  MUST_CHANGE_PASSWORD = FALSE;

-- 8. Attach the role
GRANT ROLE analytics_reader TO USER analytics_user;

Setup in Snowsight

The same grants without SQL. Slower for bulk work, but useful if you want visual confirmation of each step.

  1. Create the role. Admin → Users & Roles → Roles → + Role, named analytics_reader.
  2. Warehouse USAGE. Admin → Warehouses → your warehouse → Privileges → + Privilege → grant USAGE to the role.
  3. Database USAGE. Data → Databases → your database → Privileges → grant USAGE.
  4. Schema USAGE. Data → Databases → your database → Schemas → the schema → Privileges → grant USAGE.
  5. SELECT on tables. From the schema’s Privileges tab, grant SELECT on all tables to the role.
  6. Create the user and assign the role. Admin → Users & Roles → Users → + User, then add analytics_reader in the user’s detail view.
  7. Set defaults. In the same view, set the default role and default warehouse.

Verifying

SHOW GRANTS TO ROLE analytics_reader;

You should see USAGE on the warehouse, the database and the schema, plus SELECT entries for tables. If any of the three USAGE rows is absent, the connection will behave as though the data is not there.

Choosing a warehouse

A dedicated X-SMALL warehouse is usually right: it is the cheapest size, it handles analytical queries comfortably, and it keeps this workload off the warehouse your production jobs use. Set auto-suspend low.

CREATE WAREHOUSE analytics_wh
  WITH WAREHOUSE_SIZE = 'X-SMALL'
       AUTO_SUSPEND = 60
       AUTO_RESUME = TRUE;

Worth knowing

  • A warehouse must be able to resume for queries to run; auto-suspend is fine.
  • Identifiers are case-insensitive unless quoted, and role names are uppercased automatically.
  • All Snowflake connections use TLS.
  • If you have network policies in place, our egress addresses need to be allowed.
  • If MFA is enforced for the user, key-pair authentication is needed instead of a password — talk to us.
  • Rotate the password roughly every 90 days.
  • Query history gives you an independent record of everything climpt runs.