Databricks Databricks-Certified-Data-Engineer-Professional Exam : Databricks Certified Data Engineer Professional Exam

A data engineering team needs to create a SQL Alert that monitors data quality across multiple columns in their customer table. They want to trigger an alert when both the percentage of customers with missing email addresses exceeds 15% AND the percentage of customers with invalid phone number formats exceeds 10%. Which SQL query pattern is appropriate for implementing this multi-column alert condition?

• A. SELECT CASE WHEN email_null_pct >15 AND phone_invalid_pct> 10 THEN 1 ELSE 0 END FROM (SELECT (COUNT (CASE WHEN email IS NULL THEN 1 END) * 100.0 / COUNT (*)) as phone_invalid_pct FROM customers) metrics
• B. SELECT email, phone FROM customers WHERE email IS NULL AND phone NOT RLIKE ''[0-9-
  +()\\s]+$'
• C. SELECT email_null_pct, phone_invalid_pct FROM (SELECT (COUNT(CASE WHEN email IS NULL THEN 1 END) *
  100.0/COUNT (*)) as email_null_pct, (COUNT(CASE WHEN phone NOT RLIKE ''[0-9-+()\\s]+$' THEN 1 END)*
  100.0/COUNT (*)) as phone_invalid_pct FROM customers)
• D. SELECT COUNT (*) FROM customers WHERE email IS NULL OR phone_format_invalid = true

A data pipeline uses Structured Streaming to ingest data from kafka to Delta Lake. Data is being stored in a bronze table, and includes the Kafka_generated timesamp, key, and value. Three months after the pipeline is deployed the data engineering team has noticed some latency issued during certain times of the day.
A senior data engineer updates the Delta Table's schema and ingestion logic to include the current timestamp (as recoded by Apache Spark) as well the Kafka topic and partition. The team plans to use the additional metadata fields to diagnose the transient processing delays.
Which limitation will the team face while diagnosing this problem?

• A. New fields will not be computed for historic records.
• B. Spark cannot capture the topic partition fields from the kafka source.
• C. New fields cannot be added to a production Delta table.
• D. Updating the table schema will invalidate the Delta transaction log metadata.
• E. Updating the table schema requires a default value provided for each file added.

Two of the most common data locations on Databricks are the DBFS root storage and external object storage mounted with dbutils.fs.mount().
Which of the following statements is correct?

• A. Neither the DBFS root nor mounted storage can be accessed when using %sh in a Databricks notebook.
• B. By default, both the DBFS root and mounted data sources are only accessible to workspace administrators.
• C. The DBFS root stores files in ephemeral block volumes attached to the driver, while mounted directories will always persist saved data to external storage between sessions.
• D. DBFS is a file system protocol that allows users to interact with files stored in object storage using syntax and guarantees similar to Unix file systems.
• E. The DBFS root is the most secure location to store data, because mounted storage volumes must have full public read and write permissions.

Where in the Spark UI can one diagnose a performance problem induced by not leveraging predicate push-down?

• A. In the Delta Lake transaction log. by noting the column statistics
• B. In the Stage's Detail screen, in the Completed Stages table, by noting the size of data read from the Input column
• C. In the Query Detail screen, by interpreting the Physical Plan
• D. In the Storage Detail screen, by noting which RDDs are not stored on disk
• E. In the Executor's log file, by gripping for "predicate push-down"

A data engineer is designing a system to process batch patient encounter data stored in an S3 bucket, creating a Delta table (patient_encounters) with columns encounter_id, patient_id, encounter_date, diagnosis_code, and treatment_cost. The table is queried frequently by patient_id and encounter_date, requiring fast performance. Fine-grained access controls must be enforced. The engineer wants to minimize maintenance and boost performance. How should the data engineer create the patient_encounters table?

• A. Create a managed table in Unity Catalog. Configure Unity Catalog permissions for access controls, schedule jobs to run OPTIMIZE and VACUUM commands daily to achieve best performance.
• B. Create a managed table in Unity Catalog. Configure Unity Catalog permissions for access controls, and rely on predictive optimization to enhance query performance and simplify maintenance.
• C. Create a managed table in Hive Metastore. Configure Hive Metastore permissions for access controls, and rely on predictive optimization to enhance query performance and simplify maintenance.
• D. Create an external table in Unity Catalog, specifying an S3 location for the data files. Enable predictive optimization through table properties, and configure Unity Catalog permissions for access controls.