The Power BI Fellowship

Microsoft Fabric for BI developers Track

Fabric changes where data preparation happens and how semantic models read data. This track focuses on the decisions a BI developer makes in Fabric, not every Fabric workload. Fabric features change fast: each topic shows when it was last checked against Microsoft's documentation, and the CI flags it when a review is due. A Fabric trial covers every hands-on step; where you can't use one, the conceptual tasks and the Sprint 09 decision still apply.

Before you start A Fabric trial (or capacity) and a workspace you own. Upload the company pack CSVs to a lakehouse. No Spark knowledge is required: everything can be done with the UI, Dataflow Gen2 and T-SQL.

OneLake, Lakehouse, Warehouse and the SQL analytics endpoint

Data: NWOrders NWOrderLines NWCustomers NWProducts
Why it matters
Choosing the right store decides who can build on it (SQL people, Spark people, analysts), how security works and which Direct Lake option you can use.
Typical production failure
A team builds the gold layer as SQL views on a lakehouse endpoint, then discovers that Direct Lake on OneLake can't use non-materialised views and Direct Lake on SQL falls back to DirectQuery on every query.
When to use it
Lakehouse when data arrives as files and engineers use Spark or Dataflow Gen2; Warehouse when the team works in T-SQL and needs multi-table transactions; shortcuts to reference data without copying it.
When not to
Copying the same table into several lakehouses "to be safe": use shortcuts and one owner.

Assignments

Land the company pack in a lakehouse

A · Guided 40 min · uses company pack CSVs
  1. Create a workspace on the trial capacity and a lakehouse called nw_bronze.
  2. Upload the seven CSVs to Files, then Load to Tables (each becomes a Delta table).
  3. Open the SQL analytics endpoint and run the Q1 2026 by region query from the SQL track. Compare with the expected values there.
  4. Try an UPDATE through the SQL analytics endpoint and read the error.
Expected result: Seven Delta tables; the SQL query returns the same totals as in the SQL track (total $254,751); UPDATE fails because the SQL analytics endpoint is read-only.

Lakehouse or Warehouse for the gold layer?

C · Problem 25 min · uses —

Northwind's team knows T-SQL, not Spark (Sprint 09). Choose where the cleaned star schema (gold layer) lives and justify it against the constraints, including how it will be read by a Direct Lake model.

Work out
  • Team skills, transactions, security, how the semantic model reads it.
  • What you'd do with SQL views in the design.
What good looks like: A Fabric Warehouse for gold: T-SQL skills, multi-table transactions, materialised tables that Direct Lake (on OneLake or on SQL) reads natively. Views are fine for ad-hoc SQL users but shouldn't be the tables a Direct Lake model reads: materialise them.

Shortcut instead of copy

B · Objective 20 min · uses —

Finance has its own workspace and lakehouse, and wants the cleaned customer table owned by the sales data team. Give them access without copying it.

Requirements
  • Create a OneLake shortcut in Finance's lakehouse to the source table.
  • Explain who owns the data, who can change it, and how permissions apply.
Expected result: A shortcut in Finance's lakehouse points to the sales team's table; data isn't duplicated; the sales team stays the owner; access requires permission on the source as well as on Finance's lakehouse.

Interview questions

  • What is OneLake?
  • Lakehouse vs Warehouse?
  • What is the SQL analytics endpoint?
  • What is a shortcut?

Assessment

Can you run UPDATE through a lakehouse's SQL analytics endpoint?

  • Yes
  • No: it's read-only
  • Only on views
  • Only as an admin

A team only knows T-SQL and needs multi-table transactions for its gold layer. Best fit:

  • Lakehouse with Spark notebooks
  • Fabric Warehouse
  • Excel files in OneLake
  • Eventhouse

Use the Fabric decision guide to classify Northwind's four workloads from Sprint 09 (sales, finance, ops, web) by store.

Consider data volume, latency, skills and who writes the data.

Direct Lake in depth: OneLake vs SQL endpoint, framing, fallback, guardrails

Why it matters
Direct Lake gives near-Import speed on large data without copying it at refresh, but the two Direct Lake options behave differently when something isn't supported: one falls back to slower DirectQuery, the other fails.
Typical production failure
A Direct Lake on SQL model silently runs as DirectQuery because a table is a SQL view; performance drops and nobody knows why.
When to use it
Direct Lake on OneLake for new models (multi-source, no fallback, can add Import tables); Direct Lake on SQL when you need SQL-endpoint features or a supported source only it handles; well-maintained Delta tables within the capacity guardrails.
When not to
Treating every Direct Lake model as identical, or relying on fallback in production.

Assignments

Compare the two Direct Lake options

B · Objective 30 min · uses —

Write the one-page comparison you'd give a colleague choosing between Direct Lake on OneLake and Direct Lake on SQL analytics endpoint.

Requirements
  • Sources each can combine; fallback behaviour; SQL views; SQL-endpoint security; composite models; calculated columns; what happens when guardrails are exceeded.
Expected result: OneLake: one or more Fabric sources, can add Import tables, no DirectQuery fallback (errors instead), no non-materialised views, SQL-endpoint RLS/OLS not applied; recommended for new models. SQL: one source via its SQL analytics endpoint, falls back to DirectQuery for views, SQL-endpoint RLS/OLS and exceeded guardrails (controlled by Direct Lake behavior), can't mix with DirectQuery/Dual tables in the same model. Both: Fabric capacity only.

Prove which mode each table uses

B · Objective 30 min · uses your lakehouse from the previous topic

Build a Direct Lake model on the company pack and prove, table by table, whether queries are served by Direct Lake.

Requirements
  • Run EVALUATE TABLETRAITS() and read DirectLakeFallbackInfo.
  • Change Direct Lake behavior to DirectLakeOnly (SQL option) and observe.
  • Disable automatic updates, change data, and show the model still returns the old data until you reframe.
Expected result: TABLETRAITS shows None for each Delta table; a SQL view (SQL option) shows a fallback reason and fails under DirectLakeOnly; with automatic updates off, new data appears only after a refresh (framing).

RLS for a Direct Lake model

C · Problem 25 min · uses —

Northwind needs regional RLS on the Direct Lake sales model (Sprint 09 phase 2). The data engineers propose defining RLS in the SQL analytics endpoint. Decide where RLS goes and why.

Work out
  • Consequences for each Direct Lake option.
  • The recommended connection identity.
What good looks like: Define RLS in the semantic model. SQL-endpoint RLS isn't applied in Direct Lake on OneLake and forces DirectQuery fallback in Direct Lake on SQL. Microsoft strongly recommends a fixed-identity cloud connection for Direct Lake models with semantic-model RLS.

Interview questions

  • What is framing in Direct Lake?
  • When does Direct Lake on SQL fall back to DirectQuery?
  • Why might Direct Lake on OneLake be preferred for new models?
  • What are Direct Lake guardrails?

Assessment

A table in a Direct Lake on OneLake model exceeds the capacity's row-group guardrail. Queries:

  • Fall back to DirectQuery
  • Fail (refresh fails and the model can't be queried until fixed)
  • Use Import automatically
  • Ignore the guardrail

Which property controls fallback in Direct Lake on SQL?

  • Storage mode
  • Direct Lake behavior (Automatic, DirectLakeOnly, DirectQueryOnly)
  • Query folding
  • Large model format

Write the runbook entry "Direct Lake report is suddenly slow".

Check TABLETRAITS for fallback, Delta table file and row-group counts against guardrails, capacity memory pressure, and whether framing is current.

Dataflow Gen2, pipelines, mirroring and medallion design

Data: RawOrdersExport NWOrderLines
Why it matters
In Fabric, data preparation moves out of each semantic model into shared, scheduled layers. That's what makes one definition possible for many models.
Typical production failure
Five models each clean the same export with slightly different Power Query steps, and five "Revenue" numbers appear (Sprint 05, again).
When to use it
Bronze (raw, as landed), silver (cleaned, conformed), gold (star schema for models); Dataflow Gen2 for Power Query-style transformations, pipelines to orchestrate, mirroring to replicate operational databases continuously.
When not to
Medallion layers for their own sake on small data. Two layers are fine if they're clearly owned.

Assignments

Move the clean-up out of the model

B · Objective 40 min · uses RawOrdersExport (course dataset)

Turn the Beginner RawOrdersExport clean-up into a Dataflow Gen2 that writes a silver table, so every model reads the same cleaned data.

Requirements
  • Same rules as Beginner (header and total rows, dates, prices, duplicates).
  • Output destination: a lakehouse or warehouse table, with an explicit schema.
  • Refresh on a schedule; show two models reading the same table.
Expected result: A silver table with 27 rows and 8 columns, refreshed by the dataflow; both models read it, so a fix in the dataflow fixes both.

Design the medallion layers for Northwind

C · Problem 30 min · uses —

Following ADR-009 (Sprint 09), design bronze, silver and gold for the sales data: what lands where, which tool writes each layer, who owns it, and which data-quality checks run between layers.

Work out
  • Mirroring or pipelines for the operational database.
  • Where deduplication, currency conversion and the test-customer rule live.
  • The gold tables the semantic model reads.
What good looks like: Bronze: mirrored NorthwindDW and landed files (owner: data engineering). Silver: deduplicated, typed, conformed tables with DQ tests (dedup, test customer, FX). Gold: star schema materialised in a Warehouse (FactSales, FactReturns, dimensions), read by a Direct Lake on OneLake model. Tests block promotion from silver to gold.

Orchestrate: load, test, frame, notify

B · Objective 30 min · uses —

Build (or design) a pipeline that runs the nightly sequence so the semantic model only shows data that passed its tests.

Requirements
  • Steps: refresh the dataflow or wait for mirroring, run data-quality checks, load gold, refresh (frame) the semantic model, run model tests, notify.
  • What happens when a step fails.
Expected result: A pipeline: Dataflow → stored procedure DQ checks (fail stops the run) → gold load → semantic model refresh activity → test query → notify team channel; failures stop downstream steps and alert, so users keep seeing the last good data.

Interview questions

  • Dataflow Gen2 vs Power Query in the model?
  • What does mirroring do?
  • Explain the medallion architecture.
  • Why orchestrate refresh in a pipeline rather than schedule each item?

Assessment

Five models clean the same export differently. Best fix in Fabric:

  • Copy the best query into all five
  • One Dataflow Gen2 writing a shared table that all five read
  • DirectQuery to the CSV
  • Calculated columns

Mirroring an on-premises SQL Server into Fabric requires:

  • Nothing
  • A supported SQL Server version and a data gateway (on-premises or VNet) for connectivity
  • A Spark notebook
  • Premium per user

Draw the Northwind data flow from source to report on one page, marking owners and test points.

NorthwindDW → mirror (bronze) → silver tables → gold warehouse → Direct Lake model → reports; tests at silver→gold and after model refresh.