2.4 — Create Views, Functions and Stored Procedures, and Enrich Data
DP-600 Domain 2 · Skill 2.4
What You Need to Know
T-SQL objects package logic so that it's reusable, secured and close to the data. Where you can create them depends on the item.
What you can create where
| Object | Warehouse | Lakehouse SQL analytics endpoint |
|---|---|---|
| Tables (CREATE, CTAS, INSERT, UPDATE, DELETE, MERGE) | ✅ | ❌ read-only. Create and change tables with Spark |
| Views | ✅ | ✅ |
| Inline table-valued functions | ✅ | ✅ |
| Scalar functions | ✅ (inlineable ones) | ✅ |
| Stored procedures | ✅ | ✅ (for read and logic over tables, not for writing to lakehouse tables) |
Views
CREATE VIEW gold.vw_SalesByMonth AS
SELECT d.YearMonth, p.Category, SUM(f.Amount) AS Amount
FROM gold.FactSales f
JOIN gold.DimDate d ON f.DateKey = d.DateKey
JOIN gold.DimProduct p ON f.ProductKey = p.ProductKey
GROUP BY d.YearMonth, p.Category;
- A view hides joins and logic, gives users a stable interface, and can be secured with
GRANT SELECTon the view alone. - Direct Lake can't read views directly. Direct Lake on SQL falls back to DirectQuery for a view. For Direct Lake performance, materialize the result as a table.
Functions
- An inline table-valued function returns a table and works like a parameterized view:
SELECT * FROM dbo.fn_SalesForRegion('EMEA'). It's also the required shape for RLS predicates. - Scalar functions return one value (for example a fiscal-year calculation) for reuse in queries.
Stored procedures
- Package multi-step load logic: staging to dimension (SCD), then facts, with error handling and transactions in a warehouse.
- Run them from a pipeline's Stored procedure or Script activity, passing parameters such as the load date.
CREATE PROCEDURE etl.usp_LoadFactSales @LoadDate date AS
BEGIN
BEGIN TRANSACTION;
DELETE FROM gold.FactSales WHERE DateKey = CONVERT(int, FORMAT(@LoadDate,'yyyyMMdd'));
INSERT INTO gold.FactSales (...) SELECT ... FROM silver.Sales WHERE SaleDate = @LoadDate;
COMMIT;
END;
Enrich data (new columns or tables)
| Need | T-SQL | PySpark | Power Query |
|---|---|---|---|
| Derived column | SELECT *, Qty * Price AS Revenue or ALTER TABLE ... ADD then UPDATE |
df.withColumn("Revenue", F.col("Qty")*F.col("Price")) |
Add custom column |
| Conditional bucket | CASE WHEN Amount > 1000 THEN 'Large' ELSE 'Small' END |
F.when(...).otherwise(...) |
Conditional column |
| Lookup attribute from another table | JOIN |
join |
Merge queries |
| New table (date dimension, mapping table) | CTAS / SELECT INTO |
saveAsTable |
New query with a destination |
Key Concept: Write in a warehouse. The lakehouse SQL endpoint is for reading, views, functions and procedures that read. Views give a stable, securable interface, but Direct Lake reads tables, not views.
Exam Traps
- Running INSERT or UPDATE against a lakehouse through its SQL analytics endpoint
- Building a Direct Lake model on views and expecting Direct Lake performance
- Implementing RLS with a stored procedure instead of an inline table-valued function
- Recomputing a derived column in every report instead of enriching it once in the data
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.
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.
Sources
- What is the SQL analytics endpoint for a lakehouse?, Microsoft Learn
- T-SQL surface area in Fabric Data Warehouse, Microsoft Learn
- Direct Lake overview, Microsoft Learn
- Stored procedure activity, Microsoft Learn