Use Databricks Certified Data Engineer Professional Dumps to Obtain Databricks Certification

1. The data architect has mandated that all tables in the Lakehouse should be configured as external Delta Lake tables.

Which approach will ensure that this requirement is met?

2. A junior data engineer is working to implement logic for a Lakehouse table named silver_device_recordings. The source data contains 100 unique fields in a highly nested JSON structure.

The silver_device_recordings table will be used downstream to power several production monitoring dashboards and a production model. At present, 45 of the 100 fields are being used in at least one of these applications.

The data engineer is trying to determine the best approach for dealing with schema declaration given the highly-nested structure of the data and the numerous fields.

Which of the following accurately presents information about Delta Lake and Databricks that may impact their decision-making process?

3. 1.You were asked to setup a new all-purpose cluster, but the cluster is unable to start which of the following steps do you need to take to identify the root cause of the issue and the reason why the cluster was unable to start?

4. Which statement regarding stream-static joins and static Delta tables is correct?

5. Which of the following describes a benefit of a data lakehouse that is unavailable in a traditional data

warehouse?

6. You noticed that colleague is manually copying the notebook with _bkp to store the previous versions, which of the following feature would you recommend instead.

7. What is the purpose of a silver layer in Multi hop architecture?

8. Which of the following functions can be used to convert JSON string to Struct data type?

9. Which of the following developer operations in the CI/CD can only be implemented through a GIT provider when using Databricks Repos.

10. The data engineering team maintains the following code:





Assuming that this code produces logically correct results and the data in the source tables has been de-duplicated and validated, which statement describes what will occur when this code is executed?

11. 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, dateTimeCollected TIMESTAMP





SELECT deviceId, __ (__ (__(deviceTemp], i -> i > 100.00)))

FROM devices

GROUP BY deviceId

12. Which of the following SQL commands are used to append rows to an existing delta table?

13. A new user who currently does not have access to the catalog or schema is requesting access to the customer table in sales schema, but the customer table contains sensitive information, so you have decided to create view on the table excluding columns that are sensitive and granted access to the view GRANT SELECT ON view_name to [email protected] but when the user tries to query the view, gets the error view does not exist.

What is the issue preventing user to access the view and how to fix it?

14. The team has decided to take advantage of table properties to identify a business owner for each table, which of the following table DDL syntax allows you to populate a table property identifying the business owner of a table

CREATE TABLE inventory (id INT, units FLOAT)

15. An upstream source writes Parquet data as hourly batches to directories named with the current date.

A nightly batch job runs the following code to ingest all data from the previous day as indicated by the date variable:





Assume that the fields customer_id and order_id serve as a composite key to uniquely identify each order.

If the upstream system is known to occasionally produce duplicate entries for a single order hours apart, which statement is correct?

16. A data engineer is using a Databricks SQL query to monitor the performance of an ELT job. The ELT job is triggered by a specific number of input records being ready to process. The Databricks SQL query returns the number of minutes since the job’s most recent runtime.

Which of the following approaches can enable the data engineering team to be notified if the ELT job has not been run in an hour?

17. return x

18. You are tasked to set up a set notebook as a job for six departments and each department can run the task parallelly, the notebook takes an input parameter dept number to process the data by department, how do you go about to setup this up in job?

19. How do you check the location of an existing schema in Delta Lake?

20. Why does AUTO LOADER require schema location?

21. table(table_name))

22. A team member is leaving the team and he/she is currently the owner of the few tables, instead of transferring the ownership to a user you have decided to transfer the ownership to a group so in the future anyone in the group can manage the permissions rather than a single individual, which of the following commands help you accomplish this?

23. A data engineer is designing a data pipeline. The source system generates files in a shared directory that is also used by other processes. As a result, the files should be kept as is and will accumulate in the directory. The data engineer needs to identify which files are new since the previous run in the pipeline, and set up the pipeline to only ingest those new files with each run.

Which of the following tools can the data engineer use to solve this problem?

24. Which of the following benefits does Delta Live Tables provide for ELT pipelines over standard data pipelines that utilize Spark and Delta Lake on Databricks?

25. What type of table is created when you create delta table with below command?

CREATE TABLE transactions USING DELTA LOCATION "DBFS:/mnt/bronze/transactions"

26. Which of the following technique can be used to implement fine-grained access control to rows and columns of the Delta table based on the user's access?

27. Which configuration parameter directly affects the size of a spark-partition upon ingestion of data into Spark?

28. A user new to Databricks is trying to troubleshoot long execution times for some pipeline logic they are working on. Presently, the user is executing code cell-by-cell, using display() calls to confirm code is producing the logically correct results as new transformations are added to an operation. To get a measure of average time to execute, the user is running each cell multiple times interactively.

Which of the following adjustments will get a more accurate measure of how code is likely to perform in production?

29. A junior data engineer seeks to leverage Delta Lake's Change Data Feed functionality to create a Type 1 table representing all of the values that have ever been valid for all rows in a bronze table created with the property delta.enableChangeDataFeed = true.

They plan to execute the following code as a daily job:





Which statement describes the execution and results of running the above query multiple times?

30. Which of the following operations are not supported on a streaming dataset view?

spark.readStream.format("delta").table("sales").createOrReplaceTempView("streaming_vie w")

31. spark.sql("SELECT * FROM sales WHERE orderDate = order_date")

32. Newly joined data analyst requested read-only access to tables, assuming you are owner/admin which section of Databricks platform is going to facilitate granting select access to the user

33. Which of the following SQL command can be used to insert or update or delete rows based on a condition to check if a row(s) exists?

34. Data science team members are using a single cluster to perform data analysis, although cluster size was chosen to handle multiple users and auto-scaling was enabled, the team realized queries are still running slow, what would be the suggested fix for this?

35. Which of the following is true, when building a Databricks SQL dashboard?

36. To reduce storage and compute costs, the data engineering team has been tasked with curating a series of aggregate tables leveraged by business intelligence dashboards, customer-facing applications, production machine learning models, and ad hoc analytical queries.

The data engineering team has been made aware of new requirements from a customer-facing application, which is the only downstream workload they manage entirely. As a result, an aggregate table used by numerous teams across the organization will need to have a number of fields renamed, and additional fields will also be added.

Which of the solutions addresses the situation while minimally interrupting other teams in the organization without increasing the number of tables that need to be managed?

37. Which of the following describes how Databricks Repos can help facilitate CI/CD workflows on the Databricks Lakehouse Platform?

38. You are asked to setup an AUTO LOADER to process the incoming data, this data arrives in JSON format and get dropped into cloud object storage and you are required to process the data as soon as it arrives in cloud storage, which of the following statements is correct

39. You are using k-means clustering to classify heart patients for a hospital. You have chosen Patient Sex, Height, Weight, Age and Income as measures and have used 3 clusters. When you create a pair-wise plot of the clusters, you notice that there is significant overlap between the clusters .

What should you do?

40. Which of the following data workloads will utilize a silver table as its source?

41. You are working to set up two notebooks to run on a schedule, the second notebook is dependent on the first notebook but both notebooks need different types of compute to run in an optimal fashion, what is the best way to set up these notebooks as jobs?

42. What is the main difference between the silver layer and the gold layer in medallion architecture?

43. ELSE (temp C 33 ) * 5/9 5.END

44. Which of the following array functions takes input column return unique list of values in an array?

45. where on_hand = 0

46. What is the main difference between AUTO LOADER and COPY INTO?

47. The DevOps team has configured a production workload as a collection of notebooks scheduled to run daily using the Jobs UI. A new data engineering hire is onboarding to the team and has requested access to one of these notebooks to review the production logic.

What are the maximum notebook permissions that can be granted to the user without allowing accidental changes to production code or data?

48. What steps need to be taken to set up a DELTA LIVE PIPELINE as a job using the workspace UI?

49. A data engineer is overwriting data in a table by deleting the table and recreating the table. Another data engineer suggests that this is inefficient and the table should simply be overwritten instead.

Which of the following reasons to overwrite the table instead of deleting and recreating the table is incorrect?

50. When scheduling Structured Streaming jobs for production, which configuration automatically recovers from query failures and keeps costs low?

51. Delete records from the transactions Delta table where transactionDate is greater than current timestamp?

52. How are Delt tables stored?

53. org.apache.spark.sql.parquet OPTIONS (PATH "storage-path")

D. 1. CREATE TABLE my_table (id STRING, value STRING);

E. 1. CREATE MANAGED TABLE my_table (id STRING, value STRING);

54. You noticed a colleague is manually copying the data to the backup folder prior to running an up-date command, incase if the update command did not provide the expected outcome so he can use the backup copy to replace table, which Delta Lake feature would you recommend simplifying the process?

55. query = f"select * from + schema_name +"."+table_name"

56. You are currently working with the application team to setup a SQL Endpoint point, once the team started consuming the SQL Endpoint you noticed that during peak hours as the number of concur-rent users increases you are seeing degradation in the query performance and the same queries are taking longer to run, which of the following steps can be taken to resolve the issue?

57. The current ELT pipeline is receiving data from the operations team once a day so you had setup an AUTO LOADER process to run once a day using trigger (Once = True) and scheduled a job to run once a day, operations team recently rolled out a new feature that allows them to send data every 1 min, what changes do you need to make to AUTO LOADER to process the data every 1 min.

58. Operations team is using a centralized data quality monitoring system, a user can publish data quality metrics through a webhook, you were asked to develop a process to send messages using a webhook if there is atleast one duplicate record, which of the following approaches can be taken to integrate an alert with current data quality monitoring system

59. When defining external tables using formats CSV, JSON, TEXT, BINARY any query on the exter-nal tables caches the data and location for performance reasons, so within a given spark session any new files that may have arrived will not be available after the initial query.

How can we address this limitation?

60. If you create a database sample_db with the statement CREATE DATABASE sample_db what will be the default location of the database in DBFS?

61. What is the best way to query external csv files located on DBFS Storage to inspect the data using SQL?

62. Which of the following is a correct statement on how the data is organized in the storage when when managing a DELTA table?

63. SELECT * FROM CUSTOMERS_2020

64. Which of the following statement is true about Databricks repos?

65. Which Python variable contains a list of directories to be searched when trying to locate required modules?

66. You have configured AUTO LOADER to process incoming IOT data from cloud object storage every 15 mins, recently a change was made to the notebook code to update the processing logic but the team later realized that the notebook was failing for the last 24 hours, what steps team needs to take to reprocess the data that was not loaded after the notebook was corrected?

67. A data engineer has set up two Jobs that each run nightly. The first Job starts at 12:00 AM, and it usually completes in about 20 minutes. The second Job depends on the first Job, and it starts at 12:30 AM. Sometimes, the second Job fails when the first Job does not complete by 12:30 AM.

Which of the following approaches can the data engineer use to avoid this problem?

68. You had worked with the Data analysts team to set up a SQL Endpoint(SQL warehouse) point so they can easily query and analyze data in the gold layer, but once they started consuming the SQL Endpoint(SQL warehouse) you noticed that during the peak hours as the number of users increase you are seeing queries taking longer to finish, which of the following steps can be taken to resolve the issue?

*Please note Databricks recently renamed SQL endpoint to SQL warehouse.

69. Which of the following approaches can the data engineer use to obtain a version-controllable con-figuration of the Job’s schedule and configuration?

70. Unity catalog simplifies managing multiple workspaces, by storing and managing permissions and ACL at _______ level

71. Your colleague was walking you through how a job was setup, but you noticed a warning message that said, “Jobs running on all-purpose cluster are considered all purpose compute", the colleague was not sure why he was getting the warning message, how do you best explain this warning mes-sage?

72. FROM carts GROUP BY cartId

Expected result:

cartId items

1 [1,100,200,300,250]

73. schema_name = "bronze"

74. Which of the following developer operations in CI/CD flow can be implemented in Databricks Re-pos?

75. You are looking to process the data based on two variables, one to check if the department is supply chain and second to check if process flag is set to True

76. as total_sales from sales

77. You are currently working with the second team and both teams are looking to modify the same notebook, you noticed that the second member is copying the notebooks to the personal folder to edit and replace the collaboration notebook, which notebook feature do you recommend to make the process easier to collaborate.

78. A newly joined team member John Smith in the Marketing team who currently does not have any access to the data requires read access to customers table, which of the following statements can be used to grant access.

79. A particular job seems to be performing slower and slower over time, the team thinks this started to happen when a recent production change was implemented, you were asked to take look at the job history and see if we can identify trends and root cause, where in the workspace UI can you perform this analysis?

80. A dataset has been defined using Delta Live Tables and includes an expectations clause:

CON-STRAINT valid_timestamp EXPECT (timestamp > '2020-01-01')

What is the expected behavior when a batch of data containing data that violates these constraints is processed?

81. A Spark job is taking longer than expected. Using the Spark UI, a data engineer notes that the Min, Median, and Max Durations for tasks in a particular stage show the minimum and median time to complete a task as roughly the same, but the max duration for a task to be roughly 100 times as long as the minimum.

Which situation is causing increased duration of the overall job?

82. A DELTA LIVE TABLE pipelines can be scheduled to run in two different modes, what are these two different modes?

83. A table is registered with the following code:





Both users and orders are Delta Lake tables.

Which statement describes the results of querying recent_orders?

84. The data engineering team is looking to add a new column to the table, but the QA team would like to test the change before implementing in production, which of the below options allow you to quickly copy the table from Prod to the QA environment, modify and run the tests?

85. The data engineering team has configured a Databricks SQL query and alert to monitor the values in a Delta Lake table. The recent_sensor_recordings table contains an identifying sensor_id alongside the timestamp and temperature for the most recent 5 minutes of recordings.

The below query is used to create the alert:





The query is set to refresh each minute and always completes in less than 10 seconds. The alert is set to trigger when mean (temperature) > 120. Notifications are triggered to be sent at most every 1 minute.

If this alert raises notifications for 3 consecutive minutes and then stops, which statement must be true?

86. The Delta Live Table Pipeline is configured to run in Production mode using the continuous Pipe-line Mode.

What is the expected outcome after clicking Start to update the pipeline?

87. Which of the following is not a privilege in the Unity catalog?

88. You are looking to process the data based on two variables, one to check if the department is supply chain or check if process flag is set to True

89. You are noticing job cluster is taking 6 to 8 mins to start which is delaying your job to finish on time, what steps you can take to reduce the amount of time cluster startup time

90. Direct query on external files limited options, create external tables for CSV files with header and pipe delimited CSV files, fill in the blanks to complete the create table statement

CREATE TABLE sales (id int, unitsSold int, price FLOAT, items STRING)

________________

LOCATION “dbfs:/mnt/sales/*.csv”

91. Which of the following scenarios is the best fit for the AUTO LOADER solution?

92. The data governance team is reviewing code used for deleting records for compliance with GDPR. They note the following logic is used to delete records from the Delta Lake table named users.





Assuming that user_id is a unique identifying key and that delete_requests contains all users that have requested deletion, which statement describes whether successfully executing the above logic guarantees that the records to be deleted are no longer accessible and why?

93. How to determine if a table is a managed table vs external table?

94. Once a cluster is deleted, below additional actions need to performed by the administrator

95. What are the different ways you can schedule a job in Databricks workspace?

96. A newly joined team member John Smith in the Marketing team currently has access read access to sales tables but does not have access to update the table, which of the following commands help you accomplish this?

97. In which phase of the data analytics lifecycle do Data Scientists spend the most time in a project?

98. Which of the statements are incorrect when choosing between lakehouse and Datawarehouse?

99. Which of the following is the correct statement for a session scoped temporary view?

100. What statement is true regarding the retention of job run history?

101. You are currently working to ingest millions of files that get uploaded to the cloud object storage for consumption, and you are asked to build a process to ingest this data, the schema of the file is expected to change over time, and the ingestion process should be able to handle these changes automatically.

Which of the following method can be used to ingest the data incrementally?

102. An upstream system is emitting change data capture (CDC) logs that are being written to a cloud object storage directory. Each record in the log indicates the change type (insert, update, or delete) and the values for each field after the change. The source table has a primary key identified by the field pk_id.

For auditing purposes, the data governance team wishes to maintain a full record of all values that have ever been valid in the source system. For analytical purposes, only the most recent value for each record needs to be recorded. The Databricks job to ingest these records occurs once per hour, but each individual record may have changed multiple times over the course of an hour.

Which solution meets these requirements?

103. The data engineering team noticed that one of the job normally finishes in 15 mins but gets stuck randomly when reading remote databases due to a network packet drop, which of the following steps can be used to improve the stability of the job?

104. You have noticed the Data scientist team is using the notebook versioning feature with git integra-tion, you have recommended them to switch to using Databricks Repos, which of the below reasons could be the reason the why the team needs to switch to Databricks Repos.

105. Which of the following command can be used to drop a managed delta table and the underlying files in the storage?

106. An upstream system has been configured to pass the date for a given batch of data to the Databricks Jobs API as a parameter. The notebook to be scheduled will use this parameter to load data with the following code: df = spark.read.format("parquet").load(f"/mnt/source/(date)")

Which code block should be used to create the date Python variable used in the above code block?

107. The Databricks workspace administrator has configured interactive clusters for each of the data engineering groups. To control costs, clusters are set to terminate after 30 minutes of inactivity. Each user should be able to execute workloads against their assigned clusters at any time of the day.

Assuming users have been added to a workspace but not granted any permissions, which of the following describes the minimal permissions a user would need to start and attach to an already configured cluster.

108. A junior data engineer has configured a workload that posts the following JSON to the Databricks REST API endpoint 2.0/jobs/create.





Assuming that all configurations and referenced resources are available, which statement describes the result of executing this workload three times?

109. A data architect is designing a data model that works for both video-based machine learning work-loads and highly audited batch ETL/ELT workloads.

Which of the following describes how using a data lakehouse can help the data architect meet the needs of both workloads?

110. A nightly job ingests data into a Delta Lake table using the following code:





The next step in the pipeline requires a function that returns an object that can be used to manipulate new records that have not yet been processed to the next table in the pipeline.

Which code snippet completes this function definition?

def new_records():

A) return spark.readStream.table("bronze")

B) return spark.readStream.load("bronze")

C)





D)return spark.read.option("readChangeFeed", "true").table ("bronze")

E)