Transform Data DP-600 Practice Questions (Page 10)

Questions 46–50

1. 46application · medium

   A Fabric pipeline imports a CSV file where the price column contains the string 'N/A' for missing values. The target table expects price as a decimal. The team needs to load the data and store NULL for 'N/A' values. What should they do?

   • ACast the column directly to decimal without any replacement.
   • BLoad the column as a string and leave it as 'N/A'.
   • CUse a derived column transformation that replaces 'N/A' with NULL and casts the column to decimal.
   • DFilter out rows where price is 'N/A'.
   Reveal Solution Discussion Select an answer first
2. 47application · medium

   A Fabric lakehouse contains a Sales table with one row per order line and a Returns table with one row per returned item. Some order lines have multiple returns, and some have none. You need to create a dataset that includes every order line with its total returned quantity, and you must not lose order lines that have no returns. Which approach should you use?

   • AUse a FULL OUTER JOIN between Sales and Returns on OrderLineID, then filter to rows where Sales.OrderLineID is not null.
   • BPerform a LEFT OUTER JOIN between Sales and an aggregated Returns table on OrderLineID, then use the returned quantity column in the dataset.
   • CAppend Sales and Returns using a UNION, then group by OrderLineID.
   • DPerform an INNER JOIN between Sales and Returns on OrderLineID, then aggregate the result by OrderLineID.
   Reveal Solution Discussion Select an answer first
4. 48application · medium

   You are designing a star schema in a Fabric warehouse for a retail analytics solution. The source has a wide Orders table that includes CustomerName, CustomerCity, ProductName, ProductCategory, and OrderDate. You need to reduce redundancy and enable consistent filtering across reports. Which design should you implement?

   • ACreate separate dimension tables for Customer, Product, and Date, and a fact table containing only foreign keys and measures.
   • BKeep the wide Orders table as the fact table and add calculated columns for CustomerName and ProductName.
   • CCreate a fact table with OrderID, CustomerName, ProductName, and OrderDate, and use views to expose measures.
   • DCreate one large dimension table that contains all descriptive columns from Orders and a fact table with only OrderID.
   Reveal Solution Discussion Select an answer first
5. 49application · medium

   You have a Fabric lakehouse with a Sales fact table. You need to enrich the model by adding a new table that contains sales territory information, including TerritoryID, TerritoryName, and Region. The Sales table already has a TerritoryID column. What should you do to make the territory attributes available in reports?

   • ALoad the territory table into the lakehouse and create a relationship between Sales.TerritoryID and Territory.TerritoryID in the semantic model.
   • BCreate a stored procedure that copies territory data into the Sales table.
   • CCreate a view that unions Sales and Territory.
   • DAdd the territory columns directly to the Sales table using a join in a Dataflow.
   Reveal Solution Discussion Select an answer first
6. 50application · medium

   A Fabric warehouse stores daily store sales. The sales_amount column contains NULL for transactions that were refunded. The reporting team needs a query that returns total sales per store, but they want refunded transactions to be treated as zero in the total. What should they do?

   • AUse SUM(COALESCE(sales_amount, 0)) in the query.
   • BUse SUM(sales_amount) and filter out NULL rows.
   • CUse COUNT(sales_amount) as the total.
   • DUse AVG(sales_amount) instead of SUM.
   Reveal Solution Discussion Select an answer first