Building a Lakehouse in Microsoft Fabric

A Lakehouse in Microsoft Fabric combines the scalability and flexibility of a data lake with the structure and performance of a data warehouse.
It’s an all-in-one approach for storing, processing, and analyzing both structured and unstructured data.

What You’ll Learn

In this guide, we’ll cover:

• The difference between a Lakehouse, Warehouse, and Data Lake
• How to create tables and load data in a Fabric Lakehouse
• Why Delta tables are critical for efficient analytics

Lakehouse vs. Warehouse vs. Data Lake

Before building your Lakehouse, it’s important to understand where it fits in the modern data architecture.

Feature	Data Lake	Warehouse	Lakehouse
Data type	Raw, unstructured/semi-structured (JSON, CSV, Parquet)	Structured, schema-based	Both raw & structured in one place
Storage format	Various (often Parquet/ORC)	Proprietary storage in database	Open Delta format
Processing	Needs ETL for efficient querying	Optimized for SQL queries	SQL + big data processing
Flexibility	High	Low–Medium	High
Best for	Cheap storage & data science	BI & reporting	End-to-end analytics from single storage

Analogy:
Think of the Lakehouse as a bridge between the flexibility of a data lake and the speed of a warehouse allowing both data scientists and business analysts to work from the same platform without moving data between systems.

Creating a Lakehouse in Microsoft Fabric

1. Sign in to Microsoft Fabric and open your workspace.
2. Click New Item → Lakehouse.
3. Name your Lakehouse (e.g., Customer_Insights_Lakehouse).
4. Click Create: your Lakehouse is ready.

Loading Data into a Fabric Lakehouse

You can load data into a Fabric Lakehouse in several ways:

A. Uploading Files Directly

Best for: Small datasets, quick imports.
Steps:

1. In your Lakehouse, go to the Files section.
2. Click the three dots (…) next to Files.
3. Select Upload files.
4. Choose your file (CSV, Parquet, etc.) and click Upload.

B. Using Dataflows Gen2

Best for: Ingesting structured data from various sources into tables.

Steps:

1. Open your Fabric workspace.
2. Click + New > Dataflow Gen2.
3. Select a data source (CSV, Excel, SQL Server, etc.).
4. Load and preview the data.
5. Rename the query to match your desired table name.
6. Click Add destination > Lakehouse.
7. Select your Lakehouse and choose New table.
8. Click Publish and then Refresh to load the data.

Result: A new table is created and visible in Lakehouse Explorer and the SQL endpoint.

C. Using Pipelines

Best for: Multi-step data movement and transformation.

Steps:

1. In your workspace, click + New > Data pipeline.
2. Add a Copy Data activity.
3. Set the source (Azure SQL, Blob Storage, etc.).
4. Set the destination as your Lakehouse table.
5. Map schema and fields.
6. Run the pipeline.

Result: The table is created or updated in the Lakehouse.

D. Creating SQL Tables

Best for: Structured tables managed through T-SQL.

Steps:

1. Open your Lakehouse.
2. Click SQL analytics endpoint.
3. Select New SQL query.
4. Write and run a CREATE TABLE statement.

Notes:

• SQL-created tables support T-SQL queries.
• For Delta tables, use Notebooks with PySpark.

Why Delta Tables Are a Game-Changer

All Fabric Lakehouses store tables in Delta format, an open-source storage layer that adds ACID transactions to Parquet files.

Benefits:

• ACID transactions → Reliable updates and deletes.
• Time travel → Query historical data versions.
• Schema evolution → Add/remove columns without breaking queries.
• Performance → Optimized for large-scale analytics in Spark & SQL.

Creating a Delta Table in Microsoft Fabric Lakehouse

Step 1: Open a Notebook

1. Inside your Lakehouse, click New > Notebook.
2. Name and open your notebook.

Step 2: Create the Delta Table with PySpark

# Sample data

data = [

    ("P001", "Laptop", 1200),

    ("P002", "Monitor", 300),

    ("P003", "Keyboard", 45)

]

# Create DataFrame

columns = ["ProductID", "ProductName", "Price"]

df = spark.createDataFrame(data, columns)

# Save as Delta table

df.write.format("delta").saveAsTable("product_catalog")

Step 3: Verify the Table

• Go to Lakehouse Explorer > Tables > Refresh.
• You should see product_catalog listed.

Step 4: Query the Table (Optional)

# Load and query Delta table

df = spark.read.table("product_catalog")

df.show()

Best Practices for Lakehouse Success

• Organize tables by domain (e.g., Sales, Inventory, Customer).
• Partition large datasets by date or region.
• Run OPTIMIZE regularly on Delta tables.
• Separate raw, curated, and aggregated data for clarity.

Why This Matters

By adopting a Lakehouse in Microsoft Fabric, you:

• Eliminate the complexity of separate raw and structured data stores.
• Enable both data science and BI teams to work on the same platform.
• Gain governance, security, and scalability in one system.