Views, Stored Procedures, and Star Schemas in Lakehouses and Warehouses

Introduction

This lesson continues the domain Prepare data (45–50% of the DP-600 skills measured). This course targets the English syllabus effective October 19, 2026. Microsoft does not publish the question count or the question-type mix for the exam, so OnLevelUp mock exams use a 50-question practice length and an unofficial 80% practice target; neither is equivalent to the official 700/1000 scaled passing score.

Once data is in OneLake, someone must shape it into something analysts and semantic models can use. The exam asks you to decide whether logic belongs in a view, a function or a stored procedure, to design a star schema, to decide when to denormalize, and to enrich data with new columns or tables. After this lesson you will be able to create these objects in a warehouse and in a lakehouse SQL analytics endpoint, choose the right one for a scenario, design fact and dimension tables with surrogate keys and slowly changing dimensions, and explain the performance and governance consequences.

Learning objectives

• Create and choose between views, functions and stored procedures in a warehouse or a lakehouse SQL analytics endpoint.
• Design a star schema (facts, dimensions, grain, keys) for a lakehouse or a warehouse.
• Decide when and how to denormalize.
• Enrich data with calculated columns, lookup tables and a date dimension.
• Recognise what each engine supports (T-SQL write support, Delta tables, medallion layers).

1. What you can create where

The central rule: the SQL analytics endpoint of a lakehouse is read-only for table data, but you can still add objects such as views, functions, stored procedures that only read, and security rules. A warehouse supports full DML and multi-statement, multi-table transactions.

2. Views

A view is a stored SELECT statement. It stores no data and is evaluated at query time.

Why use a view:

• Abstraction: hide joins and renamed columns behind a stable contract.
• Security: expose a subset of columns or rows; combined with SQL permissions, a user may query the view but not the base table.
• Reuse: one definition used by many reports and semantic models.
• Compatibility: present business-friendly names to Power BI.

CREATE VIEW dbo.vw_SalesEnriched
AS
SELECT  s.OrderID,
        s.OrderDate,
        c.CustomerName,
        p.ProductName,
        s.Quantity * s.UnitPrice AS SalesAmount
FROM dbo.FactSales AS s
JOIN dbo.DimCustomer AS c ON c.CustomerKey = s.CustomerKey
JOIN dbo.DimProduct  AS p ON p.ProductKey  = s.ProductKey;

Points to remember:

• A standard view is not materialized. If the join is heavy, every query pays. Fabric Warehouse does not rely on indexed views the way SQL Server does; for heavy repeated logic, persist the result into a table with a stored procedure or a pipeline.
• A Direct Lake semantic model cannot use views as tables in the normal Direct Lake over OneLake path: a query on a view falls back to DirectQuery when the model uses the SQL analytics endpoint. Prefer physical Delta tables for Direct Lake.
• Views in a lakehouse endpoint read Delta tables written by Spark; new tables appear after metadata sync.

Example. Contoso wants sales analysts to see customer names but not the raw email column. The data engineer creates vw_CustomerPublic that omits Email, grants SELECT on the view, and does not grant SELECT on DimCustomer.

3. Functions

T-SQL user-defined functions return a scalar value or a table.

• Scalar function: returns one value, for example a fiscal year from a date.
• Inline table-valued function (iTVF): returns a table from a single SELECT; behaves like a parameterised view and optimizes well.
• Multi-statement table-valued functions are less efficient; prefer inline.

CREATE FUNCTION dbo.fn_SalesByRegion (@Region varchar(50))
RETURNS TABLE
AS
RETURN
(
    SELECT OrderDate, SUM(SalesAmount) AS Total
    FROM dbo.vw_SalesEnriched
    WHERE Region = @Region
    GROUP BY OrderDate
);

Use a function when you need parameterised, reusable logic that can be composed inside a SELECT. Use a view when no parameter is needed. Functions can also support row-level security predicates (the predicate function that a security policy applies).

4. Stored procedures

A stored procedure is a named, parameterised batch of T-SQL that can contain control flow, transactions and DML. In a warehouse it is the standard tool for repeatable transformation steps and loads.

CREATE PROCEDURE dbo.usp_LoadDimCustomer
AS
BEGIN
    -- Close changed rows (SCD type 2)
    UPDATE d
       SET d.IsCurrent = 0, d.ValidTo = SYSUTCDATETIME()
      FROM dbo.DimCustomer d
      JOIN stg.Customer s ON s.CustomerID = d.CustomerID
     WHERE d.IsCurrent = 1 AND s.City <> d.City;

    -- Insert new and changed rows
    INSERT INTO dbo.DimCustomer (CustomerID, CustomerName, City, ValidFrom, ValidTo, IsCurrent)
    SELECT s.CustomerID, s.CustomerName, s.City, SYSUTCDATETIME(), NULL, 1
      FROM stg.Customer s
      LEFT JOIN dbo.DimCustomer d
        ON d.CustomerID = s.CustomerID AND d.IsCurrent = 1
     WHERE d.CustomerID IS NULL;
END;

Typical uses: loading and merging dimensions, populating a fact table, truncating and reloading a staging schema, and being called by a pipeline Stored procedure activity or a Script activity. Benefits include a security boundary (users run EXEC without table rights), plan reuse and a single place for load logic.

CREATE TABLE AS SELECT (CTAS) and SELECT ... INTO materialise a query result in a warehouse; they are the answer when a heavy view must become a physical table.

Worked example 2: view, function or procedure? Statement. A retailer needs (1) a semantic-model-friendly object that joins sales to products, (2) the ability for a report to pass a region and get totals, and (3) a nightly job that rebuilds FactSales from staging and updates a watermark table, all inside one warehouse. Reasoning. (1) No parameter and no data change: a view. (2) A parameter with a SELECT result: an inline table-valued function. (3) Needs DML on two tables and ideally one transaction: a stored procedure called from a pipeline. Solution. Create vw_SalesProduct, fn_SalesByRegion and usp_LoadFactSales. Why other choices are worse. A view cannot accept a parameter or modify data; a function cannot perform DML on tables; a stored procedure cannot be selected from in a FROM clause, so it is awkward for reports.

5. Star schema

A star schema organises an analytical model around fact tables (measurable events) surrounded by dimension tables (descriptive context).

• Fact table: one row per event at a declared grain (for example one row per order line). Contains foreign keys to dimensions and numeric measures (quantity, amount). Additive measures can be summed across all dimensions; semi-additive (account balance) cannot be summed over time; non-additive (ratios) are recomputed.
• Dimension table: one row per entity (customer, product, date), with descriptive attributes used for filtering and grouping.
• Surrogate key: an artificial integer key in a dimension, independent of the source system's business key. It supports slowly changing dimensions and joins efficiently. Where an identity feature is not available or not suitable, surrogate keys are often generated with ROW_NUMBER() or hashing in a load procedure, or by Spark with monotonically_increasing_id() or window functions.
• Date dimension: one row per calendar day with year, quarter, month, week, fiscal calendar and holiday flags; essential for time intelligence later in the semantic model.

5.1 Slowly changing dimensions (SCD)

5.2 Why a star schema?

• Simple relationships (one-to-many from dimension to fact), fast aggregations.
• Matches the way Power BI engines compress and filter data. Direct Lake and Import models perform best with narrow fact tables and small dimensions.
• Avoids ambiguity: filters flow from dimension to fact.

5.3 Snowflake, and what to avoid

A snowflake schema normalises dimensions into sub-dimensions (Product → Subcategory → Category). It saves storage but adds joins and complexity. In Fabric, where storage is cheap and the semantic model prefers fewer joins, flatten into one dimension unless there is a good reason. Avoid one huge "flat file" table for everything: it duplicates descriptive text across millions of rows and makes the model harder to maintain.

Example. Adventure Works records line items. Grain: one row per order line. FactSales has DateKey, ProductKey, CustomerKey, Quantity, NetAmount. DimProduct carries ProductName, Subcategory, Category, Colour. DimDate is generated once for 2015–2035. Customer address changes are tracked with SCD type 2.

6. Designing in a lakehouse and in a warehouse

Medallion architecture organises layers: bronze (raw, as ingested), silver (cleansed, conformed), gold (modelled, business-ready star schema). Layers can be separate lakehouses, schemas or workspaces.

In a lakehouse, you build the star schema as Delta tables with Spark or Dataflows Gen2, then expose them to SQL through the endpoint and to Power BI through Direct Lake. Table maintenance such as OPTIMIZE (compacting small files, optionally with V-Order) and VACUUM (removing unreferenced files) keeps reads fast.

In a warehouse, you build tables with T-SQL (CTAS, INSERT, stored procedures). Warehouse tables are stored as Delta in OneLake, so Spark and shortcuts can read them as well. The warehouse is the right home when the team is SQL-centric and needs transactions.

7. Denormalization

Denormalization deliberately duplicates or flattens data to avoid joins and speed up reads. In analytics it is normal in the gold layer.

Good candidates: flattening a snowflaked product hierarchy into one product dimension; copying the customer segment onto each fact row when it is stable and filtered often; pre-aggregating a table at monthly grain for a dashboard that never needs daily detail; storing a few lookup attributes on the fact (degenerate dimensions such as an order number).

Costs: redundant storage, update anomalies when the source attribute changes, and larger fact tables. Rule: normalise to clean and conform (silver), denormalise to serve (gold).

Worked example 3: denormalize or not? Statement. DimProduct joins to DimSubcategory, which joins to DimCategory. The semantic model slicers filter by category and subcategory only. Query times are acceptable, but the model has three tables and a confusing field list. Product categories change at most once a year. Reasoning. The hierarchy is small and stable, and users only need the descriptive names. Flattening removes two relationships and two tables without meaningful redundancy cost. Solution. Create DimProduct containing ProductName, Subcategory, Category through a view or a transformation, and relate it directly to the fact. Why not keep the snowflake. It adds joins and relationships with no benefit. Why not put category on the fact table. It would repeat text millions of times and make updates costly.

8. Enriching data with new columns or tables

Enrichment adds value to existing data.

• Derived columns: SalesAmount = Quantity * UnitPrice, OrderYear, IsLateDelivery computed in a view, a CTAS, a dataflow or Spark (withColumn).
• Lookup/reference tables: map country codes to regions, SKU to product family, or postal code to sales territory; load them from a file or a shortcut and join in the gold layer.
• Date and time dimensions: generate with a recursive CTE, a numbers table or Spark.
• Bridge and mapping tables for many-to-many relationships (customers and accounts).
• External enrichment: shortcuts to another domain's tables, or a join to master data.

Where to compute? Compute as early as it is stable and reusable: in the warehouse or lakehouse rather than in each report. Keep volatile, context-dependent logic (YTD, ratios) as DAX measures in the semantic model, because they depend on filters.

from pyspark.sql import functions as F
df = spark.read.table("silver.sales")
gold = (df.withColumn("SalesAmount", F.col("Quantity") * F.col("UnitPrice"))
          .withColumn("OrderYear", F.year("OrderDate")))
gold.write.mode("overwrite").format("delta").saveAsTable("gold.FactSales")

Comparison: choosing where logic lives

Common pitfalls

• Trying to run INSERT or UPDATE through a lakehouse SQL analytics endpoint.
• Using a snowflake schema in the gold layer without a reason.
• Declaring no grain, so facts mix levels (order header and order line) and totals double count.
• Natural keys in dimensions when history must be tracked.
• Believing a standard view stores data or improves performance by itself.
• Using views as sources for Direct Lake and not noticing DirectQuery fallback.
• Putting row-dependent ratios in a column instead of a measure.

Exam tip: read verbs closely. "Parameter" suggests a function, "modify or load" suggests a stored procedure, "reuse without storing" suggests a view, and "read-only endpoint" rules out write operations.

Mini case study

Litware Hardware has a bronze lakehouse with raw orders, order lines, customers and products from three regional systems. The finance team knows T-SQL, not Spark. Requirements: a trusted star schema, history of customer region changes, no direct access to the email column for analysts, and a refresh every night.

Proposed solution.

1. Build silver tables in the lakehouse with Dataflows Gen2 or a notebook (cleansing is the next lesson).
2. Create a warehouse for gold. Reach silver tables with cross-database queries or shortcuts.
3. Model FactSales at order-line grain, DimCustomer (SCD type 2), DimProduct (flattened category hierarchy) and DimDate.
4. Write usp_LoadDimCustomer and usp_LoadFactSales and call them from a pipeline after the silver load.
5. Create vw_CustomerPublic without the email column; grant SELECT on the view only.
6. Offer fn_SalesByRegion for ad hoc parameterised analysis.

Commentary. The warehouse is chosen because the team writes T-SQL and needs DML; the views provide the security boundary; the SCD type 2 procedure preserves history; denormalised product data keeps the model a clean star.

Key terms

• View: stored query, no data.
• Inline table-valued function: parameterised view-like query.
• Stored procedure: parameterised T-SQL batch that can modify data.
• CTAS: create a table from a query result.
• Star schema: facts surrounded by dimensions.
• Grain: what one fact row represents.
• Surrogate key: artificial dimension key.
• SCD type 2: history kept as new rows.
• Denormalization: flattening to reduce joins.
• Medallion architecture: bronze, silver, gold layers.

Summary checklist

• I know which objects a lakehouse endpoint and a warehouse can create and which can modify data.
• I can pick between a view, a function and a stored procedure.
• I can design a star schema with a declared grain, surrogate keys and a date dimension.
• I can explain SCD types 1, 2 and 3.
• I can decide when to denormalize and when to avoid it.
• I can say where enrichment belongs: data layer or DAX measure.
• I remember that the practice length (50 questions) and 80% target are not official, and that the course targets the syllabus effective October 19, 2026.

Official sources

• Study guide for Exam DP-600: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-600 (opens in a new tab)
• Data warehousing in Microsoft Fabric: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-warehousing (opens in a new tab)
• Tables in a Fabric warehouse (dimensional modelling): https://learn.microsoft.com/en-us/fabric/data-warehouse/dimensional-modeling-overview (opens in a new tab)
• Lakehouse SQL analytics endpoint: https://learn.microsoft.com/en-us/fabric/data-warehouse/data-warehousing#sql-analytics-endpoint-of-the-lakehouse (opens in a new tab)