Medallion Lakehouse on Microsoft Fabric — bronze, silver, gold over OneLake

Goal#

Stand up the medallion pattern — progressively refining raw data through bronze, silver, and gold zones — on a single Fabric lakehouse, so that data lands once in OneLake and every stage and consumer reads the same physical copy rather than shuttling data between systems. This design covers zone responsibilities, format, the ingestion-engine choice per workload, Spark transformation and its file-layout knobs, serving paths, and the governance and anti-pattern traps to avoid.

A proven pattern in Fabric is to land and transform raw data in a lakehouse with Spark as a medallion architecture, then surface curated datasets through a warehouse for SQL-centric reporting teams ^[S2].

Architecture#

1. One lake, one copy: OneLake as the substrate#

Every Fabric tenant is provisioned with exactly one OneLake, and no separate storage resources need to be provisioned or managed ^[S1]. Each Fabric tenant gets a single file-system namespace: the tenant maps to the root of the lake and workspaces act like folders beneath it, with lakehouses and other items inside workspaces ^[S3]. All Fabric data items, including lakehouses, automatically persist their tabular data in OneLake using the open Delta Parquet format, regardless of which engine wrote the data ^[S1]. That one-copy design lets T-SQL, Apache Spark, and Analysis Services all operate over the same physical Delta Parquet data, so teams do not duplicate datasets per engine ^[S1].

Inference: This is exactly why medallion works cleanly in Fabric — the three zones are not three storage systems, they are three sets of Delta tables in one OneLake, so promoting data from bronze to silver to gold is a transform-and-write within the same lake, not a cross-system copy.

2. Zone responsibilities#

The medallion pattern is a curation ladder. The KB does not carry a Microsoft claim that names the three tiers, so the tier definitions below are labelled inference, but the mechanics of each zone are grounded:

• Bronze — raw landing. Data enters 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 ^[S2]. A lakehouse splits storage into a managed Tables area reserved for Delta tables and a Files area for unstructured or non-Delta data ^[S2]. Inference: bronze typically holds either raw files in the Files area or lightly-typed Delta tables that mirror the source faithfully, prioritising completeness and replayability over cleanliness.

• Silver — cleansed and conformed. Inference: silver is where Spark applies de-duplication, type enforcement, and joins to produce validated, query-ready Delta tables. All managed lakehouse tables use the Delta Lake format, which supplies ACID transactions, schema enforcement, and time travel over the underlying Parquet files ^[S2] — the properties that make repeatable, correctable transformations safe.

• Gold — business-ready serving. Inference: gold holds curated, aggregated, star-schema-shaped tables optimised for consumption. Because only Delta tables under the lakehouse /tables area surface through the SQL analytics endpoint ^[S7], gold tables must be managed Delta tables in the Tables area to be queryable in T-SQL and by Direct Lake.

3. Single format: Delta Parquet everywhere#

Use one table format across all three zones: Delta Lake over Parquet. When a Delta table lands in the managed Tables area, Fabric automatically validates the format (Delta only at present), extracts column names, types, compression, 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 ^[S2]. Parquet, CSV, and other formats must first be converted to Delta to be queryable through the SQL analytics endpoint ^[S2].

Internally, a OneLake Delta table stores data as immutable Parquet objects plus a write-ahead transaction log in a _delta_log subfolder; the log — not a directory listing — is the single source of truth for which Parquet objects currently belong to the table, which is what gives ADLS Gen2-style object storage ACID table semantics ^[S16]. Because every add/remove action in the log is immutable and versioned, the table supports time travel: a client can reconstruct any earlier state by replaying the log up to an older commit, which also lets accidental overwrites be corrected without a separate backup ^[S16].

Data flow#

1. Ingest to bronze. A source is landed via the engine that fits the workload (see the decision section). Raw fidelity is preserved.
2. Transform bronze -> silver with Spark. Cleansing, typing, de-duplication, and conforming run as PySpark/SQL, writing new Delta tables. Delta ACID and schema enforcement make these writes safe and replayable ^[S2].
3. Aggregate silver -> gold with Spark. Business logic and star-schema modelling produce curated Delta tables in the Tables area.
4. Serve gold. The SQL analytics endpoint exposes gold tables to read-only T-SQL ^[S7], and Direct Lake semantic models read the same Delta tables straight from OneLake for BI ^[S17].

Inference: every arrow above is a Spark (or engine) write of a new Delta table, never a bulk copy between stores — the copy avoidance is the whole point.

Choosing the ingestion / transform engine#

Fabric Data Factory deliberately supports both ETL and ELT in the same solution: shape data in Dataflow Gen2 before loading when quality and standardisation matter up front, or land raw data first and push transformation down to Spark notebooks or SQL engines over OneLake for large cloud-scale datasets ^[S11]. A practical division of labour: use pipelines when the problem is orchestration and large-scale movement, use Dataflow Gen2 when the problem is low-code data shaping ^[S11].

• Data Factory pipeline Copy activity — Microsoft's recommended mechanism for large-scale one-time migrations into OneLake and for high-volume recurring ingestion, on the grounds that the engine is serverless, performant at scale, and cost-effective ^[S12]. It exposes parallelism at three levels: many concurrent copy activities (for example inside a ForEach loop), per-activity intelligent throughput optimisation, and multi-threaded read/write within a single activity ^[S12]. Best for bronze landing of bulk and recurring loads.
• Dataflow Gen2 — Fabric's low-code data-preparation item built on Power Query, offering more than 300 transformations over hundreds of connectors ^[S13], and Microsoft's recommended tool when data must be shaped before it is loaded (classic ETL) ^[S11]. It can write transformed output to Lakehouse tables and files among other destinations ^[S13]. Best for business-analyst-owned cleansing where code is undesirable.
• Spark notebooks / Spark job definitions — the code-first, large-scale transform path once raw data has landed (the ELT push-down pattern) ^[S11], and one of the documented lakehouse ingestion routes with Spark job definitions for compiled production ETL ^[S2]. Best for silver and gold transformations and any complex logic.

Inference: a common shape is Copy activity (or shortcuts) into bronze, then Spark for bronze->silver->gold, with Dataflow Gen2 reserved for source-specific low-code shaping — and a pipeline orchestrating the chain.

Shortcuts as a zero-copy alternative to ingestion#

Where data already lives in another store, a shortcut can replace a copy. A OneLake shortcut points from a shortcut path to a target path in another location, behaving like a symbolic link, so deleting the shortcut leaves the target untouched ^[S9]. OneLake shortcuts mount existing external storage such as ADLS, Amazon S3, and Google Cloud Storage as zero-copy references, so cross-cloud data can be analysed in Fabric without ETL ^[S1]. In a lakehouse's Tables folder, shortcuts are allowed only at the top level (or as schema shortcuts), and when a shortcut target holds Delta Parquet data the lakehouse automatically syncs the metadata and registers the folder as a table ^[S9]. Inference: a shortcut is often the right "bronze" for data that is already governed elsewhere — you reference it in place rather than re-ingesting it.

Transformation with Spark: layout is performance#

Fabric runs Data Engineering workloads on a managed Spark platform with starter pools (preconfigured for near-instant startup) and custom pools admins tune for node size and scaling ^[S14]. Starter-pool sessions typically begin in 5–10 seconds because Fabric keeps Medium-node clusters pre-provisioned; any other node size switches to on-demand provisioning taking roughly 2–5 minutes ^[S14]. Spark billing covers only the period a session is actively running — idle pre-warmed cluster time and acquisition are unbilled ^[S14].

Three file-layout controls decide whether a medallion pipeline stays healthy:

• Optimized Write addresses the small-file problem by shuffling data across executors before writing, so each partition is handled by a single executor and produces fewer, larger files ^[S15]. Its target file size is governed by the BinSize key; in Fabric the default is 1 GB, with recommended tuning values of 256 MB for general workloads or 128 MB for small workloads to avoid over-consolidation ^[S5]. For streaming micro-batches, which naturally generate many small files, Optimized Write is recommended ^[S5]. Caution: it is not universally beneficial — on a non-partitioned table in one benchmark, disabling it produced 35% faster writes and 2x faster queries because more output files gave better read parallelism ^[S5].
• Compaction (OPTIMIZE / Auto Compaction). Auto Compaction triggers automatically after writes when the small-file count in a partition exceeds a threshold, merging undersized files without a scheduled job ^[S6]. In a benchmark, combining Auto Compaction with Optimized Write achieved the best outcome across all tested configurations — lowest runtime and lowest file-count variance — while running no compaction at all produced the worst result by a wide margin (33.27 minutes versus 12.77) ^[S6]. For tables under 1 GB, Auto Compaction alone suffices; for larger tables, combine it with periodic OPTIMIZE targeting 500 MB–1 GB files ^[S6].
• V-Order is a write-time optimisation for Parquet that improves downstream read performance across Fabric engines, paying off most in read-heavy patterns like dashboarding and repeated scans ^[S4]. It is disabled by default in newly created workspaces, a deliberate choice favouring write-heavy ingestion and transformation over read optimisation ^[S4]. The typical tradeoff Microsoft states is writes roughly 15% longer in exchange for reads that can improve significantly ^[S4]. Guidance: leaving V-Order on for write-dominated ingestion is counterproductive — the write penalty is paid every job while the read benefit goes unused ^[S4]. Inference: the natural fit in a medallion pipeline is V-Order off in bronze/silver (write-heavy churn) and V-Order on in gold (read-heavy serving), enabled per-session or via a read-heavy Spark resource profile ^[S4].

Serving: SQL analytics endpoint and Direct Lake#

Every lakehouse automatically provisions a SQL analytics endpoint at creation with no setup, exposing the lakehouse's Delta tables through a read-only T-SQL surface ^[S7]. It runs on the same engine as Fabric Data Warehouse, so it inherits that engine's performance characteristics and T-SQL limitations ^[S7]. It is strictly read-only — INSERT/UPDATE/DELETE are not possible through it and data modification must happen via Spark ^[S7] — but T-SQL views, inline functions, and stored procedures can be created and persisted on it ^[S7]. Only Delta tables under /tables are discoverable; tables referencing /files or external Delta tables created in Spark code are invisible, with a shortcut in the Tables section as the documented workaround ^[S7].

The endpoint's background sync keeps its SQL metadata current by reading the Delta transaction logs under each table's /Tables folder, not by copying or re-scanning Parquet data ^[S8]. Under normal conditions the lag between a committed change and its visibility is under a minute ^[S8].

For BI, Direct Lake mode in the Analysis Services engine lets Power BI semantic models read Delta Parquet data straight from OneLake, delivering import-mode speed without maintaining a copied dataset ^[S1]. Direct Lake loads column data from OneLake lazily — nothing is read until a query first touches a column, and the load set includes columns needed by relationships and measures ^[S17]. A 1 GB BinSize target is recommended for Delta tables that back Direct Lake models, because larger files align with the loading pattern and reduce segment loads ^[S5]. Inference: gold is the layer you point Direct Lake at, which is another reason to enable V-Order and target ~1 GB files there.

Governance & security#

Governance in OneLake is hierarchical: tenant-level security, compliance, and data-management policies automatically cover any data landing in OneLake, while workspaces distribute ownership and access ^[S1]. OneLake security operates on two planes: control-plane permissions decide what users can do, while data-plane (OneLake security model) permissions decide what data users can read — and control-plane grants often confer data access by default ^[S10].

A critical trap: SQL granular permissions (object-, column-, row-level) defined on the SQL analytics endpoint are enforced only for access through that endpoint; the same data reached through Spark or OneLake bypasses those rules ^[S7]. Treating endpoint-level GRANT/DENY as the sole security boundary is unsafe ^[S7]. Likewise, relying on OneLake security roles to restrict workspace Admins, Members, or Contributors is an anti-pattern — those roles bypass OneLake security entirely, so granular roles only constrain Viewers and users with item Read permission ^[S10]. Inference: in a medallion design, secure the write path (Spark/workspace roles on bronze/silver) separately from the read path (endpoint or OneLake security on gold) — do not assume one covers the other.

Performance#

See the Internals section for depth. In brief: keep file counts under control (Optimized Write + compaction) because sync and query latency both degrade as small-file counts grow ^[S8]; keep partition columns low-cardinality (roughly 1 GB or larger per partition) since a high-cardinality partition column fragments a table and slows metadata discovery ^[S8]; and split many lakehouses across separate workspaces, because automatic metadata discovery runs as a single background sync instance per workspace, serialised across all its lakehouses ^[S8].

Cost & capacity#

Spark cost is scoped to active session time only — idle pre-warmed cluster time, acquisition, and deallocation are unbilled for both starter and custom pools ^[S14]. Inference: prefer starter pools for interactive medallion development (5–10 s startup) and reserve custom pools for tuned production jobs, since custom sizes add 2–5 minutes of on-demand provisioning ^[S14]. The native execution engine (NEE) is included at no additional cost over standard Spark capacity billing and reported roughly 4x faster query execution on a 1 TB TPC-DS workload versus vanilla Spark ^[S15], so enabling it lowers the CU cost of a given transform without a separate line item.

Risks & anti-patterns#

• Gold-in-name-only with no maintenance. Inference: labelling a zone "gold" does not make it fast. 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 metadata scans and queries — the documented fix is scheduled Delta maintenance (OPTIMIZE/compaction) ^[S8]. A gold layer without compaction degrades exactly where it is meant to be fastest.
• V-Order left on for write-heavy zones. Paying the ~15% write penalty on bronze/silver ingestion for a read benefit you never use is counterproductive ^[S4].
• Over-partitioning. A high-cardinality partition column fragments a table into many partitions and multiplies small files, slowing metadata discovery ^[S8]; prefer lower-cardinality columns yielding ~1 GB partitions ^[S8].
• Endpoint permissions as the only boundary. Users with Spark or OneLake access read the underlying Delta files without SQL rules applying ^[S7].
• Per-group physical lakes. Standing up separate physical data lakes per business group is an anti-pattern in Fabric because it recreates the duplication that the single tenant-wide OneLake with workspaces and shortcuts is designed to eliminate ^[S1].
• Deletion vectors + Copy activity. If a downstream consumer uses Fabric Copy Activity, note deletion vectors are unsupported by that connector and deleted/updated rows can reappear unless an OPTIMIZE runs immediately before each copy ^[S18].

Assumptions#

• The team has Fabric capacity available and works within one tenant's single OneLake ^[S1].
• Consumers are a mix of T-SQL/BI users served from gold; if multi-table T-SQL transactions are required, a warehouse alongside the lakehouse is the grounded alternative below.
• "Bronze/silver/gold" tier semantics are the team's convention; the KB grounds the mechanics, not the tier names.

Alternatives#

• Lakehouse-only (this design). Spark-first curation, served via the auto-provisioned SQL endpoint and Direct Lake. Best when Spark and unstructured data are in scope.
• Lakehouse + Warehouse coexistence. Lakehouse and warehouse share the same SQL engine and both persist Delta on OneLake; the practical decision points are tooling and workload — lakehouse is Spark-first and handles unstructured data, while the warehouse is T-SQL-first and supports the multi-table transactions the lakehouse does not ^[S2]. The proven pattern is to transform in the lakehouse (medallion) and surface curated gold through a warehouse for SQL-centric reporting teams ^[S2]. Choose this when reporting teams need full T-SQL DML/transactions on the served layer.

Open questions#

• Does the team need row/column-level security on gold enforced across both the SQL and OneLake read paths? If so, model it in OneLake security roles, not endpoint GRANTs alone ^{[S7] [S10]}.
• Is any source a candidate for a shortcut rather than ingestion, avoiding a bronze copy entirely ^{[S1] [S9]}?

Source legend#

Internals#

Architecture & design#

The medallion pattern on Fabric is, physically, layered Delta tables in a single OneLake namespace. A OneLake Delta table stores data as immutable Parquet objects plus a write-ahead transaction log in a _delta_log subfolder; the log is the single source of truth for which Parquet objects belong to the table, which is what gives object storage ACID table semantics ^[S16]. Each commit is a zero-padded, sequentially numbered JSON file holding add/remove actions (which Parquet objects entered or left), plus metaData, protocol, and commitInfo records ^[S16]. Concurrency is achieved through optimistic concurrency rather than locking: a writer stages new Parquet objects independently, then atomically creates the single next-numbered log file; only one writer can win that commit number, so a loser detects the collision, rereads state, and retries ^[S16]. On ADLS Gen2 (which OneLake is built on) that atomicity is implemented via an atomic rename that fails if the target name already exists ^[S16]. Design consequence (inference): because each zone-to-zone promotion is an ordinary Delta commit, bronze->silver->gold pipelines get ACID isolation and time-travel-based recovery for free, without any orchestration-level transaction coordinator.

How it works internally#

Reads reconstruct a snapshot by pinning to the newest fully-committed log record at query start, so concurrent writers never produce a dirty read ^[S16]. To keep metadata reads fast as history grows, the Delta engine periodically compacts the log into a Parquet checkpoint (every 10 commits by default) that folds in still-relevant actions and drops superseded ones, so readers reconstruct current state from the latest checkpoint plus a few trailing JSON commits rather than replaying from commit zero ^[S16]. Writers record per-column min/max statistics (and null counts) for each Parquet object directly in its add action, enabling data skipping: engines consult the log's statistics to exclude whole objects whose value range cannot satisfy a filter, before issuing any I/O ^[S16]. The effectiveness of skipping depends on physical layout — sorting/partitioning by one column helps predicates on that column but collapses toward the whole-table range for others, which is the motivation for Z-ordering across multiple columns ^[S16].

On the compute side, Fabric's native execution engine (NEE) is built on Velox (Meta's C++ vectorised execution library) and Apache Gluten (Intel's JVM-to-native offload layer); Gluten intercepts the optimised Spark physical plan, converts supported portions to a Substrait plan, and Velox executes them as vectorised, SIMD-accelerated columnar C++ ^[S15]. When NEE hits an unsupported operator or type it falls back to JVM Spark for that segment rather than failing the job ^[S15], and it preserves the existing optimiser behaviours — adaptive query execution, cost-based rewrites, column pruning, and predicate pushdown ^[S15]. The SQL analytics endpoint's own metadata sync reads the Delta transaction logs under each table's /Tables folder rather than re-scanning Parquet, keeping T-SQL metadata current within a minute of a commit under normal conditions ^[S8].

Performance characteristics#

The dominant performance lever in a medallion pipeline is file layout, and the KB carries controlled benchmarks. Combining Auto Compaction with Optimized Write achieved the best outcome across all tested configurations — lowest total runtime (12.77 minutes) and lowest file-count variance — while no compaction at all produced 33.27 minutes and a file-count standard deviation of 864 ^[S6]. On a highly partitioned table (1,823 partitions), enabling Optimized Write cut write time from ~6m43s to 53s and file count from 175,008 to 1,823, a 19x improvement in downstream query duration ^[S5] — though on a non-partitioned table, disabling it was 35% faster to write and 2x faster to query because more files gave better read parallelism ^[S5]. V-Order reorganises the physical Parquet layout at write time (row-group distribution, encoding, compression) without altering query semantics, which is why it speeds reads without changing results ^[S4], at a typical ~15% write cost ^[S4]. At OneLake scale, going through the transaction log/checkpoint rather than listing Parquet footers keeps table discovery roughly two orders of magnitude faster at large partition counts — a benchmark table with 1,000,000 partitions took over an hour for non-Delta engines but 108 seconds for the Delta-backed engine (17 seconds with the log cached on SSD) ^[S16]. On compute, NEE reported roughly 4x faster query execution on a 1 TB TPC-DS workload versus vanilla Spark and up to 6x end-to-end on representative jobs, at no additional cost over standard Spark billing ^[S15].