Design and Implement Data Modeling in Unity Catalog DP-750 Practice Questions (Page 3)

Questions 11–15

1. 11foundation · easy

   What is the primary purpose of a temporal table?

   • ATo enforce referential integrity
   • BTo improve query performance
   • CTo reduce storage costs
   • DTo track changes over time and support point-in-time analysis
   Reveal Solution Discussion Select an answer first
2. 12application · medium

   A healthcare analytics team maintains a patient dimension table. When a patient's address changes, the team must preserve the historical address for compliance audits while also showing the current address in operational reports. The table currently has a surrogate key and a natural key. Which slowly changing dimension (SCD) type should they implement?

   • ASCD Type 1
   • BSCD Type 0
   • CSCD Type 3
   • DSCD Type 2
   Reveal Solution Discussion Select an answer first
4. 13expert · hard

   A company needs to ingest social media posts for sentiment analysis. The data arrives as a continuous stream, but the analysis only needs to run every 15 minutes. The team wants to minimize compute cost while ensuring no data loss. Which approach should you implement?

   • AUse a streaming ingestion pipeline that writes raw events to a Delta table, and run a separate batch job every 15 minutes to process the new data.
   • BUse Azure Data Factory to copy data every 15 minutes from the API to Delta.
   • CUse a batch job every 15 minutes that reads from the source API and writes to Delta.
   • DUse a streaming pipeline that both ingests and performs sentiment analysis continuously.
   Reveal Solution Discussion Select an answer first
5. 14expert · hard

   A large Delta table stores IoT sensor data. It is partitioned by `year` and `month`. Queries often filter by `sensor_id` and `reading_date`. The table also receives frequent updates to correct sensor readings. You need to balance query performance, update efficiency, and storage cost. Which design is most appropriate?

   • AKeep the `year`/`month` partitioning, enable liquid clustering on `sensor_id`, and enable deletion vectors.
   • BUse a Type 2 SCD to track all sensor reading corrections.
   • CRemove partitioning and apply Z-ordering on `sensor_id` and `reading_date`.
   • DRepartition by `sensor_id` and `reading_date`.
   Reveal Solution Discussion Select an answer first
6. 15expert · hard

   A customer dimension table in a data warehouse tracks customer attributes. The business requires: (1) the current address for operational reports, (2) the previous address for a 'last known address' field, and (3) full history for regulatory audits. The team wants to minimize storage and complexity. Which SCD design should they implement?

   • ASCD Type 2 with a current flag and effective dates
   • BSCD Type 1 with a previous_address column
   • CA temporal table with valid_from and valid_to columns
   • DSCD Type 3 with original and current address columns
   Reveal Solution Discussion Select an answer first