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 -vPlan ready 2 selected, 2 objects to create, 2 to loadto 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 └── …Choosing tables
Section titled “Choosing tables”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.
Query tables
Section titled “Query tables”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.
Filtering rows
Section titled “Filtering rows”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.
Read windows
Section titled “Read windows”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.
Where the files go
Section titled “Where the files go”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.
DuckDB reads the files itself
Section titled “DuckDB reads the files itself”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 theurl, as for Parquet files in S3. Aurlno external location covers failsplanwith the fix; the files never go through SQLLoad instead. Each batch lands in a staging or build table, anappendtoo, and publishes only when theCOPYs load as many rows asCOPY INTOreported 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.
Snowflake loads the rows itself
Section titled “Snowflake loads the rows itself”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 otherdatabase, and the source sets nowarehouseof its own. Snowflake selects the rows with oneINSERT ... SELECTon the connection’s warehouse, and nothing is unloaded. - The source’s
stageurlis inside the target’s external stageurl, on another connection or account. Snowflake unloads the files there, and the targetCOPYs them through its own stage. The load commits only when it loads as many rows asCOPY INTOreported unloading. Anappendthat writes straight into its table fails, before loading anything, when one read unloads too many files toCOPYin one statement (thousands); a smallerbatch_sizesplits 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.
Permissions
Section titled “Permissions”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.