Parquet and CSV files

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 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 from 0.27.0 csv. See CSV files.
compression	From 0.27.0. CSV only: none (default), gzip or zstd.
csv	From 0.27.0. CSV only: { delimiter, header, quote, null_value }. Default { delimiter = ",", header = true, quote = '"', null_value = "" }.
single_file	From 0.27.0. 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 load
Plan ready  1 selected, 1 to load

Loads (1)                                           sqlload load
└── app__orders                                     delete_insert (cursor: updated_at)
    ├── source  postgres  app  public.orders
    ├── files   out/app/orders/  parquet (zstd)  partitions dt={cursor:date}/
    ├── schema  first files, 7 columns
    ├── batch   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
    ├── cursor  updated_at first run, everything
    └── 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

✓ Plan complete  1 selected, 1 to load
loaded   out/app/orders/  1,001 rows  0.0s

✓ Completed  1 loaded, 1,001 rows, 0.0s

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.

From 0.27.0, 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.

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.

From 0.27.0, 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.

Replacing a table

From sqlload 0.27.0: 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. Where the filesystem can’t swap folders in one rename, SQLLoad moves the old folder aside first and warns, which leaves a moment with no folder. A hidden folder left by a stopped or failed load is deleted by the next load. A table at the root of the folder connection has no folder beside it to swap, so it is replaced as in S3.

In S3, replace writes the new files first, then deletes the older ones, so a reader in between sees both. delete_insert works this way in both stores, as the plan’s note says.

CSV files

From sqlload 0.27.0, 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.

Limitations

• Before 0.27.0, a files source can’t load into a files target.
• The lease (from 0.27.0) 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. A load paused for longer than its lease, or 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.