Databricks DATABRICKS-CERTIFIED-DATA-ENGINEER-ASSOCIATE Online Practice Questions and Exam Preparation

Databricks DATABRICKS-CERTIFIED-DATA-ENGINEER-ASSOCIATE Online Questions & Answers

• Question 161:

  A data engineer has created a new database using the following command:

  CREATE DATABASE IF NOT EXISTS customer360;

  In which of the following locations will the customer360 database be located?

  A. dbfs:/user/hive/database/customer360
  B. dbfs:/user/hive/warehouse
  C. dbfs:/user/hive/customer360
  D. More information is needed to determine the correct response
  B. dbfs:/user/hive/warehouse

  ---

  Explanation

  dbfs:/user/hive/warehouse Thereby showing "dbfs:/user/hive/warehouse/customer360.db

  The location of the customer360 database depends on the value of the spark.sql.warehouse.dir configuration property, which specifies the default location for managed databases and tables. If the property is not set, the default value is dbfs:/

  user/hive/warehouse. Therefore, the customer360 database will be located in dbfs:/user/hive/warehouse/customer360.db. However, if the property is set to a different value, such as dbfs:/user/hive/database, then the customer360 database

  will be located in dbfs:/user/hive/database/customer360.db. Thus, more information is needed to determine the correct response. Option A is not correct, as dbfs:/user/hive/database/customer360 is not the default location for managed

  databases and tables, unless the spark.sql.warehouse.dir property is explicitly set to dbfs:/user/hive/database. Option B is not correct, as dbfs:/user/hive/warehouse is the default location for the root directory of managed databases and

  tables, not for a specific database. The database name should be appended with .db to the directory path, such as dbfs:/user/hive/warehouse/customer360.db.

  Option C is not correct, as dbfs:/user/hive/customer360 is not a valid location for a managed database, as it does not follow the directory structure specified by the spark.sql.warehouse.dir property.

  References:

  Databases and Tables

  [Databricks Data Engineer Professional Exam Guide]

• Question 162:

  A data analysis team has noticed that their Databricks SQL queries are running too slowly when connected to their always-on SQL endpoint. They claim that this issue is present when many members of the team are running small queries simultaneously. They ask the data engineering team for help. The data engineering team notices that each of the team's queries uses the same SQL endpoint.

  Which of the following approaches can the data engineering team use to improve the latency of the team's queries?

  A. They can increase the cluster size of the SQL endpoint.
  B. They can increase the maximum bound of the SQL endpoint's scaling range.
  C. They can turn on the Auto Stop feature for the SQL endpoint.
  D. They can turn on the Serverless feature for the SQL endpoint.
  E. They can turn on the Serverless feature for the SQL endpoint and change the Spot Instance Policy to "Reliability Optimized."
  B. They can increase the maximum bound of the SQL endpoint's scaling range.

  ---

  Explanation

  https://community.databricks.com/t5/data-engineering/sequential-vs-concurrency-optimization-questions-from-query/td-p/36696

• Question 163:

  Which of the following Git operations must be performed outside of Databricks Repos?

  A. Commit
  B. Pull
  C. Push
  D. Clone
  E. Merge
  E. Merge

  ---

  Explanation

  Databricks Repos is a visual Git client and API in Databricks that supports common Git operations such as commit, pull, push, branch management, and visual comparison of diffs when committing1. However, merge is not supported in the Git dialog2. You need to use the Repos UI or your Git provider to merge branches3. Merge is a way to combine the commit history from one branch into another branch1. During a merge, a merge conflict is encountered when Git cannot automatically combine code from one branch into another. Merge conflicts require manual resolution before a merge can be completed1.

  References: 4: Run Git operations on Databricks Repos4, 1: CI/CD techniques with Git and Databricks Repos1, 3: Collaborate in Repos3, 2: Databricks Repos

  -What it is and how we can use it2. Databricks Repos is a visual Git client and API in Databricks that supports common Git operations such as commit, pull, push, merge, and branch management. However, to clone a remote Git repository to a Databricks repo, you must use the Databricks UI or API. You cannot clone a Git repo using the CLI through a cluster's web terminal, as the files won't display in the Databricks UI1.

  References: 1: Run Git operations on Databricks Repos | Databricks on AWS2

• Question 164:

  A new data engineering team has been assigned to work on a project. The team will need access to database customers in order to see what tables already exist. The team has its own group team. Which of the following commands can be used to grant the necessary permission on the entire database to the new team?

  A. GRANT VIEW ON CATALOG customers TO team;
  B. GRANT CREATE ON DATABASE customers TO team;
  C. GRANT USAGE ON CATALOG team TO customers;
  D. GRANT CREATE ON DATABASE team TO customers;
  E. GRANT USAGE ON DATABASE customers TO team;
  E. GRANT USAGE ON DATABASE customers TO team;

  ---

  Explanation

  The correct command to grant the necessary permission on the entire database to the new team is to use the GRANT USAGE command. The GRANT USAGE command grants the principal the ability to access the securable object, such as a database, schema, or table. In this case, the securable object is the database customers, and the principal is the group team. By granting usage on the database, the team will be able to see what tables already exist in the database. Option E is the only option that uses the correct syntax and the correct privilege type for this scenario. Option A uses the wrong privilege type (VIEW) and the wrong securable object (CATALOG). Option B uses the wrong privilege type (CREATE), which would allow the team to create new tables in the database, but not necessarily see the existing ones. Option C uses the wrong securable object (CATALOG) and the wrong principal (customers). Option D uses the wrong securable object (team) and the wrong principal (customers).

  References: GRANT, Privilege types, Securable objects, Principals

• Question 165:

  Which of the following describes a scenario in which a data engineer will want to use a single-node cluster?

  A. When they are working interactively with a small amount of data
  B. When they are running automated reports to be refreshed as quickly as possible
  C. When they are working with SQL within Databricks SQL
  D. When they are concerned about the ability to automatically scale with larger data
  E. When they are manually running reports with a large amount of data
  A. When they are working interactively with a small amount of data

  ---

  Explanation

  The scenario in which a data engineer will want to use a single-node cluster is when they are working interactively with a small amount of data. A single-node cluster is a cluster consisting of an Apache Spark driver and no Spark workers1. A single-node cluster supports Spark jobs and all Spark data sources, including Delta Lake1. A single- node cluster is helpful for single-node machine learning workloads that use Spark to load and save data, and for lightweight exploratory data analysis1. A single-node cluster can run Spark locally, spawn one executor thread per logical core in the cluster, and save all log output in the driver log1. A single-node cluster can be created by selecting the Single Node button when configuring a cluster1. The other options are not suitable for using a single-node cluster. When running automated reports to be refreshed as quickly as possible, a data engineer will want to use a multi-node cluster that can scale up and down automatically based on the workload demand2. When working with SQL within Databricks SQL, a data engineer will want to use a SQL Endpoint that can execute SQL queries on a serverless pool or an existing cluster3. When concerned about the ability to automatically scale with larger data, a data engineer will want to use a multi-node cluster that can leverage the Databricks Lakehouse Platform and the Delta Engine to handle large-scale data processing efficiently and reliably4. When manually running reports with a large amount of data, a data engineer will want to use a multi-node cluster that can distribute the computation across multiple workers and leverage the Spark UI to monitor the performance and troubleshoot the issues.

  References:

  1: Single Node clusters | Databricks on AWS

  2: Autoscaling | Databricks on AWS

  3: SQL Endpoints | Databricks on AWS

  4: Databricks Lakehouse Platform | Databricks on AWS : [Spark UI | Databricks on AWS]

• Question 166:

  A Data Engineer is building a simple data pipeline using Delta Live Tables (DLT) in Databricks to ingest customer data. The raw customer data is stored in a cloud storage location in JSON format. The task is to create a DLT pipeline that reads the raw JSON data and writes it into a Delta table for further processing.

  Which code snippet will correctly ingest the raw JSON data and create a Delta table using DLT?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

  ---

  Explanation

  The correct approach is to use @dlt.table to define a Delta Live Table and read the raw JSON data with spark.read.json("s3://..."). This properly ingests the JSON source data and materializes it as a managed Delta table in DLT.

• Question 167:

  Which of the following SQL keywords can be used to convert a table from a long format to a wide format?

  A. PIVOT
  B. CONVERT
  C. WHERE
  D. TRANSFORM
  E. SUM
  A. PIVOT

  ---

  Explanation

  The SQL keyword that can be used to convert a table from a long format to a wide format is PIVOT. The PIVOT clause is used to rotate the rows of a table into columns of a new table1. The PIVOT clause can aggregate the values of a column based on the distinct values of another column, and use those values as the column names of the new table1. The PIVOT clause can be useful for transforming data from a long format, where each row represents an observation with multiple attributes, to a wide format, where each row represents an observation with a single attribute and multiple values2. For example, the PIVOT clause can be used to convert a table that contains the sales of different products by different regions into a table that contains the sales of each product by each region as separate columns1. The other options are not suitable for converting a table from a long format to a wide format. CONVERT is a function that can be used to change the data type of an expression3. WHERE is a clause that can be used to filter the rows of a table based on a condition4. TRANSFORM is a keyword that can be used to apply a user-defined function to a group of rows in a table5. SUM is a function that can be used to calculate the total of a numeric column.

  References:

  1: PIVOT | Databricks on AWS

  2: Reshaping Data - Long vs Wide Format | Databricks on AWS

  3: CONVERT | Databricks on AWS

  4: WHERE | Databricks on AWS

  5: TRANSFORM | Databricks on AWS : [SUM | Databricks on AWS]

• Question 168:

  A data engineer is debugging a Python notebook in Databricks that processes a dataset using PySpark. The notebook fails with an error during a DataFrame transformation. The engineer wants to inspect the state of variables, such as the input DataFrame and intermediate results, to identify where the error occurs.

  Which tool should the engineer use to debug the notebook and inspect the values of variables like DataFrames?

  A. Use the Databricks CLI to download and analyze driver logs for detailed error messages
  B. Use the Python Notebook Interactive Debugger to set breakpoints and inspect variable values in real- time
  C. Use the Ganglia UI to monitor cluster resource usage and identify hardware issues
  D. Use the Spark UI to analyze the execution plan and identify stages where the job failed
  B. Use the Python Notebook Interactive Debugger to set breakpoints and inspect variable values in real- time

  ---

  Explanation

  The Python Notebook Interactive Debugger in Databricks allows setting breakpoints and inspecting variable values, including DataFrames, in real time. This makes it the correct tool for debugging transformation errors in a PySpark notebook.

• Question 169:

  Which feature of Delta Live Tables ensures that data quality rules automatically block bad records?

  A. Auto Loader checkpoints
  B. Constraints with ON VIOLATION FAIL UPDATE
  C. Optimized file compaction
  D. Notebook scheduling
  E. Schema evolution
  B. Constraints with ON VIOLATION FAIL UPDATE

  ---

  Explanation

  DLT's EXPECT ... ON VIOLATION FAIL UPDATE enforces strict constraints--if violated, the pipeline run fails.

• Question 170:

  A data engineer is configuring Unity Catalog in Databricks and needs to assign a role to a user who should have the ability to grant and revoke privileges on various data objects within a specific schema, but should not have read/write access over the schema or its objects.

  Which role should the data engineer assign to this user?

  A. Table Owner
  B. Catalog Owner
  C. Schema Owner
  D. USE catalog/schema privilege on the schema
  C. Schema Owner

  ---

  Explanation

  The Schema Owner role in Unity Catalog allows a user to grant and revoke privileges on data objects within the schema without requiring read/write access to the schema's data itself, fitting the requirement.