DE-PRO practice questions 131–140

1. Question 131 · 202 in bank

   The security team is exploring whether or not the Databricks secrets module can be leveraged for connecting to an external database. After testing the code with all Python variables being defined with strings, they upload the password to the secrets module and configure the correct permissions for the currently active user. They then modify their code to the following (leaving all other variables unchanged).

   

   Which statement describes what will happen when the above code is executed?

   • AThe connection to the external table will fail; the string "REDACTED" will be printed.
   • BAn interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the encoded password will be saved to DBFS.
   • CAn interactive input box will appear in the notebook; if the right password is provided, the connection will succeed and the password will be printed in plain text.
   • DThe connection to the external table will succeed; the string value of password will be printed in plain text.
   • EThe connection to the external table will succeed; the string "REDACTED" will be printed.

   Show answer

   Answer: E

   Explanation

   Whatever we read using dbutls.secret module is always printed as '[REDACTED]', but when consumed in code, underlying vales are passed.

   Source: Adapted from community discussion — akashdesarda

   References

   • Databricks documentation
   • Microsoft Learn

   Next question

2. Question 132 · 202 in bank

   The data engineering team maintains a table of aggregate statistics through batch nightly updates. This includes total sales for the previous day alongside totals and averages for a variety of time periods including the 7 previous days, year-to-date, and quarter-to-date. This table is named store_saies_summary and the schema is as follows:

   

   The table daily_store_sales contains all the information needed to update store_sales_summary. The schema for this table is: store_id INT, sales_date DATE, total_sales FLOAT If daily_store_sales is implemented as a Type 1 table and the total_sales column might be adjusted after manual data auditing, which approach is the safest to generate accurate reports in the store_sales_summary table?

   • AImplement the appropriate aggregate logic as a batch read against the daily_store_sales table and overwrite the store_sales_summary table with each Update.
   • BImplement the appropriate aggregate logic as a batch read against the daily_store_sales table and append new rows nightly to the store_sales_summary table.
   • CImplement the appropriate aggregate logic as a batch read against the daily_store_sales table and use upsert logic to update results in the store_sales_summary table.
   • DImplement the appropriate aggregate logic as a Structured Streaming read against the daily_store_sales table and use upsert logic to update results in the store_sales_summary table.
   • EUse Structured Streaming to subscribe to the change data feed for daily_store_sales and apply changes to the aggregates in the store_sales_summary table with each update.

   Show answer

   Answer: C

   Explanation

   A is not correct because the table is daily. If you overwrite you delete all history. You need to insert/update to keep history.

   Source: Adapted from community discussion — MDWPartners

   Next question

3. Question 133 · 202 in bank

   A member of the data engineering team has submitted a short notebook that they wish to schedule as part of a larger data pipeline. Assume that the commands provided below produce the logically correct results when run as presented.

   

   Which command should be removed from the notebook before scheduling it as a job?

   • ACmd 2
   • BCmd 3
   • CCmd 4
   • DCmd 5
   • ECmd 6

   Show answer

   Answer: E

   Explanation

   When scheduling a Databricks notebook as a job, it's generally recommended to remove or modify commands that involve displaying output, such as using the display() function. Displaying data using display() is an interactive feature designed for exploration and visualization within the notebook interface and may not work well in a production job context. The finalDF.explain() command, which provides the execution plan of the DataFrame transformations and actions, is often useful for debugging and optimizing queries. While it doesn't display interactive visualizations like display(), it can still be informative for understanding how Spark is executing the operations on your DataFrame.

   Source: Adapted from community discussion — petrv

   Next question

4. Question 134 · 202 in bank

   The business reporting team requires that data for their dashboards be updated every hour. The total processing time for the pipeline that extracts transforms, and loads the data for their pipeline runs in 10 minutes. Assuming normal operating conditions, which configuration will meet their service-level agreement requirements with the lowest cost?

   • AManually trigger a job anytime the business reporting team refreshes their dashboards
   • BSchedule a job to execute the pipeline once an hour on a new job cluster
   • CSchedule a Structured Streaming job with a trigger interval of 60 minutes
   • DSchedule a job to execute the pipeline once an hour on a dedicated interactive cluster
   • EConfigure a job that executes every time new data lands in a given directory

   Show answer

   Answer: B

   Explanation

   B is correct I think. With option C, the cluster remains on 24/7 with trigger = 60 mins which is more costly If there is an option with structure streaming with trigger = availablenow, and job scheduled per hour, that would be even more efficient. https://www.databricks.com/blog/2017/05/22/running-streaming-jobs-day-10x-cost-savings.html

   Source: Adapted from community discussion — divingbell17

   Next question

5. Question 135 · 202 in bank

   A Delta Lake table was created with the below query:

   

   Consider the following query: DROP TABLE prod.sales_by_store - If this statement is executed by a workspace admin, which result will occur?

   • ANothing will occur until a COMMIT command is executed.
   • BThe table will be removed from the catalog but the data will remain in storage.
   • CThe table will be removed from the catalog and the data will be deleted.
   • DAn error will occur because Delta Lake prevents the deletion of production data.
   • EData will be marked as deleted but still recoverable with Time Travel.

   Show answer

   Answer: C

   Explanation

   According to the exam courses answer is C, for a managed table dropped. But, as after Nov'23, UNDROP is introduced and I have test it working with UC managed tables. https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-ddl-undrop-table.html However, I don't see any official doc says UNDROP related to 'time travel'. So, be aware of the above info; in exam, watch the question carefully if it is updated.

   Source: Adapted from community discussion — hal2401me

   References

   • Databricks documentation
   • Microsoft Learn

   Next question

6. Question 136 · 202 in bank

   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?

   • ADBFS 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.
   • BBy default, both the DBFS root and mounted data sources are only accessible to workspace administrators.
   • CThe DBFS root is the most secure location to store data, because mounted storage volumes must have full public read and write permissions.
   • DNeither the DBFS root nor mounted storage can be accessed when using %sh in a Databricks notebook.
   • EThe 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.

   Show answer

   Answer: A

   Explanation

   A is correct . For E, This statement is partially incorrect. The DBFS root does use ephemeral storage, but not block volumes. Data saved there is lost when the cluster terminates unless explicitly persisted elsewhere. Mounted storage, however, can persist data between sessions depending on the underlying storage service and configuration.

   Source: Adapted from community discussion — Curious76

   Next question

7. Question 137 · 202 in bank

   The following code has been migrated to a Databricks notebook from a legacy workload:

   

   The code executes successfully and provides the logically correct results, however, it takes over 20 minutes to extract and load around 1 GB of data. Which statement is a possible explanation for this behavior?

   • A%sh triggers a cluster restart to collect and install Git. Most of the latency is related to cluster startup time.
   • BInstead of cloning, the code should use %sh pip install so that the Python code can get executed in parallel across all nodes in a cluster.
   • C%sh does not distribute file moving operations; the final line of code should be updated to use %fs instead.
   • DPython will always execute slower than Scala on Databricks. The run.py script should be refactored to Scala.
   • E%sh executes shell code on the driver node. The code does not take advantage of the worker nodes or Databricks optimized Spark.

   Show answer

   Answer: E

   Explanation

   When using %sh in a Databricks notebook, the commands are executed in a shell environment on the driver node. This means that only the resources of the driver node are used, and the execution does not leverage the distributed computing capabilities of the worker nodes in the Spark cluster. This can result in slower performance, especially for data-intensive tasks, compared to an approach that distributes the workload across all nodes in the cluster using Spark.

   Source: Adapted from community discussion — aragorn_brego

   Next question

8. Question 138 · 202 in bank

   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?

   • ADelta Lake statistics are not optimized for free text fields with high cardinality.
   • BText data cannot be stored with Delta Lake.
   • CZORDER ON review will need to be run to see performance gains.
   • DThe Delta log creates a term matrix for free text fields to support selective filtering.
   • EDelta Lake statistics are only collected on the first 4 columns in a table.

   Show answer

   Answer: A

   Explanation

   Delta Lake uses statistics and data skipping to improve query performance, but these optimizations are most effective for columns with low to medium cardinality (i.e., columns with a limited set of distinct values). Free-form text fields like the review column typically have high cardinality, meaning each value in the column (each review text) is unique or nearly unique. Consequently, statistics on such columns do not significantly improve the performance of queries searching for specific keywords within the text.

   Source: Adapted from community discussion — aragorn_brego

   Next question

9. Question 139 · 202 in bank

   The business intelligence team has a dashboard configured to track various summary metrics for retail stores. This includes total sales for the previous day alongside totals and averages for a variety of time periods. The fields required to populate this dashboard have the following schema:

   

   For demand forecasting, the Lakehouse contains a validated table of all itemized sales updated incrementally in near real-time. This table, named products_per_order, includes the following fields:

   

   Because reporting on long-term sales trends is less volatile, analysts using the new dashboard only require data to be refreshed once daily. Because the dashboard will be queried interactively by many users throughout a normal business day, it should return results quickly and reduce total compute associated with each materialization. Which solution meets the expectations of the end users while controlling and limiting possible costs?

   • APopulate the dashboard by configuring a nightly batch job to save the required values as a table overwritten with each update.
   • BUse Structured Streaming to configure a live dashboard against the products_per_order table within a Databricks notebook.
   • CConfigure a webhook to execute an incremental read against products_per_order each time the dashboard is refreshed.
   • DUse the Delta Cache to persist the products_per_order table in memory to quickly update the dashboard with each query.
   • EDefine a view against the products_per_order table and define the dashboard against this view.

   Show answer

   Answer: A

   Next question

10. Question 140 · 202 in bank

    A junior data engineer has been asked to develop a streaming data pipeline with a grouped aggregation using DataFrame df. The pipeline needs to calculate the average humidity and average temperature for each non-overlapping five-minute interval. Incremental state information should be maintained for 10 minutes for late-arriving data. Streaming DataFrame df has the following schema: "device_id INT, event_time TIMESTAMP, temp FLOAT, humidity FLOAT" Code block:

    

    Choose the response that correctly fills in the blank within the code block to complete this task.

    • AwithWatermark("event_time", "10 minutes")
    • BawaitArrival("event_time", "10 minutes")
    • Cawait("event_time + ‘10 minutes'")
    • DslidingWindow("event_time", "10 minutes")
    • EdelayWrite("event_time", "10 minutes")

    Show answer

    Answer: A

    Explanation

    To handle late-arriving data in a streaming aggregation, you need to specify a watermark, which tells the streaming query how long to wait for late data. The withWatermark method is used for this purpose in Spark Structured Streaming. It defines the threshold for how late the data can be relative to the latest data that has been seen in the same window.

    Source: Adapted from community discussion — aragorn_brego

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DE-PRO practice, question 131. 202 questions available.