Aggregating and Writing Summary Tables from a Fabric Lakehouse Gold Layer to a Fabric Warehouse Using Cross-Engine T-SQL Views and PySpark for Dual-Engine Reporting

Introduction

You've built the gold layer. Your medallion architecture is humming along — bronze ingests raw data, silver cleanses and conforms it, and gold produces carefully crafted star-schema fact and dimension tables as Delta Lake files sitting in your Fabric Lakehouse. At this point, many teams stop and connect Power BI directly to the lakehouse SQL analytics endpoint. That works. But it leaves a significant capability gap on the table.

The reality in most mid-to-large organizations is that you have two groups of consumers. The first group uses Power BI, Jupyter notebooks, or Spark SQL — they're comfortable with the lakehouse, Direct Lake semantic models, and the flexibility that comes with Delta tables. The second group — your SQL analysts, application developers, and finance reporting teams — expects a traditional SQL Warehouse experience: T-SQL stored procedures, materialized summary tables they can query with sub-second SLA guarantees, and the ability to join lakehouse data with warehouse-managed reference data without thinking about Spark. Serving both groups from a single physical copy of your gold layer data is exactly what Fabric's cross-engine architecture enables.

By the end of this lesson, you'll know how to aggregate gold-layer Delta tables from PySpark and write them as physical summary tables into a Fabric Warehouse, expose those same gold-layer tables as cross-engine T-SQL views inside the warehouse, and build a dual-engine reporting architecture where Direct Lake and SQL endpoint consumers coexist on the same underlying data without duplication or synchronization headaches.

What you'll learn:

• How to use PySpark to compute summary aggregations from gold-layer Delta tables and write results directly into a Fabric Warehouse table
• How to create cross-engine T-SQL views in a Fabric Warehouse that reference lakehouse tables through the SQL analytics endpoint
• When to materialize summary tables in the warehouse versus keeping them as views over the lakehouse
• How to structure your workspace so both Spark and SQL consumers can report reliably from the same gold layer
• How to troubleshoot the common cross-engine query failures that will absolutely show up the first time you do this

---

Prerequisites

You should be comfortable with:

• The medallion architecture pattern — specifically what lives in the gold layer and why. If you need a refresher, Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers covers this thoroughly.
• Writing PySpark in Fabric notebooks. The Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables lesson is a good baseline.
• Basic T-SQL and an understanding of the difference between a Fabric Lakehouse and a Fabric Warehouse. The Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload article explains the tradeoffs in depth.
• A Fabric workspace with at least one Lakehouse containing gold-layer Delta tables and one Warehouse item. If you haven't set up a workspace yet, see Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace.

---

Understanding the Cross-Engine Architecture

Before writing a single line of code, it's worth understanding exactly what Fabric allows here, because it's more powerful — and more nuanced — than it first appears.

Your Fabric Lakehouse stores Delta tables in OneLake. The SQL analytics endpoint is a read-only T-SQL interface that automatically reflects every Delta table in your lakehouse as a queryable SQL object. It's not a separate copy of the data — it reads the Parquet files directly. This is the key: when you create a T-SQL view in your Fabric Warehouse that references a table from the lakehouse SQL analytics endpoint, that view is calling across engines at query time. No ETL required.

The Fabric Warehouse, on the other hand, manages its own storage — it uses Delta Parquet under the hood too, but in a warehouse-managed location. Tables you INSERT INTO or COPY INTO a warehouse are physically stored there and served by the warehouse's distributed SQL engine, not by Spark. This means they support full DML (updates, deletes), warehouse-managed statistics, and consistent sub-second performance — features the SQL analytics endpoint doesn't offer because it's read-only.

So you have two complementary patterns:

Pattern 1 — Cross-engine views: Create a view in the warehouse that SELECTs from a three-part name referencing your lakehouse endpoint. No data movement. Query time reflection of whatever's in the Delta table right now. Best for operational reporting where freshness matters and the query complexity is low enough that the SQL analytics endpoint handles it well.

Pattern 2 — Materialized summary tables: Run PySpark to aggregate your gold-layer Delta tables and write the results as a managed table in the warehouse (using JDBC or the warehouse's COPY INTO from a staging file). Best for aggregations that are expensive to compute at query time, or where your SQL consumers need predictable sub-second performance on pre-computed rollups.

In practice, you'll use both. The cross-engine views serve detailed fact and dimension data; the materialized summary tables serve pre-aggregated reporting tables that Power BI or SQL clients hit constantly.

---

Setting Up the Scenario

We'll work with a realistic retail analytics scenario. Your gold layer in RetailGold_Lakehouse contains:

• gold.fact_sales — one row per sales transaction line item (~200M rows, partitioned by sale_date)
• gold.dim_product — product dimension with category hierarchy
• gold.dim_store — store dimension with region and district
• gold.dim_date — date dimension

Your Fabric Warehouse is named RetailReporting_Warehouse. Your SQL consumers — the finance and commercial teams — need two things:

1. A pre-aggregated summary_sales_by_month_region table they can query with sub-second latency for dashboards
2. The ability to write ad-hoc T-SQL queries that join full-detail fact data with their own warehouse-managed reference tables

By the end of this lesson, you'll have both.

---

Step 1: Inspecting Your Gold Layer Tables

Open a Spark notebook in your Fabric workspace and attach it to RetailGold_Lakehouse. Let's confirm the gold tables exist and understand their shape before we start aggregating.

# Confirm gold layer tables are registered in the lakehouse metastore
spark.sql("SHOW TABLES IN gold").show(truncate=False)

# Inspect fact_sales schema and a sample of partition counts
df_fact = spark.table("gold.fact_sales")
print(df_fact.schema.simpleString())

from pyspark.sql.functions import col, count, min, max
df_fact.groupBy(
    col("sale_date").substr(1, 7).alias("month")
).agg(
    count("*").alias("row_count")
).orderBy("month").show(24)

You're looking for a few things here: Are the partition counts reasonable? Is there a clearly usable date column? Do product and store keys look consistent with the dimension tables? This inspection step saves you from discovering a broken join condition halfway through a 20-minute aggregation.

---

Step 2: Computing the Summary Aggregation with PySpark

Now let's build the aggregation. We want monthly sales totals broken down by product category and store region — a classic executive-level rollup.

from pyspark.sql.functions import (
    col, sum as spark_sum, count, countDistinct,
    date_format, round as spark_round
)

# Load gold tables
fact_sales   = spark.table("gold.fact_sales")
dim_product  = spark.table("gold.dim_product")
dim_store    = spark.table("gold.dim_store")
dim_date     = spark.table("gold.dim_date")

# Build the enriched sales base with only the columns we need
# Pushing the filter to the join reduces shuffle size significantly
sales_enriched = (
    fact_sales
    .join(dim_product.select("product_key", "category_name", "subcategory_name"),
          on="product_key", how="inner")
    .join(dim_store.select("store_key", "region_name", "district_name"),
          on="store_key", how="inner")
    .join(dim_date.select("date_key", "calendar_year", "fiscal_quarter"),
          on=fact_sales["sale_date"] == dim_date["date_key"], how="inner")
)

# Aggregate to monthly / category / region grain
df_summary = (
    sales_enriched
    .groupBy(
        col("calendar_year"),
        date_format(col("sale_date"), "yyyy-MM").alias("sale_month"),
        col("fiscal_quarter"),
        col("region_name"),
        col("district_name"),
        col("category_name"),
        col("subcategory_name")
    )
    .agg(
        spark_round(spark_sum("gross_sales_amount"), 2).alias("total_gross_sales"),
        spark_round(spark_sum("discount_amount"), 2).alias("total_discounts"),
        spark_round(spark_sum("net_sales_amount"), 2).alias("total_net_sales"),
        spark_round(spark_sum("cost_amount"), 2).alias("total_cost"),
        spark_round(
            spark_sum("net_sales_amount") - spark_sum("cost_amount"), 2
        ).alias("total_gross_margin"),
        count("transaction_id").alias("transaction_count"),
        countDistinct("customer_id").alias("unique_customers")
    )
    .withColumn(
        "gross_margin_pct",
        spark_round(
            col("total_gross_margin") / col("total_gross_sales") * 100, 2
        )
    )
)

# Cache it — we'll use this DataFrame in multiple subsequent steps
df_summary.cache()
df_summary.count()  # Trigger the cache

print(f"Summary rows: {df_summary.count()}")
df_summary.show(5)

Notice a few deliberate choices in this code:

• We only select the columns we need from each dimension before joining (dim_product.select(...)) — this avoids shuffling unnecessary columns across the cluster.
• We compute gross_margin_pct as a derived column after the aggregation rather than inside the agg() call. This is a minor readability choice, but it also makes it easier to add or change margin calculations later without restructuring the groupBy.
• We cache the result. If you're writing it to both a Delta table in the lakehouse and the warehouse, you don't want to recompute it twice.

---

Step 3: Writing the Summary Back to the Gold Lakehouse (Optional Staging Step)

Before writing to the warehouse, it often makes sense to persist the summary as a Delta table in your gold lakehouse. This gives you a few things: a replayable snapshot of the aggregated data for time-travel auditing, a Direct Lake–friendly source for Power BI, and a clean staging point from which the warehouse write becomes a simple file read.

# Write summary as a Delta table in the gold lakehouse
# Use overwrite mode for a full monthly refresh pattern
(
    df_summary
    .write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .partitionBy("calendar_year", "sale_month")
    .saveAsTable("gold.summary_sales_by_month_region")
)

print("Gold summary table written successfully.")

This table is now immediately visible via the SQL analytics endpoint of RetailGold_Lakehouse. Power BI users with a Direct Lake semantic model can consume it right now — no additional steps. As discussed in Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery, Direct Lake reads these Delta Parquet files without importing them, so your BI consumers get fresh data as soon as this write completes.

---

Step 4: Writing the Summary Table into the Fabric Warehouse with PySpark JDBC

Now we write the aggregated summary into the Fabric Warehouse. The cleanest supported method from a Spark notebook is JDBC, using the Fabric Warehouse's TDS endpoint.

First, gather the warehouse connection string. In the Fabric portal, open RetailReporting_Warehouse and click the settings icon or the connection string option (usually visible in the top toolbar or workspace item details panel). It will look like:

<your-workspace-id>.datawarehouse.fabric.microsoft.com

The JDBC URL format for Fabric Warehouse is:

jdbc:sqlserver://<workspace-id>.datawarehouse.fabric.microsoft.com:1433;
  database=RetailReporting_Warehouse;
  encrypt=true;
  trustServerCertificate=false;
  hostNameInCertificate=*.datawarehouse.fabric.microsoft.com;
  loginTimeout=30;
  authentication=ActiveDirectoryServicePrincipal

For notebook authentication, you'll use either a Service Principal or the current user's token. In a production pipeline, service principal is the right call. For interactive development in a notebook, you can use token pass-through via the mssparkutils credential helper:

from pyspark.sql import SparkSession
import com.microsoft.azure.synapse.mssparkutils as mssparkutils  # available in Fabric notebooks

# Get an AAD token for the warehouse
token = mssparkutils.credentials.getToken("https://analysis.windows.net/powerbi/api")

# Warehouse JDBC connection properties
warehouse_host = "<your-workspace-id>.datawarehouse.fabric.microsoft.com"
warehouse_db   = "RetailReporting_Warehouse"
jdbc_url = (
    f"jdbc:sqlserver://{warehouse_host}:1433;"
    f"database={warehouse_db};"
    f"encrypt=true;"
    f"trustServerCertificate=false;"
    f"hostNameInCertificate=*.datawarehouse.fabric.microsoft.com;"
    f"loginTimeout=30"
)

jdbc_properties = {
    "accessToken": token,
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

# Write the summary DataFrame to the warehouse
# Use "overwrite" to replace the full table on each load
(
    df_summary
    .write
    .format("jdbc")
    .option("url", jdbc_url)
    .option("dbtable", "reporting.summary_sales_by_month_region")
    .option("accessToken", token)
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
    .option("batchsize", 100000)
    .option("truncate", "true")   # Truncate then insert — preserves table DDL and indexes
    .mode("overwrite")
    .save()
)

print("Warehouse summary table written successfully.")

A few things to know about JDBC write performance to the warehouse:

• The batchsize option controls how many rows Spark sends per JDBC batch commit. The default is 1,000 rows — far too small for a summary table of any real size. Set it to 50,000–100,000.
• The JDBC write from Spark is not parallel in the way a COPY INTO from Parquet files would be. Each Spark executor writes its partition serially through the JDBC driver. For very large summary tables (millions of rows), consider writing to a staging Parquet file in OneLake first, then using COPY INTO from the warehouse.
• If your summary table doesn't exist yet in the warehouse, create it explicitly with CREATE TABLE in the warehouse SQL editor first, using the correct schema name (reporting), column types, and any desired indexes. Letting Spark infer and create the table through JDBC works but gives you the least control over column types.

Here's the DDL to create the target table in the warehouse before the first PySpark write:

-- Run this in the Fabric Warehouse SQL editor
CREATE SCHEMA IF NOT EXISTS reporting;

CREATE TABLE reporting.summary_sales_by_month_region (
    calendar_year        SMALLINT        NOT NULL,
    sale_month           CHAR(7)         NOT NULL,   -- 'yyyy-MM'
    fiscal_quarter       CHAR(6)         NOT NULL,
    region_name          NVARCHAR(100)   NOT NULL,
    district_name        NVARCHAR(100)   NOT NULL,
    category_name        NVARCHAR(100)   NOT NULL,
    subcategory_name     NVARCHAR(200)   NOT NULL,
    total_gross_sales    DECIMAL(18,2)   NULL,
    total_discounts      DECIMAL(18,2)   NULL,
    total_net_sales      DECIMAL(18,2)   NULL,
    total_cost           DECIMAL(18,2)   NULL,
    total_gross_margin   DECIMAL(18,2)   NULL,
    gross_margin_pct     DECIMAL(8,2)    NULL,
    transaction_count    BIGINT          NULL,
    unique_customers     BIGINT          NULL
);

---

Step 5: Creating Cross-Engine T-SQL Views in the Warehouse

Here's where Fabric's architecture really shines. You can write T-SQL views in the warehouse that SELECT from your lakehouse's SQL analytics endpoint — referencing full-detail gold tables that never move to the warehouse. SQL consumers get a unified view of warehouse-managed summary tables and live lakehouse Delta tables through a single warehouse connection.

The three-part naming convention to reference a lakehouse table from the warehouse is:

[<lakehouse-name>].[<schema>].[<table-name>]

The lakehouse must be in the same workspace. Fabric resolves the reference automatically through the SQL analytics endpoint.

-- Run in the Fabric Warehouse SQL editor

-- View 1: Full-detail fact data from the lakehouse
-- SQL analysts can join this with warehouse tables without knowing anything about Spark
CREATE VIEW reporting.vw_fact_sales_detail
AS
SELECT
    fs.transaction_id,
    fs.sale_date,
    fs.store_key,
    fs.product_key,
    fs.customer_id,
    fs.quantity_sold,
    fs.gross_sales_amount,
    fs.discount_amount,
    fs.net_sales_amount,
    fs.cost_amount,
    (fs.net_sales_amount - fs.cost_amount) AS gross_margin_amount
FROM [RetailGold_Lakehouse].[gold].[fact_sales] AS fs;
GO

-- View 2: A joined view combining lakehouse dimensions with warehouse summary
-- This gives SQL consumers an enriched summary without materializing everything
CREATE VIEW reporting.vw_summary_with_store_detail
AS
SELECT
    s.calendar_year,
    s.sale_month,
    s.fiscal_quarter,
    s.region_name,
    s.district_name,
    s.category_name,
    s.subcategory_name,
    s.total_net_sales,
    s.total_gross_margin,
    s.gross_margin_pct,
    s.transaction_count,
    s.unique_customers,
    -- Join with a warehouse-managed store reference table
    wr.territory_manager_name,
    wr.sales_target_amount,
    wr.territory_tier
FROM reporting.summary_sales_by_month_region AS s
LEFT JOIN warehouse.dim_territory_reference    AS wr
    ON  s.region_name   = wr.region_name
    AND s.calendar_year = wr.target_year;
GO

The second view is particularly powerful: it's combining a pre-aggregated summary table physically stored in the warehouse with a warehouse-managed reference table that the SQL team owns and updates directly. No Spark involved, no file movement — just T-SQL.

---

Step 6: Querying Cross-Engine Views — Understanding What Actually Happens

Let's be explicit about execution behavior, because this will affect your design decisions.

When a SQL consumer queries reporting.vw_fact_sales_detail from the warehouse, the warehouse query optimizer generates a plan that:

1. Sends the predicate pushdown to the lakehouse SQL analytics endpoint
2. The endpoint reads the relevant Delta Parquet files from OneLake
3. Results are returned to the warehouse engine, which applies any remaining filter/join/aggregation logic
4. The final result set is returned to the client

This is effectively a federated query. It works well when:

• The view is used with strong WHERE clause predicates (year, month, region) that push down effectively to the Delta table's partition structure
• The result set being brought across the engine boundary is reasonably sized (filtered rows, not 200M raw rows)
• Latency of a few seconds is acceptable (the cross-engine hop adds overhead versus a native warehouse table)

It performs poorly when:

• You run SELECT * FROM reporting.vw_fact_sales_detail with no filter — you're pulling all 200M rows through the cross-engine path
• You join the cross-engine view with a large warehouse table and the optimizer doesn't push the join predicate through
• You aggregate over the cross-engine view repeatedly in interactive sessions where users expect sub-second response

This is exactly why the materialized summary_sales_by_month_region table exists in the warehouse. Most of your high-frequency dashboard queries should hit that physical table. The cross-engine view is for the SQL analyst who needs to drill into transaction-level detail on demand.

---

Step 7: Building a PySpark Orchestration Notebook for the Full Refresh Pattern

In production, you don't run this ad hoc. You encapsulate the full aggregation and warehouse write into a parameterized notebook that a Fabric Data Pipeline calls on a schedule.

# Parameterized notebook — parameters injected by pipeline
# Default values used for interactive testing

dbutils.widgets.text("target_year", "2024")
dbutils.widgets.text("refresh_mode", "full")  # 'full' or 'incremental'

target_year  = int(dbutils.widgets.get("target_year"))
refresh_mode = dbutils.widgets.get("refresh_mode")

print(f"Starting summary refresh | Year: {target_year} | Mode: {refresh_mode}")

from pyspark.sql.functions import (
    col, sum as spark_sum, count, countDistinct,
    date_format, round as spark_round, lit
)
from delta.tables import DeltaTable

# Load and filter fact table based on refresh mode
fact_sales = spark.table("gold.fact_sales")

if refresh_mode == "incremental":
    fact_sales = fact_sales.filter(col("calendar_year") == target_year)
    print(f"Incremental mode: filtering to year {target_year}")
else:
    print("Full mode: processing all years")

# Dimensions
dim_product = spark.table("gold.dim_product")
dim_store   = spark.table("gold.dim_store")
dim_date    = spark.table("gold.dim_date")

# Enriched join
sales_enriched = (
    fact_sales
    .join(dim_product.select("product_key", "category_name", "subcategory_name"),
          on="product_key", how="inner")
    .join(dim_store.select("store_key", "region_name", "district_name"),
          on="store_key", how="inner")
    .join(dim_date.select("date_key", "calendar_year", "fiscal_quarter"),
          on=fact_sales["sale_date"] == dim_date["date_key"], how="inner")
)

# Aggregation
df_summary = (
    sales_enriched
    .groupBy(
        col("calendar_year"),
        date_format(col("sale_date"), "yyyy-MM").alias("sale_month"),
        col("fiscal_quarter"),
        col("region_name"),
        col("district_name"),
        col("category_name"),
        col("subcategory_name")
    )
    .agg(
        spark_round(spark_sum("gross_sales_amount"), 2).alias("total_gross_sales"),
        spark_round(spark_sum("discount_amount"), 2).alias("total_discounts"),
        spark_round(spark_sum("net_sales_amount"), 2).alias("total_net_sales"),
        spark_round(spark_sum("cost_amount"), 2).alias("total_cost"),
        spark_round(spark_sum("net_sales_amount") - spark_sum("cost_amount"), 2)
            .alias("total_gross_margin"),
        count("transaction_id").alias("transaction_count"),
        countDistinct("customer_id").alias("unique_customers")
    )
    .withColumn(
        "gross_margin_pct",
        spark_round(col("total_gross_margin") / col("total_gross_sales") * 100, 2)
    )
)

df_summary.cache()
row_count = df_summary.count()
print(f"Aggregation complete. Rows to write: {row_count}")

# Step 1: Write/merge to gold lakehouse Delta table
if refresh_mode == "incremental":
    # MERGE on year partition for incremental — don't overwrite other years
    gold_delta = DeltaTable.forName(spark, "gold.summary_sales_by_month_region")
    (
        gold_delta.alias("target")
        .merge(
            df_summary.alias("source"),
            "target.calendar_year = source.calendar_year AND "
            "target.sale_month = source.sale_month AND "
            "target.region_name = source.region_name AND "
            "target.category_name = source.category_name"
        )
        .whenMatchedUpdateAll()
        .whenNotMatchedInsertAll()
        .execute()
    )
    print("Incremental MERGE to gold lakehouse complete.")
else:
    (
        df_summary.write
        .format("delta")
        .mode("overwrite")
        .option("overwriteSchema", "true")
        .partitionBy("calendar_year", "sale_month")
        .saveAsTable("gold.summary_sales_by_month_region")
    )
    print("Full overwrite to gold lakehouse complete.")

# Step 2: Write to Fabric Warehouse via JDBC
token = mssparkutils.credentials.getToken("https://analysis.windows.net/powerbi/api")

warehouse_host = "<your-workspace-id>.datawarehouse.fabric.microsoft.com"
warehouse_db   = "RetailReporting_Warehouse"
jdbc_url = (
    f"jdbc:sqlserver://{warehouse_host}:1433;"
    f"database={warehouse_db};"
    f"encrypt=true;"
    f"trustServerCertificate=false;"
    f"hostNameInCertificate=*.datawarehouse.fabric.microsoft.com;"
    f"loginTimeout=30"
)

if refresh_mode == "incremental":
    # Delete existing rows for the target year before inserting
    # We do this with a pre-step SQL via JDBC
    import jaydebeapi
    # Alternative: use spark.read.jdbc to confirm row count, then truncate-by-partition
    # For simplicity in production, full truncate + reload is often the safest approach
    # unless the summary table is very large (>10M rows)
    pass

(
    df_summary
    .repartition(8)     # Control parallelism — too many partitions = too many JDBC connections
    .write
    .format("jdbc")
    .option("url", jdbc_url)
    .option("dbtable", "reporting.summary_sales_by_month_region")
    .option("accessToken", token)
    .option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
    .option("batchsize", 100000)
    .option("truncate", "true")
    .mode("overwrite")
    .save()
)

print("Warehouse write complete.")

# Exit with a status value the pipeline can read
mssparkutils.notebook.exit(f'{{"rows_written": {row_count}}}')

The mssparkutils.notebook.exit() call at the end is intentional — it lets the calling pipeline capture the row count and use it in a downstream notification or validation step. This is the pattern described in Orchestrating Multi-Notebook Workflows in Microsoft Fabric: Using Pipeline Notebook Activities, Activity Dependencies, and Output Variables to Chain PySpark Transformations Across Medallion Layers.

---

Hands-On Exercise

Build the following end-to-end flow in your own Fabric workspace:

Setup: Create a Lakehouse called SalesGold_Lakehouse and a Warehouse called SalesReporting_Warehouse.

Part 1 — Seed the gold layer: In a notebook, generate a synthetic gold-layer fact table:

from pyspark.sql.functions import expr
from pyspark.sql.types import *
import random

spark.sql("CREATE SCHEMA IF NOT EXISTS gold")

# Generate synthetic fact data
data = []
categories = ["Electronics", "Apparel", "Home & Garden", "Sports"]
regions    = ["Northeast", "Southeast", "Midwest", "West"]

for year in [2022, 2023, 2024]:
    for month in range(1, 13):
        for _ in range(500):
            gross  = round(random.uniform(100, 10000), 2)
            disc   = round(gross * random.uniform(0.0, 0.15), 2)
            net    = round(gross - disc, 2)
            cost   = round(net  * random.uniform(0.4, 0.75), 2)
            data.append((
                f"TXN-{year}-{month:02d}-{random.randint(10000,99999)}",
                f"{year}-{month:02d}-{random.randint(1,28):02d}",
                random.randint(1, 50),      # store_key
                random.randint(1, 200),     # product_key
                random.randint(1000, 9999), # customer_id
                random.randint(1, 20),      # quantity
                gross, disc, net, cost,
                random.choice(categories),
                random.choice(regions),
                year,
                f"Q{(month-1)//3 + 1}"
            ))

schema = StructType([
    StructField("transaction_id", StringType()),
    StructField("sale_date", StringType()),
    StructField("store_key", IntegerType()),
    StructField("product_key", IntegerType()),
    StructField("customer_id", IntegerType()),
    StructField("quantity_sold", IntegerType()),
    StructField("gross_sales_amount", DoubleType()),
    StructField("discount_amount", DoubleType()),
    StructField("net_sales_amount", DoubleType()),
    StructField("cost_amount", DoubleType()),
    StructField("category_name", StringType()),
    StructField("region_name", StringType()),
    StructField("calendar_year", IntegerType()),
    StructField("fiscal_quarter", StringType()),
])

df = spark.createDataFrame(data, schema)
df.write.format("delta").mode("overwrite").partitionBy("calendar_year").saveAsTable("gold.fact_sales_synthetic")
print(f"Seeded {df.count()} rows")

Part 2 — Aggregate: Adapt the aggregation notebook from this lesson to compute monthly summaries from gold.fact_sales_synthetic. Your aggregation should produce totals for total_net_sales, total_gross_margin, and gross_margin_pct at the calendar_year / sale_month / region_name / category_name grain.

Part 3 — Write to warehouse: Use the JDBC write pattern to push the summary into SalesReporting_Warehouse under a schema named reporting. Verify by running SELECT COUNT(*) FROM reporting.summary_sales_by_month_region in the warehouse SQL editor.

Part 4 — Create a cross-engine view: In the warehouse SQL editor, create a view that references gold.fact_sales_synthetic from SalesGold_Lakehouse and exposes the most recent 90 days of transaction detail. Test it with a TOP 100 query.

---

Common Mistakes & Troubleshooting

"Invalid object name '[LakehouseName].[schema].[table]'" in the warehouse SQL editor

The most common cause is that the lakehouse and warehouse aren't in the same workspace. Verify both items appear in the same workspace in the Fabric portal. The second cause is that the table name doesn't exist yet in the lakehouse metastore — confirm with SHOW TABLES IN gold from a notebook. The third cause is a typo in the three-part name — the lakehouse name must match exactly, including capitalization.

JDBC write fails with "Login failed for user"

Token pass-through via mssparkutils.credentials.getToken() works only when the notebook is running interactively as a user who has write access to the warehouse. When the pipeline runs this notebook as a scheduled job, it uses the pipeline's identity. Make sure the identity running the pipeline has at least Contributor role on the warehouse item. If you're using a Service Principal, it must be explicitly granted access in the warehouse's permission settings.

JDBC write completes but the warehouse table is empty

Check whether the DataFrame had rows before the write. Add df_summary.count() before the write call and print it. If count is > 0 but the table is empty, the issue is usually a schema mismatch between the DataFrame columns and the target table DDL. Drop the target table, let Spark recreate it (remove truncate=true temporarily), confirm the schema, then recreate the DDL properly.

Cross-engine view query takes 90 seconds with no filter

This is expected behavior, not a bug. Add a WHERE calendar_year = YEAR(GETDATE()) predicate. The optimizer will push that to the SQL analytics endpoint, which uses it to read only the relevant Delta partition files. Unfiltered cross-engine queries over large Delta tables should be avoided in interactive workloads.

PySpark aggregation produces incorrect margin percentages with NULL values

If any total_gross_sales rows are 0 or NULL (products with $0 transactions), the division will produce NULL or divide-by-zero. Wrap the expression: CASE WHEN total_gross_sales > 0 THEN total_gross_margin / total_gross_sales * 100 ELSE NULL END. In PySpark, use when(col("total_gross_sales") > 0, ...).otherwise(None).

---

Summary & Next Steps

You've now built the full cross-engine reporting pattern in Fabric. Your gold-layer Delta tables stay in the lakehouse where Spark can evolve them, time-travel them, and serve Direct Lake semantic models. Your warehouse gets pre-computed summary tables that SQL consumers can query with predictable performance, plus cross-engine views that surface full-detail lakehouse data through a familiar T-SQL interface. Neither engine duplicates work, and neither group of consumers has to compromise.

To summarize what you built:

• A PySpark aggregation notebook that reads from gold Delta tables, computes monthly/regional/category rollups, writes back to the gold lakehouse, and then pushes the result to a Fabric Warehouse via JDBC
• T-SQL views in the warehouse that federate queries to the lakehouse SQL analytics endpoint using three-part naming
• A parameterized, pipeline-ready version of the notebook with full/incremental refresh modes and a structured exit value

Where to go next:

• Add row-level security to your warehouse summary tables so regional managers see only their region's data. Implementing Row-Level Security in a Fabric Warehouse and Lakehouse SQL Analytics Endpoint walks through exactly that pattern.
• Schedule the refresh notebook inside a pipeline with error handling and email alerts. Implementing End-to-End Pipeline Error Handling in Microsoft Fabric covers the If Condition and failure dependency wiring.
• Optimize the underlying gold Delta tables with OPTIMIZE and Z-Order on the columns your cross-engine views filter on most. Optimizing Delta Table Performance in a Fabric Lakehouse explains the mechanics and when each optimization pays off.
• If your SQL consumers need write-back capability (targets, annotations, manual overrides), explore Building a Fabric Data Warehouse with T-SQL: Tables, Views, and Cross-Database Queries for the full warehouse DML story.