Design and Implement Loading Patterns DP-700 Practice Questions (Page 5)

Questions 21–25

1. 21foundation · easy

   Which Fabric tool is designed to efficiently load large volumes of data into a warehouse using T-SQL commands?

   • ACOPY INTO
   • BINSERT INTO ... SELECT
   • CMERGE
   • DUPDATE
   Reveal Solution Discussion Select an answer first
2. 22application · medium

   A retail chain needs to load a full snapshot of customer data from a legacy system into a Fabric Lakehouse. The source is a large CSV file that is exported nightly. The data contains duplicate customer records and inconsistent formatting. What should you do to load the data efficiently while ensuring it is ready for analytics?

   • AUse a Dataflow Gen2 to load the CSV and rely on Power Query to remove duplicates during refresh.
   • BLoad the CSV into a SQL table and use T-SQL to clean the data after loading.
   • CUse a Spark notebook to read the CSV, deduplicate and clean the data, and write it to a Delta table.
   • DLoad the CSV directly into the Lakehouse table and handle duplicates and formatting issues in the analytics layer.
   Reveal Solution Discussion Select an answer first
4. 23application · medium

   A healthcare analytics team is building a star schema in Fabric. They need to load data from multiple source tables into dimension and fact tables. The dimension tables must have surrogate keys, and the fact table must reference those keys. What should you do to prepare the data for loading?

   • ACreate a staging table that cleans and transforms the data, then load dimensions with surrogate keys and finally load the fact table using the generated keys.
   • BLoad the fact table first and then generate dimension keys from the fact table data.
   • CUse a single table that contains both dimension and fact attributes to avoid joins.
   • DLoad the source tables directly into the fact table and use natural keys from the source.
   Reveal Solution Discussion Select an answer first
5. 24application · medium

   A marketing team receives a monthly snapshot of customer data from a CRM. The snapshot contains all current customers. The team wants to load this into a Fabric lakehouse to replace the previous month's data. The load must be efficient and ensure that the table reflects the latest snapshot. What should the engineer do?

   • AUse a merge operation with customer ID to upsert all rows.
   • BTruncate the destination table and load the full snapshot.
   • CLoad the snapshot into a new table and switch the table name.
   • DAppend the snapshot to the existing table without removing old rows.
   Reveal Solution Discussion Select an answer first
6. 25application · medium

   A company loads order data from an ERP system into a Fabric lakehouse every hour. The ERP provides a change feed with new and updated orders. The team needs to maintain a current view of orders in a delta table. The engineer must implement an incremental load that handles updates efficiently. What should the engineer do?

   • ATruncate the table and reload all orders from the ERP each hour.
   • BUse a merge operation on the order ID to upsert new and changed rows.
   • CAppend all rows from the change feed to the delta table.
   • DLoad the change feed into a staging table and then replace the main table with the staging table.
   Reveal Solution Discussion Select an answer first