Lakehouse vs Warehouse in Microsoft Fabric: When to Use Which, What Languages Work Where, and Real-World Scenario Guide

You open Microsoft Fabric and see two similar-sounding items: Lakehouse and Warehouse. Both store data. Both support SQL queries. Both write to OneLake. Both show up in Power BI. So what is the difference? When do you create a Lakehouse? When do you create a Warehouse? Can you use both? And where does the mysterious SQL analytics endpoint fit in?

This confusion is the #1 question new Fabric users ask. The short answer: Lakehouse is for data engineering (PySpark, unstructured data, ML). Warehouse is for data analytics (T-SQL, structured data, BI). But the details matter — especially which languages work where, what operations are read-only, and how the SQL analytics endpoint changes the game.

Think of a Lakehouse as a workshop — raw materials everywhere (files, JSON, CSV, images), power tools available (Spark notebooks), workbenches for building things (transformations), and a display window where visitors can see the finished products (SQL analytics endpoint). A Warehouse is a showroom — everything is organized on shelves (tables), labeled (schemas), and customers (analysts) can browse, query, and report easily using familiar tools (T-SQL). You BUILD in the workshop. You SELL from the showroom. Most companies need both.

Table of Contents

• The Quick Comparison Table
• What Is a Lakehouse?
• The Three Interfaces of a Lakehouse
• What Is a Warehouse?
• The SQL Analytics Endpoint (The Confusing Piece)
• Languages and Interfaces: What Works Where
• Read vs Write Capabilities
• Storage Format Differences
• Security Model Differences
• Scenario 1: E-Commerce Daily ETL Pipeline
• Scenario 2: Financial Reporting Platform
• Scenario 3: IoT Sensor Analytics
• Scenario 4: Customer 360 with ML
• Scenario 5: Self-Service Analytics for Business Users
• The Medallion Architecture Pattern in Fabric
• When to Use Lakehouse
• When to Use Warehouse
• When to Use Both (Most Common)
• Cross-Database Queries (Lakehouse + Warehouse Together)
• Migrating from Synapse / Databricks to Fabric
• Common Mistakes
• Interview Questions
• Wrapping Up

The Quick Comparison Table

Feature	Lakehouse	Warehouse
Primary language	PySpark, SparkSQL, Scala, SparkR	T-SQL
Primary users	Data engineers, data scientists	Data analysts, BI developers
Data types	Structured + semi-structured + unstructured	Structured only
Storage format	Delta Lake (Parquet + transaction log)	Managed columnar (optimized internally)
File storage	✅ /Files folder for raw files (CSV, JSON, images)	❌ No file storage
Notebook support	✅ Full (PySpark, SparkSQL, Scala, R)	❌ No Spark notebooks
T-SQL read	✅ Via SQL analytics endpoint (read-only)	✅ Full T-SQL (read + write)
T-SQL write	❌ SQL analytics endpoint is read-only	✅ INSERT, UPDATE, DELETE, MERGE
Stored procedures	❌ Not supported	✅ Full support
Views	✅ Via SQL analytics endpoint	✅ Full support
Table cloning	✅ Spark clone	✅ T-SQL CLONE TABLE
Delta MERGE (PySpark)	✅ Yes	❌ No Spark
T-SQL MERGE	❌ Not via endpoint	✅ Yes
Schema evolution	✅ Easy (Delta schema merge)	⚠️ Manual ALTER TABLE
Shortcuts	✅ Under Tables and Files	✅ Under Tables
Direct Lake (Power BI)	✅ Yes	✅ Yes
Auto-generated SQL endpoint	✅ Yes (created automatically)	N/A (warehouse IS the SQL endpoint)
Row-level security	✅ Via SQL analytics endpoint	✅ Full support
Best for	Engineering, ML, semi-structured data	SQL analytics, BI reporting, structured data

What Is a Lakehouse?

A Lakehouse is a dual-engine data store — it combines the flexibility of a data lake (files, any format) with the querying power of a data warehouse (SQL access via the analytics endpoint).

Lakehouse: bronze_lakehouse
  │
  ├── Tables/                    ← Delta tables (queryable via Spark AND SQL)
  │     ├── customers/           ← Delta table
  │     ├── orders/              ← Delta table
  │     └── products/            ← Delta table
  │
  └── Files/                     ← Raw files (any format — CSV, JSON, Parquet, images, logs)
        ├── raw_csv/
        │     └── sales_2026.csv
        ├── json_feeds/
        │     └── api_response.json
        └── images/
              └── product_photos/

The Three Interfaces of a Lakehouse

Every Lakehouse has three ways to interact with it:

1. LAKEHOUSE EXPLORER (default view)
   → Browse Tables and Files visually
   → Upload files, preview data
   → Manage table properties

2. NOTEBOOK (Spark)
   → PySpark, SparkSQL, Scala, SparkR
   → Full read/write on Tables and Files
   → Delta MERGE, window functions, ML
   → The DATA ENGINEER's primary tool

3. SQL ANALYTICS ENDPOINT (auto-generated)
   → T-SQL queries on Tables (READ-ONLY)
   → Create views, table-valued functions
   → Power BI connects here for Direct Lake
   → The DATA ANALYST's access point

Real-life analogy: A Lakehouse is like a restaurant kitchen with a serving window. Chefs (data engineers) work inside the kitchen (Spark notebooks) with full access to ingredients and tools. Customers (analysts) order through the serving window (SQL analytics endpoint) — they can see the menu and request dishes, but cannot enter the kitchen or modify ingredients.

What Is a Warehouse?

A Warehouse is a fully managed SQL database optimized for analytics. It supports complete T-SQL — CREATE TABLE, INSERT, UPDATE, DELETE, MERGE, stored procedures, views, functions. It is the Fabric equivalent of Azure Synapse Dedicated SQL Pool, but serverless and auto-managed.

Warehouse: sales_warehouse
  │
  └── Schemas/
        ├── dbo/
        │     ├── dim_customer          ← T-SQL table
        │     ├── dim_product           ← T-SQL table
        │     ├── dim_date              ← T-SQL table
        │     ├── fact_sales            ← T-SQL table
        │     └── vw_monthly_revenue    ← View
        │
        └── staging/
              ├── stg_customers         ← Staging table
              └── stg_orders            ← Staging table

Real-life analogy: A Warehouse is like a retail showroom. Everything is organized on shelves (schemas), labeled with price tags (data types, constraints), and customers (analysts) can browse freely, try things on (run queries), and make purchases (build reports). The showroom manager (DBA) controls the layout and inventory using familiar retail tools (T-SQL).

The SQL Analytics Endpoint (The Confusing Piece)

This is what confuses everyone. When you create a Lakehouse, Fabric automatically creates a SQL analytics endpoint — a read-only T-SQL interface on top of your Delta tables.

Lakehouse: bronze_lakehouse
  │
  ├── [Lakehouse view]         → Spark: full read/write
  │
  └── [SQL analytics endpoint] → T-SQL: READ-ONLY
        ├── SELECT ✅
        ├── CREATE VIEW ✅
        ├── Table-Valued Functions ✅
        ├── INSERT ❌ (not supported)
        ├── UPDATE ❌ (not supported)
        ├── DELETE ❌ (not supported)
        ├── MERGE ❌ (not supported)
        ├── CREATE TABLE ❌ (not supported)
        └── Stored Procedures ❌ (not supported)

Why Read-Only?

The Lakehouse tables are Delta format, managed by Spark. Allowing T-SQL writes would create conflicts — two engines writing to the same Delta files simultaneously could corrupt the transaction log. So Fabric enforces a clean separation: Spark writes, SQL reads.

Why Does the Endpoint Exist?

Three critical reasons: 1. Power BI Direct Lake connects through the SQL analytics endpoint 2. Analysts can query Lakehouse data using T-SQL without learning PySpark 3. Row-level and object-level security is enforced through the SQL endpoint

The Practical Impact

-- Connecting to the SQL analytics endpoint of a Lakehouse:

-- ✅ THIS WORKS
SELECT * FROM customers WHERE city = 'Toronto';
SELECT department, COUNT(*) FROM employees GROUP BY department;
CREATE VIEW vw_active_customers AS SELECT * FROM customers WHERE is_active = 1;

-- ❌ THIS FAILS
INSERT INTO customers VALUES (999, 'Test', 'test@email.com');
-- Error: INSERT is not supported on the SQL analytics endpoint

UPDATE customers SET city = 'Vancouver' WHERE customer_id = 1001;
-- Error: UPDATE is not supported on the SQL analytics endpoint

-- ✅ TO WRITE DATA, use a Spark notebook:
-- df.write.format("delta").mode("append").saveAsTable("customers")

Languages and Interfaces: What Works Where

Language/Tool	Lakehouse (Notebook)	Lakehouse (SQL Endpoint)	Warehouse
PySpark	✅ Full read/write	❌	❌
SparkSQL	✅ Full read/write	❌	❌
Scala	✅ Full read/write	❌	❌
SparkR	✅ Full read/write	❌	❌
T-SQL SELECT	❌ (use SparkSQL)	✅ Read-only	✅ Full
T-SQL INSERT/UPDATE/DELETE	❌	❌	✅ Full
T-SQL MERGE	❌	❌	✅ Full
T-SQL Stored Procedures	❌	❌	✅ Full
T-SQL CREATE VIEW	❌	✅ Yes	✅ Yes
Delta MERGE (PySpark)	✅ Yes	❌	❌
Power BI Direct Lake	Via SQL endpoint	✅ Connect here	✅ Yes
SSMS / Azure Data Studio	❌	✅ Connect via T-SQL	✅ Connect via T-SQL
dbt	❌	❌	✅ Yes (T-SQL adapter)

The key insight: If you need to WRITE data, you have two choices: PySpark in a Lakehouse notebook, or T-SQL in a Warehouse. The SQL analytics endpoint is read-only — it is a window, not a door.

Read vs Write Capabilities

Operation	Lakehouse (Spark)	Lakehouse (SQL Endpoint)	Warehouse (T-SQL)
Read tables	✅ spark.read.table("customers")	✅ SELECT * FROM customers	✅ SELECT * FROM customers
Write tables	✅ df.write.saveAsTable("customers")	❌ Read-only	✅ INSERT INTO customers VALUES(...)
Update rows	✅ Delta MERGE / UPDATE	❌ Read-only	✅ UPDATE customers SET...
Delete rows	✅ DELETE FROM (SparkSQL)	❌ Read-only	✅ DELETE FROM customers WHERE...
Create tables	✅ CREATE TABLE (SparkSQL)	❌ Read-only	✅ CREATE TABLE (T-SQL)
Drop tables	✅ DROP TABLE (SparkSQL)	❌ Read-only	✅ DROP TABLE (T-SQL)
Create views	✅ CREATE VIEW (SparkSQL)	✅ CREATE VIEW (T-SQL)	✅ CREATE VIEW (T-SQL)
Stored procedures	❌ Not supported	❌ Not supported	✅ Full support
Read raw files	✅ spark.read.csv("Files/data.csv")	❌ No file access	❌ No file storage
Schema evolution	✅ .option("mergeSchema", "true")	Auto-synced	Manual ALTER TABLE

Storage Format Differences

Lakehouse Storage:
  OneLake / workspace / lakehouse.Lakehouse /
    ├── Tables/
    │     └── customers/
    │           ├── _delta_log/               ← Delta transaction log
    │           ├── part-00000.snappy.parquet  ← Data file (Parquet)
    │           └── part-00001.snappy.parquet
    └── Files/
          └── raw_data/
                └── sales.csv                  ← Raw files (any format)

Warehouse Storage:
  OneLake / workspace / warehouse.Warehouse /
    └── dbo/
          └── dim_customer/
                ├── part-00000.snappy.parquet  ← Also Parquet under the hood
                └── (managed internally — you don't interact with files directly)

Both use Parquet underneath. The difference is that Lakehouse uses Delta Lake (Parquet + transaction log) managed by Spark, while Warehouse uses a managed columnar format optimized by the SQL engine. You do not interact with Warehouse files directly — T-SQL abstracts the storage layer.

Security Model Differences

Feature	Lakehouse (Spark)	Lakehouse (SQL Endpoint)	Warehouse
Workspace roles	✅ Admin/Member/Contributor/Viewer	✅ Same	✅ Same
Object-level security	❌ Not via Spark	✅ GRANT/DENY on tables/views	✅ GRANT/DENY on tables/views
Row-level security (RLS)	❌ Not via Spark	✅ Supported	✅ Supported
Column-level security (CLS)	❌ Not via Spark	✅ Supported	✅ Supported
Dynamic data masking	❌ Not via Spark	✅ Supported	✅ Supported

The key security difference: If you need fine-grained security (which analyst sees which rows), you MUST route access through the SQL analytics endpoint (Lakehouse) or Warehouse. Spark notebooks in Lakehouse do not support RLS/CLS — security is workspace-role based only.

Scenario 1: E-Commerce Daily ETL Pipeline

Problem: Load orders from Azure SQL, clean data, build star schema, serve to Power BI.

SOLUTION: Lakehouse + Warehouse

Lakehouse: ecommerce_lakehouse
  ├── Tables/bronze_orders         ← Pipeline copies raw data here
  ├── Tables/bronze_customers
  ├── Tables/silver_orders         ← Notebook cleans and validates
  └── Tables/silver_customers

Warehouse: ecommerce_warehouse
  ├── dbo.dim_customer             ← SCD Type 2 via T-SQL MERGE
  ├── dbo.dim_product
  ├── dbo.dim_date
  ├── dbo.fact_orders              ← Star schema fact table
  └── dbo.vw_monthly_revenue       ← View for Power BI

Pipeline flow:
  Copy Activity → Lakehouse (bronze)
  Notebook → Lakehouse (silver) [PySpark cleaning, dedup, validation]
  Notebook → Warehouse (gold) [read silver, T-SQL MERGE into dimensions/facts]
  Semantic Model Refresh → Power BI Direct Lake on Warehouse

Why both? Lakehouse for PySpark transformations (data engineering). Warehouse for star schema with stored procedures and views (BI serving).

Scenario 2: Financial Reporting Platform

Problem: Finance team needs strict data governance, stored procedures, and row-level security. All users know T-SQL only.

SOLUTION: Warehouse only

Warehouse: finance_warehouse
  ├── staging.stg_general_ledger
  ├── staging.stg_accounts
  ├── dbo.dim_account
  ├── dbo.dim_cost_center
  ├── dbo.fact_journal_entries
  ├── dbo.vw_monthly_p_and_l          ← View: Profit & Loss
  ├── dbo.vw_balance_sheet             ← View: Balance Sheet
  ├── dbo.usp_load_journal_entries     ← Stored procedure: ETL
  └── dbo.usp_close_month             ← Stored procedure: month-end close

Security:
  RLS: CFO sees all entities. Controllers see only their entity.
  CLS: Salary columns visible to HR only.

Why Warehouse only? SQL-first team, strict governance requirements, stored procedures needed, no PySpark skills on the team.

Scenario 3: IoT Sensor Analytics

Problem: Factory sensors send millions of JSON readings per hour. Need to process, detect anomalies, and train ML models.

SOLUTION: Lakehouse only

Lakehouse: iot_lakehouse
  ├── Files/raw_json/                   ← Raw sensor JSON files land here
  │     ├── 2026/05/25/14/
  │     └── 2026/05/25/15/
  ├── Tables/bronze_sensor_readings     ← Notebook parses JSON → Delta
  ├── Tables/silver_readings_cleaned    ← Notebook validates, fills gaps
  ├── Tables/gold_hourly_aggregates     ← Notebook aggregates per hour
  └── Tables/ml_features               ← Feature table for anomaly detection model

Notebooks:
  NB_Parse_JSON: Read Files/ → parse JSON → write bronze table
  NB_Clean_Validate: Read bronze → handle nulls, outliers → write silver
  NB_Aggregate: Read silver → window functions, aggregations → write gold
  NB_Train_Model: Read ml_features → scikit-learn → MLflow model

Why Lakehouse only? Semi-structured data (JSON), ML workloads, PySpark-heavy transformations, no need for T-SQL write operations.

Scenario 4: Customer 360 with ML

Problem: Combine customer data from 5 sources, build a unified profile, predict churn, serve to both analysts (SQL) and data scientists (notebooks).

SOLUTION: Lakehouse + Warehouse

Lakehouse: customer_360_lakehouse
  ├── Tables/bronze_crm_customers      ← From Salesforce API
  ├── Tables/bronze_web_events         ← From Event Hubs
  ├── Tables/bronze_support_tickets    ← From Zendesk API
  ├── Tables/bronze_billing            ← From SQL Database
  ├── Tables/silver_unified_customer   ← Notebook: merge all sources
  ├── Tables/ml_churn_features         ← Notebook: feature engineering
  └── Tables/ml_churn_predictions      ← Notebook: model scoring

Warehouse: customer_analytics_warehouse
  ├── dbo.dim_customer                 ← Gold: unified customer dimension
  ├── dbo.fact_interactions            ← Gold: all touchpoints
  ├── dbo.vw_churn_risk_dashboard      ← View: joins predictions + customer info
  └── RLS applied: Sales reps see only their customers

Why both? Lakehouse for ML (feature engineering, model training in Spark). Warehouse for BI (analysts query with T-SQL, RLS enforced, Power BI connects).

Scenario 5: Self-Service Analytics for Business Users

Problem: Marketing team wants to upload CSV files, explore data, build their own reports. No coding skills.

SOLUTION: Lakehouse (with Dataflow Gen2)

Lakehouse: marketing_lakehouse
  ├── Files/uploads/                    ← Marketing uploads CSVs here
  │     ├── campaign_results.csv
  │     └── ad_spend_q1.csv
  ├── Tables/campaign_results           ← Dataflow Gen2 loads CSV → Delta table
  ├── Tables/ad_spend                   ← Dataflow Gen2 cleans and loads
  └── Tables/combined_marketing_data    ← Dataflow Gen2 merges tables

Tools:
  Upload: Lakehouse UI (drag and drop files)
  Transform: Dataflow Gen2 (no-code Power Query)
  Query: SQL analytics endpoint (familiar SQL)
  Report: Power BI (Direct Lake on the endpoint)

Why Lakehouse? File upload capability (Warehouse has no /Files folder). Dataflow Gen2 for no-code ETL. SQL analytics endpoint gives SQL access without needing a separate Warehouse.

The Medallion Architecture Pattern in Fabric

RECOMMENDED PRODUCTION PATTERN:

Lakehouse: bronze_lakehouse
  ├── Tables/raw_customers         ← Copy Activity lands data here
  ├── Tables/raw_products
  ├── Tables/raw_orders
  └── Files/raw_files/             ← Any raw files (CSV, JSON, etc.)
         │
         │  Notebook (PySpark): clean, validate, dedup
         ▼
Lakehouse: silver_lakehouse
  ├── Tables/customers_clean       ← Cleaned, typed, deduplicated
  ├── Tables/products_clean
  └── Tables/orders_validated
         │
         │  Notebook (PySpark → read silver) OR
         │  Warehouse (T-SQL MERGE → build star schema)
         ▼
Warehouse: gold_warehouse
  ├── dbo.dim_customer             ← SCD Type 2
  ├── dbo.dim_product              ← SCD Type 1
  ├── dbo.dim_date                 ← Pre-populated date dimension
  ├── dbo.fact_orders              ← Fact table
  ├── dbo.vw_daily_revenue         ← Views for Power BI
  └── dbo.usp_load_dimensions     ← Stored procedures for loading
         │
         │  Power BI Direct Lake
         ▼
Power BI Reports

Bronze + Silver = Lakehouse (PySpark flexibility, schema evolution, file storage) Gold = Warehouse (T-SQL, stored procedures, views, RLS, analyst-friendly) Power BI = Direct Lake (connects to Warehouse or Lakehouse SQL endpoint)

When to Use Lakehouse

Scenario	Why Lakehouse
Your team writes PySpark/SparkSQL	Lakehouse is the only item with Spark notebooks
You have semi-structured data (JSON, XML, logs)	/Files folder accepts any format
You need schema evolution	Delta schema merge handles new columns automatically
You are building ML features/models	Spark + MLflow integration
Your data arrives as raw files	/Files folder is the landing zone
You need to process streaming data	Spark Structured Streaming writes to Lakehouse
Your Bronze/Silver layers need flexibility	Engineering workloads belong in Lakehouse

When to Use Warehouse

Scenario	Why Warehouse
Your team knows T-SQL (not PySpark)	Full T-SQL support including write operations
You need stored procedures	Not available in Lakehouse
You need T-SQL MERGE for SCD	MERGE only works in Warehouse
You need row-level security on writes	Full RLS enforcement
You are building a star schema (Gold layer)	Warehouse is designed for dimensional modeling
You are migrating from Synapse Dedicated SQL Pool	Near-identical T-SQL experience
dbt is your transformation tool	dbt works with Warehouse (T-SQL adapter)
Business analysts are primary consumers	Familiar SQL experience

When to Use Both (Most Common)

Most real-world Fabric implementations use BOTH:

Lakehouse handles:
  → Data ingestion (raw files, API responses, streaming)
  → Data engineering (PySpark cleaning, validation, dedup)
  → ML/Data Science (feature engineering, model training)
  → Bronze and Silver layers

Warehouse handles:
  → Star schema (fact + dimension tables)
  → Business logic (stored procedures, views)
  → Security (RLS, CLS, dynamic masking)
  → Gold layer + BI serving

Cross-Database Queries (Lakehouse + Warehouse Together)

Fabric supports querying across Lakehouse and Warehouse in the same workspace:

-- From the Warehouse, query a Lakehouse table
SELECT w.customer_key, w.total_orders,
       l.ml_churn_score, l.predicted_churn
FROM dbo.dim_customer w
JOIN customer_360_lakehouse.dbo.ml_churn_predictions l
    ON w.customer_id = l.customer_id;

You can also use Shortcuts to make Lakehouse tables appear inside a Warehouse for querying.

Migrating from Synapse / Databricks to Fabric

Current Tool	Migrate To	Why
Synapse Dedicated SQL Pool	Warehouse	Same T-SQL, same star schema, same stored procedures
Synapse Spark Pools	Lakehouse notebooks	Same PySpark code, same Delta Lake
Synapse Pipelines	Fabric Pipelines	Near-identical UI and activities
Databricks notebooks	Lakehouse notebooks	Same PySpark, same Delta, same MERGE
Databricks SQL Warehouse	Warehouse	T-SQL analytics, similar purpose
ADLS Gen2 raw storage	Lakehouse /Files	Or use Shortcuts to avoid moving data

Common Mistakes

1. Using Warehouse for everything — if your team writes PySpark, forcing them into T-SQL slows everyone down. Use Lakehouse for engineering, Warehouse for analytics.

2. Using Lakehouse for everything — if your team knows only T-SQL and needs stored procedures, the Lakehouse SQL analytics endpoint’s read-only limitation will frustrate them. Use Warehouse.

3. Trying to INSERT/UPDATE via the SQL analytics endpoint — it is READ-ONLY. To write data to a Lakehouse, use a Spark notebook. This is the most common error new users hit.

4. Creating one giant Lakehouse for everything — separate Bronze, Silver, and Gold into different Lakehouses (or Lakehouse + Warehouse) for clarity and access control.

5. Not using the SQL analytics endpoint for Power BI — Direct Lake mode connects through the SQL analytics endpoint. If you skip it and use import mode, you lose the real-time freshness advantage.

6. Putting raw files in a Warehouse — Warehouses have no /Files folder. Raw CSV, JSON, and other unstructured files must go in a Lakehouse.

7. Ignoring cross-database queries — you do not need to copy data between Lakehouse and Warehouse. Query across them directly or use Shortcuts.

Interview Questions

Q: What is the difference between a Lakehouse and a Warehouse in Fabric? A: A Lakehouse is a dual-engine data store supporting PySpark notebooks (full read/write) and a SQL analytics endpoint (read-only T-SQL). It handles structured and unstructured data with Delta Lake format. A Warehouse is a fully managed SQL database supporting complete T-SQL (read/write, stored procedures, MERGE). Use Lakehouse for data engineering and ML. Use Warehouse for SQL analytics and BI.

Q: What is the SQL analytics endpoint and why is it read-only? A: The SQL analytics endpoint is an auto-generated T-SQL interface on top of Lakehouse Delta tables. It is read-only because the Delta tables are managed by Spark — allowing two engines (Spark + T-SQL) to write simultaneously could corrupt the transaction log. Spark writes, SQL reads. Power BI Direct Lake connects through this endpoint.

Q: Can you use T-SQL to write data to a Lakehouse? A: No. The SQL analytics endpoint is read-only — SELECT, views, and table-valued functions are supported, but INSERT, UPDATE, DELETE, and MERGE are not. To write data to a Lakehouse, use a Spark notebook with PySpark or SparkSQL.

Q: When would you use both a Lakehouse and a Warehouse? A: Most production implementations use both. Lakehouse for data engineering (PySpark ingestion, cleaning, ML) serving as Bronze and Silver layers. Warehouse for the Gold layer (star schema with T-SQL MERGE, stored procedures, views, row-level security). Power BI connects to either via Direct Lake.

Q: How do you choose between Lakehouse and Warehouse for the Gold layer? A: If your Gold layer needs stored procedures, T-SQL MERGE for SCD, row-level security, and SQL-first analysts — use Warehouse. If your Gold layer is built entirely in PySpark notebooks and analysts only need read access — Lakehouse with its SQL analytics endpoint is sufficient. When in doubt, use Warehouse for Gold.

Wrapping Up

Lakehouse and Warehouse are not competitors — they are partners. Lakehouse is the workshop where data engineers build with PySpark. Warehouse is the showroom where analysts shop with T-SQL. The SQL analytics endpoint is the window between them — letting SQL users peek into the workshop without entering.

The pattern is clear: Lakehouse for Bronze and Silver (engineering). Warehouse for Gold (analytics). Both connected through OneLake, queryable across databases, and serving Power BI through Direct Lake. Choose based on your team’s skills and your workload’s needs.

Related posts: – Fabric Foundations: Capacity, Workspaces, Items – Microsoft Fabric Overview – Fabric Data Factory: Pipelines and Dataflow Gen2 – OneLake Shortcuts – Normalization and Star Schema – SCD Types

---

Naveen Vuppula is a Senior Data Engineering Consultant and app developer based in Ontario, Canada. He writes about Python, SQL, AWS, Azure, and everything data engineering at DriveDataScience.com.