Why Delta is the architectural fulcrum of Fabric#
Every Fabric workload you build — lakehouse, warehouse, Direct Lake semantic model, mirrored database — ultimately reads or writes the same physical format: Delta Parquet files in OneLake. This single-copy design is deliberate. The lakehouse and the warehouse share the same underlying SQL engine and both persist data as Delta on OneLake; the real decision between them is tooling and workload shape, not storage format — lakehouse is Spark-first and handles unstructured data, while warehouse is T-SQL-first and supports multi-table transactions that the lakehouse does not [S6]. Because the data itself is one shared copy, T-SQL, Spark, Analysis Services, and other engines can all operate over the same Delta files without duplicating datasets per engine or locking a team into whichever engine loaded the data first [S9]. As an architect, this reframes your job: you are not choosing a storage format per workload, you are choosing how each engine will read the one Delta copy, and you must design the table so none of those readers break.
Worked example: a lakehouse table serving Spark, T-SQL, and Power BI#
Say you land customer transactions into a lakehouse table via Spark, and three consumers need it: ad hoc Spark exploration, a T-SQL reporting layer, and a Direct Lake Power BI model.
First, the table must live under the lakehouse /tables area to be visible to the SQL analytics endpoint at all — tables that reference data in /files, or external Delta tables created purely with Spark code, are invisible to the endpoint; the documented fix is a shortcut placed in the Tables section [S4]. The endpoint itself is powered by the same compute engine as Fabric Data Warehouse, giving low-latency T-SQL over the open Delta files, but it is strictly read-only over its autogenerated tables — all writes must go through Spark, though you can layer your own views, functions, and stored procedures on top [S4].
Two write-time decisions then determine whether Direct Lake performs well later. Enable Optimized Write and target roughly 1 GB file sizes, because larger files align with how Direct Lake loads segments and reduces the number of segment loads required [S7]. And if you partition, restrict it to low-cardinality columns — Microsoft suggests staying under roughly 100-200 distinct values — because over-partitioning multiplies small Parquet files and row groups, which inflates segment counts in the semantic model [S5]. Auto Compaction helps here too: it triggers automatically after writes once small-file counts in a partition cross a threshold, merging them without a scheduled job [S8].
If you later add a foreign key constraint between two endpoint tables to model a relationship, know that it freezes further schema changes on those tables — new columns arriving via Delta schema evolution stop syncing to the endpoint until the constraint is addressed [S4]. And if you need to join this table with data in another workspace, a OneLake shortcut to a Delta table elsewhere lets you do that join in a single T-SQL query, while the Power BI semantic model connects to the endpoint separately over TDS [S4].
What goes wrong#
- Treating SQL GRANT/DENY as the security boundary. Users with Spark or OneLake access read the underlying Delta files directly, bypassing any SQL-endpoint-level rules entirely [S4].
- Overwriting instead of appending in a Direct Lake-backed table. An Overwrite wipes the table's commit history, defeating incremental framing and forcing a full cold-state reload of every segment, dictionary, and join index on the next query [S5].
- Vacuuming a table pinned by an active framing. A framed Direct Lake model is bound to a specific Delta commit version; vacuuming must not remove that version's Parquet files before the next reframe, or queries referencing those files fail [S5].
- Naming a shortcut with a space. Delta's format doesn't allow spaces in table names, so OneLake won't recognize the shortcut as a Delta table [S1].
- Turning on deletion vectors without checking downstream readers. This permanently raises the table's minReaderVersion to 3 and minWriterVersion to 7 (Delta protocol 2.3+); any consumer that doesn't support those versions can no longer read the table [S10].
Time travel and mirroring: two more architectural levers#
Because every add/remove action in the Delta log is immutable and versioned, OneLake's Delta tables support time travel: you can reconstruct any earlier state by replaying the log up to an older commit, and even correct an accidental overwrite by merging a table against one of its own prior versions instead of restoring from a separate backup [S3]. Direct Lake's own "framing" mechanic builds on this same log: framing is a metadata-only refresh that rebinds the model to the current Parquet file set in seconds, and you can deliberately pin queries to a specific framing point when the underlying Delta data is transient — for example mid-ETL — to guarantee consistent results during a long-running load [S2].
Mirroring extends this same Delta-native design to operational databases: the replicator polls a Fabric landing zone at high frequency and merges incremental Delta files into the target table, with source changes reaching OneLake in as little as 15 seconds under optimal conditions [S11]. Because the mirrored data lands as native Delta, Power BI can connect to it via Direct Lake for near-real-time reporting without importing or duplicating data [S11]. Mirroring also runs vacuum automatically — default retention is one day for databases created after mid-June 2025 (seven days for older ones), adjustable via the portal or REST API to trade storage cost against time-travel range [S11].
The architectural takeaway#
Delta in Fabric is not a per-workload storage choice — it is the shared contract every engine reads. Design decisions you make at write time (file size, partitioning, overwrite vs. append, deletion vectors) ripple into every consumer: Spark, the SQL endpoint, Direct Lake, and any cross-workspace shortcut. Architect the table once, for all its readers, not once per engine.
AI-generated deep dive (beyond the verified knowledge base)#
The section below is AI-generated from model knowledge, not from verified Fabric Codex claims. It is believed factual; verify specifics against current documentation before relying on them.
Table design patterns worth standardizing#
Most multi-engine Delta estates converge on a medallion layout: bronze tables land raw, append-only data; silver tables apply MERGE-based dedup, typing, and conforming; gold tables are the consumption layer that Direct Lake and T-SQL readers hit. The architectural payoff is that expensive mutation patterns (MERGE, deletes, schema churn) stay in bronze/silver where no semantic model is framed against them, while gold stays append-mostly — exactly the write pattern that keeps incremental framing cheap. For slowly changing dimensions, MERGE INTO with a surrogate-key strategy is the standard tool; for propagating changes downstream without full-table diffs, enabling Change Data Feed (delta.enableChangeDataFeed = true) lets consumers read just the row-level changes between two versions instead of re-scanning the table. Note that CDF, like other opt-in features, raises the table's protocol requirements — apply the same downstream-reader check you already apply to deletion vectors.
Partitioning vs. liquid clustering#
Hive-style partitioning bakes a physical directory structure into the table: rigid, visible to every engine, and effective only when the partition column is low-cardinality and evenly distributed. Skewed or high-cardinality keys fragment the table and cannot be changed later without a full rewrite. Liquid clustering (CLUSTER BY) is the newer alternative in the Delta ecosystem: it co-locates related rows without directory boundaries, tolerates skew and higher-cardinality keys, clusters incrementally as part of maintenance rather than at write time, and — critically — lets you change the clustering columns later as query patterns evolve. The trade-off is ecosystem maturity: liquid clustering is a table feature that not every engine and runtime version understands, so before adopting it on a table with T-SQL, Direct Lake, or external readers, verify each consumer's support in current documentation. A reasonable default posture: partition only when you have a natural, low-cardinality pruning column (date is the classic case), consider clustering for everything else, and never partition merely out of habit.
Vacuum and retention as governance, not housekeeping#
Two table properties control how far back a table can travel: delta.logRetentionDuration bounds how long commit history is kept in the log, and delta.deletedFileRetentionDuration bounds how long unreferenced Parquet files survive a VACUUM. Effective time travel is the intersection of the two — a log entry whose data files were vacuumed is unreadable. Delta's runtimes also ship a safety check that refuses vacuum retention windows shorter than a default threshold unless you explicitly disable the guard, because aggressive vacuum can break concurrent readers mid-query, streaming consumers, and any Direct Lake model still framed to an older commit. Treat these settings as a governance decision: document a time-travel SLA per tier (e.g., long enough to rerun yesterday's loads in silver, minimal in bronze), set the properties to match, and schedule vacuum with the same discipline as OPTIMIZE — the storage bill and the recovery window are two sides of the same dial.