Local data engine and OneLake publishing: audit and plan

The program FD-LDE-001 to FD-LDE-020 makes Fabric Desktop by KratosBI a local-first SQL development environment: register a folder of data files, query them as tables on this PC without starting a Fabric job, build persistent transformed datasets and local Delta tables, validate them, and publish them to OneLake through a verified mechanism. A run on this PC starts no Fabric job, but its OneLake reads and writes, including publishing, still use capacity. It takes ideas from DaffyDB (a folder of files served as SQL tables over TDS) without making it a dependency.

Status: audit done (main at 7f51026, 2026-10-17); the first PR carries this plan together with local projects, the engine and file discovery. Everything here is Preview until it has run on a real tenant.

1. Repository audit#

Architecture#

Baseline: python -m pytest -q in sidecar/ on Linux, Python 3.13, with the pinned constraints: 961 passed, 2 skipped before any change. CI also runs the UI syntax tests, a packaged-install import check, pip-audit and the Windows build.

What already exists#

No SQLite engine is used for data (SQLite holds the job queue, workloads and integrations state). No TDS listener, no local file catalog, no persistent local SQL database and no Excel reader exist.

Gaps that matter for safety, found during the audit#

• publish.publish and lakehouse.write_table overwrite with schema_mode="overwrite" and no preview; they rely on callers (the metadata gates, the SQL page's "replace?" question) for confirmation. FD-LDE-009 and 010 add a publish path with a dry run and explicit confirmation instead of reusing these as they are.
• FileStore.upload creates with a plain PUT, which replaces an existing file. FD-LDE-008 needs If-None-Match: * for "don't replace" and a size and MD5 check after the flush.
• delta-rs commits to OneLake rely on the store refusing a second writer of the same _delta_log entry. Whether OneLake honours that conditional write is not verified on a tenant (the same open question as leases.py). Until it is, FD-LDE-009 appends and FD-LDE-011 MERGE take the existing per-table lease and are labelled Preview.

2. Logical services and where they live#

Adapters: discovery turns files into SELECT statements (file_select, delta_select), so the engine can change without changing discovery; dialects go through sqlglot today and the SQL-002 profiles later; cloud publishing goes through the existing Store classes.

3. Implementation matrix#

Owner "this thread" means this program builds it. Other owners are threads already working on that area; this program uses what they build and doesn't edit their branches.

4. PR plan#

Each PR is independent, tested without Fabric, and reported before the next starts.

1. and 2. This plan, with local projects, engine and file discovery (LDE-001, LDE-002), in one pull request. Engine calls only; no UI. Built.
2. Excel workbooks (LDE-002 remainder): add a pinned Excel reader in its own PR (candidates: openpyxl, MIT; or fastexcel, MIT, via Polars), sheets imported into the project database and re-imported when the file changes.
3. T-SQL compatibility (LDE-004) on SQL-002's profiles, once #120 merges.
4. Local Delta (LDE-005): CREATE TABLE ... AS to a local Delta folder through delta-rs, append and overwrite, time travel, protocol checks before every write. Built: CREATE TABLE ... USING DELTA AS, INSERT INTO, INSERT OVERWRITE, VERSION AS OF / AT (VERSION => n), history, dry run and confirmed drop, in the Fabric-safe profile. MERGE, UPDATE and DELETE come with LDE-011.
5. Transformations (LDE-006): SQL scripts with parameters and dependency order, each step's output kept as a table for inspection.
6. OneLake destinations and Files transfer (LDE-007, LDE-008).
7. Delta publish with validation (LDE-009, LDE-010): dry run, preview, confirm, commit, check the committed snapshot.
8. Incremental and MERGE (LDE-011).
9. Local TDS endpoint (LDE-012): needs Chris's go first.
10. Power BI Desktop (LDE-013).
11. Quality, diagnostics and publish history (LDE-016, 017, 018).
12. Large datasets and developer tool interfaces (LDE-019, 020).

UI for the Local Data Explorer, Delta browser, destination picker, publish preview, progress and history comes from a mockup Chris approves first. The likely home is the OneLake page (a "This PC" section beside the OneLake catalog) and the SQL page's target picker, since the rail is full.

5. Decisions and why#

• DuckDB stays the engine. It is already pinned, used by the SQL page and notebooks, spills to disk beyond its memory limit, and reads Parquet, CSV and JSON natively. No second engine.
• Discovered files are views, not imports. DuckDB reads only the columns and row groups a query needs, and a changed file is seen at the next query. Delta tables become views over the files of their current version, read from the Delta log with delta-rs, so the DuckDB delta extension (a runtime download) isn't needed.
• DaffyDB's naming rules (dotted names and folders set the schema, part-file folders are one table, Parquet beats CSV beats JSON) because people coming from it expect them; naming = "flat" and per-file names cover the rest. A name clash is reported, never resolved silently.
• dbo is the default schema, as in Fabric's SQL endpoints: CREATE TABLE t makes dbo.t.
• One connection per project, one request at a time on it. Temporary tables last the session; catalog reads use their own cursor. DuckDB's file lock stops a second process from writing the same database; that is reported in words, not retried.
• Start, poll and cancel calls for long queries, because the engine answers the app one request at a time.
• No DuckDB runtime extension downloads, so the engine works offline and everything it runs comes from the pinned, audited packages.

6. Known limits and open questions#

• Excel files are found and listed but not readable until PR 3.
• Delta tables with deletion vectors, column mapping or other reader features that a Parquet read would get wrong are listed with the reason and not queryable locally yet.
• Partitioned Delta tables are read when their files sit in column=value folders (as Spark and delta-rs write them); others are reported.
• File watching is a cheap check before each query (at most every 2 seconds), not a background watcher; switch it off with auto_refresh = false and refresh explicitly.
• A notebook kernel can't open a project's database while the app has it open for writing; local_close_project releases it. Read-only sharing comes with the notebook integration.
• Whether OneLake honours delta-rs's conditional commit (needed for safe appends and MERGE from two writers) must be checked on a tenant before LDE-009 and 011 leave Preview. That test needs Chris's OK and a non-production workspace.

See local-data.md for how local data projects work.