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

A) Partition the table by `event_date` only, relying on predicate pushdown and column pruning for other filters.

B) Partition the table by `session_id` to maximize parallelism and avoid data skew.

C) Partition the table by both `event_date` and `user_id` to maximize pruning for all common filters.

D) Partition the table by `country_code` only, since it has fewer distinct values than `user_id` or `session_id`.

A) Spark SQL/DataFrame operations are optimized by Catalyst and Tungsten, which can generate efficient execution plans for set-based transformations.

B) RDD transformations always materialize intermediate results to disk, while DataFrame operations always keep all intermediate data in memory.

C) Spark SQL/DataFrame operations automatically repartition data to eliminate shuffles, whereas RDDs always require explicit shuffles.

D) Spark SQL/DataFrame operations bypass the cluster’s resource manager and therefore can use more CPU and memory than RDD-based jobs.

A) Read the Parquet files into an RDD, then use Python filter and map functions to select rows and columns.

B) Read the Parquet files as a DataFrame, then apply filter() and select() using built-in column expressions.

C) Read the Parquet files as text, parse the content with a Python UDF, and then filter the parsed DataFrame.

D) Convert the Parquet files to CSV first, then read them with inferSchema enabled and apply filters in Python.

A) Remove the cache on the intermediate DataFrame and let each aggregation recompute it

B) Increase the cluster size so that the entire cached DataFrame fits in memory

C) Change the cache level to DISK_ONLY to avoid executor OOM errors

D) Add additional cache() calls on the downstream aggregation results to reduce recomputation

A) Reject the proposal, because blindly appending daily records to orders_silver will break idempotency and create multiple rows per order_id unless an upsert/merge pattern is used.

B) Accept the proposal, because append-only loads are always idempotent as long as order_id is present and downstream queries can filter by max(event_time).

C) Accept the proposal, but require repartitioning orders_silver by order_id to ensure that appends do not create duplicates.

D) Reject the proposal, because incremental patterns cannot be used when there is a business key like order_id; full refresh is the only correct approach.

A) Window functions are always faster than `GROUP BY` aggregations in Spark.

B) The window function allows you to keep all original columns from `silver_events` for the latest event, not just `user_id`, `event_type`, and `event_time`.

C) The window function avoids shuffles, while `GROUP BY` always causes a shuffle.

D) The window function automatically handles late-arriving data, while `GROUP BY` does not.

A) Broadcast the product table in the join so that the sales table does not need to be shuffled.

B) Repartition the sales table by product_id before the join to better distribute the data.

C) Increase the shuffle partition count so that the shuffle is more parallelized.

D) Convert both tables to CSV format before joining to reduce file sizes and shuffle volume.

A) Keep df_sales.cache(), because caching always improves performance when a DataFrame is used more than once, regardless of memory constraints.

B) Remove df_sales.cache(), because caching is never beneficial in Spark; recomputing is always cheaper.

C) Cache df_sales only if it is reused multiple times in actions and there is sufficient memory; otherwise, avoid caching to prevent memory pressure and eviction.

D) Replace cache() with persist(StorageLevel.DISK_ONLY), because disk-only persistence is always faster than recomputation.

A) Spark executes each transformation immediately, but print statements are buffered; the engineer should flush stdout after each print.

B) Spark uses lazy evaluation, so transformations are only executed when an action (like write, show, or count) is called; the engineer should use actions like df.show() or df.limit(10).collect() on intermediate DataFrames.

C) Spark only executes transformations when a cache() is called; the engineer should cache each intermediate DataFrame to force execution.

D) Spark defers execution until the driver script finishes; the engineer must wait for the entire script to complete before any output appears.

A) Ignore the schema differences and rely on Spark's schema-on-read to automatically merge all file schemas without configuration.

B) Explicitly enable schema evolution features for the table format (for example, using options like mergeSchema or table-specific evolution settings) and/or define a target schema with appropriate casting and null handling.

C) Convert all JSON logs to CSV before writing to Parquet, because CSV does not have schema and will avoid schema evolution issues.

D) Drop any files that do not match the original schema so that the job can continue using a fixed schema.