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

Questions 6–10

1. 6expert · hard

   A data engineer is working with a table `sensor_readings` that has columns `sensor_id`, `reading_time`, `temperature`, `humidity`, and `battery_level`. Profiling shows that `temperature` has 5% NULLs, `humidity` has 30% NULLs, and `battery_level` has 2% NULLs. The downstream model requires all columns to be non-null. The engineer must choose a strategy. Which approach is most appropriate?

   • AFill all NULLs with 0.
   • BImpute all columns with the global mean.
   • CImpute `temperature` with the median, `humidity` with the median per sensor, and drop rows where `battery_level` is NULL.
   • DDrop all rows with any NULL in any column.
   Reveal Solution Discussion Select an answer first
2. 7expert · hard

   A data engineering team is loading a CSV file into a Unity Catalog table. The `phone_number` column contains NULL for customers who did not provide a phone number, and the `email` column contains NULL for customers who only provided a phone. The downstream marketing system requires either a phone or an email, but not both. The team wants to ensure data quality without dropping customers who have at least one contact method. What should they do?

   • AFill NULL email with a default like 'unknown@example.com'.
   • BDrop all rows where either phone_number or email is NULL.
   • CReplace NULL phone_number with a placeholder like '000-000-0000'.
   • DKeep NULLs and add a CHECK constraint that requires phone_number IS NOT NULL OR email IS NOT NULL.
   Reveal Solution Discussion Select an answer first
4. 8application · medium

   A company has a `products` table and a `discontinued_products` table, both with `product_id` and `product_name`. The team wants to create a list of products that are currently active, meaning they appear in `products` but not in `discontinued_products`. Which operation should you use?

   • ALEFT JOIN from products to discontinued_products and filter where discontinued_products.product_id IS NULL
   • BINTERSECT of both tables
   • CINNER JOIN on product_id
   • DUNION of both tables
   Reveal Solution Discussion Select an answer first
5. 9application · medium

   A healthcare analytics team is loading a CSV file into a Unity Catalog table. The `patient_age` column contains values like '45', 'N/A', and '>90'. The `visit_date` column contains dates in the format 'MM/dd/yyyy'. The team wants to run SQL aggregations on age and date. What should you do during the load?

   • ALoad the file as-is and cast columns to INT and DATE during queries.
   • BDefine patient_age as STRING and visit_date as STRING to preserve all values.
   • CUse a view that casts the columns on the fly and load the raw data into a table.
   • DClean the data first: replace 'N/A' with NULL, map '>90' to 90, and parse visit_date to DATE, then load with appropriate types.
   Reveal Solution Discussion Select an answer first
6. 10application · medium

   A retail company ingests daily sales transaction files into a Unity Catalog table. The data profiling step reveals that the `discount_amount` column has 15% missing values, but only for transactions where `payment_type = 'gift_card'`. The column is defined as DECIMAL(10,2). The analytics team wants to compute average discount per payment type without skewing results. What should you do?

   • AKeep nulls and compute averages with a filter that excludes null discount_amount only when aggregating by payment type.
   • BDrop all rows where discount_amount is null before loading.
   • CReplace missing discount_amount with 0.00 for gift_card transactions before loading.
   • DFill missing discount_amount with the overall median discount across all payment types.
   Reveal Solution Discussion Select an answer first