Data Ingestion — Data Factory in Microsoft Fabric

16 Lessons · Microsoft Fabric · DP-700 Track

Module 3 · Lesson 3.1 — Data Factory in Fabric

Data Factory in Microsoft Fabric

Data Factory in Microsoft Fabric is the platform's data integration and orchestration experience. It helps you connect to data sources, ingest data, transform data, and orchestrate end-to-end data workflows within Fabric.

If you already know Azure Data Factory (ADF), many concepts will feel familiar.

Fabric Data Factory = Connect + Ingest + Transform + Orchestrate

A simple flow:

Source Systems → Fabric Data Factory → Ingest/Transform/Orchestrate →
OneLake → Lakehouse/Warehouse → Power BI

1. Why do we need Data Factory?

Enterprise data normally exists across many systems:

SQL Server ──┐
Oracle ──────┤
Salesforce ──┤
SAP ─────────┼──► Data Factory
REST APIs ───┤
CSV / Excel ─┤
ADLS Gen2 ───┤
Amazon S3 ───┘

Before analytics can happen, that data often needs to be: Extracted → Copied/Loaded → Cleaned → Transformed → Orchestrated → Stored.

Fabric Data Factory provides capabilities for these tasks.

2. Where Data Factory fits in Fabric

DATA SOURCES (SQL, Oracle, SAP, APIs, Files, SaaS)
        │
FABRIC DATA FACTORY (Pipelines, Copy, Dataflow Gen2)
        │
     OneLake
        │
┌───────┴───────┐
Lakehouse    Warehouse
        │
  Semantic Model
        │
     Power BI

Data Factory therefore commonly sits near the beginning of an end-to-end Fabric data pipeline.

3. Main Data Factory concepts

4. Data Pipeline

A Pipeline is a collection of activities that work together to perform a data-processing workflow.

Customer_Ingestion_Pipeline
   ├── Copy Customers
   ├── Copy Orders
   ├── Run Notebook
   └── Load Gold Tables

Think of a pipeline as a workflow/orchestration container:

Start → Copy SQL Data → Run Notebook → Load Warehouse →
Refresh Semantic Model → End

5. Activities

An Activity represents an individual operation inside a pipeline.

Pipeline
├── Activity 1 → Copy Data
├── Activity 2 → Execute Notebook
├── Activity 3 → Stored Procedure / Script
└── Activity 4 → Other orchestration task

Activities can be connected based on success, failure, completion, or other supported workflow logic.

6. Copy Activity

Copy activity is one of the most commonly used Fabric Data Factory activities. Its job is simple: read data from a source and write it to a destination.

SQL Server → Copy Activity → Fabric Lakehouse
Oracle → Copy Activity → Fabric Warehouse

7. Source and Destination

Copy Activity
├── Source
└── Destination

Example:

SOURCE: SQL Server, Database: RetailDB, Table: dbo.Customers
        │
    COPY ACTIVITY
        │
DESTINATION: Fabric Lakehouse, Table: customers

8. Connections

Before Fabric can access an external data system, you generally configure a connection — the information required to connect to a data source.

Connection
├── Server
├── Database
├── Authentication
└── Credentials / identity configuration

Pipeline → Connection → SQL Server

Connections can be reused where supported.

9. On-premises data

On-Premises Network → SQL Server → Gateway → Microsoft Fabric

A supported gateway configuration provides the bridge between the cloud service and private/on-premises data. This is especially important when migrating traditional enterprise ETL workloads.

10. Dataflow Gen2

Fabric Data Factory also provides Dataflow Gen2 for low-code data preparation and transformation, using a Power Query-style experience.

Source
  ▼
Dataflow Gen2
  ├── Filter Rows
  ├── Rename Columns
  ├── Change Data Types
  ├── Remove Duplicates
  ├── Merge Queries
  ├── Add Columns
  └── Apply Business Rules
  ▼
Destination

Useful when you don't want to write Spark or SQL code for every transformation.

11. Pipeline vs Dataflow Gen2

Pipeline — mainly used for orchestration and data movement. Dataflow Gen2 — mainly used for low-code data transformation.

                 Pipeline
             Orchestrates Flow
       ┌──────────┼──────────┐
       ▼          ▼          ▼
   Copy Data  Dataflow Gen2  Notebook
                  │
              Transform

12. Pipeline vs Notebook vs Dataflow

Pipeline
├── Copy Activity → Ingest Data
├── Notebook → Complex PySpark Transformation
└── Dataflow Gen2 → Low-code Transformation

13. Fabric Data Factory and Medallion Architecture

SOURCE SYSTEMS (SQL, Oracle, Salesforce, APIs, Files)
        │
  Fabric Data Factory
        │
     BRONZE (Raw Data)
        │
  Notebook/Dataflow
        │
     SILVER (Clean Data)
        │
   Business Rules
        │
      GOLD (BI Ready)
        │
     Power BI

14. Example retail pipeline

SQL Server
├── Customers
├── Products
├── Orders
├── OrderItems
└── Inventory

PL_Retail_Daily_Load
   ├── Copy Customers
   ├── Copy Products
   ├── Copy Orders
   ├── Copy OrderItems
   ├── Copy Inventory
   ▼
Run Silver Notebook
   ▼
Run Gold Notebook
   ▼
Refresh BI Model

15. Parameters

Hard-coding values makes pipelines difficult to maintain. Avoid separate pipelines like Copy_Customers, Copy_Products, Copy_Orders, Copy_Inventory when the pattern can be parameterized.

A parameterized pipeline could use a TableName parameter, executed as:

TableName = Customers
TableName = Products
TableName = Orders

          Generic Pipeline
              TableName
      ┌───────────┼───────────┐
      ▼           ▼           ▼
  Customers   Products      Orders

This is a key technique for building metadata-driven ingestion frameworks.

16. Variables

A variable can hold a value during pipeline execution.

Examples: CurrentTable, RecordCount, LoadStatus, FileName.

Parameter → Input to pipeline/activity. Variable → Value that can change during pipeline execution.

17. Control-flow activities

Real projects need more than simple Copy activities: If, ForEach, Wait, Lookup, Set Variable, Execute Pipeline.

Lookup Table List → ForEach → Copy Table

This is extremely useful for metadata-driven ETL.

18. ForEach example

Instead of building five separate copies for Customers, Products, Orders, Inventory, Suppliers:

Lookup → Table List → ForEach Table → Copy Activity → Lakehouse

This provides a more scalable design.

19. Pipeline dependencies

Activities can be executed based on previous activity results.

Copy Data
├── Success → Run Notebook
└── Failure → Error Handling

More complete workflow:

        Copy Data
      ┌────┴────┐
   Success   Failure
      │         │
  Transform  Log Error
      │         │
  Power BI   Notify

Important for production ETL frameworks.

20. Scheduling

Schedule: Every Day, 2:00 AM → Fabric Pipeline → Load Data

Typical daily workflow:

02:00 AM → Ingestion
02:30 AM → Transformation
03:00 AM → Gold Tables
03:30 AM → Semantic Model
04:00 AM → Reports ready

21. Monitoring

Production pipelines must be monitored. You typically want to know: did the pipeline run? Did it succeed? When did it start? How long did it run? Which activity failed? What error occurred?

Pipeline Runs
Run 001 → Succeeded
Run 002 → Succeeded
Run 003 → Failed → Copy Customers → Error Details

22. Incremental Loading

Suppose a Sales table has 500 million records. Instead of copying all records every day, use an incremental approach:

Last Successful Load → Read New/Changed Records → Load Increment

Example:

Yesterday: LastLoadDate = 2026-08-01
Today: WHERE ModifiedDate > LastLoadDate

This can dramatically reduce processing.

23. Full load vs Incremental load

24. Fabric Data Factory vs Azure Data Factory

The two are related, but Fabric Data Factory is not simply ADF with a different screen. Some capabilities, terminology, supported activities, connectivity patterns, and operational behavior differ.

25. ADF migration perspective

A traditional Azure architecture:

SQL Server → Azure Data Factory → ADLS Gen2 → Synapse → Power BI

A Fabric-centered architecture:

SQL Server → Fabric Data Factory → OneLake → Lakehouse →
Spark/SQL → Semantic Model → Power BI

This is one of the major reasons Data Factory is important when moving from Azure Synapse/ADF architectures toward Fabric.

26. Recommended enterprise pattern

SOURCES (SQL, Oracle, APIs)
        │
  FABRIC DATA FACTORY (Copy/Ingest)
        │
     BRONZE
        │
  Spark Notebook
        │
     SILVER
        │
  Business Rules
        │
      GOLD
        │
  Semantic Model
        │
     Power BI

27. Best practices

✓ Use meaningful pipeline names ✓ Avoid unnecessary hard coding ✓ Use parameters ✓ Build reusable pipelines ✓ Consider metadata-driven ingestion ✓ Use incremental loads for large datasets ✓ Implement failure handling ✓ Maintain logging ✓ Monitor pipeline executions ✓ Separate DEV / TEST / PROD ✓ Follow least-privilege security ✓ Use notebooks for complex engineering transformations ✓ Use Dataflow Gen2 when low-code transformation is appropriate ✓ Design Bronze / Silver / Gold layers clearly

Naming convention example:

PL_SQL_TO_BRONZE
PL_ORACLE_TO_BRONZE
PL_BRONZE_TO_SILVER
PL_SILVER_TO_GOLD
NB_CLEAN_CUSTOMERS
NB_TRANSFORM_SALES
DF_CUSTOMER_CLEANING

28. Interview question — What is Data Factory in Fabric?

Data Factory in Microsoft Fabric is the data integration and orchestration workload used to connect to data sources, ingest and move data, transform data, and coordinate end-to-end data workflows. It provides capabilities such as pipelines, Copy activities, Dataflow Gen2, connections, parameters, control-flow activities, scheduling, and monitoring, with native integration into OneLake, Lakehouse, Warehouse, and other Fabric workloads.

29. Interview question — Pipeline vs Dataflow Gen2

A pipeline is primarily used for orchestration and data movement, while Dataflow Gen2 provides a low-code Power Query-based experience for data transformation. A pipeline can orchestrate activities that ingest data, execute transformations, run notebooks, and coordinate downstream processing.

30. Quick revision notes

FABRIC DATA FACTORY
====================
Purpose: Data Integration, Ingestion, Movement, Transformation, Orchestration

Core Concepts: Pipeline, Activity, Copy Activity, Connection,
Dataflow Gen2, Parameters, Variables, ForEach, Scheduling, Monitoring

Remember:
Pipeline = Orchestration
Copy Activity = Data Movement
Dataflow Gen2 = Low-Code Transformation
Notebook = Code-Based Transformation
OneLake = Data Foundation

Final architecture to remember:

DATA SOURCES
     │
┌────┼────┐
SQL Server  Oracle  APIs/Files
     │
FABRIC DATA FACTORY
     │
┌────┼────┐
COPY  PIPELINE  DATAFLOW GEN2
Move  Orchestrate  Transform
     │
  ONELAKE
     │
 LAKEHOUSE
     │
BRONZE → SILVER
     │
   GOLD
     │
SEMANTIC MODEL
     │
 POWER BI

Key takeaway: Fabric Data Factory is the integration and orchestration layer of Microsoft Fabric. It gets data from source systems into Fabric, coordinates processing across pipelines, notebooks and other activities, and helps move data through the architecture from source → OneLake → Lakehouse/Warehouse → Power BI.

Module 3 · Lesson 3.2 — Dataflows Gen2

Dataflows Gen2 in Microsoft Fabric

Dataflow Gen2 is a low-code/no-code data ingestion and transformation tool in Microsoft Fabric. It is part of the Data Factory experience and uses the familiar Power Query interface.

It allows you to connect to different data sources, clean and transform the data, and load the results into supported Fabric destinations such as a Lakehouse or Warehouse.

Dataflow Gen2 = Power Query-based ETL/ELT inside Microsoft Fabric

Source → Dataflow Gen2 → Power Query Transformations →
Lakehouse/Warehouse → Power BI

1. Why Dataflow Gen2?

Suppose your source contains customer data like this:

CustomerID | Name  | Country | Sales
101        | Ravi  | india   | 5000
102        | JOHN  | USA     | NULL
103        | Priya | INDIA   | 7000
103        | Priya | INDIA   | 7000

Before using it for reporting, you may need to: Remove duplicates → Handle NULL values → Standardize Country → Correct data types → Rename columns → Apply business rules.

You could write SQL or PySpark code to do this. But with Dataflow Gen2, many transformations can be created visually using Power Query.

2. Where Dataflow Gen2 fits

Microsoft Fabric
      │
  Data Factory
      │
┌─────┴─────┐
Pipeline   Dataflow Gen2
Orchestration  Transform
Data Movement  Prepare Data
      │
  OneLake
┌─────┴─────┐
Lakehouse  Warehouse

Pipeline = orchestrates the workflow. Dataflow Gen2 = transforms/prepares the data.

3. Dataflow Gen2 architecture

SOURCES (SQL, Excel, CSV, API, Cloud Data)
     │
DATAFLOW GEN2 — Power Query
(Filter, Join, Clean, Group, Transform)
     │
DESTINATION (Lakehouse, Warehouse, etc.)
     │
  Power BI

4. Power Query

The transformation engine behind Dataflow Gen2 is Power Query — familiar if you've used Power BI Desktop:

Power BI Desktop → Transform Data → Power Query Editor

Common operations: Choose Columns, Remove Columns, Rename Columns, Filter Rows, Remove Duplicates, Replace Values, Change Data Types, Split Columns, Merge Columns, Group By, Pivot, Unpivot, Merge Queries, Append Queries, Add Custom Columns.

5. Basic Dataflow Gen2 process

Get Data → Connect to Source → Select Data → Transform →
Configure Destination → Publish → Refresh/Execute

Example:

SQL Server → Customers Table → Dataflow Gen2 →
Remove Duplicates → Fix NULL Values → Standardize Country →
Lakehouse → customers_clean

6. Connecting to data

Dataflow Gen2
   └── Get Data
        ├── SQL Server
        ├── Azure SQL
        ├── Excel
        ├── Text/CSV
        ├── JSON
        ├── SharePoint
        ├── Web/API
        └── Other supported connectors

The exact connector list evolves, so always check the current Fabric connector documentation for production requirements.

7. Example transformation

Source: | CustomerID | CustomerName | Country | Sales | |---|---|---|---| | 101 | Ravi | india | 5000 | | 102 | JOHN | USA | null | | 103 | Priya | INDIA | 7000 | | 103 | Priya | INDIA | 7000 |

Step 1 — Remove duplicates: the two identical rows for 103 collapse to one. Step 2 — Handle NULL: NULL → 0. Step 3 — Standardize Country: india/INDIA/India → India. Step 4 — Standardize names: JOHN → John.

Result: | CustomerID | CustomerName | Country | Sales | |---|---|---|---| | 101 | Ravi | India | 5000 | | 102 | John | USA | 0 | | 103 | Priya | India | 7000 |

The clean result can then be written to a Fabric destination.

8. Applied Steps

Power Query records transformations as a sequence of Applied Steps:

APPLIED STEPS
Source → Navigation → Promoted Headers → Changed Type →
Removed Duplicates → Replaced Value → Filtered Rows →
Merged Queries → Renamed Columns

Each step transforms the result produced by the previous step — easier to understand visually than a large transformation script for many business users.

9. M Language

Although Dataflow Gen2 is low-code, Power Query transformations are represented using the M language.

Visual transformation: Filter Country = "India" generates M logic such as:

Table.SelectRows(
    Source,
    each [Country] = "India"
)

You don't need to become an M expert to start using Dataflow Gen2, but knowing M becomes useful for advanced transformations.

10. Data destinations

Dataflow Gen2
   ├── Lakehouse
   └── Warehouse

Example:

SQL Server → Dataflow Gen2 → Clean Customer Data →
Lakehouse → customers_clean

11. Dataflow Gen2 with Lakehouse

SQL Server → Dataflow Gen2 (Power Query) → OneLake →
Lakehouse → Customer Table

Useful for relatively straightforward data-preparation workloads.

12. Dataflow Gen2 with Warehouse

Excel/SQL/CSV → Dataflow Gen2 → Clean/Transform →
Fabric Warehouse → Fact/Dimension Tables → Power BI

Example: customers.xlsx → Dataflow Gen2 → DIM_CUSTOMER → Fabric Warehouse

13. Dataflow Gen2 and Medallion Architecture

DATA SOURCES
     │
   BRONZE (Raw Data)
     │
Dataflow Gen2 (Clean/Transform)
     │
   SILVER (Clean Data)
     │
Dataflow Gen2 (Business Rules)
     │
    GOLD (Business-Ready)
     │
   Power BI

However, Dataflow Gen2 is not mandatory for Medallion Architecture. For complex or large-scale engineering transformations, Spark notebooks or SQL may be more appropriate.

14. Dataflow Gen2 vs Pipeline

One of the most important interview distinctions.

Pipeline: "What should execute and in what order?" Dataflow Gen2: "How should this data be cleaned and transformed?"

15. Using them together

A Pipeline can orchestrate a Dataflow:

PIPELINE
   │
Copy Source Data
   │
Run Dataflow Gen2 (Transform)
   │
Run Notebook
   │
Load Gold

This is a much more realistic production architecture.

16. Dataflow Gen2 vs Notebook

Example:

Dataflow Gen2: Remove Duplicates → Replace NULL → Rename Column → Merge Tables

Notebook:
df = (
    df.dropDuplicates()
      .fillna({"Sales": 0})
)

Both can achieve transformations, but the development experience is different.

17. Dataflow Gen2 vs SQL

Use SQL when the transformation is naturally relational:

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

Use Dataflow Gen2 when visual Power Query transformation is more convenient: Sales → Filter → Join → Group By → Rename → Destination.

Neither is universally better — choose the appropriate tool for the workload and team.

18. Dataflow Gen2 vs Power BI Power Query

Traditional approach:

SQL Server → Power BI → Power Query → Semantic Model → Report

Transformation logic can become tightly coupled to an individual BI solution.

With a Fabric Dataflow:

SQL Server → Dataflow Gen2 → Clean Data → Lakehouse/Warehouse
     ├── Report A
     └── Report B

This can make transformation logic more reusable across downstream analytics solutions.

19. Query folding

An important Power Query concept: when possible, Power Query can push transformation logic back to the source system.

Without folding: SQL Server (10M rows) → Transfer all rows → Fabric → Filter

With folding/pushdown: SQL Server → Apply Filter → Return only required rows → Fabric

This can significantly improve performance.

20. Why query folding matters

Suppose your source has 100,000,000 records but you need only WHERE OrderDate >= '2026-01-01'.

Without effective pushdown: 100M rows → Transfer → Filter. With effective folding/pushdown: Source → Filter → Required rows only → Fabric.

Understanding which transformations preserve query folding can be important for production Dataflows.

21. Refresh and execution

Dataflow Gen2 → Refresh/Run → Read Source →
Apply Transformations → Write Destination

A pipeline can also be used to orchestrate the Dataflow as part of a larger workflow.

22. Error handling

Suppose a Dataflow expects CustomerID → Integer but the source contains CustomerID = "ABC". A data-type conversion can fail or produce an error.

Consider: Data Validation, NULL Handling, Data Type Validation, Error Rows, Source Schema Changes, Refresh Monitoring for production workloads.

23. Real-world example

Employee.xlsx contains EmployeeID, Name, Department, Salary, JoiningDate, but has problems: duplicate employees, NULL departments, different date formats, incorrect salary types, extra spaces in names.

Employee.xlsx → Dataflow Gen2
   ├── Remove duplicates
   ├── Trim Name
   ├── Replace NULL Department
   ├── Convert Salary
   └── Standardize JoiningDate
   ▼
Employee_Clean → Lakehouse

Now the clean table can be reused by downstream workloads.

24. Production example

SQL Server → Fabric Pipeline → Copy Activity → Bronze
     │
  Dataflow Gen2
  ┌────┼────┐
Clean  Join  Standardize
     │
   Silver
     │
  Spark/SQL
     │
    Gold
     │
Semantic Model → Power BI

25. When should you use Dataflow Gen2?

✓ You prefer low-code transformations ✓ Your team already knows Power Query ✓ You need standard data cleansing ✓ You need joins and merges ✓ You need filters and aggregations ✓ You need reusable transformation logic ✓ You want to write transformed data to Fabric destinations ✓ Business analysts need to participate in data preparation

26. When might a Notebook be better?

✓ Transformations are highly complex ✓ Large-scale Spark processing is required ✓ Advanced Python logic is needed ✓ You need custom libraries ✓ You need complex algorithms ✓ Data engineering requires programmatic control

Common enterprise strategy:

Simple/Medium Transformation → Dataflow Gen2
Complex/Large-Scale Transformation → Spark Notebook

27. Best practices

✓ Give Dataflows meaningful names ✓ Remove unnecessary columns early ✓ Filter unnecessary rows early ✓ Preserve query folding where possible ✓ Set correct data types ✓ Handle NULL values carefully ✓ Avoid unnecessary transformation steps ✓ Keep transformations understandable ✓ Use parameters where appropriate ✓ Configure destinations carefully ✓ Monitor refresh failures ✓ Separate DEV / TEST / PROD ✓ Document important business transformations

Instead of Dataflow1, Dataflow2, NewDataflow, use:

DFG_CUSTOMER_CLEANING
DFG_SALES_TRANSFORMATION
DFG_PRODUCT_STANDARDIZATION

28. Dataflow Gen1 vs Gen2

For new Fabric learning, your primary focus should be Dataflow Gen2.

29. Interview question — What is Dataflow Gen2?

Dataflow Gen2 is a low-code data ingestion and transformation capability in Microsoft Fabric Data Factory. It uses the Power Query experience to connect to data sources, clean and shape data using operations such as filtering, joining, grouping and data-type conversion, and write transformed results to supported destinations such as Fabric Lakehouse and Warehouse. It can also be orchestrated as part of a Fabric Data Pipeline.

30. Interview question — Pipeline vs Dataflow Gen2 vs Notebook

A Pipeline is primarily used to orchestrate workflows and move data, Dataflow Gen2 provides low-code Power Query-based data transformation, and a Notebook provides code-based processing using technologies such as Python and Spark. They can be combined within the same Fabric solution rather than treated as competing tools.

Pipeline = Orchestrate
Dataflow Gen2 = Transform visually
Notebook = Transform with code
Copy Activity = Move data

31. Quick revision

DATAFLOW GEN2
==============================
Part of: Fabric Data Factory
Purpose: Low-code Data Transformation
Technology: Power Query
Language: M

Typical Operations:
Filter, Clean, Join, Merge, Append, Group, Pivot, Unpivot,
Remove Duplicates, Replace Values, Change Data Types, Custom Columns

Typical Destinations: Lakehouse, Warehouse, other supported destinations
Works with: Pipeline, OneLake, Lakehouse, Warehouse, Power BI

Final diagram to remember:

DATA SOURCES
     │
┌────┼────┐
SQL  Excel  CSV/API
     │
DATAFLOW GEN2 — POWER QUERY
┌────┼────┐
FILTER  JOIN  CLEAN
GROUP   MERGE TRANSFORM
     │
FABRIC DESTINATION
┌────┴────┐
LAKEHOUSE  WAREHOUSE
     │
SEMANTIC MODEL
     │
  POWER BI

Key takeaway: Dataflow Gen2 is Fabric's Power Query-based low-code transformation tool. Use a Pipeline to orchestrate, Copy Activity to move data, Dataflow Gen2 to visually clean and transform data, and Notebooks when you need more complex code-based engineering.

Module 3 · Lesson 3.3 — Copy Activity

3.3 Copy Activity in Microsoft Fabric

Learning Objectives

• Understand what Copy Activity is.
• Understand the architecture of Copy Activity.
• Configure source and destination connections.
• Map source columns to destination columns.
• Copy data between different systems.
• Configure performance and fault-tolerance options.
• Monitor and troubleshoot Copy Activity.

What is Copy Activity?

Copy Activity is a pipeline activity in Microsoft Fabric Data Factory used to move data from a source system to a destination system. It is primarily used for data ingestion and data movement.

SQL Server → Copy Activity → Fabric Lakehouse

Copy Activity can move data between many different data sources and destinations.

Why Use Copy Activity?

• Extract data from operational databases.
• Load files into a Lakehouse.
• Move data between cloud storage systems.
• Build ETL/ELT pipelines.
• Perform full data loads.
• Implement incremental data loads.
• Schedule automated data ingestion.

Copy Activity Architecture

Fabric Data Factory
        │
     Pipeline
        │
   Copy Activity
┌───────┴───────┐
Source        Destination
SQL Server/API   Lakehouse/Warehouse
Oracle/Blob      ADLS/Blob/SQL
        │
     OneLake

Key Components

Source, Destination, Connection, Mapping, Performance settings, Fault tolerance, Monitoring.

1. Source — where the data comes from: SQL Server, Azure SQL Database, Oracle, Azure Blob Storage, ADLS Gen2, REST API, Amazon S3, PostgreSQL, MySQL.

Source: SQL Server, Database: RetailDB, Table: Customers

2. Destination — where the copied data is stored: Fabric Lakehouse, Fabric Warehouse, Azure Blob Storage, ADLS Gen2, SQL Server, Azure SQL Database.

Destination: Fabric Lakehouse, Table: Customers

3. Connection — the information required to connect to a source or destination: server name, database name, authentication method, credentials, storage account, endpoint.

SQL Server: Server: SQLSERVER01, Database: RetailDB, Authentication: SQL Authentication

Credentials should be managed securely rather than hard-coded in pipelines.

4. Mapping — defines how source columns are transferred to destination columns. | Source Column | Destination Column | |---|---| | Customer_ID | CustomerID | | Customer_Name | CustomerName | | Country_Code | Country | | Created_Date | CreatedDate |

If the source and destination column names are identical, Fabric can often automatically map them.

Creating a Copy Activity

Step 1: Open Data Factory — Open your Fabric workspace, select Data Factory → Data Pipeline.

Step 2: Create a Pipeline — Give it a meaningful name, e.g. PL_SQLServer_To_Lakehouse.

Step 3: Add Copy Activity — Inside the pipeline, select Copy data. A Copy Activity is added to the pipeline canvas.

Configure Source:

Source Type: SQL Server
Connection: RetailSQLServer
Database: RetailDB
Table: Customers

You can either select a table or use a SQL query:

SELECT * FROM dbo.Customers;

Configure Destination:

Destination: Lakehouse
Lakehouse: SalesLakehouse
Table: Customers

You can configure whether the data should create a new table or load into an existing destination.

Configure Mapping:

SQL Server              Lakehouse
Customer_ID     ───────► CustomerID
Customer_Name   ───────► CustomerName
Country         ───────► Country
Created_Date    ───────► CreatedDate

Review the mappings before running the pipeline.

Run the Pipeline: Click Save → Validate → Run. Fabric starts the Copy Activity.

Monitor the Copy Activity: After execution, open the pipeline's monitoring information to see run status, start/end time, duration, rows read/written, data transferred, throughput, and error details.

Example:

Pipeline: PL_SQLServer_To_Lakehouse
Status: Succeeded
Rows Read: 150,000
Rows Written: 150,000
Duration: 02:15

Full Load

A Full Load copies all records from the source to the destination.

SQL Server → (100% of data) → Lakehouse

SELECT * FROM Customers;

When to use: initial data migration, small tables, reference/master data, initial Lakehouse population.

Incremental Load

An Incremental Load copies only new or changed records.

Source Table
├── Existing records → Skip
└── New/Changed records → Copy → Lakehouse

SELECT * FROM Customers
WHERE ModifiedDate > '2026-08-21 00:00:00';

Incremental loading is covered in more detail in Section 3.8 – Incremental Loads.

Copy Activity with Parameters

Parameters can make pipelines reusable.

Pipeline Parameter: SourceTable = Customers

The same pipeline could then process Customers, Orders, Products, Suppliers — avoiding a separate pipeline for every table.

Copy Activity Performance

For large datasets, performance can be improved using: parallel copy, partitioning, appropriate file formats, source query optimization, destination optimization, appropriate integration runtime configuration.

Copy Activity
┌───────┼───────┐
Partition 1  Partition 2  Partition 3
        │
     Lakehouse

Instead of processing everything sequentially, data can be processed in parallel where supported.

Fault Tolerance

Copy Activity provides options to handle problematic records and errors: invalid data types, corrupt files, missing values, connection failures, network interruptions.

For production pipelines, configure appropriate error handling and monitoring.

Copy Activity vs Dataflow Gen2

Move data → Copy Activity. Transform data → Dataflow Gen2.

In real projects, both are often used together.

Real-World Example

On-Prem SQL Server → Copy Activity → Fabric Lakehouse →
Delta Table → Power BI

The pipeline can run every night at 1:00 AM. The Copy Activity connects to SQL Server, reads customer records, transfers the data, writes it to a Lakehouse Delta table, and reports the execution status.

End-to-End Hands-On Exercise

Scenario: Copy customer data from SQL Server into a Fabric Lakehouse. - Source: SQL Server, Database: RetailDB, Table: Customers. - Destination: Lakehouse: SalesLakehouse, Table: Customers. - Pipeline: PL_SQLServer_To_Lakehouse → Copy Activity → Customers Table

Steps: 1. Create a new Data Pipeline. 2. Add Copy Activity. 3. Configure SQL Server connection. 4. Select Customers. 5. Configure Lakehouse destination. 6. Select/create Customers Delta table. 7. Configure column mapping. 8. Validate the pipeline. 9. Run the pipeline. 10. Monitor the execution. 11. Open the Lakehouse. 12. Query the resulting table.

SELECT COUNT(*) AS CustomerCount
FROM Customers;

Common Errors

1. Connection Failure — incorrect credentials; network/firewall restrictions; self-hosted integration runtime issues for on-premises sources.
2. Column Mapping Error — source and destination schemas don't match; data types are incompatible.
3. Duplicate Records — running a full load repeatedly without an appropriate overwrite/merge strategy. Solution: use an incremental loading strategy or appropriate destination write behavior.
4. Pipeline Timeout — large source dataset; slow source database; network problems; inefficient query. Solution: optimize the source query and consider partitioning/parallelism.

Interview Questions

1. What is Copy Activity? A Data Factory pipeline activity used to move data from a source system to a destination system.
2. What are the main components of Copy Activity? Source, destination, connection, mapping, performance configuration, fault tolerance, and monitoring.
3. Can Copy Activity transform data? It supports basic mapping and data movement capabilities, but it is not intended for complex transformations. Dataflow Gen2 or other transformation activities are better suited for complex transformations.
4. What is the difference between Full Load and Incremental Load? Full Load copies all source data, while Incremental Load copies only new or modified data.
5. How can Copy Activity performance be improved? Through parallel copy, partitioning, optimized source queries, appropriate file formats, and suitable integration runtime configuration.
6. Can Copy Activity copy data from SQL Server to a Fabric Lakehouse? Yes.
7. How do you monitor a Copy Activity? Use the pipeline monitoring experience to review status, duration, rows read/written, data transferred, throughput, and error details.

Key Takeaways

• Copy Activity is primarily used for data movement in Fabric Data Factory.
• It supports many sources and destinations.
• The basic workflow is Source → Copy Activity → Destination.
• Column mapping controls how source data is written to the destination.
• Copy Activity supports both Full Loads and Incremental Loads.
• Use Dataflow Gen2 when complex visual transformations are required.
• Always monitor production Copy Activities for failures, throughput, and data-volume anomalies.

Module 3 · Lesson 3.4 — REST APIs

3.4 REST APIs in Microsoft Fabric

Learning Objectives

• Understand REST APIs and their architecture.
• Understand HTTP methods and status codes.
• Connect REST APIs to Microsoft Fabric.
• Retrieve JSON data from an API.
• Configure authentication.
• Handle API parameters and pagination.
• Load REST API data into a Fabric Lakehouse.

What is a REST API?

REST (Representational State Transfer) is an architectural style used to allow applications and systems to communicate over the internet using HTTP. A REST API exposes data or functionality through URLs called endpoints.

https://api.example.com/customers

A client sends an HTTP request to the endpoint, and the API returns a response, commonly in JSON format.

REST API Architecture

Client → HTTP Request → REST API Server → Query Database →
Database → REST API Server → JSON Response → Client

In Microsoft Fabric:

REST API → Data Factory Pipeline → Copy Activity →
Lakehouse → Delta Table

Why Use REST APIs for Data Ingestion?

Many modern applications expose their data through REST APIs: CRM systems, ERP systems, payment platforms, weather services, e-commerce applications, social media platforms, SaaS applications. Instead of directly accessing the application's database, a data engineer can retrieve data through its API.

HTTP Methods

For data ingestion, GET is the most commonly used method.

GET Request example:

GET https://api.example.com/customers

Response:

{
  "customers": [
    { "id": 101, "name": "John", "country": "USA" },
    { "id": 102, "name": "Anita", "country": "India" }
  ]
}

REST API Endpoint

A URL through which a specific API resource can be accessed, e.g. /customers, /products, /orders, /employees.

Query Parameters

Query parameters allow you to control the data returned by an API.

https://api.example.com/orders?country=India
https://api.example.com/orders?country=India&year=2026

Common parameters: Date, Page number, Page size, Country, Status, Search keyword.

API Response

REST APIs commonly return JSON:

{
  "id": 1001,
  "customer": "Sreehari",
  "amount": 2500,
  "country": "India"
}

JSON is especially useful for Fabric because it can be processed using Data Factory, Dataflow Gen2, Spark, and Notebooks.

HTTP Status Codes

REST API Authentication

1. API Key — passed in a header, e.g. x-api-key: ABC123, identifying the client. 2. Basic Authentication — uses username and password. Credentials should never be hard-coded into notebooks or pipelines. 3. Bearer Token — passed in the HTTP header: Authorization: Bearer <token>, commonly used with OAuth 2.0. 4. OAuth 2.0 — allows applications to securely access APIs without sharing the user's password with the application.

Application → Authentication Server → Access Token → REST API

REST API Pagination

Large APIs generally don't return all records in a single request.

Request 1 → Page 1 → 1,000 records
Request 2 → Page 2 → 1,000 records
Request 3 → Page 3 → 1,000 records

Pagination is critical when ingesting large datasets.

Types of Pagination: - Page Number — ?page=1, ?page=2 - Offset — ?offset=0&limit=100 - Cursor — ?cursor=abc123 - Next URL — API response provides the URL for the next page:

{
  "data": [...],
  "next": "https://api.example.com/orders?page=2"
}

REST API → Fabric Lakehouse

REST API → Data Factory → Copy Activity → JSON Data →
Lakehouse Files → Delta Table → Power BI

Creating REST API Ingestion Pipeline

Step 1: Create a Data Pipeline — New Item → Data Pipeline, e.g. PL_RESTAPI_To_Lakehouse.

Step 2: Add Copy Activity to the pipeline.

Step 3: Configure REST API Source:

Base URL: https://api.example.com
Relative path: customers

Step 4: Configure Authentication — Anonymous, Basic Authentication, API Key, or OAuth 2.0. Use secure connection mechanisms for production credentials.

Step 5: Configure Destination:

Destination: Fabric Lakehouse
Lakehouse: SalesLakehouse
Table: Customers

Example API Response:

[
  { "id": 1, "name": "John", "country": "USA" },
  { "id": 2, "name": "Anita", "country": "India" }
]

Resulting Delta table: | ID | Name | Country | |---|---|---| | 1 | John | USA | | 2 | Anita | India |

Incremental REST API Loading

Instead of retrieving all records every time, use a date or timestamp parameter:

https://api.example.com/orders?modifiedAfter=2026-08-21T00:00:00

Last Successful Timestamp → REST API → New/Changed Records → Lakehouse

This significantly reduces unnecessary API calls and data movement.

API Rate Limits

Example: Maximum 1,000 requests/hour. If your pipeline exceeds the limit, the API may return 429 Too Many Requests.

Best practices: respect API limits, use pagination efficiently, avoid unnecessary API calls, implement retry logic where appropriate, use incremental extraction.

Handling API Errors

API Request → Success?
   Yes → Process Data
   No  → Retry → Failure Handling

Common failures: authentication errors, invalid parameters, API rate limits, network failures, server errors, invalid JSON.

REST API vs Database

Real-World Example

An e-commerce company uses a SaaS order management system exposing GET /orders. The Fabric pipeline runs every hour:

SaaS Order API → Copy Activity → JSON → Lakehouse →
Delta Orders Table → Power BI

The pipeline retrieves only orders modified since the previous successful run.

Best Practices

• Store API credentials securely.
• Use OAuth or managed authentication where supported.
• Implement pagination for large datasets.
• Use incremental loading whenever possible.
• Respect API rate limits.
• Implement retry and failure handling.
• Validate API responses before loading.
• Log request and pipeline execution details.
• Avoid storing sensitive API tokens in plain text.

Common Issues

• 401 Unauthorized — invalid or expired authentication credentials. Solution: verify API key/token/OAuth configuration.
• 403 Forbidden — authentication succeeded but insufficient permissions. Solution: verify API permissions.
• 404 Not Found — incorrect endpoint or relative URL. Solution: verify the API endpoint.
• 429 Too Many Requests — API rate limit exceeded. Solution: reduce request frequency and implement retry/backoff.
• 500 Internal Server Error — error on the API server. Solution: retry and check the provider's status/documentation.

Interview Questions

1. What is a REST API? An HTTP-based interface that allows applications to exchange data and functionality using resources exposed through endpoints.
2. Which HTTP method is most commonly used for data ingestion? GET, because it retrieves data from an API.
3. What format do REST APIs commonly return? JSON, although XML and other formats are also possible.
4. What is API pagination? Dividing a large API response into multiple smaller requests or pages so data can be retrieved efficiently.
5. What does HTTP 429 mean? The client has exceeded the API's allowed request rate.
6. How can REST API data be loaded into a Fabric Lakehouse? A Fabric Data Factory Pipeline can use a REST API as the source through a Copy Activity and write results to Lakehouse Files or Delta Tables.
7. How do you implement incremental loading from a REST API? Use a watermark such as modifiedDate, updatedAt, or an API-provided cursor/token to retrieve only changed records since the previous successful run.

Key Takeaways

• REST APIs provide a standard way for applications to exchange data over HTTP.
• GET is commonly used to retrieve data for ingestion.
• REST APIs commonly return JSON.
• Fabric Data Factory can ingest REST API data using Copy Activity.
• Pagination, authentication, rate limits, and incremental loading are important considerations in production API pipelines.

Common architecture: REST API → Copy Activity → Lakehouse → Delta Table → Power BI

Module 3 · Lesson 3.5 — SQL Server

3.5 SQL Server → Microsoft Fabric

Learning Objectives

• Understand SQL Server as a data source for Fabric.
• Connect SQL Server to Microsoft Fabric Data Factory.
• Configure a SQL Server connection.
• Use Copy Activity to ingest SQL Server data.
• Load SQL Server tables into a Lakehouse.
• Understand on-premises connectivity.
• Implement basic incremental ingestion.

What is SQL Server?

Microsoft SQL Server is a relational database management system (RDBMS) used to store and manage structured data. Organizations commonly use it for customer data, sales transactions, orders, inventory, finance, HR systems, and operational applications.

Microsoft Fabric can ingest SQL Server data into a Lakehouse or Warehouse for analytics.

SQL Server → Fabric Architecture

SQL Server → SQL Server Connector → Fabric Data Factory →
Copy Activity → OneLake → Lakehouse → Delta Table → Power BI

For an on-premises SQL Server, additional network connectivity may be required, such as a suitable gateway/integration runtime configuration.

SQL Server Connection Types

1. On-Premises SQL Server — e.g. Server: SQLSERVER01, Database: RetailDB. The Fabric pipeline needs connectivity to the on-premises environment. 2. Azure SQL Database — a cloud-hosted relational database that can also be used as a Fabric source.

Prerequisites

SQL Server instance, database, source table, appropriate database permissions, network connectivity, Fabric workspace, Fabric Data Factory access.

Server: SQLSERVER01, Database: RetailDB, Schema: dbo, Table: Customers

Sample SQL Server Table

CREATE TABLE dbo.Customers
(
    CustomerID INT,
    CustomerName VARCHAR(100),
    Country VARCHAR(50),
    Email VARCHAR(150),
    ModifiedDate DATETIME
);

Sample data: | CustomerID | CustomerName | Country | ModifiedDate | |---|---|---|---| | 101 | John | USA | 2026-08-20 | | 102 | Anita | India | 2026-08-21 | | 103 | David | UK | 2026-08-21 |

Step 1: Open Data Factory

New Item → Data Pipeline, e.g. PL_SQLServer_To_Lakehouse.

Step 2: Add Copy Activity

Add Copy Data to the pipeline canvas — the activity contains Source → Destination.

Step 3: Configure SQL Server Source

Select the SQL Server connector, create/select a connection, and provide connection info such as Server: SQLSERVER01, Database: RetailDB. Choose the appropriate authentication method.

Step 4: Select Source Table

Select dbo.Customers, or use a source query:

SELECT
    CustomerID,
    CustomerName,
    Country,
    Email,
    ModifiedDate
FROM dbo.Customers;

Using a query is useful to select specific columns, filter records, join multiple tables, or implement incremental extraction.

Step 5: Configure Lakehouse Destination

Destination: Fabric Lakehouse
Lakehouse: SalesLakehouse
Table: Customers

Step 6: Configure Column Mapping

Step 7: Run the Pipeline

Click Validate → Run. Fabric executes the Copy Activity:

SQL Server → Copy Activity → SalesLakehouse → Customers Delta Table

Step 8: Verify the Data

Open Lakehouse → Tables → Customers, or query via SQL Analytics Endpoint:

SELECT * FROM Customers;
SELECT COUNT(*) AS CustomerCount FROM Customers;

Full Load

SELECT * FROM dbo.Customers;

SQL Server (1,000,000 rows) → Copy Activity → Lakehouse (1,000,000 rows)

Commonly used for: initial migration, small reference tables, initial Lakehouse population.

Incremental Load

For large SQL Server tables, copying the entire table every time is inefficient. Use a column such as ModifiedDate:

SELECT * FROM dbo.Customers
WHERE ModifiedDate > '2026-08-21 00:00:00';

SQL Server → ModifiedDate > Last Watermark → New/Changed Rows → Lakehouse

The last successful timestamp can be stored in a control table or pipeline metadata mechanism.

SQL Server → Lakehouse Using MERGE

SQL Server → Incremental Extract → Staging Table →
MERGE → Customers Delta Table

MERGE INTO Customers AS Target
USING CustomerStage AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
    UPDATE SET
        Target.CustomerName = Source.CustomerName,
        Target.Country = Source.Country,
        Target.Email = Source.Email,
        Target.ModifiedDate = Source.ModifiedDate
WHEN NOT MATCHED THEN
    INSERT (CustomerID, CustomerName, Country, Email, ModifiedDate)
    VALUES (Source.CustomerID, Source.CustomerName, Source.Country, Source.Email, Source.ModifiedDate);

SQL Server Authentication

Depending on the environment: SQL Authentication, Windows authentication, Microsoft Entra-based authentication, other supported mechanisms. For production, prefer secure identity-based authentication where supported.

On-Premises SQL Server Connectivity

Corporate Network → On-Premises SQL Server → (Secure Connectivity) →
Fabric Data Factory → Lakehouse

The required connectivity component depends on the Fabric connector and network architecture being used.

Performance Optimization

1. Filter at the Source — use WHERE ModifiedDate >= '2026-08-21' instead of SELECT *.
2. Select Required Columns — prefer explicit column lists over SELECT *.
3. Use Incremental Loads — avoid repeatedly transferring millions of unchanged records.
4. Optimize the Source Query — ensure appropriate indexes exist on frequently filtered columns such as ModifiedDate, OrderDate, CustomerID.

Monitoring

Monitor: pipeline status, Copy Activity status, rows read/written, data transferred, duration, throughput, errors.

Pipeline: PL_SQLServer_To_Lakehouse
Status: Succeeded
Rows Read: 250,000
Rows Written: 250,000
Duration: 03:42

Common Errors

• Connection Failed — incorrect server name; invalid credentials; network connectivity problem; firewall restriction; required connectivity component unavailable.
• Login Failed — incorrect username/password; user doesn't have database access; incorrect authentication method.
• Timeout — very large source table; slow SQL query; network latency; source database performance issues.
• Data Type Conversion Error — e.g. SQL Server Amount → VARCHAR vs Lakehouse Amount → DECIMAL. Validate and transform the source data before loading.

Real-World Example

                    SQL Server
          ┌─────────────┼─────────────┐
      Customers       Orders       Products
          └─────────────┼─────────────┘
                  Copy Activities
                         │
                    Lakehouse
                         │
                  Delta Tables
                         │
                    Power BI

For large transaction tables such as Orders, incremental loading can be used.

Best Practices

• Use incremental loads for large transactional tables.
• Filter data at the source whenever possible.
• Select only required columns.
• Use secure authentication.
• Avoid hard-coding credentials.
• Monitor pipeline executions.
• Validate source and destination row counts.
• Maintain a watermark for incremental ingestion.
• Use Delta MERGE for upsert scenarios.
• Keep raw/staging data separate from curated data where appropriate.

Interview Questions

1. How can SQL Server data be ingested into Microsoft Fabric? Using Fabric Data Factory pipelines, typically with a Copy Activity that reads from SQL Server and writes to a Lakehouse or Warehouse.
2. What is the difference between full load and incremental load? A full load copies all source records, while an incremental load copies only new or modified records.
3. How can you implement an incremental SQL Server load? Use a watermark column such as ModifiedDate or an increasing ID to identify records changed since the previous successful pipeline execution.
4. Why should filtering be performed at the SQL Server source? Source-side filtering reduces data read and transferred, improving pipeline performance and reducing unnecessary network and compute usage.
5. How can you handle updates to existing records in a Lakehouse? Extract new/changed records from SQL Server and use a Delta MERGE operation.
6. What should you monitor after a SQL Server Copy Activity? Execution status, rows read, rows written, data transferred, duration, throughput, and errors.

Key Takeaways

• SQL Server is a common enterprise source for Microsoft Fabric ingestion.
• Copy Activity can move SQL Server data into a Fabric Lakehouse.
• Use full loads for initial or small datasets.
• Use incremental loads for large transactional tables.
• A ModifiedDate or similar watermark column is commonly used for incremental extraction.
• Use Delta MERGE to synchronize new and changed records.
• Source-side filtering and proper monitoring are essential for production-grade ingestion pipelines.

Module 3 · Lesson 3.6 — Azure Blob

3.6 Azure Blob Storage → Microsoft Fabric

Learning Objectives

• Understand Azure Blob Storage.
• Understand containers, blobs, and storage accounts.
• Connect Azure Blob Storage to Microsoft Fabric.
• Use Copy Activity to ingest Blob data.
• Load CSV and Parquet files into a Fabric Lakehouse.
• Understand Blob Storage vs ADLS Gen2.
• Implement basic ingestion patterns.

What is Azure Blob Storage?

Azure Blob Storage is Microsoft's cloud object storage service for storing large amounts of unstructured data: CSV files, JSON files, Parquet files, images, videos, application logs, backups, documents, and data exchange between applications.

In a Fabric data engineering solution, Azure Blob Storage can act as a source system, with data ingested into OneLake and Lakehouse tables.

Azure Blob Storage Architecture

Azure Storage Account → Blob Container
   ├── sales.csv
   ├── customers.csv
   └── orders.parquet
        │
Fabric Data Factory → Copy Activity → OneLake →
Lakehouse → Delta Tables → Power BI

Key Azure Blob Storage Concepts

1. Storage Account — provides the namespace and storage services, e.g. demosaaccount. 2. Container — a logical grouping of blobs, e.g. Storage Account → sales-data. 3. Blob — an individual object/file stored inside a container, e.g. sales_2026_08_20.csv.

Types of Azure Blobs

For data engineering and Fabric ingestion, Block Blobs are commonly used.

Azure Blob vs ADLS Gen2

Common File Formats

CSV, JSON, Parquet, XML, TXT, Avro, Images, PDF. For analytics workloads, Parquet is generally more efficient than CSV because it is a compressed columnar format.

Connecting Azure Blob Storage to Fabric

Azure Blob → Fabric Data Factory → Copy Activity →
Fabric Lakehouse → Delta Table

Prerequisites

An Azure Storage Account, a Blob Container, source files, appropriate permissions, a Microsoft Fabric workspace, access to Fabric Data Factory.

Storage Account: retailstorage, Container: sales, File: sales.csv

Step 1: Create a Data Pipeline

New Item → Data Pipeline, e.g. PL_Blob_To_Lakehouse.

Step 2: Add Copy Activity

Add Copy Data to the pipeline canvas.

Step 3: Configure Azure Blob Source

Choose the Azure Blob Storage connector; configure the connection to your storage account.

Storage Account: retailstorage, Container: sales, File: sales.csv

Step 4: Select the File

Configure the source to read a specific file, multiple files, files matching a pattern, or files in a folder:

sales/
├── sales_2026_08_20.csv
├── sales_2026_08_21.csv
└── sales_2026_08_22.csv

A wildcard or file pattern can be useful for processing multiple daily files, depending on connector configuration.

Step 5: Configure Lakehouse Destination

Destination → Lakehouse
Lakehouse: SalesLakehouse
Table: Sales

The destination table is stored as a Delta table.

Step 6: Configure Mapping

Step 7: Run the Pipeline

Click Validate → Run:

Blob Storage → Copy Activity → Lakehouse → Sales Delta Table

Step 8: Verify the Data

SELECT * FROM Sales;
SELECT COUNT(*) AS TotalRecords FROM Sales;

Loading CSV Files

Source:

SaleID,CustomerName,Country,Amount
1,John,USA,2500
2,Anita,India,3200
3,David,UK,1800

Resulting Delta table: | SaleID | CustomerName | Country | Amount | |---|---|---|---| | 1 | John | USA | 2500 | | 2 | Anita | India | 3200 | | 3 | David | UK | 1800 |

Loading Parquet Files

Blob Storage → sales_2026.parquet → Copy Activity →
Lakehouse → Sales Delta Table

Advantages: columnar storage, compression, efficient analytical queries, reduced data transfer.

Multiple File Ingestion

Azure Blob
└── sales/
    ├── sales_2026_08_20.csv
    ├── sales_2026_08_21.csv
    ├── sales_2026_08_22.csv
    └── sales_2026_08_23.csv

The pipeline can be configured to process files according to a naming pattern or folder structure.

Incremental Blob Loading

The pipeline should process only the new file rather than reprocessing every historical file.

Blob Storage → New Daily File → Copy Activity →
Lakehouse → Delta Table

Common techniques: file naming conventions, folder partitioning, metadata-driven pipelines, file modified timestamps, control tables, processed-file tracking.

Blob Storage Authentication

Common approaches depend on the connector and environment: Account key, Shared Access Signature (SAS), Microsoft Entra ID, managed identity/service principal where supported. Prefer secure identity-based authentication where possible and avoid exposing long-lived credentials.

Security

• Use least-privilege access.
• Protect storage account credentials.
• Prefer Microsoft Entra-based authentication where supported.
• Use private networking where required by the organization.
• Control access to containers and files.
• Enable appropriate monitoring and auditing.

Performance Optimization

1. Prefer Parquet — CSV is larger and slower; Parquet is compressed and efficient.
2. Process Files in Parallel — where supported, multiple files can be processed concurrently.
3. Avoid Unnecessary Copies — use incremental or metadata-driven ingestion instead of repeatedly processing historical files.

Monitoring

Pipeline: PL_Blob_To_Lakehouse
Status: Succeeded
Files Read: 5
Rows Read: 500,000
Rows Written: 500,000
Duration: 04:12

Common Errors

1. Authentication Failed — invalid storage credentials; expired SAS token; insufficient permissions.
2. Container Not Found — incorrect container name; wrong storage account; incorrect connection configuration.
3. File Not Found — incorrect folder path; incorrect file name; file was moved or deleted.
4. CSV Parsing Error — incorrect delimiter; invalid encoding; inconsistent number of columns; corrupt file.
5. Duplicate Data — the same files are processed multiple times. Solution: maintain processed-file tracking or use an appropriate incremental ingestion strategy.

Real-World Example

Store Systems → Azure Blob Storage
   ├── sales_2026_08_20.csv
   ├── sales_2026_08_21.csv
   └── sales_2026_08_22.csv
        │
Fabric Data Factory → Copy Activity → SalesLakehouse →
Delta Table → Power BI

The pipeline can be scheduled daily and configured to process only newly arrived files.

Blob → Lakehouse vs SQL Server → Lakehouse

Interview Questions

1. What is Azure Blob Storage? Microsoft's cloud object storage service for storing large amounts of unstructured data such as CSV, JSON, Parquet, images, logs, and documents.
2. What is a Blob? An individual object or file stored inside an Azure Storage container.
3. How do you load Blob data into a Fabric Lakehouse? Create a Fabric Data Factory pipeline, configure Azure Blob Storage as the source, add a Copy Activity, configure the Lakehouse as the destination, and execute the pipeline.
4. What is the difference between Blob Storage and ADLS Gen2? ADLS Gen2 builds on Blob Storage and adds a hierarchical namespace and filesystem-oriented access controls, particularly useful for data lake and analytics workloads.
5. How can you prevent processing the same Blob file multiple times? Use file naming conventions, folder partitioning, metadata-driven pipelines, or a control table of already-processed files.
6. Which file format is generally preferred for large-scale analytics? Parquet, because it's columnar, compressed, and efficient.

Key Takeaways

• Azure Blob Storage is a common file-based source for Microsoft Fabric.
• Data can be ingested using Fabric Data Factory + Copy Activity.
• CSV, JSON, and Parquet are common ingestion formats.
• For large analytics workloads, Parquet is generally more efficient than CSV.
• Use incremental/file-tracking strategies to avoid processing the same files repeatedly.

Common enterprise pattern: Azure Blob → Copy Activity → Lakehouse → Delta Table → Power BI

Module 3 · Lesson 3.7 — ADLS

3.7 Azure Data Lake Storage (ADLS Gen2)

Learning Objectives

• Understand Azure Data Lake Storage Gen2.
• Understand the ADLS architecture.
• Understand containers, directories, and files.
• Connect ADLS Gen2 to Microsoft Fabric.
• Use Copy Activity to ingest ADLS data.
• Load ADLS files into a Fabric Lakehouse.
• Understand ADLS Gen2 vs Azure Blob Storage.
• Apply basic security and performance best practices.

What is ADLS Gen2?

Azure Data Lake Storage Gen2 (ADLS Gen2) is Microsoft's cloud storage service designed for big data analytics and data lake workloads. Built on Azure Blob Storage, it adds: hierarchical namespace, directory and file-level organization, fine-grained access control, high-performance analytics, and integration with Azure and Microsoft Fabric.

ADLS Gen2
├── Raw Data
├── Processed Data
└── Curated Data
       │
Fabric Data Factory → Copy Activity → OneLake →
Lakehouse → Delta Tables

Why Use ADLS Gen2?

Commonly used as an enterprise data lake for huge amounts of data at scale: enterprise data lakes, data warehouse staging, big data analytics, machine learning datasets, IoT data, application logs, historical data, ETL/ELT processing.

ADLS Gen2 Architecture

Azure Storage Account → Hierarchical Namespace → Container
   ├── Raw (CSV/JSON)
   ├── Processed (Parquet)
   └── Curated (Delta)
        │
Microsoft Fabric → Lakehouse

Key Components

1. Storage Account — top-level namespace, e.g. companydatalake. 2. Container / File System — top-level data lake filesystem, e.g. companydatalake/sales. 3. Directories — ADLS Gen2 supports hierarchical directories:

sales/
├── raw/
├── processed/
└── curated/

This is one of the major differences between standard Blob Storage and ADLS Gen2. 4. Files — stored inside directories:

sales/
└── raw/
      ├── sales_2026_08_20.csv
      ├── sales_2026_08_21.csv
      └── sales_2026_08_22.csv

ADLS Gen2 vs Azure Blob Storage

Simple Rule: Blob Storage → general-purpose object storage. ADLS Gen2 → enterprise data lake and analytics workloads.

ADLS Data Organization

companydatalake
├── sales
│   ├── raw
│   ├── processed
│   └── curated
├── finance
│   ├── raw
│   ├── processed
│   └── curated
└── customer
    ├── raw
    ├── processed
    └── curated

This separates data according to business domains and processing stages.

Connecting ADLS Gen2 to Microsoft Fabric

Approach 1: Copy Data — copy data from ADLS into a Fabric Lakehouse:

ADLS Gen2 → Copy Activity → OneLake → Lakehouse

Approach 2: OneLake Shortcut — create a shortcut to ADLS data without physically copying it:

ADLS Gen2 → (Shortcut) → OneLake/Lakehouse

The appropriate approach depends on data ownership, performance, freshness, governance, and architecture requirements.

ADLS Gen2 → Lakehouse Using Copy Activity

Step 1: Create a Data Pipeline — PL_ADLS_To_Lakehouse.

Step 2: Add Copy Activity.

Step 3: Configure ADLS Source:

Storage Account: companydatalake
File System: sales
Folder: raw/2026/

Step 4: Select Files — one file, multiple files, a folder, or files matching a pattern:

raw/2026/
├── sales_01.csv
├── sales_02.csv
└── sales_03.csv

Step 5: Configure Lakehouse Destination:

Destination → Lakehouse
Lakehouse: SalesLakehouse
Table: Sales

Step 6: Configure Mapping: | ADLS Column | Lakehouse Column | |---|---| | Sale_ID | SaleID | | Customer_Name | CustomerName | | Sale_Date | SaleDate | | Amount | Amount |

Step 7: Run the Pipeline — Validate → Run:

ADLS Gen2 → Copy Activity → SalesLakehouse → Sales Delta Table

Verify the Data:

SELECT * FROM Sales;
SELECT COUNT(*) AS TotalRecords FROM Sales;

OneLake Shortcut to ADLS

Instead of copying data, Fabric can create a Shortcut to supported ADLS data.

ADLS Gen2
└── sales
      └── 2026
           ├── Jan
           ├── Feb
           └── Mar
                │
        OneLake Shortcut → Fabric Lakehouse

The shortcut provides access to the external data without creating another physical copy in OneLake.

Copy vs Shortcut

ADLS Security

Common mechanisms: Microsoft Entra ID, Azure RBAC, POSIX-style ACLs, managed identities, private endpoints, network security controls.

Data Engineer → Microsoft Entra ID → Azure RBAC/ACL → ADLS Gen2

Hierarchical Namespace

One of the most important ADLS Gen2 features (HNS).

Without HNS:

container/
  ├── file1
  ├── file2
  └── virtual-folder/file3

With HNS:

container/
├── raw/sales/
├── processed/sales/
└── curated/sales/

The directory structure is managed as a native hierarchy, particularly useful for analytics and access control.

File Formats

CSV, JSON, Parquet, Avro, ORC, XML, TXT. For analytical workloads, Parquet is generally preferred because it is columnar and compressed.

Incremental Loading from ADLS

sales/
├── 2026-08-20/
│   └── sales.csv
├── 2026-08-21/
│   └── sales.csv
└── 2026-08-22/
    └── sales.csv

A pipeline can process only the new partition:

New ADLS File → Copy Activity → Lakehouse → Delta Table

Common approaches: date-based folders, file naming conventions, metadata-driven pipelines, processed-file tracking, modified timestamps.

Performance Best Practices

1. Use Parquet — columnar/compressed vs CSV's row-based/larger format.
2. Partition Data:

sales/
├── year=2025/
│   ├── month=01/
│   └── month=02/
└── year=2026/
    ├── month=01/
    └── month=02/

Partitioning can reduce the amount of data scanned for filtered queries. 3. Avoid Small Files — thousands of tiny files can negatively affect data processing performance; combine small files into larger, optimized files where appropriate.

Monitoring

Pipeline: PL_ADLS_To_Lakehouse
Status: Succeeded
Files Read: 25
Rows Read: 2,500,000
Rows Written: 2,500,000
Duration: 08:25

Common Errors

• Authentication Failed — incorrect credentials; expired credentials; missing RBAC permissions; missing ACL permissions.
• File System Not Found — incorrect container/file system name; incorrect storage account.
• Access Denied — insufficient Azure RBAC permissions; missing ADLS ACL permissions; incorrect identity being used.
• Slow Pipeline — large number of small files; network limitations; inefficient source/destination configuration; poor partitioning.

Real-World Example

Enterprise Data Lake
├── CRM → Customers
├── ERP → Finance
├── Sales → Transactions
└── IoT → Sensor Data

ADLS Gen2
   ├── Shortcuts → OneLake
   └── Copy Activity → OneLake
        │
     Lakehouse → Delta Tables → Power BI

This allows the organization to use Fabric analytics while maintaining an existing enterprise data lake.

Interview Questions

1. What is ADLS Gen2? Microsoft's cloud-based data lake storage service, built on Azure Blob Storage with hierarchical namespace and advanced security capabilities for large-scale analytics.
2. What is the main difference between Blob Storage and ADLS Gen2? ADLS Gen2 adds a hierarchical namespace and filesystem-oriented access controls, suitable for enterprise data lake and analytics workloads.
3. How can ADLS data be accessed from Microsoft Fabric? Data can be copied into OneLake using Data Factory or accessed through OneLake Shortcuts for supported scenarios.
4. What is Hierarchical Namespace? It enables native directory and file organization in ADLS Gen2 and supports filesystem-style access control.
5. When should you use a Shortcut instead of Copy Activity? Use a Shortcut when you want Fabric to access supported external data without creating another physical copy; use Copy Activity when you need to ingest, transform, or persist the data into OneLake.
6. Which file format is preferred for large analytical datasets? Parquet, because it provides columnar storage and compression.

Key Takeaways

• ADLS Gen2 is an enterprise-grade data lake storage service.
• It is built on Azure Blob Storage and adds Hierarchical Namespace (HNS).
• ADLS is commonly used for large-scale data lake and analytics workloads.
• Fabric can ingest ADLS data using Copy Activity.
• OneLake Shortcuts can provide access to supported ADLS data without copying it.
• Use Parquet, sensible partitioning, and appropriate file sizes for analytical workloads.

Common architecture: ADLS Gen2 → Data Factory → OneLake → Lakehouse → Delta Tables → Power BI

Module 3 · Lesson 3.8 — Incremental Loads

3.8 Incremental Loads in Microsoft Fabric

Learning Objectives

• Understand full load vs incremental load.
• Understand why incremental loading is important.
• Implement watermark-based incremental loads.
• Load only new and modified records.
• Use MERGE for upsert operations.
• Design an incremental pipeline using Microsoft Fabric Data Factory.
• Handle deletes and common incremental-load problems.

What is an Incremental Load?

An incremental load is a data ingestion technique where only new or changed records are loaded from the source into the destination, instead of copying the entire source table every time.

Full Load: Source → (All records) → Lakehouse
Incremental Load: Source → (New + Changed records only) → Lakehouse

Why Use Incremental Loads?

Imagine a SQL Server table containing 100 million records. If only 50,000 records changed today, a full load would unnecessarily process all 100 million records.

100,000,000 records → Incremental filter → 50,000 changed records → Lakehouse

This reduces: data movement, processing time, network traffic, compute consumption, pipeline execution time.

Full Load vs Incremental Load

Incremental Load Architecture

Source System → Incremental Filter → New/Changed Data →
Copy Activity → Staging Area → MERGE →
Delta Lake Table → Power BI

Common Incremental Loading Methods

1. Watermark Column — e.g. ModifiedDate.

ID  Name   ModifiedDate
1   John   2026-08-20
2   Anita  2026-08-22
3   David  2026-08-22

LastWatermark = 2026-08-21

SELECT * FROM Customers WHERE ModifiedDate > '2026-08-21';

Only records modified after the watermark are retrieved.

2. Increasing ID — if the source has a continuously increasing ID:

LastCustomerID = 1003

SELECT * FROM Customers WHERE CustomerID > 1003;

This works well when records are insert-only.

3. Change Data Capture (CDC) — tracks inserts, updates, and deletes made to source tables:

Source Database → CDC → Insert/Update/Delete → Fabric

CDC is useful when you need to capture both changes and deletions.

4. Change Tracking — some database platforms provide change tracking information that identifies changed records since the previous extraction.

Watermark Concept

A watermark is a value that represents the point up to which data has already been successfully processed.

Run 1: Watermark = 2026-08-20 → Load data → New Watermark = 2026-08-21
Run 2: Watermark = 2026-08-21 → Load records after 2026-08-21

Watermark Table

A common enterprise approach is to create a control table:

CREATE TABLE PipelineWatermark
(
    TableName VARCHAR(100),
    WatermarkValue DATETIME2,
    LastRunDate DATETIME2
);

This table controls subsequent incremental loads.

Incremental Load Example

Last successful watermark: 2026-08-21 00:00:00.

SELECT
    CustomerID,
    CustomerName,
    Country,
    ModifiedDate
FROM dbo.Customers
WHERE ModifiedDate > '2026-08-21 00:00:00';

The source returns only new or modified records.

Fabric Pipeline Design

Lookup Watermark
       │
Copy Activity (Source: SQL Server)
       │
Staging / Lakehouse
       │
MERGE into Delta Table
       │
Update Watermark

Important Rule: Update Watermark Only After Success

This is critical.

Read Watermark → Extract Data → Load Data →
MERGE Successfully → Update Watermark

Do not update the watermark before the destination load succeeds. Otherwise, failed records could be skipped during the next execution.

MERGE into Delta Table

Staging table:

CustomerID | CustomerName | Country
101        | John         | USA
102        | Anita        | India
104        | Rahul        | India

MERGE INTO Customers AS Target
USING CustomerStage AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
    UPDATE SET
        Target.CustomerName = Source.CustomerName,
        Target.Country = Source.Country,
        Target.ModifiedDate = Source.ModifiedDate
WHEN NOT MATCHED THEN
    INSERT (CustomerID, CustomerName, Country, ModifiedDate)
    VALUES (Source.CustomerID, Source.CustomerName, Source.Country, Source.ModifiedDate);

Result: existing customer → UPDATE; new customer → INSERT.

Handling Deletes

Incremental loads become more complicated when records are deleted from the source. If the source simply stops returning a deleted record, the destination doesn't automatically know it was deleted.

Possible solutions: - CDC — capture delete events. - Soft Delete Flag — source maintains IsDeleted = 1. - Periodic Full Reconciliation — occasionally compare source and destination to detect missing records.

Incremental Load from Blob Storage

Azure Blob
└── Sales
    ├── 2026-08-20
    ├── 2026-08-21
    └── 2026-08-22 ← New

The pipeline processes only the new date instead of reprocessing all historical files.

Incremental Load from REST API

REST APIs commonly provide parameters such as modifiedSince, updatedAfter, fromDate, lastUpdated:

https://api.example.com/orders?updatedAfter=2026-08-21

The API returns only records changed after the specified timestamp.

Incremental Load from ADLS

A file-based approach can use: folder date, file name, last modified timestamp, file metadata, processed-file control table.

ADLS
├── 2026/08/20/
├── 2026/08/21/
└── 2026/08/22/ ← Process

End-to-End Example

Scenario: A company has an SQL Server Orders table with 50 million records. Every day approximately 100,000 records are inserted or updated.

Full Load:

50,000,000 rows → Copy Activity → Lakehouse

This is expensive and unnecessary.

Incremental Load:

SQL Server → WHERE ModifiedDate > LastWatermark →
100,000 rows → Lakehouse Staging → MERGE → Orders Delta Table

This is much more efficient.

Incremental Load Best Practices

1. Use a Reliable Watermark — ModifiedDate, UpdatedTimestamp, SequenceID, ChangeVersion.
2. Store Watermark Outside the Pipeline — use a control table or other durable metadata store.
3. Update Only After Successful Processing — never advance the watermark prematurely.
4. Handle Late-Arriving Data — consider a small overlap window:

WHERE ModifiedDate >= DATEADD(minute, -5, @LastWatermark)

Then use MERGE to prevent duplicates. 5. Make Pipelines Idempotent — running the same pipeline twice should not create duplicate business records. 6. Monitor Row Counts — compare source rows vs target rows. 7. Handle Deletes — use CDC, soft deletes, or reconciliation processes where required.

Common Problems

• Duplicate Records — the same incremental records are inserted repeatedly. Solution: use business keys and MERGE.
• Missing Records — watermark updated before successful processing. Solution: update watermark only after the entire load succeeds.
• Late-Arriving Records — source records arrive with an older timestamp. Solution: use an overlap window and deduplicate using MERGE.
• Deleted Records Not Captured — source query only detects inserts and updates. Solution: use CDC, soft deletes, or periodic reconciliation.

Interview Questions

1. What is an incremental load? Processing only new or changed data since the previous successful load instead of processing the entire source dataset.
2. What is a watermark? A value, such as a timestamp or sequence number, that identifies the point up to which data has already been successfully processed.
3. What columns are commonly used as watermarks? ModifiedDate, UpdatedDate, timestamp columns, increasing IDs, or change-version columns.
4. Why is MERGE useful for incremental loading? MERGE can update existing records and insert new records in a single operation, suitable for synchronizing incremental data with Delta tables.
5. How do you handle deleted records? Use CDC, a soft-delete flag, or periodic source-to-target reconciliation.
6. When should the watermark be updated? Only after the incremental data has been successfully processed and committed to the destination.
7. What is the advantage of incremental loading over full loading? It reduces data movement, execution time, compute usage, and network traffic while improving pipeline efficiency.

Key Takeaways

• Incremental loading processes only new and changed data.
• It is essential for efficiently loading large datasets.
• Watermarks are commonly used to identify changes.
• MERGE is useful for updating existing records and inserting new records.
• Deletes require additional handling through CDC, soft deletes, or reconciliation.
• Always update the watermark after successful processing.

Production-grade pattern:

Source → Read Watermark → Extract New/Changed Data →
Staging → MERGE into Delta → Validate → Update Watermark

Module 3 · Lesson 3.9 — SQL → Lakehouse

3.9 SQL Server → Lakehouse

Learning Objectives

• Understand the SQL Server → Lakehouse ingestion architecture.
• Create a Fabric Data Factory pipeline.
• Configure SQL Server as a source.
• Configure a Fabric Lakehouse as a destination.
• Use Copy Activity to transfer data.
• Validate the ingested Delta table.
• Implement a basic incremental-loading pattern.

Overview

A common Microsoft Fabric data engineering scenario is to extract data from an operational SQL Server database and load it into a Fabric Lakehouse for analytics.

SQL Server (Customers, Products, Orders)
        │ Extract
Fabric Data Factory (Copy Activity)
        │ Load
OneLake → Lakehouse → Delta Tables
        │
     Power BI

Business Scenario

RetailDB
├── Customers
├── Products
├── Orders
└── OrderItems

Instead of allowing Power BI to directly query the operational database, data is periodically copied into a Lakehouse.

Source SQL Server

CREATE TABLE dbo.Customers
(
    CustomerID INT,
    CustomerName VARCHAR(100),
    Country VARCHAR(50),
    Email VARCHAR(150),
    ModifiedDate DATETIME
);

Step 1: Create the Lakehouse

Create SalesLakehouse with Files, Tables, SQL Analytics Endpoint, Semantic Model.

Step 2: Create a Data Pipeline

PL_SQLServer_To_Lakehouse.

Step 3: Add Copy Activity

PL_SQLServer_To_Lakehouse → Copy Activity

Step 4: Configure SQL Server Source

Server: SQLSERVER01, Database: RetailDB, Schema: dbo, Table: Customers

For an on-premises SQL Server, configure the required connectivity for your environment.

Step 5: Select Source Table

SELECT
    CustomerID,
    CustomerName,
    Country,
    Email,
    ModifiedDate
FROM dbo.Customers;

Step 6: Configure Lakehouse Destination

Destination Type: Lakehouse
Lakehouse: SalesLakehouse
Table: Customers

Stored as a Delta table.

Step 7: Configure Column Mapping

Step 8: Run the Pipeline

Validate → Run:

SQL Server → Customers → Copy Activity → SalesLakehouse → Customers Delta Table

Step 9: Monitor the Pipeline

Pipeline: PL_SQLServer_To_Lakehouse
Status: Succeeded
Rows Read: 100,000
Rows Written: 100,000
Duration: 02:15

Step 10: Validate the Lakehouse Table

SELECT * FROM Customers;
SELECT COUNT(*) AS TotalCustomers FROM Customers;

Full Load Pattern

SELECT * FROM Customers;

Suitable for: initial migration, small tables, reference data, one-time loads.

Incremental Load Pattern

Previous successful watermark: 2026-08-21 00:00:00.

SELECT
    CustomerID,
    CustomerName,
    Country,
    Email,
    ModifiedDate
FROM dbo.Customers
WHERE ModifiedDate > '2026-08-21 00:00:00';

Incremental Architecture

SQL Server → Read Last Watermark → ModifiedDate > Watermark →
Copy Activity → Staging → MERGE → Customers Delta Table →
Update Watermark

Using MERGE

MERGE INTO Customers AS Target
USING CustomerStage AS Source
ON Target.CustomerID = Source.CustomerID
WHEN MATCHED THEN
    UPDATE SET
        Target.CustomerName = Source.CustomerName,
        Target.Country = Source.Country,
        Target.Email = Source.Email,
        Target.ModifiedDate = Source.ModifiedDate
WHEN NOT MATCHED THEN
    INSERT (CustomerID, CustomerName, Country, Email, ModifiedDate)
    VALUES (Source.CustomerID, Source.CustomerName, Source.Country, Source.Email, Source.ModifiedDate);

This gives you: existing CustomerID → Update; new CustomerID → Insert.

Handling Deletes

A basic ModifiedDate filter does not automatically detect deleted SQL Server records. The Lakehouse still contains a deleted record unless the pipeline has a mechanism to detect the deletion.

Possible approaches: SQL Server Change Data Capture (CDC), soft-delete flag, Change Tracking, periodic source-to-target reconciliation.

Multi-Table Ingestion

SQL Server
├── Customers → Customers
├── Products → Products
├── Orders → Orders
└── OrderItems → OrderItems
        │
     Lakehouse

Option 1 — Separate Pipelines: PL_Customers_To_Lakehouse, PL_Products_To_Lakehouse, PL_Orders_To_Lakehouse.

Option 2 — Metadata-Driven Pipeline: a single reusable pipeline reads configuration: | Source Table | Target Table | Load Type | |---|---|---| | Customers | Customers | Incremental | | Products | Products | Full | | Orders | Orders | Incremental | | OrderItems | OrderItems | Incremental |

This approach is more scalable for enterprise environments.

Data Validation

Row Count:

SELECT COUNT(*) FROM dbo.Customers;  -- Source
SELECT COUNT(*) FROM Customers;      -- Destination

Null Checks:

SELECT COUNT(*) FROM Customers WHERE CustomerID IS NULL;

Duplicate Checks:

SELECT CustomerID, COUNT(*) AS RecordCount
FROM Customers
GROUP BY CustomerID
HAVING COUNT(*) > 1;

Common Errors

• Connection Failure — incorrect credentials; network connectivity; firewall restrictions; incorrect server/database name.
• Schema Mismatch — e.g. SQL Server Amount → VARCHAR vs Lakehouse Amount → DECIMAL. Invalid source values can cause ingestion failures.
• Duplicate Records — repeated full loads or incorrect incremental logic. Solution: use a business key and MERGE.
• Missing Records — incorrect watermark; incorrect source filter; pipeline failure; watermark updated before successful processing.

Best Practices

• Use full load for initial migration.
• Use incremental loads for large transactional tables.
• Maintain a reliable watermark.
• Update the watermark only after successful processing.
• Use MERGE for upsert scenarios.
• Validate source and destination row counts.
• Monitor pipeline execution.
• Use meaningful pipeline names.
• Keep source, staging, and curated layers logically separated where appropriate.
• Secure SQL Server credentials and connections.

Hands-On Lab

Objective: Build a pipeline to load SQL Server customer data into a Fabric Lakehouse. - Source: SQL Server, Database: RetailDB, Table: dbo.Customers. - Destination: Lakehouse: SalesLakehouse, Table: Customers. - Pipeline: PL_SQLServer_To_Lakehouse → Copy Activity → Customers Delta Table

Tasks: 1. Create SalesLakehouse. 2. Create PL_SQLServer_To_Lakehouse. 3. Add Copy Activity. 4. Configure SQL Server connection. 5. Select dbo.Customers. 6. Configure Lakehouse destination. 7. Configure column mapping. 8. Validate the pipeline. 9. Run the pipeline. 10. Monitor the execution. 11. Verify the Delta table. 12. Query the loaded data. 13. Compare source and destination row counts.

Interview Questions

1. How do you load SQL Server data into a Fabric Lakehouse? Use a Fabric Data Factory Pipeline with a Copy Activity, configure SQL Server as the source and the Lakehouse as the destination.
2. When should you use a full load? For initial migrations, small tables, or datasets where processing the entire source is acceptable.
3. When should you use an incremental load? For large transactional tables where only a small percentage of records change between pipeline executions.
4. How can you identify changed records? Use a ModifiedDate, timestamp, increasing ID, Change Tracking, or CDC depending on the source system.
5. How do you handle updates in the Lakehouse? Load the changed records into a staging area and use a Delta MERGE operation.
6. How do you validate a SQL Server → Lakehouse pipeline? Compare row counts, check duplicates and nulls, validate data types, and monitor the Copy Activity execution metrics.

Key Takeaways

• SQL Server → Lakehouse is a common Fabric ingestion pattern.
• Copy Activity performs the data movement.
• The Lakehouse stores the resulting data as Delta tables.
• Use full load for initial ingestion and incremental load for recurring large-scale ingestion.
• Use watermarks + MERGE for a robust incremental pattern.
• Always validate the data and monitor pipeline execution.

Core Pattern:

SQL Server → Data Factory Pipeline → Copy Activity →
Lakehouse → Delta Table → SQL/Spark/Power BI

Module 3 · Lesson 3.10 — Blob → Lakehouse

3.10 Blob → Lakehouse

Learning Objectives

• Understand the Blob Storage → Lakehouse ingestion pattern.
• Configure Azure Blob Storage as a source.
• Use Fabric Data Factory Copy Activity.
• Load CSV and Parquet files into a Lakehouse.
• Load multiple files from Blob Storage.
• Implement basic incremental file ingestion.
• Validate and monitor the pipeline.

What is Blob → Lakehouse?

Blob → Lakehouse is a common Microsoft Fabric data ingestion pattern where files stored in Azure Blob Storage are copied into a Microsoft Fabric Lakehouse using Data Factory Copy Activity.

Azure Blob Storage (sales.csv, customers.csv, orders.parquet)
        │ Copy Activity
Microsoft Fabric Data Factory
        │
OneLake → Lakehouse (Files, Tables)
        │
   Delta Tables → Power BI

Business Scenario

sales/
├── sales_2026_08_20.csv
├── sales_2026_08_21.csv
└── sales_2026_08_22.csv

The Fabric pipeline will: read files from Blob Storage, copy the data, store it in the Lakehouse, convert/load it into Delta tables, and make it available for SQL, Spark, and Power BI.

Source Data

SaleID,CustomerName,Country,Product,Amount
1001,John,USA,Laptop,2500
1002,Anita,India,Mobile,1200
1003,David,UK,Monitor,800
1004,Priya,India,Tablet,650

Prerequisites

Azure Storage Account, Blob container, source files, appropriate Blob permissions, Microsoft Fabric workspace, Fabric Lakehouse, access to Data Factory.

Storage Account: retailstorage, Container: sales, File: sales_2026_08_22.csv

Step 1: Create a Lakehouse

SalesLakehouse with Files, Tables, SQL Analytics Endpoint, Semantic Model.

Step 2: Create a Data Pipeline

PL_Blob_To_Lakehouse.

Step 3: Add Copy Activity

PL_Blob_To_Lakehouse → Copy Activity

Step 4: Configure Azure Blob Source

Storage Account: retailstorage, Container: sales
File: sales_2026_08_22.csv

Step 5: Configure File Format

For CSV: File Format: Delimited Text, Delimiter: ,, First Row as Header: Yes, Encoding: UTF-8. For Parquet: File Format: Parquet.

Step 6: Configure Lakehouse Destination

Destination: Lakehouse
Lakehouse: SalesLakehouse

For a structured analytics table, load into a Delta table such as Sales.

Step 7: Configure Column Mapping

Step 8: Run the Pipeline

Validate → Run:

Azure Blob → Copy Activity → SalesLakehouse → Sales Delta Table

Step 9: Monitor the Pipeline

Pipeline: PL_Blob_To_Lakehouse
Status: Succeeded
Files Read: 1
Rows Read: 10,000
Rows Written: 10,000
Duration: 00:45

Step 10: Verify the Lakehouse

SELECT * FROM Sales;
SELECT COUNT(*) AS TotalSales FROM Sales;

Loading Multiple Blob Files

Azure Blob
└── sales/
    ├── sales_2026_08_20.csv
    ├── sales_2026_08_21.csv
    └── sales_2026_08_22.csv

The Copy Activity can be configured for a folder or file pattern, e.g. sales_*.csv, loading into Sales Delta Table.

File-Based Incremental Loading

sales/
├── 2026-08-20/
├── 2026-08-21/
└── 2026-08-22/

Instead of processing all files repeatedly, the pipeline can identify and process only new files:

New Blob File → Copy Activity → Lakehouse → Delta Table

File Tracking

A production pipeline can maintain a control table: | FileName | ProcessedDate | Status | |---|---|---| | sales_2026_08_20.csv | 2026-08-20 | Success | | sales_2026_08_21.csv | 2026-08-21 | Success | | sales_2026_08_22.csv | 2026-08-22 | Success |

Before processing a file, the pipeline checks whether it has already been processed — helping prevent duplicate ingestion.

Date-Partitioned Blob Structure

sales/
└── year=2026/
    └── month=08/
        ├── day=20/
        ├── day=21/
        └── day=22/

This makes it easier to identify new data, organize historical data, process specific dates, and build scalable ingestion pipelines.

Blob → Lakehouse with Parquet

Azure Blob → sales_2026.parquet → Copy Activity →
Lakehouse → Delta Table

Benefits: columnar storage, compression, efficient data scanning, better analytical performance.

CSV vs Parquet

Blob → Lakehouse Using Shortcut

Copy Activity: Blob → Copy → OneLake
Shortcut: Blob ← Reference → OneLake

Use the approach that matches your requirements for data ownership, freshness, performance, governance, and storage.

Data Validation

Row Count:

SELECT COUNT(*) AS TotalRows FROM Sales;

Null Check:

SELECT COUNT(*) AS NullSaleIDs FROM Sales WHERE SaleID IS NULL;

Duplicate Check:

SELECT SaleID, COUNT(*) AS RecordCount
FROM Sales
GROUP BY SaleID
HAVING COUNT(*) > 1;

Common Errors

1. Authentication Failure — invalid credentials; expired SAS token; missing storage permissions.
2. File Not Found — incorrect container; incorrect folder path; incorrect file name; file was moved or deleted.
3. CSV Parsing Error — incorrect delimiter; missing header; invalid encoding; inconsistent number of columns.
4. Duplicate Data — the same Blob files are processed repeatedly. Solution: use file tracking, date-based folders, or another idempotent ingestion strategy.
5. Schema Mismatch — e.g. Blob Amount = "2500" (string) vs Lakehouse Amount = DECIMAL. Invalid values can cause conversion failures.

Production Architecture

Azure Blob Storage → New Files → Metadata Lookup →
Copy Activity → Bronze Lakehouse → Delta Table →
Transformation → Silver/Gold → Power BI

This separates ingestion from transformation and reporting.

Best Practices

• Use meaningful Blob folder structures.
• Prefer Parquet for large analytical datasets.
• Use incremental file processing.
• Maintain processed-file metadata.
• Validate row counts after ingestion.
• Use secure authentication.
• Avoid hard-coding credentials.
• Monitor Copy Activity executions.
• Design pipelines to be idempotent.
• Keep raw files available when audit/reprocessing requirements exist.

Hands-On Lab

Objective: Build a pipeline that loads an Azure Blob CSV file into a Fabric Lakehouse. - Source: Azure Blob Storage, Container: sales, File: sales.csv. - Destination: Lakehouse: SalesLakehouse, Table: Sales. - Pipeline: PL_Blob_To_Lakehouse → Copy Activity → Sales Delta Table

Tasks: 1. Create SalesLakehouse. 2. Create PL_Blob_To_Lakehouse. 3. Add Copy Activity. 4. Configure Azure Blob Storage connection. 5. Select sales.csv. 6. Configure CSV settings. 7. Select the Lakehouse destination. 8. Configure column mappings. 9. Validate the pipeline. 10. Run the pipeline. 11. Monitor the execution. 12. Verify the Sales table. 13. Run SQL validation queries.

Interview Questions

1. How do you load Azure Blob data into a Fabric Lakehouse? Use a Fabric Data Factory Pipeline with Copy Activity, configure Azure Blob Storage as the source and the Lakehouse as the destination.
2. Can Copy Activity process multiple Blob files? Yes — you can design the pipeline to process multiple files from a folder or according to a supported file pattern.
3. How do you prevent duplicate file processing? Use processed-file tracking, date-based folder structures, metadata-driven pipelines, or another idempotent ingestion mechanism.
4. Which format is preferred for large analytical datasets? Parquet, generally preferred because it is compressed and columnar.
5. What is the difference between Blob → Lakehouse Copy and a OneLake Shortcut? Copy Activity physically ingests data into OneLake, while a Shortcut provides a reference to supported external data without creating another physical copy.

Key Takeaways

• Blob → Lakehouse is a common file ingestion pattern in Microsoft Fabric.
• Copy Activity is used to move Blob data into a Lakehouse.
• CSV and Parquet are common source formats.
• Parquet is generally more efficient for large-scale analytics.
• Use incremental file processing to avoid duplicate or unnecessary ingestion.
• Validate row counts, schema, nulls, and duplicates after ingestion.

Core Pattern:

Azure Blob → Fabric Data Factory → Copy Activity →
OneLake/Lakehouse → Delta Table → SQL/Spark/Power BI

Module 3 · Lesson 3.11 — REST API → Lakehouse

3.11 REST API → Lakehouse

Learning Objectives

• Understand the REST API → Lakehouse ingestion architecture.
• Configure a REST API as a source in Fabric Data Factory.
• Work with JSON API responses.
• Configure authentication.
• Handle API parameters and pagination.
• Load API data into a Lakehouse Delta table.
• Implement incremental API ingestion.
• Monitor and troubleshoot API pipelines.

Overview

A common modern data engineering scenario is to extract data from a REST API and load it into a Microsoft Fabric Lakehouse.

REST API (/customers, /products, /orders)
        │ HTTP/JSON
Microsoft Fabric Data Factory (Copy Activity)
        │
OneLake → Lakehouse → Delta Table → Power BI

Business Scenario

GET /api/orders returns:

{
  "data": [
    { "OrderID": 1001, "CustomerID": 101, "Amount": 2500, "OrderDate": "2026-08-22" },
    { "OrderID": 1002, "CustomerID": 102, "Amount": 1200, "OrderDate": "2026-08-22" }
  ]
}

The requirement is to load these orders into a Fabric Lakehouse.

REST API → Lakehouse Architecture

REST API → GET → JSON Response → Fabric Data Factory →
Copy Activity → OneLake → Lakehouse → Orders Delta Table →
Power BI

Prerequisites

REST API endpoint, API documentation, authentication details, Fabric workspace, Fabric Lakehouse, Data Factory access, API permissions.

Base URL: https://api.example.com
Endpoint: /orders

Step 1: Create the Lakehouse

SalesLakehouse with a destination table Orders.

Step 2: Create a Data Pipeline

PL_RESTAPI_To_Lakehouse.

Step 3: Add Copy Activity

PL_RESTAPI_To_Lakehouse → Copy Activity

Step 4: Configure REST API Source

Base URL: https://api.example.com
Relative path: orders

Resulting endpoint: https://api.example.com/orders

Step 5: Configure HTTP Method

GET https://api.example.com/orders

Step 6: Configure Authentication

API Key: x-api-key: <API_KEY> Bearer Token: Authorization: Bearer <TOKEN> Basic Authentication: Username / Password OAuth 2.0:

Application → Authorization Server → Access Token → REST API

Never hard-code production secrets directly into pipeline expressions or notebooks.

Step 7: Configure Request Parameters

https://api.example.com/orders?country=India
https://api.example.com/orders?country=India&status=Completed

Common parameters: Date, Status, Customer ID, Page number, Page size, Search value.

Step 8: API Response

{
  "data": [
    { "OrderID": 1001, "CustomerID": 101, "Amount": 2500 },
    { "OrderID": 1002, "CustomerID": 102, "Amount": 1200 }
  ]
}

The pipeline must correctly handle the JSON structure before writing it to the destination.

Step 9: Configure Lakehouse Destination

Destination: Lakehouse
Lakehouse: SalesLakehouse
Table: Orders

Step 10: Configure Mapping

Step 11: Run the Pipeline

Validate → Run:

REST API → JSON → Copy Activity → Lakehouse → Orders Delta Table

Step 12: Verify the Data

SELECT * FROM Orders;
SELECT COUNT(*) AS TotalOrders FROM Orders;
SELECT SUM(Amount) AS TotalOrderValue FROM Orders;

Handling API Pagination

Request 1 → 1,000 records
Request 2 → 1,000 records
Request 3 → 1,000 records

Page Number: ?page=1, ?page=2, ?page=3 Offset: ?offset=0&limit=1000, ?offset=1000&limit=1000 Cursor: ?cursor=abc123 Next URL:

{
  "data": [...],
  "next": "https://api.example.com/orders?page=2"
}

Pagination Architecture

REST API → Page 1 → Copy Activity → Page 2 → Copy Activity →
Page 3 → ... → Lakehouse

For production implementations, the pipeline should continue requesting pages until the API indicates there is no more data.

Incremental REST API Loading

https://api.example.com/orders?updatedAfter=2026-08-21T00:00:00

Last Successful Timestamp → REST API →
updatedAfter = watermark → Changed Records → Copy Activity →
Staging → MERGE → Orders Delta Table → Update Watermark

Using MERGE for API Data

MERGE INTO Orders AS Target
USING OrderStage AS Source
ON Target.OrderID = Source.OrderID
WHEN MATCHED THEN
    UPDATE SET
        Target.CustomerID = Source.CustomerID,
        Target.Amount = Source.Amount,
        Target.OrderDate = Source.OrderDate
WHEN NOT MATCHED THEN
    INSERT (OrderID, CustomerID, Amount, OrderDate)
    VALUES (Source.OrderID, Source.CustomerID, Source.Amount, Source.OrderDate);

Existing OrderID → UPDATE; new OrderID → INSERT.

Handling API Rate Limits

Example: Maximum 1,000 requests/hour. If exceeded, the API may return HTTP 429 Too Many Requests.

Best Practices: respect the API's rate limit, use pagination efficiently, avoid unnecessary calls, use incremental extraction, implement retry/backoff where appropriate, monitor API failures.

Handling API Errors

JSON Transformation

API responses aren't always flat:

{
  "order": {
    "id": 1001,
    "customer": { "id": 101, "name": "John" },
    "amount": 2500
  }
}

Desired Lakehouse table: | OrderID | CustomerID | CustomerName | Amount | |---|---|---|---| | 1001 | 101 | John | 2500 |

Nested JSON may require additional transformation using Dataflow Gen2, Spark, or other appropriate Fabric processing.

API → Lakehouse Using a Staging Layer

REST API → Raw JSON Data → Bronze Layer → Transformation →
Silver Layer → Curated Delta Table → Power BI

This provides better auditing, reprocessing, troubleshooting, and data lineage.

Data Validation

Row Count:

SELECT COUNT(*) AS TotalOrders FROM Orders;

Null Check:

SELECT COUNT(*) AS InvalidOrders FROM Orders WHERE OrderID IS NULL;

Duplicate Check:

SELECT OrderID, COUNT(*) AS RecordCount
FROM Orders
GROUP BY OrderID
HAVING COUNT(*) > 1;

Business Validation:

SELECT COUNT(*) AS Orders, SUM(Amount) AS TotalAmount FROM Orders;

Monitoring

Monitor: API response status, pipeline status, Copy Activity status, records read/written, duration, throughput, failed requests, authentication failures, rate-limit errors.

Pipeline: PL_RESTAPI_To_Lakehouse
Status: Succeeded
API Records: 25,000
Rows Written: 25,000
Duration: 05:20

Common Problems

1. Authentication Failure (401) — check API key, access token, OAuth configuration, token expiration.
2. Forbidden (403) — credentials may be valid but lack permission for the requested resource.
3. Pagination Failure — only the first page is loaded. Solution: configure pagination correctly so all required pages are retrieved.
4. Duplicate Records — the same API records loaded multiple times. Solution: use incremental extraction and MERGE using a stable business key.
5. API Rate Limit (429) — reduce request frequency and implement retry/backoff behavior.
6. Nested JSON — use a transformation layer such as Dataflow Gen2 or Spark to flatten the response.

End-to-End Example

SaaS Application → REST API → Fabric Data Factory →
Copy Activity → Raw/Staging Data → MERGE →
Orders Delta Table → Power BI

The pipeline: reads the last successful watermark, calls the API using the watermark, retrieves new/changed orders, handles pagination, loads the response, merges records into the Delta table, validates the load, updates the watermark, and reports the pipeline status.

Hands-On Lab

Objective: Build a REST API → Lakehouse ingestion pipeline. - Source: REST API https://api.example.com/orders, Method: GET. - Destination: Lakehouse: SalesLakehouse, Table: Orders. - Pipeline: PL_RESTAPI_To_Lakehouse → Copy Activity → Orders Table

Tasks: 1. Create SalesLakehouse. 2. Create PL_RESTAPI_To_Lakehouse. 3. Add Copy Activity. 4. Configure the REST API connection. 5. Configure authentication. 6. Configure the API endpoint. 7. Configure query parameters. 8. Configure pagination if required. 9. Select Lakehouse as the destination. 10. Configure column mapping. 11. Run the pipeline. 12. Monitor the execution. 13. Query the Orders table. 14. Validate row counts and duplicates. 15. Implement incremental loading.

Interview Questions

1. How do you ingest REST API data into a Fabric Lakehouse? Use a Fabric Data Factory Pipeline with a Copy Activity, configure the REST API as the source and the Lakehouse as the destination.
2. Which HTTP method is commonly used for data extraction? GET.
3. Why is pagination important? APIs often restrict the number of records returned in a single response; pagination allows retrieving the complete dataset across multiple requests.
4. How can you implement incremental API ingestion? Use parameters such as updatedAfter, modifiedSince, or a cursor/watermark mechanism to retrieve only new or changed records.
5. How do you handle updates? Load changed records into staging and use Delta MERGE based on a stable business key.
6. How do you handle API rate limits? Respect the provider's limits, reduce unnecessary calls, use incremental extraction, and implement appropriate retry/backoff handling.
7. What should you do if the API returns nested JSON? Use a transformation layer such as Dataflow Gen2 or Spark to flatten and transform the nested structure before creating the final analytical table.

Best Practices

• Read and follow the API provider's documentation.
• Store API credentials securely.
• Use appropriate authentication.
• Implement pagination.
• Use incremental extraction for recurring loads.
• Handle 401, 403, 404, 429, and 5xx responses appropriately.
• Use stable business keys for upserts.
• Validate the API response before loading.
• Maintain a watermark for incremental processing.
• Keep raw API responses where audit/reprocessing requirements justify it.
• Monitor API usage and pipeline execution.

Key Takeaways

The REST API → Lakehouse pattern is one of the most important modern Fabric ingestion scenarios.

REST API → Authentication → GET Request → JSON Response →
Pagination → Copy Activity → Lakehouse → Delta Table →
SQL/Spark/Power BI

For production implementations, the most important concepts are authentication, pagination, incremental loading, rate-limit handling, error handling, and Delta MERGE/upsert logic.