Designing a Lakehouse Star Schema in Microsoft Fabric for Sales & Finance

1. What Makes a Lakehouse Star Schema Different?

You’re still designing facts and dimensions, but the constraints are different from classic warehouse-on-SQL:

• Storage is open: Delta tables on OneLake, not just SQL tables.
• Multiple engines: SQL Endpoint, Spark, and Power BI all hitting the same objects.
• Medallion mindset: Bronze/Silver/Gold layers instead of one big “EDW” database.

For Sales and Finance, the pattern that works well is:

• Bronze: Raw ERP/CRM exports (CSV, Parquet) – no modeling.
• Silver: Cleaned, conformed tables (still close to source structures).
• Gold: Star schemas (facts and dimensions) optimized for reporting.

This article focuses on Gold and assumes you’ve already done the heavy lifting in Bronze/Silver.

2. Core Design Principles for Fabric Lakehouse Stars

Before we dive into concrete tables, align on a few design rules that work well in Fabric:

2.1 Naming and Layering

Use a predictable, layer-aware naming convention:

• Lakehouse name: lh_sales_finance (project/domain based).
• Schema (or folder) per layer:
  • silver_erp, silver_crm, silver_hr
  • gold_star
• Tables:
  • Facts: f_sales_invoice, f_sales_order, f_gl_balance
  • Dimensions: d_customer, d_product, d_date, d_cost_center

This keeps Fabric’s SQL Endpoint and Power BI dataset views clean and predictable.

2.2 Surrogate Keys and Business Keys

In Fabric Lakehouse, you still want surrogate keys:

• Dimensions: customer_key, product_key, date_key, cost_center_key.
• Facts: Store surrogate keys, plus the business keys for traceability.

Pattern:

• d_customer.customer_key (INT, identity or hash).
• d_customer.customer_code (business key from ERP).
• f_sales_invoice.customer_key + f_sales_invoice.customer_code.

This lets you:

• Keep joins fast and simple.
• Track history and slowly changing dimensions.
• Debug issues with the original business keys.

2.3 Partitioning and Performance

Fabric’s Delta tables support partitioning. For Sales and Finance facts:

• Sales: Partition by invoice_date_key (or invoice_date), monthly.
• GL: Partition by posting_date_key or fiscal_period.

Example partitioning fields:

• partition_year (INT)
• partition_month (INT)

You can derive these in your ETL so queries that filter by date hit fewer files.

3. Canonical Sales Star Schema in a Fabric Lakehouse

Let’s design a realistic Sales star that both Sales and Finance can use.

3.1 Sales Fact Table: f_sales_invoice

Grain: one row per invoice line.

Core columns:

• Keys:
  • sales_invoice_key (surrogate, optional)
  • invoice_line_id (business line ID)
  • invoice_id (business header ID)
  • customer_key, product_key, date_key, sales_org_key, sales_rep_key
• Measures:
  • quantity
  • unit_price
  • gross_amount
  • discount_amount
  • net_amount
  • tax_amount
• Attributes (for filters):
  • currency_code
  • invoice_type
  • is_credit_memo

Example: creating f_sales_invoice in a Fabric Notebook with PySpark:

from pyspark.sql import functions as F

silver_sales = spark.read.table("silver_erp.sales_invoice_lines")

f_sales_invoice = (
    silver_sales
    .withColumn("date_key", F.date_format("invoice_date", "yyyyMMdd").cast("int"))
    .withColumn("partition_year", F.year("invoice_date"))
    .withColumn("partition_month", F.month("invoice_date"))
    .select(
        F.col("invoice_line_id"),
        F.col("invoice_id"),
        F.col("customer_key"),
        F.col("product_key"),
        F.col("sales_org_key"),
        F.col("sales_rep_key"),
        F.col("date_key"),
        F.col("currency_code"),
        F.col("invoice_type"),
        F.col("is_credit_memo"),
        F.col("quantity"),
        F.col("unit_price"),
        F.col("gross_amount"),
        F.col("discount_amount"),
        F.col("net_amount"),
        F.col("tax_amount"),
        F.col("partition_year"),
        F.col("partition_month")
    )
)

(
    f_sales_invoice
    .write
    .format("delta")
    .mode("overwrite")
    .partitionBy("partition_year", "partition_month")
    .saveAsTable("gold_star.f_sales_invoice")
)

3.2 Core Sales Dimensions

d_customer

• Keys:
  • customer_key (INT)
  • customer_code (business key)
• Attributes:
  • customer_name
  • customer_group
  • industry
  • country, city
  • payment_terms
  • is_active

d_product

• Keys:
  • product_key
  • product_code
• Attributes:
  • product_name
  • brand
  • category
  • sub_category
  • uom
  • is_active

d_sales_org / d_sales_rep

• d_sales_org: region, channel, division.
• d_sales_rep: sales person, team, manager.

These are straightforward dimensions, but in Fabric you’ll often build them with Delta merges from Silver.

Example: building d_customer with SQL in a Lakehouse SQL Endpoint:

CREATE OR REPLACE TABLE gold_star.d_customer
USING DELTA
AS
SELECT
    ROW_NUMBER() OVER (ORDER BY customer_code) AS customer_key,
    customer_code,
    customer_name,
    customer_group,
    industry,
    country,
    city,
    payment_terms,
    is_active
FROM silver_erp.customer_master;

For production, switch to MERGE to support slowly changing dimensions.

3.3 Shared Date Dimension: d_date

Re-use the same d_date for Sales and Finance.

Minimal structure:

• date_key (INT, yyyyMMdd)
• date (DATE)
• year, month, day
• month_name, quarter
• fiscal_year, fiscal_period, fiscal_quarter

Fabric Notebook example:

from pyspark.sql import functions as F
from pyspark.sql import types as T

start_date = "2015-01-01"
end_date = "2030-12-31"

dates_df = spark.range(0, F.datediff(F.lit(end_date), F.lit(start_date)) + 1) \
    .withColumn("date", F.expr(f"date_add('{start_date}', id)")) \
    .select("date") \
    .withColumn("date_key", F.date_format("date", "yyyyMMdd").cast("int")) \
    .withColumn("year", F.year("date")) \
    .withColumn("month", F.month("date")) \
    .withColumn("day", F.dayofmonth("date")) \
    .withColumn("month_name", F.date_format("date", "MMM")) \
    .withColumn("quarter", F.quarter("date"))

# Add fiscal logic as needed

(
    dates_df
    .write
    .format("delta")
    .mode("overwrite")
    .saveAsTable("gold_star.d_date")
)

4. Finance Star Schema: GL Fact and Finance Dimensions

Finance usually wants a General Ledger view aligned with Sales.

4.1 GL Fact Table: f_gl_balance

You have two common grains:

1. Transaction-level: one row per GL posting line.
2. Periodic balance: one row per account/cost center/month.

In a Lakehouse, you can support both, but make them separate facts.

4.1.1 f_gl_transaction

Grain: one row per GL posting line.

Columns:

• Keys:
  • gl_trx_id (business ID)
  • account_key
  • cost_center_key
  • date_key
  • company_key
• Measures:
  • debit_amount
  • credit_amount
  • amount_local
  • amount_group
• Attributes:
  • currency_code
  • document_type
  • posting_period

4.1.2 f_gl_balance

Grain: one row per account / cost center / period.

Columns:

• Keys:
  • account_key
  • cost_center_key
  • fiscal_period_key (or date_key representing month end)
  • company_key
• Measures:
  • opening_balance
  • debit_amount
  • credit_amount
  • closing_balance

You can build f_gl_balance by aggregating f_gl_transaction in a scheduled Fabric Dataflow Gen2 or Notebook.

Example SQL aggregation:

CREATE OR REPLACE TABLE gold_star.f_gl_balance
USING DELTA
AS
SELECT
    account_key,
    cost_center_key,
    company_key,
    fiscal_period_key,
    SUM(opening_balance) AS opening_balance,
    SUM(debit_amount) AS debit_amount,
    SUM(credit_amount) AS credit_amount,
    SUM(opening_balance + debit_amount - credit_amount) AS closing_balance
FROM silver_erp.gl_transactions_periodic
GROUP BY
    account_key,
    cost_center_key,
    company_key,
    fiscal_period_key;

4.2 Finance Dimensions

d_account

• Keys:
  • account_key
  • account_code
• Attributes:
  • account_name
  • account_type (P&L, Balance Sheet)
  • account_group (Revenue, COGS, etc.)
  • ifrs_category (if used)

d_cost_center

• Keys:
  • cost_center_key
  • cost_center_code
• Attributes:
  • cost_center_name
  • department
  • cost_center_group

d_company

• Keys:
  • company_key
  • company_code
• Attributes:
  • company_name
  • country
  • currency_code

These dimensions should share conformed attributes with Sales where it makes sense (e.g., same d_company, same d_date).

5. Connecting Sales and Finance in a Single Lakehouse Model

The value of a Lakehouse star is cross-domain analysis: margin by customer, P&L by product, etc.

5.1 Conformed Dimensions

Make these shared between Sales and Finance facts:

• d_date
• d_company
• Potentially d_product (if Finance tracks product-level COGS).

This enables:

• Sales vs. GL revenue reconciliation by month.
• Margin analysis by product, customer, or region.

5.2 Example: Revenue Reconciliation Measure in Power BI

Once your Power BI model is connected to the Fabric Lakehouse (via Direct Lake or import), you can build reconciliation measures.

Sales Revenue :=
SUM ( 'f_sales_invoice'[net_amount] )

GL Revenue :=
CALCULATE (
    SUM ( 'f_gl_transaction'[amount_local] ),
    'd_account'[account_group] = "Revenue"
)

Revenue Difference := [Sales Revenue] - [GL Revenue]

This only works reliably if:

• f_sales_invoice and f_gl_transaction both relate to the same d_date and d_company.
• Currency logic is aligned (either both in local or both in group currency).

6. ETL Patterns: From Silver to Gold in Fabric

You can build Gold stars using either Notebooks or Dataflows Gen2. For Sales and Finance, a hybrid approach works well:

• Use Notebooks for heavy joins, partitioning, and historical loads.
• Use Dataflows Gen2 for simpler incremental updates and business-friendly transformations.

6.1 Incremental Load Pattern for Facts

Typical pattern from silver_erp to gold_star:

1. Identify new/changed records using a last_modified_date in Silver.
2. Load them into a staging Delta table.
3. MERGE into the Gold fact.

Example SQL MERGE for f_sales_invoice:

MERGE INTO gold_star.f_sales_invoice AS tgt
USING staging.f_sales_invoice_delta AS src
ON tgt.invoice_line_id = src.invoice_line_id
WHEN MATCHED THEN UPDATE SET
    tgt.customer_key   = src.customer_key,
    tgt.product_key    = src.product_key,
    tgt.net_amount     = src.net_amount,
    tgt.tax_amount     = src.tax_amount,
    tgt.quantity       = src.quantity,
    tgt.unit_price     = src.unit_price
WHEN NOT MATCHED THEN INSERT (
    invoice_line_id,
    invoice_id,
    customer_key,
    product_key,
    sales_org_key,
    sales_rep_key,
    date_key,
    currency_code,
    invoice_type,
    is_credit_memo,
    quantity,
    unit_price,
    gross_amount,
    discount_amount,
    net_amount,
    tax_amount,
    partition_year,
    partition_month
)
VALUES (
    src.invoice_line_id,
    src.invoice_id,
    src.customer_key,
    src.product_key,
    src.sales_org_key,
    src.sales_rep_key,
    src.date_key,
    src.currency_code,
    src.invoice_type,
    src.is_credit_memo,
    src.quantity,
    src.unit_price,
    src.gross_amount,
    src.discount_amount,
    src.net_amount,
    src.tax_amount,
    src.partition_year,
    src.partition_month
);

Schedule this in a Fabric Pipeline to keep Gold aligned with Silver.

6.2 Handling Slowly Changing Dimensions (SCD)

For Sales and Finance, you usually need SCD Type 2 for:

• d_customer (changes in customer group, payment terms).
• d_cost_center (department changes).

Pattern:

• Add valid_from, valid_to, is_current to dimensions.
• Facts always point to the current dimension key at load time.

In Fabric, implement SCD using MERGE with conditions on business key and effective dates. Keep it simple at first (only track critical attributes) to avoid exploding your dimension size.

7. One Practical Next Step

Pick one fact table (e.g., f_sales_invoice) and two shared dimensions (d_date, d_company) and implement them in your Fabric Lakehouse Gold layer using the patterns above. Once that thin star is stable and used in Power BI, extend it with Finance (f_gl_transaction, d_account, d_cost_center) instead of trying to model the entire Sales and Finance universe in one shot.