Querying Data with SQL and the Visual Query Editor

Introduction

This lesson belongs to the domain Prepare data (45–50% of the DP-600 skills measured), in the "Query and analyze data" group: select, filter and aggregate data by using the visual query editor and SQL. This course targets the English syllabus effective October 19, 2026; Microsoft describes the change to querying and analyzing data as minor. Microsoft does not publish the question count or the question-type mix, so OnLevelUp mock exams use a 50-question practice length and an unofficial 80% practice target, which is not equivalent to the official 700/1000 scaled passing score.

Once data sits in OneLake, someone has to look at it. In Fabric you rarely start by building a report: you explore with a query. The exam describes an analyst, a data source (warehouse, lakehouse, mirrored database) and a business question, then asks which query surface and which statement solve it, and what the limits are. After this lesson you will be able to build queries in the visual query editor, write T-SQL that selects, filters, joins, groups and ranks data, explain what the SQL analytics endpoint can and cannot do, and choose between the tools available.

Learning objectives

• Build and save a query in the visual query editor, and know its limits.
• Write T-SQL to select, filter, sort, join and aggregate data.
• Use CTEs and window functions to answer ranking and running-total questions.
• Explain the role and the read-only nature of the SQL analytics endpoint of a lakehouse.
• Query across items in a workspace and pick the right query surface for the user.

1. Where SQL runs in Fabric

Fabric offers T-SQL on several items. They share the same engine family, but not the same capabilities.

Key facts for the exam:

• The SQL analytics endpoint gives read-only T-SQL access to the Delta tables of a lakehouse. To change the data you use Spark (notebooks, Spark job definitions), dataflows or pipelines. Writing through the endpoint does not work for tables.
• Tables created or changed through Spark appear in the endpoint after an automatic metadata sync. It is normally fast but not instantaneous; a table written a few seconds ago can briefly be absent or stale. Data files are always read from the same OneLake storage, so there is no copy.
• The endpoint exposes only managed Delta tables. Files in the Files section are not queryable as tables until they are loaded into a table or exposed through a shortcut to a Delta location.
• Security can be applied in the endpoint with object-level permissions, row-level security, column-level security and dynamic data masking (covered in lesson 1).

2. The visual query editor

The visual query editor is a no-code, Power Query-like surface available in a warehouse and in the SQL analytics endpoint of a lakehouse. You drag tables onto a canvas and build a diagram of transformation steps. It is designed for analysts who do not know T-SQL, and every step is translated into SQL that runs on the engine, so nothing is pulled to the client.

Typical operations, found in the ribbon or the step context menu:

What you can do with the result:

• Save as view to publish a reusable object (in a warehouse, or in the lakehouse endpoint).
• Open in Excel and create a Power BI report from the query result.
• View SQL: inspect the generated T-SQL, which is a good way to learn and to verify that the query was translated as expected.

Limits to remember:

• The visual editor is a read surface. It does not write data and does not run DDL or DML.
• It cannot use every T-SQL feature (for example procedural logic, temp tables, dynamic SQL and some window constructs). When you outgrow it, switch to the SQL query editor.
• Queries use the permissions of the signed-in user.

Example. A sales manager at Contoso Outdoor, who does not write SQL, wants the ten best-selling products of 2026. In the visual query editor she drags FactSales and DimProduct, merges them on ProductKey (inner), filters OrderDate to 2026, groups by ProductName with a sum of SalesAmount, sorts descending and keeps the top 10 rows. She selects Save as view under the name vw_Top10Products2026 so a Power BI model can reuse it, and checks View SQL to see the generated statement.

3. The SQL query editor and other T-SQL surfaces

Besides the visual editor, Fabric provides:

• The SQL query editor in the portal: write T-SQL, run selected statements, save queries as personal or shared.
• SQL Server Management Studio, Azure Data Studio and the mssql extension for Visual Studio Code, connecting through the SQL connection string of the item.
• Notebooks with T-SQL cells (a T-SQL notebook runs against a warehouse) and PySpark cells with spark.sql for the lakehouse.
• Copilot features that draft T-SQL from a prompt, which you should always review.

Authentication uses Microsoft Entra ID. Cross-item queries are possible: from a warehouse you can reference a lakehouse or another warehouse of the same workspace with three-part names (SalesLakehouse.dbo.Orders), which lets a single statement combine curated and raw data without copying them.

4. Selecting and filtering

The basic query shape and the logical order of evaluation matter for the exam: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → TOP/OFFSET.

SELECT TOP (20)
       p.Category,
       p.ProductName,
       s.OrderDate,
       s.SalesAmount
FROM   dbo.FactSales   AS s
JOIN   dbo.DimProduct  AS p ON p.ProductKey = s.ProductKey
WHERE  s.OrderDate >= '2026-01-01'
  AND  s.OrderDate <  '2027-01-01'
  AND  p.Category IN ('Bikes', 'Accessories')
  AND  s.SalesAmount BETWEEN 100 AND 5000
ORDER BY s.SalesAmount DESC;

Useful predicates and patterns:

TOP without ORDER BY returns an arbitrary set of rows; for a "top N" question, always sort. Function calls on a filtered column (for example WHERE YEAR(OrderDate) = 2026) can prevent efficient data skipping; comparing the raw column to a range is the safer habit.

5. Aggregating data

Aggregates reduce rows to summary values: SUM, COUNT, COUNT(DISTINCT ...), AVG, MIN, MAX, plus statistical ones such as STDEV. Fabric Warehouse also offers APPROX_COUNT_DISTINCT, a fast estimate for very large tables when an exact count is not required.

SELECT  d.CalendarYear,
        d.MonthNumber,
        c.Region,
        SUM(s.SalesAmount)             AS Revenue,
        COUNT(DISTINCT s.OrderID)      AS Orders,
        AVG(s.SalesAmount)             AS AvgLine
FROM    dbo.FactSales s
JOIN    dbo.DimDate     d ON d.DateKey     = s.OrderDateKey
JOIN    dbo.DimCustomer c ON c.CustomerKey = s.CustomerKey
WHERE   d.CalendarYear = 2026
GROUP BY d.CalendarYear, d.MonthNumber, c.Region
HAVING  SUM(s.SalesAmount) > 50000
ORDER BY d.MonthNumber, Revenue DESC;

Rules the exam likes to test:

• Every non-aggregated column in SELECT must appear in GROUP BY.
• WHERE filters rows before grouping and cannot reference aggregates; HAVING filters groups after aggregation.
• Aggregates ignore NULLs, except COUNT(*) which counts rows. COUNT(col) counts non-null values.
• Column aliases defined in SELECT can be used in ORDER BY but not in WHERE or HAVING.
• GROUP BY ROLLUP, CUBE and GROUPING SETS produce subtotals and grand totals in one pass (check the T-SQL surface area page for the exact support in Fabric Warehouse before relying on them in a design).

SELECT Region, Category, SUM(SalesAmount) AS Revenue
FROM   dbo.vSalesDetail
GROUP BY ROLLUP (Region, Category);   -- adds regional subtotals and a grand total

6. Joins, set operators and CTEs

Join types in T-SQL mirror the visual editor: INNER, LEFT, RIGHT, FULL, CROSS. "Anti" results are written with LEFT JOIN ... WHERE right.key IS NULL or NOT EXISTS. Prefer NOT EXISTS to NOT IN when the subquery column may contain NULLs, because NOT IN then returns no rows.

Set operators combine results with the same column count and compatible types: UNION (distinct), UNION ALL (keeps duplicates, faster), INTERSECT, EXCEPT.

A common table expression (CTE) names an intermediate result and makes long queries readable:

WITH MonthlyRevenue AS (
    SELECT d.CalendarYear, d.MonthNumber, SUM(s.SalesAmount) AS Revenue
    FROM   dbo.FactSales s
    JOIN   dbo.DimDate d ON d.DateKey = s.OrderDateKey
    GROUP BY d.CalendarYear, d.MonthNumber
)
SELECT *
FROM   MonthlyRevenue
WHERE  Revenue > 1000000;

7. Window functions

A window function computes a value for each row over a related set of rows without collapsing them (unlike GROUP BY). The OVER clause defines PARTITION BY (the groups), ORDER BY (the order) and optionally a frame (ROWS BETWEEN ...).

SELECT  Region,
        OrderMonth,
        Revenue,
        SUM(Revenue) OVER (PARTITION BY Region ORDER BY OrderMonth
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningRevenue,
        LAG(Revenue) OVER (PARTITION BY Region ORDER BY OrderMonth)        AS PrevMonth,
        RANK() OVER (PARTITION BY OrderMonth ORDER BY Revenue DESC)        AS RankInMonth
FROM    dbo.vMonthlyRevenue;

Window functions are evaluated after WHERE, GROUP BY and HAVING, so you cannot filter on them in the same SELECT. Wrap the query in a CTE and filter in the outer query.

Worked example 6: best product per region. Statement. Fabrikam sells in five regions. The analyst needs, for each region, the product with the highest revenue in 2026, and a tie must return all tied products. The data is in a warehouse view dbo.vRegionProductRevenue(Region, ProductName, Revenue). Reasoning. (1) Aggregation alone gives the max revenue per region, but not the product name; joining back to it works but is clumsy. (2) A ranking per region answers directly: partition by Region, order by Revenue descending. (3) Because ties must all be returned, RANK() or DENSE_RANK() is required; ROW_NUMBER() would pick one arbitrary product. (4) The ranking cannot be filtered in the same SELECT, so put it in a CTE. Solution.

WITH ranked AS (
  SELECT Region, ProductName, Revenue,
         RANK() OVER (PARTITION BY Region ORDER BY Revenue DESC) AS rnk
  FROM   dbo.vRegionProductRevenue
)
SELECT Region, ProductName, Revenue
FROM   ranked
WHERE  rnk = 1;

Why the other choices are worse. TOP 1 returns one row for the whole table, not one per region. ROW_NUMBER() hides ties. GROUP BY Region with MAX(Revenue) loses the product name. A subquery with WHERE Revenue = (SELECT MAX(Revenue) ...) without correlation compares to the global maximum only.

8. Reading the lakehouse through the SQL analytics endpoint

The endpoint is created automatically with the lakehouse (and with a mirrored database). Think of it as a read-only SQL window onto Delta tables.

What it supports:

• Queries over Delta tables, including cross-item queries with three-part names.
• Creating views, inline table-valued functions and stored procedures to encapsulate logic, plus security objects (roles, row-level security predicates).
• The visual query editor, the SQL query editor, external clients and Power BI semantic models in Direct Lake or DirectQuery mode.

What it does not support: INSERT, UPDATE, DELETE, MERGE and CREATE TABLE on the lakehouse tables. If the question says "the team needs to update rows with T-SQL", the answer is a warehouse (or SQL database), not the endpoint.

Two behaviors that appear in scenarios:

1. Metadata sync delay: new tables or schema changes written by Spark show up after sync. If a freshly created table is missing, the cause is usually sync timing or a table that is not Delta, not a permission problem. Fabric also lets you force a metadata refresh from the endpoint.
2. Unsupported table formats: folders of plain Parquet or CSV files are not exposed as tables.

9. Comparing the query tools

Common pitfalls

• Writing WHERE Column = NULL instead of IS NULL.
• Filtering with an aggregate in WHERE instead of HAVING.
• Using TOP without ORDER BY and trusting the result.
• Using ROW_NUMBER() when ties matter, or RANK() when gaps are not wanted.
• Using NOT IN against a nullable column.
• Expecting to run UPDATE through a lakehouse SQL analytics endpoint.
• Forgetting the metadata sync delay when a new Spark table is not visible yet.
• Joining on columns of different data types and silently converting every row.
• Believing the visual query editor can write data or run procedural logic.

Exam tip: when a scenario states that the user "does not write code" and needs a reusable dataset, think visual query editor plus Save as view. When it needs a ranking per group, think RANK/DENSE_RANK/ROW_NUMBER in a CTE. When it mentions modifying data with T-SQL, check whether the target is a warehouse or a lakehouse endpoint.

Mini case study

Adventure Works Rentals stores rental data in a lakehouse RentalsLH (loaded nightly by Spark) and curated dimensions in a warehouse RentalsDW, both in the same workspace. The finance team (T-SQL users) asks for a monthly report: revenue by branch, running year-to-date revenue, and the best three bikes per branch. A marketing analyst without SQL skills wants a list of customers with no rental in the last 12 months. Finance also asks to correct 200 mis-typed branch codes in the lakehouse table with an UPDATE statement.

Proposed solution.

1. Finance uses the SQL query editor on the warehouse. A CTE aggregates revenue by branch and month from RentalsLH.dbo.Rentals (three-part name), joined to RentalsDW.dbo.DimBranch. A running total uses SUM(...) OVER (PARTITION BY Branch ORDER BY Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).
2. The top three bikes per branch use DENSE_RANK() OVER (PARTITION BY Branch ORDER BY Revenue DESC) in a CTE and WHERE rnk <= 3 outside.
3. The marketing analyst uses the visual query editor: merge DimCustomer with Rentals using a left anti merge on the last 12 months of rentals, then Save as view for her Power BI report.
4. The UPDATE cannot run on the lakehouse SQL analytics endpoint. The correction is done in a notebook with a Delta UPDATE/MERGE statement (or fixed upstream and reloaded); the endpoint then shows the corrected values after sync.

Commentary. Each need matched the surface with the right capability. The only trap was the write: the endpoint is read-only, so the fix belongs in Spark, or the table moves to a warehouse if T-SQL writes are a permanent requirement.

Key terms

• Visual query editor: no-code query builder that generates T-SQL.
• SQL analytics endpoint: read-only T-SQL access to lakehouse (or mirrored) Delta tables.
• CTE: named intermediate result defined with WITH.
• Window function: calculation over a set of rows related to the current row, defined with OVER.
• HAVING: filter applied to groups after aggregation.
• Three-part name: item.schema.table syntax for cross-item queries.
• Metadata sync: process that refreshes endpoint table definitions after changes in the lakehouse.
• ROLLUP / CUBE / GROUPING SETS: grouping extensions producing subtotals.

Summary checklist

• I can build, save as a view and inspect the SQL of a visual query.
• I know the visual editor and the SQL analytics endpoint are read surfaces.
• I can write SELECT, WHERE, GROUP BY, HAVING and ORDER BY in the correct logical order.
• I can pick RANK, DENSE_RANK or ROW_NUMBER depending on tie handling, and filter them through a CTE.
• I can write running totals and period comparisons with window functions.
• I know when a warehouse, not a lakehouse endpoint, is required.
• I remember that the practice length (50 questions) and 80% target are not official, and that the course targets the syllabus effective October 19, 2026.

Official sources

• Study guide for Exam DP-600: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-600 (opens in a new tab)
• Query using the visual query editor: https://learn.microsoft.com/en-us/fabric/data-warehouse/visual-query-editor (opens in a new tab)
• Query using the SQL query editor: https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-query-editor (opens in a new tab)
• SQL analytics endpoint for a lakehouse: https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-sql-analytics-endpoint (opens in a new tab)
• T-SQL surface area of the Fabric Data Warehouse: https://learn.microsoft.com/en-us/fabric/data-warehouse/tsql-surface-area (opens in a new tab)