Implementing Fabric Warehouse Stored Procedures and Cross-Database Views: Encapsulating Business Logic and Joining Lakehouse SQL Endpoint Tables with Warehouse Tables Using T-SQL

Introduction

Here's a scenario that plays out constantly in real Fabric implementations: your data engineering team is ingesting raw sales transactions into a lakehouse, running PySpark notebooks to cleanse and conform the data into Delta tables, and your analytics team wants to query that conformed data alongside the curated product catalog and territory hierarchy that lives in a Fabric Warehouse. Two different stores. Two different access patterns. One business question.

The naive solution is to copy data from the lakehouse into the warehouse (or vice versa) so everything lives in one place. But that creates duplication, adds pipeline steps, and breaks the principle that OneLake is supposed to be your single source of truth. The elegant solution — and the one you'll master in this lesson — is to query across those boundaries using T-SQL, wrapping the joins in stored procedures and cross-database views so that consuming applications, analysts, and Power BI reports see a clean, stable interface instead of the mechanical details underneath.

By the end of this lesson, you'll know how to write stored procedures in a Fabric Warehouse that encapsulate business logic like revenue rollup calculations and return period comparisons, how to create views that join lakehouse SQL endpoint tables with warehouse tables using three-part naming, and how to reason about the tradeoffs of each approach so you can make defensible architectural decisions in production.

What you'll learn:

• How the Fabric Warehouse SQL endpoint and the Lakehouse SQL Analytics Endpoint relate to each other and why three-part naming works across them
• How to create stored procedures in a Fabric Warehouse that encapsulate multi-step transformation logic with parameters
• How to build cross-database views that join lakehouse Delta tables with warehouse dimension tables without copying data
• How to handle common pitfalls like read-only endpoints, permission boundaries, and query folding limitations
• When to use a stored procedure versus a view versus a materialized copy, and why that choice matters for performance and maintainability

---

Prerequisites

You should be comfortable with:

• Writing T-SQL — JOINs, CTEs, window functions, and basic DDL
• The difference between a Fabric Lakehouse and a Fabric Warehouse (see Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload if you need a refresher)
• Navigating the Fabric workspace and opening query editors
• The concept of a SQL Analytics Endpoint — if this is new, read Querying Lakehouse Data with the SQL Analytics Endpoint: Writing T-SQL Against Delta Tables Without a Warehouse first

You'll also benefit from having worked through Building a Fabric Data Warehouse with T-SQL: Tables, Views, and Cross-Database Queries, since we'll extend those patterns here.

---

Understanding the Architecture: Two Endpoints, One Fabric

Before writing a line of T-SQL, let's be precise about what you're connecting.

When you create a lakehouse in Fabric, you get two ways to interact with it. The first is the lakehouse explorer — the Spark-native, file-and-table interface for notebooks and dataflows. The second is the SQL Analytics Endpoint, which is a read-only SQL server endpoint automatically provisioned alongside every lakehouse. It exposes every Delta table in the lakehouse's Tables/ folder as a queryable SQL object using T-SQL semantics.

A Fabric Warehouse, by contrast, is a fully read-write SQL engine. You can run DDL, create stored procedures, define views, and do everything you'd expect from a traditional SQL Server-style data warehouse — with significant differences in how compute and storage work under the hood.

The critical architectural fact for this lesson is this: both the Fabric Warehouse and the Lakehouse SQL Analytics Endpoint are reachable from each other's query editor using three-part naming, as long as they're in the same Fabric workspace and you have the appropriate permissions. The syntax looks like this:

-- From within the Warehouse query editor:
SELECT *
FROM [LakehouseName].[schema].[table_name]

-- Or with the dbo schema explicitly:
SELECT *
FROM [SalesLakehouse].[dbo].[fact_transactions]

Under the hood, Fabric resolves [LakehouseName] as a reference to that lakehouse's SQL Analytics Endpoint. You're not copying data — you're federated-querying across endpoints. This is similar in spirit to linked servers in traditional SQL Server, but far simpler to configure because Fabric handles the connection management for you.

This distinction shapes everything. Your stored procedures will read from the lakehouse endpoint and write (if needed) to warehouse tables. Your views will join across both. The lakehouse is the source; the warehouse is the logic layer.

---

Setting Up the Scenario: Retail Sales Analytics

Throughout this lesson, we'll work with a realistic retail analytics scenario. Here's the data estate:

In the Sales Lakehouse (Delta tables, managed via Spark notebooks):

• fact_transactions — raw-to-conformed sales transaction records (500M+ rows, partitioned by transaction date)
• dim_customer — customer dimension built from CRM exports

In the Sales Warehouse (Fabric Warehouse tables):

• dim_product — product catalog with cost data, managed by a data team that updates it via pipeline COPY INTO
• dim_territory — regional hierarchy with territory managers
• dim_date — date dimension with fiscal calendar attributes
• agg_daily_sales — a pre-aggregated summary table that stored procedures will write into

This split is intentional and common. The lakehouse holds high-volume, Spark-processed data. The warehouse holds curated reference dimensions and aggregation targets. If you're following the Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers, think of the warehouse as your gold layer presentation surface for the reference data, while your lakehouse holds the gold-layer facts.

Let's create the warehouse tables first so you have something to work with:

-- Run this in the Sales Warehouse query editor

CREATE TABLE dbo.dim_product (
    product_key        INT           NOT NULL,
    product_id         VARCHAR(20)   NOT NULL,
    product_name       NVARCHAR(200) NOT NULL,
    category           NVARCHAR(100) NOT NULL,
    subcategory        NVARCHAR(100) NOT NULL,
    unit_cost          DECIMAL(10,2) NOT NULL,
    standard_price     DECIMAL(10,2) NOT NULL,
    is_active          BIT           NOT NULL DEFAULT 1,
    effective_date     DATE          NOT NULL,
    expiry_date        DATE          NULL
);

CREATE TABLE dbo.dim_territory (
    territory_key      INT           NOT NULL,
    territory_code     VARCHAR(10)   NOT NULL,
    territory_name     NVARCHAR(100) NOT NULL,
    region             NVARCHAR(50)  NOT NULL,
    country            NVARCHAR(50)  NOT NULL,
    territory_manager  NVARCHAR(100) NOT NULL
);

CREATE TABLE dbo.dim_date (
    date_key           INT           NOT NULL,
    calendar_date      DATE          NOT NULL,
    year               INT           NOT NULL,
    quarter            INT           NOT NULL,
    month              INT           NOT NULL,
    month_name         NVARCHAR(20)  NOT NULL,
    fiscal_year        INT           NOT NULL,
    fiscal_quarter     INT           NOT NULL,
    fiscal_period      INT           NOT NULL,
    is_weekend         BIT           NOT NULL
);

CREATE TABLE dbo.agg_daily_sales (
    agg_date           DATE          NOT NULL,
    territory_key      INT           NOT NULL,
    category           NVARCHAR(100) NOT NULL,
    total_revenue      DECIMAL(18,2) NOT NULL,
    total_cost         DECIMAL(18,2) NOT NULL,
    total_margin       DECIMAL(18,2) NOT NULL,
    transaction_count  INT           NOT NULL,
    load_timestamp     DATETIME2     NOT NULL DEFAULT SYSDATETIME()
);

---

Building Cross-Database Views

A view in the Fabric Warehouse that references the lakehouse SQL endpoint gives your consumers a stable SQL interface. When the lakehouse schema evolves — columns added, table renamed — you update the view, not every downstream report and query.

Let's build a view that joins the lakehouse's fact_transactions and dim_customer with the warehouse's dim_product, dim_territory, and dim_date:

CREATE OR ALTER VIEW dbo.vw_sales_detail
AS
SELECT
    -- Transaction identifiers
    t.transaction_id,
    t.transaction_date,
    dd.fiscal_year,
    dd.fiscal_quarter,
    dd.fiscal_period,
    dd.month_name,

    -- Customer attributes from lakehouse
    c.customer_id,
    c.customer_name,
    c.customer_segment,
    c.acquisition_channel,

    -- Product attributes from warehouse
    p.product_id,
    p.product_name,
    p.category,
    p.subcategory,

    -- Territory attributes from warehouse
    tr.territory_name,
    tr.region,
    tr.country,
    tr.territory_manager,

    -- Financial measures
    t.quantity,
    t.unit_price,
    t.discount_amount,
    t.gross_revenue,
    t.net_revenue,
    p.unit_cost,
    (t.quantity * p.unit_cost)              AS total_cost,
    (t.net_revenue - (t.quantity * p.unit_cost)) AS gross_margin,
    CASE
        WHEN t.net_revenue = 0 THEN 0
        ELSE ROUND(
            (t.net_revenue - (t.quantity * p.unit_cost)) / t.net_revenue * 100,
            2
        )
    END AS margin_pct

FROM
    -- Lakehouse SQL endpoint tables (read-only, three-part naming)
    [SalesLakehouse].[dbo].[fact_transactions]      AS t

    INNER JOIN [SalesLakehouse].[dbo].[dim_customer] AS c
        ON t.customer_id = c.customer_id

    -- Warehouse dimension tables
    INNER JOIN dbo.dim_product                        AS p
        ON t.product_id = p.product_id
        AND p.expiry_date IS NULL   -- current SCD2 record only

    INNER JOIN dbo.dim_territory                      AS tr
        ON t.territory_code = tr.territory_code

    INNER JOIN dbo.dim_date                           AS dd
        ON CAST(t.transaction_date AS DATE) = dd.calendar_date
;

Now any query against dbo.vw_sales_detail in the warehouse query editor automatically reaches across into the lakehouse. A Power BI report pointed at the warehouse endpoint can query this view without knowing anything about how the data is split across two stores.

Adding Filtered and Aggregated Views

For reporting layers, it often makes sense to add intermediate views that pre-filter or pre-aggregate, so that Power BI or other consumers don't have to push large aggregations across the federated boundary on every refresh:

CREATE OR ALTER VIEW dbo.vw_sales_current_fiscal_year
AS
SELECT *
FROM dbo.vw_sales_detail
WHERE fiscal_year = (
    SELECT MAX(fiscal_year)
    FROM dbo.dim_date
    WHERE calendar_date <= CAST(GETDATE() AS DATE)
)
;

CREATE OR ALTER VIEW dbo.vw_sales_by_category_month
AS
SELECT
    fiscal_year,
    fiscal_quarter,
    fiscal_period,
    month_name,
    category,
    region,
    SUM(net_revenue)   AS total_revenue,
    SUM(total_cost)    AS total_cost,
    SUM(gross_margin)  AS total_margin,
    COUNT(*)           AS transaction_count,
    AVG(margin_pct)    AS avg_margin_pct
FROM dbo.vw_sales_detail
GROUP BY
    fiscal_year,
    fiscal_quarter,
    fiscal_period,
    month_name,
    category,
    region
;

---

Writing Stored Procedures for Business Logic

This is where Fabric Warehouse stored procedures earn their keep. A view is a saved SELECT — it re-executes the query every time it's referenced. A stored procedure is an executable unit that can run multi-step logic, accept parameters, perform conditional branching, write to target tables, and return structured result sets.

For our retail scenario, we'll implement three stored procedures:

1. A daily aggregation refresh that materializes sales summaries
2. A period comparison procedure that returns current vs. prior period metrics
3. A territory performance ranking procedure with dynamic territory filtering

Stored Procedure 1: Daily Aggregation Refresh

This procedure reads from the cross-database view, aggregates to the day/territory/category grain, and upserts into agg_daily_sales. Running it nightly keeps the aggregation table current without expensive on-demand federation queries:

CREATE OR ALTER PROCEDURE dbo.usp_refresh_daily_sales_agg
    @refresh_date   DATE = NULL,     -- NULL = yesterday
    @territory_code VARCHAR(10) = NULL  -- NULL = all territories
AS
BEGIN
    SET NOCOUNT ON;

    -- Default to yesterday if no date provided
    IF @refresh_date IS NULL
        SET @refresh_date = CAST(DATEADD(DAY, -1, GETDATE()) AS DATE);

    DECLARE @start_time DATETIME2 = SYSDATETIME();

    -- Remove existing rows for the date range being refreshed
    -- (idempotent: safe to re-run for same date)
    DELETE FROM dbo.agg_daily_sales
    WHERE agg_date = @refresh_date
      AND (
            @territory_code IS NULL
            OR territory_key IN (
                SELECT territory_key
                FROM dbo.dim_territory
                WHERE territory_code = @territory_code
            )
      );

    -- Insert fresh aggregation from the federated view
    INSERT INTO dbo.agg_daily_sales (
        agg_date,
        territory_key,
        category,
        total_revenue,
        total_cost,
        total_margin,
        transaction_count,
        load_timestamp
    )
    SELECT
        CAST(sd.transaction_date AS DATE)   AS agg_date,
        tr.territory_key,
        sd.category,
        SUM(sd.net_revenue)                 AS total_revenue,
        SUM(sd.total_cost)                  AS total_cost,
        SUM(sd.gross_margin)                AS total_margin,
        COUNT(*)                            AS transaction_count,
        SYSDATETIME()                       AS load_timestamp
    FROM dbo.vw_sales_detail AS sd
    INNER JOIN dbo.dim_territory AS tr
        ON sd.territory_name = tr.territory_name
    WHERE
        CAST(sd.transaction_date AS DATE) = @refresh_date
        AND (
            @territory_code IS NULL
            OR sd.territory_name IN (
                SELECT territory_name
                FROM dbo.dim_territory
                WHERE territory_code = @territory_code
            )
        )
    GROUP BY
        CAST(sd.transaction_date AS DATE),
        tr.territory_key,
        sd.category;

    DECLARE @rows_inserted INT = @@ROWCOUNT;

    -- Log the operation (useful for pipeline monitoring)
    PRINT CONCAT(
        'usp_refresh_daily_sales_agg completed. ',
        'Date: ', CONVERT(VARCHAR, @refresh_date, 23), ' | ',
        'Territory: ', ISNULL(@territory_code, 'ALL'), ' | ',
        'Rows inserted: ', @rows_inserted, ' | ',
        'Duration: ', DATEDIFF(MILLISECOND, @start_time, SYSDATETIME()), 'ms'
    );
END;

Execute it like this from the query editor, or from a Fabric data pipeline:

-- Refresh yesterday's data for all territories
EXEC dbo.usp_refresh_daily_sales_agg;

-- Refresh a specific historical date for one territory
EXEC dbo.usp_refresh_daily_sales_agg
    @refresh_date   = '2024-11-15',
    @territory_code = 'NE-US';

Stored Procedure 2: Period-over-Period Comparison

This procedure returns a result set that compares current and prior period performance — the kind of calculation that's painful to express in a DAX measure or a single view, but natural in procedural SQL:

CREATE OR ALTER PROCEDURE dbo.usp_period_comparison
    @fiscal_year     INT,
    @fiscal_period   INT,       -- 1-12 fiscal month
    @category        NVARCHAR(100) = NULL,  -- NULL = all categories
    @region          NVARCHAR(50)  = NULL   -- NULL = all regions
AS
BEGIN
    SET NOCOUNT ON;

    -- Determine prior period (handle fiscal year boundary)
    DECLARE @prior_year   INT = CASE WHEN @fiscal_period = 1 THEN @fiscal_year - 1 ELSE @fiscal_year END;
    DECLARE @prior_period INT = CASE WHEN @fiscal_period = 1 THEN 12 ELSE @fiscal_period - 1 END;

    WITH current_period AS (
        SELECT
            category,
            region,
            SUM(total_revenue)      AS revenue,
            SUM(total_cost)         AS cost,
            SUM(total_margin)       AS margin,
            SUM(transaction_count)  AS txn_count
        FROM dbo.agg_daily_sales AS agg
        INNER JOIN dbo.dim_territory AS tr
            ON agg.territory_key = tr.territory_key
        INNER JOIN dbo.dim_date AS dd
            ON agg.agg_date = dd.calendar_date
        WHERE
            dd.fiscal_year   = @fiscal_year
            AND dd.fiscal_period = @fiscal_period
            AND (@category IS NULL OR agg.category = @category)
            AND (@region   IS NULL OR tr.region    = @region)
        GROUP BY
            agg.category,
            tr.region
    ),
    prior_period AS (
        SELECT
            category,
            region,
            SUM(total_revenue)      AS revenue,
            SUM(total_cost)         AS cost,
            SUM(total_margin)       AS margin,
            SUM(transaction_count)  AS txn_count
        FROM dbo.agg_daily_sales AS agg
        INNER JOIN dbo.dim_territory AS tr
            ON agg.territory_key = tr.territory_key
        INNER JOIN dbo.dim_date AS dd
            ON agg.agg_date = dd.calendar_date
        WHERE
            dd.fiscal_year   = @prior_year
            AND dd.fiscal_period = @prior_period
            AND (@category IS NULL OR agg.category = @category)
            AND (@region   IS NULL OR tr.region    = @region)
        GROUP BY
            agg.category,
            tr.region
    )
    SELECT
        COALESCE(cp.category, pp.category)  AS category,
        COALESCE(cp.region,   pp.region)    AS region,

        -- Current period
        ISNULL(cp.revenue,   0)             AS current_revenue,
        ISNULL(cp.cost,      0)             AS current_cost,
        ISNULL(cp.margin,    0)             AS current_margin,
        ISNULL(cp.txn_count, 0)             AS current_txn_count,

        -- Prior period
        ISNULL(pp.revenue,   0)             AS prior_revenue,
        ISNULL(pp.margin,    0)             AS prior_margin,
        ISNULL(pp.txn_count, 0)             AS prior_txn_count,

        -- Variance calculations
        ISNULL(cp.revenue, 0) - ISNULL(pp.revenue, 0)   AS revenue_variance,
        CASE
            WHEN ISNULL(pp.revenue, 0) = 0 THEN NULL
            ELSE ROUND(
                (ISNULL(cp.revenue, 0) - ISNULL(pp.revenue, 0))
                / pp.revenue * 100, 2
            )
        END AS revenue_growth_pct,
        CASE
            WHEN ISNULL(cp.revenue, 0) = 0 THEN NULL
            ELSE ROUND(ISNULL(cp.margin, 0) / cp.revenue * 100, 2)
        END AS current_margin_pct,
        CASE
            WHEN ISNULL(pp.revenue, 0) = 0 THEN NULL
            ELSE ROUND(ISNULL(pp.margin, 0) / pp.revenue * 100, 2)
        END AS prior_margin_pct

    FROM current_period AS cp
    FULL OUTER JOIN prior_period AS pp
        ON cp.category = pp.category
        AND cp.region  = pp.region
    ORDER BY
        current_revenue DESC;
END;

Notice that this procedure queries agg_daily_sales — the materialized warehouse table — rather than going back to the federated lakehouse view. This is deliberate. Period comparison queries are expensive; they aggregate across potentially thousands of dates. The aggregation layer absorbs that cost during the nightly refresh, so the comparison procedure returns in milliseconds.

Stored Procedure 3: Territory Performance Ranking

This one demonstrates a pattern where you use a table-valued parameter-like approach to handle dynamic filtering, plus window functions for ranking:

CREATE OR ALTER PROCEDURE dbo.usp_territory_performance_ranking
    @fiscal_year     INT,
    @metric          VARCHAR(20) = 'revenue',  -- 'revenue', 'margin', 'growth'
    @top_n           INT         = 10
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate metric parameter to prevent injection
    IF @metric NOT IN ('revenue', 'margin', 'growth')
    BEGIN
        RAISERROR('Invalid metric. Must be revenue, margin, or growth.', 16, 1);
        RETURN;
    END;

    WITH annual_summary AS (
        SELECT
            tr.territory_key,
            tr.territory_name,
            tr.region,
            tr.country,
            tr.territory_manager,
            SUM(CASE WHEN dd.fiscal_year = @fiscal_year      THEN agg.total_revenue ELSE 0 END) AS current_revenue,
            SUM(CASE WHEN dd.fiscal_year = @fiscal_year      THEN agg.total_margin  ELSE 0 END) AS current_margin,
            SUM(CASE WHEN dd.fiscal_year = @fiscal_year - 1  THEN agg.total_revenue ELSE 0 END) AS prior_revenue
        FROM dbo.agg_daily_sales AS agg
        INNER JOIN dbo.dim_territory AS tr
            ON agg.territory_key = tr.territory_key
        INNER JOIN dbo.dim_date AS dd
            ON agg.agg_date = dd.calendar_date
        WHERE dd.fiscal_year IN (@fiscal_year, @fiscal_year - 1)
        GROUP BY
            tr.territory_key,
            tr.territory_name,
            tr.region,
            tr.country,
            tr.territory_manager
    ),
    ranked AS (
        SELECT
            *,
            CASE WHEN current_revenue = 0 THEN 0
                 ELSE ROUND(current_margin / current_revenue * 100, 2)
            END AS margin_pct,
            CASE WHEN prior_revenue = 0 THEN NULL
                 ELSE ROUND((current_revenue - prior_revenue) / prior_revenue * 100, 2)
            END AS yoy_growth_pct,
            RANK() OVER (
                ORDER BY
                    CASE @metric
                        WHEN 'revenue' THEN current_revenue
                        WHEN 'margin'  THEN current_margin
                        WHEN 'growth'  THEN ISNULL((current_revenue - prior_revenue) / NULLIF(prior_revenue, 0) * 100, -9999)
                    END DESC
            ) AS performance_rank
        FROM annual_summary
    )
    SELECT TOP (@top_n)
        performance_rank,
        territory_name,
        region,
        country,
        territory_manager,
        current_revenue,
        current_margin,
        margin_pct,
        prior_revenue,
        yoy_growth_pct
    FROM ranked
    ORDER BY performance_rank;
END;

---

Calling Stored Procedures from Data Pipelines

Stored procedures become even more valuable when you wire them into Fabric data pipelines. After your nightly lakehouse refresh completes (the Spark notebooks that build fact_transactions), a pipeline can call usp_refresh_daily_sales_agg automatically.

In a Fabric data pipeline, add a Script activity (not a Copy activity) and configure it to connect to your Sales Warehouse. Set the script type to NonQuery and enter:

EXEC dbo.usp_refresh_daily_sales_agg
    @refresh_date = '@{formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')}';

The @{...} syntax is a pipeline expression that evaluates the previous day's date at runtime. This makes the pipeline dynamic — you don't hardcode the refresh date, so the same pipeline runs correctly every night regardless of when it executes.

If you need to pass the territory code from a pipeline parameter named TerritoryCode, it looks like:

EXEC dbo.usp_refresh_daily_sales_agg
    @refresh_date   = '@{formatDateTime(addDays(utcNow(), -1), 'yyyy-MM-dd')}',
    @territory_code = '@{pipeline().parameters.TerritoryCode}';

---

Hands-On Exercise

Let's put everything together into a coherent exercise. You'll build the complete cross-database reporting layer for the retail scenario.

Setup (5 minutes)

In your Fabric workspace, make sure you have:

• A lakehouse named SalesLakehouse with fact_transactions and dim_customer as Delta tables (you can create small mock tables from a notebook if you don't have real data)
• A warehouse named SalesWarehouse with the dimension tables from the DDL above

Step 1: Verify cross-database access (2 minutes)

Open the SalesWarehouse query editor and run:

SELECT TOP 5 *
FROM [SalesLakehouse].[dbo].[fact_transactions];

If you get results, three-part naming is working. If you get an error like "Database not found," double-check that both items are in the same workspace and that your account has at least Viewer access on the lakehouse.

Step 2: Create the base view (5 minutes)

Run the CREATE OR ALTER VIEW dbo.vw_sales_detail script from earlier in this lesson. Then verify it works:

SELECT TOP 100
    transaction_date,
    category,
    region,
    net_revenue,
    gross_margin,
    margin_pct
FROM dbo.vw_sales_detail
ORDER BY transaction_date DESC;

Step 3: Create all three stored procedures (10 minutes)

Run each CREATE OR ALTER PROCEDURE block in order. Test each one:

-- Test the aggregation refresh for a specific date
EXEC dbo.usp_refresh_daily_sales_agg @refresh_date = '2024-11-01';

-- Confirm rows were written
SELECT * FROM dbo.agg_daily_sales WHERE agg_date = '2024-11-01';

-- Test period comparison
EXEC dbo.usp_period_comparison
    @fiscal_year   = 2024,
    @fiscal_period = 11;

-- Test territory ranking
EXEC dbo.usp_territory_performance_ranking
    @fiscal_year = 2024,
    @metric      = 'revenue',
    @top_n       = 5;

Step 4: Build the aggregated view on top of the materialized table (5 minutes)

CREATE OR ALTER VIEW dbo.vw_executive_dashboard
AS
SELECT
    dd.fiscal_year,
    dd.fiscal_quarter,
    dd.fiscal_period,
    dd.month_name,
    agg.category,
    tr.region,
    SUM(agg.total_revenue)      AS revenue,
    SUM(agg.total_cost)         AS cost,
    SUM(agg.total_margin)       AS margin,
    ROUND(
        SUM(agg.total_margin) / NULLIF(SUM(agg.total_revenue), 0) * 100,
        2
    ) AS margin_pct,
    SUM(agg.transaction_count)  AS transactions
FROM dbo.agg_daily_sales AS agg
INNER JOIN dbo.dim_territory AS tr ON agg.territory_key = tr.territory_key
INNER JOIN dbo.dim_date AS dd      ON agg.agg_date = dd.calendar_date
GROUP BY
    dd.fiscal_year,
    dd.fiscal_quarter,
    dd.fiscal_period,
    dd.month_name,
    agg.category,
    tr.region;

Notice that vw_executive_dashboard doesn't federate to the lakehouse at all — it queries the materialized agg_daily_sales table entirely within the warehouse. This is the right choice for executive dashboards that refresh on a schedule: the expensive federated query runs once nightly in the stored procedure, and all subsequent report queries hit a local warehouse table.

Step 5: Connect Power BI (optional, 5 minutes)

Point a Power BI Direct Lake or DirectQuery connection at the Sales Warehouse endpoint, add vw_executive_dashboard as a table, and build a matrix visual. You'll be reporting on data that physically lives in a Lakehouse Delta table, without a single copy step.

---

Common Mistakes & Troubleshooting

"Cannot find the object because it does not exist or you do not have permissions"

This usually means one of three things. First, the lakehouse name in your three-part reference doesn't exactly match the lakehouse item name in the workspace — check for spaces or capitalization differences. Second, you're running the query from the lakehouse SQL endpoint query editor rather than the warehouse query editor — stored procedures only exist in the warehouse, and three-part naming to the lakehouse only works from the warehouse side. Third, your account doesn't have permission to query the lakehouse. Even in the same workspace, you need at least Viewer access on the lakehouse item.

Stored procedure runs fine in query editor, fails from pipeline

The pipeline's Script activity connects using the workspace identity (the pipeline's service principal or the pipeline runner's identity). If that identity doesn't have access to the lakehouse endpoint, the federated join inside the view will fail. Grant the pipeline's execution identity Viewer access on the lakehouse item in workspace settings. See Securing and Governing Microsoft Fabric: Workspace Roles, Item Permissions, and OneLake Data Access for a detailed walkthrough of item-level permissions.

Cross-database view is very slow

Federated queries across the lakehouse SQL endpoint have overhead. If vw_sales_detail is slow, ask yourself: is the predicate being pushed down to the lakehouse? Add an EXPLAIN or look at query insights. Common performance killers include: joining on non-integer keys that require implicit conversions, filtering on computed columns (like CAST(transaction_date AS DATE)) that can't be pushed down, and querying large lakehouse tables without a partition filter. If the view is queried interactively by many users, seriously consider materializing the join result into a warehouse table on a schedule rather than federating on demand. For large-scale Delta table optimizations on the lakehouse side, Optimizing Delta Table Performance in a Fabric Lakehouse covers how V-Order and Z-Order affect query pushdown.

"Cannot write to lakehouse tables from a stored procedure"

Correct — and by design. The lakehouse SQL Analytics Endpoint is read-only. If your stored procedure needs to write transformation results somewhere, write to a warehouse table. If you genuinely need those results back in the lakehouse (for Spark consumption or Direct Lake reports), consider using a Spark notebook as the write-back mechanism, or a pipeline Copy activity that reads the warehouse table and appends to a lakehouse Delta table.

Stored procedure parameter values silently truncated from pipeline

Pipeline expression strings have length limits, and date format strings can break if your expression syntax is off. Always test pipeline expressions in the pipeline's expression editor before wiring them to a Script activity. Use formatDateTime(utcNow(), 'yyyy-MM-dd') — not yyyy/MM/dd or other variants — to match a SQL DATE type cleanly.

View definition is stale after lakehouse schema change

If a column is added to fact_transactions in the lakehouse (via a Spark notebook), your view definition doesn't automatically update. If the view uses SELECT *, the new column won't appear until you run CREATE OR ALTER VIEW again. If the view uses explicit column names and a column is renamed or dropped, the view will start returning errors. This is an argument for explicit column lists in production views — silent schema changes are easier to catch as errors than as missing data.

---

When to Use Each Pattern

Before wrapping up, let's be explicit about when to reach for each tool:

Use a cross-database view when:

• Consumers need a clean, stable SQL interface that hides the lakehouse/warehouse boundary
• Queries are infrequent (analyst ad hoc, pipeline testing) or low-volume
• The underlying lakehouse tables are well-optimized and predicate pushdown works effectively
• You need the view result to always reflect the latest state of the lakehouse (no staleness tolerance)

Use a stored procedure when:

• You need parameterized, reusable business logic (period comparisons, sliding window calculations)
• You're materializing aggregation results to avoid repeated expensive federation queries
• You're orchestrating multi-step ETL within the warehouse tier (delete, insert, validate, log)
• You need to be called from a data pipeline with dynamic parameters

Use a materialized warehouse table (populated by a stored procedure) when:

• The query involves large aggregations across the full history of a lakehouse fact table
• Multiple reports or users query the same aggregation simultaneously
• You need sub-second response times for dashboards and the data can tolerate scheduled refresh latency

The choice isn't either/or. In this lesson's architecture, all three patterns work together: the cross-database view gives you the joined detail-level interface, the stored procedure populates the aggregation table, and the aggregation table powers fast reporting. That layered approach is exactly what a mature Fabric analytics estate looks like.

---

Summary & Next Steps

You've now built a complete cross-database analytics layer in Microsoft Fabric — stored procedures that encapsulate parameterized business logic, views that transparently join lakehouse Delta tables with warehouse dimensions, and a materialization pattern that keeps dashboards fast without duplicating data.

The key ideas to carry forward:

• Three-part naming ([LakehouseName].[schema].[table]) from the warehouse query editor gives you federated read access to any lakehouse in the same workspace — no data movement required
• Stored procedures in the Fabric Warehouse are write-capable and pipeline-callable; use them for multi-step logic and scheduled materialization
• Layer your views: federated views for detail access, warehouse-local views for aggregation reporting
• Make stored procedures idempotent (DELETE then INSERT, or MERGE) so pipeline retries don't corrupt your aggregation tables
• Validate parameters inside procedures when they come from external systems; use parameterized SQL, never string concatenation

Where to go next:

If you're thinking about access control — which users can query which views — Implementing Row-Level Security in a Fabric Warehouse and Lakehouse SQL Analytics Endpoint covers how to apply dynamic row-level policies that work consistently across both stores.

If you want to understand how the views you built here interact with Power BI Direct Lake mode and when to use it over DirectQuery against the warehouse endpoint, Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery is the natural next read.

And if you're ready to put the entire pipeline under version control — storing your stored procedure T-SQL alongside your notebook and pipeline definitions — Fabric Git Integration and Deployment Pipelines: Version Control and Promotion Across Environments shows you how to do it cleanly across dev, test, and prod workspaces.