Quick Answer
Use the Lakehouse for Spark-based data engineering. Use the Warehouse for SQL-based curated gold layers and T-SQL stored procedures. Use the SQL analytics endpoint (auto-provisioned on every Lakehouse) to let T-SQL users query Lakehouse tables read-only. Most enterprise deployments use all three together, each for its strengths.
1. Side-by-Side Comparison
| Feature | Lakehouse | SQL Endpoint | Warehouse |
|---|---|---|---|
| Write support | Yes (Spark) | No | Yes (T-SQL) |
| T-SQL INSERT/UPDATE/DELETE | No | No | Yes |
| Stored procedures | No | No | Yes |
| Spark notebooks | Yes | No | No |
| Unstructured files | Yes | No | No |
| Direct Lake source | Yes | Yes | Yes |
| Cross-item queries | Yes | Yes | Yes |
| SSMS/Azure Data Studio | Yes (read-only) | Yes (read-only) | Yes (read-write) |
2. When to Use Lakehouse
- Team is Python, Spark, or Scala-centric.
- Workload includes ML feature engineering, unstructured file processing, or large-scale transformations.
- You need to ingest raw files (CSV, JSON, Parquet) and transform into structured tables.
- You want a medallion architecture with bronze, silver, gold layers all in one artifact.
- Your team values a notebook-driven iterative development workflow.
3. When to Use Warehouse
- Team is SQL-centric with existing T-SQL stored procedures, views, and triggers.
- You need full ACID write semantics with INSERT, UPDATE, DELETE, and MERGE.
- You want BI tools to connect using standard SQL without Spark knowledge.
- You are migrating from Azure Synapse Dedicated SQL Pool or SQL Server and want to minimize refactoring.
- Gold layer curated data that serves many BI teams and downstream consumers.
4. When to Use SQL Endpoint
The SQL endpoint is automatically provisioned on every Lakehouse. You use it whenever:
- Business analysts want to write SQL queries against data that was engineered in Spark.
- Power BI developers want to connect to Lakehouse tables using standard SQL (import mode or DirectQuery).
- External SQL-centric tools (SSMS, Tableau, dbt) need T-SQL compatibility over Lakehouse content.
- You need read-only cross-database queries joining Lakehouse and Warehouse tables.
You do not choose whether to use the SQL endpoint; it exists automatically. Your choice is whether to use it as the primary query interface (which implies read-only workloads) or to route writes through the Warehouse while using the endpoint for reads.
5. Reference Architecture Patterns
Pattern A: Lakehouse-only
Spark team owns end-to-end transformation. Bronze, silver, and gold layers all live in one Lakehouse. Power BI consumes gold tables through Direct Lake. Good for data-science-first teams and smaller organizations.
Pattern B: Lakehouse + Warehouse
Spark in Lakehouse handles bronze and silver. T-SQL stored procedures in Warehouse build the gold layer. Power BI consumes Warehouse gold tables through Direct Lake. Good for mixed Spark + SQL teams.
Pattern C: Warehouse-only
SQL-first team uses only Warehouse. Data flows through Copy activity, Stored procedures build transformations, Power BI consumes directly. Good for teams migrating from Azure Synapse or SQL Server data marts.
Frequently Asked Questions
What is the difference between Fabric Data Warehouse and Lakehouse?
The Fabric Data Warehouse is a fully managed SQL experience with full read-write T-SQL support, stored procedures, and transactional semantics. The Lakehouse is a Spark-first storage abstraction that also exposes a SQL analytics endpoint for read-only T-SQL queries. Both store data in Delta Parquet format in OneLake. Use Warehouse when your team is SQL-centric and needs INSERT/UPDATE/DELETE with transactional guarantees. Use Lakehouse when your team is Python or Spark-centric and needs notebook-based transformation workflows.
What is the SQL analytics endpoint?
The SQL analytics endpoint is an auto-provisioned T-SQL endpoint that every Lakehouse exposes. It provides read-only T-SQL access to Lakehouse tables without requiring a separate warehouse. It supports SELECT statements, views, and cross-database queries to Warehouses or other Lakehouses. It does not support INSERT, UPDATE, DELETE, or stored procedures because the Lakehouse is intended to be written through Spark or Dataflow Gen2.
Can a Warehouse read from a Lakehouse and vice versa?
Yes. Fabric supports cross-item queries across Warehouses and Lakehouses within the same workspace (or via shortcuts across workspaces). A three-part name references any table in any item: [Workspace].[Lakehouse].[Table] or [Lakehouse].[dbo].[Table]. This enables patterns where bronze and silver data live in a Lakehouse (engineered by Spark) and gold-layer curated data lives in a Warehouse (for BI tooling compatibility). Both sides read each other seamlessly.
Which storage option does Power BI Direct Lake use?
Direct Lake reads from either a Warehouse or a Lakehouse. In both cases, the underlying storage is Delta Parquet in OneLake, and the semantic model binds to the tables at frame time. Performance is nearly identical because both targets store data in the same V-Ordered Parquet format. The choice between Warehouse and Lakehouse for a Direct Lake semantic model is driven by upstream transformation patterns, not by Direct Lake performance.
When should I use Spark vs T-SQL in Fabric?
Use Spark (notebooks, Spark SQL, PySpark, Scala) for large-scale data engineering, machine learning feature engineering, and complex transformations that benefit from Python or R libraries. Use T-SQL (Warehouse or SQL endpoint) for analytical queries, BI report layers, and SQL-developer-centric teams. Most enterprise Fabric deployments use both: Spark for bronze to silver transformations, T-SQL for silver to gold modeling and BI consumption.
Does the SQL endpoint support write operations?
No. The SQL analytics endpoint is read-only. Write operations to Lakehouse tables happen through Spark, Dataflow Gen2, or the Power BI Service (for Direct Lake-capable models that support write-back via specific connectors). If you need read-write SQL, use the Fabric Data Warehouse instead, which supports full T-SQL including INSERT, UPDATE, DELETE, MERGE, and stored procedures.
What T-SQL features are missing from the Fabric Data Warehouse?
Fabric Data Warehouse supports the subset of T-SQL relevant to distributed cloud analytics. Features not supported include temporal tables, cross-database triggers, some CLR functions, identity columns with specific reseed semantics, and certain system stored procedures. For most analytical workloads, the supported subset is complete. Check Microsoft docs for the current T-SQL surface area before migrating complex stored procedure logic.
Should I consolidate my enterprise data platform on Fabric?
Yes, for organizations already in the Microsoft ecosystem. Fabric consolidates warehouse, lakehouse, data factory, real-time, and BI into a single platform with unified security, governance, and billing. The alternative (Snowflake + Databricks + a separate BI tool) requires managing multiple vendors, multiple billing models, and cross-product integrations. For multi-cloud organizations with deep Databricks investment, a hybrid Fabric-for-BI plus Databricks-for-data-science pattern works but requires careful governance design.
Designing Your Fabric Architecture?
Our consultants design Fabric architectures combining Lakehouse, Warehouse, and SQL endpoint for enterprise workloads. Contact us for a design review.