SQL endpoint: Warehouse vs Lakehouse
When you start working with Microsoft Fabric, one of the first questions that usually comes up is: “What’s the difference between a SQL endpoint in the Lakehouse and the one in the Warehouse?”
At first glance, they look similar, right? They both use T-SQL, they both connect to Power BI, and they both appear as items within the Fabric workspace, so why do we need both?
Before we get into that, it’s important to understand that in Microsoft Fabric, both the Lakehouse and the Data Warehouse come with their own connection points, known as endpoints. Each with their distinct feature, depending on how you intend to interact with your data.
The three endpoints: The Lakehouse Endpoint works primarily with Spark runtimes and libraries, making it perfect for data engineering workflows, tasks like data transformations, preprocessing, or machine learning. To analyze, transform, or process data stored in your Lakehouse using Spark, you’ll need to connect through the Lakehouse endpoint, which differs from the SQL Analytics endpoint. You can establish this connection via the URL, ABFS path, or by mounting the Lakehouse directly in your explorer. Spark offers full flexibility for read and write operations using languages such as Scala, PySpark, Spark SQL, or R.
On the other hand, the SQL Analytics Endpoint (of the Lakehouse) is designed for T-SQL–based querying and reporting. It gives you that familiar, database-like experience that analysts and BI professionals love.
Finally, the Data Warehouse Endpoint is built for enterprise-scale analytics, optimized for performance, concurrency, and large data workloads.
For the purpose of this article, we’ll focus mainly on the SQL Analytics Endpoint and the Data Warehouse Endpoint, since these two play a central role in querying, data modeling, and integration within Fabric.
We’ll also explore the difference between SQL endpoints in the lakehouse and Warehouse, alongside a demo in my Fabric workspace, so you can see exactly how each one behaves, from how data is stored, to how it’s queried, and how both integrate seamlessly with Power BI.
Before we dive in, let’s understand a few key terms you’ll hear me mention often: OneLake, Lakehouse, and Warehouse.
OneLake: a single, unified, logical data lake for your entire organizations.
Next, the Lakehouse. A data architecture pattern combining data lake and data warehouse capabilities. In simple terms, it lets you store both structured and unstructured data.
Finally, we have the Warehouse, which gives you the traditional data warehouse experience.
For simplicity, I’ll be using the terms “SQL analytics endpoint” and “analytics endpoint” interchangeably. Also, I’ll use “DB” as a shorthand for database where applicable.
Let’s break down exactly what each one does starting with the analytics endpoint.
What is a SQL analytics endpoint?
Analytics endpoint is not a standalone database it’s an interface layer that is automatically created within a Lakehouse, allowing you to query the underlying data using T-SQL.
When you create a Lakehouse, the SQL analytics endpoint appears automatically (as seen above). You don’t provision it separately.
- It’s important to note that the analytics endpoint supports read-only access. This means you can run SELECT queries, create views, and build reports, but operations such as INSERT, UPDATE, DELETE, or MERGE are not supported.
- Also, it reads directly from Delta tables stored in your Lakehouse. Since Delta Lake provides ACID transactions. And the best part? There’s no data duplication, it queries the same Delta files that your Lakehouse uses.
SQL Database
A Warehouse in Microsoft Fabric is a fully managed, analytical SQL database (DB) that provides complete read and write capabilities over structured data stored in OneLake. It is designed for high-performance querying, data transformation, and large-scale analytics using the T-SQL.
Full CRUD Operations (Create, Read, Update, Delete)
The phrase “Full CRUD support” might seem mundane to newbies, but it represents the fundamental difference between a warehouse and read-only analytics interface. It means you can fully manage your data lifecycle from creation and modification to deletion.
Get Mbuotidem Awak’s stories in your inbox
Join Medium for free to get updates from this writer.
Let me explain why each operation is critical from an engineering perspective.
Fabric Warehouse distribution methods :
REPLICATE Distribution: Ideal for small dimension tables (under 2GB). This eliminates data movement during joins and improves query performance.
HASH Distribution: Rows are distributed based on a hash function applied to a specified column. Recommended for large fact tables and dimension tables.
ROUND_ROBIN Distribution: Evenly distributes rows across nodes without considering data values. Primarily used for staging tables during ETL processes where query performance is less critical.Columnstore Indexes: Fabric Warehouse uses clustered columnstore indexes by default. Columnstore compression can achieve 10x data compression and significantly improves analytical query performance through column-based storage and batch mode processing.
Query Operations and Performance: T-SQL syntax for querying data, including window functions, aggregations, and multi-table joins.
Result Set Caching: Fabric automatically caches query results at the database level. Subsequent identical queries return cached results in milliseconds, significantly reducing dashboard load times. The cache invalidates automatically when underlying data changes.
Materialized Views: Pre-computed aggregations that the query optimizer can automatically use to accelerate query performance, even when queries don’t explicitly reference them.
Slowly Changing Dimensions (Type 2): A standard pattern for tracking historical changes in dimension tables. When an attribute changes, the current row is expired (end-dated) and a new row is inserted with the updated values, preserving full history for temporal analysis.
TRUNCATE vs DELETE: TRUNCATE TABLE is a minimally logged operation that deallocates entire data pages, making it substantially faster than DELETE for removing all rows from a table. TRUNCATE resets identity columns and cannot be used with WHERE clauses.
MERGE Statement: A single DML operation that combines INSERT, UPDATE, and DELETE logic based on matching criteria. MERGE operations are commonly used for incremental data loading patterns (upserts) in data warehouses, reducing the complexity of multi-step ETL processes. For example, when loading daily customer updates: where some customers are new (INSERT), others have changed attributes (UPDATE), and some may no longer be active (DELETE or soft delete).
I know this section is dense, infact it’s a lot to digest, but understanding these fundamentals helps you truly grasp what the warehouse is all about. It is not just for data storage, it’s a full-fledged analytical engine designed to handle data processing, transformation, and performance at scale. Now, that we have a clear, theoretical understanding of both.
Let’s DEMO by creating a schema
If I run this SQL schema in the analytics endpoint, it will return an error, and that’s expected. The reason is simple: SQL analytics endpoint doesn’t support Data Definition Language (DDL) operations such as CREATE TABLE or CREATE SCHEMA as explained in this article. So, when you try to execute a schema creation statement here, it fails not because something is wrong with your SQL, but because the analytics endpoint is designed primarily for query execution.
Having clarified that limitation, let’s now execute the same SQL schema in the warehouse as shown below.
Sales table has now been created. If it doesn’t appear immediately in your object explorer, simply refresh the workspace to update the view.
The next step is to make the same DB data accessible from the SQL Analytics Endpoint without duplicating it. To do this, select Get Data > Shortcut, choose your Warehouse as the source, and the data will be linked to your Lakehouse through a shortcut (indicated by the ring icon highlighted in figure 4).
Any changes made in the Warehouse are automatically reflected in the Lakehouse, and by extension, visible through the analytics endpoint. In other words, it doesn’t store a separate copy of the data, it simply reads from the same Delta tables in OneLake. So, when a record is updated in the Warehouse (for example, changing the customer name in the Sales table to Mbuotidem Awak), that update is instantly reflected in the analytics endpoint as well.
Let’s now take a closer look at the key differences between a analytics endpoint and a warehouse, what sets them apart, and when to use each in your Fabric environment.
Connecting to Power BI
You can connect to Power BI from both the analytics endpoint and the Warehouse, depending on your use case. If you’re using the analytics endpoint, Power BI connects directly to the Lakehouse Delta tables through the endpoint’s read-only interface. This setup is ideal for building reports and dashboards.
For the Warehouse, Power BI connects using the Warehouse connection string or dataset connection. This option is best suited for enterprise-grade semantic models, where you need to manage relationships, apply measures, and refresh data at scale.
Did you find this article helpful? Connect with me Twitter or LinkedIn
