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

Questions 16–20

1. 16application · medium

   A company has a Unity Catalog table `web_events` that stores clickstream data. A new batch of events arrives in a temporary table `new_events` with the same schema. The `event_id` column is unique across both tables. The data team needs to add the new events to the existing table without modifying existing rows. What should you do?

   • AUse `INSERT INTO web_events SELECT * FROM new_events`.
   • BUse `UPDATE web_events SET event_id = (SELECT event_id FROM new_events)`.
   • CUse `INSERT OVERWRITE web_events SELECT * FROM new_events`.
   • DUse `MERGE INTO web_events USING new_events ON web_events.event_id = new_events.event_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *`.
   Reveal Solution Discussion Select an answer first
2. 17application · medium

   A company has two tables in Unity Catalog: `customers(customer_id, name, email)` and `orders(order_id, customer_id, order_date, amount)`. Some customers have no orders, and some orders reference customers that were deleted. The analytics team wants a report that includes every customer, even those without orders, but only for customers that still exist. Which join type should you use?

   • ALEFT JOIN from customers to orders on customer_id
   • BRIGHT JOIN from customers to orders on customer_id
   • CFULL OUTER JOIN on customer_id
   • DINNER JOIN on customer_id
   Reveal Solution Discussion Select an answer first
4. 18application · medium

   A data analyst has just loaded a new table `web_traffic` into Unity Catalog. Before building reports, they want to understand the distribution of `page_views` and identify any outliers or missing values. Which action should they take first?

   • ACreate a chart in a notebook without any aggregation.
   • BRun `DESCRIBE TABLE web_traffic` to see column types.
   • CRun `SELECT * FROM web_traffic LIMIT 10` to eyeball the data.
   • DUse `SUMMARY` or `DESCRIBE` statistics on the table to get min, max, mean, and null counts.
   Reveal Solution Discussion Select an answer first
5. 19foundation · medium

   Which transformation converts a normalized table (e.g., separate rows per product in an order) into a wide format with one row per order and multiple product columns?

   • Apivot()
   • Bunpivot()
   • Cfilter()
   • DgroupBy()
   Reveal Solution Discussion Select an answer first
6. 20application · medium

   A sales team has a table `sales(region, month, revenue)` in Unity Catalog. They want to create a report that shows each region as a row and each month as a column (e.g., Jan, Feb, Mar) with revenue values. Which operation should you use?

   • AUse JOIN on region to combine months side by side.
   • BUse UNION to combine monthly revenue values into one column.
   • CGROUP BY region and month with SUM(revenue), then pivot on month.
   • DUse FILTER to select only one month at a time.
   Reveal Solution Discussion Select an answer first