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

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

• Question 11:

  How do you grant all privileges on a table to a group in Databricks SQL?

  A. GRANT ALL TO group ON TABLE
  B. GRANT FULL ACCESS TO group ON TABLE
  C. GRANT ALL PRIVILEGES ON TABLE table_name TO group
  D. GRANT USAGE ON TABLE TO group
  E. GRANT MODIFY ON TABLE TO group
  C. GRANT ALL PRIVILEGES ON TABLE table_name TO group

  ---

  Explanation

  GRANT ALL PRIVILEGES ON TABLE gives full control to the group on the table.

• Question 12:

  Which of the following commands will return the location of database customer360?

  A. DESCRIBE LOCATION customer360;
  B. DROP DATABASE customer360;
  C. DESCRIBE DATABASE customer360;
  D. ALTER DATABASE customer360 SET DBPROPERTIES ('location' = '/user'};
  E. USE DATABASE customer360;
  C. DESCRIBE DATABASE customer360;

  ---

  Explanation

  The command DESCRIBE DATABASE customer360; will return the location of the database customer360, along with its comment and properties. This command is an alias for DESCRIBE SCHEMA customer360;, which can also be used to get the same information. The other commands will either drop the database, alter its properties, or use it as the current database, but will not return its location.

  References: DESCRIBE DATABASE | Databricks on AWS DESCRIBE DATABASE - Azure Databricks - Databricks SQL

• Question 13:

  A data engineer has realized that the data files associated with a Delta table are incredibly small. They want to compact the small files to form larger files to improve performance.

  Which of the following keywords can be used to compact the small files?

  A. REDUCE
  B. OPTIMIZE
  C. COMPACTION
  D. REPARTITION
  E. VACUUM
  B. OPTIMIZE

  ---

  Explanation

  The keyword that can be used to compact the small files associated with a Delta table is OPTIMIZE. The OPTIMIZE command performs file compaction on a Delta table by rewriting a set of small files into a set of larger files1. This can improve the performance of queries that scan the table by reducing the number of files that need to be read and the amount of metadata that needs to be processed1. The OPTIMIZE command can also optionally sort the data within each file by a given set of columns, which can further improve the query performance by enabling data skipping and predicate pushdown1. The OPTIMIZE command can be applied to the whole table or to a specific partition of the table1. The other keywords are not suitable for compacting the small files associated with a Delta table. REDUCE is a keyword used in the SQL syntax for aggregating data using a user- defined function2. COMPACTION is not a valid keyword in SQL or Python. REPARTITION is a keyword used in the Python syntax for changing the number of partitions of a DataFrame or an RDD3. VACUUM is a keyword used to remove files that are no longer referenced by a Delta table and are older than a retention threshold4.

  References:

  1: OPTIMIZE | Databricks on AWS

  2: REDUCE | Databricks on AWS

  3: repartition | Databricks on AWS

  4: VACUUM | Databricks on AWS

• Question 14:

  A data engineer needs to conduct Exploratory Analysis on data residing in a database that is within the company's custom-defined network in the cloud. The data engineer is using SQL for this task. Which type of SQL Warehouse will enable the data engineer to process large numbers of queries quickly and cost-effectively?

  A. Serverless compute for notebooks
  B. Pro SQL Warehouse
  C. Classic SQL Warehouse
  D. Serverless SQL Warehouse
  B. Pro SQL Warehouse

  ---

  Explanation

  A Pro SQL Warehouse is designed for high-performance, cost-effective query execution at scale. It is optimized for running large volumes of queries quickly, making it ideal for exploratory analysis on enterprise datasets.

• Question 15:

  A data engineering team has noticed that their Databricks SQL queries are running too slowly when they are submitted to a non-running SQL endpoint. The data engineering team wants this issue to be resolved. Which of the following approaches can the team use to reduce the time it takes to return results in this scenario?

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

  ---

  Explanation

  Option D is the correct answer because it enables the Serverless feature for the SQL endpoint, which allows the endpoint to automatically scale up and down based on the query load. This way, the endpoint can handle more concurrent queries and reduce the time it takes to return results. The Serverless feature also reduces the cold start time of the endpoint, which is the time it takes to start the cluster when a query is submitted to a non- running endpoint. The Serverless feature is available for both AWS and Azure Databricks platforms.

  References: Databricks SQL Serverless, Serverless SQL endpoints, New Performance Improvements in Databricks SQL

• Question 16:

  A data engineer has been provided a PySpark DataFrame named df with columns product and revenue. The data engineer needs to compute complex aggregations to determine each product's total revenue, average revenue, and transaction count.

  Which code snippet should the data engineer use?

  

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

  ---

  Explanation

  The correct code uses PySpark functions (sum, avg, count) with aliases inside .agg() after grouping by product. This produces total revenue, average revenue, and transaction count for each product.

• Question 17:

  Which of the following tools is used by Auto Loader process data incrementally?

  A. Checkpointing
  B. Spark Structured Streaming
  C. Data Explorer
  D. Unity Catalog
  E. Databricks SQL
  B. Spark Structured Streaming

  ---

  Explanation

  Auto Loader provides a Structured Streaming source called cloudFiles that can process new data files as they arrive in cloud storage without any additional setup. Auto Loader uses a scalable key-value store to track ingestion progress and ensure exactly-once semantics. Auto Loader can ingest various file formats and load them into Delta Lake tables. Auto Loader is recommended for incremental data ingestion with Delta Live Tables, which extends the functionality of Structured Streaming and allows you to write declarative Python or SQL code to deploy a production-quality data pipeline.

  References: What is Auto Loader?, What is Auto Loader? | Databricks on AWS, Solved: How does Auto Loader ingest data? - Databricks - 5629

• Question 18:

  Which of the following benefits is provided by the array functions from Spark SQL?

  A. An ability to work with data in a variety of types at once
  B. An ability to work with data within certain partitions and windows
  C. An ability to work with time-related data in specified intervals
  D. An ability to work with complex, nested data ingested from JSON files
  E. An ability to work with an array of tables for procedural automation
  D. An ability to work with complex, nested data ingested from JSON files

  ---

  Explanation

  The array functions from Spark SQL are a subset of the collection functions that operate on array columns. They provide an ability to work with complex, nested data ingested from JSON files or other sources. For example, the explode

  function can be used to transform an array column into multiple rows, one for each element in the array. The array_contains function can be used to check if a value is present in an array column. The array_join function can be used to

  concatenate all elements of an array column with a delimiter. These functions can be useful for processing JSON data that may have nested arrays or objects.

  References:

  1: Spark SQL, Built-in Functions - Apache Spark

  2: Spark SQL Array Functions Complete List - Spark By Examples

  3: Spark SQL Array Functions - Syntax and Examples - DWgeek.com

  4: Spark SQL, Built-in Functions - Apache Spark : Spark SQL, Built-in Functions - Apache Spark : [Working with Nested Data Using Higher Order Functions in SQL on Databricks - The Databricks Blog]

• Question 19:

  A data engineer needs to create a table in Databricks using data from their organization's existing SQLite database.

  They run the following command:

  

  Which of the following lines of code fills in the above blank to successfully complete the task?

  A. org.apache.spark.sql.jdbc
  B. autoloader
  C. DELTA
  D. sqlite
  E. org.apache.spark.sql.sqlite
  A. org.apache.spark.sql.jdbc

  ---

  Explanation

• Question 20:

  A data engineer is reviewing the documentation on audit logs in Databricks for compliance purposes and needs to understand the format in which audit logs output events. How are events formatted in Databricks audit logs?

  A. In Databricks, audit logs output events in a JSON format.
  B. In Databricks, audit logs output events in a CSV format.
  C. In Databricks, audit logs output events in an XML format.
  D. In Databricks, audit logs output events in a plain text format.
  A. In Databricks, audit logs output events in a JSON format.

  ---

  Explanation

  Databricks audit logs output events in JSON format, which provides structured, machine-readable data suitable for compliance monitoring and integration with security tools.