Databricks Certified Data Engineer Professional Practice Test Questions, Exam Dumps

Practice Exams:

View All

Certified Data Engineer Professional Databricks Practice Test Questions and Exam Dumps

Question No 1:

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?

A. date = spark.conf.get("date")

B. input_dict = input() date = input_dict["date"]

C. import sys date = sys.argv[1]

D. date = dbutils.notebooks.getParam("date")

E. dbutils.widgets.text("date", "null") date = dbutils.widgets.get("date")

Answer: E

Explanation:

When working with Databricks Jobs, parameters are passed to notebooks using widgets. These widgets allow users to define dynamic input that can be accessed during the runtime of the notebook, making it easy to use parameters like a date value in a scheduled job.

In this case, an upstream system is passing the date parameter to the Databricks Jobs API, and your notebook must retrieve that parameter properly to load data dynamically based on the date.

Let’s break down the options:

A. date = spark.conf.get("date")

While spark.conf.get() is used to retrieve Spark configuration properties, it is not used for retrieving job parameters passed via the Databricks Jobs API. This would only work if the date parameter were stored in the Spark config, which it isn’t in this context.

B. Using input() and a dictionary

This syntax is typical of a standard Python script, not a Databricks notebook. The Databricks execution model doesn’t use Python’s input() function for passing parameters—it uses widgets instead. Furthermore, input() would block execution, expecting a user to manually input data, which doesn’t work with scheduled jobs.

C. sys.argv[1]

Again, this is typical of command-line interface (CLI) applications or standalone Python scripts. It does not apply to Databricks notebooks running in the managed notebook environment, especially when executed via the Databricks Jobs API.

D. dbutils.notebooks.getParam("date")

There is no such function as getParam() in the Databricks dbutils.notebooks module. This line would result in an AttributeError. This appears to be a fabricated or incorrect function.

E. dbutils.widgets.text("date", "null") and date = dbutils.widgets.get("date")

This is the correct approach. Here's why:

dbutils.widgets.text("date", "null") creates a text widget in the notebook named "date" with a default value of "null". This allows the notebook to accept an external parameter.
dbutils.widgets.get("date") retrieves the actual value passed to the widget—whether it came from manual entry or a Databricks Job invocation.
This mechanism works seamlessly with parameterized notebook jobs, allowing the job scheduler to pass a parameter (like the date value) through the API.

This method is officially recommended by Databricks for parameterizing notebooks.

Conclusion:
Only option E aligns with how Databricks passes and retrieves parameters for scheduled notebook jobs. The other options are either incorrect for the Databricks environment or syntactically invalid.Would you like help writing the full code block for loading the data based on the parameterized date input?

Question No 2:

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.

A. "Can Manage" privileges on the required cluster
B. Workspace Admin privileges, cluster creation allowed, "Can Attach To" privileges on the required cluster
C. Cluster creation allowed, "Can Attach To" privileges on the required cluster
D. "Can Restart" privileges on the required cluster
E. Cluster creation allowed, "Can Restart" privileges on the required cluster

Answer: D

Explanation:

In Databricks, managing access and usage of clusters is critical for controlling both costs and resource availability. When a workspace administrator pre-configures interactive clusters, it means users aren’t expected to create new clusters, but rather to re-use and restart existing ones. The clusters may auto-terminate after a period of inactivity, but they still exist and can be restarted when needed.

Now, let’s examine the goal: users must be able to start and attach to an existing cluster at any time. This doesn't mean creating a new cluster or managing all aspects of it—it only means restarting it and submitting jobs to it.

Here's what each option entails:

Option A: "Can Manage" privileges on the required cluster
This permission allows users to edit cluster settings, restart, attach, and terminate the cluster. While it would achieve the goal, it grants more privileges than necessary, especially allowing users to modify or delete the cluster, which could cause problems. Thus, this is not minimal.
Option B: Workspace Admin privileges, cluster creation allowed, "Can Attach To" privileges on the required cluster
This combination includes admin-level privileges, which go far beyond what’s required. Workspace Admins can do everything in the environment, including managing users and billing. This is overly permissive and not the minimal necessary access.
Option C: Cluster creation allowed, "Can Attach To" privileges on the required cluster
This allows users to create new clusters and attach notebooks to clusters. However, the question specifically mentions using already-configured clusters, so the ability to create new clusters is unnecessary and introduces potential cost and control issues. This also lacks the ability to restart terminated clusters, so it's incomplete.
Option D: "Can Restart" privileges on the required cluster
This is the correct and most minimal permission. It allows users to restart a terminated cluster, and by default, users with "Can Restart" privileges also receive "Can Attach To" privileges. This means they can attach notebooks and jobs to the cluster once it’s running. They cannot modify the cluster’s configuration, terminate it early, or affect other users' access. This is the least privilege required to achieve the stated goal.
Option E: Cluster creation allowed, "Can Restart" privileges on the required cluster
This goes beyond the minimal requirement by enabling cluster creation, which is unnecessary in this scenario and could result in uncontrolled cost growth. While it technically works, it’s not the minimal necessary access.

Conclusion:
To allow users to start (restart) and attach to existing clusters, the minimum required permission is "Can Restart" on the cluster. This satisfies the business requirement without exposing the cluster to unnecessary changes or additional costs.

Question No 3:

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

A. Cluster: New Job Cluster; Retries: Unlimited; Maximum Concurrent Runs: Unlimited
B. Cluster: New Job Cluster; Retries: None; Maximum Concurrent Runs: 1
C. Cluster: Existing All-Purpose Cluster; Retries: Unlimited; Maximum Concurrent Runs: 1
D. Cluster: New Job Cluster; Retries: Unlimited; Maximum Concurrent Runs: 1
E. Cluster: Existing All-Purpose Cluster; Retries: None; Maximum Concurrent Runs: 1

Answer: D

Explanation:

When scheduling Structured Streaming jobs in production environments, the goal is to ensure high availability, fault tolerance, and cost-effectiveness. Let’s break down the key elements from the question and how the options fulfill those criteria:

1. Cluster Type: New Job Cluster vs Existing All-Purpose Cluster

A New Job Cluster is ephemeral, created specifically for the job, and terminated when the job completes. This minimizes resource consumption and is more cost-efficient.
An Existing All-Purpose Cluster is designed for interactive workloads and shared usage, which can lead to resource contention and higher costs due to continuous uptime, even when no job is running.
Therefore, using a New Job Cluster is more optimal in production for cost control and reliability.

2. Retries: Unlimited vs None

Retries ensure that transient or recoverable errors (e.g., network hiccups, brief storage unavailability) don't cause job failures.
Setting Retries: Unlimited ensures that Structured Streaming jobs can automatically recover from failures without manual intervention.
Retries: None would cause the job to fail permanently at the first issue, which is not acceptable in robust production environments.

3. Maximum Concurrent Runs: Unlimited vs 1

Maximum Concurrent Runs: 1 ensures idempotency and prevents race conditions. This is especially critical for streaming jobs, where multiple concurrent instances could lead to duplicate processing or inconsistent state.
Unlimited concurrent runs are suitable for batch jobs or multi-tenant pipelines, but can result in data corruption or processing conflicts in streaming scenarios.

Analysis of the Options:

A. Uses a New Job Cluster and Unlimited Retries, but allows Unlimited Concurrent Runs, which can cause problems in streaming environments. Not optimal.
B. Uses a New Job Cluster and restricts to a single run, but has no retries, making it fragile.
C. Uses an Existing All-Purpose Cluster, which is expensive and meant for shared usage, not dedicated streaming. Even though it has retries, it's not cost-efficient.
D. Uses a New Job Cluster, enables Unlimited Retries (for fault tolerance), and limits to 1 concurrent run (ensuring job integrity). This is the ideal production configuration.
E. Uses an All-Purpose Cluster and has no retries—the worst of both worlds.

Conclusion:

Option D is the best configuration for production Structured Streaming jobs. It ensures cost control by using a New Job Cluster, maintains resilience through Unlimited Retries, and prevents concurrency issues by limiting to a single concurrent run. This configuration strikes the right balance between robustness and efficiency in a production environment.

Question No 4:

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

A. The total average temperature across all sensors exceeded 120 on three consecutive executions of the query
B. The recent_sensor_recordings table was unresponsive for three consecutive runs of the query
C. The source query failed to update properly for three consecutive minutes and then restarted
D. The maximum temperature recording for at least one sensor exceeded 120 on three consecutive executions of the query
E. The average temperature recordings for at least one sensor exceeded 120 on three consecutive executions of the query

Answer: A

Explanation:

To understand this question, it's crucial to break down both the alert configuration and the behavior of the alert system within Databricks SQL.

The alert in question is based on a SQL query that calculates the mean(temperature) from the recent_sensor_recordings Delta Lake table. This table contains data for the last 5 minutes of sensor readings, and the alert condition is set to trigger if the mean temperature (averaged across all records returned by the query) is greater than 120°F. The alert refreshes every minute, and notifications are configured to be sent no more than once per minute.

The scenario states that notifications were triggered for 3 consecutive minutes and then stopped. Since alerts only send notifications when their trigger condition is met, we can deduce that for 3 consecutive executions (one per minute), the mean temperature across all sensor readings exceeded 120. After those 3 minutes, the condition was no longer met (i.e., the mean dropped below or equal to 120), so the notifications stopped.

Let’s analyze the answer choices:

A. This is correct. Since the query computes the mean temperature, and the condition is that it must be greater than 120, the alert being triggered three times in a row implies that this mean value exceeded 120 for those executions.
B. This is incorrect because if the table was unresponsive, the query would either fail or return no data. An unresponsive table would prevent the alert from being triggered at all, not cause it to fire.
C. This is incorrect. A failure in the source query or data refresh would cause either no alert or error messages, not a clean three-minute trigger followed by silence. The scenario explicitly mentions that alerts were triggered—so the query was functioning as intended.
D. This is incorrect. The alert is based on mean(temperature), not maximum temperature. A single sensor spiking above 120 would not necessarily raise the mean above 120 unless many other values were also high.
E. This is also incorrect because the query and alert are not grouped by sensor. The mean temperature is calculated across all sensor data returned by the query. Even if a single sensor's average was above 120, that wouldn't be sufficient unless the total average across all records exceeded 120.

Conclusion: The only answer that accurately reflects the configuration and behavior of the alerting system is A. The alert condition is tied to the overall average temperature of all sensor readings. If the alert triggered for three consecutive minutes, the total average (not sensor-specific) must have exceeded the threshold each time.

Question No 5:

Which approach will allow this developer to review the current logic for this notebook?

A. Use Repos to make a pull request use the Databricks REST API to update the current branch to dev-2.3.9
B. Use Repos to pull changes from the remote Git repository and select the dev-2.3.9 branch.
C. Use Repos to checkout the dev-2.3.9 branch and auto-resolve conflicts with the current branch.
D. Merge all changes back to the main branch in the remote Git repository and clone the repo again.
E. Use Repos to merge the current branch and the dev-2.3.9 branch, then make a pull request to sync with the remote repository.

Explanation:

To review the current logic of the notebook, the developer needs access to the correct branch that contains the desired code, in this case, dev-2.3.9. Since the developer is working in a personal branch with old logic, the issue lies in selecting the correct branch to view the latest changes.

Option A involves making a pull request and using the Databricks REST API to update the current branch to dev-2.3.9. While this sounds like a potential solution, it adds unnecessary complexity, as pulling from a Git repository should ideally be done directly via the Databricks Repos interface instead of using API calls to manually update the branch. Moreover, making a pull request without ensuring the right branch is available in the dropdown won't directly resolve the issue of selecting the correct branch.

Option B offers a more straightforward solution by suggesting that the developer uses the Databricks Repos interface to pull changes from the remote Git repository and select the dev-2.3.9 branch. This is the most efficient way to ensure the developer can access the desired logic without any unnecessary steps. Pulling the changes ensures that the local environment is up to date with the remote repository, and selecting the correct branch will enable the developer to see the latest version of the code.

Option C is not ideal because while checking out the dev-2.3.9 branch would allow the developer to review the correct logic, auto-resolving conflicts could lead to undesired changes or errors in the notebook's code. The developer should first pull the branch and manually resolve any conflicts if necessary, but auto-resolution may not ensure the desired outcome.

Option D suggests merging all changes back to the main branch and cloning the repository again. This is an unnecessary and overly complicated solution. Merging changes and cloning the repo again is not necessary to simply access and review the correct branch. This approach introduces more steps and complexity than needed for the task at hand.

Option E involves merging the current branch with the dev-2.3.9 branch and making a pull request. While this would eventually sync the changes, it is not the best solution for reviewing the logic immediately. The developer should simply pull the correct branch first before considering any merges.

The most effective and direct solution is B, where the developer uses Repos to pull the latest changes and select the desired branch dev-2.3.9 to review the current logic.

Question No 6:

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?

A. The connection to the external table will fail; the string "REDACTED" will be printed.
B. An 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.
C. An 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.
D. The connection to the external table will succeed; the string value of password will be printed in plain text.
E. The connection to the external table will succeed; the string "REDACTED" will be printed.

Answer: E

Explanation:

In this scenario, the security team is using the Databricks secrets module to securely store and access sensitive information, like passwords. The secrets module is designed to ensure that secrets (such as passwords or API keys) are never printed in plain text, offering an extra layer of security.

Let's analyze each option:

A. The connection to the external table will fail; the string "REDACTED" will be printed.

This option suggests the connection will fail, but the use of the secrets module doesn't inherently cause failures. If the password is properly retrieved and the connection configuration is correct, the connection should succeed. Additionally, the secrets module doesn't output the string "REDACTED" unless a secret access issue arises. Thus, this option is unlikely.

B. An 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.

The secrets module doesn't typically require an interactive input box to retrieve a password. It's designed for automated access without manual intervention. Once the secret is set up, it’s automatically fetched by the code, and there's no step where the password is saved back to DBFS (Databricks File System). So, this option is incorrect.

C. An 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.

The secrets module is designed to prevent printing sensitive information like passwords in plain text. If a password is retrieved from the secrets store, it won't be printed to the screen. Therefore, an interactive input box is unnecessary, and plain text printing of the password would not happen. This is not the correct behavior of the secrets module.

D. The connection to the external table will succeed; the string value of password will be printed in plain text.

This is incorrect. As mentioned earlier, the Databricks secrets module ensures that sensitive data like passwords is not exposed. If the password is stored in the secrets module and accessed correctly, it won’t print the password in plain text.

E. The connection to the external table will succeed; the string "REDACTED" will be printed.

This is the correct behavior. When using the secrets module, if you access the secret (such as the password) in your code, Databricks will mask the value and print "REDACTED" instead of exposing the actual secret. This is done to protect sensitive information from being inadvertently displayed in logs or output. Therefore, while the connection will succeed, the string "REDACTED" will be printed in the logs to prevent exposure of the actual password.

Conclusion:
The Databricks secrets module ensures that passwords and other sensitive data are securely handled. If the secrets are correctly configured and accessed, the system will mask the actual secret value and display "REDACTED". This is done to maintain security and avoid exposing sensitive information in the notebook’s output.

Question No 7:

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: image7. 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?

A. Each write to the orders table will only contain unique records, and only those records without duplicates in the target table will be written.
B. Each write to the orders table will only contain unique records, but newly written records may have duplicates already present in the target table.
C. Each write to the orders table will only contain unique records; if existing records with the same key are present in the target table, these records will be overwritten.
D. Each write to the orders table will only contain unique records; if existing records with the same key are present in the target table, the operation will fail.
E. Each write to the orders table will run deduplication over the union of new and existing records, ensuring no duplicate records are present.

Answer: E

Explanation:

In scenarios where the upstream system produces duplicate entries for the same order (as indicated by the composite key of customer_id and order_id), it’s crucial to handle data ingestion and deduplication in a way that ensures unique records are written into the target table, regardless of the possible duplicates.

Let's break down the options:

Option A: Each write to the orders table will only contain unique records, and only those records without duplicates in the target table will be written.
This option suggests that only non-duplicate records will be written. However, this statement is misleading because it assumes that deduplication is performed only on the target table, meaning records might still have duplicates in the batch itself. It doesn’t account for the fact that duplicate records might already exist in the batch before they are written. Therefore, this option is incorrect because it doesn't address the deduplication of both incoming and existing records.
Option B: Each write to the orders table will only contain unique records, but newly written records may have duplicates already present in the target table.
This option acknowledges the possibility of duplicates already being present in the target table, but it doesn't address how the system would handle these duplicates. Simply ensuring that new records are unique doesn't prevent the creation of duplicate records if the upstream system has already produced duplicates. Thus, it does not solve the problem entirely. Therefore, this option is incorrect.
Option C: Each write to the orders table will only contain unique records; if existing records with the same key are present in the target table, these records will be overwritten.
While this option addresses the presence of existing records and suggests that they will be overwritten, it doesn't provide a full solution to the deduplication problem. Overwriting existing records could lead to data loss, especially in cases where the new records represent different or more recent data that should be retained. This option doesn’t fully handle the deduplication of incoming records, so it's incorrect.
Option D: Each write to the orders table will only contain unique records; if existing records with the same key are present in the target table, the operation will fail.
This option suggests that the operation would fail if duplicate records are found in the target table, which is an overly restrictive approach. Instead of failing the operation, the system should be designed to handle duplicates gracefully, either by deduplicating or by applying business rules for record updating. Therefore, this option is incorrect.
Option E: Each write to the orders table will run deduplication over the union of new and existing records, ensuring no duplicate records are present.
This is the correct option because it addresses the core issue of duplicate records in both the incoming batch and the existing data in the target table. By performing deduplication over the combined set of new and existing records, it ensures that only unique records are retained, preventing duplication in the final dataset. This approach efficiently handles the scenario where the upstream system occasionally produces duplicates for the same order, ensuring that the final orders table will contain no duplicates.

Conclusion:
The best solution for ensuring that no duplicate records are present in the orders table after ingestion is to use deduplication over both the new and existing records. This ensures that even if the upstream system produces duplicates, the target table will only contain unique entries.Would you like to see an example of how to implement this deduplication process in a specific environment like Databricks or another tool?

Question No 8:

Which statement correctly describes the outcome of executing these command cells in order in an interactive notebook?

A. Both commands will succeed. Executing show tables will show that countries_af and sales_af have been registered as views.
B. Cmd 1 will succeed. Cmd 2 will search all accessible databases for a table or view named countries_af: if this entity exists, Cmd 2 will succeed.
C. Cmd 1 will succeed and Cmd 2 will fail. countries_af will be a Python variable representing a PySpark DataFrame.
D. Both commands will fail. No new variables, tables, or views will be created.
E. Cmd 1 will succeed and Cmd 2 will fail. countries_af will be a Python variable containing a list of strings.

Answer: C

Explanation:

To answer this question, let's break down the commands and their implications in the context of Databricks notebooks:

Cmd 1 is a PySpark command that creates a view named countries_af based on the data in the geo_lookup table, filtering for countries on the African continent. This command will register countries_af as a temporary view or DataFrame in the current session, which will be used for querying in SQL.
Cmd 2 is a SQL command that creates a view named sales_af, using data from the sales table and joining it with countries_af (the view created in Cmd 1). This command assumes countries_af exists in the current database context, which is a view registered by Cmd 1.

Now, let’s analyze the options:

Option A is incorrect because the first command will succeed in creating a view or temporary DataFrame, but the second command will fail due to the behavior of Databricks in the interactive notebook. The view created in Cmd 1 is not automatically accessible in SQL context directly. Instead, it would be treated as a Python variable containing a PySpark DataFrame, not a SQL view.
Option B is incorrect because Cmd 2 does not search all accessible databases automatically. If countries_af is not registered as a valid view in the SQL context (as it is treated as a PySpark DataFrame), Cmd 2 will fail even if it exists in a different database.
Option C is correct. Cmd 1 will succeed and create a PySpark DataFrame that can be referenced in Python, but not directly in SQL. Cmd 2 will fail because it expects countries_af to be a view in the SQL context, but countries_af is a Python variable, not a SQL view. Therefore, countries_af will be treated as a PySpark DataFrame, causing Cmd 2 to fail.
Option D is incorrect because Cmd 1 will succeed in creating a PySpark DataFrame (even though it won't be accessible in SQL), and Cmd 2 will fail due to the issue with SQL context.
Option E is incorrect. While Cmd 1 will succeed, the output will not be a list of strings but a PySpark DataFrame. Furthermore, Cmd 2 will fail for the reasons explained.

In conclusion, the most accurate description of what will happen is C because Cmd 1 creates a PySpark DataFrame (not a SQL view) and Cmd 2 fails because it expects a SQL view, not a Python variable.

Question No 9:

Which statement describes how the Delta engine identifies which files to load?

A. All records are cached to an operational database and then the filter is applied
B. The Parquet file footers are scanned for min and max statistics for the latitude column
C. All records are cached to attached storage and then the filter is applied
D. The Delta log is scanned for min and max statistics for the latitude column
E. The Hive metastore is scanned for min and max statistics for the latitude column

Explanation:

In Delta Lake, optimization techniques like partition pruning and predicate pushdown are used to efficiently filter the data and minimize the amount of data loaded into memory. The query filter latitude > 66.3 is a predicate filter that would ideally be pushed down to the underlying storage to avoid loading unnecessary data.

Option A suggests that all records are cached to an operational database before the filter is applied. However, caching all records would be inefficient and contrary to how Delta Lake optimizes data access. The Delta engine does not cache all records before applying filters; instead, it works by pruning unnecessary files based on metadata like partitioning and statistics.

Option B proposes that the Parquet file footers are scanned for min and max statistics for the latitude column. This is the correct approach because Delta Lake utilizes the statistics stored in the Parquet file footers to optimize query execution. When querying partitioned data, the Delta engine can use these statistics (such as the minimum and maximum values for a given column) to identify which files contain relevant data based on the filter condition. In this case, the engine can skip over files that don't contain records where latitude > 66.3, improving query performance.

Option C indicates that all records are cached to attached storage, which is not how Delta Lake operates. Delta Lake focuses on minimizing data retrieval by leveraging metadata and statistics, not by caching all records to storage.

Option D suggests that the Delta log is scanned for min and max statistics for the latitude column. While the Delta log tracks changes to the Delta table, it does not typically store min and max statistics for individual columns. The Delta log helps track changes like inserts, deletes, and updates, but for filtering data based on specific columns, the Delta engine relies on the Parquet file footers or the underlying metastore.

Option E implies that the Hive metastore is scanned for min and max statistics for the latitude column. While the Hive metastore contains metadata about the table schema and partitions, it does not typically store column-level statistics like min and max values for individual columns. The Delta engine relies on the Parquet file footers for this kind of optimization.

The most accurate description of how the Delta engine identifies which files to load is B, as it leverages the min and max statistics stored in the Parquet file footers to optimize the filtering process.