Filesystem¶

omniload supports reading and writing different file formats from various local and remote filesystem types.

Filesystem handlers support globbing, gzip decompression, as well as format and reader hints.

Note

A concrete filesystem source path must match an existing file. If it does not, the ingest fails during file discovery. A glob may match zero files and still succeed. Supported glob syntax uses *, ?, and [...]; braces such as {a,b} are treated as literal filename characters. This rule is based on matched files, not loaded rows, so a parseable file with no data rows and an incremental re-run with no new rows remain valid.

Supported formats¶

The file format is inferred from the file extension (with optional .gz suffix), or by using a format hint if your resource URI does not include file extensions.

Format	Description	Extensions	Format hint	Read	Write
Apache Avro	Apache Avro object container files	.avro	#avro	✅	❌
BSON	Binary JSON (MongoDB dump format)	.bson	#bson	✅	❌
CBOR	Concise Binary Object Representation (RFC 8949)	.cbor	#cbor	✅	❌
CSV	Comma-separated values with a header row	.csv	#csv	✅	✅
CSV (DuckDB)	Comma-separated values read with DuckDB	.csv_duckdb	#csv_duckdb	✅	❌
CSV (headless)	Comma-separated values without a header row	.csv	#csv_headless	✅	❌
Feather	Feather V2, the Apache Arrow IPC file format	.feather, .arrow, .ipc	#feather	✅	✅
JSON	One JSON document: an object or an array	.json	#json	✅	✅
JSONL	Newline-delimited JSON	.jsonl	#jsonl	✅	✅
MessagePack	Efficient binary serialization format	.msgpack	#msgpack	✅	❌
ODS	OpenDocument spreadsheet format	.ods	#ods	✅	❌
ORC	Apache ORC format	.orc	#orc	✅	✅
Parquet	Apache Parquet format	.parquet	#parquet	✅	✅
Vortex	Vortex high-performance columnar data format	.vortex	#vortex	✅	✅
XLSX	Excel spreadsheet format	.xlsx	#xlsx	✅	✅
XML	XML format	.xml	#xml	✅	❌
YAML	YAML format	.yaml, .yml	#yaml	✅	✅

Note

Supported formats for write operations are currently CSV, Feather, JSON, JSONL, ORC, Parquet, Vortex, XLSX, and YAML.

Supported filesystems¶

Name	Description	Protocol scheme	Read	Write
Local files	Files on local and mounted filesystems	file://	✅	✅
Azure Storage	Azure Blob and Data Lake Storage	az://, adls://, abfss://	✅	✅
Databricks files	Databricks files on volumes and workspaces	dbfs://	✅	❌
Dropbox	Dropbox	dropbox://	✅	❌
S3	Amazon S3 and compatible filesystems	s3://	✅	✅
FTP	File transfer protocol (FTP)	ftp://	✅	❌
GCS	Google Cloud Storage	gs://	✅	✅
Google Drive	Google Drive	gdrive://	✅	❌
HDFS	Hadoop distributed file system	hdfs://	✅	❌
HTTP	Files addressed by an HTTP or HTTPS URL	http://, https://	✅	❌
OCI	Oracle Cloud Infrastructure Object Storage	oci://	✅	❌
OneDrive	Microsoft OneDrive	onedrive://	✅	❌
OSS	Alibaba Object Storage Service (OSS)	oss://	✅	❌
R2	Cloudflare R2	r2://	✅	❌
SharePoint	Microsoft SharePoint	sharepoint://	✅	❌
SFTP	SSH File Transfer Protocol	sftp://	✅	❌
WebDAV	Web Distributed Authoring and Versioning (WebDAV)	http+webdav://, https+webdav://	✅	❌
WebHDFS	WebHDFS REST API for Hadoop	webhdfs://	✅	❌

Note

omniload supports read and write operations on both local and remote filesystems. For some filesystems, write support has not been unlocked yet, but we expect it to land during the upcoming releases.

Incremental file selection¶

Filesystem sources append every matching file again on a normal re-run. Add --filesystem-incremental to keep an mtime cursor and append rows only from new or newly modified files:

omniload ingest \
    --source-uri 'file:///data/events/*.jsonl' \
    --dest-uri duckdb:///local.duckdb \
    --dest-table 'public.events' \
    --filesystem-incremental

This mode is opt-in and append-only. Its cursor needs durable dlt pipeline state: the default temporary pipeline directory works when the destination supports state sync, while other destinations require a stable --pipelines-dir. Files at the current maximum modification time are deduplicated by their URL, and an older-mtime backfill requires --full-refresh to reset the cursor.

For HTTP and HTTPS, each selected file must return a valid Last-Modified header. An incremental directory-index glob makes one metadata request per matched file to obtain it. A missing or malformed header stops extraction instead of substituting the current time.

Note

--filesystem-incremental and --columns describe the reader, not the storage service, so they apply to every scheme on this page and mean the same thing on each. Everything else a source URI carries is that filesystem’s own connection configuration.

The single-file csv:// and file:// destinations are rejected because they replace their output using only the rows selected for the current run.

See Filesystem sources for the state, boundary, source-identity, and reset details.

Format hints¶

If a file is correctly encoded but has a non-standard extension, append a #format fragment to tell omniload how to read it:

omniload ingest \
    --source-uri 'file://data/event-data#jsonl' \
    --source-table 'events' \
    --dest-uri duckdb:///local.duckdb \
    --dest-table 'public.events'

If the format hint is not one of the known formats, the path is preserved 1:1, so file://data/vendor#1/report.csv reads the file at data/vendor#1/report.csv as CSV.

Example: CSV files without headers

For CSV files without a header row, use the #csv_headless hint and optionally supply column names with --columns:

omniload ingest \
    --source-uri 'file://data/raw-data.csv#csv_headless' \
    --source-table 'raw' \
    --columns "id:bigint,name:text,value:double" \
    --dest-uri duckdb:///local.duckdb \
    --dest-table 'public.raw_data'

Without column names, columns are auto-named unknown_col_0, unknown_col_1, and so on.

Reader hints¶

The URI fragment is also a channel to convey reader hints: Besides a #format token, it can carry #key=value pairs that a reader may use to parametrize how a file is read. For example, the spreadsheet reader takes a sheet_name parameter. A format hint and named hints can coexist in one fragment, &-separated.

file://quotes.dat#xlsx&sheet_name=daily&header=0

The named-hint grammar:

Values are percent-decoded, so #sheet_name=My%20Sheet gives the value My Sheet and #sheet_name=R%26D gives R&D.
Only the first = splits key from value, so a value may itself contain = (#range=A1=B2 gives range = A1=B2).
An empty value is preserved (#sheet_name= gives sheet_name = ""); a reader decides whether that means “unset”.
Duplicate keys take the last value (#sheet_name=a&sheet_name=b gives b).
JSON arrays select several values when the reader accepts a list, for example #sheet_name=["events","inventory"].
If any segment of the fragment is neither a key=value pair nor a single known format, the whole #... is treated as a literal part of the path, so a real # in a filename keeps working. Percent-encode a literal # as %23 when a trailing path#key=value would otherwise be read as a fragment.

Note

Reader hints can be used to forward additional parameters as key=value pairs to the underlying pipeline element implementation. For example, CSV and XLSX readers forward corresponding parameters to the polars.read_csv and polars.read_excel functions, and the XML reader requires a #tagname=<row-tag> hint to define the repeated element that represents one record / row.

Workbook tables¶

XLSX and ODS workbooks are multi-table sources. Without a worksheet selector, omniload reads every worksheet and uses each worksheet name as its destination table name. Empty worksheets are skipped.

--dest-table keeps its usual <dataset>.<table> syntax. For a plural workbook load, the dataset component is used and the table component is a placeholder:

omniload ingest \
    --source-uri 'file://data/workbook.xlsx' \
    --dest-uri 'duckdb:///local.duckdb' \
    --dest-table 'landing.workbook'

A worksheet named Quarterly Sales becomes quarterly_sales under the default schema naming convention. With --schema-naming direct, its name remains Quarterly Sales. Distinct worksheet names that resolve to the same table under the active convention fail before loading, with both worksheet and workbook names in the error.

When a glob matches several workbooks, worksheets with the same original name merge into one destination table. Different original names that collide after normalization fail instead of merging silently.

Select one worksheet with #sheet_name=<name> or #sheet_id=<number>. A single selection keeps the existing one-table behavior and uses the table component of --dest-table. Select several worksheets with a JSON array such as #sheet_name=["events","inventory"] or #sheet_id=[1,2].

Note

The csv:// and file:// destinations each produce one output file, and the cloud blob destinations currently address one table path. They reject a plural workbook load. Select one worksheet or use a dataset-capable destination.

Database files¶

An object whose name carries a database extension is a database, not a file to decode. omniload hands it to the SQL source instead of a reader, so --source-table selects a table inside the database and the usual SQL reflection, chunking, and type mapping apply:

omniload ingest \
    --source-uri 's3://analytics/snapshots/events.duckdb?access_key_id=ACCESS&secret_access_key=SECRET' \
    --source-table 'main.events' \
    --dest-uri 'duckdb:///local.duckdb' \
    --dest-table 'raw.events'

Extension	Engine
.db	SQLite or DuckDB, read from header
.ddb, .duckdb	DuckDB
.sqlite, .sqlite3	SQLite

The engine is read from the file header, so a mislabeled object still loads. An empty file falls back to its extension, and a .db file that is neither engine reports both candidates.

Databases are read from s3://, r2://, gs://, az://, adls://, and abfss://. Credentials and storage options are the same query parameters the matching filesystem source takes. The URI names one object: globs, # fragments, and credentials in the authority are rejected rather than silently reinterpreted. The object path also has to ride --source-uri, because --source-table names a table inside the database rather than the object, unlike the split form the file sources accept.

Local databases need no filesystem source. Address them with DuckDB or SQLite directly, as duckdb:///path/to/events.duckdb.

Note

The object is downloaded whole into a run-scoped temporary directory before it is opened, so the database must fit on local disk. The copy is removed after success and after failure, and changes to it are never written back. Remote databases are sources only.

File format routing¶

omniload reads each file format through the best available path rather than one generic reader. This section explains how that routing works, so the individual per-format pages (Avro, BSON, CBOR, Feather, MessagePack, ORC, Parquet, XML, YAML) can stay focused on how to use each format.

In general, omniload builds mostly upon the excellent fsspec, polars and iterabledata packages for local and remote filesystem access and format decoding.

Format	Library	Description
CSV (`#csv`), JSONL	`polars` / `pyarrow`	Built-ins.
CSV (`#csv_duckdb`)	`duckdb`	DuckDB-backed CSV reader.
Avro	`polars`	Whole-file format.
BSON	Dedicated in-tree codec	Needs extended-type normalization.
CBOR	`cbor`	Whole-file format.
Feather	`pyarrow`	Batched reader and writer.
JSON	`orjson`	Whole-document parse.
MessagePack	`iterabledata`	Streamed record-by-record.
ODS	`polars`	Whole-file format.
ORC	`pyarrow`	Striped reader and writer.
Parquet	`pyarrow`	Batched reader.
Vortex	`vortex-data`	Batched reader and writer.
XML	`lxml`	Whole-file parse, hardened.
XLSX	`polars` / `xlsxwriter`	Whole-file reader, row writer.
YAML	`yaml`	Whole-file decode, safe.

omniload uses the iterabledata package for reading or decoding a few formats not covered by native reader implementations. Install the iterable extra to make them available to your environment.

pip install 'omniload[iterable]'

iterabledata exposes a uniform per-format class that yields record dicts from a file object. When routing to iterabledata, omniload feeds it the source’s fsspec filesystem handle that is already authenticated, so remote filesystem access to Amazon S3, Azure Blob Storage, Google Cloud Storage, or SFTP works transparently.

Where using iterabledata is not applicable, for example to enhance error handling, or applying stronger security policies, omniload directly uses relevant low-level decoder libraries.

Vortex needs the vortex extra, which requires Python 3.11 or newer.

pip install 'omniload[vortex]'

omniload installs the BSON, DuckDB CSV and spreadsheet decoders itself. Installing the filesystem package on its own, as dlt-filesystem, leaves them to its bson, duckdb and spreadsheet extras.

File format notes¶

Performance¶

Where available, omniload uses Polars to read and decode files from local and remote filesystems. Polars builds upon Apache Arrow and is written in Rust. This guarantees robustness and speed.

Whole-file decode¶

Some formats are whole-file rather than streaming. For those, omniload decodes the bytes with the format’s own library directly.

JSON and JSONL¶

.json and .jsonl are separate formats. .jsonl is always read as one record per line. .json is read as one document: an object loads as a single row, an array loads as one row per element, and indentation makes no difference. A .json file that turns out to hold line-delimited records still loads, because records are the fallback once the document does not parse as one value.

Note

Both are available for write operations, and each writes the shape its reader expects: .jsonl one record per line, .json a single array document.

Streaming¶

Records are pulled in batches until the reader signals end-of-file, and flushed per file so a multi-file glob never drops a partial final chunk. iterabledata rewinds the handle on construction, which fails on a non-seekable stream (a pipe, some compressed or SFTP handles); such a stream is spooled into memory first, while a seekable handle streams straight through.

Type normalization¶

Binary formats carry types JSON does not implement, for example raw bytes, timestamps, tagged values. The decoders hand some of those back as native Python objects that a text or Parquet loader can not serialize. omniload normalizes rows to portable values before handing data to the loader.

bytes becomes a base64-encoded string, which is portable across text loaders and Parquet alike. This covers CBOR / MessagePack binary value types and YAML !!binary values.
An unknown CBOR tag becomes a plain {"tag": ..., "value": ...} object rather than crashing the load.
A MessagePack Timestamp extension becomes a UTC datetime.
XML doesn’t need any normalization: Its values are all strings or nested objects/lists, which every loader handles equally well.
A YAML !!set type becomes a list.

Some values are made portable by the decoder itself rather than by omniload: cbor2 decodes the standard CBOR tags (datetime, big integers, decimals) into native Python types directly.

Handed one of these Python values directly, a format with no column type of its own for it writes it as text rather than refusing it, so a decimal lands in a JSON, JSONL, YAML or CSV file as 1.50, with the scale a float would drop, while Parquet carries it as a decimal column. Nested maps and arrays are handled recursively. The exact per-format mapping is on each format’s own documentation page under “Extended-type handling”, for that format’s decoder or its writer as the format allows; several are read-only.

Whether a writer is handed such a value at all is a separate question, decided by the staging format rather than by the destination: see What a load delivers. Under the default staging a decimal reaches every writer as a string, so it is written as text even where the format could hold a decimal column.

CSV and XLSX are the write formats with neither a nested nor a binary column type. A struct, a list or a bytes value is written there the way the JSON writers write it: JSON text for a document, a base64 string for binary. So a nested source exports to either rather than failing, and the cell holds something a reader can parse.

What a load delivers¶

An ingest is not the reader and writer back to back: dlt stages the rows between them, and the staging format decides what a writer receives. So the values that reach a destination file are chosen by the staging format, not by how much the destination format can hold.

The table below is for a local file:// destination, which stages gzip JSONL by default. A warehouse destination that supports Parquet staging, DuckDB and BigQuery among them, selects it automatically from a filesystem source, so such a load follows the right-hand column without being asked.

Source column	Default staging (gzip JSONL)	`--loader-file-format parquet`
integer, float, boolean, string	itself	itself
date, timestamp, time, binary, decimal	ISO or base64 string	itself, retyped by dlt’s schema
list, struct	itself	JSON string
all-null column	dropped	dropped
duration	load fails	load fails

“Itself” describes what staging hands the writer, not what the file ends up holding, and it carries one qualification: dlt infers a schema rather than copying the source’s, so a string shaped like a timestamp is retyped as one. 2026-09-01T12:00:00 reaches a default-staged load as the text 2026-09-01T12:00:00+00:00, and a Parquet-staged one as a UTC timestamp column.

Warning

ORC cannot store a time column at all. A source carrying one loads to .orc under the default staging, where the column is text by then, and fails under Parquet staging with Unknown or unsupported Arrow type: time64[us].

Two of those rows are worth spelling out.

A duration column cannot be loaded at all. dlt’s extract step serializes rows as JSON and refuses a Timedelta, so the run fails with Type is not JSON serializable: Timedelta before any writer sees it. Cast such a column in the source query if you need it.

A column that is null in every row does not reach the output. dlt omits a null key per row, so a wholly null column has no keys anywhere and the writer never learns it existed.

Under Parquet staging dlt applies its own schema rather than the source’s, so a naive timestamp arrives as UTC and a decimal is converted to dlt’s default scale of 9. That conversion has to be lossless rather than rounding: it fails the load with Rescaling Decimal value would cause data loss where it would discard a nonzero fractional digit. 3.14 converts, and so does a value padded with trailing zeros past scale 9; 3.1400000001 does not. The precision the destination then records is the writer’s choice, not dlt’s: a source decimal128(38, 2) carrying 3.14 lands as decimal128(10, 9) in Feather and ORC, and as decimal128(38, 9) in Parquet. Use the default staging when you want the text form, and Parquet staging when you want typed columns.

Integrity and truncation¶

The read mechanism determines how a damaged file behaves, and it is worth knowing which guarantee you get.

Empty files raise reader errors for Avro, CSV, Feather, ORC, and Parquet. Empty JSON and JSONL files yield no rows.
Whole-file decode (CBOR, XML, YAML) raises on a corrupt or malformed file rather than loading partial data. CBOR additionally must be a single top-level value; files that concatenate several top-level objects are read only up to the first, a decoder limitation that cannot be detected at read time. XML additionally rejects an entity-expansion bomb and a mismatched encoding declaration.
Block-structured formats (Avro) carry no trailing index or record count, so a tail cut exactly at a block boundary leaves a shorter valid file that nothing can distinguish from the original: the surviving blocks load and nothing is reported. A cut one byte into the next block’s header is detectable corruption and is nonetheless accepted the same way, which is a limitation of the reader rather than of the format; two or more bytes in does raise. Feather, ORC and Parquet each carry a footer that a truncation destroys, so they raise on the same damage.
Streaming formats (MessagePack) carry no length prefix, so a truncated tail reads as a clean end-of-file: the partial trailing record, and anything after a mid-stream corruption, are dropped silently. Validate file integrity upstream if partial loads would be a problem.