The OneLake catalog: specification

Status: built in waves (below). Waves 1 to 5 are in the code. Where it differs from Reference, this describes what is planned and that describes what runs today. Naming does not change.

The idea

Metadata lives in a local SQLite file, and data lives in OneLake with a cache on this machine. Three parts have one job each:

Anything that wants metadata reads the catalog. Nothing waits on OneLake to answer a question the catalog can answer.

 Tree        Tabs        Command line
    \          |          /
     \         |         /
   +-----------------------------+          OneLake,
   |  Window                     | ------>  Fabric REST API
   |  sign-in, read-only guard,  |          (listings, metadata)
   |  names, sync worker         |
   +-----------------------------+
      |                  |
      | reads, writes    | statements, token
      v                  v
 +-----------+      +-----------------------+
 |  Catalog  |      |  Server               | ------>  OneLake
 |  SQLite   |      |  engine: DuckDB and   |          (rows, files)
 +-----------+      |  disk cache           |
                    +-----------------------+

What must always hold

1. Works offline. Everything already read keeps working with no internet and no sign-in.
2. All metadata is in one local SQLite file, read and written with Node's built-in node:sqlite.
3. Updates are asynchronous. A background worker keeps the lists current, and a reader never waits for it unless it asked for something the catalog has never held.
4. The tree opens at once: before sign-in, with no server running, and with no DuckDB loaded.
5. Nothing bypasses the catalog, the command line included.

The catalog

One file per account: engine-server\catalog-<id>.sqlite in the extension's storage. An update keeps it, and F5 has its own.

Every row says when it was last read from OneLake.

• Who writes and reads: each window opens the file itself, in WAL mode, with short transactions, so a reader is not held up. Every window hears what another wrote from the file, within a few seconds. A walk over every table is one pass over lake_table and lake_column (allTables), not a query per table.
• A tab's SQL can't reach it: the file is outside the folders DuckDB may open.
• Rows are never evicted by age or size. Offline depends on them.
• A table missing from a complete listing is marked dropped, and removed when a later sync confirms it.
• It can be rebuilt. A file that can't be opened, or has an older schema (user_version), is set aside and refilled by the worker. Offline with no usable file, the tree says nothing is known.
• Clear Cached OneLake Data deletes it. Sign Out keeps it, and another account has its own.

What stays outside it:

The sync worker

It runs in a window's extension host, not the server (src/syncworker.js). That is where VS Code's sign-in, proxy and certificates already are, so no token or network path has to move, and syncing needs no DuckDB and no server. A lease in the catalog file says which window sweeps the tenant, and another takes over within 90 seconds when that one goes.

• Table metadata is read only for lakehouses opened, as prefetchMetadata does, and for tables in use, so the tenant's manifests are not walked. A staging item is never swept.
• Limits: one request at a time and five listings every 15 seconds, so a large tenant fills in over minutes. A 429, a 503 or no answer at all stops everything for a minute, and a listing that fails isn't asked again for an hour.
• Every window hears what another wrote: a look at the file every few seconds (PRAGMA data_version), and the tree redraws what changed.
• Offline: it asks nothing, and carries on by itself. Nothing waits on a timeout.
• A failure is kept in sync_state and shown on its node as refresh failed. The last good rows are still served.

Tables in use

A table is in use when statements read it 3 times in 14 days. Every statement that reads its rows counts, from a tab or the command line, and a model's query counts for the tables it reads. DESCRIBE and a plain EXPLAIN read none.

What such a table is short of isn't its repeat: a repeat already reads from disk. It is short of a current snapshot, and of the parts of it nothing has read. So the worker does two things for it:

1. Its metadata is read every hour, so a statement never waits for that and isn't answered from a snapshot a day old.
2. It is read whole, once at each snapshot, so every column works offline. The worker decides and DuckDB reads: EXPLAIN ANALYZE SELECT <column> FROM <table>, a column at a time, goes through the disk cache as any statement does, and no row reaches a client. Each is a line in Logs, under a comment that says why. When it finishes, the table's icon goes to a check.

It is the last thing to run:

• One at a time, by the window with the lease, on an engine already up. It never starts one.
• Only once nobody has run anything for 2 minutes, on any window or the command line. The server says so over the control pipe, so asking leaves no line in Logs.
• Anyone's statement stops it, within 3 seconds. What it read stays on disk, so the next try carries on from there. It tries again after 2 more quiet minutes.
• After 10 minutes it stops, and waits 6 hours, so a slow connection isn't kept busy. A read that fails waits as long.

What it may bring down, under onelakeStudio.keepUsedTablesGB, 10 GB by default, and never over half of diskCacheMaxGB. The disk cache evicts the oldest first, and what someone read comes before this.

A table over the limit says so in its tooltip. 0 turns the reading off, and the hourly metadata stays on.

The costs of this:

• A table in use is read whole once, at its current snapshot, even if what you read of it was already held.
• It runs on the engine's own threads and memory, and DuckDB's are shared, so it can be heard on a laptop until someone runs something.
• "Read whole" is what happened, not what is still there. The cache's blocks are opaque, so eviction can take them afterwards with nothing to say so.

Opening

1. The window opens the catalog with node:sqlite. It knows the last account from VS Code's state.
2. The tree draws from its rows, with no network and no prompt.
3. Then, behind it: silent sign-in, start or join the server, and the worker takes its queue.
4. DuckDB's native library loads only when something needs it. Nothing on the path to the tree may import @duckdb/node-api.

On a first run there is no catalog, so the tree shows sign-in or a loading node, then fills in.

What still waits: a statement waits for the server, and something never synced waits for its one job. Neither holds up the tree.

How a statement runs

As Reference describes, with one change: resolve and resolveFile query the catalog, in the window. The view is delta_scan on a Delta table's kept log, or iceberg_scan on an Iceberg table's metadata location. SHOW ALL TABLES and information_schema are answered from the catalog.

A name not in a kept listing asks the worker for that listing before it is called missing. So does a read that fails with an IO Error, for that table's listing and metadata.

The command line

Without --fresh, src/agent.js makes no OneLake or Fabric call. ls, items, models, files, find, table and exists read the catalog with the same code as the tree. status and log ask nothing either.

Does a table exist (exists): from the listings alone, so it needs no table's metadata, though what is held about one counts.

--fresh is a top-priority job. It goes ahead of what the worker has next, one request at a time, and what it reads goes into the catalog, so everyone sees one answer. It isn't held to five listings every 15 seconds, as someone is waiting. Offline it fails at once, and so it does while OneLake has asked for a pause.

What doesn't go through the worker: sql --fresh, whose statement reads its tables' metadata, and model --fresh, which downloads a model's file. Neither is a listing.

Target: tens of milliseconds for a metadata command with VS Code open.

Measured on a real tenant, 4,988 tables, with VS Code open: a table-exists check over the pipe is 59ms yes, and 69 to 140ms no with the closest names, in a lakehouse of 521 and of 2,014 tables. ls is 63ms, and find over everything is 253ms. A new node process adds about 170ms before any of that.

Reading data

Nothing downloads a file on its own, unless a table is in use (Tables in use). Otherwise the sync worker reads listings only. Bytes move when someone asks for them:

A table opens on Stats by default, which reads nothing. When a table not in use changes, nothing downloads until someone reads it again.

DuckDB keeps reading the bytes: a Delta table with delta_scan, and an Iceberg table with iceberg_scan, over abfss://, azure and cache_httpfs.

A Delta table is never read through Fabric's Iceberg conversion. Measured on a real table: a file's own column ids disagreed with the conversion's, and iceberg_scan gave one column another's values with no error. The conversion's metadata is still where a table's format and Stats come from.

delta_scan reads the log itself, past DuckDB's file system. On the real table, DuckDB's file log held the Parquet files and no log file. So a Delta table reads through one path, online and offline (src/deltamirror.js):

1. Its log is kept in the cache folder: the latest checkpoint, the commits after it, and any deletion vectors, copied as they are and never rewritten. It's asked for again past catalogTtlMinutes, and when a read fails. A file whose size differs from the listing's, or that was written after the copy, is copied again: a table dropped and made again starts its log over.
2. delta_scan runs on that folder. It reads no log from OneLake, online or offline.
3. A mount serves the Parquet files it names. The log's paths are relative, so delta_scan asks for them under the local folder. cache_httpfs_mount in our fork of cache_httpfs serves them from OneLake, through the disk cache and under their abfss:// paths.

Rewriting the log to absolute paths was tried and isn't the way: delta_scan takes a path as absolute only if it starts with /, and a rewrite would mean producing Delta's metadata ourselves. A table's log is in a folder of its own, so the disk cache's eviction doesn't touch it.

Measured on the real scada table, 22 million rows, with the engine's lockdown on and a space in the folder's name:

Mounting onto a local folder or a file:// path is refused by the lockdown.

No file downloader, because of what was measured on generated local Delta tables of 40 files and 131 MB:

• LIMIT 5 opened all 40 files and read 37 MB, with read_parquet too, and at one thread as at eight.
• With files missing, a loop that fetched each file DuckDB asked for fetched 17 to 40 of the 40 for LIMIT 5.
• So a file store has to choose each statement's files itself, and reading one column of a big table still fetches whole files. Reading parts of files already works offline.

The costs of this choice:

• The patched, unsigned cache_httpfs stays, and reading rows offline depends on it.
• The catalog can't say how much of a table is held, as the cache's blocks are opaque.
• httpfs stays in any case: quack_serve fails without it on DuckDB 2.0.0.

data_file keeps each file's abfss:// path, so a file store can be added later without changing the catalog.

Offline

Whatever isn't held says so at once.

A table that moved on since it was read: its metadata was read again online, and its new snapshot's rows never were. Offline, a statement and the Data tab read the last snapshot that was read, and its age is when that was read. Stats shows the newest metadata held. Back online, it's the current snapshot again.

What goes, and what stays

ARCHITECTURE.md changes when this ships: The host does the work no longer covers listings and metadata, The cache and Offline describe the catalog, and the tree no longer waits for the server.

What it needs

A higher minimum VS Code. engines.vscode is now ^1.101.0, the first version on Node 22. node:sqlite has been run on 1.144 only, so whether 1.101 itself has it is still to be shown.

Open items

Waves

Each wave works on its own, and is proved before the next starts.

On its own, whenever: Iceberg's files in a folder eviction skips. It probably needs a change in the cache_httpfs fork.

Only if needed: data_file.

How it is tested

Through CI and F5, as for everything here.

• Unit (test/js): rows from fixtures, queue priority, back-off, pause and resume, dropped tables, the import.
• Command line (test/js/cli*.test.mjs): every metadata command answers with the network refused, the three answers above with their exit codes, and --fresh going through the queue.
• e2e (signed in, no internet): the tree, listings, Stats and History answer, a table already read returns rows, and what isn't held fails at once.
• Timing, in CI and on a real machine: activation to a drawn tree with no server, signed out, with no internet; and a table-exists check through bin/cli.mjs, warm and cold.
• On a real tenant: synced from empty with the tree usable throughout, online to offline and back, two windows, and a table added in Fabric appearing with no click.