Views, Functions, Procedures and Enrichment

What it is

Views, functions and stored procedures are T-SQL objects that hold reusable logic. In Fabric you create them in a warehouse or in a lakehouse's SQL analytics endpoint.

Enriching data means adding information that the source does not have: a calculated column, a category, a lookup value from another table, or a whole new table such as a date dimension.

How it works

Which object to use

On a lakehouse's SQL analytics endpoint you can create views, functions and procedures, but none of them can modify the lakehouse tables. Data changes in T-SQL need a warehouse.

A view that also enriches

CREATE VIEW dbo.vSalesEnriched
AS
SELECT
    s.OrderID,
    s.OrderDate,
    s.Quantity * s.UnitPrice AS LineTotal,
    CASE WHEN s.Quantity >= 100 THEN 'Bulk' ELSE 'Standard' END AS OrderSize,
    c.Region
FROM dbo.Sales AS s
JOIN dbo.Customer AS c
    ON c.CustomerID = s.CustomerID;

The view adds a calculated column, a derived category and a region looked up from another table. The data is computed at query time, not stored.

An inline table-valued function

CREATE FUNCTION dbo.fn_SalesByRegion (@Region varchar(50))
RETURNS TABLE
AS
RETURN
    SELECT OrderID, OrderDate, LineTotal
    FROM dbo.vSalesEnriched
    WHERE Region = @Region;
GO

SELECT * FROM dbo.fn_SalesByRegion('West');

A stored procedure that loads a fact table

CREATE PROCEDURE dbo.usp_LoadDailySales
    @LoadDate date
AS
BEGIN
    DELETE FROM dbo.FactSales WHERE OrderDate = @LoadDate;

    INSERT INTO dbo.FactSales (OrderID, OrderDate, CustomerKey, LineTotal)
    SELECT s.OrderID, s.OrderDate, c.CustomerKey, s.Quantity * s.UnitPrice
    FROM stg.Sales AS s
    JOIN dbo.DimCustomer AS c
        ON c.CustomerID = s.CustomerID
    WHERE s.OrderDate = @LoadDate;
END;
GO

EXEC dbo.usp_LoadDailySales @LoadDate = '2026-10-01';

Deleting the day before inserting it makes the load safe to rerun. A data pipeline can call the procedure with the Stored procedure activity and pass the date as a parameter.

Querying across items

A warehouse can read other warehouses and lakehouse SQL analytics endpoints in the same workspace with three-part names. This is how a procedure loads a warehouse from a lakehouse:

INSERT INTO dbo.DimProduct (ProductID, ProductName, Category)
SELECT ProductID, ProductName, Category
FROM SalesLakehouse.dbo.products;

Enriching in Spark and Power Query

The same enrichment can happen earlier, in a notebook or a Dataflow Gen2. This PySpark code builds a new date table:

from pyspark.sql import functions as F

dates = spark.sql(
    "SELECT explode(sequence(to_date('2024-01-01'), to_date('2026-12-31'), interval 1 day)) AS Date"
)
dim_date = (dates
    .withColumn("Year", F.year("Date"))
    .withColumn("MonthNo", F.month("Date"))
    .withColumn("Month", F.date_format("Date", "MMM")))
dim_date.write.format("delta").mode("overwrite").saveAsTable("dim_date")

In Power Query, Add column > Custom column writes M such as:

Table.AddColumn(Source, "Margin", each [Revenue] - [Cost], type number)

Conditional columns and Column from examples add derived values without writing code.

A worked example

Report authors keep rewriting the same sales and customer join, and a nightly job must load yesterday's orders.

1. Create the view vSalesEnriched so authors query one object with friendly columns.
2. Create fn_SalesByRegion for regional teams that always filter on one region.
3. Create usp_LoadDailySales in the warehouse and schedule a pipeline that calls it each night with yesterday's date.

How the exam asks about this

• "Expose a simplified, joined dataset without storing another copy." A view.
• "Load and update tables with T-SQL on a schedule." A stored procedure in a warehouse, called from a data pipeline.
• "Add a column that groups orders into size bands." A CASE expression in a view or query, or a conditional column in Power Query.