Microsoft Fabric Lakehouse: Architecture, SQL Analytics, and Delta Optimization

What Is the Fabric Lakehouse?#

The Fabric Lakehouse merges data-lake scale storage with warehouse-style querying: structured and unstructured data live in one location, and the same data can be analyzed with both Apache Spark and T-SQL, with no data movement between systems ^[S1]. Every managed table in that storage layer uses the Delta Lake format, which supplies ACID transactions, schema enforcement, and time travel over the underlying Parquet files ^[S1].

The Lakehouse is the engineering-first item in the Fabric platform. It is Spark-native and comfortable with heterogeneous data — raw logs, JSON, CSV, images, and columnar tables can all coexist in the same item. A companion SQL analytics endpoint is provisioned automatically at creation time, making the same Delta tables immediately available for T-SQL queries without any configuration step ^[S1]. Creating a Lakehouse in fact provisions three linked items in one step: the Lakehouse storage itself, a read-only SQL analytics endpoint, and a default Power BI semantic model ^[S14].

For teams already working with SQL Server or Synapse T-SQL, the practical question is often "Lakehouse or Warehouse?" Both persist data as Delta on OneLake and run on the same SQL engine for T-SQL queries, so the choice comes down to tooling and workload: the Lakehouse is Spark-first and handles unstructured data, while the Warehouse is T-SQL-first and supports multi-table ACID transactions, which the Lakehouse does not ^[S1].

Core Concepts#

The Tables and Files Split#

A Lakehouse divides its storage into two top-level areas: a managed Tables area reserved exclusively for Delta tables, and a Files area for any data — unstructured files, raw ingestion stages, non-Delta formats — that does not need to participate in the Delta transaction model ^[S1].

When data lands in the Tables area, Fabric automatically validates the format (Delta only at present), extracts column names, types, compression settings, and partitioning metadata, and registers the table in the metastore — so it is immediately queryable from Spark SQL or T-SQL without a manual CREATE TABLE ^[S1]. The Files area makes no such promise; it is an open filesystem where shortcuts can be placed at any depth without triggering table discovery ^[S4].

According to the Fabric terminology reference, a Lakehouse is a database built over the data lake that holds files, folders, and tables, served by both the Apache Spark engine and the SQL engine, gaining ACID transaction support when its tables use the open Delta format ^[S2].

Structured and unstructured data, queried by both Spark and T-SQL, with zero data movement between engines.

Ingestion Routes#

Data can enter a Lakehouse through several routes: Spark notebooks, pipeline copy activities, Spark job definitions for compiled production ETL, Dataflows Gen2 for low-code preparation, or OneLake shortcuts that reference external data in place without copying it ^[S1]. Because each route ultimately writes Delta files to OneLake, T-SQL consumers see a consistent view regardless of how the data arrived. Inference: whether Direct Lake semantic models and KQL eventhouses also observe the same consistency guarantees depends on their own engine semantics, which the verified claims do not address.

A notebook must have a Lakehouse attached before it can write Delta tables, and any unqualified table name in the notebook's code resolves against whichever Lakehouse is currently attached [S14, tier-6 — treat as a practical convention rather than a hard product guarantee]. Switching the attached Lakehouse partway through a project forces a rewrite of any unqualified table references, since they no longer resolve to the same tables [S14, tier-6].

OneLake Shortcuts in the Tables Folder#

OneLake shortcuts are an important mechanism for federating data without duplication. In the Tables folder, shortcuts are allowed only at the top level (or as schema shortcuts in schema-enabled lakehouses), and when a shortcut target holds Delta Parquet data the Lakehouse automatically syncs the metadata and registers the folder as a table ^[S4]. The Files folder allows shortcuts at any depth with no table discovery ^[S4].

This asymmetry matters: a shortcut pointing at a Delta table in another workspace lands in Tables and becomes immediately queryable through T-SQL. Inference: a shortcut placed in the Files area is likely invisible to the SQL analytics endpoint, because the verified claim establishes only that Files-area shortcuts have no table discovery — the precise endpoint visibility boundary is not independently verified.

Lakehouse vs. Warehouse in Practice#

A proven pattern is to combine both item types in one workspace: land and transform raw data in a Lakehouse with Spark — a medallion architecture is a natural fit — then surface curated datasets through a Warehouse for SQL-centric reporting teams ^[S1]. Every Lakehouse gets an autogenerated SQL analytics endpoint that exposes its Delta tables to T-SQL for read-only querying: you can create views, inline table-valued functions, and procedures and manage object permissions, but you cannot modify the underlying data through that endpoint ^[S5]. Because that endpoint is read-only, multi-table T-SQL DML (INSERT/UPDATE/DELETE spanning several tables in one transaction) is not possible directly against a Lakehouse — the Warehouse item exists precisely to cover that gap [S17, tier-6, consistent with the verified read-only-endpoint claims above].

The medallion architecture (bronze/silver/gold) itself can be implemented either as separate schemas inside one Lakehouse, which suits small-to-medium teams sharing a workspace, or as one Lakehouse per layer, which suits larger teams that need independent ownership, differing security policies, or the ability to share the gold layer across workspaces via shortcuts [S14, tier-6].

Diagnosing Table Health Before You Optimize#

Delta table files can drift into a suboptimal physical layout over time even though the logical data hasn't changed — this gradual drift is described as the underlying cause of Lakehouse queries slowing down without any obvious change in the query itself or the data volume ^[S12]. Because that drift is invisible from the query text alone, Microsoft's guidance is to diagnose a table's physical condition before reaching for compaction, rather than assuming every slow query needs an OPTIMIZE.

Rule: check table health with the stored procedure before scheduling blanket compaction. Microsoft ships a T-SQL stored procedure specifically to surface table health metrics — file counts, sizes, and related physical-layout signals — so that OPTIMIZE can be targeted at tables that actually need it ^[S12].

-- Check table health before deciding whether OPTIMIZE is warranted
EXEC sp_get_table_health_metrics 'gold.sales_summary';

-- Only compact tables the diagnostic actually flags as fragmented,
-- rather than running OPTIMIZE against every table on a timer

This diagnose-first pattern pairs naturally with the compaction mechanics covered later in this article: Auto Compaction and Optimized Write reduce how often a table needs manual attention, but the health check is what tells you which of the remaining tables are worth a scheduled OPTIMIZE run at all ^{[S12] [S10]}.

The SQL Analytics Endpoint#

What It Provides#

Every Lakehouse — and every warehouse, SQL database, and mirrored database in Fabric — automatically provisions exactly one SQL analytics endpoint apiece at creation time with no setup, exposing the item's Delta tables through a read-only T-SQL query surface without any data being moved or copied ^{[S3] [S11]}. The endpoint runs on the same engine as Fabric Data Warehouse, so it inherits the warehouse engine's performance characteristics, T-SQL data types, and limitations ^{[S3] [S11]}.

The endpoint is strictly read-only for data: INSERT, UPDATE, and DELETE are not possible through it, and data modification must happen via Spark in the Lakehouse — yet T-SQL views, inline functions, and stored procedures can still be created and persisted on the endpoint ^{[S3] [S11]}. When a Delta table is created or changed in the Lakehouse, the endpoint detects it and refreshes its own SQL metadata automatically; there is no import step ^[S3].

For Power BI, the endpoint exposes a Tabular Data Stream (TDS) interface that semantic models can connect to directly. Cross-workspace analysis is also possible by creating OneLake shortcuts to Delta tables in other lakehouses or warehouses and joining them in a single T-SQL query ^[S3].

From the lakehouse ribbon, an "Analyze data with" dropdown opens the same data in different engines: the SQL analytics endpoint for T-SQL, an Eventhouse endpoint for KQL real-time analytics, or a new or existing Spark notebook ^[S1].

What the Endpoint Cannot See#

Only Delta tables whose data lives under the Lakehouse /tables area are discoverable by the endpoint; tables referencing data in /files, and external Delta tables created with Spark code outside that path, are invisible to it — the documented workaround is to place a shortcut in the Tables section ^[S3]. This means raw files stored as CSV, JSON, or Parquet under the Files area are not queryable via T-SQL, and are not visible to the default Power BI semantic model either, until they are written or registered as a proper Delta table [S14, tier-6, consistent with the verified Tables/Files boundary].

If a Delta file was written to a table path without going through saveAsTable — for example, a plain .save() call from a Spark job — the underlying Parquet files exist on disk but are unregistered in the catalog and so stay invisible to the SQL endpoint. Calling spark.catalog.createTable() against that same path registers it as a managed table, making it visible to the endpoint without moving any data [S14, tier-6].

# Files exist on disk from a plain .save() call, but the catalog doesn't know about them
df.write.format("delta").mode("overwrite").save("Tables/orphaned_sales")

# Register the existing path as a managed table so the SQL endpoint can see it
spark.catalog.createTable(
    "orphaned_sales",
    path="Tables/orphaned_sales",
    source="delta"
)

Similarly, only Delta tables surface through the SQL analytics endpoint, including Delta tables reached via OneLake shortcuts; Parquet, CSV, and other formats must first be converted to Delta to be queryable there ^[S1].

Security Boundary Limitations#

SQL granular permissions — object-, column-, and row-level security — defined on the SQL analytics endpoint are enforced only for access through that endpoint; the same data reached through Spark or other OneLake paths bypasses those rules entirely, so workspace roles must separately secure the alternate access paths ^[S3].

Inference: this means a defense-in-depth approach is necessary — endpoint-level security is the right layer for T-SQL consumers, but it cannot substitute for workspace-level access controls when Spark or direct OneLake access is possible.

Keeping the Endpoint's Metadata Fresh: Manual Refresh Paths#

Automatic sync is not the only lever available. Beyond the background process, three manual-refresh paths exist for the SQL analytics endpoint: an on-demand Refresh action in the endpoint's portal Explorer toolbar, the Refresh SQL endpoint metadata REST API for programmatic use, and — only on endpoints created after the newer metadata sync architecture is enabled — the sys.sp_dw_refresh_ext_table T-SQL stored procedure, which refreshes a single named table's data rather than re-scanning the whole endpoint's schema ^[S11].

Microsoft's own guidance is to match the refresh mechanism to the problem: use the schema-level refresh (the portal button or the REST API) only when tables or columns were added, removed, or retyped, and prefer the narrower sys.sp_dw_refresh_ext_table procedure for pure data-freshness issues on a single table, since it avoids re-scanning the entire item ^[S11].

-- Narrow refresh: only this table's data is stale, not its schema
-- (available only on endpoints created after the new metadata sync is enabled)
EXEC sys.sp_dw_refresh_ext_table 'gold.sales_summary';

POST https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/sqlEndpoints/{sqlEndpointId}/refreshMetadata
Authorization: Bearer {token}

Rule: reach for the REST API refresh only after a schema change, not as a routine freshness fix. A full schema-level refresh re-scans the whole endpoint, which is unnecessary work when the actual problem is just one table's stale rows after a late-arriving batch load ^[S11].

Delta and Apache Iceberg: Interoperability, Not Competition#

Fabric lakehouses are natively Delta-formatted, not Iceberg, and Delta is described as a natural fit for that reason [S13, tier-6]. But the two open table formats are closer than they might appear: both Iceberg and Delta Lake persist the actual row data as ordinary Parquet files on object storage, and differ only in how they track table metadata and snapshot history on top of that shared Parquet layer [S13, tier-6]. Internally, Iceberg organizes table state as a tree of snapshots pointing to manifest lists and manifest files, coordinated through an external catalog, whereas Delta Lake uses a self-describing, append-only JSON transaction log with periodic Parquet checkpoints and does not require an external catalog to read current state [S13, tier-6].

That metadata-only difference is what makes format-bridging tools possible. Delta's UniForm feature lets a single copy of Delta table data be read as an Iceberg table by external engines — Snowflake, BigQuery, Trino, and Dremio are named examples — without maintaining or paying for a second copy of the data [S13, tier-6].

Pattern: use UniForm when a non-Fabric engine needs Iceberg-native access to Fabric-managed Delta data. Rather than standing up a parallel Iceberg export pipeline, enabling UniForm on the Delta table avoids a second physical copy and the sync burden that comes with keeping two copies consistent [S13, tier-6].

-- Enable UniForm so external Iceberg-reading engines can query this Delta table
-- without a separate Iceberg copy or export pipeline
ALTER TABLE gold.sales_summary SET TBLPROPERTIES (
  'delta.universalFormat.enabledFormats' = 'iceberg'
);

Delta Optimization: How It Works#

V-Order: Write-Time Read Optimization#

V-Order is a write-time optimization for Parquet files that improves downstream read performance across Fabric engines; it pays off most in read-heavy patterns like dashboarding, interactive analytics, and repeated scans ^[S6]. Microsoft states the typical tradeoff as writes taking roughly 15% longer on average, in exchange for reads that can improve significantly depending on the workload ^[S6].

V-Order is disabled by default in all newly created Fabric workspaces (spark.sql.parquet.vorder.default=false), a deliberate default that favors write-heavy ingestion and transformation pipelines over read optimization ^[S6]. It can be controlled at three levels: the Spark session config spark.sql.parquet.vorder.default, the Delta table property delta.parquet.vorder.enabled, and the per-write DataFrame writer option parquet.vorder.enabled ^[S6]. Toggling the table property only affects future writes — files already on disk keep the layout they were written with, so applying or removing V-Order on existing data requires a physical rewrite via table compaction (OPTIMIZE) ^[S6].

Rule: enable V-Order selectively by layer, not globally. For Gold/serving-layer tables that are queried far more often than they are written, enable V-Order at session level or via the ReadHeavy Spark resource profile ^[S6]. For Bronze/Silver ingestion pipelines, keep it disabled — the 15% write overhead is paid on every job run while the read benefit goes unused ^[S6].

# Gold layer notebook: read-heavy, enable V-Order for the session
spark.conf.set("spark.sql.parquet.vorder.default", "true")
gold_df.write.format("delta").mode("overwrite").saveAsTable("gold.sales_summary")

# Bronze/Silver notebook: write-heavy, leave V-Order off (the workspace default)
# no spark.conf.set needed -- spark.sql.parquet.vorder.default is false by default
silver_df.write.format("delta").mode("append").saveAsTable("silver.transactions")

Deletion Vectors: Faster Writes with Merge-on-Read#

Deletion vectors shift Delta Lake mutations from copy-on-write to merge-on-read: rather than rewriting entire Parquet files for any changed record, the system records the positions of affected rows in a small binary file and filters those rows out at query time ^[S8]. For UPDATE operations, deletion vectors soft-delete the old version of changed rows in their existing Parquet file via a new .bin record, then write only the updated row values to a new, smaller Parquet file — avoiding a full rewrite of the source file ^[S8].

Enabling deletion vectors permanently raises a table's minReaderVersion to 3 and minWriterVersion to 7, requiring Delta reader/writer protocol version 2.3 or later; downstream consumers that do not support these versions will be unable to read the table ^[S8].

Rule: pair deletion vectors with scheduled OPTIMIZE, never enable them alone. If deletion vectors accumulate without periodic OPTIMIZE, read performance degrades: a benchmark scenario with 53% non-append changes and no compaction ran 2.3x slower, and a MERGE-only scenario with 5 million new records ran 1.5x slower than the copy-on-write baseline ^[S8]. After OPTIMIZE compacts the accumulated deletion vectors, read performance was identical between deletion-vector and copy-on-write tables in the same benchmark ^[S8].

-- Wrong: enable and forget
ALTER TABLE silver.transactions SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');

-- Right: enable, then schedule maintenance alongside it
ALTER TABLE silver.transactions SET TBLPROPERTIES ('delta.enableDeletionVectors' = 'true');
-- ...and run this on a schedule (e.g. nightly notebook or pipeline activity):
OPTIMIZE silver.transactions;

Table Compaction: Auto Compaction, Optimized Write, and OPTIMIZE#

Small Parquet files accumulate naturally from frequent batch writes, UPDATE/MERGE operations, and streaming micro-batches. Optimized Write is a separately configurable Fabric feature that coalesces small shuffle partitions into larger files at write time, reducing small-file accumulation before data reaches disk. Fragmented writes measurably degrade Parquet compression efficiency; in one community benchmark on a partitioned dataset, the feature-disabled variant produced roughly 33% more data on disk than the Optimized Write variant ^[S9]. When UPDATE or MERGE operations are combined with deletion vectors enabled, the probability of generating small files remains high even with Optimized Write active, because those operations touch many existing files and create new ones for changed records ^[S9].

Auto Compaction in Delta Lake triggers automatically after write operations when the number of small files in a table partition exceeds a configurable threshold, merging undersized files into larger ones without requiring a scheduled job ^[S10]. It is controlled by three tunable properties: maxFileSize (default 128 MB), minFileSize (default unset, calculated as half of maxFileSize at runtime), and minNumFiles (default 50, the count of undersized files that must exist before compaction fires) ^[S10].

Rule: turn on Auto Compaction by default; add scheduled OPTIMIZE once a table exceeds 1 GB. For tables smaller than 1 GB, Auto Compaction alone is sufficient; for larger tables, combine it with periodic ad-hoc OPTIMIZE runs targeting 500 MB–1 GB file sizes ^[S10]. In a benchmark of 200 iterations of 1,000-row batch writes, Auto Compaction + Optimized Write together produced the best outcome of five tested configurations — 12.77 minutes total runtime and a file-count standard deviation of 14 — while running no compaction at all produced the worst result: 33.27 minutes and a standard deviation of 864 ^[S10].

A recommended post-OPTIMIZE partition size range is roughly 256 MB–1 GB per file: partitions under about 128 MB hurt metadata-read performance because of too-many-small-files overhead, while partitions over about 4 GB hurt parallelism; for time-series tables, partitioning by year and month rather than by day avoids accumulating an excessive number of small partitions [S14, tier-6]. A dedicated Lakehouse Maintenance pipeline activity can run VACUUM and OPTIMIZE natively as part of a scheduled pipeline, and frames that same 256 MB–1 GB file size range as a practical target for good Direct Lake query performance; this activity was described as preview-stage at time of writing [S16, tier-6].

-- Enable Auto Compaction and tune its thresholds on a Gold table
ALTER TABLE gold.sales_summary SET TBLPROPERTIES (
  'delta.autoOptimize.autoCompact' = 'true',
  'delta.targetFileSize' = '134217728'
);

Schema Evolution with DataFrameWriterV2#

Spark's DataFrameWriterV2 API changes how overwrites interact with schema. Calling replace() or createOrReplace() under DataFrameWriterV2 automatically overwrites a Delta table's schema and partitioning as a direct consequence of the method choice itself, without requiring the overwriteSchema=true option that the older V1 writer needs to get the same effect ^[S15].

# V1 writer: schema changes need an explicit escape hatch
df.write.format("delta").mode("overwrite") \
    .option("overwriteSchema", "true") \
    .saveAsTable("silver.transactions")

# DataFrameWriterV2: replace() overwrites schema and partitioning
# as a consequence of the method itself -- no extra option needed
df.writeTo("silver.transactions").replace()

What Goes Wrong#

V-Order on write-heavy pipelines. Leaving V-Order enabled on write-dominated ingestion or transformation pipelines is counterproductive: the write penalty is paid on every job while the read-side benefit goes unused ^[S6]. Disable V-Order at session level for such workloads and reserve it for read-heavy access patterns.

No compaction at all. Running no compaction produced the worst benchmarked result by a wide margin — 33.27 minutes total runtime versus 12.77 minutes with Auto Compaction + Optimized Write, and a file-count standard deviation of 864 versus 14 ^[S10].

Deletion vectors without maintenance. Left uncompacted, deletion-vector tables ran 1.5x–2.3x slower than the copy-on-write baseline in benchmarks ^[S8]. Schedule OPTIMIZE regularly whenever deletion vectors are enabled.

Compacting on a timer instead of by condition. Running OPTIMIZE on a fixed schedule regardless of a table's actual state wastes capacity on healthy tables while genuinely fragmented tables can still slip through untouched between runs ^[S12]. Check table health first, then target compaction at what the diagnostic flags.

Unsafe security boundaries. Treating endpoint-level GRANT/DENY as the sole security boundary for Lakehouse data is unsafe, because users with Spark or OneLake access read the underlying Delta files without the SQL rules applying ^[S3].

Non-Delta files not visible through SQL. Only Delta tables surface through the SQL analytics endpoint; Parquet, CSV, and other formats must first be converted to Delta to be queryable there ^[S1]. Teams that land raw data in CSV or JSON and expect immediate T-SQL access will be surprised when those files do not appear.

Oversized workspaces slow down sync. A workspace holding many Lakehouses is a documented cause of increased SQL analytics endpoint sync latency, because one background sync instance serves the whole workspace; Microsoft's stated mitigation is to split Lakehouses across separate workspaces ^[S11].

Declarative Transformations#

Declarative transformation frameworks resolve inter-table dependencies automatically and validate the dependency graph at compile time rather than at runtime, contrasting with procedural notebooks where dependencies are implied by cell execution order and errors only surface during a run ^[S7]. For stable, SQL-expressible business logic in Fabric, the recommended approach is to use native Materialized Lake Views for low operational overhead, or dbt for richer testing, CI/CD integration, and cross-platform portability; neither choice requires migrating the entire pipeline simultaneously ^[S7].

Internals#

Architecture & design#

The Lakehouse's architecture is a two-tier storage split governed by one metastore. The managed Tables area holds only Delta tables and is auto-registered on write; the Files area is an open filesystem for anything else, including shortcuts at any folder depth ^{[S1] [S4]}. The SQL analytics endpoint sits alongside this storage as a second compute surface: it shares the same underlying Delta files but runs on the Fabric Data Warehouse engine rather than Spark, giving it warehouse-grade T-SQL performance characteristics, data types, and limitations without owning a separate copy of the data ^{[S3] [S11]}.

This design is what makes the endpoint's read-only constraint structural rather than a policy choice: the endpoint has no write path of its own into the Delta log, so all mutation has to go through Spark, and the endpoint's job is strictly to observe and expose what Spark (or any other Delta writer) has already committed ^{[S3] [S11]}. That same one-endpoint-per-item design extends beyond the Lakehouse: warehouses, SQL databases, and mirrored databases in Fabric each get exactly one SQL analytics endpoint apiece on the same principle ^[S11].

How it works internally#

The endpoint's background sync process keeps SQL metadata current by reading the Delta transaction logs under each table's /Tables folder in OneLake, rather than by copying or re-scanning the underlying Parquet data itself ^[S11]. Automatic metadata discovery runs as a single background sync instance per Fabric workspace, and that one instance is responsible for scanning every Lakehouse in the workspace for committed changes — the scan work is serialized across Lakehouses rather than parallelized per Lakehouse ^[S11].

Under the legacy (default) sync path, one process handles three responsibilities together: discovering newly created or dropped Delta tables, detecting schema changes (column adds/removes/type changes) in existing tables, and detecting data changes (inserts/updates/deletes) to keep query results current ^[S11]. A newer metadata sync architecture, announced in preview in May 2026, replaces log re-scanning with an external-tables-based approach for parsing Delta logs and building the SQL catalog; it applies only to newly created SQL analytics endpoints in workspaces that opt in, while existing endpoints stay on the legacy sync path ^[S11]. The new design decouples schema-change detection from data-change detection into separate refresh paths — a periodic background refresh for data plus an on-demand refresh triggered when an incoming query finds cached data stale — aimed at making newly landed data queryable within seconds instead of waiting on a shared scan cycle ^[S11]. Sync health under the new path is inspectable through the sys.dm_db_external_tables_log_status DMV, which exposes per-table fields including the last update timestamp, the Delta log version last processed, the latest checkpoint version applied, and whether the last sync attempt was blocked ^[S11].

The new sync path also has documented gaps: it does not support Delta's deprecated multi-part checkpoint feature, so tables using that feature fail to update under it, and it currently cannot be enabled at all on workspaces using workspace private link ^[S11]. On endpoints created after the new sync is enabled, the schema-vs-data distinction also shows up in the manual-refresh surface: the narrow sys.sp_dw_refresh_ext_table stored procedure only exists in that world, refreshing a single table's data without re-scanning the endpoint's whole schema, while the portal Refresh button and the REST API remain the tools for schema-level changes on any endpoint ^[S11].

At the storage-format layer, V-Order reorganizes the physical Parquet layout at write time — adjusting row-group distribution, encoding choices, and compression — rather than changing the logical data, which is why it speeds up reads without altering query semantics ^[S6]. V-Ordered files remain fully compliant with the open-source Parquet specification, so any standard Parquet reader can consume them; non-Fabric engines simply do not get the read acceleration ^[S6]. Deletion vectors work by the same merge-on-read principle described above: metadata is stored as .bin files named with a UUID pattern, and the Delta log entry for the affected Parquet file is annotated with storageType, path, offset, sizeInBytes, and cardinality fields pointing to the corresponding deletion vector file ^[S11].

Delta and Iceberg differ at this same internal layer rather than at the physical-storage layer: Iceberg tracks table state as a tree of snapshots pointing to manifest lists and manifest files and needs an external catalog, while Delta's JSON transaction log with periodic Parquet checkpoints is self-describing and catalog-independent for reading current state [S13, tier-6]. UniForm bridges the two by writing the extra Iceberg metadata alongside the existing Delta log, rather than duplicating the Parquet data itself [S13, tier-6].

Performance characteristics#

Under normal conditions, the lag between a committed Lakehouse change and its visibility in the SQL analytics endpoint is under one minute, ranging from a few seconds to minutes; the background sync process itself only runs while the endpoint is active and stops after 15 minutes of endpoint inactivity ^[S11]. Sync and query latency both degrade as small-file counts grow: because Parquet files are immutable, every update or delete adds new files rather than rewriting existing ones, so unmaintained tables accumulate files that add read overhead to both the metadata scan and subsequent queries — the documented fix is scheduled Delta table maintenance (OPTIMIZE/compaction) ^[S11]. Partition column choice also affects sync latency: a high-cardinality partition column fragments a Delta table into a large number of partitions, which slows the metadata discovery scan; Microsoft recommends lower-cardinality partition columns that yield partitions of roughly 1 GB or larger ^[S11].

On the storage-optimization side, the benchmarked numbers above are directional but consistent: V-Order costs roughly 15% longer write time on average for a variable read speedup ^[S6]; deletion vectors, in one 100-million-row synthetic benchmark, cut single-row delete time by roughly 8x and mass-deletion time by about 2.5x, while remaining 1.5x–2.3x slower on reads if left uncompacted ^[S8]; and Auto Compaction combined with Optimized Write produced the lowest total runtime and file-count variance across five tested write-pattern configurations ^[S10].

Worked Example: A Medallion Lakehouse with SQL Reporting#

Inference: the following scenario assembles verified mechanics into a plausible end-to-end pattern. All individual steps are grounded in verified claims; the scenario framing itself is the author's synthesis.

A team receives daily transaction exports in CSV format from a source system. They want Spark-based engineering work in a Bronze/Silver/Gold medallion structure, with Gold tables available for Power BI reporting over T-SQL.

Storage setup:

1. Create one Lakehouse per medallion layer (Bronze, Silver, Gold) in the same workspace ^[S1]. Raw CSV files land in Bronze's Files area — they will not appear in the SQL analytics endpoint, which is intentional at this stage ^[S3].
2. Spark notebooks read Bronze files, apply type casting and deduplication, and write Delta tables to Silver's Tables area. Enable Auto Compaction on Silver tables from the start ^[S10].
3. A second notebook aggregates Silver into Gold Delta tables. For Gold, enable V-Order at the session level because Gold tables are read far more often than they are written ^[S6].

# Gold aggregation notebook
spark.conf.set("spark.sql.parquet.vorder.default", "true")

gold_df = (
    spark.table("silver.transactions")
    .groupBy("region", "product_category")
    .agg({"amount": "sum", "order_id": "count"})
)
gold_df.write.format("delta").mode("overwrite").saveAsTable("gold.sales_summary")

SQL analytics and security:

4. The Gold Lakehouse's SQL analytics endpoint automatically exposes its Delta tables for T-SQL queries and Power BI connections, with sync typically completing within a minute of the write ^{[S3] [S11]}. Create views and row-level security objects on the endpoint to shape what each reporting team can see ^[S3].
5. Do not rely solely on endpoint GRANT/DENY to protect sensitive columns — also apply workspace permissions, since Spark or direct OneLake access bypasses endpoint-level rules entirely ^[S3].
6. If a late-arriving Gold batch needs its data refreshed on the endpoint without a schema change, use the narrow sys.sp_dw_refresh_ext_table procedure (on endpoints with the new sync enabled) rather than the full schema-level refresh, to avoid re-scanning the whole item ^[S11].

Maintenance:

7. Before scheduling any compaction, run the table-health diagnostic stored procedure against each Gold table to confirm it is actually fragmented rather than compacting on a blind timer ^[S12].
8. On tables the diagnostic flags, run a weekly OPTIMIZE to compact files into the 500 MB–1 GB range and to apply V-Order to any files written before it was enabled ^{[S6] [S10] [S12]}.
9. If the ingestion pipeline uses MERGE for upserts, enable deletion vectors on Silver and Gold tables to avoid full-file rewrites on each upsert cycle, and pair it with scheduled OPTIMIZE to avoid read regression ^[S8]. Confirm that no Fabric COPY Activity reads these tables directly; if it does, run OPTIMIZE immediately before each copy operation ^[S8].

Scale watch-outs:

Keep each medallion layer's Lakehouses reasonably sized per workspace: sync is a single instance per workspace, so a workspace crowded with many Lakehouses slows metadata discovery for all of them — split across workspaces if this becomes a bottleneck ^[S11]. Also watch Microsoft's documented workspace ceiling of 150 combined warehouse and SQL analytics endpoint items ^[S3].