View Full Databricks Certified Data Engineer Associate Exam Dumps and Practice Test Dumps.

Question 161

A data engineering team wants to maintain multiple historical versions of a customer’s address rather than replacing the previous value. Which approach is most appropriate?

1. SCD Type 2
2. SCD Type 1
3. Full table overwrite
4. Temporary view

Correct Answer: 1

Explanation

Slowly Changing Dimension Type 2 preserves historical versions of dimension records rather than replacing previous values. When a customer’s address changes, the existing record can be closed with an end date or equivalent indicator, while a new record is inserted for the updated address. This allows analysts to determine which address was valid during a particular period. SCD Type 1 instead replaces the previous value. A full overwrite and temporary view do not provide the required historical tracking behavior.

Question 162

Which column is commonly used as the matching condition when implementing a MERGE for customer records?

1. A descriptive city name
2. A stable business key such as customer_id
3. The ingestion timestamp alone
4. A randomly generated value for every load

Correct Answer: 2

Explanation

A stable business key, such as customer_id, is commonly used to match source records with corresponding target records during a MERGE operation. The key should identify the same business entity consistently across data loads. A descriptive field such as city may not uniquely identify a customer, while an ingestion timestamp can change for every batch. A newly generated value for each load would prevent reliable matching. Using an appropriate business key enables accurate updates and inserts.

Question 163

A pipeline receives duplicate records from an upstream system, and each record contains a unique transaction identifier. Which technique can remove duplicate transactions?

1. groupBy without aggregation
2. orderBy
3. dropDuplicates([“transaction_id”])
4. printSchema

Correct Answer: 3

Explanation

dropDuplicates([“transaction_id”]) removes duplicate rows based on the specified transaction identifier. This is useful when the same business event may arrive more than once from an upstream source. The transaction ID provides a basis for determining which records represent the same logical event. groupBy is generally used for aggregation, orderBy sorts data, and printSchema displays the DataFrame structure. Deduplicating on the appropriate business key helps prevent repeated transactions from entering downstream datasets.

Question 164

A data engineer wants to combine two DataFrames containing the same set of columns and compatible data types by adding the rows from one below the other. Which operation should be used?

1. join()
2. filter()
3. select()
4. union()

Correct Answer: 4

Explanation

The union() operation combines rows from two compatible DataFrames into a single DataFrame. The DataFrames should have compatible schemas, with corresponding columns positioned appropriately. A join combines datasets based on matching keys or conditions, while filter removes rows that do not meet a condition and select chooses or derives columns. Therefore, when the requirement is to stack records from two datasets with compatible structures, union is the appropriate DataFrame operation.

Question 165

Which Spark transformation is most appropriate for keeping only records where the order status is “Completed”?

1. filter()
2. count()
3. collect()
4. explain()

Correct Answer: 1

Explanation

The filter() transformation is used to retain only rows that satisfy a specified condition. For example, a DataFrame can be filtered with a condition such as status == “Completed” to keep only completed orders. count() returns the number of rows, collect() retrieves records to the driver, and explain() displays the execution plan. Because filtering is a transformation, Spark can incorporate it into the execution plan before an action triggers computation.

Question 166

A DataFrame contains many columns, but the downstream table requires only customer_id, name, and email. Which operation should the engineer use?

1. dropDuplicates()
2. select()
3. groupBy()
4. orderBy()

Correct Answer: 2

Explanation

The select() operation allows a data engineer to choose the columns required for downstream processing. In this scenario, selecting customer_id, name, and email produces a DataFrame containing only the required fields. dropDuplicates() removes duplicate records, groupBy() organizes records for aggregation, and orderBy() sorts records. Selecting only necessary columns can also reduce the amount of data carried through later transformations, making the pipeline easier to understand and potentially more efficient.

Question 167

A pipeline needs to calculate total sales separately for each store. Which Spark operation should be used before applying the aggregation function?

1. select()
2. filter()
3. groupBy()
4. drop()

Correct Answer: 3

Explanation

groupBy() creates groups based on one or more columns so that aggregate functions can be applied independently to each group. For store-level sales, an engineer could use groupBy(“store_id”).sum(“sales”) to calculate the total for each store. select() chooses columns, filter() restricts rows, and drop() removes columns. Grouping by the appropriate business dimension is therefore the key step required before calculating an aggregate such as total sales.

Question 168

A data engineer needs to sort transaction records so that the newest transactions appear first. Which operation is appropriate?

1. filter()
2. union()
3. groupBy()
4. orderBy()

Correct Answer: 4

Explanation

The orderBy() operation sorts a DataFrame according to one or more columns. To display the newest transactions first, the engineer can order by the transaction timestamp in descending order. filter() limits rows according to a condition, union() combines compatible DataFrames, and groupBy() creates groups for aggregation. Sorting is particularly useful when producing ordered outputs for analysis or when inspecting recent records, although distributed datasets should not be assumed to have a permanent global order unless explicitly required.

Question 169

A data engineer wants to create a reusable DataFrame containing only active customers and use it in several subsequent transformations. Which approach is appropriate?

1. Apply a filter to create the DataFrame
2. Delete the source table
3. Run VACUUM on the source
4. Create a new cluster for every transformation

Correct Answer: 1

Explanation

A filter can create a DataFrame containing only records that meet a required condition, such as active customers. The resulting DataFrame can then be referenced by subsequent transformations in the same processing workflow. Deleting the source table would destroy data, while VACUUM is a Delta table maintenance operation unrelated to filtering records. Creating a separate cluster for each transformation is unnecessary. Reusing a properly defined filtered DataFrame can also make transformation logic easier to read and maintain.

Question 170

A data engineer repeatedly uses the same expensive DataFrame in several actions during one Spark application. Which feature can help avoid recomputing the DataFrame each time?

1. MERGE
2. cache()
3. DELETE
4. DESCRIBE HISTORY

Correct Answer: 2

Explanation

The cache() operation can retain a DataFrame’s computed data so that subsequent actions may reuse it instead of recalculating the same transformations. This can improve performance when the same expensive intermediate dataset is accessed repeatedly within an application. However, caching consumes cluster memory and should be used selectively. MERGE modifies Delta data, DELETE removes records, and DESCRIBE HISTORY provides table history. Caching is therefore appropriate when repeated reuse justifies the additional resource consumption.

Question 171

Which Spark operation triggers computation and returns the number of records in a DataFrame?

1. count()
2. filter()
3. select()
4. withColumn()

Correct Answer: 1

Explanation

count() is a Spark action that triggers execution and returns the number of records in a DataFrame. In contrast, filter(), select(), and withColumn() are transformations that describe additional processing without immediately returning the final computed result. Spark uses lazy evaluation, meaning transformations are generally not executed until an action is called. Therefore, count is appropriate when an engineer needs to determine how many records are currently present in a DataFrame.

Question 172

A data engineer needs to add a new column containing a calculated value based on two existing columns. Which DataFrame operation should be used?

1. union()
2. groupBy()
3. withColumn()
4. orderBy()

Correct Answer: 3

Explanation

The withColumn() operation can add a new column or replace an existing column using an expression derived from other columns. For example, an engineer can calculate total price by multiplying quantity by unit price and assign the result to a new column. union() combines rows, groupBy() creates groups for aggregation, and orderBy() sorts records. WithColumn is therefore appropriate when transformation logic needs to create a derived field within an existing DataFrame.

Question 173

A data engineer needs to change the name of a DataFrame column from cust_id to customer_id without changing its values. Which operation should be used?

1. withColumnRenamed()
2. filter()
3. dropDuplicates()
4. collect()

Correct Answer: 1

Explanation

withColumnRenamed() changes the name of an existing DataFrame column while preserving its underlying values. This is useful when source systems use inconsistent naming conventions and a pipeline needs to standardize column names. filter() changes which rows are retained, dropDuplicates() removes duplicate records, and collect() retrieves data to the driver. Therefore, withColumnRenamed is the appropriate operation when the requirement is specifically to change a column’s name.

Question 174

A data engineer wants to combine customer information with order information using customer_id, keeping only customers who have matching orders. Which join type should be used?

1. left join
2. full outer join
3. cross join
4. inner join

Correct Answer: 4

Explanation

An inner join returns records where the join condition matches in both datasets. In this scenario, joining customers and orders on customer_id with an inner join keeps only customers that have corresponding orders. A left join would preserve all customer records even when no order exists, while a full outer join would retain unmatched records from both sides. A cross join creates combinations rather than matching records by key. Therefore, inner join matches the stated requirement.

Question 175

A data engineer wants every customer to remain in the result even when that customer has no matching order. Which join type is appropriate when customers are the left dataset?

1. left join
2. inner join
3. right-only join
4. cross join

Correct Answer: 1

Explanation

A left join preserves every record from the left dataset and adds matching records from the right dataset when they exist. If a customer has no matching order, the customer still appears in the result, with missing values for order-side columns. An inner join would remove customers without matches. A cross join produces combinations between datasets rather than matching on a key. Therefore, a left join is appropriate when all customers must remain in the output.

Question 176

A pipeline joins a small reference dataset with a much larger dataset. The engineer wants Spark to potentially distribute the small dataset efficiently to reduce the cost of the join. Which optimization can be considered?

1. VACUUM
2. Broadcast join
3. DELETE
4. Time travel

Correct Answer: 2

Explanation

A broadcast join can be considered when one side of a join is sufficiently small to be distributed to the executors. By making the small dataset available across the relevant workers, Spark may avoid a large shuffle of that dataset and perform the join more efficiently. The technique should be used only when the smaller dataset is appropriate for broadcasting because excessive broadcast size can consume executor memory. VACUUM, DELETE, and time travel address different Delta Lake operations.

Question 177

A data engineer wants to inspect how Spark plans a join and whether filters or other operations are being applied as expected. Which method is useful?

1. cache()
2. explain()
3. drop()
4. union()

Correct Answer: 2

Explanation

The explain() method displays the logical and physical execution plan for a DataFrame query. This can help engineers investigate how Spark intends to execute joins, filters, projections, and other operations. Examining the plan can reveal whether an unexpected join strategy or expensive operation is contributing to poor performance. cache() concerns data reuse, drop() removes columns, and union() combines DataFrames. Therefore, explain is the appropriate method for inspecting Spark’s planned execution.

Question 178

A data engineer wants to prevent unauthorized users from reading a sensitive table in Unity Catalog. Which approach should be applied?

1. Grant SELECT to every workspace user
2. Remove the table schema
3. Apply appropriate Unity Catalog privileges
4. Store the table in a temporary DataFrame

Correct Answer: 3

Explanation

Unity Catalog privileges provide a centralized mechanism for controlling access to governed data assets. An administrator or authorized owner can grant appropriate privileges, such as SELECT, to specific users or groups according to their responsibilities. Granting access to every workspace user would unnecessarily broaden permissions. Removing the schema is not an access-control solution, and a temporary DataFrame does not replace governance controls on the underlying data. Applying appropriate privileges supports controlled and auditable access to sensitive datasets.

Question 179

A team wants analysts to query a curated dataset but should not allow them to modify the underlying table. Which privilege should generally be granted for read-only access?

1. MODIFY
2. CREATE
3. SELECT
4. OWNERSHIP

Correct Answer: 3

Explanation

The SELECT privilege provides permission to read data from a governed table without granting modification rights. This is appropriate when analysts need to query a curated dataset but should not alter its contents. MODIFY provides data modification capabilities, while CREATE relates to creating objects and ownership provides broader control. Applying only the privileges required for the user’s role supports the principle of least privilege and reduces the risk of unintended changes to production datasets.

Question 180

A production pipeline requires access to a protected database credential, but the credential should not be embedded in the notebook or Git repository. Which solution is most appropriate?

1. Put the credential in a notebook comment
2. Store it in a secure secrets mechanism and retrieve it at runtime
3. Add it to a table description
4. Include it directly in the job name

Correct Answer: 2

Explanation

Sensitive credentials should be stored in a secure secrets mechanism rather than embedded directly in notebooks or source-control repositories. The pipeline can retrieve the required secret at runtime when authentication is needed. This reduces the risk of credentials being exposed through code, commits, notebook sharing, or configuration files. Comments, table descriptions, and job names are not suitable locations for secrets because they can be visible to users without the required credential-management controls.