Joining Multiple Delta Tables Across Fabric Lakehouses and Warehouses in a Single Spark Notebook: Cross-Workspace Queries, OneLake Paths, and Writing Results to a Gold Layer Table

Introduction

Here's a situation every Fabric practitioner eventually runs into: your silver-layer transaction data lives in a lakehouse in the DataEngineering workspace, your product dimension is maintained by a different team in a warehouse in the ProductMaster workspace, and your customer master data is being mirrored from Azure SQL into a third lakehouse in the CRM workspace. The business wants a unified gold-layer fact table that joins all three. Your first instinct might be to copy everything into one place before you join. That instinct is wrong — and expensive.

Microsoft Fabric's OneLake storage model means that every Delta table, whether it lives in a lakehouse or a warehouse, has a deterministic, addressable path in the global OneLake namespace. A Spark notebook running in any workspace can read those paths directly, perform the join in Spark's distributed execution engine, and write the result to a gold-layer Delta table — all without a single COPY or data duplication step. The key is understanding how those paths are constructed, what authentication model governs access, and how to write the result back cleanly.

By the end of this lesson, you will have a complete, production-grade pattern for cross-workspace, cross-store joins in a single PySpark notebook. You'll understand the mechanics well enough to debug it when it breaks and optimize it when it scales.

What you'll learn:

• How OneLake ABFS paths are constructed for lakehouses and warehouses in different workspaces
• How to load Delta tables from multiple workspaces into Spark DataFrames using spark.read.format("delta")
• How to use mssparkutils.fabric to resolve workspace and item GUIDs programmatically
• How to structure a multi-source join that produces a clean gold-layer result
• How to write the result back to a gold lakehouse using append, overwrite, and MERGE patterns
• Performance and governance considerations when reading across workspace boundaries

---

Prerequisites

Before working through this lesson, you should be comfortable with:

• Creating and navigating lakehouses and warehouses in Microsoft Fabric. If you're new to the distinction between these two storage types, review Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload before continuing.
• Writing PySpark in Fabric Spark notebooks. The patterns here assume fluency with DataFrames, schema inspection, and basic Delta writes. Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables covers the foundations.
• A working understanding of how OneLake organizes data. OneLake Explained: One Copy of Data, Delta Tables, and Shortcuts provides the mental model you need for the path construction section.
• Basic familiarity with the medallion architecture. This lesson writes to a gold layer — Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers explains why the gold layer exists and what it should contain.

You'll also need:

• A Fabric capacity (F2 or larger, or a trial capacity)
• Contributor or higher access to at least two workspaces
• Three Delta tables spread across at least two workspaces to work with

---

Understanding the OneLake Path Structure

Before you write a single line of PySpark, you need a firm grip on how OneLake addresses data. This is the mental model everything else in the lesson depends on.

Every Fabric workspace has a unique GUID. Every item inside that workspace — a lakehouse, a warehouse, a KQL database — also has a unique GUID. OneLake's ABFS (Azure Blob File System) path scheme encodes both of these GUIDs to create a globally addressable location for any file or table in your tenant.

The canonical path pattern looks like this:

abfss://<workspace-guid>@onelake.dfs.fabric.microsoft.com/<item-guid>/Tables/<table-name>

For a lakehouse, the structure under <item-guid> follows the lakehouse container layout:

• Tables/ — managed Delta tables created via the lakehouse UI or Spark writes
• Files/ — unmanaged files, parquet, CSV, JSON, etc.

For a warehouse, the structure is slightly different:

abfss://<workspace-guid>@onelake.dfs.fabric.microsoft.com/<item-guid>/<schema-name>/<table-name>

Warehouse tables in OneLake are surfaced under their schema folder. The default schema is dbo, so a warehouse table called dim_product in the dbo schema lives at:

abfss://<workspace-guid>@onelake.dfs.fabric.microsoft.com/<item-guid>/dbo/dim_product

This path structure means you need two pieces of information before you can read any cross-workspace table: the workspace GUID and the item GUID. There are two ways to get them: manually from the browser URL, or programmatically via mssparkutils.

---

Finding GUIDs: Manual vs. Programmatic

The Manual Approach

When you navigate to a lakehouse or warehouse in the Fabric UI, the browser URL contains both GUIDs. For example:

https://app.fabric.microsoft.com/groups/a1b2c3d4-e5f6-7890-abcd-ef1234567890/lakehouses/f9e8d7c6-b5a4-3210-fedc-ba9876543210

The first GUID (a1b2c3d4...) is the workspace ID. The second (f9e8d7c6...) is the lakehouse item ID. You can hard-code these in a notebook, but that's fragile — if the item is recreated or moved, the GUIDs change.

The Programmatic Approach with mssparkutils

mssparkutils.fabric provides a runtime API for resolving workspace and item metadata. This is the approach you should use in production notebooks:

# Resolve GUIDs for a workspace and item by their display names
def get_onelake_path(workspace_name: str, item_name: str, item_type: str = "Lakehouse", table_name: str = None, schema: str = "dbo") -> str:
    """
    Build an ABFS OneLake path for a Delta table in any workspace.
    
    Parameters:
        workspace_name: Display name of the target workspace
        item_name: Display name of the lakehouse or warehouse
        item_type: "Lakehouse" or "Warehouse"
        table_name: Name of the Delta table
        schema: Schema name (warehouse only, defaults to 'dbo')
    
    Returns:
        ABFS path string suitable for spark.read.format("delta")
    """
    workspace_id = mssparkutils.fabric.getWorkspaceId() if workspace_name == "current" else _resolve_workspace_id(workspace_name)
    item_id = _resolve_item_id(workspace_id, item_name, item_type)
    
    base = f"abfss://{workspace_id}@onelake.dfs.fabric.microsoft.com/{item_id}"
    
    if item_type == "Lakehouse":
        return f"{base}/Tables/{table_name}"
    elif item_type == "Warehouse":
        return f"{base}/{schema}/{table_name}"
    else:
        raise ValueError(f"Unsupported item type: {item_type}")

The _resolve_workspace_id and _resolve_item_id functions use the REST API via mssparkutils. Let's build those:

import requests
import json

def _resolve_workspace_id(workspace_name: str) -> str:
    """Use mssparkutils to list workspaces and find the GUID by display name."""
    workspaces = mssparkutils.fabric.getWorkspaces()
    for ws in workspaces:
        if ws.displayName == workspace_name:
            return ws.id
    raise ValueError(f"Workspace '{workspace_name}' not found or not accessible to this identity.")

def _resolve_item_id(workspace_id: str, item_name: str, item_type: str) -> str:
    """Find a specific lakehouse or warehouse GUID within a workspace."""
    items = mssparkutils.fabric.getArtifacts(workspace_id)
    for item in items:
        if item.displayName == item_name and item.type == item_type:
            return item.id
    raise ValueError(f"Item '{item_name}' of type '{item_type}' not found in workspace {workspace_id}.")

For notebooks running interactively, the execution identity is your own Entra ID user. For pipeline-triggered notebooks, it's the pipeline's identity. Keep this distinction in mind as you design your access model.

---

Setting Up the Scenario

Let's make this concrete with a realistic scenario. You work at a retail company. Here's where your data lives:

Table	Type	Workspace	Store Type
fact_transactions_silver	Silver fact table	DataEngineering	Lakehouse
dim_product	Product dimension	ProductMaster	Warehouse
dim_customer	Customer dimension	CRM	Lakehouse
gold_sales	Gold output	DataEngineering	Lakehouse

The join logic: each transaction has a product_id and customer_id. You want to enrich the transaction with the product category, product name, customer segment, and customer region, then write the denormalized gold table.

Start your notebook by importing everything you need and establishing the path resolution functions:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, current_timestamp, lit, coalesce
from pyspark.sql.types import StructType, StructField, StringType, LongType, DoubleType, TimestampType
from delta.tables import DeltaTable
import mssparkutils

# Workspace and item names — parameterized at the top for easy maintenance
WORKSPACE_TRANSACTIONS = "DataEngineering"
LAKEHOUSE_TRANSACTIONS  = "SilverLakehouse"
TABLE_TRANSACTIONS      = "fact_transactions_silver"

WORKSPACE_PRODUCTS      = "ProductMaster"
WAREHOUSE_PRODUCTS      = "ProductWarehouse"
TABLE_PRODUCTS          = "dim_product"

WORKSPACE_CUSTOMERS     = "CRM"
LAKEHOUSE_CUSTOMERS     = "CRMLakehouse"
TABLE_CUSTOMERS         = "dim_customer"

WORKSPACE_GOLD          = "DataEngineering"
LAKEHOUSE_GOLD          = "GoldLakehouse"
TABLE_GOLD              = "gold_sales"

Externalizing these names at the top is a professional habit. When a workspace is renamed or a lakehouse is swapped out, you change one line instead of hunting through twenty abfss:// strings.

---

Reading Delta Tables from Multiple Workspaces

With your path helper functions defined, reading each source table is straightforward. The critical pattern is that you always call spark.read.format("delta").load(path) — not spark.read.parquet(), not spark.sql() with a table name. You're using the Delta reader so that you get transaction log consistency and schema enforcement:

# ── Silver transactions from the DataEngineering Lakehouse ──────────────────
path_transactions = get_onelake_path(
    workspace_name=WORKSPACE_TRANSACTIONS,
    item_name=LAKEHOUSE_TRANSACTIONS,
    item_type="Lakehouse",
    table_name=TABLE_TRANSACTIONS
)

df_transactions = (
    spark.read.format("delta")
    .load(path_transactions)
    .select(
        col("transaction_id"),
        col("customer_id"),
        col("product_id"),
        col("transaction_date"),
        col("quantity").cast("integer"),
        col("unit_price").cast("double"),
        col("total_amount").cast("double"),
        col("store_region")
    )
)

print(f"Transactions loaded: {df_transactions.count():,} rows")
df_transactions.printSchema()

# ── Product dimension from the ProductMaster Warehouse ──────────────────────
path_products = get_onelake_path(
    workspace_name=WORKSPACE_PRODUCTS,
    item_name=WAREHOUSE_PRODUCTS,
    item_type="Warehouse",
    table_name=TABLE_PRODUCTS,
    schema="dbo"
)

df_products = (
    spark.read.format("delta")
    .load(path_products)
    .select(
        col("product_id"),
        col("product_name"),
        col("product_category"),
        col("product_subcategory"),
        col("brand"),
        col("list_price").cast("double")
    )
    # Only pull active products to avoid fan-out on historical dim records
    .filter(col("is_active") == True)
)

print(f"Active products loaded: {df_products.count():,} rows")

# ── Customer dimension from the CRM Lakehouse ───────────────────────────────
path_customers = get_onelake_path(
    workspace_name=WORKSPACE_CUSTOMERS,
    item_name=LAKEHOUSE_CUSTOMERS,
    item_type="Lakehouse",
    table_name=TABLE_CUSTOMERS
)

df_customers = (
    spark.read.format("delta")
    .load(path_customers)
    .select(
        col("customer_id"),
        col("customer_segment"),
        col("customer_region"),
        col("customer_since_date"),
        col("is_loyalty_member").cast("boolean")
    )
)

print(f"Customers loaded: {df_customers.count():,} rows")

Notice that you're selecting only the columns you need from each source. This is a column pruning pattern — Spark's Delta reader will push column selection down to the Parquet file reader, which means you're only deserializing the bytes you actually need. For wide tables (50+ columns), this matters enormously.

---

Performing the Multi-Table Join

Now comes the join itself. The structure here matters: you want to join the dimension tables to the fact table, not the other way around. The fact table drives the grain of the output:

# ── Build the gold-layer DataFrame ──────────────────────────────────────────

df_gold = (
    df_transactions.alias("txn")
    
    # Join product dimension — left join to preserve transactions with unmatched products
    .join(
        df_products.alias("prod"),
        on=col("txn.product_id") == col("prod.product_id"),
        how="left"
    )
    
    # Join customer dimension — left join to preserve transactions with unmatched customers
    .join(
        df_customers.alias("cust"),
        on=col("txn.customer_id") == col("cust.customer_id"),
        how="left"
    )
    
    # Project the final column set with explicit aliasing to avoid ambiguity
    .select(
        col("txn.transaction_id"),
        col("txn.transaction_date"),
        col("txn.store_region"),
        col("txn.customer_id"),
        coalesce(col("cust.customer_segment"), lit("Unknown")).alias("customer_segment"),
        coalesce(col("cust.customer_region"), lit("Unknown")).alias("customer_region"),
        col("cust.is_loyalty_member"),
        col("txn.product_id"),
        coalesce(col("prod.product_name"), lit("Unknown Product")).alias("product_name"),
        coalesce(col("prod.product_category"), lit("Uncategorized")).alias("product_category"),
        coalesce(col("prod.product_subcategory"), lit("Uncategorized")).alias("product_subcategory"),
        coalesce(col("prod.brand"), lit("Unknown")).alias("brand"),
        col("txn.quantity"),
        col("txn.unit_price"),
        col("txn.total_amount"),
        # Derived metrics
        (col("txn.unit_price") / col("prod.list_price")).alias("discount_ratio"),
        # Audit columns
        current_timestamp().alias("gold_created_at"),
        lit("notebook_cross_workspace_join").alias("gold_source_process")
    )
)

# Inspect before writing
df_gold.printSchema()
print(f"Gold rows to write: {df_gold.count():,}")

The coalesce(..., lit("Unknown")) pattern on dimension attributes is important. In a left join, unmatched dimension rows produce NULLs. Downstream Power BI reports that filter on product_category will silently exclude NULL rows, creating invisible data loss. Replacing NULLs with an explicit "Unknown" sentinel keeps all transactions in the output and makes the data quality issue visible to analysts.

Understanding Join Performance Across Workspace Boundaries

When you join tables that are read from different workspaces, you're working with DataFrames that Spark has already loaded into its execution memory from those remote paths. The join itself happens entirely in Spark — there's no cross-workspace SQL engine coordination happening during the join. Spark reads both datasets, shuffles or broadcasts as needed, and joins in memory. This means:

1. Dimension tables should be broadcast. If dim_product has 50,000 rows and dim_customer has 200,000 rows, both fit comfortably in a broadcast join. Use broadcast() to force it:

from pyspark.sql.functions import broadcast

df_gold = (
    df_transactions.alias("txn")
    .join(
        broadcast(df_products).alias("prod"),
        on=col("txn.product_id") == col("prod.product_id"),
        how="left"
    )
    .join(
        broadcast(df_customers).alias("cust"),
        on=col("txn.customer_id") == col("cust.customer_id"),
        how="left"
    )
    .select(...)
)

Broadcast joins avoid the shuffle that a sort-merge join requires. For dimension tables under ~100MB (or a few million rows), this is almost always the right call. Spark's auto-broadcast threshold is typically 10MB — you'll need to force it manually for larger dimensions.

2. Cache dimensions before joining multiple fact tables. If your notebook runs three different gold-layer materializations against the same dimension tables, cache them:

df_products.cache()
df_customers.cache()

# Force the cache to materialize
df_products.count()
df_customers.count()

Without caching, Spark will re-read the remote Delta path for each job that uses those DataFrames. With caching, it reads once and holds in executor memory.

3. Partition the fact table reads. If your silver transaction table is partitioned by date, pass a filter before you join:

from pyspark.sql.functions import to_date

# Only process last 30 days — partition pruning will limit files read
df_transactions = (
    spark.read.format("delta")
    .load(path_transactions)
    .filter(col("transaction_date") >= "2024-11-01")
)

Delta's transaction log allows Spark to identify exactly which Parquet files contain data for a given partition range. This is called partition pruning, and it means you're reading a fraction of the total table rather than scanning it end-to-end.

---

Writing to the Gold Layer

You have three realistic options for writing the gold DataFrame back to a Delta table: full overwrite, append, and MERGE. Which one you use depends on whether the gold layer is a snapshot or an accumulation, and whether you're running incrementally or doing a full refresh.

Option 1: Full Overwrite (Simplest, Best for Small-to-Medium Gold Tables)

path_gold = get_onelake_path(
    workspace_name=WORKSPACE_GOLD,
    item_name=LAKEHOUSE_GOLD,
    item_type="Lakehouse",
    table_name=TABLE_GOLD
)

(
    df_gold
    .write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .save(path_gold)
)

print(f"Gold table written to: {path_gold}")

Full overwrite is idempotent — run it ten times and you get the same result. It's the right choice when your gold table is a complete rebuild every run, and when the total volume is manageable (typically under a few hundred GB). The overwriteSchema option is important: without it, Spark will refuse to overwrite if you've added or removed columns since the table was first created.

The downside is that while the overwrite is happening, the table is in a partially-written state. Delta's transaction log ensures atomicity — readers see either the old complete table or the new complete table, never a partial state. But the operation can be slow for large tables.

Option 2: Partitioned Overwrite (Better for Date-Partitioned Gold Tables)

If your gold table is partitioned by transaction_date, you can overwrite only the partitions you're rebuilding in this run:

spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")

(
    df_gold
    .write
    .format("delta")
    .mode("overwrite")
    .partitionBy("transaction_date")
    .save(path_gold)
)

With partitionOverwriteMode set to dynamic, Spark overwrites only the partitions present in df_gold. Other partitions in the gold table are left untouched. This is much more efficient when you're doing an incremental refresh — you process the last 7 days of transactions, write only those partitions, and the historical data stays in place.

Option 3: MERGE (Most Flexible, Required for True Upsert Semantics)

For writing data with append, overwrite, and merge patterns, MERGE is the most powerful option. Use it when:

• You need to update existing gold rows when source data changes (corrections, late-arriving facts)
• You want to insert new rows and update changed rows atomically
• The gold table is large enough that a full rebuild is prohibitively expensive

from delta.tables import DeltaTable

# Check if the gold table already exists
if DeltaTable.isDeltaTable(spark, path_gold):
    gold_table = DeltaTable.forPath(spark, path_gold)
    
    (
        gold_table.alias("existing")
        .merge(
            df_gold.alias("incoming"),
            condition="existing.transaction_id = incoming.transaction_id"
        )
        .whenMatchedUpdate(set={
            # Update dimension attributes if product/customer data changed
            "customer_segment":    "incoming.customer_segment",
            "customer_region":     "incoming.customer_region",
            "is_loyalty_member":   "incoming.is_loyalty_member",
            "product_name":        "incoming.product_name",
            "product_category":    "incoming.product_category",
            "product_subcategory": "incoming.product_subcategory",
            "brand":               "incoming.brand",
            "discount_ratio":      "incoming.discount_ratio",
            "gold_created_at":     "incoming.gold_created_at"
        })
        .whenNotMatchedInsertAll()
        .execute()
    )
    print("MERGE complete: existing gold table updated.")
    
else:
    # First run — create the table with an initial write
    (
        df_gold
        .write
        .format("delta")
        .mode("overwrite")
        .partitionBy("transaction_date")
        .save(path_gold)
    )
    print("Initial gold table created.")

The MERGE pattern here is doing something subtle and important: when a matched transaction exists, it only updates the dimension-derived attributes (segment, category, brand, etc.) — not the raw fact measures like quantity, unit_price, or total_amount. This is intentional. If the silver layer corrects a transaction's total_amount, that correction should come through as a new version of the transaction row in silver, not as a MERGE update to gold. The gold MERGE only handles late-arriving dimension data.

---

Registering the Gold Table in the Lakehouse Metastore

Writing to a path creates the Delta files, but it doesn't automatically register the table in the Hive metastore that the lakehouse's SQL Analytics Endpoint uses. Without registration, your table won't appear in the lakehouse explorer or be queryable via T-SQL.

You have two options:

Option A: Write directly to a lakehouse-attached notebook path

If your notebook has the gold lakehouse attached as its default lakehouse, you can write using the Tables/ mount path:

# When writing to the current notebook's attached lakehouse
df_gold.write.format("delta").mode("overwrite").saveAsTable("gold_sales")

saveAsTable registers the table in the Hive metastore automatically. The table appears in the lakehouse explorer immediately.

Option B: Register a path-written table manually

If you wrote to an explicit ABFS path (as in the examples above), register it afterward:

spark.sql(f"""
    CREATE TABLE IF NOT EXISTS gold_sales
    USING DELTA
    LOCATION '{path_gold}'
""")

Or if the table already exists at the path but isn't registered:

spark.sql(f"REFRESH TABLE gold_sales")

---

Putting It All Together: The Complete Notebook

Here's the fully assembled notebook, structured as a production artifact with proper parameterization and error handling:

# Cell 1: Parameters (can be overridden by a pipeline)
WORKSPACE_TRANSACTIONS = "DataEngineering"
LAKEHOUSE_TRANSACTIONS = "SilverLakehouse"
TABLE_TRANSACTIONS     = "fact_transactions_silver"
DATE_FILTER_START      = "2024-01-01"  # Set to None for full load

WORKSPACE_PRODUCTS     = "ProductMaster"
WAREHOUSE_PRODUCTS     = "ProductWarehouse"
TABLE_PRODUCTS         = "dim_product"

WORKSPACE_CUSTOMERS    = "CRM"
LAKEHOUSE_CUSTOMERS    = "CRMLakehouse"
TABLE_CUSTOMERS        = "dim_customer"

WORKSPACE_GOLD         = "DataEngineering"
LAKEHOUSE_GOLD         = "GoldLakehouse"
TABLE_GOLD             = "gold_sales"

# Cell 2: Imports and path utilities
from pyspark.sql.functions import col, current_timestamp, lit, coalesce, broadcast
from delta.tables import DeltaTable
import mssparkutils

def _resolve_workspace_id(workspace_name: str) -> str:
    workspaces = mssparkutils.fabric.getWorkspaces()
    for ws in workspaces:
        if ws.displayName == workspace_name:
            return ws.id
    raise ValueError(f"Workspace '{workspace_name}' not found or not accessible.")

def _resolve_item_id(workspace_id: str, item_name: str, item_type: str) -> str:
    items = mssparkutils.fabric.getArtifacts(workspace_id)
    for item in items:
        if item.displayName == item_name and item.type == item_type:
            return item.id
    raise ValueError(f"Item '{item_name}' ({item_type}) not found in workspace {workspace_id}.")

def get_onelake_path(workspace_name, item_name, item_type="Lakehouse", table_name=None, schema="dbo"):
    workspace_id = _resolve_workspace_id(workspace_name)
    item_id = _resolve_item_id(workspace_id, item_name, item_type)
    base = f"abfss://{workspace_id}@onelake.dfs.fabric.microsoft.com/{item_id}"
    if item_type == "Lakehouse":
        return f"{base}/Tables/{table_name}"
    elif item_type == "Warehouse":
        return f"{base}/{schema}/{table_name}"
    raise ValueError(f"Unknown item type: {item_type}")

# Cell 3: Load source tables
path_transactions = get_onelake_path(WORKSPACE_TRANSACTIONS, LAKEHOUSE_TRANSACTIONS, "Lakehouse", TABLE_TRANSACTIONS)
path_products     = get_onelake_path(WORKSPACE_PRODUCTS, WAREHOUSE_PRODUCTS, "Warehouse", TABLE_PRODUCTS)
path_customers    = get_onelake_path(WORKSPACE_CUSTOMERS, LAKEHOUSE_CUSTOMERS, "Lakehouse", TABLE_CUSTOMERS)
path_gold         = get_onelake_path(WORKSPACE_GOLD, LAKEHOUSE_GOLD, "Lakehouse", TABLE_GOLD)

df_txn = spark.read.format("delta").load(path_transactions)
if DATE_FILTER_START:
    df_txn = df_txn.filter(col("transaction_date") >= DATE_FILTER_START)

df_prod = spark.read.format("delta").load(path_products).filter(col("is_active") == True)
df_cust = spark.read.format("delta").load(path_customers)

# Cache dimensions — they're reused in the join
df_prod.cache(); df_cust.cache()
_ = df_prod.count(); _ = df_cust.count()

print(f"Transactions: {df_txn.count():,} | Products: {df_prod.count():,} | Customers: {df_cust.count():,}")

# Cell 4: Build gold DataFrame
df_gold = (
    df_txn.alias("txn")
    .join(broadcast(df_prod).alias("prod"), col("txn.product_id") == col("prod.product_id"), "left")
    .join(broadcast(df_cust).alias("cust"), col("txn.customer_id") == col("cust.customer_id"), "left")
    .select(
        col("txn.transaction_id"),
        col("txn.transaction_date"),
        col("txn.store_region"),
        col("txn.customer_id"),
        coalesce(col("cust.customer_segment"), lit("Unknown")).alias("customer_segment"),
        coalesce(col("cust.customer_region"), lit("Unknown")).alias("customer_region"),
        coalesce(col("cust.is_loyalty_member"), lit(False)).alias("is_loyalty_member"),
        col("txn.product_id"),
        coalesce(col("prod.product_name"), lit("Unknown Product")).alias("product_name"),
        coalesce(col("prod.product_category"), lit("Uncategorized")).alias("product_category"),
        coalesce(col("prod.product_subcategory"), lit("Uncategorized")).alias("product_subcategory"),
        coalesce(col("prod.brand"), lit("Unknown")).alias("brand"),
        col("txn.quantity"),
        col("txn.unit_price"),
        col("txn.total_amount"),
        (col("txn.unit_price") / col("prod.list_price")).alias("discount_ratio"),
        current_timestamp().alias("gold_created_at"),
        lit("cross_workspace_join_notebook").alias("gold_source_process")
    )
)

print(f"Gold rows to write: {df_gold.count():,}")

# Cell 5: Write to gold layer with MERGE
if DeltaTable.isDeltaTable(spark, path_gold):
    gold_dt = DeltaTable.forPath(spark, path_gold)
    (
        gold_dt.alias("existing")
        .merge(df_gold.alias("incoming"), "existing.transaction_id = incoming.transaction_id")
        .whenMatchedUpdate(set={
            "customer_segment": "incoming.customer_segment",
            "customer_region":  "incoming.customer_region",
            "product_name":     "incoming.product_name",
            "product_category": "incoming.product_category",
            "discount_ratio":   "incoming.discount_ratio",
            "gold_created_at":  "incoming.gold_created_at"
        })
        .whenNotMatchedInsertAll()
        .execute()
    )
    print("MERGE into gold table complete.")
else:
    df_gold.write.format("delta").mode("overwrite").partitionBy("transaction_date").save(path_gold)
    print("Gold table created (initial load).")

---

Governance and Security Considerations

Cross-workspace reads have security implications you need to think through carefully. This is covered in depth in Securing and Governing Microsoft Fabric: Workspace Roles, Item Permissions, and OneLake Data Access, but here's what matters specifically for this notebook pattern:

Identity used for reads: When you run this notebook interactively, OneLake authorizes your reads using your Entra ID token. When a pipeline triggers the notebook, the pipeline runs under the workspace identity — or a service principal if you've configured one. The service principal must have Viewer access (minimum) to every source workspace, and Contributor access to the gold workspace to write.

Workspace roles vs. item permissions: Workspace Viewer gives you read access to all items in that workspace. If a team manages sensitive data in the same workspace as the items you need, they may not want to grant you Workspace Viewer. In that case, use OneLake item-level permissions to grant read access to specific lakehouse or warehouse items without exposing the entire workspace.

Row-level security caveat: OneLake ABFS reads bypass any Row-Level Security (RLS) policies defined on the SQL Analytics Endpoint or Warehouse. When you use spark.read.format("delta").load(path), you're reading raw Parquet files — there's no SQL layer to enforce RLS. If the dim_customer table has RLS policies that restrict which customer segments a given user can see, those policies do NOT apply to direct Delta reads. Design your access model accordingly, or use the SQL Analytics Endpoint as the access layer when you need RLS enforcement.

---

Hands-On Exercise

Work through this scenario to reinforce the lesson:

1. Create three workspaces in your Fabric trial: Exercise_DataEng, Exercise_Finance, and Exercise_HR.

2. Create a silver lakehouse in Exercise_DataEng with a Delta table called fact_employee_sales. Use this schema: employee_id (string), product_id (string), sale_date (date), quantity (integer), revenue (double).

3. Create a warehouse in Exercise_Finance with a Delta table dim_product containing: product_id (string), product_name (string), category (string), margin_pct (double).

4. Create a lakehouse in Exercise_HR with a Delta table dim_employee containing: employee_id (string), employee_name (string), department (string), region (string).

5. Write a notebook in Exercise_DataEng that:

   • Uses get_onelake_path to build paths for all three source tables
   • Reads each table with spark.read.format("delta")
   • Joins all three into a gold DataFrame that includes employee_name, department, region, product_name, category, a calculated gross_margin column (revenue * margin_pct), and sale_date
   • Writes the result to a gold_employee_sales table in a gold lakehouse in Exercise_DataEng

6. Verify that the table appears in the gold lakehouse explorer and is queryable via the SQL Analytics Endpoint.

7. Deliberately introduce a path error (swap a GUID or misspell a table name) and observe the error message. Practice reading the ABFS error to diagnose whether it's a permissions issue vs. a path-not-found issue.

---

Common Mistakes & Troubleshooting

"Path does not exist" on a cross-workspace read

This is the most common error. It can mean three things:

• The ABFS path is wrong (wrong workspace GUID, wrong item GUID, or wrong table name/schema)
• The execution identity doesn't have access to that workspace or item
• The table doesn't exist at the Tables/<name> location (maybe it was written to Files/ instead)

Diagnose: Print the path string before reading and manually verify it by navigating to the OneLake explorer in the Fabric portal. You can also try mssparkutils.fs.ls(path) to list the directory contents and confirm the _delta_log folder is present.

Empty DataFrame after join

If the join returns zero rows, check whether your join keys have mismatched data types or trailing whitespace. A product_id stored as "P001 " (with a trailing space) won't match "P001". Use .trim() on join keys during the select phase.

MERGE fails with "schema mismatch"

This happens when the gold table's existing schema differs from the incoming DataFrame's schema — for example, if a new column was added to silver. Strategies: use MERGE only with whenNotMatchedInsertAll() and explicit whenMatchedUpdate set clauses (which lets you ignore new columns in the merge condition), or rebuild with overwrite and overwriteSchema = true, then re-enable MERGE on subsequent runs.

Broadcast join OOM (out of memory)

If you force a broadcast join on a dimension table that's larger than you expected, executor memory pressure can cause OOM failures. The symptom is a cryptic Java heap error in the Spark logs. Fix: remove the broadcast() hint and let Spark choose sort-merge join. Alternatively, increase executor memory by choosing a larger Spark node tier in the lakehouse settings.

Warehouse Delta path returns wrong schema

Warehouse tables in OneLake are read as Delta format, but if the warehouse has schema evolution or views involved, you might read a different schema than you expect from the SQL interface. Always call .printSchema() on the warehouse DataFrame immediately after reading to verify column names and types.

Cross-workspace MERGE conflict errors

If two notebooks simultaneously try to MERGE into the same gold table, Delta's optimistic concurrency control will fail one of them with a ConcurrentModificationException. Solve this with pipeline orchestration — use a Fabric data pipeline to sequence the notebook runs and avoid concurrent writes to the same Delta table. Orchestrating Loads with Fabric Data Pipelines: Copy Activities, Parameters, and Schedules covers the patterns for sequencing dependent notebook activities.

---

Summary & Next Steps

You've built a complete, production-grade pattern for reading Delta tables from multiple Fabric workspaces — across lakehouses and warehouses — joining them in a single Spark notebook, and writing the results to a gold-layer Delta table. Let's consolidate what you know:

• OneLake ABFS paths follow a deterministic abfss://<workspace-guid>@onelake.dfs.fabric.microsoft.com/<item-guid>/Tables/<table> structure for lakehouses, and .../<item-guid>/<schema>/<table> for warehouses.
• mssparkutils.fabric lets you resolve GUIDs programmatically by display name, making your notebooks resilient to infrastructure changes.
• All Delta reads are direct file reads — they bypass SQL-layer RLS and go through OneLake's identity-based authorization.
• Broadcast joins are essential for dimension tables; cache dimensions when they're used in multiple joins.
• MERGE is the right write pattern for gold tables that accumulate over time; full overwrite is simpler for full-rebuild gold tables.
• The gold lakehouse's SQL Analytics Endpoint makes your output immediately queryable for T-SQL and Power BI without any additional configuration — provided the table is in the Tables/ directory.

The natural next step is to make this notebook run automatically. You can call it from a Fabric data pipeline with notebook parameters for DATE_FILTER_START, enabling daily incremental loads. Once the gold table is stable, connect a Power BI semantic model to it in Direct Lake mode for zero-copy, high-performance reporting — Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery walks through exactly how to set that up.

If you find that your gold table is growing large and query performance is degrading, apply Delta maintenance operations: OPTIMIZE, Z-Order on your most common filter columns, and VACUUM to remove old file versions. The principles and commands are covered in Optimizing Delta Table Performance in a Fabric Lakehouse: V-Order, OPTIMIZE, VACUUM, and Z-Order for Faster Queries and Smaller Storage.