sqlload.toml

sqlload.toml is a loader project’s configuration. sqlload reads it from the working directory, or from -c/--config or SQLLOAD_CONFIG. Unknown keys are errors. See Projects for how the parts fit together.

${ENV:NAME} anywhere in the file is replaced with the environment variable NAME; an unset variable is an error. $${ is a literal ${.

The Kafka exporter uses a different file format.

Top level

Key	Default	Meaning
python_paths	none	Folders, relative to the file, scanned for Python sources.
default_target	none	The target for sources that name no destination. Not needed with one target.
concurrency	4	The most tables loading at once. --concurrency overrides it.
[connections.NAME]		How to reach a system.
[targets.NAME]		Where loaded tables go.
[sources.NAME]		Where data comes from.

Connections

Each connection has a kind.

postgres

For Postgres sources, and for Postgres targets too.

Key	Default	Meaning
host	required
port	5432
user	required
password	none	Use ${ENV:NAME}.
database	required
sslmode	prefer	disable, prefer, require, verify-ca or verify-full, as in libpq. See TLS.
sslrootcert	none	CA certificates (PEM) to trust, instead of the public roots built into SQLLoad.
sslcert, sslkey	none	A client certificate and its private key (PEM). Set both or neither.
ssh	none	From 0.27.0. A bastion to reach host through. See SSH tunnels.

The ssh table, from 0.27.0:

Key	Default	Meaning
host	required	The bastion.
port	22
user	required
private_key_file	ssh-agent	The private key to sign in with. ~/ is the home folder. Without it (and without password), SQLLoad tries each key ssh-agent holds.
private_key_passphrase	none	Use ${ENV:NAME}.
password	none	From 0.32.0. Signs in when no private_key_file is set. Use ${ENV:NAME}.
known_hosts	~/.ssh/known_hosts	The file the bastion’s host key must be listed in.

snowflake

Key	Default	Meaning
account	required	The account identifier, such as myorg-myaccount.
user	required
auth	required	{ method = "key_pair", private_key_file = "..." }, { method = "pat", token = "${ENV:...}" }, or, from 0.28.0, { method = "oauth_authorization_code" } to sign in in the browser. See Signing in.
role	none
warehouse	none
database	required
load_method	required for targets	How data gets into Snowflake. From 0.26.0, a connection only a source reads needs none. See Load methods.

databricks

Key	Meaning
host, client_id, client_secret, warehouse_id, catalog, volume	See Databricks. All required.

bigquery

Key	Default	Meaning
location	required	Where query jobs run and datasets are created, such as EU.
service_account_key	required	The service account key file’s JSON text. Use ${ENV:NAME}.
project	the key’s project	The project tables load into and jobs run in.

See BigQuery.

clickhouse

Key	Default	Meaning
host	required
port	8123, or 8443 with secure	The HTTP port.
user	default
password	none	Use ${ENV:NAME}.
secure	false	true for HTTPS.

See ClickHouse.

duckdb

Key	Meaning
database	The database file, or :memory:.

motherduck

Available as a beta from 0.33.0. From 0.34.0 it connects through a separate DuckDB 1.5 process: install sqlload[motherduck]; see MotherDuck.

Key	Meaning
database	The MotherDuck database, with or without md:. Required, here or in the target. It can’t hold the token.
token	A MotherDuck access token. Use ${ENV:NAME}. Default: the motherduck_token or MOTHERDUCK_TOKEN environment variable.

s3

Key	Meaning
bucket	The bucket.
region	The bucket’s region.
endpoint	A custom S3-compatible endpoint.
profile	From 0.27.0. The profile of the AWS shared credentials file to take keys from. Default: AWS_PROFILE, else default.

SQLLoad takes the first credentials it finds, in this order:

1. AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY (with AWS_SESSION_TOKEN) in the environment.
2. From 0.27.0, keys in the shared credentials file (AWS_SHARED_CREDENTIALS_FILE, default ~/.aws/credentials), under the connection’s profile, else AWS_PROFILE, else default.
3. Web identity.
4. The instance or container role.

From 0.27.0, a profile you name, with profile or AWS_PROFILE, must have aws_access_key_id and aws_secret_access_key in that file; otherwise the connection fails rather than trying the next source. SSO, assumed roles and credential_process in ~/.aws/config aren’t read. Also from 0.27.0, sqlload debug shows where each S3 connection’s keys come from, never the keys.

SQLLoad removes files in bulk, up to 1,000 per request. For an S3-compatible store without bulk delete, set AWS_DISABLE_BULK_DELETE=true: SQLLoad then deletes each file with its own request.

filesystem

Key	Meaning
root	A folder on the machine SQLLoad runs on.

sftp

From 0.32.0. An SFTP server that files sources read from.

Key	Default	Meaning
host	required
port	22
user	required
private_key_file	none	The private key to sign in with. ~/ is the home folder.
private_key_passphrase	none	Use ${ENV:NAME}.
password	none	Signs in when no private_key_file is set. Use ${ENV:NAME}. Without either, SQLLoad tries each key ssh-agent holds.
known_hosts	required	The file the server’s host key must be listed in. SQLLoad never trusts a server on first use, and host key checks can’t be turned off.

odbc

From 0.27.0. See ODBC.

Key	Meaning
connection_string	The driver’s connection string. Use ${ENV:NAME}.
dsn	A data source name, instead of connection_string.
user, password	With dsn, when the DSN doesn’t hold them. Set connection_string or dsn, not both.

http

For Python HTTP sources; its settings reach table functions as ctx.connection.

Key	Meaning
base_url	The API’s base URL.
token	For sl.bearer() and sl.api_key(...).
username, password	For sl.basic().
client_id, client_secret	From 0.27.0. For sl.oauth2_client_credentials(...), and for sl.oauth2_refresh_token(...) when the API wants the client too.
refresh_token	From 0.27.0. For sl.oauth2_refresh_token(...).
headers	Headers sent with every request.

Targets

Key	Applies to	Meaning
connection	all	A snowflake, databricks, bigquery, clickhouse, postgres, duckdb, s3 or filesystem connection, or from 0.33.0 a motherduck connection.
loader_schema	warehouses, required	The schema for tables whose source sets no schema; a BigQuery target’s dataset, a ClickHouse target’s database.
database	warehouses except ClickHouse	Overrides the connection’s database.
path	files	Each table’s folder template, with {source}, {schema}, {table}, {cursor} and {cursor:date}. Default {source}/{table}/.
format	files	parquet (default), or from 0.27.0 csv.
compression	files, csv	From 0.27.0. none (default), gzip or zstd.
csv	files, csv	From 0.27.0. { delimiter, header, quote, null_value }; default ,, true, " and an empty string.
single_file	files	From 0.27.0. One file per table or partition, named after the table folder. Default false.
max_footer_reads	files	The most Parquet footers plan reads for one table’s schema. Default 100.

Sources

Key	Applies to	Meaning
kind	all	postgres, postgres_cdc, snowflake (from 0.26.0), odbc (from 0.27.0), bigquery (from 0.30.0) or files. Python sources are declared in Python.
connection	all	The connection the source reads.
description	all	The source’s description; a files source’s table description.
destination	all	The target; default_target when omitted.
schema	warehouse targets	The destination schema; the target’s loader_schema when omitted.
table_name	all	The destination name template, with {source}, {schema} and {table}. Default {source}__{table}, or {source} for files.
destination_path	files targets	The folder template for this source’s tables, overriding the target’s path.
include, exclude	postgres, postgres_cdc, snowflake, odbc, bigquery	schema.table patterns to sync and to leave out (dataset.table for bigquery).
new_tables	postgres	sync (default), skip or fail. See Sources.
cdc_mode, deletes, slot, publication, batch_interval	postgres_cdc	See Postgres CDC settings.
exclude_columns, allow_columns	all	Column patterns to leave out, and to keep anyway.
skip_tables_with_columns	postgres	Leave out tables with a column matching one of these patterns.
mask_key	all	The key for hmac masks, normally ${ENV:MASK_KEY}.
warehouse	snowflake	From 0.26.0. The warehouse unloads run on; the connection’s when omitted.
stage	snowflake	From 0.26.0. Where unloads go: { url, storage_integration, region } or { name }; the user stage when omitted. See Snowflake.
max_text_size, max_binary_size	odbc	From 0.27.0. The longest text and binary value a read holds, in bytes; caps every text and binary column. See ODBC.
project	bigquery	From 0.30.0. The project the tables live in; the connection’s when omitted.
path	files	The folder holding the files, relative to the connection.
format	files	parquet (default), ndjson, json or csv.
compression	files	auto (default), none, gzip or zstd.
csv	files	{ delimiter, header, quote, null_values }; defaults ",", true, '"', [""].
shape	files	From 0.27.0. columns (default) or record: each JSON object or CSV line whole in one json column. See Whole records.
record_column	files	From 0.27.0. The column shape = "record" loads into; record when omitted.
partition_columns	files	Hive key=value folders as typed columns, such as { dt = "date" }.
path_columns	files	From 0.26.0. A path template whose captures load as typed columns, such as "{region}/{day:date}/*". See Columns from paths.
unmatched_paths	files	From 0.26.0. fail (default) or skip: what a file whose path doesn’t match path_columns does.
constant_columns	files	From 0.26.0. The same value in every row, such as { region = "eu" }, typed by its TOML value or { value, type }.
copy_to_internal_stage	files into Snowflake	Upload files no external stage covers to @~. Much slower.
[sources.NAME.tables."schema.table"]	all	Per-table settings. A files source’s table is named after the source.

A source can also set any table setting, as the default for its tables.

Postgres CDC settings

A postgres_cdc source takes these:

Key	Default	Meaning
kind		postgres_cdc.
connection		A postgres connection.
include, exclude		schema.table patterns, as for a Postgres source.
cdc_mode	merge	merge keeps one row per key; changelog appends every change. A files target takes changelog only.
deletes	soft	merge only: soft marks a deleted row with _sqlload_deleted_at; hard removes it.
slot	sqlload_<source>	The replication slot.
publication	sqlload_<source>	The publication. SQLLoad creates sqlload_<source> when it’s missing.
batch_interval	10s, 1m into files	With load --follow: how often it writes what it has read. At least 1s.

Column settings (columns, exclude_columns, mask and the rest) work as for other sources. A captured table takes no write_strategy, cursor_column, batch_size or state. merge matches rows on the primary key, and a unique_key must lie within it.

Table settings

Set on a source for all its tables, or in a [sources.NAME.tables."schema.table"] entry for one.

Key	Default	Meaning
write_strategy	from the cursor and key	append, merge, delete_insert or replace. See Write strategies.
unique_key	the primary key	A column or list of columns, for merge.
cursor_column	none	The column runs continue from. See Cursors.
cursor_start	none	Where the first run starts.
lookback	none	Read this far before the maximum, such as "2h".
lookback_from	destination	From 0.26.0. destination (the destination’s maximum) or now: what lookback counts back from. See Reload a window.
lookahead	none	From 0.26.0. Read only cursor values before now plus this; a date cursor reads up to and including today plus that many days.
append_cursor_inclusive	false	Whether append reads from the maximum itself (inserting its rows again) rather than after it.
batch_size	none	Commit one cursor window at a time: a duration ("1d") or an integer step, or "none".
contract	none	none or enforced. See Contracts.
on_schema_change	append_new_columns	append_new_columns, ignore or fail.
auto_widen	false	Widen inferred columns along bool → int → decimal, timestamp_ntz → timestamp_tz.
type_change_rebuild	require_confirmation	allow, require_confirmation or deny.
checks	none	{ unique, not_null, min_rows, max_row_drop, warn }, run on the staged rows before they’re published. See Checks before publish.

ClickHouse targets also take these, for the tables SQLLoad creates. Other targets refuse them, and Python tables don’t take them. See Table layout.

Key	Default	Meaning
engine	MergeTree	The table engine with its arguments, of the MergeTree family.
order_by	the unique_key, else unsorted	The sorting key: an expression or a list of them.
partition_by	none	The partitioning expression, such as "toYYYYMM(updated_at)".
primary_key	none	A prefix of order_by.
table_settings	none	Table settings by name, such as { index_granularity = 4096 }.

Only in a table entry:

Key	Meaning
description	The table’s description, which becomes its comment.
name	The destination table name, overriding table_name.
sql	A query whose rows are the table (snowflake sources from 0.26.0; odbc sources from 0.27.0; bigquery sources from 0.30.0); the entry’s key is its name.
columns	Pinned column types, such as { total = "decimal(18,2)" }. See Types.
mask	Masked columns: { email = "hmac" } or "hash".
exclude_columns, allow_columns, destination_path	As on the source, for this table.

Python tables take the same settings as decorator arguments, except lookahead and lookback_from, which they refuse, plus allow_empty, state and flatten. See Python sources.