Skip to content

Sources

Snowflake

Load Snowflake tables and query results: choosing tables, query tables, read windows around now, and where unloaded files go.

A Snowflake source loads tables and views of one database, and the results of queries you write. Snowflake unloads each read as Parquet with COPY INTO, and SQLLoad reads the files and loads them into the source’s target. A DuckDB target reads them from S3 itself, and a Snowflake target loads the rows inside Snowflake; see Snowflake loads the rows itself.

[connections.warehouse]
kind = "snowflake"
account = "myorg-myaccount"
user = "loader"
auth = { method = "pat", token = "${ENV:SNOWFLAKE_TOKEN}" }
warehouse = "LOAD_WH"
database = "ANALYTICS"
[sources.shop]
kind = "snowflake"
connection = "warehouse"
description = "Order tables of the shop's warehouse."
include = ["SHOP.ORDERS"]
write_strategy = "replace"
[sources.shop.tables."SHOP.ORDERS"]
description = "Orders, one row per order."
cursor_column = "order_date"
write_strategy = "delete_insert"
lookback = "7d"
lookahead = "7d"
lookback_from = "now"
[sources.shop.tables.big_orders]
description = "Orders of 500 or more."
sql = "SELECT ORDER_ID, CUSTOMER_ID, PLACED_AT, TOTAL FROM SHOP.ORDERS WHERE TOTAL >= 500"

A connection that only a source reads needs no load_method. Set one when a target loads into the same connection.

$ sqlload plan -v
Plan ready 2 selected, 2 objects to create, 2 to load
to DuckDB local
Objects (2) sqlload apply
├── raw.shop__orders create table
│ ├── order_id int -> BIGINT
│ ├── customer_id int -> BIGINT
│ ├── order_date date -> DATE
│ ├── placed_at timestamp_tz -> TIMESTAMP WITH TIME ZONE (stored as a UTC instant; the offset is not kept)
│ ├── total decimal(12,2) -> DECIMAL(12,2)
│ ├── items json -> JSON
│ └── + _sqlload_load_id, _loaded_at
└── raw.shop__big_orders create table
…
Loads (2) sqlload load
├── raw.shop__big_orders replace
│ ├── source snowflake shop query big_orders
│ ├── route read as Arrow -> DuckDB takes Arrow
│ …
└── raw.shop__orders delete_insert (cursor: order_date)
├── source snowflake shop SHOP.ORDERS
├── route unload Parquet to internal stage @~/sqlload_unload/ -> download unchanged to a local temporary folder -> DuckDB reads them (nothing through the loader)
├── reads order_date from 2026-11-01 (now minus lookback 7days), below 2026-11-16 (now plus lookahead 7days)
├── stage internal: no stage is set, so Snowflake writes to your user stage, and SQLLoad fetches the files in parallel, decrypts them and removes them after the read
└── …

include and exclude are SCHEMA.TABLE patterns of the connection’s database, matched without regard to case (* matches any text). Views match too. A [sources.shop.tables."SCHEMA.TABLE"] entry sets options for one table; on its own, it doesn’t add the table. Write its key as Snowflake stores the name, normally in upper case.

A source needs at least one include pattern or one query table.

Snowflake stores unquoted names in upper case. A column such as ORDER_ID loads as order_id. A name created in quotes with other characters, such as "OrderId", loads exactly as written.

A table entry with sql is a query table: its rows are the query’s result, and the entry’s key is its name. SQLLoad reads the query’s columns at plan without reading any rows.

cursor_column names a column of the query’s result. SQLLoad wraps the query and filters on the cursor outside it, so the query itself needs no placeholder.

where narrows a table to the rows that match a condition, written in Snowflake SQL:

[sources.marts]
where = "REGION = 'EU'"
[sources.marts.tables."SHOP.ORDERS"]
where = "REGION = 'EU' AND STATUS <> 'VOID'"

SQLLoad ANDs it into every SELECT it reads the table with, next to the cursor filter: cursor reads, batch_size windows, --reload, replace, and the key scan of load --sync-deletes. The table keeps its primary key, so unique_key = "{primary_key}" still works, and the database can use its indexes. On a source it applies to every table include matches; a table’s own where replaces it. plan checks the condition on the source, counting no rows (AND 1 = 0) and shows it on a filter line, so a typo fails plan, naming the table.

A row that later stops matching isn’t removed from a merge, append or delete_insert table; it stops being updated. replace drops it, and so does deletes with load --sync-deletes (see Rows deleted at the source).

A query table refuses where: put the condition in its sql.

A table with a cursor_column filters the query Snowflake unloads, so only the rows in the read window leave Snowflake. The window starts at the destination’s maximum, less any lookback. To read a fixed window around the time of the run instead, such as 7 days back to 7 days ahead, set lookback_from = "now" and lookahead; see Reload a window.

stage Snowflake writes to SQLLoad reads with
Omitted A folder of your user stage, @~/sqlload_unload/ The temporary credentials Snowflake hands out, several files at a time
{ url = "s3://example-bucket/unload/", storage_integration = "S3_UNLOAD", region = "eu-west-2" } That S3 folder, through the storage integration Its AWS credentials, found as for an s3 connection (the shared credentials file)
{ name = "ANALYTICS.SHOP.UNLOAD_STAGE" } That stage, internal or external on S3 As for the rows above

The user stage and other internal stages work on Snowflake accounts hosted on AWS. A named internal stage must use Snowflake’s default client-side encryption; one created with ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') is refused when the table loads.

Each read unloads into a folder of its own, which SQLLoad removes after the read, when the read fails too. A read fails when the files hold a different number of rows than COPY INTO reports. A run that is killed can leave its folder behind; remove such folders with REMOVE, or with a lifecycle rule on the bucket.

With an external stage, SQLLoad needs to list, read and delete objects under the folder. The storage integration must allow Snowflake to write there.

With a stage given by url and a DuckDB target, DuckDB reads the unloaded files from S3 itself, so no rows pass through SQLLoad. plan shows it in the route:

route unload Parquet to s3://example-bucket/unload/ -> DuckDB reads them (nothing through the loader)

DuckDB uses the same S3 credentials as SQLLoad, and turns times and timestamps back exactly, whatever its session time zone. A load fails before it commits when DuckDB loads a different number of rows than COPY INTO reports, and the folder is removed either way. A table without a cursor commits once per run rather than about every 1 GB.

Databricks and ClickHouse read the files themselves

Section titled “Databricks and ClickHouse read the files themselves”

With a stage given by url, Databricks and ClickHouse targets read the unloaded files from S3 themselves too, so no rows pass through SQLLoad:

route unload Parquet to s3://example-bucket/unload/ -> Databricks COPY INTO (nothing through the loader)
route unload Parquet to s3://example-bucket/unload/ -> ClickHouse reads them (nothing through the loader)
  • Databricks COPYs the files through the Unity Catalog external location that covers the url, as for Parquet files in S3. A url no external location covers fails plan with the fix; the files never go through SQLLoad instead. Each batch lands in a staging or build table, an append too, and publishes only when the COPYs load as many rows as COPY INTO reported unloading.
  • ClickHouse reads each file with s3() into the load’s staging table, signed with the same S3 credentials as SQLLoad, and publishes only when it loads as many rows as were unloaded.

Timestamps come back exactly from their whole microseconds, whatever the session’s time zone. A table with a TIME column, or a mask, goes through SQLLoad into either target, and plan says why: neither stores a time of day as a time.

Into a Snowflake target, no rows pass through SQLLoad in two cases:

  • The target writes through the source’s connection: the same connection, with no other database, and the source sets no warehouse of its own. Snowflake selects the rows with one INSERT ... SELECT on the connection’s warehouse, and nothing is unloaded.
  • The source’s stage url is inside the target’s external stage url, on another connection or account. Snowflake unloads the files there, and the target COPYs them through its own stage. The load commits only when it loads as many rows as COPY INTO reported unloading. An append that writes straight into its table fails, before loading anything, when one read unloads too many files to COPY in one statement (thousands); a smaller batch_size splits the read.

plan shows which:

route Snowflake INSERT ... SELECT on the source's connection (nothing through the loader)
route unload Parquet to s3://example-bucket/stage/unload/ -> Snowflake COPY (nothing through the loader)

Otherwise SQLLoad reads the files and stages them for the target, as for any other source: with the user stage, a named stage, or an S3 folder the target’s stage doesn’t cover. A table with a mask always goes through SQLLoad, which masks the values. Times and timestamps come back exactly, as with DuckDB.

Without a stage SQLLoad downloads the files from the user stage unchanged, 16 at a time, into a temporary folder, and DuckDB reads them from there:

route unload Parquet to internal stage @~/sqlload_unload/ -> download unchanged to a local temporary folder -> DuckDB reads them (nothing through the loader)

The folder, named sqlload-unload-…, is in the system’s temporary directory (TMPDIR). It needs room for one read’s files (the whole table, or one window with batch_size) for each table loading at once, up to concurrency. Where the temporary directory is in memory (tmpfs), so are the files. The folder is removed after the read, when the read fails too; a run that is killed can leave it behind.

SQLLoad reads the files instead, and plan says why, for a table with masked columns or checks before publish, for a stage given by name, and for targets other than DuckDB.

The role needs USAGE on the warehouse, database and schemas, and SELECT on the tables and views. An external stage given by url also needs USAGE on the storage integration; one given by name needs USAGE on the stage, or READ and WRITE on an internal one.

Snowflake Loads as
NUMBER(p,0), p up to 18 int
NUMBER(p,s) otherwise, including INTEGER (NUMBER(38,0)) decimal(p,s)
FLOAT float
VARCHAR(n); default length varchar(n); text
BOOLEAN, DATE, TIME, BINARY bool, date, time, binary
TIMESTAMP_NTZ timestamp_ntz
TIMESTAMP_TZ, TIMESTAMP_LTZ timestamp_tz, the instant in UTC
VARIANT, OBJECT, ARRAY json

Times and timestamps keep their microseconds. Other types, such as GEOGRAPHY, need a pinned type, such as columns = { area = "text" }. Pin int on an INTEGER column whose values fit 64 bits, such as columns = { order_id = "int" }. See Types.