The Power BI Fellowship

SQL for BI Track

Not database administration: the SQL a BI developer writes and reads every week. Everything runs on the Northwind company pack. Use SQL Server Developer or Express (closest to most jobs), DuckDB (no install: it reads the CSVs directly) or SQLite. The model answers are written in portable SQL and executed in our CI against SQLite, so their numbers are guaranteed to match the expected results.

Before you start Load the seven CSVs from data/experience/company/ as tables named orders, order_lines, returns, customers, products, regions and fx_rates. DuckDB: CREATE TABLE orders AS SELECT * FROM 'orders.csv'; and so on. SQL Server: Import Flat File wizard in SSMS.

Querying for BI: grain, joins and aggregation

Data: NWOrders NWOrderLines NWCustomers NWRegions NWFx
Why it matters
Most wrong numbers in BI are join mistakes: a join that multiplies rows, or one that silently drops them. SQL is where you see the grain clearly.
Typical production failure
An analyst joins orders to order lines and counts rows as orders; the dashboard reports twice as many orders as the business has.
When to use it
Whenever data comes from a database: check the grain of every table and of every result before aggregating.
When not to
Writing the whole model in one giant query. Keep views at a clear grain and let the semantic model do the slicing.

Assignments

Q1 2026 sales by region

A · Guided 40 min · uses orders, order_lines, regions, fx_rates
  1. Load the company pack into your SQL engine (see Before you start).
  2. order_lines contains exact duplicate rows from a re-export. In a CTE, keep one copy of each (SELECT DISTINCT * is enough here).
  3. Join the lines to orders. Keep only Status = 'Completed', OrderDate from 2026-01-01 to 2026-03-31, and exclude the test customer C19999.
  4. Line value = ROUND(Qty × UnitPrice × (100 − DiscountPct) / 100, 2). Convert EUR lines to USD: join fx_rates on the order month (first 7 characters of OrderDate) and Currency, and multiply; round each line to cents.
  5. Join regions on SalesRegionKey and group by RegionName. Add a grand total in a second query, or with ROLLUP if your engine has it.
Expected result: Five rows. West $60,740, South $58,572, East $48,802, Central $64,587, Europe $22,050; total $254,751.

Count orders, lines and customers correctly

B · Objective 30 min · uses orders, order_lines

Sales wants one row per channel for Q1 2026 with the number of orders, order lines and buying customers, plus a total row. The totals must be right, not just the channel rows.

Requirements
  • Same filters as the previous assignment (deduplicated, completed, no test customer, Q1 2026).
  • Show in a comment why COUNT(*) after the join is the wrong way to count orders.
  • Explain why the channel rows don't add up to the total for customers.
Expected result: Total: 583 orders, 1,187 lines, 280 customers. The customers column doesn't add up across channels, because some customers buy through more than one.

The customer list that lost customers

C · Problem 30 min · uses customers, orders, order_lines

Marketing asked for every customer with their Q1 2026 sales, including customers who bought nothing (they want to win them back). The query they were given returns only 280 rows, but the company has 420 real customers. Find out why and fix it.

Work out
  • Return one row per customer (excluding the test account) with Q1 2026 gross sales, 0 when they bought nothing.
  • Explain the bug in one sentence.
What good looks like: 420 rows, of which 140 have 0. The cause: a LEFT JOIN followed by a WHERE condition on the right-hand table turns it back into an inner join.

Interview questions

  • What does "grain" mean, and why check it before writing a join?
  • Difference between WHERE and ON in a LEFT JOIN?
  • COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)?
  • Why do distinct counts not add up across categories?

Assessment

SELECT COUNT(*) FROM orders o JOIN order_lines l ON l.OrderID = o.OrderID returns:

  • The number of orders
  • The number of order lines that have a matching order
  • The number of customers
  • The number of orders with more than one line

A LEFT JOIN from customers to orders with WHERE orders.OrderDate >= '2026-01-01' returns:

  • All customers
  • Only customers with at least one order on or after that date
  • Only customers without orders
  • An error

Write a query that returns the grain (the key columns and whether they are unique) of each table in the company pack.

For each table compare COUNT(*) with COUNT(DISTINCT key) (or with a GROUP BY on the candidate key HAVING COUNT(*) > 1). order_lines will surprise you.

CTEs, window functions and deduplication

Data: NWOrderLines NWOrders NWCustomers NWProducts
Why it matters
Window functions answer the questions BI is asked every day (top N, running totals, previous period, latest row per key) without self-joins, and ROW_NUMBER is the standard tool for removing duplicates safely.
Typical production failure
A load deduplicates with SELECT DISTINCT on a table that has a changing timestamp column: nothing is removed, and the fact doubles.
When to use it
Ranking, running totals, period-over-period, keeping the latest version of a row, gaps and islands.
When not to
Using window functions to do what the semantic model will do anyway at query time (running totals over the visual's axis are often better as DAX or visual calculations).

Assignments

Remove the duplicated lines with ROW_NUMBER

B · Objective 25 min · uses order_lines

The March re-export duplicated some order lines. SELECT DISTINCT works today, but next time the duplicates may differ in a load timestamp. Write a deduplication that keeps exactly one row per (OrderID, LineNo) whatever the other columns contain.

Requirements
  • One row per (OrderID, LineNo).
  • Say how many rows you removed, and how you would choose which copy to keep if the copies differed.
Expected result: 7,252 rows in, 7,215 rows out: 37 duplicates removed.

Top products and a running total

B · Objective 30 min · uses order_lines, orders, products

For the Q1 2026 review, Jordan wants the top three products by gross sales and the cumulative gross sales month by month.

Requirements
  • Use RANK or ROW_NUMBER for the top three (no TOP or LIMIT, so it works everywhere).
  • Running total by month with SUM() OVER (ORDER BY …).
  • Same filters and currency rules as the previous topic.
Expected result: Top three: Riverstone Kayak 10 $53,397, Drift Paddle Board $32,749, Summit 65L Backpack $21,945. Running total: January $107,539, February $178,287, March $254,751.

How dependent are we on one customer?

C · Problem 30 min · uses order_lines, orders, customers

The CFO asks how concentrated Q1 2026 sales are. Answer with numbers, and say whether the business should worry.

Work out
  • Share of Q1 gross sales from the largest customer, and from the top 10.
  • A one-sentence answer for the CFO.
What good looks like: The largest customer, Summit Outfitters Co., is 15.4% of Q1 2026 gross sales ($39,351). Combined with Sprint 01 (they stopped ordering in February) that is a material risk.

Interview questions

  • ROW_NUMBER, RANK and DENSE_RANK: what's the difference?
  • How do you keep the latest version of each customer row?
  • When is a CTE better than a subquery?
  • What does the window frame in SUM() OVER (ORDER BY Month) default to, and why does it matter?

Assessment

Which removes duplicates even when the duplicate rows differ in a LoadedAt column?

  • SELECT DISTINCT *
  • ROW_NUMBER() OVER (PARTITION BY OrderID, LineNo ORDER BY LoadedAt DESC) = 1
  • GROUP BY *
  • UNION

Two products tie for second place. Which function returns 1, 2, 2, 4?

  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • NTILE

Write a query that shows, for each month of 2026, gross sales and the change versus the previous month, using LAG.

Aggregate to one row per month in a CTE first, then LAG(Gross) OVER (ORDER BY Month).

Views, incremental extraction and idempotent loads

Data: NWOrders NWOrderLines NWFx
Why it matters
Power BI should read from stable, documented views at a clear grain, and loads should be safe to re-run. That is what makes refresh failures recoverable.
Typical production failure
A nightly load is re-run after a failure and appends the same day twice; March revenue doubles in the board pack.
When to use it
A view per fact and dimension that the model reads; watermark-based incremental extraction with a lookback; delete-and-insert or MERGE on the business key.
When not to
Business logic spread across Power Query steps in five models; it belongs in one view (or the warehouse) so every consumer agrees.

Assignments

A view Power BI can trust

B · Objective 40 min · uses orders, order_lines, fx_rates

Replace the Power Query steps from Sprint 01 with one view, vw_FactSales, that every model can read.

Requirements
  • One row per order line, deduplicated; completed orders only; test customer excluded.
  • Only the columns a model needs, with clear names and types; amounts in USD.
  • Include OrderDate and PostingDate so both Sales and Finance definitions are possible.
Expected result: For Q1 2026 the view returns 1,187 rows totalling $254,751 gross USD, the same as the Sprint 01 model.

Incremental extraction with a watermark

B · Objective 30 min · uses orders

The last successful load ran with a watermark of 2026-03-24. Extract only what changed since then, but remember that orders can still change status (cancellations) and get returns after they are first loaded.

Requirements
  • Rows after the watermark.
  • The same with a 7-day lookback, and why the lookback exists.
  • Where the watermark should be stored and when it should move.
Expected result: 51 orders after the watermark; 105 with a 7-day lookback (from 2026-03-17). The full table has 3,715.

The load that doubled March

C · Problem 40 min · uses order_lines

Last night's fact load failed half-way and was re-run. It uses INSERT INTO fact_sales SELECT … for the load date. The March total in the warehouse is now far too high. Make the load safe to run any number of times.

Work out
  • Show the failure mode with a small test (run the load twice).
  • Write an idempotent version (delete-and-insert for the batch, or insert-where-not-exists on the key, or MERGE).
  • Prove that running it twice leaves the same row count.
What good looks like: After two runs of the idempotent load the table has 7,215 rows, exactly as after one.

Interview questions

  • Why point Power BI at views instead of tables?
  • What is a watermark and why use a lookback?
  • What makes a load idempotent?
  • How does Power BI incremental refresh relate to this?

Assessment

A nightly load runs INSERT INTO fact SELECT … WHERE LoadDate = @d. It fails half-way and is re-run. What happens?

  • Nothing; databases prevent duplicates
  • The rows inserted before the failure are inserted again
  • The re-run updates the existing rows
  • The table is truncated first

Which column is the best watermark for extracting changed orders?

  • OrderDate
  • A LastModified timestamp maintained by the source system
  • OrderID
  • The load date of the BI table

Write the view for dim_customer with one row per customer, including a row for "Unknown" (key -1) that facts can point to when the customer is missing.

SELECT … FROM customers UNION ALL SELECT '-1', 'Unknown', … ; see the warehousing track for why the unknown member matters.

Making slow source queries fast

Data: SlowQuery NWOrders NWOrderLines NWReturns
Why it matters
Refresh time is usually source-query time. A 22-minute query makes every refresh slow, blocks incremental refresh and loads the source system everyone else uses.
Typical production failure
A function on a date column (YEAR(OrderDate) = 2026) forces a full scan of 72 million rows every night, and nobody notices until the refresh window overlaps the working day.
When to use it
Read the execution plan, make predicates sargable, remove implicit conversions, select only needed columns, replace row-by-row subqueries with joins or EXISTS, and index for the access path.
When not to
Adding indexes blindly: every index slows writes and costs storage. Fix the query first, then index for the remaining access pattern.

Assignments

The 22-minute query

C · Problem 60 min · uses slow_query.sql

The source query feeding FactSales now takes 22 minutes and the refresh window is at risk. Kenji sent the query and the plan summary (slow_query.sql). Investigate the query and its access pattern.

Work out
  • List every problem in the query and the plan, and what each one costs.
  • Write a corrected query that returns the same rows.
  • Suggest the index (or indexes) you would ask Kenji for, and why.
  • Describe the expected downstream impact on the Power BI refresh and on incremental refresh.
What good looks like: Run on the company pack, the original logic and your rewrite return the same 1,171 rows (raw lines of completed 2026 orders with no return). The rewrite uses a date range, matching join types, NOT EXISTS and only the needed columns.

Sargable or not?

B · Objective 20 min · uses —

An index exists on Orders(OrderDate) and another on Customers(Email). For each predicate decide whether SQL Server can seek on the index, and rewrite those that can't.

Requirements
  • WHERE YEAR(OrderDate) = 2026
  • WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
  • WHERE CONVERT(date, OrderDate) = '2026-03-31' (OrderDate is datetime2)
  • WHERE LOWER(Email) = 'ava.patel@example.com'
  • WHERE Email LIKE 'ava.%'
  • WHERE Email LIKE '%@example.com'
Expected result: Seekable: the date range and LIKE 'ava.%'. CONVERT(date, …) on a datetime2 column is a special case SQL Server can still seek on. Not seekable: YEAR(), LOWER() and a leading wildcard; rewrite the first two as ranges or with a computed column, and don't index the third.

SQL or Power Query?

D · Ambiguous 30 min · uses —

Elena asks where these transformations should live for the certified sales model: in the warehouse view, in Power Query, or in DAX. Decide and justify each, considering query folding, reuse and who maintains it.

Work out
  • Deduplicating order lines.
  • Converting EUR to USD at the monthly rate.
  • Splitting a full name into first and last name for a slicer.
  • Excluding the test customer.
  • A "days since last order" column per customer.
  • Formatting numbers as $1.2M on a card.
Deliver
  • A table: transformation, where, why, risk.
What good looks like: Warehouse view: deduplication, currency conversion and test-customer exclusion (shared rules, foldable, one place to fix). Power Query or the view: the name split if the source can't. DAX measure: days since last order (depends on filter context). Model formatting: the $1.2M display. At most two of the six belong in DAX.

Interview questions

  • What does "sargable" mean?
  • Why is an implicit conversion on a join column expensive?
  • Correlated subquery vs NOT EXISTS vs LEFT JOIN … IS NULL?
  • How does a slow source query affect Power BI beyond refresh time?

Assessment

Which predicate lets SQL Server seek on an index on Orders(OrderDate)?

  • YEAR(OrderDate) = 2026
  • OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'
  • DATEPART(month, OrderDate) = 3
  • CAST(OrderDate AS varchar(10)) LIKE '2026%'

A plan shows a Table Scan on Returns executed 41 million times. The likely cause:

  • Too few CPUs
  • A correlated subquery evaluated once per outer row
  • Statistics are up to date
  • The Returns table is small

In SQL Server, capture the actual execution plan of your rewritten query and compare logical reads (SET STATISTICS IO ON) with the original.

Look for scans turning into seeks on Orders(OrderDate), the disappearance of the per-row Returns scan, and fewer pages read.