Querying Lakehouse Data with the SQL Analytics Endpoint: Writing T-SQL Against Delta Tables Without a Warehouse

Introduction

You've loaded data into your Fabric lakehouse. Your Delta tables are sitting there in OneLake, neatly organized, full of transformed sales records or customer data or whatever your pipeline has been churning through. Now a colleague taps you on the shoulder: "Can you just write me a quick SQL query against that data?" They don't care about Spark. They don't want to spin up a notebook. They want a SELECT statement.

Here's the thing: you don't need a Fabric Data Warehouse to run T-SQL against lakehouse tables. Every lakehouse in Microsoft Fabric comes with something called a SQL Analytics Endpoint — a read-only SQL interface that is automatically generated the moment Delta tables land in your lakehouse. You can connect to it with SQL Server Management Studio, use it to power Power BI reports, or just run ad-hoc queries directly in the Fabric portal. No extra setup, no additional cost, no separate service to provision.

By the end of this lesson, you'll understand how the SQL Analytics Endpoint works under the hood, how to navigate to it in the Fabric UI, and how to write real T-SQL queries — including filters, aggregations, joins across multiple tables, and views — against your Delta Lake data. You'll also know where its boundaries are, so you don't waste time trying to INSERT data through an interface that's deliberately read-only.

What you'll learn:

• What the SQL Analytics Endpoint is and how it differs from a Fabric Data Warehouse
• How to switch to the SQL Analytics Endpoint view inside a lakehouse
• How to write T-SQL SELECT queries, including WHERE clauses, GROUP BY aggregations, and window functions
• How to create views in the endpoint to encapsulate reusable logic
• How to join tables across the same lakehouse, and when cross-lakehouse joins make sense
• The key limitations of the endpoint and how to work around them

---

Prerequisites

This lesson assumes you've already created a Fabric workspace with at least one lakehouse that contains Delta tables. If you're starting from scratch, work through Building Your First Lakehouse in Microsoft Fabric: Files, Tables, and the SQL Analytics Endpoint first.

You should also have a basic understanding of SQL syntax — SELECT, WHERE, GROUP BY, and JOIN. If you've written SQL in any context before (Azure SQL, Postgres, MySQL), you'll feel right at home. If not, you'll still follow along, because we'll explain every clause as we go.

---

What Is the SQL Analytics Endpoint, Exactly?

To understand the SQL Analytics Endpoint, it helps to understand what Delta tables actually are. When Spark writes data to a lakehouse table — whether you loaded it with a pipeline, a Dataflow Gen2, or a PySpark notebook — it stores that data as Parquet files in OneLake. Alongside those Parquet files sits a transaction log (a folder called _delta_log) that tracks every change: inserts, updates, deletes, schema changes. This combination — Parquet data plus a transaction log — is what makes it a Delta table.

The SQL Analytics Endpoint is a service that reads those same Delta tables and exposes them through the Tabular Data Stream (TDS) protocol — the same protocol SQL Server uses. This means any SQL client that knows how to talk to SQL Server can connect to your lakehouse data without knowing anything about Spark, Parquet, or Delta Lake internals.

Here's why this matters architecturally: the endpoint doesn't copy your data or store a separate representation of it. It reads the Delta files in-place from OneLake. When you run a T-SQL query, the endpoint's distributed query engine translates that SQL into reads against the underlying Parquet files, applies any filtering it can at the file level (a technique called predicate pushdown), and returns results. The data lives in one place. The SQL interface is just a lens over it.

This is meaningfully different from a Fabric Data Warehouse, which manages its own separate storage for tables you define inside it. If you want a deeper comparison of when to use each, the article on Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload walks through the decision in detail.

---

Navigating to the SQL Analytics Endpoint

When you open a lakehouse in the Fabric portal, you land on the Lakehouse view by default. You can see your Files section, your Tables section, and a ribbon of actions at the top. To switch to the SQL Analytics Endpoint, look at the dropdown in the upper-right area of the screen — it will say "Lakehouse." Click it, and you'll see a second option: SQL analytics endpoint. Select it.

The view transforms. You're now looking at something that resembles SQL Server Management Studio or Azure Data Studio. On the left you have an Explorer pane listing your schemas and tables. In the center is a query editor where you can type T-SQL. At the top, there's a toolbar with a Run button (or you can press F5).

You'll notice that your lakehouse tables appear under the dbo schema by default. If you created tables inside named schemas from Spark — for example, by writing to a schema called sales — those will show up under their respective schemas here too.

---

Writing Your First T-SQL Queries

Let's use a realistic scenario throughout this lesson. Imagine you've built a retail data lakehouse with the following tables:

• sales.orders — one row per order, with columns: order_id, customer_id, order_date, region, total_amount
• sales.order_items — one row per line item, with columns: item_id, order_id, product_id, quantity, unit_price
• dim.customers — customer dimension with: customer_id, customer_name, customer_segment, country
• dim.products — product dimension with: product_id, product_name, category, subcategory

Simple SELECT with Filtering

Start simple. Let's see the orders from the last 30 days in the Northeast region:

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM sales.orders
WHERE region = 'Northeast'
  AND order_date >= DATEADD(DAY, -30, CAST(GETDATE() AS DATE))
ORDER BY order_date DESC;

This looks identical to what you'd write in Azure SQL Database, and that's the point. The T-SQL dialect supported by the SQL Analytics Endpoint is compatible with SQL Server 2022 syntax for read operations, including date functions like DATEADD, DATEDIFF, EOMONTH, and DATEFROMPARTS.

Aggregations with GROUP BY

Now let's build something more useful — total revenue by region, broken down by month:

SELECT
    region,
    YEAR(order_date)    AS order_year,
    MONTH(order_date)   AS order_month,
    COUNT(*)            AS order_count,
    SUM(total_amount)   AS total_revenue,
    AVG(total_amount)   AS avg_order_value
FROM sales.orders
WHERE order_date >= '2024-01-01'
GROUP BY
    region,
    YEAR(order_date),
    MONTH(order_date)
ORDER BY
    order_year,
    order_month,
    region;

Joining Across Tables

Here's where the real power shows up. You can join any tables within the same lakehouse freely, even if they live in different schemas:

SELECT
    c.customer_name,
    c.customer_segment,
    c.country,
    COUNT(DISTINCT o.order_id)  AS order_count,
    SUM(o.total_amount)         AS lifetime_value
FROM sales.orders AS o
INNER JOIN dim.customers AS c
    ON o.customer_id = c.customer_id
WHERE c.country = 'United States'
GROUP BY
    c.customer_name,
    c.customer_segment,
    c.country
ORDER BY lifetime_value DESC;

Now let's go deeper and include the line items to calculate revenue at the product level:

SELECT
    p.category,
    p.subcategory,
    p.product_name,
    SUM(oi.quantity)                        AS units_sold,
    SUM(oi.quantity * oi.unit_price)        AS gross_revenue,
    COUNT(DISTINCT o.order_id)              AS orders_containing_product
FROM sales.order_items AS oi
INNER JOIN sales.orders AS o
    ON oi.order_id = o.order_id
INNER JOIN dim.products AS p
    ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
    p.category,
    p.subcategory,
    p.product_name
ORDER BY gross_revenue DESC;

This is a three-table join running entirely against Delta files in OneLake. No data movement, no copying into a warehouse. The query optimizer handles it.

---

Window Functions: Analytics Without Subqueries

Window functions are where T-SQL really earns its keep for analytical work. They let you compute running totals, rankings, and moving averages without cramming everything into nested subqueries. The SQL Analytics Endpoint supports the full T-SQL windowing syntax.

Let's rank customers by lifetime value within each customer segment:

SELECT
    c.customer_name,
    c.customer_segment,
    SUM(o.total_amount)  AS lifetime_value,
    RANK() OVER (
        PARTITION BY c.customer_segment
        ORDER BY SUM(o.total_amount) DESC
    ) AS rank_in_segment
FROM sales.orders AS o
INNER JOIN dim.customers AS c
    ON o.customer_id = c.customer_id
GROUP BY
    c.customer_name,
    c.customer_segment
ORDER BY
    c.customer_segment,
    rank_in_segment;

And here's a running total of monthly revenue — useful for cumulative progress charts:

WITH monthly_revenue AS (
    SELECT
        YEAR(order_date)   AS yr,
        MONTH(order_date)  AS mo,
        SUM(total_amount)  AS monthly_total
    FROM sales.orders
    GROUP BY
        YEAR(order_date),
        MONTH(order_date)
)
SELECT
    yr,
    mo,
    monthly_total,
    SUM(monthly_total) OVER (
        PARTITION BY yr
        ORDER BY mo
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_revenue
FROM monthly_revenue
ORDER BY yr, mo;

Notice the CTE (Common Table Expression) using WITH — that works exactly as you'd expect. CTEs, subqueries, derived tables, all supported.

---

Creating Views to Encapsulate Logic

One of the most underused features of the SQL Analytics Endpoint is the ability to create views. A view is a saved SQL query that you reference by name, like a virtual table. It doesn't store data — it stores the query definition. Every time you query the view, the underlying SQL runs against the current Delta data.

This is incredibly useful for a few reasons: you can hide complex join logic from downstream users, you can create analyst-friendly column names without altering the underlying tables, and you can use views as the source for Power BI semantic models.

Here's how to create a view that pre-joins orders with customer and product dimensions for a flat, report-ready dataset:

CREATE VIEW reporting.vw_order_details AS
SELECT
    o.order_id,
    o.order_date,
    o.region,
    o.total_amount,
    c.customer_name,
    c.customer_segment,
    c.country,
    oi.product_id,
    oi.quantity,
    oi.unit_price,
    oi.quantity * oi.unit_price  AS line_revenue,
    p.product_name,
    p.category,
    p.subcategory
FROM sales.orders AS o
INNER JOIN dim.customers AS c
    ON o.customer_id = c.customer_id
INNER JOIN sales.order_items AS oi
    ON o.order_id = oi.order_id
INNER JOIN dim.products AS p
    ON oi.product_id = p.product_id;

To create a schema first (if reporting doesn't exist yet):

CREATE SCHEMA reporting;

Now any analyst on the team — or any Power BI report — can query reporting.vw_order_details without needing to understand the join logic. And because it's a view over Delta tables, it always reflects the latest data.

---

Cross-Lakehouse Queries with Three-Part Names

The SQL Analytics Endpoint supports three-part naming for querying across lakehouses within the same workspace. Instead of schema.table, you use lakehouse_name.schema.table.

This is powerful in a medallion architecture setup, where you might have separate lakehouses for bronze, silver, and gold layers. You can write a gold-layer query that still references silver tables directly:

SELECT
    g.product_name,
    g.category,
    s.raw_supplier_code,
    g.gross_revenue
FROM gold_lakehouse.reporting.vw_order_details AS g
INNER JOIN silver_lakehouse.sales.orders AS s
    ON g.order_id = s.order_id;

If you're curious how the medallion pattern maps to Fabric lakehouse structure, Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers goes deep on that design.

---

Connecting External Clients

The SQL Analytics Endpoint isn't limited to the Fabric portal's built-in query editor. Because it speaks the TDS protocol, you can connect from:

SQL Server Management Studio (SSMS): In the connection dialog, paste your endpoint's connection string (found in the lakehouse settings under "SQL connection string"), select "Microsoft Entra ID - Universal with MFA" as the authentication method, and connect. You'll see your schemas and tables in the Object Explorer exactly as if you were connecting to an Azure SQL Database.

Azure Data Studio: Same connection string, same authentication. Azure Data Studio also supports notebooks, so you can mix T-SQL cells with Markdown cells to build shareable analytical documents.

Power BI Desktop: When adding a data source, choose "Microsoft Fabric" or "SQL Server," paste the endpoint connection string, and select the tables or views you want. This is the basis for Direct Lake mode reporting — though Direct Lake connects at the OneLake layer rather than through the SQL endpoint specifically.

Python / JDBC / ODBC: Any driver that supports SQL Server connectivity works. Use pyodbc with the SQL Server driver, or any JDBC-compatible tool like DBeaver.

---

Hands-On Exercise

Let's put everything together. For this exercise, you'll need a lakehouse with at least two Delta tables. If you've been following the learning path, use the tables you've already created. If not, create a simple lakehouse and load some sample data using Ingesting Data with Dataflow Gen2: Power Query Skills in Microsoft Fabric.

Step 1: Navigate to the SQL Analytics Endpoint Open your lakehouse, click the dropdown in the upper right that says "Lakehouse," and switch to "SQL analytics endpoint." Confirm that your tables appear in the Explorer pane on the left.

Step 2: Write a Basic Aggregation In the query editor, write a query that counts rows and calculates a SUM or AVG for a numeric column, grouped by at least one categorical column. Run it with F5.

Step 3: Add a JOIN If you have two tables with a shared key, write a query that joins them and produces a combined result. Verify the row count makes sense.

Step 4: Use a Window Function Add a RANK() or ROW_NUMBER() window function to your join query. Partition by one of your categorical columns and order by your numeric column.

Step 5: Create a View Wrap your most useful query in a CREATE VIEW statement. Give it a meaningful name under a reporting schema. Then query your view with SELECT * FROM reporting.your_view_name to confirm it works.

Step 6: Connect from SSMS (optional) Find your SQL connection string in the lakehouse settings (look for "SQL connection string" or "SQL Analytics Endpoint" under the lakehouse properties). Open SSMS and connect to it using Entra ID authentication. Browse the object explorer to see your schemas, tables, and the view you just created.

---

Common Mistakes & Troubleshooting

"My table doesn't appear in the SQL endpoint" This almost always means your data is in the Files section, not the Tables section, of the lakehouse. A CSV file dropped into Files is not a Delta table and isn't queryable via T-SQL. You need to either load it properly as a Delta table (via Spark, a Dataflow, or a pipeline), or use the "Load to Tables" shortcut in the lakehouse UI to convert it. Writing and Running Your First PySpark Notebook in Microsoft Fabric shows you how to create Delta tables from Spark.

"I tried to INSERT or UPDATE and got an error" Correct — the SQL Analytics Endpoint is read-only by design. You cannot write data through it. All writes must go through Spark (notebooks, Dataflow Gen2, pipelines). If you need a warehouse that supports DML (INSERT, UPDATE, DELETE, MERGE) via T-SQL, consider using an actual Fabric Data Warehouse. The endpoint is for querying, not loading.

"My query runs but returns stale data" This is rare but can happen if the Delta table metadata hasn't been refreshed. The SQL Analytics Endpoint automatically syncs with the Delta log, but there's sometimes a brief lag (typically under a minute) after a large Spark write completes. Wait a moment and retry.

"My window function query is very slow" Window functions require a full sort of the partitioned data, which can be expensive on large tables. Make sure your WHERE clause is filtering aggressively before the window function is applied. Using a CTE or subquery to pre-filter, then applying the window function on the filtered set, often dramatically improves performance.

"I created a view but can't find it in the Explorer pane" Try refreshing the Explorer pane — right-click the schema node and select Refresh, or use the refresh icon at the top of the Explorer. Views appear under the schema where you created them, separate from the Tables node.

"Cross-lakehouse query fails with an object not found error" Double-check the lakehouse name used in the three-part name. The name is case-sensitive, and it must exactly match the lakehouse name in your workspace. Also confirm both lakehouses are in the same workspace.

---

Summary & Next Steps

The SQL Analytics Endpoint bridges two worlds that are often treated as separate: the Spark-powered lakehouse ecosystem and the familiar, T-SQL-speaking world of relational databases. You don't have to choose between storing data efficiently as Delta tables and querying it with the SQL skills your team already has. The endpoint makes both possible simultaneously, reading the same Parquet files, reflecting the same transaction log, requiring no additional infrastructure.

In this lesson you've learned how to navigate to the endpoint in the Fabric portal, write SELECT queries with filtering and aggregation, join across multiple tables and schemas, use window functions for analytical calculations, create views for reusable logic, and connect from external SQL clients. You've also seen where the endpoint's boundaries are — it's read-only, it requires Delta tables (not raw files), and it only supports cross-lakehouse queries within the same workspace.

For next steps, consider how the views you create in the SQL Analytics Endpoint can serve as the foundation for Power BI semantic models — either in import mode or in Direct Lake mode, which you can explore in the article on Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery. If your SQL querying needs grow beyond read-only analytics — you need stored procedures, DML operations, or fine-grained T-SQL permissions — that's the signal to look at Building a Fabric Data Warehouse with T-SQL: Tables, Views, and Cross-Database Queries.

The SQL Analytics Endpoint is one of those Fabric features that looks simple on the surface — you just flip a switch and your lakehouse speaks SQL. But once you understand why it works (Delta on OneLake, TDS protocol, in-place reads), you can use it with confidence and know exactly where it will serve you well and where you need something more.