1) Separate Layers

Use a layered approach inside the Lakehouse:

• Bronze → Raw ingestion (no business logic)

• Silver → Cleaned & transformed tables

• Gold → Analytics-ready tables (star schema)

For reporting efficiency, Power BI should ideally connect to the Gold layer.

2) Always Model as a Star Schema

Even in Lakehouse.

Use:

• Fact tables (transactions, events, metrics)

• Dimension tables (Date, Customer, Product, Region, etc.)

Avoid:

• Snowflake schema unless necessary

• Fact-to-fact relationships

• Many-to-many unless unavoidable

• Bi-directional unless justified

3) Use Surrogate Keys (Not Natural Keys)

In your Gold layer:

• Create integer surrogate keys for dimensions

• Avoid joining on text columns

• Avoid GUID joins if possible

This reduces storage size and improves relationship performance.

4) Watch Cardinality

High-cardinality columns:

• Transaction IDs

• Timestamps at second-level

• Free-text descriptions

Keep them out of dimensions and visuals if not needed.