Snowflake

A Snowflake connection needs load_method. It has no default, because it decides how data gets into Snowflake:

[connections.wh]
kind = "snowflake"
account = "myorg-myaccount"
user = "SQLLOAD"
auth = { method = "key_pair", private_key_file = "/etc/sqlload/key.p8" }
role = "LOADER"
warehouse = "LOAD_WH"
database = "RAW"
load_method = { type = "external_stage", url = "s3://example-bucket/sqlload/", storage_integration = "LAKE_INT" }

type	Data goes through	Setup
external_stage	An external stage on your S3 prefix. Files sources under url load directly from it.	A storage integration, or an existing stage, and SQLLoad’s write access to the bucket.
internal_user_stage	The user stage @~.	None.
internal_table_stage	The table stage @%table.	None.
internal_named_stage	A named internal stage: name = "DB.SCHEMA.STAGE".	The stage.
streaming	Snowpipe Streaming into a transient staging table.	None.
insert	Batched multi-row INSERT into a transient staging table. Very slow; plan warns.	None.

A method the role can’t use fails at plan. SQLLoad never falls back to another one.

auth is { method = "key_pair", private_key_file = "..." }, { method = "pat", token = "${ENV:SNOWFLAKE_TOKEN}" }, or { method = "oauth_authorization_code" }. See Signing in.

Signing in

method	Use it for	Notes
key_pair	Unattended runs: schedulers, CI, servers.	The only method Snowpipe insertFiles accepts.
pat	Unattended runs.	A programmatic access token.
oauth_authorization_code	A person running SQLLoad at a terminal.	From 0.28.0. Signs in in the browser.

From 0.28.0, { method = "oauth_authorization_code" } signs you in through Snowflake’s built-in SNOWFLAKE$LOCAL_APPLICATION integration, so nobody has to create a security integration. The first command that connects prints a sign-in address and opens it in your browser. You sign in there as the connection’s user, with whatever your account asks for: password and MFA, or single sign-on. The browser then returns to SQLLoad on 127.0.0.1. Where no browser can open, as over SSH, SQLLoad prints the address and waits for you to paste the address the browser ended on (a page on 127.0.0.1 that fails to load).

SQLLoad asks for the connection’s role and a refresh token, caches the tokens in ~/.cache/sqlload/snowflake-tokens.json (under XDG_CACHE_HOME when set; under %LOCALAPPDATA% on Windows), and refreshes them before they expire. On Linux and macOS only you can read that file. Later runs sign in again only once the refresh token has expired or been revoked. Deleting the file forgets every connection’s sign-in on this machine; the refresh tokens stay valid at Snowflake until they expire. sqlload debug shows the method and the cache file. With no terminal, or with plan --json or contract ... --json, a run that needs to sign in fails at once instead of waiting; sign in once from a terminal, or use key_pair or pat.

Password and MFA are supported through this browser sign-in, not as a direct login: SQLLoad never sees your password. A security integration of your own can stand in for the built-in one:

auth = { method = "oauth_authorization_code", client_id = "...", client_secret = "${ENV:OAUTH_SECRET}", redirect_uri = "http://127.0.0.1:8765/" }

The redirect URI must be the one the integration registered, on http://127.0.0.1; a plain http redirect needs OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE on the integration.

External stage

Key	Meaning
url	The stage’s location, s3://bucket/prefix/. Use the bucket and folder the stage reads, not a subfolder.
storage_integration	SQLLoad creates _SQLLOAD_STAGE in each loader schema with this integration, at apply.
name	Or an existing stage on url, as DATABASE.SCHEMA.STAGE. Give exactly one of storage_integration and name.
region	The bucket’s region, for SQLLoad’s own writes.
keep_staged_files	Keep staged files after their batch commits, and leave this stage out of the cleanup. Default false.

SQLLoad writes the Parquet it stages with your AWS credentials, found as for an s3 connection: AWS_* environment variables, the shared credentials file under AWS_PROFILE (from 0.27.0), web identity, or the instance or container role. Snowflake reads the bucket through the stage.

Staged files are deleted once their batch commits. The next load removes what failed runs left, after a day.

Files into Snowflake

Files of a files source (Parquet, JSON, NDJSON or CSV) under the external stage’s url load directly: Snowflake reads them itself, and nothing passes through SQLLoad. Live tests check that this gives the same values as SQLLoad’s own reader.

• A column the direct COPY can’t load exactly, such as Float16, a decimal over 38 digits or a nested type, fails plan, or the load for a later batch, naming the column.
• Files no external stage covers fail plan, unless the source sets copy_to_internal_stage = true. Then SQLLoad downloads each file and uploads it unchanged to @~, where Snowflake loads it. That’s much slower than an external stage, and plan warns.
• A hash-masked column reaches Snowflake in plain text, in a temporary table that ends with the session, and is hashed there. An hmac key must never appear in SQL, so hmac-masked files go through SQLLoad instead, which plan warns is much slower.
• From 0.35.0, CSV encoding and skip_rows go into the COPY as ENCODING and SKIP_HEADER. An encoding Snowflake doesn’t support fails plan. See CSV encodings.

Files are never read and re-uploaded by SQLLoad otherwise.