Skip to content

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.

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.

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

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 schema. Required.
database Overrides the connection’s database. Not for ClickHouse.

A source’s 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.

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 Databricks
staging_dir = "/data/sqlload" # where the rows wait; the system's temporary folder by default

A 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.