Momentum One Academy · article

Lakehouse vs. Warehouse in Microsoft Fabric

The Lakehouse-versus-Warehouse decision is rarely a choice between “modern” and “old.” In a Momentum One production design, it is a choice about the data contract you need to operate: who writes the data, what shape it has, which engine owns transformation, how much transactionality is required, and how business users consume the result.

A team may receive JSON events, CSV extracts, and relational orders in the same week. Data engineers may need Spark to standardize them, while finance expects governed tables, repeatable SQL transformations, and a stable Power BI model. Choosing a store by habit can create an awkward platform: a Lakehouse treated like a writable warehouse, or a Warehouse forced to behave like a raw file landing zone. Both designs can work for a demo. They become expensive when freshness, security, performance, and recovery are tested together.

The practical answer is often not “pick one forever.” Fabric lets a Lakehouse and Warehouse share the same OneLake foundation, so a controlled combination can be more honest than a single-item architecture. 1 2

The shared foundation: OneLake and Delta Lake

OneLake is Fabric’s organization-wide logical data lake. It is provisioned with the Fabric tenant and is the common storage layer for analytics data. Fabric documentation describes OneLake as supporting one copy of data across analytical engines, with shortcuts that can reference data in other workspaces or external locations without copying it. 4 This matters operationally: moving from Lakehouse engineering to Warehouse serving does not automatically mean creating a second physical copy for every consumer.

Delta Lake is the table format that makes this sharing useful. In a Fabric Lakehouse, Delta is the default table format, with transaction logs and Parquet data files behind the table abstraction. Delta brings transactional consistency, schema handling, history, and time-travel capabilities that ordinary CSV or Parquet files do not provide on their own. 5 The open storage layer is therefore shared, but the experience and write contract are not.

KQL belongs in a neighboring decision, not in the Lakehouse-versus-Warehouse write-model test. Fabric can expose an Eventhouse endpoint from a Lakehouse for KQL-based, high-performance real-time analysis. If the requirement is event-stream exploration rather than batch curation or relational serving, evaluate that real-time path explicitly instead of forcing the workload into a Warehouse. 1

This is the first distinction to make with a delivery team: OneLake is the storage foundation; Lakehouse and Warehouse are workload-oriented ways of organizing, writing, securing, and serving data on that foundation. A Delta table in OneLake does not turn a read-only Lakehouse SQL analytics endpoint into a full Warehouse.

What a Fabric Lakehouse is good at

A Fabric Lakehouse is the natural starting point when the source landscape is heterogeneous or when Spark is the primary development tool. It can hold structured tables and unstructured or semi-structured files, and it supports notebooks, Spark jobs, pipelines, Dataflows Gen2, shortcuts, and a SQL analytics endpoint. 1

The familiar production pattern is medallion architecture. Bronze preserves source data, silver applies validation and standardization, and gold presents curated data for reporting or downstream products. Microsoft’s Fabric guidance recommends separating layers and allows the layers to be implemented as Lakehouses, Warehouses, or a combination. It also recommends considering separate workspaces for layer-level control and governance. 9

The Lakehouse has two important surfaces. Spark is the write and transformation surface for notebook-led engineering. The automatically provisioned SQL analytics endpoint is a read-only T-SQL surface over Delta tables. It can query tables, create views and reusable SQL objects, and serve reporting, but data changes must be made through Spark or another supported ingestion path. 10

That boundary is useful rather than inconvenient. It lets a data engineering team expose a stable read interface without allowing an analyst’s query session to mutate the curated data. It also prevents an architectural mistake: planning a production UPDATE or multi-table transaction against the Lakehouse SQL analytics endpoint when the workload requires the full Warehouse write surface.

What a Fabric Warehouse is good at

A Fabric Warehouse is the stronger fit for a structured, SQL-first serving layer. Microsoft positions it for star or snowflake schemas, curated corporate data marts, and governed business-intelligence models. It is developed primarily with T-SQL and supports full data-definition and data-manipulation capabilities, including multi-table ACID transactions, views, functions, and stored procedures. 2

A Warehouse also changes who owns the physical layout. The Warehouse engine manages Delta-backed storage and uses distributed query processing, caching, and automatic maintenance capabilities. It can ingest with T-SQL such as COPY INTO, INSERT, or CREATE TABLE AS SELECT, as well as with pipelines and Dataflows Gen2. 2 3

This makes Warehouse attractive when the team’s main question is, “How do we publish a governed relational model that many SQL users can change and query safely?” It is not the best landing zone for arbitrary raw files or a notebook-centric data-science workflow. Those needs usually belong in a Lakehouse before a Warehouse is considered as a serving layer.

A production decision rule

Use the following questions in order rather than beginning with a product preference:

Is Spark the main development language? Choose a Lakehouse when Python, Scala, Spark SQL, or R notebooks are central. Choose a Warehouse when T-SQL is the primary development and operating language. 7
Do you need multi-table transactions? If a business operation must update several related tables atomically, a Warehouse is the safer fit. The Lakehouse SQL analytics endpoint is read-only, and the decision guide distinguishes the Lakehouse from the Warehouse on this point. 7 10
Are files and semi-structured data first-class inputs? If the answer is yes, begin with a Lakehouse. If the workload is structured data with a stable relational model, a Warehouse may be the more direct path. 7
Where should the serving contract live? A Lakehouse SQL endpoint can serve read-only queries over Delta tables. A Warehouse is more appropriate when the serving layer itself needs T-SQL DDL, DML, procedures, and transactional loading.
What is the real consumer? Power BI can use either path. The choice between Import, DirectQuery, and Direct Lake is a semantic-model decision that depends on the source layout, security, freshness, capacity, and measured report behavior—not simply on the word “Lakehouse.”

For many Momentum One implementations, the outcome is a Lakehouse for bronze and silver, followed by either a Lakehouse gold layer or a Warehouse gold layer. Use the latter when the curated model needs a SQL-first transactional contract. Use the former when keeping Spark and open Delta tables central is more valuable than adding a second serving item.

Implementation pattern: raw in, curated out

Start by writing down the data contract before creating items. Name the source, expected arrival pattern, keys, event-time column, late-data behavior, sensitivity classification, and owner. Decide whether the bronze layer should preserve the original files or create a raw Delta table. If data already lives in OneLake, ADLS, or another supported location, a shortcut may avoid an unnecessary copy; the shortcut still introduces a dependency on the source location and its access path. 4 9

For silver and gold, prefer Delta tables when the data is intended for repeated Fabric consumption. Keep the Lakehouse SQL endpoint in mind while placing data: only supported Delta tables registered in the Lakehouse tables area are exposed there. Files left in the files area are not automatically equivalent to queryable endpoint tables. 10

A small Fabric notebook transformation can look like this:

from pyspark.sql.functions import col, to_timestamp

raw = spark.read.json("Files/bronze/orders/")

silver = (
    raw.select("order_id", "customer_id", "order_ts", "amount", "status")
       .withColumn("order_ts", to_timestamp("order_ts"))
       .filter(col("order_id").isNotNull())
       .dropDuplicates(["order_id"])
)

# Workshop bootstrap: create a managed Delta table in the attached Lakehouse.
silver.write.format("delta").mode("overwrite").saveAsTable("silver_orders")

The code is intentionally small, but production discipline sits around it. The overwrite mode is convenient for a workshop bootstrap or a rebuildable development table; it is not an automatic answer for incremental production loads. In production, define an idempotent ingestion key, handle late arrivals explicitly, record row counts and rejected records, and choose an append or merge pattern that matches the source’s correction behavior. Schedule table maintenance rather than assuming every writer produces an ideal layout.

If the curated contract moves to a Warehouse, load it with an approved T-SQL or orchestration pattern, model dimensions and facts deliberately, and test the full reporting workload. Do not copy tables merely because two Fabric items exist. First ask whether the consumer needs a second physical table or can query the existing Delta data through a supported cross-item pattern.

Direct Lake: the serving choice after storage

Direct Lake is a Power BI semantic-model storage mode, not a replacement for Lakehouse or Warehouse. It loads required columns from Delta tables in OneLake into memory on demand. A Direct Lake refresh is primarily metadata framing rather than a full data copy, so it can reduce the cost and delay associated with repeatedly importing the entire data volume. 6

The detail that matters in production is framing. A framing operation establishes the point in time from which the semantic model sees the Delta table. If automatic updates are disabled, later writes are not visible until the next successful framing operation. With automatic updates enabled, Fabric can frame when source changes are detected, but a long-running preparation process may still justify a deliberate refresh boundary so consumers do not see a partially prepared gold layer. 6 7

Direct Lake on OneLake does not fall back to DirectQuery. Direct Lake on SQL can fall back to DirectQuery when conditions such as SQL-based security, an unmaterialized SQL view, or table guardrails prevent direct loading. Fallback keeps a report functional in some cases, but it can be slower. Treat fallback as something to detect and test, not as a hidden performance plan. The official guidance documents TABLETRAITS() as a way to inspect fallback information. 7

The practical sequence is: optimize the Delta tables, build a small representative semantic model, test cold and warm report interactions, validate security paths, and confirm the model’s refresh or automatic-update behavior. Direct Lake performance depends on the table layout and the capacity environment; a green demo with a small table is not evidence that the production model is ready. 6 8

Trade-offs and common failure modes

Treating the Lakehouse endpoint as a Warehouse. A team creates a Lakehouse, connects a SQL client, and expects arbitrary inserts, updates, deletes, or multi-table transactions. The fix is to write through Spark or use a Warehouse when the relational write contract is essential. 7 10

Leaving data as files and expecting SQL discovery. Raw files are useful in bronze, but a file in the Lakehouse files area is not automatically a table in the SQL analytics endpoint. Convert or write the serving dataset as a registered Delta table, and verify that the endpoint sees it before building reports. 1 10

Small-file accumulation. Frequent micro-batch or poorly sized Spark writes can leave too many small files. That increases metadata work and can degrade Spark, SQL endpoint, Direct Lake, and external-reader performance. Fabric’s maintenance guidance recommends appropriate compaction and layout controls for Lakehouse tables; it also warns that VACUUM removes unreferenced files and should not be shortened casually when time travel or concurrent readers matter. 8

Assuming “one copy” means “no dependency.” A shortcut avoids copying data, but the consumer still depends on the source’s availability, permissions, schema, and physical layout. Document the owner and failure behavior for every shortcut used in a production path. 4

Silent DirectQuery fallback. A Direct Lake on SQL model can appear healthy while some tables are being queried through DirectQuery. Measure the model and inspect fallback reasons. During development, a Direct Lake-only setting can expose issues earlier; in production, choose behavior deliberately so a performance regression is not mistaken for a data outage. 7

Security applied to only one path. SQL endpoint security applies when data is accessed through that endpoint. It does not automatically govern Spark or every other access path. Align workspace, OneLake, endpoint, and semantic-model permissions with the actual routes users and services will take. 4 10

Operational checklist

Before promoting a Lakehouse, Warehouse, or hybrid design, confirm that:

The workload has a named owner and a documented contract for keys, schema changes, late data, and corrections.
The chosen write engine matches the design: Spark for Lakehouse engineering, T-SQL and supported ingestion for Warehouse serving.
Every report-facing dataset is a supported, registered Delta table or an intentionally documented Warehouse object.
Bronze, silver, and gold responsibilities are explicit, with a decision on whether gold is Lakehouse or Warehouse.
Table layout is monitored. Small-file growth, compaction, clustering or partition choices, and retention are part of operations, not a one-time tuning task.
Security has been tested through the same route used by each persona: Spark, SQL endpoint, semantic model, and report.
Direct Lake tests include freshness after a write, framing behavior, cold-column loading, capacity pressure, and fallback detection where applicable.
Recovery is defined: source replay, Delta history or Warehouse recovery options, rejected records, and the consequences of retention cleanup.
A realistic performance test uses production-like row widths, cardinalities, concurrency, and report queries rather than a toy dataset.

The Module 2 workshop connection

In Module 2 — Fabric Lakehouse Approach, make the decision concrete with one small end-to-end build. Land a JSON or CSV order feed in bronze, transform it with a notebook into a Delta silver table, query that table through the Lakehouse SQL analytics endpoint, and expose a curated model to Power BI. Then make one deliberate comparison: keep gold in the Lakehouse, or publish a Warehouse gold model when the exercise needs relational T-SQL writes and multi-table transaction semantics.

The deliverable should be more than a screenshot. Record the chosen store, writer, table locations, refresh boundary, security path, maintenance action, and the reason the alternative was rejected. That decision record becomes a production handoff: it explains not only where the data lives, but why the platform will remain understandable when the next source, report, or correction arrives.

Ready to turn the decision into a tested architecture? Continue with the Momentum One Academy Fabric Production Workshop and build the Lakehouse-to-serving path with production checks included.

References

Plan your workshop