← All notes

An ADBC-over-ODBC backend for omniload, built on request

omniload's maintainer asked whether adbcBridge could give the dlt-based loader ADBC and ODBC reach; the answer is a fourth SQL backend, contributed as a pull request with tests on PostgreSQL, CrateDB and SQL Server.

The request

omniload is a polyglot data loader built on dlt: one command line and one URI scheme to copy a table from any source to any destination, with dlt's incremental append, merge and scd2 strategies. On 2026-09-15 its maintainer asked on panodata/omniload#138 whether adbcBridge could "support omniload and/or dlt in one way or another to bring ADBC support to the table", and after a same-day exchange settled on the shape: a dlt table-loader backend registered through dlt's own hook, "to make incremental loading work as usual", building on the unixODBC, PostgreSQL and SQL Server ODBC drivers the omniload container image already ships.

What was built

panodata/omniload#362 adds --sql-backend adbcbridge next to omniload's sqlalchemy, pyarrow and connectorx backends. It is one commit of fourteen files: a loader module, four small changes to the model, command line, API and router, a dependency extra, a handbook page with pointers from three source pages, a changelog entry, and two test files.

How the backend fits dlt

dlt's sql_table source builds its query with SQLAlchemy and hands it to a table loader, one class per backend, selected by name. dlt 1.22 added register_table_loader_backend(), which is the hook omniload's maintainer pointed at. The adbcbridge loader is a small subclass of dlt's BaseTableLoader: it lets the base class build the query, cursor filter and ordering included, compiles it to SQL text with literal binds, executes that text through adbcBridge, and yields Arrow record batches. Because the query is dlt's own, --incremental-key, --interval-start and --interval-end, --sql-limit and --sql-exclude-columns behave exactly as with the other backends, and dlt's incremental state is unchanged. The registration happens the first time the backend is selected, so the extra is only needed by users who ask for it.

Where the ODBC connection string comes from

omniload's users give one URI, the SQLAlchemy one, and that URI is still needed for reflection. Asking for a second, ODBC-shaped string on every run would have been a poor fit, so the loader derives it from the SQLAlchemy URL for the families whose ODBC drivers omniload's container image already installs, plus two more:

These are the shapes adbcBridge's own compatibility matrix verifies per driver. The registered driver names are the ones Debian's odbc-postgresql and Microsoft's msodbcsql18 packages install, which is what the omniload image carries; a per-family environment variable renames a driver, and --sql-odbc-uri supplies the whole string for any other database, a DSN, or a non-default port. CrateDB's crate:// URIs name the HTTP port, so a URI on 4200, or with no port, maps to the PostgreSQL wire port.

Two rules carried over from omniload

omniload's scd2 strategy compares rows by a hash dlt computes only for Python rows, so it already rejects the pyarrow and connectorx backends by name; adbcbridge produces Arrow too and joins that list, with a test. A query: source is read through sqlalchemy whatever backend is named, as before.

How it was tested

Everything ran in omniload's own harness, on Linux, then again on macOS.

• The Docker-free unit lane, with thirteen new tests: the command-line choice, the derivation table above, the driver-name override, quoting of a value that carries the separator, the explicit-string error for an unknown scheme, idempotent registration, the router forwarding the string to dlt, and the scd2 rejection. The suite's failure set before and after the change is identical on Linux; on macOS it has no failures.
• Against containers, in one session: on PostgreSQL 18 a differential load against omniload's pyarrow backend over integer, double, text with non-ASCII characters, numeric, boolean, date, timestamp(0), timestamptz(3) and timestamp columns; incremental append through the command line over three runs, with a row past the cursor arriving and an older one not; merge with a primary key and an update; a page size of seven rows over 1,000 rows, and a row limit; a custom query: source. On CrateDB 5.10.11 an append load over the PostgreSQL wire protocol with an explicit ODBC string. On SQL Server 2025, the image omniload pins, and on 2022, a differential load against the sqlalchemy backend. Seven of seven pass.
• omniload's container image, built from the branch: with no override, a postgresql:// source loads through the image's own registered PostgreSQL ODBC driver on Python 3.14, and a timestamp(0) column arrives at second precision.
• The macOS pass repeated the unit lane and the PostgreSQL differential on a scratch cluster built from source, and that is where the differential failed: the cluster's initdb had taken the host's time zone, and adbcBridge turned out to assume a UTC session on PostgreSQL-wire servers, shifting timestamptz values on read and on ingest. That defect is fixed in adbcBridge 0.1.3, which the extra now pins, and the same cluster passes the differential with it. It is written up on its own page.

Three things the harness taught along the way, none of them the backend's: SQL Server 2025 aborts at start on a hybrid-core laptop CPU unless the container is pinned to a uniform set of cores; two containers starting at once can lose a race for the harness's reaper container, which a retry settles; and omniload's query: path patches dlt for the life of the process, so the custom-query test runs in a subprocess.

Why it matters

For omniload users this is Arrow straight from the driver on databases that have an ODBC driver and no native ADBC one, SQL Server first among them, and a second route on the PostgreSQL-wire servers where the native driver's COPY read path stops, such as CrateDB. For adbcBridge it is the first integration requested from outside, and the first time the driver is exercised inside another project's test harness rather than its own.

Where this comes from: the adbcBridge compatibility matrix runs one workload through every database's ODBC driver on Linux, macOS and Windows and records each failure with its first error; the rows for these databases are at https://adbcbridge.org/matrix/#postgres, https://adbcbridge.org/matrix/#cratedb and https://adbcbridge.org/matrix/#mssql, and the request and the pull request are linked above.