2.7 — Query Data with SQL and the Visual Query Editor
DP-600 Domain 2 · Skill 2.7
What You Need to Know
Analytics engineers explore and validate data with T-SQL, either written by hand or built with the visual query editor, against warehouses and lakehouse SQL analytics endpoints. Exam items show a query and ask what it returns, or ask which clause to change.
Where you query
- Warehouse and lakehouse SQL analytics endpoint: open the SQL query editor in the Fabric portal, or connect from SSMS or VS Code with the SQL connection string.
- Cross-database queries: within a workspace, join tables across warehouses and lakehouse endpoints with three-part names:
SELECT ... FROM SalesLH.dbo.orders o JOIN FinanceWH.dbo.budget b ON .... - Saving: save a query as a view to reuse it, or keep it as a personal or shared query in the editor.
The visual query editor
- A Power Query-style canvas for warehouses and SQL endpoints: drag tables on, then choose columns, filter rows, group by, merge (join), append, sort and keep top rows. It writes the T-SQL for you.
- View SQL shows the generated statement. You can save the result as a view or open it as a new SQL query.
- It's meant for no-code exploration by analysts. Steps that can't be expressed in SQL aren't available, and complex logic is better written in T-SQL.
Select, filter and aggregate: patterns to recognize
-- Top 5 categories by revenue in 2026, only categories above 1M
SELECT TOP (5) p.Category, SUM(f.Amount) AS Revenue
FROM gold.FactSales f
JOIN gold.DimProduct p ON p.ProductKey = f.ProductKey
JOIN gold.DimDate d ON d.DateKey = f.DateKey
WHERE d.[Year] = 2026 -- filters rows BEFORE grouping
GROUP BY p.Category
HAVING SUM(f.Amount) > 1000000 -- filters groups AFTER grouping
ORDER BY Revenue DESC;
| Need | Use |
|---|---|
| Filter rows | WHERE |
| Filter aggregated groups | HAVING |
| Running total or rank | Window functions: SUM(Amount) OVER (ORDER BY DateKey), RANK() OVER (PARTITION BY Category ORDER BY Amount DESC) |
| Previous row's value | LAG(Amount) OVER (PARTITION BY ProductKey ORDER BY DateKey) |
| Subtotals | GROUP BY ROLLUP(...), CUBE(...), GROUPING SETS(...) |
| Distinct count | COUNT(DISTINCT CustomerKey) (or APPROX_COUNT_DISTINCT for speed on huge tables) |
| Reuse logic within a query | Common table expressions (WITH cte AS (...)) |
Exploratory analytics
- Descriptive (what happened) and diagnostic (why): aggregations, comparisons with earlier periods, drill-downs, and the decomposition tree and key influencers visuals in Power BI.
- Predictive and prescriptive: bring model scores (for example a notebook that batch-scores with a Fabric ML model and
PREDICT, then writes the predictions to a table) into the model and show them in visuals; use what-if parameters for scenarios. - Profile data before modeling: row counts, distinct counts, null rates, min and max values (T-SQL aggregates, or the Power Query profiling tools).
Key Concept: WHERE filters rows and HAVING filters groups. Window functions add rank, running totals and LAG without collapsing rows. The visual query editor builds the same T-SQL without code and can save it as a view.
Exam Traps
- Putting an aggregate condition in
WHEREinstead ofHAVING - Using
GROUP BYwhen the requirement is a rank or running total that keeps every row (use window functions) - Expecting the visual query editor to write data into tables
- Forgetting the database name (three-part names) in cross-database queries
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 17 DP-600 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- Query using the visual query editor, Microsoft Learn
- Query using the SQL query editor, Microsoft Learn
- Write a cross-database SQL query, Microsoft Learn
- SELECT - OVER clause (Transact-SQL), Microsoft Learn