Choose Fabric Lakehouse when file-oriented data engineering, Spark and mixed data formats drive the workload. Choose Fabric Warehouse when a T-SQL-led relational serving layer and multi-table transactions drive it. Both can participate in one analytics architecture. Decide which team owns each transformation and which interface writes each dataset before combining them.
Key takeaways
- Lakehouse and Warehouse are different development and management experiences, not a simple choice between modern and legacy storage.
- The Lakehouse SQL analytics endpoint queries Delta tables but does not provide a general T-SQL data-write path.
- Benchmark representative queries, ingestion and concurrent work before making performance claims.
- Test permissions through every permitted access route, not only the report interface.
Two experiences on a shared data platform
Microsoft’s Lakehouse and Warehouse decision guide describes both as open-format analytics stores in OneLake. The useful distinction is how your team develops, transforms and serves data. Lakehouse supports a data-engineering approach with files, Delta tables and Spark. Warehouse provides a relational environment primarily developed through T-SQL.
A Lakehouse automatically has a SQL analytics endpoint. The endpoint documentation describes read-only access over Lakehouse Delta data: SELECT queries and supported SQL objects do not turn it into a general INSERT, UPDATE or DELETE interface. Use a supported Lakehouse write path for data modification. This boundary is often more decisive than a feature checklist.
Fabric Warehouse provides the relational DDL and DML experience and multi-table ACID transactions for warehouse workloads. It suits curated data marts and SQL-centric transformation. It does not mean every existing SQL Server feature or operational application pattern transfers unchanged. Review supported syntax and limitations for your migration.
Fabric Lakehouse vs Warehouse: decision matrix
| Requirement | Lakehouse candidate | Warehouse candidate | Validation question |
|---|---|---|---|
| Primary development style | Spark, notebooks and file-oriented engineering | T-SQL and relational modeling | Who will maintain the transformations? |
| Data shape | Mixed files and structured tables | Curated structured analytics tables | Where are parsing and schema rules owned? |
| SQL data writes | SQL analytics endpoint is read-only over data | Supported warehouse DML | Does the application require SQL write transactions? |
| Multi-table transaction requirement | Do not assume notebook steps form a multi-table transaction | Warehouse supports multi-table ACID transactions | Which business updates must commit together? |
| Business intelligence | Serve curated tables through an appropriate model and interface | Serve relational marts through an appropriate model and interface | Are definitions and refresh responsibilities consistent? |
| Performance | Depends on layout, workload and capacity | Depends on workload and capacity | What does a controlled pilot show? |
Use the matrix to select a candidate, not to skip testing. Team skills matter because operating an unfamiliar transformation stack creates a maintenance cost even when a demonstration works. Consider ownership and recovery alongside initial delivery speed.
A practical decision flow
- Do you need to land and process mixed source files? Start by evaluating Lakehouse for that engineering stage.
- Do you require a T-SQL-led curated model with multi-table commits? Evaluate Warehouse for that stage.
- Do you primarily need SQL reads of already curated Lakehouse tables? Evaluate the SQL analytics endpoint before creating another physical copy.
- Are both patterns necessary? Define the boundary and data contract, then test the combined path.
- Does the candidate meet security, freshness and recovery targets? Approve only after these checks, not after a successful first query.
When combining experiences, avoid uncontrolled dual ownership. If a customer dimension is produced in one layer and consumed in another, define who changes its schema, how corrections propagate and which dataset is authoritative. A shared platform does not eliminate data contracts.
Illustrative architecture examples
Mixed operational files and data-science preparation
A team receives nested event records, exports and occasional attachments. Lakehouse is a reasonable candidate for landing and engineering because the first problem is parsing, normalization and controlled table creation. The workflow is source files → validated transformations → curated Delta tables. The practical output is a documented analytical dataset, with rejected records and source lineage retained according to policy.
Validate late-arriving data, reprocessing and schema changes. Do not assume an arbitrary file in the Files area becomes queryable through the SQL endpoint as a table. Check the supported table registration and access path.
Finance reporting with a SQL-owned relational mart
A SQL team maintains reconciled dimensions and facts, with related changes that must commit together. Warehouse is a candidate for the curated serving layer. The workflow is approved source extracts → validated staging and relational transforms → reconciled reporting tables. The output is a governed mart with an explicit reporting grain and reconciliation checks.
Test the actual SQL used, transaction behavior and correction process. Neither choice independently proves accounting correctness; the transformation rules and review evidence do that.
Engineering and BI teams sharing curated data
A Lakehouse produces curated tables while analysts need SQL access. First evaluate the SQL analytics endpoint and supported cross-item access rather than automatically copying everything into Warehouse. If a separate warehouse adds a meaningful modeling or transactional responsibility, document that benefit and the added freshness and recovery obligations.
These examples are design scenarios, not descriptions of completed client deployments. Their purpose is to make the decision boundary testable.
Governance must follow the access path
Inventory access through workspace roles, item permissions, SQL endpoints, notebooks, shortcuts and reporting models. Define the intended identity and permissions at each boundary. A restriction applied only in a report may not govern access through another authorized interface.
The OneLake security guidance for SQL endpoints distinguishes user-identity and delegated-identity access modes. The mode affects where data access rules are enforced. Review the current feature status and selected configuration, then test with both an allowed and a denied identity. Do not assume SQL policies automatically govern every direct data-access route.
- Record the owner for each source, transformation and published dataset.
- Test revoked access and cross-team boundaries with representative identities.
- Define retention, recovery and deletion responsibilities for copies and shortcuts.
- Review service identities separately from human analyst roles.
Compare workloads with a reproducible pilot
There is no credible universal winner for query speed or cost based only on the item name. Keep data volume, query logic, output correctness and capacity conditions comparable. Record cold and repeated runs separately and include concurrent ingestion or refresh work.
| Measure | Test condition | Acceptance evidence |
|---|---|---|
| Interactive query latency | Representative filters, joins and concurrent users | Latency distribution meets the agreed target |
| Freshness | Normal load and late correction | Published results are complete and timely |
| Capacity consumption | Comparable workload windows | Consumption and throttling are understood |
| Recovery | Failed job followed by retry or replay | No duplication or silent omission |
| Access control | Allowed, denied and revoked identities | Every tested route respects intended access |
Use the Fabric capacity planning guide to separate workload design from sizing. A candidate that passes in isolation may fail when it shares capacity with another busy team.
Common questions
When should I use Fabric Lakehouse instead of Warehouse?
Start with Lakehouse when mixed data and Spark-led engineering are central. Validate the supported write and serving paths against your actual workload.
Can I update Lakehouse tables through its SQL analytics endpoint?
The endpoint provides read-only access to the underlying data. Use a supported Lakehouse write path rather than treating it as a Warehouse DML endpoint.
Can Lakehouse and Warehouse be used together?
Yes, when each has a clear responsibility. Define ownership, freshness, permissions and recovery so combining them does not create unnecessary copies or conflicting data.
Which option is faster?
Test representative queries and concurrent work under comparable conditions. Architecture labels alone do not establish latency or cost for your workload.
Should every gold layer be a Warehouse?
No. A curated layer can be implemented differently depending on transformation, transaction and serving requirements. Choose based on the contract that layer must fulfill.
Choose the ownership model before scaling the platform
Document the data shape, write interface, transaction requirements and reporting contract. Plan your Microsoft Fabric implementation with a measured comparison and a clear boundary between engineering and serving.