Files

A files source is one table built from every file under a folder, in S3, a local folder or, from 0.32.0, an SFTP server. To set one up, see Load files.

[connections.lake]
kind = "s3"
bucket = "example-bucket"
region = "eu-west-2"

[sources.events]
kind = "files"
connection = "lake"
path = "events/"
format = "parquet"
description = "Click events, partitioned by day."
partition_columns = { dt = "date" }
write_strategy = "delete_insert"
cursor_column = "dt"

Setting	Meaning
connection	An s3 or filesystem connection, or from 0.32.0 an sftp connection.
path	The folder holding the files, relative to the connection.
format	parquet (default), ndjson (one object per line), json (objects one after another, or one top-level array) or csv; from 0.35.0, also xlsx and xml. See Excel, XML and ZIP files.
compression	For text formats: auto (default, by file name: .gz is gzip, .zst is zstd), none, gzip or zstd; from 0.35.0, also zip, which reads the format’s files inside .zip archives. Parquet compresses inside the file.
csv	{ delimiter = ",", header = true, quote = '"', null_values = [""] }, the defaults. Without a header, columns are column_1, column_2, … From 0.35.0, also encoding = "utf-8" and skip_rows = 0; see CSV encodings. From 0.49.0, column_names names the columns of files without a header; see Naming headerless columns.
xlsx	From 0.35.0. { sheet = "...", header = true, skip_rows = 0 }: the worksheet (the first when omitted), whether its first row read names the columns, and rows skipped above it. From 0.49.0, also range and header_row; see Excel, XML and ZIP files.
xml	From 0.35.0. { record_path = "orders/order", lists = [] }: the elements from the root to each row’s element (required), and child elements always loaded as a JSON array.
members	From 0.35.0. With compression = "zip", a pattern the members’ names must match, such as "exports/*.csv".
shape	From 0.27.0. columns (default) loads typed columns; record loads each JSON object or CSV line whole into one json column. See Whole records.
record_column	From 0.27.0. The column shape = "record" loads into; record when omitted.
partition_columns	Hive key=value folders loaded as typed columns, such as { dt = "date" }.
path_columns	From 0.26.0. A template over each file’s path whose captures load as typed columns, such as "{region}/{order_date:date}/*". See Columns from paths.
unmatched_paths	From 0.26.0. What a file whose path doesn’t match path_columns does: fail (default) fails plan and names it, skip leaves it out.
constant_columns	From 0.26.0. Columns with the same value in every row, such as { region = "eu", as_of = 2026-11-08 }. From 0.37.0, a table setting, so the source’s table entry can set its own. See Columns from paths.
copy_to_internal_stage	Snowflake only: upload files no external stage covers to the user stage. See Load methods.

CSV encodings

From 0.35.0. csv.encoding names the files’ character encoding: utf-8 (the default), cp1252 (Windows Western European), latin-1 (ISO-8859-1, each byte read as the code point of the same number), ascii, utf-16le, utf-16be, or any other label of the Encoding Standard, such as windows-1250, iso-8859-15 or shift_jis. An unknown name fails plan.

A byte the encoding doesn’t define, such as 0x81 in cp1252 or any byte over 127 in ascii, fails the table with the file, line and byte, such as a.csv: line 3, byte 15: 0x81 is not valid windows-1252. A UTF-8 file with other bytes fails the same way, with a hint to set csv.encoding. A UTF-16 file whose byte order mark names the other byte order fails too, naming the encoding to set.

csv.skip_rows skips that many lines at the start of each file, before the header (or the first record without one), such as a report title. A line ends at \n or \r\n, so quotes in a skipped line don’t matter, and a file whose lines end in a bare \r fails rather than being skipped whole. Line numbers in errors count the skipped lines.

csv = { encoding = "cp1252", skip_rows = 2 }

Snowflake reading CSV files itself decodes them and skips the lines in its COPY, which supports utf-8, latin-1, windows-1250 to windows-1256, iso-8859-2, -5, -6, -7 and -15, koi8-r, utf-16le and utf-16be. Any other encoding fails plan for that route. Snowflake loads a byte the encoding leaves undefined, such as 0x81 in cp1252, as a control character rather than failing; plan finds one only in the rows it samples.

Excel, XML and ZIP files

From 0.35.0. SQLLoad reads these itself on every destination, as no warehouse does; copy_to_internal_stage and shape = "record" don’t apply.

Excel (format = "xlsx"). One worksheet loads per source: xlsx.sheet, or the first sheet. xlsx.skip_rows skips rows at the top, such as a title; the next row names the columns unless header = false, when columns are column_1, column_2, … by their place in the sheet. Empty rows are left out. Cells keep the type the workbook stores: a whole number is int, other numbers float, TRUE/FALSE bool, and a date-formatted cell date, timestamp_ntz or time (a column holding dates and timestamps is timestamp_ntz). An empty cell or an error such as #N/A is NULL. Columns are typed from a sample, as for CSV, and a later value that doesn’t fit fails with its row; pin a type in columns to convert instead. Formulas load their cached values. Older .xls workbooks aren’t read. A headerless column whose column_N name the header already uses fails, and so does a sheet holding over 20 million values.

From 0.49.0, xlsx.range reads an A1 range, as Google Sheets ranges do: an optional sheet (in single quotes when its name has spaces, as in 'Q4 Report'!B3:H), the columns, and optional rows; an open-ended range such as A3:C runs to the last row. It replaces sheet and skip_rows, and setting it with either fails plan. Cells outside the range don’t load, and a range past the sheet’s data loads no rows, like an empty sheet. Its first row holding a value names the columns; xlsx.header_row names another, counting from the range’s first row, for title rows inside it. Without a header, columns are column_1, column_2, … by their place in the range, or the names in column_names:

xlsx = { range = "Prices!A3:C" }          # sheet, first row and columns in one place
xlsx = { range = "A:C", header = false }  # first sheet, columns A to C, no header row
column_names = ["metric", "category", "value"]

Naming headerless columns

From 0.49.0, column_names names the columns of CSV or Excel files without a header row, in order; types, keys and other settings then use those names:

[sources.metrics]
kind = "files"
connection = "inbox"
format = "xlsx"
xlsx = { range = "A:C", header = false }
column_names = ["metric", "category", "value"]
write_strategy = "merge"
unique_key = ["metric", "category"]

[sources.metrics.tables.metrics]
columns = { value = "decimal(12,2)" }

• It’s for csv = { header = false } and xlsx = { header = false }; with a header row, the names come from the data, and plan fails.
• The count must match the data’s columns exactly: the range’s width for Excel (the columns up to the last one holding a value without a range), and each file’s for CSV. Otherwise plan fails, or for a later file the load, naming both counts and the file, so a shifted layout fails rather than loading under the wrong names.
• The names become columns like header names, under the target’s naming. Two names equal ignoring case fail plan.
• Renaming the columns of data that has a header is modelling work for downstream; SQLLoad has no general rename.

XML (format = "xml"). Each element at xml.record_path is a row. Its attributes and child elements are its columns, by name without a namespace prefix. A child that appears more than once in a row, or one xml.lists names, loads as a JSON array; a child with attributes or children of its own loads as a JSON object, keys in document order. Text is trimmed of surrounding whitespace and typed as CSV fields are; an element with xsi:nil="true" is NULL. Namespace declarations and other attributes in the XML Schema instance namespace, such as xsi:type, aren’t columns. record_path may name prefixes, which are ignored. These fail, with the line where it applies: malformed XML, a document that ends inside an element, an element with two attributes of one name or an attribute and a child of one name, a UTF-16 document, and a record_path whose parent elements the document doesn’t have.

[sources.customers]
kind = "files"
connection = "lake"
path = "drop/"
format = "xml"
xml = { record_path = "customers/customer", lists = ["phone"] }
description = "Customers from the nightly XML export."
write_strategy = "replace"

ZIP archives (compression = "zip"). Each .zip file under path is opened, and each member whose name ends with the format’s suffix (.csv, .xlsx, .xml, …) and matches members loads as a file of its own, recorded in _sqlload_source_file as <archive>.zip/<member>. Other members are left out. For an archive holding several formats, define one source per format over the same path:

[sources.orders]
kind = "files"
connection = "lake"
path = "drop/"
format = "csv"
compression = "zip"
members = "*.csv"
description = "Orders from the nightly ZIP export."
write_strategy = "replace"

[sources.lines]
kind = "files"
connection = "lake"
path = "drop/"
format = "xlsx"
compression = "zip"
xlsx = { sheet = "Lines" }
description = "Order lines from the workbook in the nightly ZIP export."
write_strategy = "replace"

A member’s version is its archive’s, so with append an archive rewritten in place fails the table, as any rewritten file does. Only an archive’s index and the members read are fetched, with range requests. Workbooks, XML documents and ZIP members are read whole, up to 1 GiB each; a larger member, or an archive naming two members alike, fails.

Which files load

Strategy	Reads
append	Only files not loaded yet, by _sqlload_source_file. A file rewritten in place since it loaded fails the table, with the fix. From 0.45.0, so does a listed file loaded without its version (an empty _sqlload_source_version, as in rows loaded before SQLLoad recorded it), since whether it was rewritten can’t be told: --reload loads the table again and records every file’s version. Before 0.45.0 such a file was taken as loaded. From 0.45.0, plan refuses an append table that excludes _sqlload_source_version, as it refuses one excluding _sqlload_source_file.
delete_insert with a partition cursor_column (from partition_columns or path_columns)	The newest loaded partition and everything after it, replacing those partitions whole. From 0.51.0, a file that changed since it loaded whose partition lies below that range fails the table, as does a new file below it in a partition the table holds; see below.
delete_insert with a replace_key (from 0.51.0)	Only files not loaded yet, or changed since, one at a time, oldest modified first, each replacing the rows of the replace_key values it holds.
replace	Every file, swapped in as a new table.
merge with a unique_key (from 0.38.0)	Only files not merged yet, one at a time, oldest modified first.

A files source needs a write_strategy, from the source or its table entry: plan refuses it otherwise. Before 0.46.0 it took replace without a cursor_column, and before 0.44.0 delete_insert with one. Files report no primary key, so a merge names its unique_key columns.

$ sqlload plan
…
└── raw.events_log                                  append
    ├── source  files  lake/events/  parquet
    ├── files   1 new of 3 listed
    ├── route   DuckDB reads directly from lake/events/ (nothing through the loader)
…
$ sqlload load
…
loaded  raw.events_log      1 row  0.0s
…

Large loads commit at 1,000 files or about 1 GB. Under delete_insert a partition is never split across commits.

From 0.51.0, delete_insert on a partition cursor fails the table when a listed file changed since it loaded but its partition lies below the range the load reads, such as a monthly file sent again corrected after the next month’s loaded. Skipping it would keep its old rows. A new file below the range fails it too when the table holds other files of its partition, such as a part file added to a loaded month, which would otherwise never load. The error names the file and the fixes:

$ sqlload plan
…
Errors (1)
└── raw.le
    ├── reason  /data/drop/le_LE-FY27-P01.csv changed since it was loaded (version e3c0-3b9aca00-47
    │           is now e3c0-b2d05e00-3b), but its file_version LE-FY27-P01 is below this load's
    │           range, which starts at LE-FY27-P02, so delete_insert would skip it and keep its old
    │           rows
    └── fix     set replace_key to replace rows by a column the files hold (and drop
                cursor_column), load from it with --start-cursor LE-FY27-P01, or rebuild the table
                with --reload

To find such files, plan reads each file and version the table holds, from its _sqlload_source_file and _sqlload_source_version columns, unless the load has --start-cursor or --reload, with a SELECT DISTINCT over the table on every plan. On Snowflake, BigQuery and ClickHouse that reads both columns of the whole table, which BigQuery bills. plan refuses a table that excludes either column, or an existing table without one, naming the fix, as it couldn’t find those files.

--start-cursor LE-FY27-P01 replaces that partition and every one after it, once. For files sent again as a matter of course, use a replace_key.

Replacing rows by a key

From 0.51.0, delete_insert with a replace_key and no cursor_column loads each new or changed file in its own commit, oldest modified first, then path. Each commit deletes the rows holding a replace_key value the file holds, then inserts the file’s rows. A file sent again corrected replaces its version whole, lines it dropped included, and leaves other versions alone. One file must hold all of a value’s rows. Before each commit, SQLLoad finds the files the table’s rows of the commit’s values came from, by _sqlload_source_file. When one of them is still in the folder, or loaded earlier in the same run, the table fails, naming both files: two files of the folder holding one value, such as the parts of an export split into part files, would leave only the later one’s rows. Put all of a value’s rows in one file, or remove the outdated file from the folder. A file sent again under the same name, or one whose earlier file has left the folder (deleted, or moved by after_load), replaces the value. A file’s rows always commit together. plan refuses a table that excludes _sqlload_source_file.

[sources.le]
kind = "files"
connection = "lake"
path = "finance/le/"
format = "csv"
description = "Latest Estimate, one file per version, each row holding its le_version."
write_strategy = "delete_insert"
replace_key = ["le_version"]

$ sqlload plan
…
└── raw.le                                          delete_insert (replace_key: le_version; NULLs in keys match)
    ├── source  files  lake/finance/le/  csv, compression by file name
…
    ├── files   1 new of 1 listed
    └── batch   1 file, one commit each, oldest modified first (ties by path), each recorded in the run log

It picks files as merging files does: each commit records its file and version in _sqlload_runs, and loads into DuckDB and Postgres targets so far. --reload, state = "logged" and a cursor_column are refused. after_load applies to it, as to merge.

Merging files

From 0.38.0, merge upserts each new file on its unique_key. A load merges its files in order of their modified time, then path, one commit each, so a key that several of them carry ends with the value from the file modified last. A file that turns up after others merged, even with an older modified time, merges after them. Each commit also records that file and its version in _sqlload_runs, in the same transaction, and later loads skip the files recorded there. If a file fails, the files before it stay merged, and the error names the file that failed. The next load continues from that file.

[sources.prices]
kind = "files"
connection = "lake"
path = "prices/"
format = "csv"
description = "Price lists, each file updating the prices it names."
write_strategy = "merge"
unique_key = ["sku"]

• A merged file whose version changed, even back to one merged before, is merged again, as the newest file.
• Stores that keep modified times to the second, as SFTP does, and the members of one ZIP archive, which share the archive’s time, fall back to path order for files from the same second.
• From 0.39.0, a file that holds one unique_key value in more than one row merges the last of those rows, or the one dedup_sort picks; see Repeated keys. With on_duplicate_keys = "fail", as in 0.38.0, such a file fails instead, naming the key’s columns and examples, before anything of it merges. From 0.51.0 rows whose key holds NULLs are collapsed too, a NULL matching a NULL; before, they were never collapsed. The order of a file’s rows comes from _sqlload_source_row: when exclude_columns drops it, a file repeating a key fails as in 0.38.0, plan says so, and dedup_sort is refused. From 0.51.0 that includes a file repeating a key with NULLs in it, even under the default on_duplicate_keys.
• Merging files loads into DuckDB and Postgres targets so far, and MotherDuck through its DuckDB connection, which isn’t tested live. --reload, state = "logged" and a cursor_column are refused: the run log, not a cursor, picks the files. To merge every file again, drop the table: a table created anew starts a new record.

Deleting or moving loaded files

From 0.39.0, after_load says what happens to each file once its rows are committed: "keep" (the default), "delete", or { move_to = "<folder>" }.

[sources.orders]
kind = "files"
connection = "lake"
path = "drop/"
format = "csv"
description = "Orders dropped by the nightly export."
write_strategy = "append"
after_load = { move_to = "loaded" }

• It acts only on files whose rows committed, after the load of the table succeeded. A failed load deletes and moves nothing, including files committed before the failure. Those stay where they are, and later loads skip them, so delete or move them yourself.
• move_to is a folder of the same connection, read as path is. It can’t be inside path or hold it, however either is written, or moved files would be listed and loaded again; plan refuses that. Each file keeps its key below path, so drop/2026/a.csv moves to loaded/2026/a.csv.
• A file already at the destination is never replaced: both stay. On a local disk a move is a hard link, then a delete; where a hard link fails, as across volumes, the file is copied into a new file that is created only if none is there. S3 has no copy that refuses to replace a file, so SQLLoad checks the destination is free, then copies on the server: a file written to that same key at that moment would be replaced. S3 copies files of up to 5 GB; a larger one stays where it is and is reported. Either way the copy’s size is checked before the source is deleted.
• Each file’s version is checked just before it’s deleted or moved, and a file that changed since it was read is left where it is, since its new rows aren’t loaded.
• If a delete or move fails, the load still counts and isn’t run again. load --json reports after_load on the table, with status "failed" and each file left in failed, and a warning is logged. Its status is "done" when every file was handled.
• It needs write_strategy = "append" or "merge", or from 0.51.0 "delete_insert" with a replace_key, which never read a loaded file again. A file that reappears under a loaded name is a new file when its version differs; an identical copy, such as the same bytes on S3, is skipped as loaded and stays. --reload is refused.
• From 0.39.1, a run that finds the folder empty skips the table and exits 0, since after_load handled every file loaded before: plan lists it under skipped, as does load --json in plan.skipped. That needs an existing folder holding no file at all (hidden ones aside) and a table that has loaded before. A folder on a local disk that is missing or can’t be read, one holding files of another format or that don’t match path_columns, and a table that was never loaded all still fail, as does an empty folder whose destination table another source loads too. Added in 0.42: while the folder is empty, a source sharing its destination table still can’t replace or delete_insert into it, even when it runs alone. On S3 a prefix exists only while it holds files, and an unmounted share is an empty folder, so after the first load those look empty: watch the skipped reason. Without after_load, an empty folder always fails.
• It isn’t for SFTP connections, which SQLLoad only reads, ZIP archives, whose members another source may still read, or files targets.

Columns

Parquet columns come from the files’ footers. CSV and JSON columns are inferred from a sample of records, unless you pin them with columns. Each row also gets _sqlload_source_file, _sqlload_source_version and _sqlload_source_row (Columns SQLLoad adds).

From sqlload 0.27.0, Parquet list, struct and map columns load as json. A list becomes an array, a struct becomes an object with its fields in order, and a map becomes an object keyed by its keys’ text. Decimals, dates, times and timestamps inside them are strings, such as "1.50" or "2026-11-08T10:00:00.123456". Before 0.27.0 these columns fail plan, and exclude_columns leaves them out. A destination never reads these files itself, because its own conversion differs. In Snowflake, where files under an external stage or copied with copy_to_internal_stage only load directly, such a column still fails plan, so leave it out with exclude_columns.

From 0.27.0, pin a list column, or a JSON array in a CSV or JSON file, as array(T) to load it as an array instead, such as columns = { tags = "array(text)" }.

Columns from paths

From sqlload 0.26.0, values can come from folder and file names that aren’t key=value folders. path_columns is a template over each file’s path below path:

[sources.orders]
kind = "files"
connection = "lake"
path = "orders/"
path_columns = "{region}/{channel}/{order_date:date}/*.parquet"
constant_columns = { shop = "main", schema_version = 2 }
write_strategy = "delete_insert"
cursor_column = "order_date"

A file at orders/eu/web/2026-11-08/part-0.parquet loads with region = 'eu', channel = 'web', order_date = 2026-11-08, shop = 'main' and schema_version = 2.

In a template	Matches
{name} or {name:type}	A value inside one folder or file name, loaded as column name. The type is anything partition_columns takes, text by default.
*	Any text inside one folder or file name.
**	Any number of folders, as a whole segment: **/{day:date}.json.
Anything else	Itself. / separates folders.

The template matches the whole path, file name included. A date capture matches 2026-11-08 or 20261108 and loads as a date, and an integer capture matches digits only, so *_{export_date:date}_*.csv finds the date in orders_eu_20261108_v2.csv. A text capture takes the shortest value the rest of the template allows, so keep a literal between two captures. %XX escapes in a captured value are undone, as for Hive folders.

A file whose path doesn’t match fails plan and names the file, unless unmatched_paths = "skip"; plan then counts the files it leaves out. A captured value that doesn’t convert to its type always fails plan.

constant_columns gives every row of the source the same values. A string loads as text, an integer as int, a float as float and true or false as bool. An unquoted TOML date (2026-11-08) loads as date, and a date and time as timestamp_tz with an offset or timestamp_ntz without. For any other type, write { value = "12.50", type = "decimal(10,2)" }. From 0.37.0, constant_columns is a table setting that every source takes: set on [sources.NAME.tables.NAME], its map replaces the source’s.

Captured and typed values load the same in every destination: a boolean as true or false, an integer without leading zeros, a date as 2026-11-08, and a timestamp with an offset converted to UTC.

Captured and constant columns work like Hive partition columns. A captured date can be the cursor_column of delete_insert, which replaces the newest loaded value and everything after it; a constant can’t. A column in the files with the same name is replaced, and plan notes the column it drops; other kinds of source fail instead. Destinations that read the files themselves still do: SQLLoad writes each file’s values into the COPY or read_parquet as literals. A column can come from only one of partition_columns, path_columns and constant_columns, ignoring case.

Whole records

From sqlload 0.27.0, shape = "record" loads each record whole into one json column instead of typed columns, to land data as it is and parse it in the destination:

[sources.events]
kind = "files"
connection = "lake"
path = "events/"
format = "ndjson"
shape = "record"
record_column = "payload"
partition_columns = { dt = "date" }
write_strategy = "append"

• JSON and NDJSON: each object as JSON. A key given twice keeps its last value. A record that isn’t an object fails the load and names its file and line.
• CSV: each line as a JSON array of its fields as strings, such as ["1", "a,b", null]. A field matching null_values is null. With header = true, each file’s first line is skipped without being checked, so files with different headers load together. Every line needs as many fields as the file’s first line.

Nothing is sampled and the columns never change: new keys and fields arrive inside the record, so the source’s tables pin no other columns, and record_column can’t be a name SQLLoad adds itself. Partition, path and constant columns and the _sqlload_source_* columns load beside it as usual. The column is json in every destination (VARIANT in Snowflake), and Snowflake still reads the files itself, also from copy_to_internal_stage. Parquet files already have typed columns, so they can’t use shape = "record".

Files on an SFTP server

From 0.32.0. An sftp connection reads files from an SFTP server. To set one up, see Load files from an SFTP server.

• path is a folder below the user’s login folder (~/ may start it), or from / when it starts with /. SQLLoad lists it and every folder below it, except hidden ones. It reads regular files only; symbolic links are skipped. A server must give each file’s modification time.
• The server’s host key must be in known_hosts. An unknown or changed key fails before anything is listed, with the key’s fingerprint and the command that adds it.
• A file’s version (_sqlload_source_version) is its size and modification time, as <size>-<mtime in milliseconds>. So append loads only new files, and notices a file rewritten in place when its size or modification time changed. SFTP gives modification times to the second, so a rewrite within the same second that keeps the size isn’t noticed.
• No destination reads files over SFTP itself, so the rows always pass through SQLLoad’s reader, even into Snowflake with an external stage; copy_to_internal_stage is refused. plan shows read over SFTP at the start of the route. Text files stream as they arrive, and Parquet is read in ranges, as from S3.
• _sqlload_source_file names the server by its host, then the file’s path: sftp://host/path for a path from /, and sftp://user@host/~/path for one below the login folder. A change of host (an IP address for a name, say), or of user for a login-folder path, makes append see every file as new and load it again. The port isn’t part of it, so two SFTP sources loading into one table must not name the same host and path from / (two chrooted users, say).
• SQLLoad only reads from the server: it never writes, moves or deletes files there.
• Each table being read uses one SFTP channel on the session. OpenSSH allows 10 per connection (MaxSessions), so keep concurrency at 10 or less for one server.
• The SSH session is the one SSH tunnels use: one session per server and sign-in, kept alive while the run lasts.

Files the destination reads itself

When the destination can load the files exactly by itself, it does, and no row passes through SQLLoad:

• DuckDB reads Parquet in S3 or a local folder with read_parquet.
• Snowflake runs COPY from an external stage over the files’ bucket, for Parquet, JSON, NDJSON and CSV.
• ClickHouse reads Parquet in S3 with s3().

plan names the route for each table. See Let the warehouse read the files and Load methods.