BenchMark Quizzes by MDFT Pro

Copy Data from On-Premises Database to Warehouse

You work for MDFT Pro, a well-known training agency that maintains course catalog and instructor information in an on-premises database. Claire, a Data Integration Engineer at MDFT Pro, manages a Fabric workspace that contains a warehouse named CourseManagementWarehouse. The organization’s course catalog system runs on an on-premises Microsoft SQL Server database named CatalogDB that contains tables for course descriptions, learning objectives, prerequisites, instructor qualifications, and teaching schedules. This database is accessed through an on-premises data gateway that has been properly installed and configured for the Fabric workspace. Claire needs to copy data from multiple tables in CatalogDB into CourseManagementWarehouse on a nightly schedule to support business intelligence reports about course offerings, instructor utilization, and enrollment capacity planning. The solution should leverage Fabric’s native capabilities and provide reliable data movement from the on-premises environment to the cloud warehouse.

Which item should Claire use to copy data from CatalogDB to CourseManagementWarehouse?

Choose the correct answer from the options below.

Explanations for each answer:

A Dataflow Gen1 dataflow is incorrect. Dataflow Gen1 is being deprecated in Fabric and is not recommended for new implementations. While it might support connections through an on-premises data gateway, it's not the optimal solution for loading data into Fabric warehouses. Dataflow Gen1 lacks the full orchestration capabilities and modern features available in data pipelines.
A data pipeline is correct. A data pipeline with a Copy data activity is the correct solution for copying data from an on-premises SQL Server database to a Fabric warehouse. Data pipelines support on-premises data gateway connections (version 3000.214.2 or higher) and provide the Copy data assistant for configuring source connections, selecting tables, and specifying the warehouse destination. Note that external staging storage (Azure Blob Storage or ADLS Gen2) must be enabled when copying to a warehouse, as workspace staging is not supported.
A KQL queryset is incorrect. KQL querysets are used for writing and running KQL (Kusto Query Language) queries against KQL databases and Real-Time Analytics endpoints. They are query and analysis tools, not data movement or ETL tools. KQL querysets cannot copy data from on-premises SQL Server databases or ingest data into warehouses.
A notebook is incorrect. While you could write PySpark or Scala code in a notebook to copy data from SQL Server to a warehouse, this approach requires custom code development, error handling, and maintenance. A data pipeline provides a no-code/low-code solution with built-in monitoring, scheduling, and retry capabilities that is more efficient and maintainable than a custom notebook implementation.

Learn more about ingesting data into warehouse using pipelines:

Ingest Data into Warehouse Using Pipelines

Practice Exam

Copy Data from On-Premises Database to Warehouse

Discuss this question on social media: