2.5 — Ingest Data by Using Pipelines
DP-700 Domain 2 · Skill 2.5
What You Need to Know
The pipeline Copy activity is Fabric's workhorse for moving data. It connects to dozens of sources, copies at scale, converts file formats and needs no code. Pipelines wrap it in loops, conditions, retries and alerts.
Connecting to sources
| Where the source is | How the Copy activity reaches it |
|---|---|
| A public cloud endpoint (SaaS API, public Azure SQL, S3) | A cloud connection with stored credentials |
| On premises, or SQL Server on a VM in a private network you manage | An on-premises data gateway installed close to the source |
| An Azure resource behind a private endpoint or virtual network | A virtual network (VNet) data gateway |
| An ADLS Gen2 account behind a firewall | Trusted workspace access, using the workspace identity |
Managed private endpoints serve a different purpose. They let Spark (notebooks and Spark job definitions) reach private Azure resources. They aren't how a pipeline Copy activity connects.
The Copy activity in practice
- Copy assistant: a wizard that builds the source, the destination and the mapping, including copying many tables at once.
- Destinations: lakehouse Tables (written as Delta) or Files, warehouse tables, KQL databases, SQL databases and external stores.
- Format conversion without changing the data: CSV, JSON or Parquet in, Delta table or Parquet out, with column mapping and type conversion only. This is the right tool when raw data must land exactly as it arrived, apart from its file format.
- Into a warehouse: the Copy activity uses
COPY INTObehind the scenes. For sources it can't bulk-load directly, turn on staging. - Fault tolerance: skip incompatible rows and log them, instead of failing the whole run.
- Performance: Intelligent throughput optimization and the Degree of copy parallelism setting (see lesson 3.6).
Common pipeline patterns
- Metadata-driven copy: a Lookup reads a control table listing the source tables, then a ForEach runs a Copy activity for each one, with
@item().TableNamein the source query. - Parallel ingestion: activities with no dependencies between them run at the same time. A ForEach with Sequential off and a batch count runs its iterations in parallel.
- Transient failures: set Retry (for example 3) and a retry interval on the activity that talks to the flaky source, so that a timeout doesn't fail the whole run.
- Dependent stages: link the stages with On success when one must finish before the next starts, and put an Outlook or Teams activity on the On fail path.
Other ingestion paths
- Copy job: no-code, stateful incremental copy with no pipeline needed (lesson 2.1).
- Dataflow Gen2: ingest and transform with Power Query, with data destinations such as a lakehouse or warehouse.
- Notebook: call REST APIs or read files with Python when connectors don't fit.
- T-SQL
COPY INTOin a warehouse: bulk-load Parquet or CSV files from ADLS Gen2 or Blob storage. - Eventstreams: continuous events (lesson 2.7).
Key Concept: First decide how the pipeline reaches the source (connection, on-premises gateway or VNet gateway). Then the Copy activity moves the data, and the pipeline adds order, parallelism and retries.
Exam Traps
- Using a managed private endpoint to let a pipeline Copy activity reach a private database (that's for Spark)
- Choosing a dataflow when the data only needs to be copied and converted to another format
- Chaining independent copies one after another, which makes the run slower than it needs to be
- Expecting a pipeline to reach on-premises SQL Server without a gateway
Check your understanding
Practice Scenario
🔒 This practice scenario is part of Skillmarked Pro
The lessons stay free. Pro unlocks the other 16 DP-700 practice scenarios and the 50-question timed mock exam, plus everything for every other exam.
Launch Deal: first month €9.95, then €19.90/month. Enter code LAUNCH50 at checkout. Ends 1 December.
Cancel anytime. Or €199 a year, which saves 17%.
Not sure yet? Try 10 free mock exam questions.
Already Pro? Log in.
Sources
- How to copy data using copy activity, Microsoft Learn
- Use virtual network data gateway with pipeline in Fabric, Microsoft Learn
- Managed private endpoints for Fabric, Microsoft Learn
- Trusted workspace access, Microsoft Learn
- Ingest data into the Warehouse, Microsoft Learn