PathTailor
FRSign inBuild my path
PathTailor
Build my pathSign inLire en français

OneLake, Lakehouse and Warehouse

First lesson of the Microsoft Fabric for Analysts course, free to read: about 20 minutes, 3 exercises and 3 quiz questions. This is the catalogue's base lesson; in the student space, it is rewritten for your profile.

Objectives

  • Place Fabric, OneLake and the items of a workspace in context
  • Load files into a lakehouse and query them in SQL
  • Choose between lakehouse and warehouse depending on the need

OneLakeLakehouseWarehouseDelta format

Fabric in one sentence

Microsoft Fabric is a SaaS analytics platform that brings together, in one interface and on one storage layer, what used to be spread across several services: ingestion and transformation (Data Factory), data engineering (Spark), SQL warehouse, data science, real-time analytics and Power BI. The Power BI service you used in BI7 is part of Fabric.

You always work in workspaces (same Admin, Member, Contributor, Viewer roles), hosted on a capacity that provides the compute power. What is possible depends on your organization’s license and capacity.

OneLake: a single lake

All Fabric data is stored in OneLake, a data lake that is unique for the whole organization, often compared to a “OneDrive for data”. Each workspace is a folder in it, each item (lakehouse, warehouse) a subfolder.

Tables are stored there in the open Delta format: Parquet files (compressed columns) accompanied by a transaction log that guarantees write consistency and keeps a version history. Major consequence: the same table can be read by Spark, by the SQL engine and by Power BI without copying.

Shortcuts extend this idea: they display in a lakehouse data located in another item or in compatible external storage, without moving it.

The lakehouse

A lakehouse combines the flexibility of a lake and the structure of a warehouse. It has two areas:

  • Files: files of any format (CSV, JSON, Parquet, images), unmanaged;
  • Tables: managed Delta tables, queryable in SQL.

A simple approach: upload ventes_plat.csv to a folder in Files, then right-click > Load to tables: Fabric creates the Delta table ventes_plat.

Every lakehouse automatically has a SQL analytics endpoint: read-only T-SQL access to its tables. You can write queries, views and functions there, and connect from SQL tools:

SELECT Magasin, SUM(Quantite * PrixUnitaire) AS CA
FROM dbo.ventes_plat
GROUP BY Magasin
ORDER BY CA DESC;

To write to a lakehouse’s tables, you go through Spark (notebooks), dataflows or pipelines, not through this endpoint.

The warehouse

Fabric’s Warehouse is a relational warehouse with full T-SQL: CREATE TABLE, INSERT, UPDATE, DELETE, multi-table transactions, stored procedures. Its tables are also Delta in OneLake. It can read the tables of a lakehouse in the same workspace using a three-part name:

CREATE TABLE dbo.ca_categorie AS
SELECT Categorie, SUM(Quantite * PrixUnitaire) AS CA
FROM lh_ventes.dbo.ventes_plat
GROUP BY Categorie;
Need Rather
varied files, semi-structured data, Python/Spark team Lakehouse
SQL team, star schema, transactional updates Warehouse
SQL reads of a lakehouse’s tables The lakehouse’s SQL endpoint

The two often coexist in the same solution.

The medallion architecture

A common organization of data into three layers:

  • Bronze: raw data, as received;
  • Silver: cleaned, typed, deduplicated data;
  • Gold: analysis-ready tables (facts and dimensions, aggregates), the sources of semantic models.

Each layer can be a lakehouse (or a warehouse for gold). This course follows this breakdown: raw file, table cleaned by a dataflow, gold tables produced by a notebook, then a Direct Lake model.

Exercises

Solutions and tests stay on the server. In the student space, your code is run and checked, and the tutor gives hints before the solution.

  1. TaskDifficulty 22 hints

    Create the lakehouse and load the sales

    Access: use the Fabric workspace provided by the training (a workspace on a Fabric or trial capacity). Available features depend on your organization’s license and capacity. Fabric evolves quickly: if a label differs slightly, look for the equivalent item in the New item menu. Data: the ventes_plat.csv file (same data as the BI7 course, Total ventes = 415):

    NumVente,Date,DateLivraison,CodeMagasin,Magasin,Region,CodeProduit,Produit,Categorie,Quantite,PrixUnitaire
V001,2026-01-03,2026-01-05,M1,Lyon,Auvergne-Rhône-Alpes,P01,Jus d'orange,Boissons,10,3
V002,2026-01-03,2026-01-06,M2,Lille,Hauts-de-France,P02,Pates,Epicerie,20,2
V003,2026-01-10,2026-01-12,M3,Nantes,Pays de la Loire,P03,Yaourt,Frais,30,1
V004,2026-01-17,2026-01-19,M1,Lyon,Auvergne-Rhône-Alpes,P04,Riz,Epicerie,15,2
V005,2026-01-24,2026-01-27,M2,Lille,Hauts-de-France,P05,Eau,Boissons,50,1
V006,2026-02-02,2026-02-04,M3,Nantes,Pays de la Loire,P01,Jus d'orange,Boissons,12,3
V007,2026-02-07,2026-02-09,M1,Lyon,Auvergne-Rhône-Alpes,P06,Fromage,Frais,8,6
V008,2026-02-14,2026-02-16,M2,Lille,Hauts-de-France,P03,Yaourt,Frais,25,1
V009,2026-02-21,2026-02-24,M3,Nantes,Pays de la Loire,P02,Pates,Epicerie,18,2
V010,2026-02-28,2026-03-03,M1,Lyon,Auvergne-Rhône-Alpes,P05,Eau,Boissons,40,1
V011,2026-03-06,2026-03-09,M2,Lille,Hauts-de-France,P04,Riz,Epicerie,10,2
V012,2026-03-13,2026-03-16,M3,Nantes,Pays de la Loire,P06,Fromage,Frais,5,6
    1. In the training workspace, create a lakehouse named lh_ventes.
    2. In Files, create a folder brut and upload ventes_plat.csv.
    3. Right-click the file > Load to tables > new table ventes_plat.
    4. Open the lakehouse’s SQL analytics endpoint and run a query that calculates SUM(Quantite * PrixUnitaire) overall and then by Magasin.
    • The Files/brut folder contains ventes_plat.csv and Tables contains ventes_plat
    • The table has 12 rows and 11 columns
    • The total is 415
    • By store: Lyon 148, Lille 135, Nantes 132

  2. TaskDifficulty 22 hints

    A warehouse table

    Access: use the Fabric workspace provided by the training (a workspace on a Fabric or trial capacity). Available features depend on your organization’s license and capacity. Fabric evolves quickly: if a label differs slightly, look for the equivalent item in the New item menu.

    1. Create a warehouse named wh_ventes in the same workspace.
    2. In the warehouse’s query editor, run:
    CREATE TABLE dbo.ca_categorie AS
SELECT Categorie, SUM(Quantite * PrixUnitaire) AS CA
FROM lh_ventes.dbo.ventes_plat
GROUP BY Categorie;
    1. Add a test row with INSERT INTO dbo.ca_categorie VALUES ('Test', 1);, check it, then delete it with DELETE.
    2. Try the same INSERT statement on the lakehouse’s SQL endpoint and note the message.
    • dbo.ca_categorie contains 3 rows: Boissons 156, Epicerie 126, Frais 133
    • The INSERT and then the DELETE work in the warehouse
    • The INSERT is rejected on the lakehouse's SQL endpoint (read-only)

  3. Multiple choiceDifficulty 21 hint

    Lakehouse or warehouse?

    A team of SQL developers must maintain a star schema with daily transactional updates. Which Fabric item should they favor?

    • A lakehouse, writing through its SQL endpoint
    • A warehouse
    • A Files folder
    • A dashboard

End-of-lesson quiz

  1. In which format are OneLake tables stored?

    • Delta (Parquet and a transaction log)
    • CSV
    • .pbix files

  2. What does a lakehouse’s SQL analytics endpoint allow?

    • Querying the tables in read-only T-SQL
    • Modifying the tables with UPDATE
    • Uploading files

  3. What is a OneLake shortcut used for?

    • To expose data located elsewhere without copying it
    • To speed up a report
    • To create a user

The rest of the course

  1. Dataflows Gen2 and Pipelines: Ingest and Prepare20 min
  2. Notebooks: Transforming with PySpark and Spark SQL20 min
  3. Direct Lake: Semantic Models on OneLake20 min
  4. Governance: Access, Security, Lineage and Capacity20 min
Build my pathSee the full syllabus

Microsoft Fabric for Analysts · Advanced · 1 h 40 min