Learn how to use Microsoft Fabric's Pipeline Copy Activity to ingest data from REST APIs, Azure Blob Storage, and Azure SQL databases into a Lakehouse. This hands-on lesson walks through real configuration steps, explains every key decision, and covers common pitfalls so your first ingestion pipeline actually works.
Picture this: your organization runs on data scattered across a dozen different systems. Sales figures live in an Azure SQL database. Product catalog updates come in nightly as CSV files dropped into Azure Blob Storage. And your marketing team consumes data from a third-party SaaS platform that only exposes a REST API. You need all of it in one place before your data scientists and analysts can do anything useful with it.
This is the fundamental ingestion problem that the Fabric Pipeline Copy Activity was built to solve. A Copy Activity is a single, configurable step inside a Fabric Data Pipeline that reads data from a source connector, optionally maps columns, and writes the result to a sink — in our case, a Lakehouse table or file. No code required for the basic case, and it scales from a few thousand rows to hundreds of millions without you worrying about infrastructure.
By the end of this lesson, you'll know exactly how to wire up three of the most common real-world source types — a public REST API, Azure Blob Storage, and an Azure SQL Database — and land that data cleanly into a Fabric Lakehouse. You'll understand not just which buttons to click, but why each configuration choice matters so you can adapt these patterns to whatever sources you encounter in the wild.
What you'll learn:
Before diving in, you should have:
Before touching any configuration screens, build this mental model: a Copy Activity is a managed data shuttle. On one end is a source connector — a configured description of where data lives and how to authenticate. On the other end is a sink connector — a configured description of where to put the data. In the middle, Fabric's execution engine handles serialization, parallelism, and error recovery automatically.
The source and sink configurations each live inside what Fabric calls a connection (previously called a linked service in Azure Data Factory, and you'll still see that terminology in the UI and documentation). A connection stores the endpoint URL, authentication method, and any secrets needed to reach a data system. Secrets are stored in Fabric's built-in secret store or referenced from Azure Key Vault — you never hard-code credentials into the pipeline itself.
Key insight
Connections are workspace-level objects. Once you create a connection for, say, your Azure SQL Database, every pipeline in the workspace can reuse it. This means you update credentials in one place if they rotate, not buried in dozens of pipelines.
Each Copy Activity also has an optional schema mapping step in the middle. If your source has columns named CustomerID, Cust_Name, and CreateDt, but your Lakehouse table expects customer_id, customer_name, and created_at, you can define that mapping in the Copy Activity without writing any transformation code. However, for truly complex transformations — multiple joins, aggregations, business logic — you'd hand the data off to a Spark notebook after landing it raw. That's the heart of the medallion architecture approach described in Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers.
Navigate to your Fabric workspace. Click the + New button in the upper-left area of the workspace, then select Data pipeline from the list of item types. Give it a meaningful name — something like ingest_sales_sources rather than Pipeline1. Fabric opens the pipeline canvas.
The canvas is a drag-and-drop design surface. Along the top you'll see a toolbar with activity types. Click Copy data from the toolbar (or click the Add pipeline activity button and select Copy data). A Copy Activity block appears on the canvas. Click it to select it, and you'll see a properties panel appear at the bottom of the screen with tabs: General, Source, Sink, Mapping, and Settings.
The General tab lets you name the activity itself (name it something like copy_orders_from_sql) and set a timeout. The source and sink configurations live in their respective tabs — that's where we'll spend most of our time.
Tip
You can have multiple Copy Activities on the same pipeline canvas and chain them together with success/failure arrows. A common pattern is three Copy Activities in parallel — one per source — all feeding into the same Lakehouse, then a Notebook activity downstream that runs after all three succeed.
REST APIs are everywhere. Payment processors, CRM platforms, weather data, financial market feeds — if a third-party system exposes data, it almost certainly does so over HTTP with a JSON response. The REST connector in Fabric handles this natively.
Click your Copy Activity on the canvas and select the Source tab in the properties panel. Click the + New button next to the "Connection" field. In the dialog that appears, search for REST and select the REST connector.
For our worked example, we'll use the Open Library Books API, which is completely public and requires no API key. In the Base URL field, enter:
https://openlibrary.org
For Authentication type, select Anonymous since this API requires no credentials. Click Create to save the connection.
Back in the Source tab, you'll see a field for Relative URL. This is the path appended to your base URL for each specific request. Enter:
/search.json?q=data+engineering&limit=100
This searches Open Library for books about data engineering and returns up to 100 results as JSON.
The REST connector returns the raw HTTP response body. You need to tell Fabric where the actual data records live within that JSON structure. In the Source tab, find Request method and leave it as GET. Then look for the Additional columns and Pagination rules options.
For the Open Library API, the records we want live at the path docs inside the response. In the Source tab, find the field labeled Additional URL parameters or look in the dataset configuration for a JSON path option — the exact label varies slightly with Fabric version, but you're looking for a way to specify the root path into the response body. Set it to $.docs (this is JSONPath notation, where $ means "the root of the document" and .docs means "the docs property").
Warning
Many production REST APIs require authentication. OAuth 2.0 bearer tokens are the most common pattern. When you create the connection, select Service Principal or Anonymous with a header, then add your Authorization: Bearer <token> in the Additional headers field in the Source tab. Never store tokens directly in the pipeline — store them as workspace secrets or Key Vault references.
Pagination is the other major concern with REST APIs. If an API returns 1,000 records per page and you need 50,000 records, you need the Copy Activity to automatically walk through pages. In the Source tab, expand Pagination rules. The most common patterns are:
For the Open Library example, pagination isn't critical at 100 records, so leave it disabled for now.
For this example, we'll write the raw JSON to the Lakehouse Files section first (a raw/bronze landing zone pattern) rather than directly to a structured table, because we don't know exactly what shape the API returns until we inspect it.
Click the Sink tab. Click + New next to Connection and select Microsoft Fabric Lakehouse. Choose your workspace and Lakehouse from the dropdowns. Under Root folder, select Files. Set the File path to something like bronze/open_library/books.json. For File format, select JSON.
Click Save and then Run at the top of the canvas to test the pipeline. After a moment you'll see a green checkmark on the activity if it succeeded. Navigate to your Lakehouse and browse to Files → bronze → open_library — you should find books.json waiting there.
Azure Blob Storage is probably the most common raw data landing zone in enterprise Azure architectures. CSV exports from legacy systems, Parquet files from upstream pipelines, JSON logs from applications — they often end up in a storage account before being processed further. Connecting Fabric pipelines to Blob Storage is straightforward and extremely fast.
Add a new Copy Activity to your canvas (or reuse the existing one for this exercise). In the Source tab, click + New next to Connection and search for Azure Blob Storage.
You have several authentication options here. For a storage account in the same Azure subscription as your Fabric capacity, Account key is the simplest option. Enter your storage account name and paste the access key from the Azure Portal (navigate to your storage account → Access keys → copy key1). For production workloads, use a Managed Identity or Service Principal instead of an account key — these don't expire and don't require you to manage secrets rotation.
After creating the connection, configure the File path in the Source tab. This has three parts: the container name, the folder path, and the file name (which can include wildcards). For example:
raw-datasales/orders/2024/*.csv (wildcard to pick up all CSV files in that folder)Set the File format to DelimitedText (CSV). Expand the format settings to specify whether the first row is a header (almost always yes), what the delimiter character is (comma by default), and what the encoding is (UTF-8 in most cases).
Tip
The wildcard *.csv in the file name field tells the Copy Activity to read all CSV files in the specified folder and concatenate them into a single output. This is incredibly useful for date-partitioned landing zones where nightly files pile up in the same folder. Just make sure all files share the same schema.
This time, instead of landing to Files, let's write directly to a Lakehouse Table — which means Fabric will create or append to a Delta table automatically.
In the Sink tab, create a new Lakehouse connection (or reuse the one you created earlier). Under Root folder, select Tables instead of Files. Set the Table name to raw_orders.
Under Table action, you have three choices:
For an initial load or full refresh, use Overwrite. For incremental daily loads, use Append. Choose Overwrite for now.
Click the Mapping tab. If the source file has a header row, Fabric can auto-detect the schema — click Import schemas and it will read a sample of the file and propose column mappings. Review them. If column names in the file have spaces or special characters (like Order Date or Customer#), Fabric will preserve them but you might want to clean them up here by typing new names in the Destination column column of the mapping grid.
Relational databases are still the workhorses of transactional systems. Whether you're reading from Azure SQL Database, SQL Server on-premises (via a gateway), or Azure SQL Managed Instance, the SQL connector in Fabric handles all of them.
This is where the Copy Activity really shines for the classic ELT (Extract, Load, Transform) pattern: pull data out of operational SQL databases as-is, land it into the Lakehouse, and then transform it with Spark or SQL — rather than trying to do everything in one step.
For more on how the Fabric Lakehouse compares to the Fabric Warehouse as a destination for SQL-sourced data, see Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload.
In the Source tab, click + New and search for Azure SQL Database. Enter your server name (the fully qualified domain name like myserver.database.windows.net), database name, and authentication details. SQL Authentication (username + password) works for quick setup; Service Principal or Managed Identity is preferred for production.
After creating the connection, you have two ways to define what data to pull:
Option A: Table — select a table or view name from a dropdown. The Copy Activity will do a full SELECT * from that object.
Option B: Query — write a custom SQL query. This is more flexible:
SELECT
OrderID,
CustomerID,
OrderDate,
TotalAmount,
Status
FROM dbo.Orders
WHERE OrderDate >= '2024-01-01'
AND Status IN ('Completed', 'Shipped')
Use a query when you want to filter rows (for incremental loads), select specific columns (to avoid copying sensitive fields), or join multiple tables before extraction. Keep the query simple — heavy computation should happen in the destination, not in the source database, which you don't want to overload.
Key insight
For incremental loads from SQL sources, a common pattern is to parameterize the WHERE clause with a watermark date. You store the last successful load timestamp in a metadata table or pipeline variable, pass it as a parameter to the Copy Activity's query, and update it after each successful run. This is covered in depth in Orchestrating Loads with Fabric Data Pipelines: Copy Activities, Parameters, and Schedules.
Configure the Sink tab just as we did for the Blob Storage source — Lakehouse Tables, with an appropriate table name like raw_orders_sql. For initial loads, Overwrite. For incremental loads, Append.
One important setting under the Sink's Advanced section: Copy method. The default is Bulk insert, which is fast for most cases. If you're inserting into a table that already has data and need to avoid duplicates, you can use Upsert — but that requires specifying a key column and the table must already exist with the right schema.
After clicking Run (or after scheduling the pipeline), Fabric opens the Output panel at the bottom of the canvas. Click the small glasses icon next to any activity run to see detailed metrics: rows read, rows written, data volume transferred, duration, and throughput in MB/s.
If the run fails, click the error icon to see the full error message. Common failure patterns and their causes are covered in the next section.
For pipelines in production, navigate to Monitor in the left sidebar of the Fabric workspace. This shows a history of all pipeline runs across the workspace, with the ability to drill into each activity, see logs, and trigger reruns.
Tip
After a successful Copy Activity that writes to a Lakehouse table, navigate to your Lakehouse and click the table name. Fabric shows a preview of the data. For files landed in the Files section, right-click a file and select Preview to see raw content. Always spot-check your first few ingestion runs before building downstream reports on the data.
Work through this complete exercise to cement the concepts:
Goal: Build a pipeline that reads product data from a public REST API and lands it as a table in your Lakehouse.
In your Fabric workspace, open your Lakehouse and confirm it has a Tables section and a Files section visible in the left panel.
Create a new Data Pipeline named exercise_rest_to_lakehouse.
Add a Copy Activity. Name the activity copy_books_data.
In the Source tab, create a new REST connection:
https://openlibrary.org/search.json?q=python+programming&limit=50In the Sink tab, connect to your Lakehouse. Set Root folder to Files, file path to bronze/books_raw.json, and format to JSON.
Click Run. Wait for the green checkmark.
Navigate to your Lakehouse → Files → bronze. Verify books_raw.json exists.
Now add a second Copy Activity on the same canvas. Name it copy_books_to_table. Connect its source to the same Lakehouse file (bronze/books_raw.json) using the Lakehouse connector with Files as the root folder. Set the sink to Tables with the table name books. Set the table action to Overwrite.
Draw a success arrow from copy_books_data to copy_books_to_table by hovering over the first activity until a small arrow appears on its edge, then dragging it to the second activity.
Click Run again. Both activities should run in sequence. Check your Lakehouse Tables section — you should see a books Delta table with queryable data.
Note
The two-step pattern in this exercise (REST → Files → Tables) deliberately mirrors the bronze/silver pattern. Raw files in the Files section give you a safety net — if the table write fails or the schema changes, your raw data is still there. You can always reprocess files into tables without re-calling the API.
"Connection refused" or timeout errors on REST sources This almost always means the base URL is wrong, or the API requires authentication that you haven't configured. Double-check that the base URL doesn't include the path (paths go in the Relative URL field). Test the full URL in a browser or Postman first before configuring it in Fabric.
Schema drift — the pipeline succeeds but the table has wrong or missing columns If your CSV files change structure between runs (a column is added upstream), the Copy Activity in Overwrite mode will recreate the table with the new schema, which can break downstream reports. In Append mode, new columns cause an error. Enable Schema drift handling in the Copy Activity's advanced settings to allow new columns to flow through automatically.
Blob Storage "AuthenticationFailed" errors This usually means the account key is wrong, or the key has been rotated in the Azure Portal. Check that you copied the full key (they're long base64 strings). For production, switch to Managed Identity to avoid this class of problem entirely.
SQL Source "Login failed" errors Make sure the SQL Server firewall allows connections from Azure services. In the Azure Portal, navigate to your SQL Server → Networking → check Allow Azure services and resources to access this server. Fabric pipelines run from Azure infrastructure, so this setting must be enabled.
Rows written is 0 but no error
This happens when your SQL query's WHERE clause filters out all rows (for example, a watermark date that's set too far in the future), or when the Blob Storage wildcard matches no files. The Copy Activity counts this as success because no error occurred. Always check the rows-written metric in the activity output, not just the green checkmark.
Warning
If you use Overwrite mode on a Lakehouse table that downstream Power BI reports are querying via Direct Lake mode, those reports will briefly see an empty table during the window between truncation and the new data being written. For business-critical datasets, consider writing to a staging table first and then using a Stored Procedure or Notebook activity to swap it with an atomic rename operation.
You now know how to use the Fabric Pipeline Copy Activity to ingest data from three of the most common source types: REST APIs, Azure Blob Storage, and Azure SQL Database. More importantly, you understand why each configuration choice matters — from the difference between base URL and relative URL in the REST connector, to why Managed Identity beats account keys for Blob Storage, to why you should keep SQL extraction queries simple and do transformation downstream.
The pattern you've built here — landing raw data into Lakehouse Files or Tables — is the foundation of every serious Fabric data architecture. The next logical step is transformation: taking that raw bronze data and refining it into clean, business-ready silver and gold layers. For that, explore Transforming Data with Spark Notebooks in Microsoft Fabric: PySpark for Lakehouse Tables, which picks up exactly where this lesson leaves off.
If you need richer transformation logic at the ingestion stage — joins, aggregations, data type coercions — without writing Spark code, Ingesting Data with Dataflow Gen2: Power Query Skills in Microsoft Fabric shows you how to use Power Query's visual interface as an alternative or complement to the Copy Activity.
And once your data is clean and in tables, you'll want to understand how Power BI can query it directly without import — that's Direct Lake mode, explained in Direct Lake Mode in Power BI: How It Works and When to Use It over Import and DirectQuery.