Free Access to Databricks.Databricks-Certified-Professional-Data-Engineer.v2025-10-27.q109 with Valid Practice Test (Page 19)

Question 86

Data science team has requested they are missing a column in the table called average price, this can be calculated using units sold and sales amt, which of the following SQL statements allow you to reload the data with additional column

A.1.INSERT OVERWRITE sales
2.SELECT *, salesAmt/unitsSold as avgPrice FROM sales
B.1.CREATE OR REPLACE TABLE sales
2.AS SELECT *, salesAmt/unitsSold as avgPrice FROM sales
C.MERGE INTO sales USING (SELECT *, salesAmt/unitsSold as avgPrice FROM sales)
D.OVERWRITE sales AS SELECT *, salesAmt/unitsSold as avgPrice FROM sales
E.COPY INTO SALES AS SELECT *, salesAmt/unitsSold as avgPrice FROM sales

Question 87

A data ingestion task requires a one-TB JSON dataset to be written out to Parquet with a target part-file size of
512 MB. Because Parquet is being used instead of Delta Lake, built-in file-sizing features such as Auto-Optimize & Auto-Compaction cannot be used.
Which strategy will yield the best performance without shuffling data?

A.Set spark.sql.files.maxPartitionBytes to 512 MB, ingest the data, execute the narrow transformations, and then write to parquet.
B.Set spark.sql.shuffle.partitions to 2,048 partitions (1TB*1024*1024/512), ingest the data, execute the narrow transformations, optimize the data by sorting it (which automatically repartitions the data), and then write to parquet.
C.Set spark.sql.adaptive.advisoryPartitionSizeInBytes to 512 MB bytes, ingest the data, execute the narrow transformations, coalesce to 2,048 partitions (1TB*1024*1024/512), and then write to parquet.
D.Ingest the data, execute the narrow transformations, repartition to 2,048 partitions (1TB*
1024*1024/512), and then write to parquet.
E.Set spark.sql.shuffle.partitions to 512, ingest the data, execute the narrow transformations, and then write to parquet.

Question 88

You were asked to identify number of times a temperature sensor exceed threshold temperature (100.00) by each device, each row contains 5 readings collected every 5 minutes, fill in the blank with the appropriate functions.
Schema: deviceId INT, deviceTemp ARRAY<double>, dateTimeCollected TIMESTAMP

SELECT deviceId, __ (__ (__(deviceTemp], i -> i > 100.00)))
FROM devices
GROUP BY deviceId

A.SUM, COUNT, SIZE
B.SUM, SIZE, SLICE
C.SUM, SIZE, ARRAY_CONTAINS
D.SUM, SIZE, ARRAY_FILTER
E.SUM, SIZE, FILTER

Question 89

A Databricks job has been configured with 3 tasks, each of which is a Databricks notebook. Task A does not depend on other tasks. Tasks B and C run in parallel, with each having a serial dependency on Task A.
If task A fails during a scheduled run, which statement describes the results of this run?

A.Because all tasks are managed as a dependency graph, no changes will be committed to the Lakehouse until all tasks have successfully been completed.
B.Tasks B and C will attempt to run as configured; any changes made in task A will be rolled back due to task failure.
C.Unless all tasks complete successfully, no changes will be committed to the Lakehouse; because task A failed, all commits will be rolled back automatically.
D.Tasks B and C will be skipped; some logic expressed in task A may have been committed before task failure.
E.Tasks B and C will be skipped; task A will not commit any changes because of stage failure.

Question 90

A SQL Dashboard was built for the supply chain team to monitor the inventory and product orders, but all of the timestamps displayed on the dashboards are showing in UTC format, so they requested to change the time zone to the location of New York. How would you approach resolving this issue?

A.Move the workspace from Central US zone to East US Zone
B.Change the timestamp on the delta tables to America/New_York format
C.Change the spark configuration of SQL endpoint to format the timestamp to Ameri-ca/New_York
D.Under SQL Admin Console, set the SQL configuration parameter time zone to Ameri-ca/New_York
E.Add SET Timezone = America/New_York on every of the SQL queries in the dashboard.

Question 86

Question 87

Question 88

Question 89

Question 90

Download PDF File