Query with the Visual Query Editor and SQL

What it is

A warehouse and a lakehouse's SQL analytics endpoint offer two ways to query the same tables. The Visual query editor builds a query by dragging tables and choosing steps, with no code. The SQL query editor runs T-SQL that you write.

Both return a result grid, and both can save the query as a view. The exam asks you to pick the right steps in the visual editor and to read or complete T-SQL that selects, filters and aggregates.

How it works

The Visual query editor

You open it with New visual query in the warehouse or SQL analytics endpoint. Drag tables from the Explorer onto the canvas, then add steps from a ribbon that looks like Power Query.

The editor translates each step into T-SQL, and View SQL shows the statement it generated. Only steps that can be translated to T-SQL are available, which is why some Power Query transformations are missing.

The visual editor is the best answer for analysts who do not write SQL but need to explore warehouse tables or create a view for others.

Selecting, filtering and aggregating with T-SQL

SELECT TOP 10
    p.CategoryName,
    SUM(f.SalesAmount) AS TotalSales,
    COUNT(DISTINCT f.OrderID) AS Orders
FROM dbo.FactSales AS f
JOIN dbo.DimProduct AS p
    ON p.ProductKey = f.ProductKey
JOIN dbo.DimDate AS d
    ON d.DateKey = f.DateKey
WHERE d.[Year] = 2026
GROUP BY p.CategoryName
HAVING SUM(f.SalesAmount) > 100000
ORDER BY TotalSales DESC;

SQL evaluates the clauses in this logical order:

That order explains two classic exam traps. WHERE cannot use an aggregate such as SUM, because rows are filtered before they are grouped; use HAVING. A column alias such as TotalSales works in ORDER BY but not in WHERE.

Window functions

Window functions calculate across related rows without collapsing them, so each row keeps its detail.

SELECT
    d.[Year],
    d.MonthNo,
    SUM(f.SalesAmount) AS MonthSales,
    SUM(SUM(f.SalesAmount)) OVER (
        PARTITION BY d.[Year] ORDER BY d.MonthNo) AS RunningTotal,
    RANK() OVER (
        PARTITION BY d.[Year] ORDER BY SUM(f.SalesAmount) DESC) AS MonthRank
FROM dbo.FactSales AS f
JOIN dbo.DimDate AS d
    ON d.DateKey = f.DateKey
GROUP BY d.[Year], d.MonthNo;

Where you can run the SQL

The SQL query editor is in the Fabric portal. Because the warehouse and the SQL analytics endpoint expose a SQL connection string, tools such as SQL Server Management Studio and Visual Studio Code can run the same queries. Queries can also join items in the same workspace with three-part names.

A worked example

A sales manager asks for the top categories in 2026 with more than 100,000 in sales, ranked by sales.

1. A business analyst without SQL skills opens a visual query, drags FactSales, DimProduct and DimDate, merges them on their keys, filters Year to 2026, groups by category with a sum of SalesAmount, and sorts descending.
2. The analyst uses View SQL to check the generated statement, then saves it as a view called vTopCategories2026.
3. A developer later adds the threshold with HAVING SUM(f.SalesAmount) > 100000, which a WHERE clause could not do.

How the exam asks about this

• "Show only customers whose total sales exceed 50,000." GROUP BY with HAVING SUM(...) > 50000.
• "Rank products by sales within each category, with ties sharing a rank and no gaps." DENSE_RANK() OVER (PARTITION BY Category ORDER BY Sales DESC).
• "An analyst who does not know SQL must join two warehouse tables and save the result for others." The Visual query editor, saved as a view.