Cleanse, Transform, and Load Data into Unity Catalog DP-750 Practice Questions (Page 1)

Questions 1–5

1. 1expert · hard

   A company has a `sales` table with columns `order_id`, `product_id`, `quantity`, `price`. Due to a system glitch, some rows are exact duplicates (all columns identical). The team wants to remove duplicates, but they also need to keep a record of how many duplicates were removed for auditing. Which approach should they use?

   • AUse `MERGE` with a self-join to remove duplicates.
   • BUse `GROUP BY` on all columns and keep the first row.
   • CUse `ROW_NUMBER()` with a partition on all columns to identify duplicates and delete all but one, logging the count.
   • DUse `SELECT DISTINCT *` to create a new table and overwrite the original.
   Reveal Solution Discussion Select an answer first
2. 2application · medium

   A company receives weekly sales files from two regions. Each file has the same schema: `transaction_id`, `product_id`, `quantity`, `sale_date`. The team needs to create a single Unity Catalog table containing all transactions from both regions. The `transaction_id` is unique only within each region, so the same ID can appear in both files. What should you do?

   • AUse UNION ALL to combine the two files, keeping all rows.
   • BUse EXCEPT to load only transactions that appear in one file but not the other.
   • CUse INTERSECT to find common transactions and load only those.
   • DUse UNION to combine the two files, keeping all rows.
   Reveal Solution Discussion Select an answer first
4. 3application · medium

   A marketing team has a table `campaign_metrics(campaign_id, impressions, clicks, conversions)` in Unity Catalog. They need to create a long-format table with columns `campaign_id`, `metric_name`, and `metric_value` to feed a visualization tool that expects one row per metric. Which operation should you use?

   • AUse PIVOT on campaign_id to create one column per campaign.
   • BUse GROUP BY campaign_id with SUM on each metric.
   • CUse UNION to stack the three metric columns into one column.
   • DUse UNPIVOT on the metric columns to convert them into rows.
   Reveal Solution Discussion Select an answer first
5. 4foundation · medium

   Which operation groups rows by a key and then applies an aggregation function to each group?

   • Ajoin().withColumn()
   • Bdistinct().orderBy()
   • Cfilter().select()
   • DgroupBy().agg()
   Reveal Solution Discussion Select an answer first
6. 5foundation · medium

   Which operation computes a single summary value (such as sum, count, or average) from a set of rows?

   • AwithColumn()
   • Bjoin()
   • Cunion()
   • Dagg()
   Reveal Solution Discussion Select an answer first