Snowflake – Getting Started: Account, Worksheet and Connectors

Getting from nothing to a running query takes about ten minutes, and most of that is waiting for a confirmation email. This lesson covers the account choices that are annoying to change later, the four settings every session needs, and how to connect from outside the browser.

The trial account

Sign up at signup.snowflake.com. It is 30 days and a fixed amount of free credit, and it does not ask for a card. Two of the choices on that form are worth thinking about for a moment.

Edition. The trial defaults to Enterprise, which is the right pick for learning because it enables things Standard does not — multi-cluster warehouses, materialised views, and 90 days of Time Travel instead of 1. Just be aware that a feature working on your trial does not prove it works on the Standard account your employer bought.

Cloud and region. Pick the cloud and region your data already lives in. Loading from an S3 bucket in us-west-2 into a Snowflake account in Azure Europe works, and you pay egress for the privilege on every load. This one is genuinely hard to change afterwards — an account cannot be moved between regions, only replicated to a new one.

Your account has a URL of the form https://<org>-<account>.snowflakecomputing.com, and the <org>-<account> half is the account identifier every connector asks for. Note it down now; it is under Admin → Accounts in the UI.

What a new account already has

• SNOWFLAKE_SAMPLE_DATA — the shared TPC-H and TPC-DS data this track queries. It is a share, not a copy: you are reading Snowflake's data, you pay nothing to store it, and you cannot write to it.
• SNOWFLAKE — a read-only database of your own account's usage and metadata. Lesson 15 lives in here.
• A warehouse, usually COMPUTE_WH, sized X-Small.
• The system roles: ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN, PUBLIC. Lesson 14 explains why you should stop using the first one almost immediately.

Snowsight and the four context settings

Snowsight is the web UI, and a worksheet in it is where most people write their first thousand queries. The thing to understand about it is session context: every statement runs as a role, on a warehouse, in a database and a schema. Get one wrong and you get either a permission error or, worse, the right query against the wrong data.

-- Where am I?
SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE(), CURRENT_SCHEMA();

-- Set all four explicitly. Do this at the top of any script you care about.
USE ROLE SYSADMIN;
USE WAREHOUSE COMPUTE_WH;
USE DATABASE SNOWFLAKE_SAMPLE_DATA;
USE SCHEMA TPCH_SF1;

Snowsight has dropdowns for all four, and they are per-worksheet, not global. A script that runs in one worksheet and fails in another is nearly always this.

Fully-qualified names sidestep the problem entirely, and are worth the extra typing in anything saved to a file:

SELECT COUNT(*) FROM snowflake_sample_data.tpch_sf1.orders;

Creating somewhere to work

You cannot write to the sample share, so make a database of your own. This track uses LEARN_SNOWFLAKE for anything it creates:

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS learn_snowflake;
CREATE SCHEMA   IF NOT EXISTS learn_snowflake.staging;

-- A small warehouse of our own, suspending aggressively.
CREATE WAREHOUSE IF NOT EXISTS learn_wh
  WAREHOUSE_SIZE      = XSMALL
  AUTO_SUSPEND        = 60      -- seconds idle before it stops
  AUTO_RESUME         = TRUE
  INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE learn_wh;
USE SCHEMA   learn_snowflake.staging;

INITIALLY_SUSPENDED = TRUE matters more than it looks. Without it the warehouse starts running the moment you create it and bills until something suspends it, which for a warehouse you created to use next week is pure waste.

Identifiers are upper case

This surprises everyone exactly once. Unquoted identifiers are folded to upper case, so customer, Customer and CUSTOMER are the same object. Quoted identifiers are case-sensitive and stored exactly as written.

CREATE TABLE my_table (id INT);      -- stored as MY_TABLE
CREATE TABLE "my_table" (id INT);    -- a DIFFERENT table, stored as my_table

SELECT * FROM my_table;              -- finds MY_TABLE
SELECT * FROM "my_table";            -- finds the quoted one

The rule that keeps you out of trouble: never quote an identifier you created yourself. Quoting shows up when a loading tool creates columns from a CSV header — "Order Date" — and from then on every query has to quote it too.

What else is in Snowsight

Four parts of the UI earn their keep, and knowing they exist saves writing SQL to find things out.

• Worksheets — SQL editors with results below. Each one carries its own context, keeps a history, and can be shared with a colleague.
• Databases — a browser over every object your current role can see. Useful as a permissions check: if a table is not listed, the role cannot read it, and no amount of retyping the name will help.
• Query History (Monitoring → Query History) — every query in the account for the last 14 days, with duration, the warehouse used, bytes scanned, and the Query Profile behind each one. Lesson 11 lives here.
• Admin → Usage — credits consumed, broken down by warehouse. Worth a look on day one so the shape of it is familiar before there is a bill to explain.

Snowsight also charts results directly from a worksheet and pins them to a dashboard, which is enough for internal monitoring without buying a BI tool.

The four errors everyone hits first

Connecting from outside the browser

Three clients cover almost everything.

The CLI, installed with pip install snowflake-cli:

# One-time: create a named connection. Prompts for the details.
snow connection add --connection-name learn

snow connection test --connection-name learn

# Run a statement, or a whole file.
snow sql --connection-name learn -q "SELECT CURRENT_VERSION();"
snow sql --connection-name learn -f setup.sql

From Python, with pip install snowflake-connector-python:

import os
import snowflake.connector

conn = snowflake.connector.connect(
    account=os.environ["SNOWFLAKE_ACCOUNT"],      # e.g. myorg-myaccount
    user=os.environ["SNOWFLAKE_USER"],
    private_key_file=os.environ["SNOWFLAKE_KEY"], # not a password — see below
    role="SYSADMIN",
    warehouse="LEARN_WH",
    database="LEARN_SNOWFLAKE",
    schema="STAGING",
)

with conn.cursor() as cur:
    cur.execute("SELECT c_mktsegment, COUNT(*) "
                "FROM snowflake_sample_data.tpch_sf1.customer "
                "GROUP BY 1")
    for segment, customers in cur:
        print(segment, customers)

conn.close()

Use key-pair authentication, not a password

Passwords for service accounts are a problem in any system, and Snowflake has been progressively removing the option — password-only sign-in for programmatic users is being retired in favour of key pairs. Set it up once and stop thinking about it:

# Generate an encrypted private key and the matching public key.
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out sf_key.p8
openssl rsa -in sf_key.p8 -pubout -out sf_key.pub

# Strip the header, footer and newlines — Snowflake wants the body only.
grep -v 'PUBLIC KEY' sf_key.pub | tr -d '\n'

Then attach the public key to the user. Human users get MFA; service accounts get a key and the TYPE = SERVICE marker, which makes their purpose explicit:

USE ROLE SECURITYADMIN;

ALTER USER etl_service SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';

-- Confirm it took. The fingerprint should match your key.
DESCRIBE USER etl_service;

Keep the private key out of the repository and out of the image — an environment variable injected at runtime, or a secrets manager. Lesson 16 comes back to this alongside network policies, which are the other half of locking a service account down.

Next: virtual warehouses — the thing you are actually paying for.