Building a Star Schema in a Fabric Lakehouse Gold Layer: Creating Dimension and Fact Delta Tables with PySpark for Direct Lake Reporting

Introduction

You've landed cleaned, conformed data in your Silver layer. Your Bronze tables are ingesting raw files, your Silver transformations are running on schedule, and now you're staring at the final challenge: producing a Gold layer that Power BI can consume at blazing speed without copying data into an import model. That's exactly the problem a star schema in your Fabric Lakehouse Gold layer solves — and it's where your medallion architecture finally pays its dividend.

The Gold layer isn't just "another table." It's a deliberate, structured data model: a constellation of dimension tables and fact tables, designed so that Power BI's Direct Lake engine can scan Delta Parquet files directly from OneLake without an intermediary semantic cache. Get this layer right and your reports refresh in seconds, your semantic model stays lean, and your analysts can write DAX measures against a model that actually makes sense. Get it wrong and you end up with one monstrous denormalized table that's impossible to maintain and brutal for the query engine to scan.

By the end of this lesson, you'll know exactly how to design and build a production-quality star schema in your Gold layer using PySpark inside a Fabric Notebook, write each dimension and fact table as an optimized Delta table, and wire it up for Direct Lake reporting in Power BI.

What you'll learn:

• How to design a star schema for a real retail sales scenario, including surrogate key strategy
• How to build dimension tables (dim_customer, dim_product, dim_date) as Delta tables using PySpark
• How to build a fact table (fact_sales) that joins to dimensions via surrogate keys
• How to write, partition, and optimize these tables for Direct Lake performance
• How to validate the schema before connecting Power BI in Direct Lake mode

---

Prerequisites

You should be comfortable with the following before working through this lesson:

• You have a Fabric-enabled workspace and an existing Lakehouse. If you're starting from scratch, see Building Your First Lakehouse in Microsoft Fabric: Files, Tables, and the SQL Analytics Endpoint.
• You understand the medallion architecture and where Gold fits. The concepts are covered in Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers.
• You've written PySpark in a Fabric Notebook before. If Delta table write patterns are unfamiliar, review Writing Data from a Spark Notebook to a Fabric Lakehouse Delta Table: Append, Overwrite, and Merge Patterns with PySpark.
• You have a basic understanding of how Direct Lake mode works. The mechanics are explained in Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery.

---

Why Star Schemas Still Matter in a Lakehouse

There's a school of thought that says: "Just dump everything into a big flat table and let the query engine handle it." And for exploration, that's fine. But for reporting — especially with Direct Lake — the star schema still wins, for reasons that are as relevant in 2024 as they were in the Kimball era.

Cardinality and scan efficiency. Direct Lake reads column groups directly from Delta Parquet files. A well-designed fact table with integer surrogate keys is far cheaper to scan than one with wide string columns repeated millions of times. When Power BI evaluates a DAX filter on dim_product[category], it identifies matching surrogate keys in the small dimension file and uses them to filter the large fact table — a fundamentally cheaper operation than scanning a string column across 50 million rows.

Model clarity. Analysts writing DAX measure against a star schema have a clear mental model: dimensions describe things, facts measure events. When you flatten everything into one table, you force analysts to understand physical layout choices that should be invisible to them.

Maintainability. When a product category name changes, you update one row in dim_product. In a flat table, you run an update across millions of fact rows.

---

Designing the Schema: A Retail Sales Scenario

We'll work with a realistic retail scenario. Your company sells products across multiple store regions. Each sale transaction records what was sold, to whom, where, and when. Your Silver layer has already cleaned and conformed the raw data — now we need to model it.

Here's our target star schema:

Fact table:

• fact_sales — one row per order line item, containing measures (quantity, unit price, discount, net revenue) and foreign keys to dimensions

Dimension tables:

• dim_customer — customer demographics and segments
• dim_product — product hierarchy (product → subcategory → category)
• dim_store — store location and region
• dim_date — calendar attributes (day, week, month, quarter, fiscal period)

This is a classic Kimball-style star. fact_sales sits in the center; the four dimensions radiate outward. Power BI will build relationships between the fact table's foreign keys and each dimension's surrogate primary key.

Surrogate Key Strategy

Your Silver layer data has natural business keys — customer_id from your CRM, product_sku from your product catalog. These are fine for joining data operationally, but they make bad dimension primary keys for two reasons: they can change, and they expose business logic in your model.

We'll generate integer surrogate keys in PySpark using monotonically_increasing_id() — but we'll do it safely. The raw monotonically_increasing_id() function produces non-sequential integers across partitions, which is fine for uniqueness but suboptimal for the kind of range-scan performance Direct Lake benefits from. We'll use a row_number() window function instead, which gives us clean sequential integers starting from 1.

---

Setting Up the Notebook

In your Fabric workspace, create a new Notebook and attach it to your Gold Lakehouse. If you're keeping your medallion layers in separate Lakehouses (recommended for access control), attach the notebook to your Gold Lakehouse and read Silver data using the abfss:// path or a shortcut.

Start with common imports in your first cell:

from pyspark.sql import functions as F
from pyspark.sql.window import Window
from pyspark.sql.types import (
    StructType, StructField, IntegerType, StringType,
    DecimalType, DateType, BooleanType, LongType
)
from delta.tables import DeltaTable
from datetime import date, timedelta
import pandas as pd

# Define your Gold Lakehouse path
GOLD_PATH = "Tables"  # When attached to Gold Lakehouse, 'Tables/' resolves to managed Delta tables

# Silver Lakehouse ABFSS path (if reading cross-lakehouse)
SILVER_ABFSS = "abfss://<workspace-id>@onelake.dfs.fabric.microsoft.com/<silver-lakehouse-id>/Tables"

When your notebook is attached to the Gold Lakehouse, writing to Tables/dim_customer automatically registers that Delta table in the Lakehouse's metastore — meaning it appears in the Lakehouse Explorer and is immediately queryable through the SQL Analytics Endpoint and by Power BI's Direct Lake engine.

---

Building the Date Dimension

The date dimension is special: it doesn't come from any Silver table. You generate it programmatically to cover your full reporting range. This is one of the most reused pieces of code in any lakehouse project, so it's worth getting right once.

def build_dim_date(start_date: str, end_date: str) -> None:
    """
    Generate a complete date dimension covering the given range.
    Writes to the Gold Lakehouse as a managed Delta table.
    """
    start = pd.to_datetime(start_date)
    end = pd.to_datetime(end_date)
    date_range = pd.date_range(start=start, end=end, freq='D')

    rows = []
    for d in date_range:
        fiscal_year = d.year if d.month >= 7 else d.year - 1  # July fiscal year start
        fiscal_quarter = ((d.month - 7) % 12 // 3) + 1

        rows.append({
            "date_key":         int(d.strftime("%Y%m%d")),   # Integer YYYYMMDD — compact, sortable
            "full_date":        d.date(),
            "day_of_week":      d.day_name(),
            "day_of_week_num":  d.dayofweek + 1,             # 1=Monday, 7=Sunday
            "is_weekend":       d.dayofweek >= 5,
            "day_of_month":     d.day,
            "day_of_year":      d.dayofyear,
            "week_of_year":     int(d.strftime("%V")),
            "month_num":        d.month,
            "month_name":       d.month_name(),
            "month_short":      d.strftime("%b"),
            "quarter":          d.quarter,
            "quarter_label":    f"Q{d.quarter}",
            "year":             d.year,
            "year_month":       int(d.strftime("%Y%m")),
            "fiscal_year":      fiscal_year,
            "fiscal_quarter":   fiscal_quarter,
            "fiscal_year_label": f"FY{fiscal_year}",
        })

    pdf = pd.DataFrame(rows)
    df = spark.createDataFrame(pdf)

    (df.write
       .format("delta")
       .mode("overwrite")
       .option("overwriteSchema", "true")
       .saveAsTable("dim_date"))

    print(f"dim_date written: {df.count()} rows from {start_date} to {end_date}")

# Generate dates covering your full historical range plus 2 future years
build_dim_date("2019-01-01", "2026-12-31")

Notice we're using an integer date_key in YYYYMMDD format rather than the date itself as the primary key. This is a deliberate choice: integer joins in Parquet are faster than date joins, and the YYYYMMDD format is human-readable in query results. Power BI handles the date formatting on its end.

---

Building the Customer Dimension

The customer dimension comes from your Silver layer's silver_customers table. We'll read it, apply business logic, generate surrogate keys, and write to Gold.

def build_dim_customer() -> None:
    """
    Build dim_customer from the Silver customers table.
    Applies surrogate key generation and customer segmentation logic.
    Full overwrite — assumes Silver is the system of record.
    """
    # Read from Silver (adjust path to match your workspace)
    silver_customers = spark.read.format("delta").load(f"{SILVER_ABFSS}/silver_customers")

    # Define surrogate key window — order by natural key for deterministic assignment
    window_spec = Window.orderBy("customer_id")

    dim_customer = (
        silver_customers
        .filter(F.col("is_active") == True)  # Only active customers in dimension
        .select(
            F.row_number().over(window_spec).alias("customer_key"),  # Surrogate PK
            F.col("customer_id").alias("customer_bk"),               # Business key — keep for traceability
            F.col("first_name"),
            F.col("last_name"),
            F.concat(F.col("first_name"), F.lit(" "), F.col("last_name")).alias("full_name"),
            F.col("email"),
            F.col("city"),
            F.col("state"),
            F.col("country"),
            F.col("postal_code"),
            F.col("date_of_birth"),
            F.col("loyalty_tier"),
            # Derive age bucket from date_of_birth
            F.when(F.datediff(F.current_date(), F.col("date_of_birth")) / 365 < 25, "18-24")
             .when(F.datediff(F.current_date(), F.col("date_of_birth")) / 365 < 35, "25-34")
             .when(F.datediff(F.current_date(), F.col("date_of_birth")) / 365 < 45, "35-44")
             .when(F.datediff(F.current_date(), F.col("date_of_birth")) / 365 < 55, "45-54")
             .otherwise("55+").alias("age_bucket"),
            F.col("registration_date"),
            F.col("_silver_updated_at").alias("dim_last_updated"),
        )
    )

    (dim_customer.write
                 .format("delta")
                 .mode("overwrite")
                 .option("overwriteSchema", "true")
                 .saveAsTable("dim_customer"))

    count = dim_customer.count()
    print(f"dim_customer written: {count:,} rows")

build_dim_customer()

A few things worth unpacking here. First, we keep the business key (customer_bk) alongside the surrogate key. This isn't redundant — it's essential for debugging, for loading fact data, and for re-running dimension builds that need to re-establish the mapping. Second, we derive age_bucket in the Gold layer rather than Silver. Silver should preserve source fidelity; Gold is where business-defined categorizations live.

If you need to track historical changes — for example, when a customer changes their loyalty tier — that's a Slowly Changing Dimension Type 2 problem, which requires MERGE rather than overwrite. That's covered in depth in Implementing Slowly Changing Dimensions in a Fabric Lakehouse: Using PySpark and Delta Lake MERGE to Track Historical Changes Across Medallion Layers. For this lesson, we're building a Type 1 (overwrite on change) dimension.

---

Building the Product Dimension

Products have a hierarchy: SKU → subcategory → category. We want all three levels flattened into a single wide dimension row — the standard Kimball approach that keeps joins simple for Power BI.

def build_dim_product() -> None:
    """
    Build dim_product with flattened product hierarchy.
    Reads from silver_products, silver_subcategories, silver_categories.
    """
    silver_products      = spark.read.format("delta").load(f"{SILVER_ABFSS}/silver_products")
    silver_subcategories = spark.read.format("delta").load(f"{SILVER_ABFSS}/silver_subcategories")
    silver_categories    = spark.read.format("delta").load(f"{SILVER_ABFSS}/silver_categories")

    # Join hierarchy levels
    product_hierarchy = (
        silver_products.alias("p")
        .join(
            silver_subcategories.alias("sc"),
            F.col("p.subcategory_id") == F.col("sc.subcategory_id"),
            "left"
        )
        .join(
            silver_categories.alias("cat"),
            F.col("sc.category_id") == F.col("cat.category_id"),
            "left"
        )
    )

    window_spec = Window.orderBy("p.product_sku")

    dim_product = product_hierarchy.select(
        F.row_number().over(window_spec).alias("product_key"),
        F.col("p.product_sku").alias("product_bk"),
        F.col("p.product_name"),
        F.col("p.brand"),
        F.col("p.unit_cost"),
        F.col("p.list_price"),
        F.col("p.weight_kg"),
        F.col("p.is_active").alias("is_currently_active"),
        # Subcategory level
        F.col("sc.subcategory_id"),
        F.col("sc.subcategory_name"),
        # Category level
        F.col("cat.category_id"),
        F.col("cat.category_name"),
        F.col("cat.department"),                  # Top of hierarchy
    )

    (dim_product.write
                .format("delta")
                .mode("overwrite")
                .option("overwriteSchema", "true")
                .saveAsTable("dim_product"))

    count = dim_product.count()
    print(f"dim_product written: {count:,} rows")

build_dim_product()

---

Building the Store Dimension

def build_dim_store() -> None:
    """
    Build dim_store from silver_stores.
    Includes geographic hierarchy and store attributes.
    """
    silver_stores = spark.read.format("delta").load(f"{SILVER_ABFSS}/silver_stores")

    window_spec = Window.orderBy("store_code")

    dim_store = silver_stores.select(
        F.row_number().over(window_spec).alias("store_key"),
        F.col("store_code").alias("store_bk"),
        F.col("store_name"),
        F.col("store_type"),           # e.g. 'flagship', 'outlet', 'online'
        F.col("open_date"),
        F.col("close_date"),
        F.col("is_active"),
        F.col("address_line1"),
        F.col("city"),
        F.col("state"),
        F.col("country"),
        F.col("region"),               # e.g. 'Northeast', 'EMEA'
        F.col("district"),
        F.col("square_footage"),
        F.col("manager_name"),
    )

    (dim_store.write
              .format("delta")
              .mode("overwrite")
              .option("overwriteSchema", "true")
              .saveAsTable("dim_store"))

    count = dim_store.count()
    print(f"dim_store written: {count:,} rows")

build_dim_store()

---

Building the Fact Table

Now for the centerpiece. fact_sales joins to each dimension using its surrogate key. The trick here is the lookup join: we read each dimension back from Gold, select only the surrogate key and business key, and join them against the Silver transaction data.

This is the correct pattern. Never hardcode surrogate keys in fact loading logic — always derive them by joining to the dimension. That way, if you ever regenerate a dimension (changing which customers are active, for example), you rerun the fact load and the keys stay consistent.

def build_fact_sales() -> None:
    """
    Build fact_sales by joining Silver transactions to Gold dimension tables.
    Uses surrogate key lookups to replace business keys with dimension FKs.
    Full overwrite pattern — assumes Silver is complete and correct.
    """

    # Read Silver transaction data
    silver_sales = spark.read.format("delta").load(f"{SILVER_ABFSS}/silver_sales")

    # Read dimension surrogate key lookups (only the columns we need for the join)
    lkp_customer = (spark.read.format("delta").table("dim_customer")
                    .select("customer_key", "customer_bk"))

    lkp_product  = (spark.read.format("delta").table("dim_product")
                    .select("product_key", "product_bk"))

    lkp_store    = (spark.read.format("delta").table("dim_store")
                    .select("store_key", "store_bk"))

    # Prepare the date key join — derive integer date key from transaction date
    silver_sales_with_date_key = silver_sales.withColumn(
        "date_key",
        F.date_format(F.col("transaction_date"), "yyyyMMdd").cast(IntegerType())
    )

    # Join all dimension surrogate keys
    fact = (
        silver_sales_with_date_key.alias("s")
        .join(lkp_customer.alias("c"),
              F.col("s.customer_id") == F.col("c.customer_bk"), "left")
        .join(lkp_product.alias("p"),
              F.col("s.product_sku") == F.col("p.product_bk"), "left")
        .join(lkp_store.alias("st"),
              F.col("s.store_code") == F.col("st.store_bk"), "left")
        .select(
            # Surrogate foreign keys
            F.col("c.customer_key"),
            F.col("p.product_key"),
            F.col("st.store_key"),
            F.col("s.date_key"),

            # Degenerate dimensions (stored on fact, don't need their own dim table)
            F.col("s.order_id"),
            F.col("s.order_line_num"),
            F.col("s.sales_channel"),      # 'in-store', 'online', 'wholesale'
            F.col("s.promotion_code"),

            # Measures — use Decimal for currency to avoid float precision errors
            F.col("s.quantity_sold").cast(IntegerType()),
            F.col("s.unit_price").cast(DecimalType(12, 2)),
            F.col("s.discount_amount").cast(DecimalType(12, 2)),
            F.col("s.net_revenue").cast(DecimalType(14, 2)),
            F.col("s.cost_of_goods").cast(DecimalType(12, 2)),
            (F.col("s.net_revenue") - F.col("s.cost_of_goods")).cast(DecimalType(14, 2)).alias("gross_profit"),

            # Audit columns
            F.col("s.transaction_date"),
            F.col("s._silver_updated_at").alias("fact_last_updated"),
        )
    )

    # Validate: warn if any dimension joins produced nulls (orphaned facts)
    orphaned_customers = fact.filter(F.col("customer_key").isNull()).count()
    orphaned_products  = fact.filter(F.col("product_key").isNull()).count()
    orphaned_stores    = fact.filter(F.col("store_key").isNull()).count()

    if orphaned_customers > 0:
        print(f"WARNING: {orphaned_customers:,} fact rows with no matching customer")
    if orphaned_products > 0:
        print(f"WARNING: {orphaned_products:,} fact rows with no matching product")
    if orphaned_stores > 0:
        print(f"WARNING: {orphaned_stores:,} fact rows with no matching store")

    # Write fact table — partition by year_month for efficient incremental loading later
    (fact.write
         .format("delta")
         .mode("overwrite")
         .option("overwriteSchema", "true")
         .partitionBy("date_key")  # WARNING: See note below before using this
         .saveAsTable("fact_sales"))

    count = fact.count()
    print(f"fact_sales written: {count:,} rows")

build_fact_sales()

The orphan validation block is worth keeping in production. When a fact row can't find a matching dimension key, Direct Lake won't crash — Power BI will simply show a blank in the dimension column. But that means your revenue totals are fine while your category slicers don't work. Silent data quality failures are the worst kind, so surface them here.

---

Post-Write Optimization for Direct Lake

After writing your Gold tables, run OPTIMIZE on each to compact small files and apply V-Order encoding. V-Order is a Microsoft-specific Parquet write optimization that dramatically improves read performance in the Direct Lake engine.

def optimize_gold_tables():
    """
    Run OPTIMIZE on all Gold tables to compact files and apply V-Order encoding.
    V-Order is critical for Direct Lake query performance.
    """
    gold_tables = ["dim_date", "dim_customer", "dim_product", "dim_store", "fact_sales"]

    for table_name in gold_tables:
        print(f"Optimizing {table_name}...")
        spark.sql(f"OPTIMIZE {table_name}")
        print(f"  {table_name} optimized.")

    # VACUUM removes old file versions — set to 168 hours (7 days) retention
    # This limits your time-travel window but keeps storage costs manageable
    for table_name in gold_tables:
        spark.sql(f"VACUUM {table_name} RETAIN 168 HOURS")
        print(f"  {table_name} vacuumed.")

optimize_gold_tables()

By default, Fabric Spark clusters write with V-Order enabled. But running OPTIMIZE ensures that any files written before V-Order was configured (or written in multiple small batches) get rewritten into large, V-Order-encoded Parquet files. For your Gold layer, this is non-negotiable if you want Direct Lake to perform well.

---

Verifying the Schema Before Connecting Power BI

Before you wire up the semantic model, validate your schema in the SQL Analytics Endpoint. This is the same interface Direct Lake uses to understand your table structure.

In your Lakehouse, switch to the SQL Analytics Endpoint view. Run these queries to confirm referential integrity:

-- Verify fact table has no orphaned foreign keys
SELECT
    SUM(CASE WHEN c.customer_key IS NULL THEN 1 ELSE 0 END) AS orphaned_customers,
    SUM(CASE WHEN p.product_key  IS NULL THEN 1 ELSE 0 END) AS orphaned_products,
    SUM(CASE WHEN s.store_key    IS NULL THEN 1 ELSE 0 END) AS orphaned_stores,
    SUM(CASE WHEN d.date_key     IS NULL THEN 1 ELSE 0 END) AS orphaned_dates,
    COUNT(*) AS total_fact_rows
FROM fact_sales f
LEFT JOIN dim_customer c ON f.customer_key = c.customer_key
LEFT JOIN dim_product  p ON f.product_key  = p.product_key
LEFT JOIN dim_store    s ON f.store_key    = s.store_key
LEFT JOIN dim_date     d ON f.date_key     = d.date_key;

-- Spot-check a few joined rows to confirm keys resolve correctly
SELECT TOP 5
    f.order_id,
    c.full_name,
    p.product_name,
    p.category_name,
    s.store_name,
    s.region,
    d.full_date,
    d.month_name,
    d.fiscal_year,
    f.quantity_sold,
    f.net_revenue,
    f.gross_profit
FROM fact_sales f
JOIN dim_customer c ON f.customer_key = c.customer_key
JOIN dim_product  p ON f.product_key  = p.product_key
JOIN dim_store    s ON f.store_key    = s.store_key
JOIN dim_date     d ON f.date_key     = d.date_key
ORDER BY f.transaction_date DESC;

If both queries return clean results — no orphans, recognizable dimension values — your Gold layer is ready for Power BI.

For a deeper walkthrough of querying lakehouse tables through the SQL endpoint, see Querying Lakehouse Data with the SQL Analytics Endpoint: Writing T-SQL Against Delta Tables Without a Warehouse.

---

Connecting to Power BI in Direct Lake Mode

With your Gold tables written and optimized, creating a Direct Lake semantic model is straightforward. In your Lakehouse, click New semantic model from the toolbar. Fabric generates a new semantic model item in your workspace, pre-populated with all tables from the Gold Lakehouse.

In the semantic model editor:

1. Add fact_sales, dim_customer, dim_product, dim_store, and dim_date to the model.
2. Create relationships:
   • fact_sales[customer_key] → dim_customer[customer_key] (Many to One)
   • fact_sales[product_key] → dim_product[product_key] (Many to One)
   • fact_sales[store_key] → dim_store[store_key] (Many to One)
   • fact_sales[date_key] → dim_date[date_key] (Many to One)
3. Mark dim_date as the date table using full_date.
4. Hide all surrogate key columns from the report view — they're infrastructure, not something analysts should see.

Because all five tables are Delta tables in the Gold Lakehouse, Power BI reads them directly from OneLake without copying data. Updates to your Gold tables are reflected in reports after a framing operation — no scheduled refresh required beyond that. For a complete walkthrough of the semantic model setup, see Connecting a Power BI Semantic Model to a Fabric Lakehouse in Direct Lake Mode: Creating, Refreshing, and Optimizing Delta Tables for Reporting.

---

Hands-On Exercise

Work through this end-to-end exercise in your own Fabric environment.

Scenario: You have Silver layer data for an e-commerce business: silver_orders, silver_customers, silver_products. Build a Gold star schema with a fact_orders table and three dimensions.

Step 1 — Generate dim_date: Use the build_dim_date function from this lesson. Generate dates from 2020-01-01 through 2027-12-31. After writing, query the table in the SQL Analytics Endpoint and confirm you have 2,922 rows.

Step 2 — Build dim_customer: From silver_customers, select these columns: customer_id (as business key), email, signup_date, country, customer_segment. Add a derived column: is_high_value — set to True where customer_segment == 'Premium'. Generate surrogate keys and write to dim_customer.

Step 3 — Build dim_product: From silver_products, create a dimension with product hierarchy (join silver_products to silver_categories). Add a price_tier derived column: 'Budget' (list_price < 25), 'Mid-range' (25–100), 'Premium' (> 100).

Step 4 — Build fact_orders: Join silver_orders to your three dimensions using surrogate key lookups. Your fact table should contain: customer_key, product_key, date_key, order_id, quantity, unit_price, revenue, discount_pct. Validate for orphans after writing.

Step 5 — Optimize and validate: Run OPTIMIZE and VACUUM on all four tables. Confirm row counts in the SQL Analytics Endpoint. Run a JOIN query across all four tables to verify that a sample of 10 orders shows correct customer names, product names, and calendar attributes.

Step 6 — Connect to Power BI: Create a Direct Lake semantic model. Build relationships. Create one DAX measure: [Total Revenue] = SUM(fact_orders[revenue]). Build a matrix visual sliced by dim_date[month_name] and dim_product[price_tier].

---

Common Mistakes & Troubleshooting

Surrogate keys aren't stable between runs

Symptom: After rerunning the dimension build, the semantic model shows broken relationships or wrong dimension values on existing reports.

Cause: Using monotonically_increasing_id() or non-deterministic ordering in your row_number() window spec. If you sort by a non-unique column, ties can resolve differently across Spark runs.

Fix: Always order your surrogate key window by the natural business key, which is unique. If your business key isn't unique in the Silver source, that's a data quality issue to fix upstream.

Fact table has millions of orphaned rows

Symptom: Your orphan validation shows large numbers of NULLs for one dimension.

Cause: Your Silver data includes records that don't have a matching entry in the dimension — either because the dimension filters them out (e.g., is_active == True on customers) or because the dimension and fact were built from Silver data at different points in time.

Fix: Either relax the dimension filter, or add a default "Unknown" member to each dimension (with customer_key = -1, customer_name = 'Unknown') and use a coalesce in your fact join to assign orphaned rows to it.

Direct Lake falls back to DirectQuery

Symptom: Power BI Desktop shows "Using DirectQuery" in the status bar when you expected Direct Lake.

Cause: Common triggers include: columns with unsupported data types (e.g., BinaryType, complex MapType), row counts exceeding your capacity tier's Direct Lake limits, or Parquet files not yet V-Order encoded.

Fix: Check your column types — cast anything exotic to supported types (strings, decimals, integers, dates). Run OPTIMIZE with V-Order enabled. If you're on a small F SKU, check the Direct Lake capacity limits for your tier.

OPTIMIZE runs slowly on large fact tables

Symptom: The OPTIMIZE step for fact_sales takes 20+ minutes.

Cause: Your fact table has too many small files (often caused by overpartitioning or incremental appends without periodic compaction).

Fix: If partitioning by date_key, switch to year_month. If the table grew via many small appends, run OPTIMIZE with a ZORDER BY on your most common filter columns (e.g., OPTIMIZE fact_sales ZORDER BY (date_key, product_key)). This co-locates related rows and makes subsequent reads faster.

Schema mismatch errors on overwrite

Symptom: AnalysisException: Failed to merge fields 'unit_cost' and 'unit_cost' (or similar).

Cause: Your Silver schema changed — a column's type evolved — and you're overwriting a Gold table that has the old schema.

Fix: Add .option("overwriteSchema", "true") to your write operation (already included in the code above) to allow schema evolution. For more complex schema migration scenarios, see Handling Schema Evolution in Fabric Lakehouse Delta Tables: Adding Columns, Merging Incompatible Schemas, and Enforcing Constraints Across Medallion Layers.

---

Summary & Next Steps

You've now built a complete, production-oriented star schema in your Fabric Lakehouse Gold layer. Here's what you accomplished:

• Designed a four-dimension star schema around a retail sales fact table, following Kimball modeling principles adapted for Spark and Delta Lake
• Generated a comprehensive date dimension programmatically, including fiscal calendar attributes
• Built customer, product, and store dimensions with surrogate keys derived from deterministic row_number() window functions
• Loaded a fact table using dimension surrogate key lookups — the correct, maintainable pattern
• Added orphan validation to surface silent data quality failures before they reach Power BI
• Applied OPTIMIZE and VACUUM to prepare the Gold tables for Direct Lake's V-Order-powered engine
• Validated referential integrity through the SQL Analytics Endpoint before connecting Power BI

The star schema pattern is the foundation that makes everything else in your reporting layer work well. Direct Lake reads it efficiently because the column groups are narrow and well-encoded. Analysts understand it because the model matches their mental model of the business. Developers maintain it because dimension changes are isolated from fact data.

Where to go next:

• If your dimensions need to track history (customer loyalty tier changes, product reclassifications), build SCD Type 2 logic using Delta MERGE — covered in Implementing Slowly Changing Dimensions in a Fabric Lakehouse: Using PySpark and Delta Lake MERGE to Track Historical Changes Across Medallion Layers.
• If your fact table is large and you want incremental loads rather than full overwrites, learn the watermark pattern in Incrementally Loading Data into a Fabric Lakehouse with Watermarks and Pipeline Lookup Activities.
• If you need to govern who can see which rows (e.g., regional managers only see their region's data), implement row-level security on top of your Gold schema using Implementing Row-Level Security in a Fabric Warehouse and Lakehouse SQL Analytics Endpoint: Dynamic Policies, Workspace Roles, and Testing Access as a Business User.