Targets
Parquet and CSV files
Write each table as zstd Parquet or CSV files in S3, Google Cloud Storage or a local folder, partitioned by a template.
A files target writes each table as Parquet or CSV files in its own folder.
[connections.out]kind = "filesystem"root = "out"
[targets.files]connection = "out"path = "{source}/{table}/dt={cursor:date}/"| Key | Meaning |
|---|---|
connection |
An s3, gcs or filesystem connection. |
path |
Each table’s folder: a template with {source}, {schema}, {table}, and {cursor} or {cursor:date} to partition by the cursor. Default {source}/{table}/. |
format |
parquet (default), or csv. See CSV files. |
compression |
CSV only: none (default), gzip or zstd. |
csv |
CSV only: { delimiter, header, quote, null_value }. Default { delimiter = ",", header = true, quote = '"', null_value = "" }. |
single_file |
Write one file per table, or per partition, named after the table folder. Default false. |
max_footer_reads |
The most Parquet footers plan reads for one table’s schema. Default 100. |
A source’s destination_path, or one table’s, overrides the target’s path.
$ sqlload plan -vPlan ready 1 selected, 1 to load
Loads (1) sqlload load└── app__orders delete_insert (cursor: updated_at) ├── source postgres app public.orders ├── reads updated_at: first run, everything ├── columns 5 ├── files out/app/orders/ parquet (zstd) partitions dt={cursor:date}/ ├── schema first files, 7 columns ├── note each partition written or in the window gets its new files first, then its older files are deleted; not atomic, so a reader in between sees both └── commits about every 1 GB, in updated_at order (an index leads with it, NULLs first), between distinct values; each commit replaces its own cursor range
✓ Plan complete 1 selected, 1 to load$ sqlload load✓ Plan complete 1 to load (sqlload plan shows the tree)
loaded out/app/orders/ 1,000 rows 0.1s
✓ Completed 1 loaded, 1,000 rows, 0.1s├── slowest out/app/orders/ 0.1s└── peak memory 66.2 MiBFiles targets take append, replace and delete_insert. merge needs a table format that
updates rows in place, so plan refuses it on a files target.
The table’s schema is the footers of the newest load in its newest partition. sqlload contract diff --all-files reads every file’s footer to report files whose columns differ. See
sqlload contract.
A postgres_cdc source loads into a files
target as a changelog, adding files with each load.
A files source loads into a files target.
SQLLoad reads the source files and writes them out again, with append, replace, or
delete_insert by a partition cursor. Each commit records the source files it loaded in
_sqlload/loaded/<commit>.json. Under append, the next load skips those files and reads only
new ones. With a partition cursor, append reads from the target’s newest partition onward, so
a late file added to an older partition isn’t loaded. As for other targets, a source file
rewritten in place fails the table until you reload the table with --reload, or use
delete_insert on a partition cursor. A record counts only while the table still holds its
commit’s files: the files of a load that failed and was repaired load again, and the next load
deletes records whose files are gone.
A record groups the source files by the partition folder their rows went to, and a
group counts only while the commit still has files in that folder. A delete_insert that replaces
one partition of an earlier multi-partition commit drops that partition’s files from the record,
and keeps the rest. Once a table has more than 64 records, a load merges them into one,
_sqlload/loaded/compacted.<id>.json, so planning reads a few records rather than one per commit.
A record in an older SQLLoad’s format, a list of files without their folders, can’t be read. A plan or load that finds one blocks the table rather than treat its files as never loaded:
…/_sqlload/loaded/<commit>.json is a record of loaded source files in the format of an older SQLLoad, which this one cannot read. Fix: load the table with --reload, which rewrites its files and recordsThe target folder can’t be the source folder, or inside it or around it: a load would delete or
read back its own source files, so plan blocks the table. A files source also can’t load into a
single_file target, as its one file carries no load id to record the source files against.
SQLLoad keeps its bookkeeping for each table under <table folder>/_sqlload/: markers for loads in
progress, the run log of state = "logged" tables, and the source files a files source’s
loads loaded. Run one load at a time per files table.
SQLLoad enforces that rule. A load takes a lease on the table, a
file in _sqlload/lease/, before it repairs or writes anything. It renews the lease every minute
and releases it when it finishes. While another load holds a live lease, plan blocks the table
and load refuses it:
the table is being loaded by load 3f9c… (started 2026-11-08 09:00:00 UTC, lease until 2026-11-08 09:10:00 UTC); retry once it finishesA lease lasts 10 minutes, so a load that crashed holds the table until then. The next load then repairs what it left. Before deleting older files, a load checks that it still holds the lease. If its lease lapsed, or another load took the table, it stops without deleting them.
A load also checks, once it holds the lease, that no other load committed to the
table or replaced its files since the table was planned. Under append and delete_insert,
what a load reads was chosen from the files at plan time, so a load planned before
another one finished would load the same source files or rows again. It stops before writing
instead, with an error a rerun clears:
orders: another load changed the table's files in out/orders/ after this load was planned, so what it would read may already be loaded; stopped before writing. Run the load againRepairing a load found writing leaves an aborted tombstone for 7 days. That load
may only have lost its lease and still be running: any file it stores later is deleted by the next
load, rather than counting as written. After 7 days the next load deletes the tombstone.
How each commit ended is a file in _sqlload/decided/, which is only ever created,
never overwritten: a load commits by creating it as replacing, and a load repairing the table
creates it as aborted (the tombstone). Of a load committing late and a load repairing it, only
one decides the commit: a repair that finds the commit already finished withdraws its aborted
and keeps the load’s files. If that repair stops before withdrawing it, the aborted still isn’t
taken for a tombstone: a tombstone keeps the load’s marker in _sqlload/pending/ beside it, and the
next load deletes an aborted without one, and nothing else. A load that checked its lease, then stalled past it while another load
repaired the table, fails when it resumes, without deleting any file:
…/_sqlload/decided/<commit>: this commit was already decided, by another load that repaired this table after this load's lease lapsed (or by this load's own retried write); stopped before deleting any filesThis uses the same create-only writes as the lease, so it needs no more of the store. A store that can’t do them fails the load rather than skip the check.
Replacing a table
Section titled “Replacing a table”In a local folder, replace and --reload write the new table into a hidden folder beside the
table folder, carry over every file of the table folder that the load doesn’t replace (the
_sqlload/runs/ log, the load’s lease, and any file you keep beside the data), then swap the two folders in one rename
(renameat2 with RENAME_EXCHANGE on Linux, RENAME_SWAP on macOS) and delete the old one. A
reader sees the old files or the new ones, never both. A hidden folder left by a stopped or failed
load is deleted by the next load. One ending in -old, which an earlier release left when it stopped
while moving the old table aside, is kept with a warning, as it may hold the table’s only copy.
A load checks that it still holds the table’s lease right before that rename, and writes nothing in between. A load that takes the table first moves every hidden folder other loads staged beside it away, in one rename each, then checks its own lease again before it repairs anything. The swapping load also checks that its hidden folder is the one it made when it started, right before the rename and again right after it, at the table path. So a folder another load moved away, which its later writes made again without its earlier files, is never swapped in. If the check after the rename fails, the load swaps the two folders back at once, with the same one-rename swap, and stops with an error a rerun clears, leaving the table as it was. Only filesystems with that swap stage a table, so undoing it is always one rename; should even that fail, the error says to check the table folder. A load that checked its lease, then paused past it while another load took the table, either swaps its table in before that move, or finds its hidden folder gone and fails at the rename, leaving the table as it was:
out/orders/: cannot swap the new table in: No such file or directory (os error 2)When its swap lands after the other load took the table, it carries that load’s lease away with the old folder, so the other load stops before repairing, with an error a rerun clears:
orders: lost the table's lease to load 3f9c…, as it was not refreshed for 10 minutes; stopped before deleting any filesWhich path replace and --reload take:
| Where the table is | How the new table is written | What a reader sees |
|---|---|---|
| A local folder on Linux (ext4, XFS, Btrfs, tmpfs) or macOS (APFS) | In a hidden folder beside the table folder, swapped in with one rename | The old files or the new ones, never both |
| A local folder on a filesystem that can’t swap two folders in one rename (NFS, EFS, SMB mounts), or on Windows | Into the table folder; the older files are deleted once the commit’s marker in _sqlload/decided/ records it |
Both, for a moment between the write and the delete |
| A table at the root of the folder connection | As the row above, since it has no folder beside it | Both, for a moment |
| S3 | As the row above | Both, for a moment |
A load picks its path once, before it writes anything: it tries the one-rename swap on two empty folders beside the table folder, and takes the second row’s path if the filesystem doesn’t support it. A load that started staging never switches path: if the swap then fails, it stops without changing the table. Either path keeps a load that stalled past its lease from losing rows.
delete_insert writes the new files first, then deletes the older ones, in every store, as the
plan’s note says.
CSV files
Section titled “CSV files”A files target can write CSV instead of Parquet.
[targets.exports]connection = "out"path = "{table}/"format = "csv"compression = "gzip"csv = { delimiter = ",", header = true, quote = '"', null_value = "" }single_file = trueFiles are named part-<load id>.<commit>-<n>.csv, with .gz or .zst added when compressed. With
single_file = true each table, or each partition, is one file named after the table folder, such
as orders/orders.csv.gz. It is held in memory and written whole at the end of the load, which
commits in one piece; a load that would hold more than 2 GiB fails instead. Outside the local
replace swap, the file is overwritten in place, so a load that fails while writing several
partitions can leave some partitions new and others old until it is rerun. A table whose columns
grow partway through a load (a Python source adding a column) can’t use single_file.
Columns are written in the table’s order, the loader’s _sqlload_load_id and _sqlload_loaded_at
last. Values are rendered the same way on every load:
| Type | Written as |
|---|---|
text, varchar, uuid |
As is |
int, smallint |
Digits, with - when negative |
decimal(p,s) |
Plain digits with exactly s decimals, such as -0.05 |
float |
The shortest digits that read back the same value, never an exponent, such as 0.0000001; NaN, Infinity, -Infinity |
bool |
true, false |
date |
2026-11-08 |
time |
05:00:00.123456 |
timestamp_tz |
In UTC: 2026-11-08T05:00:00.123456Z (years outside 0 to 9999 as +10000-…) |
timestamp_ntz |
2026-11-08T05:00:00.123456 |
json |
Compact JSON text: no whitespace outside strings, keys in their order |
binary |
Lowercase hex |
A value is quoted when it is empty, equals null_value, or holds the delimiter, the quote or a line
break; a quote inside it is written twice. NULL is written as null_value, unquoted, so with the
default empty null_value a NULL is an empty field and an empty string is "".
plan doesn’t compare an existing CSV table’s columns with the new ones, since CSV files have no
footers. For the same reason, append by a {cursor:date} partition and postgres_cdc sources
need Parquet, and single_file doesn’t take append; plan says so.
Limitations
Section titled “Limitations”- The lease trusts the clocks of the machines that load, and
needs a store that honours create-only writes, as a local folder and S3 (
If-None-Match: *) do. An S3-compatible store that ignores that header can still let two loads write one table. WithAWS_CONDITIONAL_PUT=disabled, loads into S3 fail instead. replaceanddelete_insertwrite the new files first, then delete the older ones. A reader in between sees both, as the plan’snotesays.- A load that lost its lease and keeps going for more than 7 days after another load repaired it
isn’t covered: once the
abortedtombstone is gone, files it stores count as written, and a commit is refused only while the tombstone exists.