Skip to content

Sources

Files

The files source: settings, which files load, columns, columns from paths, whole records, SFTP servers and the files a destination reads itself.

A files source is one table built from every file under a folder, in S3, Google Cloud Storage, a local folder or 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, gcs or filesystem connection, or 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; 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; 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, … Also encoding = "utf-8" and skip_rows = 0; see CSV encodings. column_names names the columns of files without a header; see Naming headerless columns. Also escape, quote = false and encoding_check = true; see Quotes and escapes and CSV encodings.
xlsx { 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. Also range and header_row; see Excel, XML and ZIP files.
xml { 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 With compression = "zip", a pattern the members’ names must match, such as "exports/*.csv".
pattern A glob each file’s path below path must match, such as "file_*.txt" or "**/*.dat", in place of the format’s name suffix. See Choosing files.
shape columns (default) loads typed columns; record loads each JSON object or CSV line whole into one json column. See Whole records.
record_column 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 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 What a file whose path doesn’t match path_columns does: fail (default) fails plan and names it, skip leaves it out.
constant_columns Columns with the same value in every row, such as { region = "eu", as_of = 2026-11-08 }. 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.

A field that starts with quote (" by default) is quoted: it may hold the delimiter and line breaks, and a quote inside it is written twice ("say ""hi"""), as Excel, Python’s csv module, pandas and Postgres write CSV. A quote inside an unquoted field (12" pipe) is plain text.

  • csv = { escape = "\\" } also reads a backslash inside a quoted field as making the next character plain text, so "said \"hi\"" reads as said "hi". A doubled quote still works. There is no escape by default, so a value ending in a backslash, such as "C:\exports\", reads as written.
  • csv = { quote = false } reads every quote as a plain character: fields split on the delimiter only, and "Big" deal loads as written.
  • After a closing quote, only the delimiter or a line end may follow. Anything else fails the file, naming the line, such as a.csv: line 2: text after a closing quote, as does a file that ends inside a quoted field (unterminated quoted field). Standard CSV never has either, so they mean the file is being misread. Both errors suggest escape = "\\" (unless it is set) and quote = false.

SQLLoad doesn’t guess either setting: \" is ambiguous, and a wrong guess would misread values without an error. Snowflake reading the files itself takes both settings in its COPY.

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.

Files labelled “ISO-8859-1” rarely are: a file holding Œ, œ or €, which ISO-8859-1 doesn’t have, is usually iso-8859-15 (common on European Unix) or cp1252 (Windows). Read with the wrong one, BŒUF bœuf €5 loads as B¼UF b½uf ¤5. To tell them apart, look at the bytes of a word you know, such as grep -a 'B.UF' a.csv | xxd: BC is iso-8859-15, 8C is cp1252, and C5 92 is UTF-8.

A file read as latin-1, cp1252 or iso-8859-15 fails at its end, with the line and byte, when its bytes say it is in another encoding:

  • Bytes 0x80 to 0x9F read as latin-1 or iso-8859-15 are control characters no text holds: the file is probably cp1252, and the error says to set encoding = "cp1252".
  • Bytes above 0x7F that are mostly valid UTF-8 sequences, such as C3 A9 for é, mean the file is UTF-8 (Société), and the error says to set encoding = "utf-8". A real latin-1 file holding é among its other accents still loads.

csv = { encoding_check = false } turns the check off. It can’t tell an ISO-8859-15 file read as latin-1, whose ¼ and ½ are real latin-1 characters. Files read in these encodings go through SQLLoad’s reader rather than Snowflake’s own, which has no such check, unless the check is off.

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.

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.

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

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 naming. Two names equal ignoring case fail plan.
  • To rename the columns of data that has a header, use 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.

A transform is a Python function that makes a file loadable: it fixes headers, drops title and totals rows, or unpivots a sheet. Keep business logic downstream, in SQL, where it can change without reloading files. Decorate the function with @sl.transform in a module under python_paths, and name it in transform: @sl.transform registers it under its own name, and @sl.transform("kpis") under another. It needs the sqlload Python package and pyarrow; the standalone sqlload-cli executable refuses a table with a transform. The same kind of function also reshapes each Google Sheets range and each sl.http page.

python_paths = ["transforms"]
[sources.metrics]
kind = "files"
connection = "lake"
path = "drop/metrics/"
format = "csv"
description = "Monthly metrics, one file a month."
write_strategy = "append"
transform = "metric_rows"

It runs once for each file, and for each member of a ZIP archive, after SQLLoad reads it and before its rows are typed against the table. It’s called as f(rows, ctx): rows is the file’s rows as a pyarrow.Table, and ctx an sl.TransformContext. It returns or yields, in any mix, what a Python table may yield: dicts, lists of dicts, a pyarrow.Table, or a polars or pandas DataFrame. Dicts are the simplest:

transforms/metrics.py
import pyarrow as pa
import sqlload as sl
@sl.transform
def metric_rows(rows: pa.Table, ctx: sl.TransformContext):
for row in rows.to_pylist():
if row["metric"] in (None, "Total"):
continue
for month, value in row.items():
if month != "metric" and value is not None:
yield {"metric": row["metric"], "month": month, "value": float(value)}
$ sqlload load
✓ Plan complete 1 object to create, 1 to load (sqlload plan shows the tree)
created raw.metrics 8 columns
loaded raw.metrics 5 rows 0.0s
transform ./drop/metrics/2026-10.csv 0.00s
transform ./drop/metrics/2026-11.csv 0.01s
✓ Completed 1 loaded, 5 rows, 0.1s
├── slowest raw.metrics 0.0s
└── peak memory 134 MiB

ctx.unit is "file". ctx also has path (as in _sqlload_source_file), modified_at, sheet, range, skip_rows and table.

The rows it gets are typed so that no value fails the read before the function sees it: CSV and XML values as text, Parquet columns as stored, Excel cells as the workbook stores each column, and JSON values as text except nested objects and lists, which stay JSON. exclude_columns and allow_columns apply to the file’s own columns, before the function; columns and mask apply to what it returns.

Dict values are typed as JSON values are, so a whole number is int and a decimal float; pin exact types in columns. For large files, return Arrow or a DataFrame, which skips converting each row. The same unpivot in polars:

transforms/metrics.py
import polars as pl
import pyarrow as pa
import sqlload as sl
@sl.transform
def metric_rows(rows: pa.Table, ctx: sl.TransformContext):
frame = pl.from_arrow(rows).filter(pl.col("metric").is_not_null() & (pl.col("metric") != "Total"))
return frame.unpivot(index="metric", variable_name="month", value_name="value").drop_nulls("value")

A pandas DataFrame crosses with its columns only; after filtering, call reset_index(drop=True) so no index is left over.

  • plan looks the name up among the functions @sl.transform registered on python_paths, and fails when none has it, listing those that do, or when two do, naming both files:

    $ sqlload plan
    Errors (1)
    └── raw.metrics
    └── reason transform metrics_rows: no function is registered as metrics_rows with @sl.transform; the registered ones are metric_rows
    ✗ Plan failed 1 error

    It then runs the function on the newest file (by name), and the table’s columns are what it returned, typed as a Python table’s rows are. So the function runs at plan and again at load: keep it free of side effects.

  • Every file must return the columns the planned file did, or fewer (a missing one loads NULL). A file returning a new column fails, naming it; pin it in columns to add it.

  • A transform that raises, or returns or yields something else, fails that file and names the function and the file. Its rows don’t commit, and after_load leaves it where it is. The table stops there, as for a file that doesn’t read; files committed before it stay committed.

  • The file order, merge, after_load and the _sqlload_source_* columns work as without a transform; _sqlload_source_row numbers the rows the function returned. SQLLoad always reads the files itself, so a destination never reads them directly.

  • Each file is read whole and held in memory while the function runs. What it returns or yields loads batch by batch as it comes, so a generator needs memory for one file and one batch of its output.

  • The load report gives each file’s transform time, and a hint when dicts made a large file slow.

Each commit records the version of the transform it ran (a hash of the file the function is defined in) and the files it ran on, in the same transaction as their rows. The record goes to the table’s run log, _sqlload_runs in its schema (objects under the table’s folder on a files target), which plan lists under Objects as create table (run log) until apply (or the first load) creates it. A load only inserts into it, so a role without CREATE on the schema can load once apply has run under one that has it.

Files already loaded aren’t read again, so after a change, plan warns on the table for as long as it holds files an earlier version loaded, here after a load of one new file:

$ sqlload plan -v
Plan ready 1 selected, 1 to load
Loads (1) sqlload load
└── raw.metrics append
├── source files ./drop/metrics/ csv, compression by file name transform metric_rows
├── route read csv into Arrow -> DuckDB takes Arrow
├── columns 3
├── files 0 new of 3 listed
├── batch 0 batches, up to 1,000 files or about 1 GB per commit
└── transform changed: 2 files loaded under an earlier version (--reload transforms every file again)
✓ Plan complete 1 selected, 1 to load

The version covers the file the function is in; a change in another module it imports isn’t noticed. When that file can’t be read, as for a function defined in a notebook, it covers the function’s own code instead. That code is Python’s bytecode, which changes between Python minor versions, so after upgrading Python, plan says such a transform changed even if you didn’t edit it; --reload loads its files under the new version. To load every file again with the new transform, run sqlload load <table> --reload (Reloading a files table), after which the warning is gone. A table whose after_load deletes its files can’t be reloaded: drop it and load the files still in path.

sqlload load <table> --reload reads every listed file again into a new table, which replaces the old one in one commit at the end; until then readers see the old table.

  • append, replace and delete_insert tables read the files in batches, as a first load does. A delete_insert with a replace_key refuses --reload.

  • merge tables reload too, on DuckDB targets (Postgres targets refuse --reload for every table). The files merge into the new table in batches, as a load merges them: files merged before go in the order they merged, matched by name and version, or for a file under move_to by name, so a file after_load moved keeps its place; files never merged follow, oldest modified first, and so does a new file in path named as one move_to holds, whose rows then win over the older copy’s. The new table’s file record (a reset, then one record per batch, naming its files) is written in the commit that swaps it in, so a reload that fails part way leaves the old table and its file record as they were. After a reload, the next load reads only files added since. plan shows merge (unique_key: …) into a new table, swapped in at the end (--reload). A merge table with unnest is refused, since its child tables aren’t rebuilt: drop the table and its child tables and load again.

  • With after_load = { move_to = ... } the reload also lists the files under move_to, which keep the names they loaded under (_sqlload_source_file names the file under path). plan shows both folders and how many files it reads from each:

    ├── source files ./drop/ and ./done/ csv, compression by file name
    ├── files 3 to read of 3 listed: 1 in ./drop/, 2 in ./done/

    Once the new table is in, files read from path move as usual; those already under move_to stay there. A reload that fails moves nothing.

    When some of the files are under move_to, SQLLoad reads them itself, even where the destination reads files directly on a normal load, and the route line says why:

    ├── route read parquet into Arrow -> DuckDB takes Arrow (--reload also reads the files after_load moved to ./done/scores/)
  • With after_load = "delete", --reload is still refused: the files loaded before are gone.

Without pattern, a files source reads the files whose names end in its format’s suffix: .csv, .csv.gz and .csv.zst for format = "csv", and so on. pattern chooses them instead, by a glob over each file’s path below path, so files of any name load as the source’s format, such as the pipe-delimited .txt exports an ERP drops beside other files:

[sources.exports]
kind = "files"
connection = "drop"
path = "erp/"
pattern = "file_*.txt"
format = "csv"
csv = { delimiter = "|" }
write_strategy = "merge"
unique_key = ["id"]

* matches within one name and ** across folders, as in path_columns and members: "file_*.txt" reads file_1.txt but not old/file_2.txt, which "**/file_*.txt" reads too. Compression still follows the name (.gz, .zst) unless compression sets it, and with compression = "zip" the pattern picks the archives while members picks their members. A file must match pattern before path_columns captures from its path. Files that don’t match are never read, moved or deleted, and a folder holding only such files counts as empty for on_empty and after_load. plan says what it chose:

├── files 2 new of 2 matching file_*.txt, 1 other file ignored

Without pattern, a folder of .txt files fails plan with the suffixes it looked for, suggesting pattern. A destination that reads the files itself gets the same files, each named in its COPY.

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. 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. 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. 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 Only files not loaded yet, or changed since, 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 Only files not merged yet, oldest modified first. Under --reload (DuckDB), every file again, in the same order, into a new table swapped in at the end; see Reloading a files table.

A files source needs a write_strategy, from the source or its table entry: plan refuses it otherwise. Files report no primary key, so a merge names its unique_key columns.

$ sqlload plan -v
…
└── 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. A merge, or delete_insert by replace_key, commits consecutive files together, in order, and ends with the same rows as committing them one by one: the newest file wins a key several of them hold.

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.

Such a new file fails it even when every file the table holds of its partition has left the folder, as plan reads the partitions from the files the table holds. A file below the range that the table holds without a version (an empty _sqlload_source_version, as in rows loaded before SQLLoad recorded it) fails it too, as whether it changed can’t be told; rebuild the table with --reload to record each file’s version:

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

delete_insert with a replace_key and no cursor_column loads each new or changed file, oldest modified first, then path, up to 1,000 files or about 1 GB per commit. Each file replaces the rows holding a replace_key value it holds with its own rows, as if it committed on its own. 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, for each of its files, the files the table’s rows of the file’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, and the commit, with every file in it, rolls back: 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. The error names the fixes: put all of a value’s rows in one file; or, when newer files restate older ones, set on_overlapping_files = "last_wins" (below), or remove the older file from the folder by hand. A failed load moves nothing, so after_load can’t clear the failure, but after_load = { move_to = ... } (or "delete", which rules out --reload) keeps it from happening again: each file leaves the folder once it loads, and a file sent later replaces its values. 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 -v
…
└── 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, 1 commit, oldest modified first (ties by path), each recorded in the run log

It picks files as merging files does: each commit records its files and versions 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.

A feed whose deliveries overlap, each restating the end of the period before, holds one value in two files on purpose. on_overlapping_files = "last_wins" (default "fail") lets the file last in order replace the values it holds, even while an earlier file holding them is still in the folder. Each file replaces only the values it holds, so the earlier file’s other values stay, in one commit or across several:

sales/
sales_2026-11-07.csv business_date Nov 1 .. Nov 7
sales_2026-11-14.csv business_date Nov 5 .. Nov 14 <- Nov 5 to 7 come from this file
[sources.sales]
kind = "files"
connection = "lake"
path = "sales/"
pattern = "sales_*.csv"
format = "csv"
description = "Daily sales, each delivery restating the days before it."
write_strategy = "delete_insert"
replace_key = ["business_date"]
order = "path"
on_overlapping_files = "last_wins"

Set order = "path" when the names sort by date, as here; by default the file modified last wins. Under "last_wins" files commit together into DuckDB and PostgreSQL; into other warehouses, or for a table that excludes _sqlload_source_row, whose rows carry no number to tell which of them a later file in the same commit replaces, each file loads in its own commit. A file that comes before one already loaded, such as an older file sent late, or with order = "path" one rewritten while the later file isn’t, still fails rather than take back the later file’s values. By modified time a rewritten file sorts last and replaces them, and files rewritten together load again in order. Leave it at "fail" when the files may be parts of one export, as "last_wins" would keep only the last part’s rows of a value split across them. plan -v says so on the table’s batch line. It applies only to a files source’s delete_insert with a replace_key, and plan refuses it elsewhere: a merge already lets the last file win each key.

merge upserts each new file on its unique_key. A load merges its files in order of their modified time, then path, so a key that several of them carry ends with the value from the file modified last. Consecutive files merge in one commit, up to 1,000 files or about 1 GB, with the same rows as merging them one by one. A file that turns up after others merged, even with an older modified time, merges after them. Each commit also records its files and their versions in _sqlload_runs, in the same transaction, and later loads skip the files recorded there. If a commit fails, it rolls back with all its files and the commits before it stay merged; a file that fails to read or convert is named in the error. The next load continues from that commit’s first file. With on_duplicate_keys = "fail", each file merges in its own commit, so a key repeated across files isn’t refused.

[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. With newest_by, its rows still only replace rows they aren’t older than, so Monday’s file coming back after Tuesday’s leaves Tuesday’s rows.
  • order = "path" orders files by their path below path instead (a ZIP member’s as <archive>.zip/<member>), in code point order as text cursors compare, so the file whose name sorts last wins a repeated key whatever the modified times. Landing steps often rewrite modified times: unzipping (Python’s zipfile stamps the time it extracts), cp, gsutil and sync tools all do. Use it when names carry a sortable date, such as export_2026-11-14.csv; dates need ISO order and zero padding (2026-11-09, not 2026-11-9). plan names it: 3 files, 1 commit, in path order. It applies to delete_insert by replace_key too, and to --reload.
  • 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.
  • A file that holds one unique_key value in more than one row merges the last of those rows, or the newest by newest_by; see Repeated keys. With on_duplicate_keys = "fail", such a file fails instead, naming the key’s columns and examples, before anything of it merges. Rows whose key holds NULLs are collapsed too, a NULL matching a NULL.
  • Merging files loads into DuckDB and Postgres targets so far, and MotherDuck through its DuckDB connection, which isn’t tested live. 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), or run --reload on DuckDB, which keeps the old table until every file merged; see Reloading a files table.

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. GCS copies only if no file 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 "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 with move_to reads the moved files too, and with "delete" it’s refused; see Reloading a files table.
  • 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 fails unless on_empty = "skip"; see Empty folders.
  • It works on ZIP archives (compression = "zip"): an archive is deleted or moved once, after every member the load read from it (those matching members and the format) has committed, and only if it’s still the version read. If a member fails, the load fails and the archive stays where it is. plan refuses it while another files source on the same connection reads ZIP archives in the same folder, or one inside or around it, naming that source, since it may still need them. load --json counts archives, not members, under after_load.
  • It isn’t for SFTP connections, which SQLLoad only reads, or files targets.

By default a folder that exists but holds no files fails the load, as it can mean a broken mount or a wrong path: no .csv, .csv.gz, .csv.zst files found; ... where an empty folder is normal, set on_empty = "skip". A pipeline that clears its landing folder itself, so some days bring no files, sets on_empty = "skip":

[sources.orders]
kind = "files"
connection = "landing"
path = "orders/"
format = "csv"
description = "Orders unzipped into the landing folder by the download step."
write_strategy = "merge"
unique_key = ["order_id"]
order = "path"
on_empty = "skip"
  • The table is skipped and the load exits 0, with the table under skipped in plan and in load --json’s plan.skipped: no files to load in orders/ (on_empty = "skip"). The table is left as it is. On a filesystem connection that holds even before its first load.
  • Only an existing folder holding no file at all (hidden ones aside) counts as empty. A missing folder, one that can’t be read, and one holding files of another format or that don’t match path_columns still fail.
  • S3, GCS and SFTP list a folder that doesn’t exist as empty, so SQLLoad can’t tell a mistyped path from an empty one there. On those connections on_empty = "skip" skips an empty folder only once its table exists; before its first load it fails, naming the path.
  • after_load already skips a folder it emptied once its table exists, without on_empty.

Parquet columns come from the files’ footers. CSV and JSON columns are inferred from a sample of records, unless you pin them with columns. infer_types = false loads every column not pinned as text, CSV fields exactly as written; see Loading every column as text. Each row also gets _sqlload_source_file, _sqlload_source_version and _sqlload_source_row (Columns SQLLoad adds). BigQuery doesn’t report a row’s position in its file: into BigQuery, Parquet in GCS loads through BigQuery itself only when exclude_columns drops _sqlload_source_row; see Parquet in Google Cloud Storage.

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

A JSON or NDJSON object loads as one json column unless the table’s flatten loads its keys as <object>__<key> columns. See Flattening objects.

A table’s parse reads text columns as times, in a list of formats such as ISO and %m/%d/%Y, and in a time zone. See Parsing text into times.

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

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

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

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.

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, and in GCS when the gcs connection has an HMAC key.
  • MotherDuck reads Parquet in S3, and in GCS with an HMAC key, on its servers.
  • Snowflake runs COPY from an external stage over the files’ bucket, for Parquet, JSON, NDJSON and CSV.
  • ClickHouse reads Parquet in S3 with s3().
  • BigQuery queries Parquet in GCS as temporary external tables, on a table that excludes _sqlload_source_row, which BigQuery can’t fill.

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