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

A data team is working to optimize an existing large, fast-growing table 'orders' with high cardinality columns, which experiences significant data skew and requires frequent concurrent writes. The team notice that the columns 'user_id', 'event_timestamp' and 'product_id' are heavily used in analytical queries and filters, although those keys may be subject to change in the future due to different business requirements. Which partitioning strategy should the team choose to optimize the table for immediate data skipping, incremental management over time, and flexibility?

• A. Cluster the table with: ALTER TABLE orders CLUSTER BY user_id, product_id, event_timestamp
• B. Use z-order after partitiing the table: OPTIMIZE orders ZORDER BY (user_id, product_id) WHERE event_timestamp = current date () - 1 DAY
• C. Partition the table with: ALTER TABLE orders PARTITION BY user_id, product_id, event_timestamp
• D. Z-order the table with OPTIMIZE orders ZORDER BY (user_id, product_id, event_timestamp)

A Structured Streaming job deployed to production has been experiencing delays during peak hours of the day. At present, during normal execution, each microbatch of data is processed in less than 3 seconds. During peak hours of the day, execution time for each microbatch becomes very inconsistent, sometimes exceeding 30 seconds. The streaming write is currently configured with a trigger interval of 10 seconds.
Holding all other variables constant and assuming records need to be processed in less than 10 seconds, which adjustment will meet the requirement?

• A. Decrease the trigger interval to 5 seconds; triggering batches more frequently allows idle executors to begin processing the next batch while longer running tasks from previous batches finish.
• B. Increase the trigger interval to 30 seconds; setting the trigger interval near the maximum execution time observed for each batch is always best practice to ensure no records are dropped.
• C. Use the trigger once option and configure a Databricks job to execute the query every 10 seconds; this ensures all backlogged records are processed with each batch.
• D. The trigger interval cannot be modified without modifying the checkpoint directory; to maintain the current stream state, increase the number of shuffle partitions to maximize parallelism.
• E. Decrease the trigger interval to 5 seconds; triggering batches more frequently may prevent records from backing up and large batches from causing spill.

The view updates represents an incremental batch of all newly ingested data to be inserted or updated in the customers table.
The following logic is used to process these records.

Which statement describes this implementation?

• A. The customers table is implemented as a Type 1 table; old values are overwritten by new values and no history is maintained.
• B. The customers table is implemented as a Type 0 table; all writes are append only with no changes to existing values.
• C. The customers table is implemented as a Type 3 table; old values are maintained as a new column alongside the current value.
• D. The customers table is implemented as a Type 2 table; old values are maintained but marked as no longer current and new values are inserted.
• E. The customers table is implemented as a Type 2 table; old values are overwritten and new customers are appended.

A user wants to use DLT expectations to validate that a derived table report contains all records from the source, included in the table validation_copy.
The user attempts and fails to accomplish this by adding an expectation to the report table definition.

Which approach would allow using DLT expectations to validate all expected records are present in this table?

• A. Define a SQL UDF that performs a left outer join on two tables, and check if this returns null values for report key values in a DLT expectation for the report table.
• B. Define a function that performs a left outer join on validation_copy and report and report, and check against the result in a DLT expectation for the report table
• C. Define a temporary table that perform a left outer join on validation_copy and report, and define an expectation that no report key values are null
• D. Define a view that performs a left outer join on validation_copy and report, and reference this view in DLT expectations for the report table

Given the following PySpark code snippet in a Databricks notebook:
filtered_df = spark.read.format("delta").load("/mnt/data/large_table")
\
.filter("event_date > '2024-01-01'")
filtered_df.count()
The data engineer notices from the Query Profiler that the scan operator for filtered_df is reading almost all files, despite the filter being applied.
What is the probable reason for poor data skipping?

• A. The filter condition involves a data type excluded from data skipping support.
• B. The event_date column is outside the table's partitioning and Z-ordering scheme.
• C. The filter is executed only after the full data scan, preventing data skipping.
• D. The Delta table lacks optimization that enables dynamic file pruning.