Certified Data Engineer Associate Exam Questions

In Databricks, the transfer of ownership for Delta tables in Data Explorer must be performed by a Workspace administrator. Since the original data engineer no longer has access, only an admin with the necessary permissions can transfer ownership of the Delta tables to another user, in this case, the new lead data engineer.

• A. Databricks account representative: This role typically deals with account-level issues and might not have the necessary permissions to transfer ownership of Delta tables within the Databricks workspace.

• B. This transfer is not possible: This is not true; the transfer is possible, but it requires the right permissions, which a workspace administrator has.

• C. Workspace administrator: A workspace admin has the permissions required to transfer ownership of the Delta tables in the absence of the original owner.

• D. New lead data engineer: Although the new lead engineer is the designated owner, they cannot transfer ownership unless they have the necessary admin privileges.

• E. Original data engineer: The original data engineer cannot perform the transfer since they no longer have access to the workspace.

In PySpark, to access a Delta table (or any table registered in the Spark session), you use the spark.table() method, which allows you to load the table into a DataFrame that can be used for further analysis or testing. This method is commonly used in the data engineering workflow for accessing and manipulating data.

1. A. DESCRIBE LOCATION customer360;: This is not a valid command in Databricks or Spark SQL for describing a database's location.

2. B. DROP DATABASE customer360;: This command would delete the database, not describe or return its location.

3. C. DESCRIBE DATABASE customer360;: This is the correct command to retrieve metadata about the database, including its location.

4. D. ALTER DATABASE customer360 SET DBPROPERTIES ('location' = '/user');: This command is used to alter the properties of a database, but it is not used to retrieve the current location.

5. E. USE DATABASE customer360;: This command sets the current database to customer360 but does not return the location.

CREATE TABLE my_table (id INT COMMENT 'Unique Identification Number', name STRING COMMENT 'PII', age INT COMMENT 'PII')

TBLPROPERTIES ('contains_pii'=True)

COMMENT 'Contains PII';

Spark SQL provides array functions that are particularly useful for handling and manipulating complex, nested data types, such as arrays within JSON files. These functions allow you to perform operations on arrays, including extracting, transforming, and manipulating the data efficiently.

• A. An ability to work with data in a variety of types at once: This is more related to the flexibility of Spark SQL and not specifically to array functions.

• B. An ability to work with data within certain partitions and windows: This is more related to window functions in Spark SQL, not specifically to array functions.

• C. An ability to work with time-related data in specified intervals: This is related to time functions in Spark SQL, not array functions.

• D. An ability to work with complex, nested data ingested from JSON files: This is correct because Spark's array functions are particularly useful when dealing with nested data structures like those found in JSON files, where arrays are commonly used.

• E. An ability to work with an array of tables for procedural automation: This is not directly related to Spark SQL array functions.

The MERGE command is used to perform Upserts (insert, update, or delete operations) into a Delta table, allowing you to avoid writing duplicate records. It matches records in the source data to records in the target Delta table based on a specified condition (such as a primary key), and then performs actions like updating existing records, inserting new records, or deleting conflicting records.

• A. DROP: This command is used to drop (delete) a table or a database, not for inserting data.

• B. IGNORE: This is not a valid operation in the context of Delta tables for handling duplicates.

• C. MERGE: The MERGE command is the right choice for avoiding duplicates, as it allows for conditional inserts and updates.

• D. APPEND: The APPEND operation simply adds new data to a Delta table without checking for duplicates, which could result in duplicated records.

• E. INSERT: The INSERT command adds data to a table, but it does not have built-in functionality to check for and avoid duplicates.

A is the correct choice because it follows the correct syntax for creating a SQL function. It uses CREATE FUNCTION, defines the input parameter (city STRING), specifies the return type (RETURNS STRING), and uses a valid RETURN statement with a CASE expression to provide the logic.

In this scenario, the data engineer can use PySpark along with Python’s control flow (like if statements) to control when the final query in the program runs. By writing a Python script, they can execute all queries and use the current day of the week to conditionally run the final query only on Sundays. This allows the program to run every day, with the final query being restricted to Sundays based on the control flow logic in Python.

The COPY INTO statement in Databricks is used to load data from external files into a Delta table. In this case, if the data engineer is running the command every day to load the sales data into the transactions table, but the number of records has not changed, it's likely because the data for the previous day has already been copied into the table, so there are no new records to insert.

Here’s why the other options are not correct:

• A. The format of the files to be copied were not included with the FORMAT_OPTIONS keyword.: The file format (PARQUET in this case) is already specified in the FILEFORMAT = PARQUET clause, so there is no need for additional format options.

• B. The names of the files to be copied were not included with the FILES keyword.: The COPY INTO command doesn't require explicitly specifying individual file names if the entire directory (like "/transactions/raw") is provided. It will automatically load all files in the specified directory.

• D. The PARQUET file format does not support COPY INTO.: This is incorrect because PARQUET is supported by COPY INTO. It is a common and supported format for data ingestion.

• E. The COPY INTO statement requires the table to be refreshed to view the copied rows.: This is not true. COPY INTO automatically updates the table with the new data, so there is no need to refresh the table manually to view the newly copied records.

To create a table in Databricks from an SQLite database, the USING clause must specify JDBC, as JDBC is the standard method for connecting to relational databases like SQLite.

Correct SQL syntax:

sql
Copier le codeCREATE TABLE jdbc_customer360 
USING org.apache.spark.sql.jdbc 
OPTIONS (
 url "jdbc:sqlite:/customers.db", 
 dbtable "customer360"
);

This tells Databricks to use the JDBC connector to retrieve data from the SQLite database.