Targets
Targets and load methods
Where loaded tables go, and how data gets there: files the warehouse reads itself, stages and bulk COPY.
The fastest load is rarely reading rows and inserting them. It’s letting the warehouse bulk-load
files: straight from your bucket or folder when the data is already in files, or from a stage that
SQLLoad fills with Parquet. SQLLoad picks that route wherever it loads the data exactly, and plan
names the route for every table.
How data gets in
Section titled “How data gets in”| Into | Parquet files in S3, GCS or a folder | CSV, JSON, NDJSON files | Postgres or Python rows |
|---|---|---|---|
| DuckDB | DuckDB reads them with read_parquet (in GCS, with an HMAC key) |
Read by SQLLoad, appended as Arrow | Appended as Arrow |
| MotherDuck (beta) | In S3, or in GCS with an HMAC key: MotherDuck’s servers read them with read_parquet. Otherwise read by SQLLoad, appended as Arrow |
Read by SQLLoad, appended as Arrow | Appended as Arrow |
| Snowflake | In S3: COPY from an external stage over the bucket. Otherwise read by SQLLoad, then loaded as rows are |
In S3: COPY from an external stage over the bucket. Otherwise as for Parquet files |
Written as Parquet to the load_method’s stage, then COPY (or streamed or inserted, if the load_method says so) |
| Databricks (beta) | In S3: COPY INTO where they lie, through an external location. In a folder: staged by SQLLoad in the volume, then COPY INTO |
Read by SQLLoad, staged as Parquet in the volume, then COPY INTO |
Written as Parquet to the volume, then COPY INTO |
| BigQuery (beta) | In GCS, on a table that excludes _sqlload_source_row: BigQuery queries them where they lie into a staging table. Otherwise read by SQLLoad, streamed as Arrow into a staging table, then one query job |
As for Parquet files | Streamed as Arrow into a staging table, then one query job. A BigQuery source on the target’s own connection: BigQuery selects the rows with INSERT ... SELECT |
| ClickHouse | In S3: ClickHouse reads them with s3() into a staging table. In a folder: read by SQLLoad, sent as Arrow |
Read by SQLLoad, sent as Arrow into a staging table | Sent as Arrow into a staging table, then swapped in by table or partition |
| Postgres | Read by SQLLoad, then binary COPY into a temporary staging table |
As for Parquet files | Binary COPY into a temporary staging table, then one transaction per commit |
A Snowflake source loading into Snowflake stays inside Snowflake: an INSERT ... SELECT on the
source’s own connection, or a COPY of its unloaded files through the target’s external stage.
See Snowflake loads the rows itself.
Into DuckDB, Databricks and ClickHouse, a Snowflake source unloaded to an S3 url is read by the
target itself; see Databricks and ClickHouse read the files themselves.
There are no silent fallbacks. If the route a table needs can’t be used, plan fails for that
table and says how to fix it.
Targets
Section titled “Targets”A target is where loaded tables go. It names a connection and where tables are placed in it. A
project can have several targets, for example one per environment; each source loads into its
destination, or the project’s default_target.
Connection kind |
Target writes | Places tables with |
|---|---|---|
snowflake |
Snowflake tables | loader_schema, and optionally database |
databricks (beta) |
Unity Catalog tables | loader_schema, in the connection’s catalog |
bigquery (beta) |
BigQuery tables | loader_schema as the dataset, in the connection’s project |
clickhouse |
ClickHouse tables, on one self-hosted server | loader_schema as the database |
postgres |
Postgres tables | loader_schema, and optionally database |
duckdb |
DuckDB tables | loader_schema |
motherduck (beta) |
MotherDuck tables | loader_schema, and optionally database |
s3, filesystem |
Parquet or CSV files, one folder per table | path |
Warehouse targets
Section titled “Warehouse targets”A Snowflake, Databricks, BigQuery, ClickHouse, Postgres or DuckDB target places tables in a schema (a dataset in BigQuery, a database in ClickHouse):
[connections.warehouse]kind = "duckdb"database = "shop.duckdb"
[targets.local]connection = "warehouse"loader_schema = "raw"| Key | Meaning |
|---|---|
connection |
A snowflake, databricks, bigquery, clickhouse, postgres or duckdb connection, or a motherduck connection. |
loader_schema |
The schema for tables whose source sets no destination_schema. Required. |
database |
Overrides the connection’s database. Not for ClickHouse. |
A source’s destination_schema overrides loader_schema for its tables. A DuckDB connection’s database is
a file path, or :memory:. A Snowflake connection also needs a load_method; see
Snowflake. A Postgres target uses the same connection kind as a
Postgres source.
Each kind has its own page: Snowflake, Databricks, BigQuery, ClickHouse, Postgres, DuckDB, MotherDuck and Parquet files.
Sending rows at commit
Section titled “Sending rows at commit”send says when a table’s rows reach its target. With send = "at_commit", each
commit’s rows wait on the machine running SQLLoad, in local Arrow files, and reach the target in one
burst just before the commit: the target runs nothing while the source is read. With
send = "per_batch", each batch goes to the target as it’s read.
Snowflake, Databricks and MotherDuck bill for the time a warehouse runs queries, and keep it
running a while after the last one (a minute’s minimum after Snowflake resumes, MotherDuck’s
cooldown, a Databricks SQL warehouse’s auto-stop). A slow read sending batch after batch keeps them
billing for the whole read, so at_commit is the default there. Every other target defaults to
per_batch.
[targets.cloud]connection = "md"loader_schema = "raw"send = "at_commit" # the default on MotherDuck, Snowflake and Databricksstaging_dir = "/data/sqlload" # where the rows wait; the system's temporary folder by defaultA source or table can set send too. Commits stay where they are: once after the read for
replace and --reload, after each window for batch_size, about every 1 GB for incremental reads,
and every 1,000 files or about 1 GB for files sources. A read that fails part-way sends nothing for
its commit, and the held files are removed after each commit, failed or not. plan shows a send
line for each table under at_commit, with about how much a commit holds where the source gives a
size, and load fails before reading when staging_dir has less room than that.
send doesn’t apply where the target reads the files itself, such as Parquet files DuckDB reads or
an external_stage Snowflake copies from: each commit there is already one COPY, and plan says
send n/a: <target> reads the files itself. A column added part-way through a read sends the rows
held so far before the table is altered.