Skip to content

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 -v
Plan 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 MiB

Files 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 records

The 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 finishes

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

Repairing 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 files

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

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 files

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

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 = true

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

  • 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. With AWS_CONDITIONAL_PUT=disabled, loads into S3 fail instead.
  • replace and delete_insert write the new files first, then delete the older ones. A reader in between sees both, as the plan’s note says.
  • A load that lost its lease and keeps going for more than 7 days after another load repaired it isn’t covered: once the aborted tombstone is gone, files it stores count as written, and a commit is refused only while the tombstone exists.