Which Fabric item copies on-premises SQL Server data to Warehouse1?

Design and implement loading patterns Ingest and transform batch data

Answer Correct answer: B — use a data pipeline with a Copy data activity to move Database1 through the on-premises data gateway into Warehouse1.

You have a Fabric workspace that contains a warehouse named Warehouse1. You have an on-premises Microsoft SQL Server database named Database1 that is accessed by using an on-premises data gateway. You need to copy data from Database1 to Warehouse1. Which item should you use?

a Dataflow Gen1 dataflow
a data pipeline Correct Answer
a KQL queryset
a notebook

Community Insight

This question tests which Fabric item can perform gateway-backed SQL Server-to-warehouse data movement, and the trap is assuming a notebook or Dataflow Gen1 is the natural copy tool.

Copying on-premises SQL Server data into a Fabric warehouse through an on-premises data gateway requires a Fabric item that supports gateway-backed data movement and a warehouse sink. This page establishes that a data pipeline with the Copy data activity is the correct choice, not Dataflow Gen1, a KQL queryset, or a notebook.

The most common wrong answer is a notebook because it can connect to SQL Server, but it is code-first and lacks the built-in gateway-backed Copy data activity; Dataflow Gen1 is also a trap because it is deprecated.

Community Discussion (18 comments)

alqazqzy.asseel 👍 1 Selected: B

Data pipeline using copy data activity, u can also ingest data using dataflow gen2 dataflow when you need want to transform, cleaning or shaping data while ingestion

Waloo2_11 👍 1 Selected: B

B.data pipeline, using copy data pipeline

juamd 👍 1 Selected: B

For the Fabric Warehouse the data is not external, here the copy activity acts as a bridge.

Josekonoor 👍 2 Selected: B

"Copy Data from SQL Server to a lakehouse" -> B. a data pipeline

Thatoyaka 👍 2 Selected: B

Why B. a data pipeline is correct: Data Pipelines in Microsoft Fabric are used for data movement and orchestration, much like Azure Data Factory. They support: On-premises SQL Server as a source via the on-premises data gateway Warehouse as a sink (destination) Pipelines are designed for copying data between sources and targets — perfect for your scenario.

nirvana183 👍 1 Selected: B

Data pipeline in Microsoft Fabric

ogbenisho 👍 1 Selected: B

Copy data is meant to ingest data from source systems to Landing zone (Bronze layer/ Raw Layer). Copy data is a capacity from Data Pipeline.

Wipag 👍 1 Selected: B

Purpose-built for orchestrating data movement. Supports Copy Data activity, which works with: On-prem SQL Server via gateway Fabric warehouse as a sink Provides scheduling, retries, logging, etc. Correct and most robust solution in Fabric for this task.

DarioReymago 👍 1 Selected: B

is correct

Danielnimi 👍 1 Selected: B

The pipeline in Fabric works same way as pipeline n Data Dactory. It supports the use of self-hosted Integrated Runtime (IR) which is used to establish connection between a private network (on-prem) and Azure cloud.

sesav4742 👍 3 Selected: B

A data pipeline, since it has the "Copy data" activity, the most efficient way to copy data into fabric when there are no transformations to be done

CMDev 👍 1 Selected: B

It says, you need to copy data, dont specify if we need to do some transformations. In this case, a data pipeline with an activity "copy data" is enough and the best choice in terms of efficency and minimal effort. Check this link: https://learn.microsoft.com/en-us/fabric/fundamentals/decision-guide-pipeline-dataflow-spark

arunmewada 👍 1 Selected: B

I attempted to use a data pipeline to load data directly from on-premises to the warehouse, but since the warehouse does not support external sources, I first loaded the data into a Lakehouse and then moved it to the warehouse.

arunmewada 👍 2

answer is A

robertlavigne 👍 18 Selected: B

B. Notebooks can’t go through a gateway, KQL isn’t appropriate for a sql db. Gen1 dataflows are deprecated. Data pipelines work through a datagateway and are fast

mmanrik 👍 2 Selected: B

A data pipeline, since it has the "Copy data" activity, the most efficient way to copy data into fabric when there are no transformations to be done (if there are a Dataflow Gen 2 might be better, depends on the amount of data to be copied).

QAZdbarhate12345678 👍 3 Selected: B

• In this case, a data pipeline can be used to transfer data from Database1 (the SQL Server database) to Warehouse1 (in the Fabric workspace).

Tuki93 👍 2 Selected: B

Dataflow Gen 1 is deprecated.

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

A data pipeline is the Fabric Data Factory item built for data movement and orchestration, and its Copy data activity can read from an on-premises SQL Server source through an on-premises data gateway and load the rows into Warehouse1. The gateway handles the network path to Database1, while the pipeline defines source, sink, mappings, and scheduling without custom code. Because this scenario asks only to copy data, not to transform it, the pipeline is the most direct and efficient fit. The data pipeline also aligns with the exam's expectation that Fabric Data Factory handles gateway-backed batch ingestion into a warehouse.

Why the Other Options Are Wrong

A Dataflow Gen1 dataflow is not the right choice because Gen1 dataflows are deprecated in Fabric, and even Dataflow Gen2 is better suited when transformations are needed rather than a straight copy. A KQL queryset is a query surface for KQL databases and Eventhouse data; it cannot serve as the ingestion mechanism from SQL Server into a warehouse. A notebook is code-first and can connect to many sources, but it does not provide the built-in gateway-backed Copy data activity and would require custom movement code. The exam expects the native Fabric data-movement item when the source is on-premises and the destination is a warehouse.

Community Comment Notes

The discussion overwhelmingly favors a data pipeline: robertlavigne noted that notebooks cannot go through a gateway, KQL is not appropriate for a SQL database, and "Gen1 dataflows are deprecated", while sesav4742 and mmanrik emphasized the Copy data activity as the efficient path for a no-transformation copy. Thatoyaka summarized that pipelines support on-premises SQL Server via the gateway and Warehouse as a sink. Tuki93 reinforced the elimination of Dataflow Gen1 by stating "Dataflow Gen 1 is deprecated." A minority comment from arunmewada chose A, but it supplied no reasoning and is outweighed by the deprecation and gateway-support arguments.

Official Reference

Exam Strategy

When the question mentions an on-premises source plus an on-premises data gateway, look for the Fabric item that natively supports gateway connections and a Copy data activity. Eliminate Dataflow Gen1 on deprecation grounds and KQL querysets because they query KQL databases, not SQL Server.

Frequently Asked Questions

Why not use a Dataflow Gen1 dataflow to copy Database1 into Warehouse1?

Dataflow Gen1 is deprecated in Fabric, so exam answers favor a data pipeline with Copy data for a direct gateway-backed load from SQL Server to a warehouse.

Why is a KQL queryset not correct for Database1?

A KQL queryset queries KQL databases in Eventhouse; it is not a general data-movement item and cannot copy SQL Server tables into Warehouse1.

More DP-700 FAQ →

Related Analysis

← Back to DP-700 Study Guide