Difficulty: Medium | Concepts: Fabric Data Pipeline, Copy Activity, REST API Pagination, Lakehouse Sink, Parameterization

Ingesting data from a REST API into a Fabric Lakehouse is one of the most common real-world tasks — and it's also a favorite Microsoft Fabric interview question because it tests whether you understand pagination, authentication, and incremental loading, not just "drag a Copy Activity onto the canvas."

Fabric Pipeline API Ingestion — Scenario

You're asked: "We need to pull order data every night from a third-party REST API that returns paginated JSON (100 records per page) and land it as Delta tables in a Lakehouse. How would you design this pipeline?"

Sample API Response

GET https://api.vendor.com/v1/orders?page=1&pageSize=100

{
  "data": [
    { "orderId": 1001, "amount": 250.00, "orderDate": "2026-06-30" },
    { "orderId": 1002, "amount": 89.50,  "orderDate": "2026-06-30" }
  ],
  "page": 1,
  "totalPages": 42
}

Pipeline Design

Component	Purpose
Web Activity / Lookup	Call page 1 to read `totalPages` from the response
ForEach Activity	Iterate from 1 to totalPages (or use pagination rules natively)
Copy Activity	REST source → Lakehouse Files/Tables sink, per page
Base parameter / query param	Pass `page` dynamically into the Copy Activity's REST connector
Notebook / Dataflow (optional)	Flatten JSON & write to a curated Delta table

Steps

Create a REST connection in the pipeline pointing to the base API URL, with the auth type (API key / OAuth2 / Bearer token stored in a Fabric connection or Key Vault).
Use the connector's built-in pagination rules (preferred) — set "Next page token" to read from the response body's page/totalPages fields, so Fabric automatically loops without you building a manual ForEach.
If the API doesn't support token-based pagination, use a Lookup Activity to fetch total pages first, then a ForEach looping 1..totalPages, with a Copy Activity inside using @item() as the page parameter.
Set the Copy Activity sink to the Lakehouse, landing raw JSON in the Files section (bronze layer) or directly as a Delta table if the schema is flat and stable.
Add a downstream Notebook to flatten nested JSON, cast types, and write to a clean Delta table (silver layer).
Wrap the whole thing in a Schedule trigger (nightly) and add a Fail activity + alert on the Copy Activity's failure path.

Visual Representation

Lookup Activity: get totalPages = 42 | ForEach (1 to 42) | Copy Activity: GET /orders?page=@item() | Sink: Lakehouse/Files/bronze/orders/page_@{item()}.json | Notebook: flatten JSON, dedupe, write Delta table "orders_silver"

Common Mistake to Avoid

Candidates often forget to handle rate limiting and throttling — hammering the API with 42 parallel ForEach iterations can trigger HTTP 429 errors. In an interview, always mention setting the ForEach's batch count (concurrency) to a safe value and adding a retry policy with exponential backoff on the Copy Activity.

Conclusion

The key talking points interviewers look for are: using native pagination rules over manual loops when possible, separating raw (bronze) landing from curated (silver) transformation, and always addressing authentication, throttling, and failure handling — not just the happy path.