Ingest and Transform Batch Data DP-700 Practice Questions (Page 1)

Questions 1–5

1. 1expert · hard

   A batch pipeline ingests daily sales data. Occasionally, the same sale appears in two consecutive daily files due to a source system retry. The pipeline must ensure that each sale is counted only once in the daily aggregation. The data has a unique SaleID. What is the most efficient way to handle this?

   • AUse a T-SQL query with DISTINCT on the entire row.
   • BUse a KQL query to deduplicate the data.
   • CUse a Dataflow Gen2 to remove duplicates with a Remove Duplicates transformation.
   • DUse a PySpark notebook to dropDuplicates on SaleID before writing to the aggregation table.
   Reveal Solution Discussion Select an answer first
2. 2foundation · easy

   Which SQL function is used to calculate the total sum of a numeric column?

   • ASUM()
   • BCOUNT()
   • CMAX()
   • DAVG()
   Reveal Solution Discussion Select an answer first
4. 3application · medium

   A data warehouse contains a Sales table with a Region column that has some NULL values. A reporting requirement states that NULL regions should be displayed as 'Unknown' in reports. You need to write a T-SQL query that replaces NULLs with 'Unknown' and then counts sales per region. Which query should you use?

   • ASELECT COALESCE(Region, 'Unknown') AS Region, COUNT(*) AS SalesCount FROM Sales GROUP BY Region
   • BSELECT ISNULL(Region, 'Unknown') AS Region, COUNT(*) AS SalesCount FROM Sales GROUP BY ISNULL(Region, 'Unknown')
   • CSELECT Region, COUNT(Region) AS SalesCount FROM Sales GROUP BY Region
   • DSELECT Region, COUNT(*) AS SalesCount FROM Sales GROUP BY Region
   Reveal Solution Discussion Select an answer first
5. 4foundation · easy

   In a PySpark DataFrame, which method removes duplicate rows based on all columns?

   • Aunique()
   • Bdistinct()
   • Cdrop_duplicates()
   • DdropDuplicates()
   Reveal Solution Discussion Select an answer first
6. 5expert · hard

   A Fabric warehouse contains a table 'Orders' with columns: OrderID, CustomerID, OrderDate, Amount. The table has some rows with NULL Amount. Analysts need a report that shows the total amount per customer, but they want to see two numbers: the total including NULLs treated as 0, and the total excluding NULLs. The report must be created as a single T-SQL query. What should you do?

   • AUse SUM(Amount) and COUNT(Amount) in the same SELECT.
   • BUse AVG(Amount) and SUM(Amount) in the same SELECT.
   • CUse two separate queries and combine the results with UNION.
   • DUse SUM(ISNULL(Amount, 0)) and SUM(Amount) in the same SELECT with GROUP BY CustomerID.
   Reveal Solution Discussion Select an answer first