The SQL that matters, and why
Examples use the course's company pack (orders, order_lines, customers, products, regions; amounts are in each order's currency). The SQL track has assignments with answers that are executed in CI.
| Topic | What it's for in BI | Learn it |
|---|---|---|
SELECT, WHERE | Extract only the rows and columns the model needs | Querying for BI |
JOIN | Assemble facts with their keys; find orphans with LEFT JOIN … WHERE x IS NULL | Querying for BI |
GROUP BY | Pre-aggregate before import; reconcile totals with the source | Querying for BI |
| CTEs | Readable, testable transformation steps | CTEs and windows |
| Window functions | Deduplicate, rank, running totals, "latest row per key" | CTEs and windows |
CASE | Classify and clean in one pass | Querying for BI |
| Views | A stable, documented interface between source and model | Views and loads |
| Stored procedures | Controlled, parameterised extracts and loads | Views and loads |
| Indexes | Make DirectQuery and incremental-refresh queries fast | Slow source queries |
| Execution plans | See why a query is slow before guessing | Slow source queries |
| Temp tables | Stage multi-step transformations | Views and loads |
| SCD patterns | Keep dimension history correct | Keys and SCDs |
| Incremental predicates | Load only what changed | Views and loads |
| Date functions | Calendars, fiscal periods, time zones | Calendars and time zones |
Patterns you'll use weekly
One row per key (deduplicate)
-- order_lines contains a re-exported batch: the same (OrderID, LineNo) appears twice
WITH ranked AS (
SELECT ol.*,
ROW_NUMBER() OVER (PARTITION BY OrderID, "LineNo" ORDER BY ProductKey) AS rn
FROM order_lines AS ol
)
SELECT * FROM ranked WHERE rn = 1;When the source has a load timestamp or version column, order by it descending so you keep the latest copy rather than an arbitrary one.
Orphans: facts with no dimension row
SELECT ol.ProductKey, COUNT(*) AS lines
FROM order_lines AS ol
LEFT JOIN products AS p ON p.ProductKey = ol.ProductKey
WHERE p.ProductKey IS NULL
GROUP BY ol.ProductKey;Reconcile the report with the source
SELECT strftime('%Y-%m', o.OrderDate) AS month, -- FORMAT(o.OrderDate, 'yyyy-MM') in T-SQL
ROUND(SUM(ol.Qty * ol.UnitPrice * (1 - ol.DiscountPct / 100.0)), 2) AS net_sales,
COUNT(DISTINCT o.OrderID) AS orders
FROM orders AS o
JOIN order_lines AS ol ON ol.OrderID = o.OrderID
GROUP BY 1
ORDER BY 1;Put the result next to the same breakdown from your model. If they differ, the model is wrong until proven otherwise.
Running total
SELECT month, net_sales,
SUM(net_sales) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running
FROM monthly_sales;Incremental extract
-- the predicate Power BI's incremental refresh folds into each partition query
SELECT * FROM orders
WHERE OrderDate >= @RangeStart AND OrderDate < @RangeEnd;Note the >= and <: with <= on both ends a row exactly on the boundary loads into two partitions.
Reading an execution plan
When a DirectQuery visual or a refresh is slow, capture the SQL Power BI sends (Performance Analyzer → Copy query, or a trace in DAX Studio), run it in SSMS with Include Actual Execution Plan, and look for:
- Scans on big tables where you expected seeks: a missing or unusable index (functions on the column, such as
YEAR(OrderDate) = 2026, prevent seeks; writeOrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'). - Huge estimated vs actual row differences: stale statistics.
- Key lookups repeated millions of times: add included columns to the index.
- Sorts and hash matches spilling to tempdb: memory grants too small for the data volume.
Then fix the source (index, view, pre-aggregated table) before you fix the model.
Where should this transformation happen?
Do work as far upstream as is practical, and as far downstream as is necessary. Roche's maxim, in practice:
| Operation | Usually prefer | Why |
|---|---|---|
| Rename columns for business users | Power Query or the model | Presentation concern; cheap anywhere |
| Aggregate 300 million rows | SQL or the warehouse | The engine built for it, close to the data |
| A business metric (Revenue, Margin %) | DAX measure in a shared semantic model | Must respond to filters; one definition for every report |
| Slowly changing dimension type 2 | Warehouse | Needs history the model can't reconstruct |
| A display label ("Q1 2026") | Date table column | Computed once, sortable |
| Fix bad source data | At the source, or the first upstream layer | Every consumer benefits; the model only hides it |
| A KPI shared across reports | Measure in a certified model | Prevents three different "Revenue" numbers (Sprint 05) |
| One-off clean-up for an ad-hoc analysis | Power Query | Fast to change, nobody else depends on it |
| Joins that create the star schema | Warehouse views, or Power Query when there's no warehouse | Folds to the source; keeps the model simple |
| Currency conversion | Upstream, stored as converted amounts | Per-row lookups in DAX are slow |
Dialects
The course uses portable SQL, and CI runs it on SQLite. At work you'll mostly meet T-SQL (SQL Server, Azure SQL, Fabric Warehouse and SQL analytics endpoints). The differences you'll hit first: date formatting (FORMAT vs strftime), TOP n vs LIMIT n, string concatenation (+ or CONCAT vs ||), and ISNULL vs COALESCE (use COALESCE: it's standard).