Part 5 — Accessing REST APIs in Microsoft Fabric: Choosing the Right Ingestion Approach

Introduction

In previous articles, we explored how to connect Microsoft Fabric to on-premises databases and local SFTP servers. Another common enterprise data source is REST APIs.

Modern organizations rely heavily on APIs to exchange data between systems. Common examples include:

• CRM systems
• ERP applications
• Weather services
• Payment platforms
• Social media platforms
• E-commerce applications
• Internal microservices

Unlike traditional databases and files, APIs often introduce additional complexity such as authentication, pagination, rate limiting, and nested JSON structures. As a result, selecting the right ingestion approach is important.

In this article, we will focus on the Bronze layer of the Medallion Architecture and explore different ways to ingest API data into Microsoft Fabric.

By the end of this guide, you will understand:

• How to connect Microsoft Fabric to REST APIs
• When to use Copy Activity
• When Dataflow Gen2 may be a better option
• When a Notebook provides the most flexibility
• How to land raw API data into a Lakehouse Bronze layer

Azure Functions and Logic Apps can also be used for API extraction and orchestration, particularly when advanced authentication, workflow automation, or event-driven processing is required. However, for most Microsoft Fabric implementations, Copy Activity, Dataflow Gen2, and Notebooks are the primary ingestion options.

Why APIs Are Different from Traditional Sources

When working with databases or files, data is usually available in a predictable structure. APIs, however, often introduce additional challenges:

• Authentication requirements
• Pagination
• Rate limiting
• Nested JSON structures
• Dynamic schemas
• Incremental extraction

Because of these challenges, API ingestion may require more than a simple Copy Activity.

Scope of This Article

Our overall architecture follows the Medallion Architecture pattern.

However, in this article, we will focus only on the Bronze layer and the extraction process.

REST API
    ↓
Microsoft Fabric
    ↓
Lakehouse Files (Bronze Layer)

The objective is to retrieve raw data from an API and store it in OneLake exactly as received.

Transformations, Silver processing, and Gold business models can be implemented later once the data has been successfully ingested.

Choosing the Right Ingestion Approach

When working with APIs in Microsoft Fabric, there is no single solution that works for every API.

A practical approach is:

Step 1 — Start with Copy Activity

Copy Activity should always be the first option you evaluate because it provides:

• Minimal configuration
• No coding required
• Easy maintenance
• Built-in monitoring
• Fast implementation

For many simple REST APIs, this may be all that is required.

Step 2 — Consider Dataflow Gen2

If Copy Activity cannot process the API response correctly, Dataflow Gen2 provides:

• Low-code development
• Visual transformations
• JSON parsing capabilities
• Data shaping functionality

Dataflow Gen2 offers a good balance between simplicity and flexibility.

Step 3 — Use a Notebook When More Flexibility Is Required

For more complex APIs, a Notebook provides complete control over:

• Authentication
• Pagination
• Retry logic
• Complex JSON processing
• Custom business logic
• Multiple API calls

This approach is commonly used in enterprise-scale implementations.

Demo Scenario

For this demonstration, we will use the DummyJSON Products API.

API Endpoint:

https://dummyjson.com/products

The API returns product information in JSON format, making it ideal for demonstrating API ingestion into Microsoft Fabric.

Our objective is simple:

DummyJSON API
      ↓
Microsoft Fabric
      ↓
Lakehouse Files (Bronze Layer)

Since this article focuses on source connectivity and ingestion, we will only discuss extracting and landing the data into the Bronze layer.

Bronze Layer

The Bronze layer stores raw API responses exactly as received.

Examples:

• products.json
• customers.json
• orders.json

Sample API Response:

{
  "products": [
    {
      "id": 1,
      "title": "Essence Mascara",
      "price": 9.99,
      "category": "beauty"
    }
  ]
}

Now that we understand the API structure, let’s build a simple ingestion pipeline to land the raw response into the Bronze layer.

Step 1 — Create a Microsoft Fabric Workspace

Create a Fabric workspace and ensure you have access to:

• Data Factory
• Lakehouse
• Notebook

Create a new Lakehouse called:

RetailLakehouse

Step 2 — Create the Landing Structure

Inside the Lakehouse Files area, create the following folder structure:

Files
    └── API

This folder will store the raw API responses.

Step 3 — Create an API Connection

Navigate to:

Manage Connections and Gateways

Create a new connection and select:

REST

Provide the required details:

• Base URL
• Authentication Type
• Credentials

Note the Common authentication methods.

Anonymous — Suitable for public APIs.

Step 4 — Create a Data Pipeline

Create a new pipeline: PL_API_INGESTION

Add a Copy Data activity.

Step 5 — Configure the REST Source

Select:

REST Source

Preview the response to validate connectivity.

Step 6 — Configure the Bronze Destination

Destination:

Lakehouse Files

Path:

Files/API/products.json

This stores the raw API payload exactly as received.

Benefits of storing raw data include:

• Auditing
• Replay capability
• Troubleshooting
• Schema evolution support

Step 7 — Execute the Pipeline

Run the pipeline.

Verify that:

products.json appears in the Bronze folder.

The raw response is now safely stored in OneLake

Why Copy Activity May Not Always Be Enough

In this demonstration, Copy Activity works perfectly because the DummyJSON API provides a simple JSON response.

However, not all APIs behave the same way.

During testing with APIs such as the Northwind API, we found that some responses may require additional handling depending on the API structure and ingestion requirements., we found that some API responses may require additional handling due to:

• Complex JSON structures
• Pagination
• Authentication requirements
• Dynamic endpoints
• Custom request parameters

In these scenarios, Dataflow Gen2 or Notebook-based ingestion may provide a more suitable solution.

Alternative Approach — Dataflow Gen2

Dataflow Gen2 provides a low-code alternative for API ingestion.

Architecture:

REST API
     ↓
Dataflow Gen2
     ↓
Lakehouse Files (Bronze Layer)

Dataflow Gen2 is useful when:

• Copy Activity cannot process the response correctly
• JSON requires basic flattening
• A low-code approach is preferred
• Business users need to maintain the solution

Demo — Dataflow Gen2

Dataflow Gen2 provides a low-code way to connect to an API, parse the JSON response, and load the result into a Lakehouse table.

In this demo, we will use the DummyJSON Products API and select only four simple columns:

• id
• title
• category
• price

Power Query :

let
Source = Json.Document(
Web.Contents("https://dummyjson.com/products")
),

Products = Source[products],

ProductsTable = Table.FromList(
    Products,
    Splitter.SplitByNothing(),
    null,
    null,
    ExtraValues.Error
),

ExpandedProducts = Table.ExpandRecordColumn(
    ProductsTable,
    "Column1",
    {"id", "title", "category", "price"},
    {"id", "title", "category", "price"}
),

ChangedType = Table.TransformColumnTypes(
    ExpandedProducts,
    {
        {"id", Int64.Type},
        {"title", type text},
        {"category", type text},
        {"price", type number}
    }
)

in
ChangedType

Steps:

1. Create a new Dataflow Gen2 in your Fabric workspace.
2. Select the API/Web source and use the DummyJSON API endpoint.
3. Open Advanced Editor.
4. Replace the generated query with the Power Query code above.
5. Select the Lakehouse destination.
6. Choose the target Lakehouse
7. Save and run the Dataflow.

After the Dataflow runs successfully, a new table will be created in the Lakehouse with the selected product data.

Dataflow Gen2 is typically used when API responses need to be parsed and shaped into a tabular structure. If the requirement is to store the raw JSON response exactly as received, Copy Activity or Notebook-based ingestion may be more appropriate.

Alternative Approach — Notebook-Based API Ingestion

For maximum flexibility, a Notebook can be used.

Architecture:

REST API
     ↓
Notebook
     ↓
Lakehouse Files (Bronze Layer)

Demo — Notebook-Based API Ingestion

In this demo, we will use a Fabric Notebook to call the DummyJSON Products API and save the raw JSON response into the Lakehouse Files area.

This approach is useful when you need more flexibility for authentication, pagination, retry logic, or custom API handling.

Steps

1. Create a new Notebook in your Fabric workspace.
2. Attach the default Lakehouse. In this demo, the Lakehouse is RetailLakehouse.
3. Add the code below to a notebook cell.
4. Run the cell.
5. Verify that a new timestamped JSON file is created in the Lakehouse Files area.

Notebook Code

import requests
import json
from datetime import datetime

# Use notebookutils.fs to ensure directory exists and to write into default Lakehouse Files
# In Fabric Spark notebooks, relative paths like "Files/..." point to the default Lakehouse.

api_url = "https://dummyjson.com/products"

response = requests.get(api_url)
response.raise_for_status()

data = response.json()

timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")

# Target folder under the default Lakehouse Files
target_folder = "Files/API"

# Ensure the API folder exists in the Lakehouse Files area
if not notebookutils.fs.exists(target_folder):
    notebookutils.fs.mkdirs(target_folder)

# Build the full path using the Lakehouse-relative convention
output_path = f"{target_folder}/products_{timestamp}.json"

# Write JSON content using notebookutils.fs.put (avoids local FS issues)
notebookutils.fs.put(output_path, json.dumps(data, indent=4), overwrite=True)

print(f"API data successfully saved to Lakehouse path: {output_path}")

Result

After the notebook runs successfully, a new JSON file will be created in the Lakehouse path:

Files/API/products_YYYYMMDD_HHMMSS.json

This stores the raw API response in the Bronze layer and keeps each execution as a separate timestamped file for audit and replay purposes. It will create anew file with timestamp .json

Many enterprise APIs introduce complexities that are difficult to handle through low-code tools alone.

Examples include:

OAuth Authentication

Generate Token
      ↓
Call API
      ↓
Refresh Token
      ↓
Continue Processing

Dynamic Pagination

Page 1
  ↓
Page 2
  ↓
Page 3
  ↓
Until No More Records

Parent-Child API Relationships

Get Customers
       ↓
Get Orders
       ↓
Get Payments

Notebooks provide complete control over these scenarios.

Choosing the Right Tool

Scaling to Multiple APIs

In real-world projects, we rarely ingest a single API endpoint.

Instead of creating separate pipelines for every API, a metadata-driven approach can be used.

A control table can store:

• API endpoint
• Authentication type
• Target file path
• Load frequency
• Target destination

The pipeline can read the metadata and dynamically process multiple APIs using a single framework.

This significantly reduces maintenance effort and improves scalability.

API Challenges to Consider

Pagination

Many APIs return limited records per request.

Examples:

?page=1
?page=2
?page=3

The ingestion process must iterate through pages until all records have been retrieved.

Rate Limiting

APIs often restrict the number of requests that can be made within a specific time period.

Implement:

• Retry logic
• Wait intervals
• Error handling

Authentication Expiry

Tokens may expire.

Implement token refresh mechanisms where required.

Schema Changes

API fields can change over time.

Storing raw responses in the Bronze layer helps maintain recoverability and simplifies troubleshooting.

Advanced Integration Options

Although this Blog focuses on Copy Activity, Dataflow Gen2, and Notebooks, Azure Functions and Logic Apps can also be used for API extraction and orchestration.

Azure Functions are useful when:

• Complex authentication is required
• Custom API processing is needed
• Event-driven ingestion is required
• Additional business logic must be applied before loading data

Logic Apps are useful when:

• Workflow orchestration is required
• Approval processes are involved
• External SaaS applications need to be integrated
• Automated notifications or actions are needed

For most Microsoft Fabric implementations, Copy Activity, Dataflow Gen2, and Notebooks remain the primary API ingestion options. Azure Functions and Logic Apps are typically used for more advanced integration scenarios.

Recommended Architecture

For enterprise-scale implementations:

REST API
      ↓
Copy Activity / Dataflow Gen2 / Notebook / Azure Function / Logic Apps
      ↓
Lakehouse Files (Bronze Layer)

Microsoft Fabric provides multiple options for ingesting API data, and the most appropriate choice depends on the API’s complexity, authentication requirements, transformation needs, and operational requirements.

In most Microsoft Fabric implementations, Copy Activity should be evaluated first because it is the simplest option. If additional parsing, shaping, or flexibility is required, Dataflow Gen2 or Notebooks are typically the next choices. Azure Functions and Logic Apps are generally reserved for advanced integration, workflow automation, or event-driven scenarios.

This architecture aligns with Microsoft Fabric best practices and provides a scalable foundation for future Silver and Gold transformations.

Tips

• Always start with the simplest ingestion option.
• Begin with Copy Activity whenever possible.
• Not every API can be handled through Copy Activity alone.
• Dataflow Gen2 provides an excellent low-code alternative.
• Notebooks offer maximum flexibility for enterprise API integrations.
• The right choice depends on the complexity of the API rather than the tool itself.

In our Northwind API example, Copy Activity was the first option evaluated. When that approach proved unsuitable for the API response, Dataflow Gen2 and Notebook-based approaches became more appropriate alternatives.

Conclusion

REST APIs are one of the most common data sources in modern data platforms. Microsoft Fabric provides multiple options for ingesting API data, including Copy Activity, Dataflow Gen2, and Notebooks.

For simple APIs, Copy Activity is often the quickest and easiest solution. However, some APIs may require additional handling for authentication, pagination, or complex response structures. In these scenarios, Dataflow Gen2 or Notebook-based ingestion can provide greater flexibility.

In this article, we focused on extracting data from an API and landing it into the Bronze layer of a Lakehouse. This provides a solid foundation for future Silver and Gold transformations within the Medallion Architecture.