Launch Deal · 50% off 1st month ·

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 INTO behind 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

  1. 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().TableName in the source query.
  2. 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.
  3. 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.
  4. 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 INTO in 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.

See plans

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.