Filesystem: Converge `csv://` on `file://`

[hampsterx]

About

csv:// is the last format-named scheme in either registry. excel:// was folded into the
filesystem readers in 67233322; everything else in sources and destinations names a
transport or a database. You called this one
in GH-106 already:

csv:// is certainly not a valid protocol scheme and should not be used as such [...] let's
exclusively use file:// for addressing filesystem-based resources going forward?

Agreed. This is what it costs, because csv:// is not a thin alias today: it has its own read
and write implementations, the two produce different results from the same file, and the write
one loses rows.

Verified at 02a60bcb (v0.12.0).

The legacy destination drops rows, silently

omniload.target.csv.CsvDestination.post_load() reads exactly one staged file:

def find_first_file(path):
    for entry in os.listdir(path):
        full_path = os.path.join(path, entry)
        if os.path.isfile(full_path):
            return full_path
    return None

dlt stages {dataset}/{table}/{load_id}.{ext} and rotates load files once file_max_items or
file_max_bytes is configured, so a load that produces more than one data file is written out
partially, and the run reports success.
dlt_filesystem.target.local.LocalFilesystemDestination iterates sorted(os.listdir(table_dir))
and does not have this.

printf 'id,name\n1,alice\n2,bob\n3,carol\n' > three.csv
export DATA_WRITER__FILE_MAX_ITEMS=1

omniload ingest --source-uri "file://$PWD/three.csv" --source-table t \
    --dest-uri "csv://$PWD/out_csv.csv"   --dest-table public.t
omniload ingest --source-uri "file://$PWD/three.csv" --source-table t \
    --dest-uri "file://$PWD/out_file.csv" --dest-table public.t

out_csv.csv        out_file.csv
id,name            id,name
2,bob              3,carol
                   1,alice
                   2,bob

One row of three, exit code 0. Separately, and less serious: file:// emits rows in staged-file
order rather than source order, visible above.

The read paths disagree too

omniload.source.csv.api.LocalCsvSource parses with csv.DictReader; file:// resolves to
read_csv, which is Polars (GH-165).

printf 'id,name,note\n1,alice,\n,,\n2,bob,hello\n' > one.csv

omniload ingest --source-uri "csv://$PWD/one.csv"  --source-table t \
    --dest-uri duckdb:///a.duckdb --dest-table public.t
omniload ingest --source-uri "file://$PWD/one.csv" --source-table t \
    --dest-uri duckdb:///b.duckdb --dest-table public.t

csv://   id VARCHAR, name VARCHAR, note VARCHAR   2 rows
         {'id': '1', 'name': 'alice', 'note': None}
         {'id': '2', 'name': 'bob',   'note': 'hello'}

file://  id BIGINT,  name VARCHAR, note VARCHAR   3 rows
         {'id': 1,    'name': 'alice', 'note': None}
         {'id': None, 'name': None,    'note': None}
         {'id': 2,    'name': 'bob',   'note': 'hello'}

DictReader yields strings, so without explicit --columns hints or a constraining existing
schema a csv:// load lands as text; the Polars path infers. LocalCsvSource.dlt_source also
skips rows whose values are all empty, which is the ,, line, and remove_empty_columns drops
empty-valued keys per row, leaving dlt to reconstruct the column from whichever rows carry it.

LocalCsvSource passes uri.split("://")[1] straight to open(), so csv:// does no glob
expansion, no gzip decompression and no #format hint parsing, exposes no CSV dialect or parser
options, and the split form the filesystem family supports
(--source-uri csv:// --source-table one.csv) fails with
[Errno 2] No such file or directory: ''.

Incrementality is the one thing that only exists here

LocalCsvSource.handles_incrementality() is False, so run_ingest keeps --incremental-key
and the adapter filters rows against incremental.start_value. The filesystem family returns
True, and run_ingest preserves the CLI request as requested_incremental_key, which
dlt_filesystem.source.fsspec.local rejects:

ValueError: Local file source takes care of incrementality on its own, you should not provide incremental_key

So between the two local-file schemes, only csv:// attempts row-level cursor filtering.
--filesystem-incremental selects whole files by modification time, which is a different
mechanism.

It half works, though. --incremental-key alone loads, because the stored cursor is also a
string. Adding explicit bounds crashes, because the CLI parses those into datetime:

printf 'id,updated_at\n1,2024-01-01\n2,2025-06-01\n' > dates.csv
omniload ingest --source-uri "csv://$PWD/dates.csv" --source-table t \
    --dest-uri duckdb:///c.duckdb --dest-table public.t \
    --incremental-key updated_at --interval-start 2025-01-01

TypeError: '<' not supported between instances of 'str' and 'datetime.datetime'

So whatever is kept here has to be fixed, not ported as-is.

Write side, the rest of it

Both destinations are the same shape (stage into a temp directory, reassemble one file in
post_load()), and CsvDestination.dlt_dest already rewrites csv:// to file:// before
handing the URI to dlt. LocalFilesystemDestination's docstring records the relationship. They
differ in how they deal with dlt omitting null keys per row: CsvDestination rewrites the file
with an expanded header when a later row introduces a new column,
LocalFilesystemDestination buffers and takes the column union for CSV and Parquet (JSONL keeps
rows sparse). GH-168 carries that trade-off.

Docs

docs/supported-sources/file.md has a "Relationship to csv://" section telling readers
csv:// is unchanged and to use it "only when you specifically want the standalone CSV reader".
I wrote that in GH-135 as transitional wording; it and docs/supported-sources/csv.md are what
change with whatever is decided here.

Questions

@amotl, three calls before this is mechanical:

• Column types change for existing csv:// users: text becomes inferred. Straight swap in one
  release, or a release of deprecation warning first?
• Row-level --incremental-key exists on this scheme and nowhere else in the family. Drop it
  with csv://, or fix the string/datetime comparison and move it into the readers, which puts
  a row-level concept into a family that is file-level throughout?
• A literal registry alias would also make csv://data.jsonl and csv://*.csv work, since the
  filesystem source routes on extension. Is csv:// meant to widen like that on its way out, or
  should it stay CSV-only and warn?

[amotl]

Evaluation

The legacy destination drops rows, silently.

Please fix when applicable and possible. 🙇

The read paths disagree too.

Is there any action to be taken to improve the situation? If so, please go ahead at your disposal.

Incrementality is the one thing that only exists here. The implementation has issues, so whatever is kept here has to be fixed, not ported as-is.

Please fix or improve at your disposal. Thank you in advance!

Write side, the rest of it and Docs.

Please proceed with corresponding changes as you've suggested. Thank you!

Questions

Straight swap in one release, or a release of deprecation warning first?

As we are still in the v0.x.x release range, please proceed with breaking changes eagerly; educating users via changelog item, and possible a little emphasis, will be good at the moment.

Row-level --incremental-key exists on this scheme and nowhere else in the family. Drop it with csv://, or fix the string/datetime comparison and move it into the readers, which puts a row-level concept into a family that is file-level throughout?

I am not able to detect yet if you think this detail would be good or bad. My thoughts are: If that feature is viable for reading from csv://, why shouldn't it be viable for other tabular data formats?

A literal registry alias would also make csv://data.jsonl and csv://*.csv work, since the filesystem source routes on extension. Is csv:// meant to widen like that on its way out, or should it stay CSV-only and warn?

If you think it is a good idea to use a literal registry alias (I am not 100% sure about it after only quickly trying to digest your suggestions), yes, please warn or even reject such misspellings: csv://, as long as the codebase keeps it up, should be CSV-only, right.