All 35 lessons
Start here: cloud, data, Fabric and Power BI basics
- 1. Start here: the exam, the plan and what you already know
- 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
- 3. Data basics: files, tables, formats and the lakehouse idea
- 4. A tour of Microsoft Fabric: OneLake, workspaces and items
- 5. Power BI in plain words: semantic models, reports and DAX
Prepare data: get data in (exam area 2, 45-50%)
- 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
- 7. Choosing a data store: lakehouse, warehouse or eventhouse
- 8. Use data without copying: shortcuts, mirroring and OneLake integration
- 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
- 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
- 11. Messy data: duplicates, missing values and nulls
Prepare data: transform and query (exam area 2)
Design and build semantic models (exam area 3, 25-30%)
- 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
- 19. Relationships: star schemas, bridge tables and many-to-many
- 20. DAX foundations: measures, filter context, CALCULATE and variables
- 21. DAX functions the exam tests: iterators, table filters, windows and information functions
- 22. Calculation groups, dynamic format strings and field parameters
- 23. Composite models and large semantic model storage format
Optimize models, then secure them (exam areas 3 and 1)
- 24. Direct Lake in depth: fallback, refresh and the two flavors
- 25. Incremental refresh for semantic models
- 26. Faster reports and DAX: finding and fixing slow queries
- 27. Who can see what: workspace roles and item permissions
- 28. Fine-grained security: rows, columns, objects and files
- 29. Governance: sensitivity labels and endorsement
Lifecycle, then exam practice (exam area 1)
22 min
Views, functions and stored procedures in T-SQL
Create views, functions and stored procedures in a warehouse and know what the SQL analytics endpoint allows.
By the end of this lesson you can
- Write a view and use it as a shared, secure query
- Write an inline table-valued function and a scalar function with parameters
- Write a stored procedure for a repeatable load and run it from a pipeline
- Say what you can create on a Warehouse versus a SQL analytics endpoint
From earlier lessons · 1 of 2
Your team writes only T-SQL and must run INSERT, UPDATE and MERGE on tables. Which Fabric item fits?
Pick one answer.
From earlier lessons · 2 of 2
A Dataflow Gen2 query has exact repeat rows from the till. Which Power Query step removes them?
Pick one answer.
At Juniper Coffee, finance and three regional analysts each wrote their own revenue by store query. On Monday, North showed three different totals in three meetings. The nightly summary load is a 40-line script that someone pastes into the Warehouse by hand. You need one shared definition, and a load that runs itself.
The data in this lesson. Two tiny Juniper Coffee tables in a Warehouse. Every result below is traced from these rows.
dbo.Stores
| StoreID | Region | StoreName. |
|---|---|---|
| 1 | North | Harbour St. |
| 2 | South | Mill Lane. |
| 3 | North | Station Rd. |
dbo.Sales
| SaleID | StoreID | SaleDate | Qty | Amount | Product. |
|---|---|---|---|---|---|
| 101 | 1 | 2026-11-02 | 2 | 9.00 | Latte. |
| 102 | 2 | 2026-11-02 | 1 | 4.80 | Mocha. |
| 103 | 3 | 2026-11-03 | 1 | 4.50 | Latte. |
| 104 | 1 | 2026-11-03 | 3 | 7.50 | Scone. |
Views: one shared query with a name
Exam depth: know it cold.
A view is a saved SELECT statement with a name. It stores no rows. Each time someone queries the view, the engine runs its SELECT on the current data.
Views give you two things.
- Reuse: write the join and the business rules once. Every report and analyst then reads the same numbers.
- A security layer: grant
SELECTon the view, not on the tables under it. Users see only the columns and rows the view returns. This is object-level security withGRANT.
CREATE VIEW fails if the view already exists. CREATE OR ALTER VIEW creates it, or replaces its definition, in one statement. Altering a view keeps the permissions granted on it. Dropping and recreating it loses them.
CREATE OR ALTER VIEW dbo.vw_StoreRevenueASSELECT st.Region, st.StoreName, SUM(s.Amount) AS RevenueFROM dbo.Sales AS sJOIN dbo.Stores AS st ON s.StoreID = st.StoreIDGROUP BY st.Region, st.StoreName;GOGRANT SELECT ON dbo.vw_StoreRevenue TO [Finance Analysts];
You are an analytics engineer at Juniper Coffee. Finance needs revenue by store from the Warehouse. They must not see the raw till rows in dbo.Sales, and the totals must always be current. You need the least privilege setup. What should you do?
Pick one answer.
Functions: inline table-valued and scalar
Exam depth: know it cold.
A user-defined function is a saved routine that takes parameters, values you pass in, and returns a result. In Fabric Warehouse and the SQL analytics endpoint, CREATE FUNCTION makes two kinds.
- Inline table-valued function (inline TVF): its body is one
SELECT, and it returns a table. You call it inFROM, so it works like a view that takes parameters. - Scalar function: returns one value, such as a number or a label. You call it in
SELECTorWHERE, with its schema name:dbo.fn_SizeBand(Qty). Inline TVFs are generally available. Scalar functions are still listed as a preview feature.
Multi-statement table-valued functions, common in SQL Server, aren't among them.
Keep scalar functions simple. Fabric folds each one into the calling query, called inlining. By default, CREATE FUNCTION rejects a scalar function it can't inline. WITH INLINE = AUTO creates it anyway, but it still can't be used in a query over a table.
CREATE OR ALTER FUNCTION dbo.fn_RegionSales (@Region varchar(10))RETURNS TABLEASRETURN (SELECT s.SaleID, s.SaleDate, s.AmountFROM dbo.Sales AS sJOIN dbo.Stores AS st ON s.StoreID = st.StoreIDWHERE st.Region = @Region);GOSELECT * FROM dbo.fn_RegionSales('North');
CREATE OR ALTER FUNCTION dbo.fn_SizeBand (@Qty int)RETURNS varchar(10)ASBEGINRETURN CASE WHEN @Qty >= 3 THEN 'Large' ELSE 'Small' END;END;GOSELECT SaleID, Qty, dbo.fn_SizeBand(Qty) AS BandFROM dbo.Sales;
You are a BI developer at Juniper Coffee. Each regional report needs the sales rows for one region, with the region passed in at query time. The result must join to other tables in FROM. What should you create in the Warehouse?
Pick one answer.
Stored procedures: repeatable steps with parameters
Exam depth: know it cold.
A stored procedure is a saved batch of T-SQL statements with a name. It can take parameters, run several statements in order, and change data. In a Warehouse that includes INSERT, UPDATE, DELETE, MERGE and CREATE TABLE.
You run it with EXEC, short for EXECUTE. You can't use a procedure in FROM, because it isn't a table.
Procedures suit repeatable loads: the same steps every night with a different date. A parameter can have a default value, such as @LoadDate date = NULL. An OUTPUT parameter hands a value back to the caller.
CREATE OR ALTER PROCEDURE works in Fabric, as it does for views and functions.
CREATE OR ALTER PROCEDURE dbo.usp_LoadDailySales@LoadDate dateASBEGINDELETE FROM dbo.DailySales WHERE SaleDate = @LoadDate;INSERT INTO dbo.DailySales (SaleDate, StoreID, Revenue)SELECT SaleDate, StoreID, SUM(Amount)FROM dbo.SalesWHERE SaleDate = @LoadDateGROUP BY SaleDate, StoreID;END;
Run it on a schedule from a pipeline. A Pipeline (older name: Data pipeline) is Fabric's item for ordering and scheduling steps. Its Stored procedure activity runs a procedure in a Fabric Warehouse or another SQL database.
- Add the Stored procedure activity to the pipeline canvas.
- On the Settings tab, choose the Warehouse connection and the procedure name.
- Expand Stored procedure parameters and select Import. Give each parameter a value: typed text, or an expression such as yesterday's date.
- Save, then select Run, or Schedule the pipeline.
The pipeline adds what a pasted script lacks: a schedule, run history and retries.
Choose: view, function or procedure
Exam depth: know it cold.
| View | Inline TVF | Scalar function | Stored procedure. | |
|---|---|---|---|---|
| Takes parameters | No | Yes | Yes | Yes. |
| Returns | A table | A table | One value | Result sets, or nothing. |
| You call it in | FROM | FROM | SELECT, WHERE | EXEC. |
| Changes data | No | No | No | Yes, in a Warehouse. |
| Typical use | Shared logic, security | Rows for a passed value | A reusable calculation | A repeatable load. |
Decision rule: the same rows for everyone, a view. The same rows filtered by a value you pass in, an inline TVF. One value per row, a scalar function. Steps that change data, a stored procedure.
Match
Match each Juniper Coffee need to the object that fits
Pick an item on the left, then its match on the right.
What works where: Warehouse or SQL analytics endpoint
Exam depth: know it cold.
Every Lakehouse gets a SQL analytics endpoint: a read-only T-SQL view of its Delta tables. Spark, dataflows and pipelines write the tables; the endpoint can't.
| Warehouse | SQL analytics endpoint. | |
|---|---|---|
| Create, alter or drop tables, incl. CTAS | Yes | No. |
INSERT, UPDATE, DELETE, MERGE on tables | Yes | No. |
| Views | Yes | Yes. |
| Inline TVFs and scalar functions | Yes | Yes. |
| Stored procedures | Yes | Yes, but they can't change table data. |
Permissions with GRANT | Yes | Yes. |
Decision rule: anything that writes rows or makes tables needs a Warehouse. Views, functions and read-only procedures work on both.
You are an analytics engineer at Juniper Coffee. Loyalty data sits in a Lakehouse. An analyst connects to its SQL analytics endpoint and must add reusable T-SQL logic there, with no new copies of data. Which object can she create on the endpoint?
Pick one answer.
Cross-database queries in one workspace
Exam depth: recognize it.
A cross-database query reads tables from another Warehouse, SQL analytics endpoint or mirrored database in a single query. You name them with a three-part name: item.schema.table. Nothing gets copied.
Example from the Warehouse: SELECT * FROM JuniperLakehouse.dbo.loyalty_scans AS ls JOIN dbo.Stores AS st ON ls.store_id = st.StoreID.
Two rules. The other item must be in the same workspace, added to Explorer with + Warehouses. For another workspace, create a shortcut in a Lakehouse first, then query through it.
Try it in Fabric
On a free trial, in a Warehouse that holds a small table or two:
- On the Home ribbon, select New SQL query.
- Paste a
CREATE OR ALTER PROCEDUREstatement, such as the load above, and select Run. - In Explorer, expand your schema. The procedure appears in the Stored Procedures folder; views and functions have their own folders.
- Open a new query, type
EXECwith the procedure name and its parameter, and select Run. - If a new table doesn't show, select ... next to Tables, then Refresh.
Exam-style practice
You are an analytics engineer at Juniper Coffee. A teammate runs SELECT * FROM dbo.usp_LoadDailySales('2026-11-03'); in the Warehouse and gets an error. They need the procedure's load to run for that date. What should they do?
Pick one answer.
You are a BI developer at Juniper Coffee. Finance reads dbo.vw_StoreRevenue through a GRANT on the view. You must add a Qty column to it. Last month a teammate ran DROP VIEW and then CREATE VIEW, and finance lost access. How should you change the view?
Pick one answer.
You are an analytics engineer at Juniper Coffee. The nightly load already lives in dbo.usp_LoadDailySales, which takes @LoadDate. It must run at 2 a.m. for yesterday's date, with run history and retries, and the logic must not be rewritten. What should you use?
Pick one answer.
Worth keeping
- View: shared logic and a security layer; no parameters; always current.
- Inline TVF: rows for a value you pass in, used in FROM. Scalar function: one value per row.
- Stored procedure: steps that change data, run with EXEC or a pipeline's Stored procedure activity.
- Use CREATE OR ALTER so grants survive a change.
- SQL analytics endpoint: views, functions and read-only procedures; no table writes.
One revenue definition now feeds every report. But the tables under it are still raw till rows, with store names typed by hand. Next lesson you reshape them into facts and dimensions: a star schema.
Leaderboard
Loading…
50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.
Sources for this lesson (18)
Each fact in the lesson is marked with the number of the page it comes from.
- [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
- [139]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
- [140]Microsoft Learn: What is Fabric Data Warehouse?
- [141]Microsoft Learn: CREATE VIEW (Transact-SQL)
- [142]Microsoft Learn: ALTER VIEW (Transact-SQL)
- [143]Microsoft Learn: CREATE FUNCTION (Fabric Data Warehouse)
- [144]Microsoft Learn: Create scalar user-defined functions
- [145]Microsoft Learn: CREATE PROCEDURE (Transact-SQL)
- [146]Microsoft Learn: Stored procedures (Database Engine)
- [147]Microsoft Learn: Tutorial: Transform data with a stored procedure in a Warehouse
- [148]Microsoft Learn: Use the stored procedure activity
- [149]Microsoft Learn: Incrementally load data from Data Warehouse to Lakehouse
- [150]Microsoft Learn: SQL granular permissions in Microsoft Fabric
- [151]Microsoft Learn: Secure your Fabric Data Warehouse
- [152]Microsoft Learn: Column-level security in Fabric Data Warehouse
- [153]Microsoft Learn: Query the warehouse or SQL analytics endpoint
- [154]Microsoft Learn: Ingest from warehouse tables and mirrored databases with T-SQL
- [155]Microsoft Learn: What's new in Microsoft Fabric?