Databricks Certified Data Engineer Associate Practice Questions: ELT with Spark SQL and Python Domain

Correct Answer: B

Explanation: Option B is correct because using the `broadcast` function on the smaller DataFrame can significantly optimize join operations by reducing shuffle operations. Option A is incorrect because a full outer join is not always optimal and can be more resource-intensive. Option C is incorrect because converting DataFrames to RDDs loses the optimization benefits of DataFrames. Option D is incorrect because while caching can help, it does not specifically optimize the join operation itself.

Correct Answer: D

Explanation: Option D is correct because the Catalyst optimizer is a core component of Spark SQL that analyzes and optimizes SQL queries to produce efficient execution plans. Option A is incorrect because Catalyst doesn't compile SQL queries into RDD transformations directly. Option B is incorrect because while Catalyst can optimize DataFrame operations, its primary role is in SQL query optimization. Option C is incorrect because it does not convert DataFrames into SQL queries; instead, it optimizes the SQL queries themselves.

Correct Answer: A

Explanation: Option A is correct because it specifies the format as 'parquet' and sets the compression option to 'snappy' correctly. Option B is incorrect because 'snappy' is not a valid mode for writing. Option C is incorrect because the correct way to set compression is through the `option()` method, not as a parameter in `parquet()`. Option D is incorrect because the `save()` method does not accept `compression` as a direct parameter; it should be set using `option()`.

Correct Answer: D

Explanation: D is correct because setting the option 'inferSchema' to 'true' ensures that Spark infers the schema from the JSON file's data. A is incorrect because it does not involve schema inference; a predefined schema is used instead. B is incorrect because while it will infer the schema by default, explicitly setting 'inferSchema' is a better practice for clarity. C is incorrect because 'spark.read.csv()' is for CSV files, not JSON, and manually parsing JSON is inefficient.

Correct Answer: B

Explanation: The filter() method is used to filter rows in a DataFrame, and df.filter(df.column_name > 10) is the correct syntax. Option A is incorrect as select() is used for selecting columns, not filtering rows. Option C, where(), is a valid method but requires a string expression or column object, not a direct condition. Option D, query(), is not a method in the DataFrame API.

Correct Answer: C

Explanation: The 'MERGE INTO' statement in Delta Lake is specifically designed for performing upserts, allowing you to perform incremental loads by merging new data with existing data in the table. Option A is incorrect because schema enforcement ensures data quality but does not handle incremental loads. Option B is incorrect as time travel allows you to query previous versions of data, not for loading data incrementally. Option D is incorrect because VACUUM is used to clean up old data files, not for loading data.

Correct Answer: B

Explanation: Option B is correct because window functions perform calculations across a set of table rows that are related to the current row, providing capabilities beyond simple aggregation. Option A is incorrect because window functions can also be used with DataFrame APIs. Option C is incorrect because window functions are not limited to aggregation; they can perform a variety of calculations. Option D is incorrect because window functions are not inherently slower; their performance depends on the specific use case and query optimization.

Correct Answer: C

Explanation: Using broadcast joins for small tables can significantly improve the performance of Spark SQL queries, as it avoids the shuffle of large datasets. Option C is correct. Option A is incorrect because while DataFrame transformations can be optimized, the choice between SQL and DataFrame APIs should depend on the specific use case. Option B is incorrect because performing operations on the driver node can lead to bottlenecks and is not scalable. Option D is incorrect because partitioning tables can improve query performance by enabling parallel processing and reducing data shuffling.

Correct Answer: B

Explanation: Python UDFs (User Defined Functions) allow you to perform custom operations that are not possible with the built-in functions of Spark SQL, making Option B correct. Option A is incorrect because Python UDFs are executed on worker nodes, not the driver node. Option C is incorrect because Python UDFs are generally slower than Spark SQL built-in functions due to serialization and deserialization overhead. Option D is incorrect because Python UDFs do not automatically optimize execution plans; they require careful handling to avoid performance bottlenecks.

Correct Answer: B

Explanation: Option B is correct because Spark SQL is optimized for complex transformations due to its Catalyst optimizer, while DataFrame APIs can be more readable and easier to use for simpler transformations. Option A is incorrect because there are scenarios where Spark SQL might be more efficient. Option C is incorrect because DataFrame APIs can be equally optimized due to their reliance on the same execution engine. Option D is incorrect because Spark SQL is generally more efficient than RDDs due to optimizations.