Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 33 lessons
  1. Start here: cloud and data basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. The three languages: SQL, PySpark and KQL side by side
    6. 6. Getting data in: the map of Fabric tools
  2. Store, ingest and transform batch data (exam area 2, 30-35%)

    1. 7. Choosing a data store and a transformation tool
    2. 8. Shortcuts and mirroring: use data without copying it
    3. 9. Ingest data with pipelines and Copy job
    4. 10. Transform with PySpark and SQL: join, denormalize, aggregate
    5. 11. Messy data: duplicates, missing values and late arrivals
    6. 12. Full and incremental loads, and loading a star schema
  3. Orchestrate and stream (exam areas 1 and 2)

    1. 13. Pipeline or notebook? Choosing how to orchestrate
    2. 14. Schedules and event-based triggers
    3. 15. Parameters and dynamic expressions
    4. 16. Streaming in Fabric: Eventstreams, Spark streaming and KQL
    5. 17. KQL and windowing functions for real-time data
  4. Set up and secure a Fabric solution (exam area 1, 30-35%)

    1. 18. Workspace settings: Spark, domains, OneLake and data workflows
    2. 19. Version control with Git and database projects
    3. 20. Deployment pipelines: development, test and production
    4. 21. Who can see what: workspace roles and item permissions
    5. 22. Fine-grained security: rows, columns, objects, files and masking
    6. 23. Governance: sensitivity labels and endorsement
  5. Monitor, fix and optimize (exam area 3, 30-35%)

    1. 24. Monitoring hub, alerts and Activator
    2. 25. Fixing errors in pipelines, dataflows and notebooks
    3. 26. Fixing errors in Eventhouse, Eventstream and T-SQL
    4. 27. Optimize lakehouse tables: V-Order, OPTIMIZE, VACUUM and partitions
    5. 28. Optimize Spark, pipelines and warehouses
    6. 29. Optimize eventstreams, eventhouses and queries
  6. Exam practice and final review

    1. 30. Exam day: question types, timing and how to think
    2. 31. Mock exam 1
    3. 32. Mock exam 2
    4. 33. Final review: every decision on one page
See the leaderboard

18 min

Ingest data with pipelines and Copy job

Copy data from a source into a lakehouse or warehouse with the Copy activity or a Copy job.

By the end of this lesson you can

  • Set up a Copy activity: connection, source, destination, mapping and table action
  • Choose between a Copy job and a pipeline Copy activity
  • Pick the right gateway to reach on-premises or private-network data
  • Copy a CSV file into a Lakehouse table

From earlier lessons · 1 of 2

A partner keeps raw files in Google Cloud Storage. You need them in your Lakehouse with no copy. What do you create?

Pick one answer.

From earlier lessons · 2 of 2

What do mirrored tables in Fabric let you do?

Pick one answer.

Every night, Northwind Rides' maintenance partner drops a CSV of station repairs into cloud storage. The ops team wants it in a Lakehouse table by 6 a.m. There's also an old depot inventory database on a server in the Northwind office, behind the firewall. Neither one fits a shortcut or mirroring. You need to copy.

[4]

Pipelines and the Copy activity

Exam depth: know it cold.

A pipeline is a Fabric item that runs a series of steps, called activities. The Copy activity is the step that moves data from a source to a destination.

Its settings sit on five tabs:

  1. General: name, timeout and retries.
  2. Source: where to read. You pick a connection, then a table, a query or a file.
  3. Destination: where to write, and how.
  4. Mapping: which source column goes to which destination column.
  5. Settings: performance, staging and fault tolerance.

The Copy activity changes data only lightly on the way, such as column types and names. You add other activities after it to transform the data.

[79][80]

A pipeline canvas with one Copy data activity. Below it are the General, Source, Destination, Mapping and Settings tabs, with the General tab open showing Name, Timeout and Retry.
Microsoft Learn, CC BY 4.0

[79]

Connection, source and destination

Exam depth: know it cold.

A connection stores how to reach a data store: its address and the credentials to sign in. You create it once in Manage connections and gateways, or from the Source tab. Then you reuse it.

On the Source tab you pick the connection and what to read. For a database, that's a table, a query or a stored procedure (a saved SQL routine). For files, it's a file path and a file format, such as delimited text for CSV.

For a Lakehouse destination, you choose a Root folder:

  • Tables writes a Delta table.
  • Files writes files, such as Parquet or CSV.

[79][80][81]

The Destination tab of a Copy activity with a Lakehouse connection. Root folder is set to Tables, there is a Table box, and Table action offers Append, Overwrite and Upsert. Advanced shows Apply V-Order and Enable partitions.
Microsoft Learn, CC BY 4.0

[80]

When the destination is a Lakehouse table, the Table action decides what happens to rows already there.

Table actionWhat it doesUse it when
AppendAdds new rows and keeps the old ones.Each run brings only new rows.
OverwriteReplaces the data and the schema.Each run brings the full data set.
UpsertUpdates rows that match on key columns and inserts the rest.Rows can change after they first arrive.

Overwrite keeps the older Delta versions, so you can still time travel back to them.

You are a data engineer at Northwind Rides. The partner's nightly CSV always holds the full list of open repairs, not just new ones. A Copy activity loads it into a Lakehouse table. After a week, the table holds seven copies of each repair. What should you change?

Pick one answer.

Mapping: lining up the columns

Exam depth: know it cold.

Mapping says which source column feeds which destination column, and with what data type.

On the Mapping tab, select Import schemas. Fabric matches the columns for you. You can then change names, drop columns or add a mapping.

Two limits to remember:

  • You can rename destination columns only when the copy creates a new table. An existing table keeps its column names.
  • For a Lakehouse table, you can change the destination type, such as int to string.

[79][80]

[84]

Copy job: copying without a pipeline

Exam depth: know it cold.

A Copy job is its own Fabric item. It copies data from source to destination with a guided setup and no pipeline to build.

  • Full copy copies everything on every run.
  • Incremental copy copies everything once, then only new or changed rows.

Incremental copy finds changes in one of two ways. A watermark is a column that grows with each change, such as a modified date or ROWVERSION. CDC (change data capture) reads the source's change log and also catches deletes. The Copy job remembers where the last run stopped, so you don't track it.

It writes with append by default, or merge, overwrite or SCD Type 2, which keeps history rows. It can run once or on several schedules.

[45]

Copy jobCopy activity in a pipeline
Build effortGuided setup, no pipeline.You build the pipeline yourself.
Incremental copyBuilt in, with watermark or CDC.You track the last watermark yourself.
Other steps around itNone.Any activity: notebooks, loops, conditions.
Parameterized, metadata-driven loadsNot its focus.Built for it.

Decision rule: just moving data, even incrementally, use a Copy job. Copying is one step in a bigger orchestrated flow, use a Copy activity in a pipeline.

[69][79]

You are a data engineer at Northwind Rides. You must copy the trips table from Azure SQL Database into a Lakehouse every hour. Only rows changed since the last run should move, using the table's ModifiedAt column. You want the least build effort. What should you use?

Pick one answer.

[45][69]

Same file, every night, set to Append. The table remembers everything. Twice. Picture: Three-panel comic strip, clean cartoon style.

Reaching data behind a firewall: gateways

Exam depth: know it cold.

Fabric runs in the cloud. It can't reach a server inside your office network on its own. A gateway is the bridge.

On-premises data gatewayVirtual network data gateway
What it isA Windows app you install on a machine in your network.A gateway Microsoft runs for you inside your Azure virtual network.
Use it forServers on premises, behind a firewall.Azure data sources locked to a private network or private endpoint.
Who maintains itYou: installs, monthly updates, monitoring.Microsoft. No hardware to manage.

An Azure virtual network is a private network you define in Azure. A private endpoint gives an Azure service an address only inside that network. The on-premises gateway only opens outbound connections, so no inbound firewall ports are needed.

You set up the gateway, then create a connection that uses it. Pipelines, Copy jobs, Dataflow Gen2 and mirroring can all use gateways.

[45][81][82][83]

[79]

You are a data engineer at Northwind Rides. An Azure SQL Database is reachable only through a private endpoint in the company's Azure virtual network. IT refuses to run and patch any new virtual machines. You need a Copy job to read it. What should you set up?

Pick one answer.

[83]

Try it in Fabric: copy a CSV into a Lakehouse table

A free Fabric trial is enough. Use the Lakehouse from the last lesson, or make a new one.

  1. Download the sample CSV at fabrictutorialdata.blob.core.windows.net/sampledata/Coffee/Dim_Products.csv.
  2. In your Lakehouse, select Get data > Upload files and upload it to Files.
  3. In the workspace, select New item, then Pipeline. Name it copy_products.
  4. On the Activities tab, select Copy data > Add to canvas.
  5. On Source, pick your Lakehouse connection. Set Root folder to Files and browse to the CSV.
  6. Set File format to DelimitedText. Open Settings and turn on First row as header.
  7. On Destination, pick the same Lakehouse. Set Root folder to Tables, select New and name the table products.
  8. Set Table action to Overwrite.
  9. On Mapping, select Import schemas and check the columns.
  10. On the Home tab, select Run, then Save and run. Watch the Output tab.

When it succeeds, refresh the Lakehouse. products appears under Tables.

[78][79][80][84][85][86]

Exam-style practice

You are a data engineer at Northwind Rides. The depot inventory database runs on SQL Server on a server in the office, behind the firewall. A pipeline must copy it into a Lakehouse every night. What do you need first?

Pick one answer.

[81][82]

You are a data engineer at Northwind Rides. Each night you must copy 40 tables, with the list of tables read from a control table. After each copy, a notebook cleans the data, and a failure must send an alert. What should you build?

Pick one answer.

[69]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 15

[45][69][79][80][82][83][84]

Worth keeping

  1. A Copy activity needs a connection, a source and a destination, then mapping and a table action.
  2. Append adds, Overwrite replaces, Upsert updates on key columns.
  3. A Copy job does full or incremental copies with no pipeline and tracks the watermark for you.
  4. Use a pipeline when the copy is one step in a bigger flow.
  5. On-premises servers need an on-premises data gateway; private Azure networks use a virtual network data gateway.

On the job

At Northwind Rides, the partner CSV and the depot database end up on two different paths.

The repair CSV is a plain nightly copy. A Copy job loads it into the raw_repairs table at 5 a.m. with overwrite, since each file is the full list.

The depot database is larger, and only a few rows change each day. An IT admin installs an on-premises data gateway on a small office server. A second Copy job uses it, copying incrementally on the LastUpdated column with merge.

Neither job transforms anything. Both land raw data in the Lakehouse, ready for the next step: shaping it with code.

[45][80][81]

The raw tables are in. But ops wants daily revenue per city zone, with station names instead of IDs. Next lesson you write the PySpark and SQL that join and sum it.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (12)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [4]Microsoft Learn: Study Guide for Exam DP-700
  2. [45]Microsoft Learn: What is Copy job in Data Factory for Microsoft Fabric?
  3. [69]Microsoft Learn: Microsoft Fabric decision guide: Choose a data movement strategy
  4. [78]Microsoft Learn: Quickstart: Get data into OneLake
  5. [79]Microsoft Learn: How to copy data using copy activity
  6. [80]Microsoft Learn: Configure lakehouse in a copy activity
  7. [81]Microsoft Learn: Access on-premises data sources in Data Factory for Microsoft Fabric
  8. [82]Microsoft Learn: What is an on-premises data gateway?
  9. [83]Microsoft Learn: What is a virtual network (VNet) data gateway?
  10. [84]Microsoft Learn: Delimited text format in Data Factory in Microsoft Fabric
  11. [85]Microsoft Learn: Quickstart: Create your first pipeline to copy data
  12. [86]Microsoft Learn: Incrementally load data from Data Warehouse to Lakehouse