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.
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.
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 (not released yet), 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.

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

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

[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."

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.
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.
replace	Every file, swapped in as a new table.

merge isn’t supported for files sources: a file would be read again once its rows are updated.

$ sqlload load
…
└── 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)
…
loaded   raw.events_log  1 rows  0.0s

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

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 (not released yet), 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" }

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