Connecting from Outside the Browser

SnowSQL, Snowflake CLI, Python/JDBC/ODBC drivers, key-pair auth, MFA, network policies, and the setup needed before tools integrate.

Every chapter so far has lived inside Snowsight — you log in through a browser, a human clicks “Sign in,” and the worksheet just works. That’s a fine place to learn and a terrible place to run a pipeline from. The moment you want dbt to build models, Airflow to kick off a load, a Python script to publish a report, or a BI tool to refresh a dashboard, you need a way in that doesn’t involve a person and a browser tab. That way in is a driver or a CLI, pointed at your account, authenticating as an identity that has no thumbs.

This is the least glamorous chapter in the book and the one that unblocks all the tool integration that follows. Get the connection parameters and the authentication right once, in a place you understand, and every downstream tool becomes “paste these five values.” Get it wrong and you’ll spend a week reading stack traces that all say the same unhelpful thing. So let’s make it boring on purpose.

The connection parameters, in one place

Almost every way of connecting — SnowSQL, the snow CLI, the Python connector, JDBC, a dbt profile — wants the same handful of values. Learn them once here and they’re the same everywhere:

  • account — your account identifier, written as orgname-accountname (organization name, a hyphen, account name). You’ll find both in Snowsight under your account menu, or by running SELECT CURRENT_ORGANIZATION_NAME(), CURRENT_ACCOUNT_NAME();. This is the value newcomers get wrong most often, because there’s an older form — a “locator” like xy12345 optionally suffixed with a region and cloud (xy12345.us-east-1.aws) — that still works but is deprecated and interacts badly with key-pair auth. Prefer orgname-accountname.
  • host — the full server name, which is just the account identifier plus a domain: orgname-accountname.snowflakecomputing.com. Most connectors derive the host from account for you, so you rarely set it by hand — but when a tool insists on a URL (JDBC does), this is how it’s built.
  • user — the login name. For a person, dana. For a pipeline, a dedicated service user like svc_airflow (create one; don’t let Airflow log in as you).
  • role — which role the session activates on connect. If you skip it, you land in the user’s DEFAULT_ROLE. Always set it explicitly for automation — you want the pipeline running as LOADER, not as whatever default drifts to next quarter.
  • warehouse — the compute that runs your queries. No warehouse, no query: you’ll connect fine and then every SELECT fails with “no active warehouse selected.” Set it.
  • database and schema — the default namespace, so you can write orders instead of sales.public.orders. Optional but convenient; a well-behaved script fully-qualifies its object names anyway.

Keep those seven straight and the rest of this chapter is just “where do I type them.”

SnowSQL — the classic CLI

SnowSQL is Snowflake’s original command-line client: a standalone binary you download and install (it’s not pip-installable), available for macOS, Linux, and Windows from the Snowflake website or via the account’s Downloads page. Once installed, the bare command drops you into an interactive shell:

snowsql -a myorg-myaccount -u dana

-a is the account, -u the user; it’ll prompt for a password. That’s fine for a one-off, but you don’t want to retype connection details every time. SnowSQL reads named connections from ~/.snowsql/config (a plain INI file), and this is where the seven parameters go to live:

[connections.bookshop]
accountname = myorg-myaccount
username = dana
password = ...              # omit this in favour of key-pair; see below
rolename = ANALYST
warehousename = COMPUTE_WH
dbname = SALES
schemaname = PUBLIC

Note the SnowSQL-specific key names — accountname, dbname, warehousename — they’re not the same spellings the drivers use, which is a mild annoyance unique to this tool. With that stanza saved, connecting is:

snowsql -c bookshop

-c names the connection. You can define as many [connections.<name>] blocks as you have accounts or roles, and set a [connections] default so a bare snowsql picks one up.

Running scripts and passing variables

Interactive is for exploring; automation runs files. Two flags cover it:

# run a single statement and exit
snowsql -c bookshop -q "SELECT COUNT(*) FROM sales.public.orders;"

# run a whole .sql file and exit
snowsql -c bookshop -f nightly_checks.sql

SnowSQL supports variable substitution, which is what turns a static script into a parameterized one. Enable it, then reference variables with a leading &:

snowsql -c bookshop \
  -f load_day.sql \
  -o variable_substitution=true \
  -D load_date=2026-06-28

Inside load_day.sql:

COPY INTO sales.public.orders
  FROM @sales.public.orders_stage/&load_date/;

The -D name=value flag defines the variable; &load_date expands to 2026-06-28 at run time. You can also set variables from within the session (!set variable_substitution=true, then !define load_date=2026-06-28), which is handy when exploring.

The ! commands

Anything starting with ! in a SnowSQL session is a client command, handled by SnowSQL itself rather than sent to Snowflake. The ones you’ll actually use:

  • !source <file> (alias !load) — run a SQL file without leaving the session. This is how you compose: keep small .sql files and stitch them together interactively.
  • !spool <file> — start capturing all output to a file; !spool off stops. Your ad-hoc audit log.
  • !set / !define — set SnowSQL options and variables mid-session.
  • !print <text> — echo a line, useful as a progress marker in a spooled run.
  • !result <query_id> — re-fetch the results of a query you already ran.
  • !help lists them all; !quit (or !exit) leaves.

SnowSQL still works and plenty of shops still lean on it, but understand its status: it’s in maintenance, not active development. New Snowflake features land in the driver ecosystem and the newer CLI first. If you’re starting fresh, learn SnowSQL enough to read someone else’s runbook, and reach for the tool in the next section for anything new.

The newer Snowflake CLI (snow)

Snowflake CLI — the command is snow — is the going-forward command-line tool, and unlike SnowSQL it is pip-installable and actively developed:

pip install snowflake-cli
snow --version

Its config lives at ~/.snowflake/config.toml (TOML, not INI), and it uses the driver’s parameter names, so what you learned in section one transfers directly. You rarely hand-edit the file — there’s a command to add a connection interactively:

snow connection add
# prompts for name, account, user, role, warehouse, database, schema, auth...

snow connection list          # show configured connections
snow connection test -c bookshop   # actually open a connection and report

snow connection test is the single most useful command in this whole chapter: it connects, runs a trivial query, and prints back the account, user, role, and warehouse it resolved. When something’s misconfigured, this tells you which of the seven values is wrong before any real work fails.

Running SQL mirrors SnowSQL:

snow sql -c bookshop -q "SELECT CURRENT_VERSION();"
snow sql -c bookshop -f nightly_checks.sql
snow sql -c bookshop -q "SELECT * FROM orders WHERE day = <% load_date %>;" \
  --variable "load_date='2026-06-28'"

Note the different templating syntax — snow uses <% var %> placeholders and --variable to fill them, where SnowSQL used &var and -D. Same idea, different dialect; don’t mix them up.

Where snow really pulls ahead of SnowSQL is that it’s not just a SQL runner — it’s a deployment tool for Snowflake’s developer surfaces:

  • snow snowpark builds and deploys Snowpark Python functions and stored procedures — it packages your code and dependencies, uploads them to a stage, and registers the objects, so you can develop a UDF locally and snow snowpark deploy it.
  • snow app manages Snowflake Native Apps through their full lifecycle — snow app run to create and upgrade the application package, snow app deploy to sync files.
  • snow streamlit, snow git, and snow stage round it out for deploying Streamlit-in-Snowflake apps, wiring up Git repositories, and pushing files to stages.

You won’t touch snow app or snow snowpark on day one — they’re for the developer workflows a couple of chapters out — but it’s worth knowing that the same CLI you use for snow sql is the one Snowflake wants you deploying real code with. Learn snow as the default; keep SnowSQL as the legacy you can read.

Authentication, from weakest to strongest

Connecting is half account parameters and half proving who you are. Snowflake supports several authenticators, and the right one depends on whether a human or a machine is logging in.

Password, and the MFA that now wraps it

The obvious one — password = ... — is also the one Snowflake is actively steering you away from for humans. As of 2025–2026, Snowflake is phasing in mandatory multi-factor authentication for human logins: password-only sign-in for people is being blocked in stages, and users are expected to enroll a second factor (an authenticator app, or a passkey). For an interactive human, that’s a good thing and mostly invisible once enrolled.

For automation, MFA is a non-starter — a nightly Airflow run can’t approve a push notification at 2 a.m. This is exactly why the sections below exist. The rule to internalize: passwords are for bootstrapping and humans-with-MFA, never for service accounts.

Key-pair authentication, done properly

For any non-human identity, key-pair authentication is the standard: the service holds a private RSA key, Snowflake holds the matching public key, and the connector proves possession by signing a short-lived JWT. No shared secret sits in Snowflake, nothing to phish, and rotation is a first-class operation.

Generate an encrypted 2048-bit key pair with openssl. Encrypted means the private key file itself is protected by a passphrase — do that for anything that touches a real account:

# private key (encrypted, PKCS#8) — you'll be prompted for a passphrase
openssl genrsa 2048 | \
  openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

# derive the public key from the private one
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Now register the public key on the user. Snowflake wants the base64 body only — strip the -----BEGIN PUBLIC KEY----- / -----END----- lines and the newlines, then:

ALTER USER svc_airflow SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A...';

The private key stays on the machine that connects (ideally pulled from a secrets manager at runtime, never committed). Snowflake never sees it.

Rotation without downtime is the reason key-pair auth beats a rotating password. A user can hold two public keys at once, RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2:

-- 1. register the new public key in the second slot
ALTER USER svc_airflow SET RSA_PUBLIC_KEY_2='<new public key body>';
-- 2. roll every connector over to the new PRIVATE key
-- 3. once nothing uses the old key, retire it
ALTER USER svc_airflow UNSET RSA_PUBLIC_KEY;
-- (optionally promote the new one back into the primary slot)

Because both keys are valid during the overlap, there’s no window where connections fail. That’s the whole trick: two slots, migrate, retire.

To confirm what’s registered, DESC USER svc_airflow exposes a RSA_PUBLIC_KEY_FP property — the SHA-256 fingerprint of the key Snowflake holds, shown as SHA256:.... You can compute the same fingerprint from your local public key and compare, which is invaluable when a login mysteriously fails:

openssl rsa -pubin -in rsa_key.pub -outform DER | \
  openssl dgst -sha256 -binary | openssl enc -base64

If that string doesn’t match RSA_PUBLIC_KEY_FP, the key you’re signing with is not the key Snowflake trusts — full stop. More on that failure below.

External browser / SSO, and OAuth

Two more authenticators, for completeness. externalbrowser hands authentication off to your identity provider (Okta, Entra, Google) via SAML/SSO: the connector pops open a browser window, you complete SSO there, and the session comes back authenticated. It’s the right choice for a developer on a laptop at a company that uses SSO — no Snowflake password at all — but it’s inherently interactive, so it’s useless for a headless pipeline.

snowflake.connector.connect(account="myorg-myaccount", user="dana",
                            authenticator="externalbrowser")

OAuth is the token-based path: an external authorization server (or Snowflake’s own) issues an access token, and the connector presents it with authenticator="oauth" and token=.... This is how BI tools and some orchestrators integrate at scale, and how you avoid embedding any long-lived credential in the tool. It’s a bigger topic than a foundations chapter can do justice to — the mechanics of registering a security integration and minting tokens belong in the security chapter — but know the name and that it exists between “browser SSO for humans” and “key-pair for machines.”

Why key-pair wins for service accounts, stated plainly: a password is a shared secret that must live, in plaintext, wherever the service runs — and if it leaks, anyone with it is the service until you notice and rotate, which invalidates every copy at once (downtime). A key pair inverts all of that: Snowflake stores only the public half (useless to a thief), the private half never transits the network, rotation is dual-slot and downtime-free, and you can prove key identity with a fingerprint. For anything automated, reach for keys.

Drivers and connectors

Below the CLIs sit the actual connectors — libraries your application code imports. The Python connector (snowflake-connector-python) is the one you’ll meet first, and it uses exactly the seven parameters from section one:

import snowflake.connector

conn = snowflake.connector.connect(
    account="myorg-myaccount",
    user="dana",
    password="...",           # or key-pair; see below
    role="ANALYST",
    warehouse="COMPUTE_WH",
    database="SALES",
    schema="PUBLIC",
)

cur = conn.cursor()
cur.execute("SELECT n_name, SUM(o_totalprice) "
            "FROM orders o JOIN customer c ON c.c_custkey = o.o_custkey "
            "JOIN nation n ON n.n_nationkey = c.c_nationkey "
            "GROUP BY n_name")
for name, total in cur:
    print(name, total)

cur.close()
conn.close()

Pulling results into pandas is one call, and pushing a DataFrame back into a table is another — write_pandas bulk-loads via a temporary stage under the hood, far faster than row-by-row INSERTs:

import pandas as pd
from snowflake.connector.pandas_tools import write_pandas

df = cur.fetch_pandas_all()          # results → DataFrame

# ... transform df ...

success, nchunks, nrows, _ = write_pandas(conn, df, table_name="ORDERS_SUMMARY")
print(f"loaded {nrows} rows")

The key-pair variant swaps the password for a private key. The modern connector takes the key file path and its passphrase directly, so you don’t have to hand-load it with cryptography:

import os
import snowflake.connector

conn = snowflake.connector.connect(
    account="myorg-myaccount",
    user="svc_airflow",
    role="LOADER",
    warehouse="LOAD_WH",
    database="SALES",
    schema="PUBLIC",
    private_key_file="/secrets/rsa_key.p8",
    private_key_file_pwd=os.environ["SNOWFLAKE_PRIVATE_KEY_PASSPHRASE"],
)

The passphrase comes from the environment (or a secrets manager); the key file is mounted, not baked into the image. That’s the pattern every orchestrator connection should follow.

Other languages, same story. Snowflake ships first-party connectors for JDBC (Java, and the lingua franca for BI tools — Tableau, Power BI, and dbt-on-JVM paths speak it), ODBC (the Windows/C world, Excel, older BI), Go, Node.js, and .NET. They differ in surface syntax but take the same seven parameters and the same authenticators — JDBC just wants them as a URL, e.g. jdbc:snowflake://myorg-myaccount.snowflakecomputing.com/?warehouse=COMPUTE_WH&db=SALES&role=ANALYST. If you can connect from Python, you can connect from any of them by translating the parameter names.

Finally, Snowpark. When you want DataFrame-style transformations that execute inside Snowflake rather than pulling data to your client, you open a Snowpark Session — and it’s configured with the very same dictionary of parameters:

from snowflake.snowpark import Session

connection_parameters = {
    "account": "myorg-myaccount",
    "user": "svc_airflow",
    "role": "TRANSFORMER",
    "warehouse": "TRANSFORM_WH",
    "database": "SALES",
    "schema": "PUBLIC",
    "private_key_file": "/secrets/rsa_key.p8",
    "private_key_file_pwd": os.environ["SNOWFLAKE_PRIVATE_KEY_PASSPHRASE"],
}
session = Session.builder.configs(connection_parameters).create()

df = session.table("orders").group_by("o_orderstatus").count()
df.show()

Session.builder.configs(...).create() is your entry point to everything Snowpark; the payoff is that the transformation runs on Snowflake’s compute, not your laptop, so the data never leaves the warehouse. Same credentials, different execution surface.

Network policies — locking down where connections come from

Authentication answers who; network policies answer from where. A network policy is an allow/block list of IP ranges (CIDR) that Snowflake enforces before a connection even gets to authenticate:

CREATE NETWORK POLICY corp_and_ci
  ALLOWED_IP_LIST = ('203.0.113.0/24', '198.51.100.7')
  BLOCKED_IP_LIST = ('198.51.100.100');

ALLOWED_IP_LIST is the allowlist; BLOCKED_IP_LIST carves exceptions out of it. A policy does nothing until you attach it, and you can attach at two levels:

-- account-wide: applies to everyone
ALTER ACCOUNT SET NETWORK_POLICY = corp_and_ci;

-- or per-user: e.g. pin the Airflow service account to your CI egress IPs
ALTER USER svc_airflow SET NETWORK_POLICY = ci_egress_only;

The per-user attachment is the powerful move for the “different identities, different network stories” point: your human developers might be allowed from the office VPN, while svc_airflow is locked to the fixed egress IPs of your CI/orchestration cluster and nowhere else. If those credentials leak, they’re inert from any other network. (A user-level policy overrides the account-level one for that user.) One caution — always confirm your own current IP is in the allowlist before setting an account policy, or you’ll lock yourself out; keep an ACCOUNTADMIN session open as an escape hatch while you test.

For the strongest posture, network policies pair with private connectivity — AWS PrivateLink, Azure Private Link, or Google Private Service Connect — which routes traffic to Snowflake over the cloud provider’s private backbone instead of the public internet, so your account is reachable only from inside your VPC/VNet. That’s an infrastructure project rather than a CREATE statement, and it’s the enterprise answer to “our data must never traverse the public internet.” For now, know it exists and that network policies are the SQL-level version of the same instinct: constrain where connections originate, not just who makes them.

Debugging JWT token is invalid

Set up key-pair auth and, sooner or later, you’ll hit the connector’s least helpful error: 250001: Could not connect to Snowflake backend ... JWT token is invalid. It almost never means the JWT library is broken. It means one of three things, in rough order of likelihood:

  1. Account identifier format. This is the big one. Key-pair JWTs are signed with the account and user baked into the token’s subject, and Snowflake is picky: use the orgname-accountname form, not the legacy locator with a region suffix. A JWT minted against xy12345.us-east-1 when the account is really myorg-myaccount fails validation even though a password login with the same string might have worked. Fix the account value first.
  2. Fingerprint mismatch. The private key you’re signing with doesn’t correspond to the public key Snowflake has stored. Compute your local key’s fingerprint (the openssl ... | openssl dgst -sha256 one-liner from earlier) and compare it to RSA_PUBLIC_KEY_FP in DESC USER. If they differ, you either registered the wrong public key, are pointing at the wrong .p8 file, or — after a rotation — retired the key still in use. Re-register or re-point.
  3. Clock skew. The JWT is short-lived (Snowflake allows roughly a minute of validity), so if the machine’s clock is off by more than that, the token is “expired” or “not yet valid” the instant it arrives. Common on CI runners and containers with a drifted clock. Sync the clock (NTP) and retry.

A fourth, quieter cause: case. The connector uppercases the account and user into the JWT subject, and if the user was created with a quoted lowercase name, the subject won’t match. Stick to unquoted (folded-uppercase) user names for service accounts and this never bites. Work down that list — identifier, fingerprint, clock, case — and the “invalid” JWT resolves every time.

Commands and code in this chapter are docs-checked against the series’ stated Snowflake versions, but they were not executed in this repository unless a companion project explicitly says so.

Final thoughts

Connecting from outside the browser is the seam where Snowflake stops being a website you visit and becomes a database your tools talk to — and the whole game is making that seam boring. The parameters never change: account as orgname-accountname, plus user, role, warehouse, and usually database/schema, whether you’re typing them into ~/.snowflake/config.toml, a Python connect() call, or a JDBC URL. Learn the snow CLI as the going-forward default and keep enough SnowSQL to read a legacy runbook. And draw the one line that matters most: humans authenticate interactively — MFA, or externalbrowser SSO — while services authenticate with key pairs, rotated through the two-slot RSA_PUBLIC_KEY / RSA_PUBLIC_KEY_2 dance, pinned by network policy to the IPs they’re allowed to originate from. When JWT token is invalid shows up, it’s the account identifier or the fingerprint nine times out of ten. Get this identity-and-network story boring and auditable now, because every chapter after this one — dbt, Airflow, the whole orchestrated stack — assumes a connection that just works, quietly, without a person in the loop.

Next: Snowflake SQL Dialect and Data Types

Comments