(filesystem)= # 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. ::: (file-formats)= ## Supported formats The file format is inferred from the file extension (with optional `.gz` suffix), or by using a {ref}`format hint ` if your resource URI does not include file extensions. | Format | Description | Extensions | Format hint | Read | Write | |:-----------------|:------------------------------------------------|:-----------------------|:--------------|:-----|:------| | {ref}`avro` | Apache Avro object container files | .avro | #avro | ✅ | ❌ | | {ref}`bson` | Binary JSON (MongoDB dump format) | .bson | #bson | ✅ | ❌ | | {ref}`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 | ✅ | ❌ | | {ref}`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 | ✅ | ✅ | | {ref}`msgpack` | Efficient binary serialization format | .msgpack | #msgpack | ✅ | ❌ | | {ref}`ods` | OpenDocument spreadsheet format | .ods | #ods | ✅ | ❌ | | {ref}`orc` | Apache ORC format | .orc | #orc | ✅ | ✅ | | {ref}`parquet` | Apache Parquet format | .parquet | #parquet | ✅ | ✅ | | {ref}`vortex` | Vortex high-performance columnar data format | .vortex | #vortex | ✅ | ✅ | | {ref}`xlsx` | Excel spreadsheet format | .xlsx | #xlsx | ✅ | ✅ | | {ref}`xml` | XML format | .xml | #xml | ✅ | ❌ | | {ref}`yaml` | YAML format | .yaml, .yml | #yaml | ✅ | ✅ | :::{note} Supported formats for write operations are currently CSV, Feather, JSON, JSONL, ORC, Parquet, Vortex, XLSX, and YAML. ::: (filesystem-types)= ## Supported filesystems | Name | Description | Protocol scheme | Read | Write | |:--------------------------|:--------------------------------------------------|:--------------------------------|:-----|:------| | {ref}`Local files ` | Files on local and mounted filesystems | file:// | ✅ | ✅ | | {ref}`azure-storage` | Azure Blob and Data Lake Storage | az://, adls://, abfss:// | ✅ | ✅ | | {ref}`dbfs` | Databricks files on volumes and workspaces | dbfs:// | ✅ | ❌ | | {ref}`dropbox` | Dropbox | dropbox:// | ✅ | ❌ | | {ref}`s3` | Amazon S3 and compatible filesystems | s3:// | ✅ | ✅ | | {ref}`ftp` | File transfer protocol (FTP) | ftp:// | ✅ | ❌ | | {ref}`gcs` | Google Cloud Storage | gs:// | ✅ | ✅ | | {ref}`gdrive` | Google Drive | gdrive:// | ✅ | ❌ | | {ref}`hdfs` | Hadoop distributed file system | hdfs:// | ✅ | ❌ | | {ref}`http` | Files addressed by an HTTP or HTTPS URL | http://, https:// | ✅ | ❌ | | {ref}`oracle-oci` | Oracle Cloud Infrastructure Object Storage | oci:// | ✅ | ❌ | | {ref}`onedrive` | Microsoft OneDrive | onedrive:// | ✅ | ❌ | | {ref}`oss` | Alibaba Object Storage Service (OSS) | oss:// | ✅ | ❌ | | {ref}`r2` | Cloudflare R2 | r2:// | ✅ | ❌ | | {ref}`sharepoint` | Microsoft SharePoint | sharepoint:// | ✅ | ❌ | | {ref}`sftp` | SSH File Transfer Protocol | sftp:// | ✅ | ❌ | | {ref}`webdav` | Web Distributed Authoring and Versioning (WebDAV) | http+webdav://, https+webdav:// | ✅ | ❌ | | {ref}`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: ```sh 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 {ref}`Filesystem sources ` for the state, boundary, source-identity, and reset details. (format-hint)= (format-hints)= ## 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: ```sh 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. :::{rubric} Example: CSV files without headers ::: For CSV files without a header row, use the `#csv_headless` hint and optionally supply column names with `--columns`: ```sh 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-hint)= (reader-hints)= ## 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. ```text 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 {ref}`xlsx` readers forward corresponding parameters to the [polars.read_csv] and [polars.read_excel] functions, and the {ref}`xml` reader requires a `#tagname=` hint to define the repeated element that represents one record / row. ::: (workbook-tables)= ## 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 `.` syntax. For a plural workbook load, the dataset component is used and the table component is a placeholder: ```sh 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=` or `#sheet_id=`. 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)= ## 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: ```sh 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 {ref}`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](duckdb.md) or [SQLite](sqlite.md) 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)= ## 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. ```sh 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. ```sh 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 {ref}`file-load-types`. 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. (file-load-types)= ### 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. [CSV]: https://en.wikipedia.org/wiki/Comma-separated_values [iterabledata]: https://pypi.org/project/iterabledata/ [JSON]: https://en.wikipedia.org/wiki/JSON [JSONL]: https://en.wikipedia.org/wiki/JSON_streaming#JSONL [polars.read_csv]: https://docs.pola.rs/api/python/stable/reference/api/polars.read_csv.html [polars.read_excel]: https://docs.pola.rs/api/python/stable/reference/api/polars.read_excel.html