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. On Windows also a drive path ('C:\data\lake') or a network share ('\\server\share\lake'); write it as a TOML literal string, in single quotes, so the backslashes stay.

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(), and from 0.36.0 for sl.oauth2_password(...).
client_id, client_secret	From 0.27.0. For sl.oauth2_client_credentials(...), and for sl.oauth2_refresh_token(...) or sl.oauth2_password(...) 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.
naming	warehouses	From 0.40.0. sqlload (default) or dlt: how its tables name the columns a load adds and the child tables unnest makes; dlt also names the tables and columns themselves in dlt’s snake_case. A source or table may override it. See Naming.

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.
max_batch_memory	odbc	From 0.52.0. The fetch buffer of each table: the most the rows of one fetch hold, such as "2GB"; 512 MiB, or less on a small machine, when omitted. It doesn’t bound the load’s whole memory. See ODBC.
long_column_threshold	odbc	From 0.52.0. The buffer per value over which a text or binary column is read value by value; "4KiB" when omitted. 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.
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	required	append, merge, delete_insert or replace. From 0.46.0 every table needs one, from its own entry or its source; plan refuses a table without it. See Write strategies.
unique_key	required for merge	A column or list of columns, or "{primary_key}" for the primary key the source reports (not for Python, files or Google Sheets sources). From 0.46.0 a merge table needs one. See merge.
on_duplicate_keys	dedupe	From 0.39.0. What merge does with rows of one commit that repeat a key: dedupe keeps one row per key, fail refuses the commit. See Repeated keys.
null_keys	match	From 0.51.0. What merge does with a NULL in a unique_key column, and delete_insert in a replace_key column: match matches it with a NULL, so the row is updated rather than inserted again; fail refuses a commit holding one. See NULLs in keys.
replace_key	none	From 0.51.0. A column or list of columns whose values delete_insert replaces whole: each commit deletes the rows holding a value its rows hold, then inserts them. Not unique. Only for delete_insert, and not with a cursor_column: each load reads the whole source, or a files source’s new and changed files. See Replacing rows by a key.
dedup_sort	none	From 0.39.0. Which repeated row merge keeps: { column = "desc" } the highest value, "asc" the lowest; without it, the row read last.
cursor_column	none	The column runs continue from. From 0.51.0, a list such as ["updated_at", "created_at"] reads the first non-NULL column of each row. See Cursors.
cursor_start	none	Where the first run starts.
lookback	none	Read this far before the maximum: a duration on a date or timestamp cursor, such as "2h"; from 0.52.0, a number of cursor units on an integer cursor, such as 1. See Cursors.
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.
cursor_bounds	parameters	From 0.47.0, ODBC sources only. parameters binds the cursor’s bounds as typed parameters; literals writes them into the query as ODBC escapes ({ts '…'}, {d '…'}), for a driver that refuses a bound value. See Cursor bounds.
null_cursor	fail	From 0.51.0. What an incremental read does with source rows whose cursor is NULL: fail fails the load when there are any, include reads them on every run (merge only), ignore skips them. See NULL cursor values.
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.
constant_columns	none	The same value in every row, such as { tenant_id = "acme" }, typed by its TOML value or { value, type }. From 0.26.0 on files sources; from 0.37.0 on every source but postgres_cdc, and per table, where a table’s map replaces its source’s. A files source replaces a file column of the same name, and plan notes it; every other source fails at plan when it already has a column of the name, ignoring case, before anything is written. See Several tenants in one table.
unnest	none	From 0.40.0. In a table entry only: the dotted paths of the list fields whose items become rows of child tables, such as ["items", "items.parts"], or "all". See Nested data.
max_depth	1000	From 0.40.0. In a table entry with unnest: how many levels of child tables it makes at most.
naming	the target’s	From 0.40.0. sqlload or dlt. See Naming.
row_id	false	From 0.40.0. Give every row a unique id, in _sqlload_row_id. Always on with unnest and under naming = "dlt", where it is _dlt_id.
transform	none	From 0.52.0, files and google_sheets sources. The name a Python function was registered under with @sl.transform (its own name, or the one given as @sl.transform("kpis")) in a module under python_paths, run on each file’s or range’s rows before they load. plan fails on a name no function has, or two do. Needs python_paths and the sqlload Python package. An sl.http job takes transform= instead. See Transforming each file and Transforming each range.
column_names	none	From 0.49.0. The columns’ names, in order, for data without a header row: CSV and Excel files with header = false, and google_sheets ranges with header = false. The count must match the data’s columns. See Naming headerless columns.

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.