Study with Certified-Data-Engineer-Professional Actual Test Questions

A Structured Streaming job deployed to production has been resulting in higher than expected cloud storage costs. At present, during normal execution, each microbatch of data is processed in less than 3s; at least 12 times per minute, a microbatch is processed that contains 0 records. The streaming write was configured using the default trigger settings. The production job is currently scheduled alongside many other Databricks jobs in a workspace with instance pools provisioned to reduce start-up time for jobs with batch execution.
Holding all other variables constant and assuming records need to be processed in less than 10 minutes, which adjustment will meet the requirement?

• A. Increase the number of shuffle partitions to maximize parallelism, since the trigger interval cannot be modified without modifying the checkpoint directory.
• B. Set the trigger interval to 500 milliseconds; setting a small but non-zero trigger interval ensures that the source is not queried too frequently.
• C. Use the trigger once option and configure a Databricks job to execute the query every 10 minutes; this approach minimizes costs for both compute and storage.
• D. Set the trigger interval to 3 seconds; the default trigger interval is consuming too many records per batch, resulting in spill to disk that can increase volume costs.
• E. Set the trigger interval to 10 minutes; each batch calls APIs in the source storage account, so decreasing trigger frequency to maximum allowable threshold should minimize this cost.

A large company seeks to implement a near real-time solution involving hundreds of pipelines with parallel updates of many tables with extremely high volume and high velocity data.
Which of the following solutions would you implement to achieve this requirement?

• A. Use Databricks High Concurrency clusters, which leverage optimized cloud storage connections to maximize data throughput.
• B. Store all tables in a single database to ensure that the Databricks Catalyst Metastore can load balance overall throughput.
• C. Partition ingestion tables by a small time duration to allow for many data files to be written in parallel.
• D. Configure Databricks to save all data to attached SSD volumes instead of object storage, increasing file I/O significantly.
• E. Isolate Delta Lake tables in their own storage containers to avoid API limits imposed by cloud vendors.

A data engineer has created a new cluster using shared access mode with default configurations.
The data engineer needs to allow the development team access to view the driver logs if needed.
What are the minimal cluster permissions that allow the development team to accomplish this?

• A. CAN MANAGE
• B. CAN VIEW
• C. CAN ATTACH TO
• D. CAN RESTART

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 email, phone FROM customers WHERE email IS NULL AND phone NOT RLIKE 'ˆ[0-9-
  +()\\s]+$'
• B. 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
• 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

The data science team has requested assistance in accelerating queries on free form text from user reviews. The data is currently stored in Parquet with the below schema:
item_id INT, user_id INT, review_id INT, rating FLOAT, review STRING
The review column contains the full text of the review left by the user. Specifically, the data science team is looking to identify if any of 30 key words exist in this field.
A junior data engineer suggests converting this data to Delta Lake will improve query performance.
Which response to the junior data engineer's suggestion is correct?

• A. Delta Lake statistics are not optimized for free text fields with high cardinality.
• B. Text data cannot be stored with Delta Lake.
• C. ZORDER ON review will need to be run to see performance gains.
• D. Delta Lake statistics are only collected on the first 4 columns in a table.
• E. The Delta log creates a term matrix for free text fields to support selective filtering.