Module 2 – OneLake & Lakehouse

11 Lessons

This module covers OneLake and the Lakehouse in Microsoft Fabric: unified storage, files and tables, Delta Lake, shortcuts to external data, and the practical steps to create a Lakehouse, load data, and query it.

Module Learning Path

1. OneLake
2. Lakehouse Architecture
3. Files
4. Tables
5. Delta Lake
6. Managed vs Shortcut Tables
7. External Data
8. Create Lakehouse
9. Upload CSV
10. Query Data
11. Create Delta Tables
12. Module Review

Lesson 2.1 – OneLake

Learning Objectives

• Understand what OneLake is.
• Learn how OneLake works in Microsoft Fabric.
• Explore the key features and architecture of OneLake.
• Understand the benefits of a unified data lake.
• Use OneLake to manage and access data.

What is OneLake?

OneLake is the unified, centralized data lake for Microsoft Fabric. It serves as a single storage layer for all Fabric workloads, allowing different services to access the same data without creating multiple copies.

Think of OneLake as the "OneDrive for Data", where all organizational data is stored securely and can be shared across analytics workloads.

Why OneLake?

Before OneLake, organizations often stored data in multiple locations, leading to: - Duplicate data - Higher storage costs - Data inconsistency - Complex data integration - Difficult data governance

OneLake solves these challenges by providing a single source of truth for all data.

OneLake Architecture

Microsoft Fabric
        │
┌───────┼───────┐
Data Factory   Data Engineering   Data Warehouse
        │
     OneLake
        │
┌───────┼───────┐
Lakehouse   Warehouse   SQL Database
        │
   Power BI Reports

All Fabric workloads use OneLake as the common storage layer.

Key Features of OneLake

1. Unified Storage — All Fabric workloads store their data in OneLake (Lakehouse, Warehouse, SQL Database, Data Engineering, Data Science, Power BI).

2. Single Copy of Data — Instead of storing multiple copies, OneLake stores one copy that can be accessed by different services. - Example: A sales dataset is stored once in OneLake. Data Engineers clean it, Data Scientists build ML models, Analysts create Power BI reports — everyone works on the same data.

3. Open Data Format — OneLake stores data using Delta Lake (Parquet-based) format. Benefits: ACID transactions, version history, faster queries, compatibility with Apache Spark.

4. Automatic Availability — Whenever you create a Lakehouse, Warehouse, or SQL Database, Fabric automatically stores the data in OneLake. No additional configuration required.

5. Security — Integrates with Microsoft Entra ID (Azure AD), Role-Based Access Control (RBAC), workspace permissions, and data governance policies.

6. Shortcuts — OneLake supports Shortcuts, allowing you to reference external data without copying it. Supported sources include ADLS Gen2, Amazon S3, Google Cloud Storage (supported scenarios), and other OneLake locations. Enables data virtualization and reduces storage duplication.

OneLake Structure

OneLake
├── Sales Workspace
│      ├── Sales Lakehouse
│      ├── Sales Warehouse
│      └── Sales Reports
├── Finance Workspace
│      ├── Finance Lakehouse
│      └── Finance Reports
├── HR Workspace
│      ├── Employee Lakehouse
│      └── HR Dashboard
└── Marketing Workspace
       ├── Campaign Lakehouse
       └── Marketing Reports

Each workspace has its own storage, while everything resides under the same OneLake environment.

How to Access OneLake

1. Sign in to Microsoft Fabric.
2. Open OneLake from the left navigation pane.
3. Browse Workspaces, Lakehouses, Files, Tables, Shortcuts.
4. Open a Lakehouse to upload files, create folders, view Delta tables, and query data using SQL or Spark.

OneLake vs Traditional Data Lakes

Real-World Example

A retail company stores sales data in OneLake. Data Engineers ingest and clean the data. Data Scientists build demand forecasting models. Business Analysts create Power BI dashboards. Managers review reports to make business decisions. All teams access the same data stored in OneLake, ensuring consistency and reducing storage costs.

Benefits of OneLake

• Single source of truth
• Eliminates unnecessary data copies
• Built-in support for Delta Lake
• Simplifies data governance
• Enables collaboration across teams
• Integrates seamlessly with all Microsoft Fabric workloads
• Supports large-scale analytics and AI solutions

Best Practices

• Organize data by workspace and business domain.
• Use meaningful names for Lakehouses and folders.
• Avoid storing duplicate datasets.
• Use Shortcuts to reference external data instead of copying it.
• Implement role-based access control for secure collaboration.
• Regularly monitor storage usage and data quality.

Interview Questions

1. What is OneLake? OneLake is the unified, centralized data lake in Microsoft Fabric that stores and manages data for all Fabric workloads.
2. Why is OneLake called the "OneDrive for Data"? Because it provides a single, centralized location for storing and sharing organizational data across multiple analytics services, similar to how OneDrive centralizes file storage.
3. What file format does OneLake primarily use? Delta Lake, which is built on Parquet and supports ACID transactions and efficient analytics.
4. What are OneLake Shortcuts? Shortcuts are references to external data sources that allow you to access data without physically copying it into OneLake.
5. How does OneLake reduce data duplication? By storing a single copy of data that can be accessed by different Fabric workloads, eliminating the need for multiple copies.

Key Takeaways

• OneLake is the centralized storage layer for Microsoft Fabric.
• It provides a single source of truth for organizational data.
• Data is stored in Delta Lake format for reliability and performance.
• OneLake Shortcuts allow access to external data without duplication.
• All Fabric workloads—including Data Engineering, Data Warehouse, Data Science, and Power BI—can securely access the same data, enabling efficient collaboration and governance.

Lesson 2.2 – Lakehouse Architecture

A Lakehouse in Microsoft Fabric combines the flexibility and scalability of a data lake with many of the structured analytics capabilities associated with a data warehouse.

In Fabric, a Lakehouse is built on OneLake and commonly uses Delta Lake tables stored in Parquet format.

Lakehouse = Data Lake flexibility + Data Warehouse-style analytics

1. Why do we need a Lakehouse?

Traditionally, organizations maintained separate systems:

Data Lake — used for CSV, JSON, Parquet, Images, Logs, Raw Data, Semi-structured Data.

Data Warehouse — used for Structured Tables, SQL Queries, Fact Tables, Dimension Tables, Business Reporting.

This could result in an architecture where data moves: Data Sources → Data Lake → (Transform/Move) → Data Warehouse → Power BI.

The Lakehouse architecture brings these approaches closer together — combining Data Lake capabilities (Files) and Warehouse capabilities (Tables) into one Analytics layer.

2. Fabric Lakehouse Architecture

DATA SOURCES (SQL, Oracle, SAP, Salesforce, APIs, CSV, IoT)
        │
DATA INGESTION (Data Factory, Pipelines, Dataflow Gen2)
        │
════════════════════════════
ONELAKE — FABRIC LAKEHOUSE
   FILES        TABLES        SHORTCUTS
 CSV/JSON     Delta Tables   External Data
                 Parquet
════════════════════════════
        │
  ┌─────┼─────┐
Spark   SQL   Power BI
  │      │        │
Engineering Analytics Reporting

The same Lakehouse data can be used by multiple analytical experiences.

3. Main components of a Lakehouse

Lakehouse
├── Tables
└── Files

4. Files Area

The Files area is used for file-based data:

Retail_Lakehouse
└── Files
    ├── raw/
    ├── source/
    ├── archive/
    └── reference/

Examples: customers.csv, products.csv, orders.json, sales.parquet, inventory.csv. Useful for raw and semi-structured datasets.

5. Tables Area

The Tables area contains structured Lakehouse tables:

Retail_Lakehouse
├── Tables
│   ├── customers
│   ├── products
│   ├── orders
│   ├── order_items
│   └── sales
└── Files

Lakehouse tables are commonly Delta Lake tables, processed via Spark and exposed through Fabric's SQL analytics capabilities.

6. Delta Lake

Delta Lake = Parquet Data + Transaction Log

Sales Delta Table
├── Parquet Data Files
│   ├── part-0001.parquet
│   ├── part-0002.parquet
│   └── part-0003.parquet
└── _delta_log
    ├── transaction metadata
    └── table versions

Delta Lake adds table-management and transactional capabilities on top of Parquet-based data.

7. Why Delta Lake?

With simple files, managing consistency when multiple users update data simultaneously can become difficult. Delta Lake provides: - ACID Transactions - Schema Enforcement - Schema Evolution - Table Versioning - Time Travel - MERGE / UPDATE / DELETE support - Reliable batch processing

8. Parquet

Delta Lake tables typically store their underlying data using Apache Parquet — a column-oriented data file format designed for analytics.

Traditional row-oriented representation:

1, Ravi,  India, 50000
2, John,  USA,   70000
3, David, UK,    60000

Columnar storage organizes values by column instead:

Customer_ID:   1, 2, 3
Customer_Name: Ravi, John, David
Country:       India, USA, UK
Revenue:       50000, 70000, 60000

This is useful for analytical queries that often read only selected columns.

9. Delta + Parquet relationship

DELTA LAKE TABLE
├── Parquet Files → Data
└── Delta Log → Transactions, Metadata, Versions

Parquet stores the data. Delta Lake adds the transactional table layer.

10. Lakehouse and OneLake

Fabric Lakehouses use OneLake as their storage foundation. You don't normally provision a separate Azure storage account for every Fabric Lakehouse — Fabric manages the underlying storage integration.

11. Lakehouse and Spark

Fabric Data Engineering provides Apache Spark capabilities for processing Lakehouse data (PySpark, Spark SQL, Python, Scala).

Typical data engineering flow: Read Raw Data → Remove Duplicates → Handle NULLs → Join Tables → Apply Business Rules → Write Delta Table.

12. Lakehouse SQL Analytics Endpoint

A Fabric Lakehouse also provides a SQL analytics endpoint, allowing users to query Lakehouse tables using T-SQL:

SELECT
  Region,
  SUM(SalesAmount) AS TotalSales
FROM dbo.Sales
GROUP BY Region;

Particularly useful for SQL developers and analysts who don't want to use Spark for every analytical query.

13. One data source, multiple engines

Delta Tables can be accessed via Spark (Data Engineer), SQL (SQL Analyst), and Power BI (BI Developer) — different users, same analytical data.

14. Medallion Architecture

A very common Lakehouse design divides data into three logical stages:

BRONZE → SILVER → GOLD (Raw → Cleaned → Business Ready)

15. Bronze Layer

Contains raw source data (customers_raw, products_raw, orders_raw, sales_raw). Characteristics: raw data, minimal transformation, source-level structure, historical data, data ingestion layer.

16. Silver Layer

Contains cleaned and standardized data. Transformations may include: remove duplicates, handle NULL values, correct data types, standardize dates, clean strings, join data, validate records.

Example: sales_raw → Remove duplicates → Fix NULL values → Standardize currency → sales_clean

17. Gold Layer

Contains business-ready data (daily_sales, monthly_revenue, customer_360, sales_by_region, product_performance). Typically optimized for Reporting, Dashboards, KPIs, Business Analytics, Power BI.

18. Complete Medallion Architecture

DATA SOURCES (SQL Server, Oracle, Salesforce, Files)
        │
   Data Factory
        │
   ┌─────────┐
   │ BRONZE  │  Raw Data
   └─────────┘
        │  Spark / ETL
   ┌─────────┐
   │ SILVER  │  Cleaned Data
   └─────────┘
        │  Business Rules
   ┌─────────┐
   │  GOLD   │  Business Ready
   └─────────┘
        │
  Semantic Model
        │
     Power BI

Easy memory: Bronze = Raw, Silver = Clean, Gold = Business

19. Example: Retail Lakehouse

Sources: SQL Server → Orders, Oracle → Inventory, Salesforce → Customers, CSV → Products.

Flow: Sources → Fabric Pipeline → Bronze (orders_raw, customers_raw, products_raw) → Spark Notebook → Silver (orders, customers, products) → Business Rules → Gold (fact_sales, dim_customer, dim_product) → Semantic Model → Power BI.

20. Lakehouse and Star Schema

The Gold layer can be designed using a Star Schema for BI workloads:

        DIM_DATE
           │
DIM_CUSTOMER ── FACT_SALES ── DIM_PRODUCT
           │
        DIM_STORE

FACT_SALES could contain: Sale_ID, Date_Key, Customer_Key, Product_Key, Store_Key, Quantity, Sales_Amount, Discount, Profit. Dimensions provide descriptive attributes. Highly useful for Power BI semantic models.

21. Lakehouse + Power BI

Lakehouse → Gold Delta Tables → Semantic Model → Power BI → Sales Dashboard / Customer Dashboard / Finance Dashboard.

22. Direct Lake

Power BI can use Direct Lake for supported Fabric scenarios: OneLake → Delta Tables → Direct Lake → Semantic Model → Power BI.

This is different from the traditional pattern of always importing data into a separate Power BI model storage layer.

23. OneLake Shortcuts in Lakehouse

Instead of copying 10 TB of external data into the Lakehouse, you may create a OneLake Shortcut:

Lakehouse
└── Files
    ├── local_data
    └── external_sales → Shortcut

This can reduce unnecessary duplication and data movement.

24. Lakehouse vs Data Lake

25. Lakehouse vs Warehouse

A project can use both: Raw Sources → Lakehouse → Data Engineering → Fabric Warehouse → Power BI. Or some architectures may use the Lakehouse Gold layer directly for Power BI.

26. Lakehouse vs Warehouse — when to choose?

Choose Lakehouse when: you work heavily with Spark; need files + tables; have structured and semi-structured data; use Python/PySpark; want Medallion Architecture; data engineering is a major requirement.

Choose Warehouse when: team primarily uses T-SQL; data is strongly relational; traditional warehouse development is preferred; star schema is central; SQL-based BI workloads dominate.

They aren't necessarily mutually exclusive.

27. Complete Fabric Lakehouse Architecture

DATA SOURCES (SQL, Oracle, SAP, APIs, Salesforce, CSV, JSON, IoT)
        │
DATA INGESTION (Data Factory, Pipeline, Dataflow Gen2)
        │
════════════════════════
ONELAKE — FABRIC LAKEHOUSE
  BRONZE → Spark/ETL → SILVER → Business Rules → GOLD
════════════════════════
        │
  ┌─────┼──────────────┐
Spark   SQL    Semantic Model → Direct Lake
        │              │
        └──────┬───────┘
            Power BI
               │
          Business Users

28. Important terminology

29. Interview Question — What is a Lakehouse?

A strong answer: A Lakehouse in Microsoft Fabric combines the flexibility of a data lake with structured analytics capabilities commonly associated with a data warehouse. It uses OneLake as its storage foundation and supports Files and Delta Lake Tables. Data engineers can process the data using Apache Spark, while SQL users can query Lakehouse tables through the SQL analytics endpoint, and Power BI can consume the analytical data through Fabric's semantic-model and Direct Lake capabilities.

30. Interview Question — Explain Lakehouse Architecture

A strong answer: In Fabric Lakehouse architecture, data is ingested from source systems using tools such as Data Factory and stored in a Lakehouse on OneLake. Data is commonly processed using a Medallion Architecture consisting of Bronze for raw data, Silver for cleaned and standardized data, and Gold for business-ready data. Lakehouse tables use Delta Lake over Parquet, allowing the same data to be processed through Spark, queried through SQL, and consumed by Power BI.

31. Quick revision

LAKEHOUSE = Data Lake + Data Warehouse Concepts

Storage: OneLake
Lakehouse Areas: Files, Tables
Table Technology: Delta Lake (Parquet Data + Transaction Log)
Processing: Spark, PySpark, SQL, Notebooks, Data Factory

Medallion Architecture:
BRONZE (Raw Data) → SILVER (Clean Data) → GOLD (Business Data) → Power BI

The one diagram to remember:

SOURCES → DATA FACTORY → ONELAKE → LAKEHOUSE
                                       │
                              ┌────────┴────────┐
                            FILES              TABLES
                                              (DELTA LAKE)
                              └────────┬────────┘
                                    BRONZE → SILVER → GOLD
                                       │
                          ┌────────────┼────────────┐
                        SPARK          SQL        POWER BI

Key takeaway: The Fabric Lakehouse is not simply a folder where you store files. It is an analytical architecture on OneLake that combines files, Delta tables, Spark processing, SQL access, medallion data engineering, and Power BI integration into a unified Fabric experience.

Lesson 2.3 – Files

Learning Objectives

• Understand the Files section in a Lakehouse.
• Learn how files are stored in Microsoft Fabric.
• Explore supported file formats.
• Upload and organize files.
• Access files using Spark and SQL.
• Follow best practices for file management.

What is the Files Section?

The Files section in a Microsoft Fabric Lakehouse is a storage area where you can upload and organize raw or processed data files. These files are stored in OneLake and are available for data engineering, data science, and analytics workloads.

Unlike the Tables section, files in the Files area are not automatically registered as database tables. They remain as files until you load or transform them into tables.

Why Use the Files Section?

• Store raw data from external sources.
• Keep backup copies of datasets.
• Organize data into folders.
• Store files before transforming them into Delta tables.
• Share files across Fabric workloads.

Files Architecture

Microsoft Fabric
    │
 Lakehouse
    │
┌───┴────────────┐
Files Area    Tables Area
CSV, JSON,    Delta Tables
Parquet,      SQL Queryable
Images,
Text Files
    │
 OneLake

Supported File Formats

Folder Structure

Files
├── Sales
│     ├── sales_2025.csv
│     ├── sales_2026.csv
├── Customers
│     ├── customers.csv
├── Products
│     ├── products.csv
└── Archive
      ├── old_sales.csv

Using folders improves organization and simplifies data management.

Uploading Files

1. Open your Lakehouse.
2. Select the Files section.
3. Click Upload.
4. Choose a file from your computer (e.g., sales.csv).
5. Click Open. Fabric uploads the file into OneLake.

Creating Folders

Example folders: Sales, HR, Finance, Marketing, Archive. Folders make it easier to locate datasets.

Previewing Files

Supported previews: CSV, JSON, TXT, Parquet (schema preview). The preview allows you to inspect the data before processing it.

Reading Files with Spark

df = spark.read.csv(
    "Files/Sales/sales.csv",
    header=True,
    inferSchema=True
)
display(df)

Reading Parquet Files

df = spark.read.parquet("Files/Sales/sales.parquet")
display(df)

Loading Files into a Table

df = spark.read.csv(
    "Files/Sales/sales.csv",
    header=True
)
df.write.mode("overwrite").saveAsTable("Sales")

The Sales table will appear under the Tables section of the Lakehouse.

Files vs Tables

Common Use Cases

• Data Ingestion — Upload raw CSV files received from customers.
• Backup Storage — Store original datasets before performing transformations.
• Machine Learning — Keep training datasets in the Files section for notebooks.
• Log Storage — Store application logs in text or JSON format.
• Data Exchange — Exchange files between teams using OneLake.

Best Practices

• Use meaningful folder names.
• Separate raw, processed, and archive data.
• Keep original files unchanged for auditing.
• Prefer Parquet for large datasets due to better performance and compression.
• Remove unused files to reduce clutter.
• Apply workspace permissions to secure sensitive data.

Common Issues

• Upload Failed — File size exceeds limits; network interruption; insufficient permissions.
• File Not Visible — Refresh the Lakehouse; verify correct folder; check workspace access.
• Schema Errors — Incorrect delimiter in CSV; missing header row; inconsistent data types.

Real-World Example

A retail company receives a daily sales file (sales_2026_08_21.csv) from its stores. The file is uploaded to the Files section. A Data Pipeline validates and cleans the data. The cleaned data is written to a Delta table in the Tables section. Business analysts query the table using SQL and build Power BI reports. This workflow keeps the raw data intact while providing optimized, queryable tables for analytics.

Interview Questions

1. What is the Files section in a Microsoft Fabric Lakehouse? The Files section stores raw and processed files in OneLake. These files are not automatically registered as database tables.
2. Which file formats are commonly supported? CSV, Parquet, JSON, TXT, Excel, Avro, XML, and image formats.
3. Can you query files directly using SQL? Generally, files are not directly queryable as database tables. They are typically loaded into Delta tables or accessed through Spark before SQL-based analytics.
4. Why is Parquet recommended for large datasets? Parquet is a columnar storage format that provides better compression and faster query performance than row-based formats like CSV.
5. What is the difference between the Files and Tables sections? The Files section stores unregistered files for staging or processing, while the Tables section contains managed Delta tables that are registered in the Lakehouse metadata and can be queried using SQL.

Key Takeaways

• The Files section is used to store raw and processed files in OneLake.
• Files can be organized using folders for easier management.
• Common formats include CSV, Parquet, JSON, and TXT.
• Files are typically transformed into Delta tables before SQL analytics.
• Using the Files section effectively supports scalable data ingestion, processing, and governance in Microsoft Fabric.

Lesson 2.4 – Tables

Learning Objectives

• Understand the Tables section in a Lakehouse.
• Learn how tables are stored in Microsoft Fabric.
• Differentiate between Files and Tables.
• Query tables using SQL.
• Create and manage Delta tables.
• Follow best practices for table management.

What is the Tables Section?

The Tables section in a Microsoft Fabric Lakehouse contains structured data stored as Delta Lake tables. Unlike files, tables are registered in the Lakehouse metadata, making them immediately available for SQL queries, Spark notebooks, Data Pipelines, and Power BI.

All tables are stored in OneLake using the Delta Lake format.

Why Use Tables?

• Store structured and processed data.
• Enable fast SQL queries.
• Support reporting and dashboards.
• Share data across Fabric workloads.
• Maintain data consistency with ACID transactions.

Tables Architecture

Microsoft Fabric
      │
  Lakehouse
      │
┌─────┴──────────┐
Files Area    Tables Area
(Raw Files)   (Delta Tables)
      │
   OneLake
      │
SQL Analytics Endpoint
      │
Power BI / Spark / SQL

Characteristics of Tables

• Stored in Delta Lake format.
• Registered in the Lakehouse catalog.
• Support SQL queries.
• Support Spark operations.
• ACID compliant.
• Automatically available through the SQL Analytics Endpoint.
• Can be used by Power BI without data duplication.

How Tables are Created

Method 1: Load Data from Files — Upload a CSV file and load it into a table.

Method 2: SQL

CREATE TABLE Customers
(
  CustomerID INT,
  CustomerName STRING,
  Country STRING
);

Method 3: Spark Notebook

data = [
    (1, "John", "USA"),
    (2, "Anita", "India"),
    (3, "David", "UK")
]
df = spark.createDataFrame(data, ["CustomerID", "CustomerName", "Country"])
df.write.mode("overwrite").saveAsTable("Customers")

Method 4: Data Pipeline — can load data from SQL Server, Oracle, SAP, Azure SQL, CSV, REST APIs, Amazon S3, ADLS Gen2.

Viewing Tables

Lakehouse
├── Files
└── Tables
      ├── Customers
      ├── Sales
      ├── Products
      └── Orders

Clicking a table displays columns, data preview, data types, row count (where available), and table properties.

Querying Tables

SELECT * FROM Sales;

SELECT * FROM Sales WHERE Country = 'India';

SELECT * FROM Sales ORDER BY Amount DESC;

SELECT Country, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Country;

Joining Tables

SELECT
  c.CustomerName,
  s.Amount
FROM Customers c
JOIN Sales s
  ON c.CustomerID = s.CustomerID;

Table Metadata

Includes: table name, column names, data types, storage format, owner, creation date, last modified date. Metadata helps Fabric efficiently manage and optimize tables.

Data Types

Table Operations

-- Insert
INSERT INTO Customers VALUES (4, 'Rahul', 'India');

-- Update
UPDATE Customers SET Country = 'USA' WHERE CustomerID = 1;

-- Delete
DELETE FROM Customers WHERE CustomerID = 4;

-- Drop
DROP TABLE Customers;

Tables vs Files

SQL Analytics Endpoint

Every Lakehouse includes a SQL Analytics Endpoint, allowing users to query tables using T-SQL, connect SQL tools, build Power BI reports, and perform analytics without moving data.

Best Practices

• Use meaningful table names (e.g., Sales, Customers, Orders).
• Define appropriate data types.
• Store analytical data as Delta tables.
• Avoid duplicate tables.
• Partition large tables when appropriate.
• Regularly optimize and maintain large datasets.
• Implement workspace permissions for secure access.

Common Issues

• Table Not Visible — Refresh the Lakehouse; verify table creation completed; ensure correct workspace.
• SQL Query Failed — Incorrect SQL syntax; missing permissions; table/column name mismatch.
• Data Type Errors — Incorrect schema definition; invalid data values during ingestion; inconsistent source data.

Real-World Example

A retail company receives daily sales data as CSV files. The files are uploaded to the Files section. A Data Pipeline cleans and validates the data. The transformed data is stored in the Sales Delta table. Business analysts query the table using SQL. Power BI dashboards use the same table to display daily sales performance.

Interview Questions

1. What is the Tables section in a Microsoft Fabric Lakehouse? The Tables section stores structured data as Delta Lake tables that are registered in the Lakehouse catalog and can be queried using SQL.
2. What storage format do Lakehouse tables use? Delta Lake.
3. How can tables be created in Microsoft Fabric? By loading files, using SQL, writing data with Spark notebooks, or through Data Pipelines.
4. What is the SQL Analytics Endpoint? A built-in SQL interface that allows users to query Lakehouse tables using T-SQL and connect reporting tools like Power BI.
5. What is the difference between Files and Tables? Files store raw, unregistered data, while Tables store structured Delta Lake data that is registered in the catalog and optimized for SQL queries and analytics.

Key Takeaways

• The Tables section stores structured Delta Lake tables in OneLake.
• Tables are registered in the Lakehouse catalog and support SQL queries.
• Data can be loaded into tables from files, SQL scripts, Spark notebooks, or Data Pipelines.
• The SQL Analytics Endpoint provides direct access to Lakehouse tables for analytics.
• Delta tables offer ACID transactions, improved reliability, and seamless integration with Microsoft Fabric workloads.

Lesson 2.5 – Delta Lake

Learning Objectives

• Understand what Delta Lake is.
• Learn why Delta Lake is used in Microsoft Fabric.
• Explore the architecture of Delta Lake.
• Understand ACID transactions and the Delta Log.
• Learn about Time Travel, Schema Enforcement, and Schema Evolution.
• Compare Delta Lake with Parquet.
• Follow best practices for working with Delta tables.

What is Delta Lake?

Delta Lake is an open-source storage framework that enhances data lakes with ACID transactions, schema management, versioning, and high-performance data processing.

In Microsoft Fabric, all Lakehouse tables are stored in Delta Lake format, making them reliable, scalable, and optimized for analytics.

Think of Delta Lake as a smart layer on top of Parquet files that adds database-like capabilities to data stored in OneLake.

Why Delta Lake?

Traditional data lakes often face: data corruption due to concurrent writes, duplicate records, no transaction support, difficult schema management, limited recovery options. Delta Lake solves these issues by adding reliability and advanced data management features.

Delta Lake Architecture

Microsoft Fabric
       │
   Lakehouse
       │
 Delta Lake Table
       │
┌──────┴──────┐
Parquet Files   _delta_log
(Actual Data)   (Transaction Log)
       │
   OneLake

A Delta table consists of Parquet files (actual data) and _delta_log (records every transaction and metadata change).

Components of Delta Lake

1. Parquet Files — the actual table data, columnar format. Benefits: efficient storage, high compression, faster analytical queries, optimized for big data.

2. Delta Log — hidden _delta_log folder storing table versions, inserts, updates, deletes, schema changes, and metadata. Ensures every operation is tracked and recoverable.

ACID Transactions

Example of Atomicity: Suppose you insert 10,000 sales records and the process fails after 7,000 rows. Without Delta Lake, partial data may remain. With Delta Lake, the entire transaction is rolled back, preventing incomplete data.

Time Travel

Allows you to view previous versions of a table, recover accidentally deleted data, audit historical changes, and compare data across versions.

Example: Version 1 (Original Data) → Version 2 (New Records Added) → Version 3 (Records Updated) → Version 4 (Incorrect Data Loaded). You can query or restore Version 3 if needed.

Schema Enforcement

Prevents invalid data from being written. E.g., if CustomerID is INT and you try loading CustomerID = "ABC", it will fail. This helps maintain data quality.

Schema Evolution

Delta Lake can automatically accommodate a new column (when enabled), allowing schemas to evolve without recreating the table — e.g., adding a City column to an existing CustomerID | Name | Amount table.

Versioning

This version history enables auditing and recovery.

Delta Lake vs Parquet

Delta Lake Operations

-- Create a Delta Table
CREATE TABLE Sales
(
  SaleID INT,
  CustomerName STRING,
  Amount DECIMAL(10,2)
);

-- Insert Data
INSERT INTO Sales VALUES
(1, 'John', 2500.50),
(2, 'Anita', 3200.00);

-- Update Data
UPDATE Sales SET Amount = 3500.00 WHERE SaleID = 2;

-- Delete Data
DELETE FROM Sales WHERE SaleID = 1;

In Fabric Lakehouse, tables are created in Delta format by default. Unlike plain Parquet, Delta Lake supports UPDATE and DELETE operations efficiently.

Merge (Upsert)

MERGE INTO Sales AS Target
USING NewSales AS Source
ON Target.SaleID = Source.SaleID
WHEN MATCHED THEN
    UPDATE SET Target.Amount = Source.Amount
WHEN NOT MATCHED THEN
    INSERT (SaleID, CustomerName, Amount)
    VALUES (Source.SaleID, Source.CustomerName, Source.Amount);

Especially useful for incremental data loading.

Performance Benefits

Columnar storage (Parquet), metadata optimization, efficient file pruning, predicate pushdown, data skipping, optimized reads and writes.

Real-World Example

An e-commerce company stores daily order data in a Delta table. New orders are appended nightly. Incorrect orders are updated. Cancelled orders are deleted. Analysts query the latest data. Auditors review previous table versions using Time Travel. This ensures data remains accurate and recoverable without creating duplicate datasets.

Best Practices

• Store analytical tables in Delta format.
• Use meaningful table and column names.
• Keep schemas consistent across data loads.
• Use MERGE for incremental updates instead of deleting and reloading data.
• Partition very large tables where appropriate.
• Periodically optimize large Delta tables to improve query performance.
• Monitor transaction history for auditing and troubleshooting.

Common Issues

• Schema Mismatch — incoming data doesn't match existing schema. Solution: validate source schema or enable schema evolution.
• Duplicate Records — can occur during repeated data loads. Solution: use MERGE instead of repeated INSERT.
• Slow Queries — too many small files, poor partitioning, large unfiltered scans. Solution: optimize tables and use partitioning.

Interview Questions

1. What is Delta Lake? An open-source storage framework that adds ACID transactions, schema management, versioning, and reliable data processing to data lakes.
2. Why does Microsoft Fabric use Delta Lake? It provides reliable, high-performance storage with transaction support, schema enforcement, version history, and seamless integration with analytics workloads.
3. What is the _delta_log folder? The transaction log that records all table changes, metadata, and versions, enabling ACID transactions and Time Travel.
4. What is Time Travel in Delta Lake? It allows users to access previous versions of a Delta table for auditing, recovery, and historical analysis.
5. How is Delta Lake different from Parquet? Delta Lake builds on Parquet by adding transaction logs, ACID compliance, version history, schema enforcement, schema evolution, and support for updates and deletes.

Key Takeaways

• Delta Lake is the default storage format for Lakehouse tables in Microsoft Fabric.
• It combines Parquet files with a transaction log (_delta_log) to provide reliability and performance.
• Features such as ACID transactions, Time Travel, Schema Enforcement, and Schema Evolution make Delta Lake suitable for enterprise analytics.
• Delta Lake supports efficient INSERT, UPDATE, DELETE, and MERGE operations.
• Using Delta Lake enables scalable, governed, and high-performance data management in Microsoft Fabric.

Lesson 2.6 – Managed vs Shortcut Tables

In a Microsoft Fabric Lakehouse, data can either be stored and managed within the Fabric/OneLake environment or made available through OneLake Shortcuts that reference data stored somewhere else.

Managed Table = Fabric manages the table and its underlying data in OneLake. Shortcut Table = Fabric provides access to data through a shortcut without requiring a traditional copy into that Lakehouse location.

1. Basic architecture

FABRIC LAKEHOUSE
   ├── MANAGED TABLE → Data stored and managed in OneLake → Delta Table
   └── SHORTCUT → Data referenced from another location → External/other data

The key question is: Where is the actual data physically stored and who manages it?

2. What is a Managed Table?

A managed table is a table whose underlying data and metadata are managed through the Lakehouse.

Sales_Lakehouse
└── Tables
    ├── Customers
    ├── Products
    └── Sales
         │
      OneLake
         │
┌────────┴────────┐
Parquet Files   Delta Log

Fabric/Spark manages the table and its storage location.

3. Example of creating a Managed Table

df_sales.write \
    .format("delta") \
    .mode("overwrite") \
    .saveAsTable("sales")

Resulting structure:

Lakehouse
└── Tables
    └── sales
        ├── Parquet data
        └── _delta_log

Query it:

SELECT * FROM sales;
-- or, via the Lakehouse SQL analytics endpoint:
SELECT * FROM dbo.sales;

4. Managed Table lifecycle

CREATE TABLE → Fabric/Spark manages table storage → INSERT/UPDATE/MERGE →
Data changes → DROP TABLE → Managed table/data lifecycle

This is different from simply referencing externally managed data.

5. What is a Shortcut?

A OneLake Shortcut is a logical reference to data located somewhere else — like a Windows shortcut pointing to a Documents folder. The shortcut itself isn't another copy of all the files.

6. Shortcut architecture

Without a shortcut: ADLS Gen2 → (Copy Data) → OneLake → Lakehouse

With a shortcut: ADLS Gen2 → (Reference) → OneLake Shortcut → Lakehouse

The original data remains in its source storage location while Fabric provides a logical path to it.

7. Shortcut Table

When a shortcut points to compatible tabular data, particularly Delta-formatted data, it can be surfaced as a table in the Lakehouse.

Sales_Lakehouse
└── Tables
    ├── Local_Customers
    ├── Local_Products
    └── External_Sales → Shortcut → ADLS Gen2 → Delta Table

From the user's perspective, Tables shows Customers, Products, and External_Sales — but External_Sales may actually reference data stored outside that Lakehouse.

8. Shortcut does not mean copy

Traditional Copy: External Storage (10 TB) → Copy → OneLake (10 TB) — now potentially two physical copies exist.

With a shortcut: External Storage → Shortcut → Fabric Lakehouse — Fabric references the source rather than requiring a duplicate copy.

9. Where can shortcuts point?

Common scenarios include references to: another location within OneLake, ADLS Gen2, Amazon S3, and other supported external storage. The exact supported shortcut types should be checked against current Fabric documentation when designing a production architecture.

10. Internal OneLake Shortcut

Suppose Finance_Lakehouse owns Customers data and the Sales team needs the same data. Instead of copying it, you could use a shortcut: Finance_Lakehouse → Customers → (Shortcut) → Sales_Lakehouse → Customers_Shortcut. This allows multiple teams to work with shared data without unnecessarily duplicating it.

11. External Shortcut example

ADLS Gen2
/retail/
├── customers/
├── products/
└── sales/

Fabric could reference these through shortcuts:

Retail_Lakehouse
└── Tables
    ├── Customers (Shortcut)
    ├── Products (Shortcut)
    └── Sales (Shortcut)
         │
     ADLS Gen2

The original datasets remain in ADLS.

12. Managed vs Shortcut — storage

Managed Table: Fabric Lakehouse → Managed Table → OneLake → Actual Data

Shortcut: Fabric Lakehouse → Shortcut → Actual Data → Other OneLake location or External Storage

Managed → Fabric/Lakehouse owns the managed storage lifecycle. Shortcut → Fabric references data whose primary storage lifecycle exists elsewhere.

13. Managed vs Shortcut comparison

14. What happens if the source changes?

Copied/Managed approach: if you copied the source yesterday (1,500 rows now vs 1,000 rows copied), you may need to run your ingestion process again to update the managed table.

Shortcut approach: because the shortcut references the underlying data, source changes can become visible through the shortcut according to the source format, caching behavior, and supported Fabric functionality.

15. What happens if the shortcut is deleted?

If you remove the shortcut, you are removing the Fabric reference — the source data remains under the ownership of the original source system and still exists.

16. What happens if the source data is deleted?

If the actual source data is deleted or becomes inaccessible, the shortcut cannot provide the expected data. A shortcut depends on the target data continuing to exist and remaining accessible.

17. Shortcut dependency

Managed Table: Local Managed Data — lower dependency on original source after ingestion.

Shortcut: External Data — dependency on target availability, permissions and structure.

Shortcuts can reduce duplication, but you need to manage this dependency.

18. Example — Bronze, Silver and Gold

External ADLS → Shortcut → BRONZE (Raw Data)
    │
Spark Transformation
    │
SILVER (Managed Delta Tables)
    │
Business Transformation
    │
GOLD (Managed Delta Tables)
    │
Power BI

Bronze = Reference existing data; Silver = Clean and standardize; Gold = Business-ready data. You don't necessarily need to copy the raw data first.

19. Example architecture

Instead of copying 20 TB of raw historical data from ADLS Gen2, you might use a shortcut for Bronze, then Spark notebooks to build managed Delta tables for Silver and Gold, feeding Power BI. This provides a practical combination of virtualized access to raw data and Fabric-managed curated data.

20. When should you use Managed Tables?

• Fabric should own the curated dataset
• You need to transform source data
• You want controlled Delta tables
• You need Silver/Gold data
• You need independent lifecycle management
• You don't want analytics availability tightly coupled to the original source
• You need optimized business-ready data

21. When should you use Shortcuts?

• Data already exists elsewhere
• You want to avoid unnecessary copying
• Dataset is very large
• Multiple teams need shared data
• You want to reference another OneLake location
• Data remains owned by another team/system
• You want faster onboarding of existing lake data

22. When might a shortcut not be ideal?

• Source availability is unreliable
• Source schema changes frequently
• You require complete lifecycle control
• You need strong isolation from source changes
• You need heavy transformation before consumption
• External permissions are difficult to manage

In these situations, ingesting the data into a managed Fabric table may provide more control.

23. Shortcut vs Pipeline Copy

24. Important: Shortcut is not an ETL process

A shortcut primarily gives access/reference. It doesn't automatically mean clean data, transform data, remove duplicates, apply business rules, or create aggregates. For transformations, you still use Spark Notebooks, Data Factory, Dataflow Gen2, SQL, or other Fabric transformation capabilities.

25. Managed and Shortcut together

SOURCE SYSTEMS → ADLS/S3 → Shortcut → BRONZE
    │
Spark Notebook
    │
Managed SILVER
    │
Managed GOLD
    │
Power BI

This gives you: Shortcuts to reduce unnecessary raw-data copying, and Managed Tables to control transformed/curated data.

26. Real-world retail example

ADLS
├── sales/
├── customers/
└── products/

Create shortcuts into Bronze (sales, customers, products), transform via Spark into Silver Managed Tables (sales_clean, customers_clean, products_clean), then apply business rules to build Gold Managed Tables (fact_sales, dim_customer, dim_product, dim_date), feeding Power BI.

27. Complete architecture

EXTERNAL DATA SOURCES (ADLS, S3, Other OneLake Data)
       │  Reference
   ONELAKE SHORTCUT
       │
════════════════════
FABRIC LAKEHOUSE — BRONZE (Shortcut Data)
════════════════════
       │  Spark/ETL
SILVER (Managed Delta Tables)
       │  Business Rules
GOLD (Managed Delta Tables)
       │
  Semantic Model
       │
    Power BI

28. Interview question — Managed vs Shortcut Tables

A strong answer: A managed table in Microsoft Fabric is a table whose data and metadata are managed within the Lakehouse using OneLake, commonly as a Delta table. A shortcut table instead references data stored in another OneLake location or supported external storage without requiring a traditional copy into the Lakehouse. Managed tables provide greater control over the table and data lifecycle, while shortcuts are useful for reducing data duplication and sharing or accessing existing data.

29. Interview scenario

Q: You have 50 TB of historical data already stored in ADLS Gen2. Do you copy everything into Fabric?

A good answer: Not necessarily. If the existing data format and architecture are compatible with the Fabric workload, I would evaluate using OneLake Shortcuts to reference the ADLS data instead of immediately copying all 50 TB. I could use the shortcut as part of the Bronze/raw layer and then create managed Delta tables for Silver and Gold where cleansing, standardization, optimization, and business transformations are required.

30. Easy memory trick

MANAGED — Data lives under Lakehouse-managed storage; Fabric manages table lifecycle. Best for: Silver/Gold, Curated Data, Business Data.

SHORTCUT — Data lives somewhere else; Fabric references it. Best for: Existing Data, Shared Data, Large Raw Data, Bronze Layer.

Key Takeaway

Managed tables are appropriate when Fabric should own and control the curated table and its lifecycle. Shortcuts are appropriate when data already exists elsewhere and you want Fabric to access it without unnecessary copying.

A common practical pattern is: Shortcut for Bronze → Managed Delta Tables for Silver → Managed Delta Tables for Gold → Power BI.

Lesson 2.7 – External Data

Learning Objectives

• Understand what external data is in Microsoft Fabric.
• Learn the different external data sources supported by Fabric.
• Connect external data to a Lakehouse.
• Understand Import vs Shortcut approaches.
• Learn best practices for integrating external data.
• Explore real-world use cases.

What is External Data?

External Data refers to data that resides outside Microsoft Fabric but can be accessed, imported, or referenced for analytics and reporting. Microsoft Fabric allows you to connect to many external systems, enabling you to analyze data without unnecessary duplication.

Why Use External Data?

Organizations often store data across multiple platforms: on-premises databases, cloud storage, business applications, enterprise data warehouses, APIs, ERP and CRM systems. Microsoft Fabric enables you to access these sources from a single analytics platform.

External Data Architecture

External Sources (SQL Server, Oracle, ADLS Gen2, Amazon S3)
       │
Microsoft Fabric
       │
Data Pipeline / Dataflow
       │
Import Data  or  OneLake Shortcut
       │
OneLake Lakehouse
       │
SQL • Spark • Power BI

Types of External Data Sources

Ways to Use External Data

1. Import Data — data is copied from the external source into OneLake. - Advantages: faster query performance, available even if the source is offline, optimized for analytics. - Disadvantages: requires additional storage, data synchronization is needed, duplicate copies may exist.

2. OneLake Shortcuts — creates a reference to external data without copying it into OneLake. - Advantages: no data duplication, lower storage costs, single source of truth, near real-time access to external data. - Disadvantages: depends on source system availability, performance may depend on the external source, access permissions must be maintained on the source.

Import vs Shortcut

Connecting External Data

Method 1: Upload Files — Open a Lakehouse → Go to Files → Click Upload → Select a CSV, Excel, JSON, or Parquet file → Upload to OneLake.

Method 2: Data Pipeline — Connect to SQL Server, Oracle, Azure SQL, SAP, REST API, Amazon S3, ADLS Gen2. Steps: Create a Data Pipeline → Add a Copy Data activity → Configure the source connection → Select OneLake as the destination → Run or schedule the pipeline.

Method 3: Dataflow Gen2 — Connect to multiple sources, clean and transform data using a visual interface, load data into Lakehouse tables.

Method 4: OneLake Shortcut — Open your Lakehouse → Go to Files or Tables → Select New Shortcut → Choose the external storage location → Authenticate and create the shortcut. The external data becomes available without being copied into OneLake.

Common External Data Sources

Security Considerations

• Use Microsoft Entra ID authentication where supported.
• Grant only the required permissions.
• Protect sensitive credentials.
• Encrypt data during transmission.
• Audit external connections regularly.

Best Practices

• Use Shortcuts when you want to avoid copying data.
• Import data when high-performance analytics or offline availability is required.
• Organize external data by business domain.
• Schedule regular refreshes for imported datasets.
• Validate data quality before loading into production.
• Monitor pipeline and refresh failures.

Common Issues

• Connection Failed — incorrect credentials, network/firewall restrictions, source system unavailable.
• Authentication Error — expired credentials, missing permissions, incorrect authentication method.
• Slow Data Loading — large datasets, limited network bandwidth, source system performance issues.

Real-World Example

A retail company stores customer data in SQL Server, product information in Oracle, and historical sales files in Amazon S3. Customer data is imported from SQL Server using a Data Pipeline. Product data is refreshed from Oracle daily. Sales files are accessed through a OneLake Shortcut to Amazon S3. All datasets are combined in a Lakehouse, and Power BI dashboards provide a unified view of sales, inventory, and customer insights.

Interview Questions

1. What is external data in Microsoft Fabric? Data stored outside Microsoft Fabric that can be imported or accessed through connectors and OneLake Shortcuts.
2. What is the difference between importing data and using a OneLake Shortcut? Importing copies the data into OneLake, while a OneLake Shortcut references external data without copying it.
3. Name some external data sources supported by Microsoft Fabric. SQL Server, Oracle, Azure SQL Database, ADLS Gen2, Amazon S3, SharePoint, Salesforce, REST APIs, CSV, Excel, JSON, and Parquet files.
4. When should you use a OneLake Shortcut? When you want to avoid data duplication and access the latest data directly from the source system.
5. What are common methods to bring external data into Microsoft Fabric? Data Pipelines, Dataflow Gen2, file uploads, and OneLake Shortcuts.

Key Takeaways

• External Data is data stored outside Microsoft Fabric that can be integrated for analytics.
• Microsoft Fabric supports a wide range of databases, cloud storage services, SaaS applications, files, and APIs.
• You can either import external data into OneLake or use OneLake Shortcuts to access it without duplication.
• Data Pipelines and Dataflow Gen2 are commonly used to ingest and transform external data.
• Choosing between Import and Shortcut depends on your performance, storage, and data freshness requirements.

Lesson 2.8 – Create Lakehouse

Learning Objectives

• Understand what a Lakehouse is.
• Create a Lakehouse in Microsoft Fabric.
• Explore the Lakehouse interface.
• Understand the Files and Tables sections.
• Access the SQL Analytics Endpoint.
• Learn best practices for organizing Lakehouse resources.

What is a Lakehouse?

A Lakehouse is the primary data storage and analytics component in Microsoft Fabric. It combines the flexibility of a Data Lake with the performance and management capabilities of a Data Warehouse.

A Lakehouse stores both structured and unstructured data in OneLake using the Delta Lake format, making it accessible to SQL, Spark, Data Pipelines, Data Science, and Power BI.

Why Create a Lakehouse?

• Store raw and processed data.
• Manage files and Delta tables in one place.
• Query data using SQL.
• Process data with Spark notebooks.
• Build Power BI reports and dashboards.
• Share data across multiple Fabric workloads.

Lakehouse Architecture

Microsoft Fabric
      │
  Workspace
      │
  Lakehouse
      │
┌─────┼──────────────┐
Files Tables      SQL Analytics
(Raw)(Delta Tables) Endpoint
      │
   OneLake
      │
Power BI • Spark • Pipelines

Prerequisites

• You have access to Microsoft Fabric.
• A Fabric trial or Fabric license is active.
• You have a workspace with Member or Admin permissions.

Steps to Create a Lakehouse

1. Open Microsoft Fabric — go to https://app.fabric.microsoft.com and sign in.
2. Open a Workspace — select Workspaces, open an existing one or create a new one (e.g., "Fabric Demo Workspace").
3. Create a New Item — click + New Item, select Lakehouse.
4. Enter Lakehouse Details — provide a name (e.g., "SalesLakehouse"), optionally add a description, click Create. Microsoft Fabric provisions the Lakehouse in a few seconds.

Exploring the Lakehouse

After creation, you'll see:

SalesLakehouse
├── Files
├── Tables
├── SQL Analytics Endpoint
└── Semantic Model

Files Section — stores CSV, Excel, JSON, Parquet files, images, text files. Stored in OneLake but not automatically registered as database tables.

Tables Section — contains Delta tables, managed tables, queryable datasets. Supports SQL queries, Spark notebooks, Power BI reports, Data Pipelines.

SQL Analytics Endpoint — allows you to query Delta tables using T-SQL, connect Power BI, connect SQL tools, create views and stored procedures (where supported).

SELECT * FROM Sales;

Semantic Model — automatically created for each Lakehouse; enables Power BI report creation, data visualization, and business intelligence analysis.

Typical Lakehouse Workflow

CSV File → Files Section → Transform Data → Delta Table →
SQL Analytics Endpoint → Power BI Dashboard

Common Operations

Upload files, create folders, load CSV files into Delta tables, create notebooks, build Data Pipelines, query tables using SQL, create reports and dashboards.

Best Practices

• Use meaningful Lakehouse names (e.g., SalesLakehouse, FinanceLakehouse).
• Organize files into folders such as Raw, Processed, and Archive.
• Keep raw files unchanged for auditing purposes.
• Store analytical data as Delta tables.
• Grant workspace permissions based on user roles.
• Avoid creating duplicate datasets.

Common Issues

• Unable to Create a Lakehouse — Fabric trial is inactive; insufficient workspace permissions; workspace capacity is unavailable.
• Lakehouse Not Visible — refresh the workspace; verify correct workspace; confirm the Lakehouse was created successfully.
• Cannot Access SQL Analytics Endpoint — the Lakehouse is still provisioning; insufficient permissions; temporary service issues.

Real-World Example

A manufacturing company creates a Lakehouse named ManufacturingLakehouse. Daily production CSV files are uploaded to the Files section. A Data Pipeline validates and transforms the data. Cleaned data is stored as Delta tables. Engineers query production metrics using SQL. Managers monitor performance through Power BI dashboards connected to the same Lakehouse.

Interview Questions

1. What is a Lakehouse in Microsoft Fabric? A unified storage and analytics component that combines the capabilities of a Data Lake and a Data Warehouse using Delta Lake and OneLake.
2. What components are automatically created with a Lakehouse? Files, Tables, a SQL Analytics Endpoint, and a Semantic Model.
3. Where are Lakehouse files stored? In OneLake.
4. What is the purpose of the SQL Analytics Endpoint? It provides a T-SQL interface to query Delta tables and connect reporting tools such as Power BI.
5. Can a Lakehouse store both files and tables? Yes. The Files section stores raw data, while the Tables section stores structured Delta Lake tables for analytics.

Key Takeaways

• A Lakehouse is the core data storage component in Microsoft Fabric.
• It combines Data Lake flexibility with Data Warehouse capabilities.
• Every Lakehouse includes Files, Tables, a SQL Analytics Endpoint, and a Semantic Model.
• Data is stored in OneLake using the Delta Lake format.
• Lakehouses provide a unified foundation for SQL analytics, Spark processing, Data Pipelines, and Power BI reporting.

Lesson 2.9 – Upload CSV

Learning Objectives

• Understand why CSV files are used in Microsoft Fabric.
• Upload a CSV file to a Lakehouse.
• Load CSV data into a Delta table.
• Preview and validate uploaded data.
• Learn best practices for CSV data ingestion.

What is a CSV File?

A CSV (Comma-Separated Values) file is a plain text file used to store tabular data. Each row represents a record, and each column is separated by a comma.

CustomerID,CustomerName,Country,Sales
101,John,USA,2500
102,Anita,India,3200
103,David,UK,1800
104,Priya,India,4100

CSV is one of the most commonly used formats for exchanging data between systems because it is simple, lightweight, and widely supported.

Why Upload CSV Files?

• Import data from external systems.
• Load historical datasets.
• Share business data.
• Perform data analysis.
• Build reports and dashboards.

CSV Upload Architecture

CSV File → Microsoft Fabric → Lakehouse
                              │
                   ┌──────────┴──────────┐
                Files Area          Load to Table
                   │                     │
                   └──────────┬──────────┘
                          Delta Table
                              │
                   SQL • Spark • Power BI

Prerequisites

• Access to Microsoft Fabric.
• A Lakehouse has been created.
• The CSV file is available on your computer.
• Permission to upload files.

Sample CSV File

Sales.csv:

SaleID,CustomerName,Country,Product,Amount
1,John,USA,Laptop,2500
2,Anita,India,Mobile,1200
3,David,UK,Monitor,800
4,Priya,India,Tablet,650
5,Rahul,India,Keyboard,120

Method 1: Upload CSV to the Files Section

1. Go to https://app.fabric.microsoft.com and sign in.
2. Click Workspaces, open your workspace (e.g., "Fabric Demo Workspace").
3. Select your Lakehouse (e.g., "SalesLakehouse").
4. Click Files.
5. Click Upload, select Sales.csv, click Open.

The file is uploaded to the Files section of the Lakehouse.

Verify the Upload:

Files
└── Sales.csv

Click the file to preview its contents and verify the data uploaded correctly.

Method 2: Load CSV into a Delta Table

1. Right-click the CSV file.
2. Select Load to Tables (or Load to New Table, depending on the interface).
3. Confirm the detected schema.
4. Enter a table name (e.g., "Sales").
5. Click Load. Fabric creates a Delta table in the Tables section.

Verify the Table:

Tables
└── Sales

Open the table to view columns, data preview, and data types.

Query the Table Using SQL:

SELECT * FROM Sales;

Sample output: | SaleID | CustomerName | Country | Product | Amount | |---|---|---|---|---| | 1 | John | USA | Laptop | 2500 | | 2 | Anita | India | Mobile | 1200 | | 3 | David | UK | Monitor | 800 | | 4 | Priya | India | Tablet | 650 | | 5 | Rahul | India | Keyboard | 120 |

Load CSV Using a Notebook (PySpark)

df = spark.read.csv(
    "Files/Sales.csv",
    header=True,
    inferSchema=True
)
display(df)

# Save as a Delta table:
df.write.mode("overwrite").saveAsTable("Sales")

Common CSV Options

Best Practices

• Include a header row with meaningful column names.
• Use UTF-8 encoding to avoid character issues.
• Ensure consistent data types within each column.
• Remove blank rows before uploading.
• Use descriptive file names such as Sales_2026_08.csv.
• Validate the data after loading.
• Keep the original CSV file in the Files section for auditing purposes.

Common Issues

• Upload Failed — network interruption, file size exceeds limits, insufficient permissions.
• Incorrect Columns — wrong delimiter, missing header row, extra commas in the data.
• Data Type Errors — mixed data types within a column, invalid numeric or date values.
• Duplicate Data — uploading the same file multiple times, reloading data without clearing existing records.

Real-World Example

A retail company receives a daily file named Sales_2026_08_21.csv from its stores. The CSV file is uploaded to the Files section. Fabric loads the file into the Sales Delta table. Data Engineers validate and transform the data. Business Analysts query the table using SQL. Power BI dashboards display daily sales metrics using the same Lakehouse.

Interview Questions

1. What is a CSV file? A plain text file used to store tabular data where each column is separated by a delimiter, typically a comma.
2. Where is a CSV file stored after uploading? In the Files section of the Lakehouse in OneLake.
3. How do you make CSV data queryable? Load the CSV into a Delta table, which registers it in the Lakehouse and makes it accessible through the SQL Analytics Endpoint.
4. Can CSV files be read using Spark? Yes, via spark.read.csv().
5. Why should you keep the original CSV file? Supports auditing, troubleshooting, data recovery, and reprocessing if needed.

Key Takeaways

• CSV is a widely used format for exchanging tabular data.
• Upload CSV files to the Files section of a Lakehouse.
• Load CSV data into Delta tables for SQL querying and analytics.
• Use the SQL Analytics Endpoint or Spark notebooks to work with the data.
• Validate the uploaded data and retain the original files as part of a robust data governance strategy.

Lesson 2.10 – Query Data

Learning Objectives

• Understand how to query data in a Lakehouse.
• Use the SQL Analytics Endpoint.
• Write basic and advanced SQL queries.
• Filter, sort, aggregate, and join data.
• Use SQL for data analysis.
• Follow SQL best practices in Microsoft Fabric.

What is Querying Data?

Querying data is the process of retrieving, filtering, analyzing, and manipulating data stored in a database or Lakehouse using SQL. In Microsoft Fabric, you can query Delta tables stored in a Lakehouse using the SQL Analytics Endpoint.

Why Query Data?

• Retrieve specific records.
• Analyze business data.
• Generate reports.
• Validate uploaded data.
• Prepare datasets for Power BI.
• Support business decision-making.

Query Architecture

Delta Tables → OneLake Storage → SQL Analytics Endpoint →
SQL Queries → Reports • Dashboards • Analytics

Prerequisites

1. Create a Lakehouse.
2. Upload a CSV file.
3. Load the CSV into a Delta table (e.g., "Sales").
4. Open the SQL Analytics Endpoint.

Sample Data

Sales table: | SaleID | CustomerName | Country | Product | Amount | |---|---|---|---|---| | 1 | John | USA | Laptop | 2500 | | 2 | Anita | India | Mobile | 1200 | | 3 | David | UK | Monitor | 800 | | 4 | Priya | India | Tablet | 650 | | 5 | Rahul | India | Keyboard | 120 |

Opening the SQL Analytics Endpoint

Open your Lakehouse → Select SQL Analytics Endpoint → Click New SQL Query. A SQL editor opens where you can write and execute queries.

Basic SQL Queries

Retrieve All Records

SELECT * FROM Sales;

Retrieve Specific Columns

SELECT CustomerName, Amount FROM Sales;

Filter Data

SELECT * FROM Sales WHERE Country = 'India';

Filter with Multiple Conditions

SELECT * FROM Sales WHERE Country = 'India' AND Amount > 500;

Sort Data

SELECT * FROM Sales ORDER BY Amount ASC;
SELECT * FROM Sales ORDER BY Amount DESC;

Aggregate Functions

SELECT COUNT(*) AS TotalSales FROM Sales;
SELECT SUM(Amount) AS TotalRevenue FROM Sales;
SELECT AVG(Amount) AS AverageSale FROM Sales;
SELECT MAX(Amount) AS HighestSale FROM Sales;
SELECT MIN(Amount) AS LowestSale FROM Sales;

Group By

SELECT Country, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Country;

Sample output: | Country | TotalSales | |---|---| | India | 1970 | | USA | 2500 | | UK | 800 |

HAVING Clause

SELECT Country, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Country
HAVING SUM(Amount) > 1000;

DISTINCT

SELECT DISTINCT Country FROM Sales;

LIMIT Results

SELECT TOP 3 * FROM Sales;

Note: The SQL Analytics Endpoint supports T-SQL, so TOP is used instead of LIMIT.

Joining Tables

Customers table: | CustomerID | CustomerName | City | |---|---|---| | 1 | John | New York | | 2 | Anita | Hyderabad | | 3 | David | London |

SELECT
  s.CustomerName,
  c.City,
  s.Amount
FROM Sales s
JOIN Customers c
  ON s.CustomerName = c.CustomerName;

Using Aliases

SELECT
  CustomerName AS Customer,
  Amount AS SalesAmount
FROM Sales;

Filtering with LIKE

SELECT * FROM Sales WHERE CustomerName LIKE 'A%';

Date Filtering

SELECT * FROM Orders WHERE OrderDate >= '2026-01-01';

Creating a View

CREATE VIEW HighValueSales AS
SELECT * FROM Sales WHERE Amount > 1000;

SELECT * FROM HighValueSales;

Query Performance Tips

• Select only required columns instead of SELECT * in production.
• Filter data early using WHERE.
• Use Delta tables for better performance.
• Avoid unnecessary joins.
• Use appropriate data types.
• Partition very large tables when appropriate.

Common SQL Errors

• Table Not Found — incorrect table name; wrong schema or workspace.
• Invalid Column Name — typographical error; column does not exist.
• Syntax Error — missing comma; incorrect SQL keyword; unmatched parentheses.
• Permission Denied — insufficient workspace or database permissions.

Real-World Example

A retail company stores daily sales data in the Sales Delta table. Business analysts execute queries such as total daily revenue, sales by country, top-selling products, customers with purchases above ₹10,000, and monthly sales trends. The query results are then used to build Power BI dashboards for management.

Interview Questions

1. What is the SQL Analytics Endpoint in Microsoft Fabric? A built-in T-SQL interface that allows users to query Delta tables stored in a Lakehouse.
2. Which language is used to query Lakehouse tables? T-SQL (Transact-SQL).
3. Which SQL statement retrieves all records from a table? SELECT * FROM Sales;
4. Which clause is used to filter records? The WHERE clause.
5. What is the difference between WHERE and HAVING? WHERE filters rows before grouping, while HAVING filters grouped results after GROUP BY.

Key Takeaways

• The SQL Analytics Endpoint enables T-SQL queries on Lakehouse Delta tables.
• SQL can be used to retrieve, filter, sort, aggregate, and join data.
• Common SQL clauses include SELECT, WHERE, GROUP BY, HAVING, ORDER BY, and JOIN.
• Views can simplify frequently used queries.
• Writing efficient SQL improves performance and supports scalable analytics in Microsoft Fabric.

Lesson 2.11 – Create Delta Tables

Learning Objectives

• Understand what a Delta table is.
• Create Delta tables in a Lakehouse.
• Load data into Delta tables.
• Perform INSERT, UPDATE, DELETE, and MERGE operations.
• Query Delta tables using SQL.
• Learn best practices for managing Delta tables.

What is a Delta Table?

A Delta Table is a table stored in the Delta Lake format. It combines the efficiency of Apache Parquet with advanced capabilities such as ACID transactions, Time Travel, Schema Enforcement, Schema Evolution, and high-performance analytics.

In Microsoft Fabric Lakehouse, all managed tables are stored as Delta Tables by default.

Why Use Delta Tables?

• Reliable data storage
• Fast SQL queries
• Support for updates and deletes
• Version history
• Better data quality
• Seamless integration with Spark, SQL, and Power BI

Delta Table Architecture

Microsoft Fabric
      │
  Lakehouse
      │
 Delta Table
      │
┌─────┴──────┐
Parquet Files  _delta_log
(Data Files)   (Transaction Log)
      │
   OneLake

Ways to Create Delta Tables

• SQL
• Spark Notebooks
• Data Pipelines
• Loading CSV/Parquet files into a table

Method 1: Create a Delta Table Using SQL

CREATE TABLE Sales
(
  SaleID INT,
  CustomerName VARCHAR(100),
  Country VARCHAR(50),
  Product VARCHAR(100),
  Amount DECIMAL(10,2)
);

Note: In Microsoft Fabric Lakehouse, CREATE TABLE creates a Delta table by default.

Verify:

SELECT * FROM Sales;

Initially, the table will be empty.

Method 2: Create a Delta Table from a CSV File 1. Upload Sales.csv to the Files section. 2. Right-click the file. 3. Select Load to New Table. 4. Specify the table name (e.g., "Sales"). 5. Review the detected schema. 6. Click Load. Fabric creates a Delta table automatically.

Method 3: Create a Delta Table Using PySpark

data = [
    (1, "John", "USA", "Laptop", 2500),
    (2, "Anita", "India", "Mobile", 1200),
    (3, "David", "UK", "Monitor", 800)
]
columns = ["SaleID", "CustomerName", "Country", "Product", "Amount"]

df = spark.createDataFrame(data, columns)
df.write.mode("overwrite").saveAsTable("Sales")

Insert Data

INSERT INTO Sales
VALUES
(4, 'Priya', 'India', 'Tablet', 650),
(5, 'Rahul', 'India', 'Keyboard', 120);

Update Data

UPDATE Sales SET Amount = 1500 WHERE SaleID = 2;

Delete Data

DELETE FROM Sales WHERE SaleID = 5;

MERGE (Upsert)

Assume a source table named NewSales contains new and updated records:

MERGE INTO Sales AS Target
USING NewSales AS Source
ON Target.SaleID = Source.SaleID
WHEN MATCHED THEN
    UPDATE SET
        Target.Amount = Source.Amount,
        Target.Product = Source.Product
WHEN NOT MATCHED THEN
    INSERT
    (
        SaleID,
        CustomerName,
        Country,
        Product,
        Amount
    )
    VALUES
    (
        Source.SaleID,
        Source.CustomerName,
        Source.Country,
        Source.Product,
        Source.Amount
    );

MERGE is commonly used for incremental ETL and data warehouse loading.

View Table Schema

DESCRIBE TABLE Sales;

Example output: | Column | Data Type | |---|---| | SaleID | INT | | CustomerName | VARCHAR | | Country | VARCHAR | | Product | VARCHAR | | Amount | DECIMAL(10,2) |

Query the Delta Table

SELECT * FROM Sales;

SELECT SUM(Amount) AS TotalSales FROM Sales;

SELECT Country, SUM(Amount) AS TotalSales
FROM Sales
GROUP BY Country;

Delta Table Lifecycle

CSV File → Upload to Files → Load into Delta Table →
Query with SQL → Update/Delete/Merge → Power BI Reports

Best Practices

• Use meaningful table names.
• Define appropriate data types.
• Use primary business keys for MERGE operations.
• Use MERGE instead of deleting and reloading entire datasets.
• Keep raw files in the Files section for auditing.
• Validate data before loading into production.
• Partition very large Delta tables when appropriate.
• Regularly optimize large Delta tables to improve query performance.

Common Issues

• Table Already Exists — attempting to create a table with an existing name. Solution: use a different name or drop the existing table if appropriate.
• Data Type Mismatch — source data does not match the table schema. Solution: validate and transform the data before loading.
• Duplicate Records — repeated inserts without checking existing data. Solution: use MERGE for incremental loads.
• Permission Denied — insufficient workspace or Lakehouse permissions. Solution: request the required role (Member or Admin) from the workspace administrator.

Real-World Example

A retail company receives a daily Sales.csv file. The file is uploaded to the Files section. It is loaded into the Sales Delta table. New records are inserted each day. Existing records are updated using MERGE. Analysts query the Delta table through the SQL Analytics Endpoint. Power BI dashboards display near real-time sales performance.

Interview Questions

1. What is a Delta Table? A table stored in Delta Lake format that provides ACID transactions, versioning, schema management, and high-performance analytics.
2. What is the default table format in a Microsoft Fabric Lakehouse? Delta Lake.
3. Which SQL command creates a Delta table in Microsoft Fabric?

CREATE TABLE Sales
(
  SaleID INT,
  CustomerName VARCHAR(100),
  Amount DECIMAL(10,2)
);

4. Which command is used to update existing rows and insert new rows in one operation? MERGE.
5. Why are Delta Tables preferred over standard Parquet files? They provide ACID transactions, support for UPDATE, DELETE, and MERGE, schema enforcement, version history, and better reliability for enterprise data workloads.

Key Takeaways

• Delta Tables are the default table format in Microsoft Fabric Lakehouses.
• They are stored in OneLake using the Delta Lake format.
• Delta Tables support INSERT, UPDATE, DELETE, and MERGE operations.
• You can create Delta Tables using SQL, PySpark, Data Pipelines, or by loading files into a Lakehouse.
• Delta Tables provide the foundation for reliable, scalable, and high-performance analytics in Microsoft Fabric.