Launch Deal · 50% off 1st month ·

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 WHERE instead of HAVING
  • Using GROUP BY when 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.

See plans

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.