Launch Deal · 50% off 1st month ·

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 SELECT on 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.

See plans

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.