Microsoft Certified: Fabric Analytics Engineer AssociatePrepare and transform data (20-25%)Medium
A company is migrating its on-premises SQL Server database to Microsoft Fabric. They have a large fact table, `SalesData`, containing over 500 GB of historical sales records, which needs to be ingested into a Lakehouse table. This ingestion needs to happen as a one-time full load, followed by daily incremental loads for new and updated records. The solution should be scalable and reliable. Which Microsoft Fabric tool is most appropriate for this scenario?
- ADataflows Gen2 with a SQL connector
- BSpark notebook using `read.jdbc`
- CReal-time Analytics with Eventstream
- DData Pipelines with a Copy Data activity
Show answer & explanationAnswer & explanation
Correct answer: D. Data Pipelines with a Copy Data activity
Data Pipelines with the Copy Data activity are designed for scalable data movement, including large initial loads and incremental updates, making them ideal for migrating large databases to a Lakehouse.
Why the other options are wrong
- A. While Dataflows Gen2 can connect to SQL Server, it's generally less optimized for very large initial loads and complex incremental strategies compared to Data Pipelines.
- B. A Spark notebook could perform this, but Data Pipelines offer a more managed and often simpler low-code approach for routine data movement tasks, especially with incremental loading features.
- C. Real-time Analytics with Eventstream is for streaming data ingestion, not batch processing of historical data from a SQL Server database.
Data Pipelines (Copy Data)
A Microsoft Fabric component for orchestrating and automating data movement and transformation activities.
- Supports large-scale data transfer.
- Offers various data source and sink connectors.
- Can be scheduled and monitored.
Memory trick: A pipeline moves mountains of data, steadily and surely.