Write Efficient Spark SQL and PySpark Code to Apply Advanced Data Transformations, Including Window Functions, Joins, and Aggregations, to Manipulate and Analyze Large Datasets. DATA-ENGINEER-PROFESSIONAL Practice Questions (Page 1)

Questions 1–5

1. 1application · medium

   A team needs to identify the first and last order date for each customer from an `orders` table. Which SQL query is correct?

   • ASELECT customer_id, FIRST(order_date), LAST(order_date) FROM orders GROUP BY customer_id;
   • BSELECT customer_id, order_date FROM orders GROUP BY customer_id, order_date HAVING order_date = MIN(order_date) OR order_date = MAX(order_date);
   • CSELECT customer_id, order_date FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) IN (1, COUNT(*) OVER (PARTITION BY customer_id));
   • DSELECT customer_id, MIN(order_date), MAX(order_date) FROM orders GROUP BY customer_id;
   Reveal Solution Discussion Select an answer first
2. 2foundation · easy

   Which technique in Spark can reduce shuffle overhead when joining a large DataFrame with a small one?

   • ABroadcast hint
   • BBucketing on both DataFrames
   • CUsing a cross join
   • DIncreasing shuffle partitions
   Reveal Solution Discussion Select an answer first
4. 3expert · hard

   A pipeline joins `orders` (large, 1 TB) with `customers` (large, 500 GB) on `customer_id`. Both tables are skewed: a few customer IDs have millions of orders. The job completes but takes 4 hours. The data engineer wants to reduce runtime without changing the business logic. Which approach is most effective?

   • AUse a broadcast join on `customers`.
   • BEnable Adaptive Query Execution (AQE) and set `spark.sql.adaptive.skewJoin.enabled=true`.
   • CAdd a `repartition(10000, 'customer_id')` before the join.
   • DIncrease `spark.sql.shuffle.partitions` to 5000.
   Reveal Solution Discussion Select an answer first
5. 4application · medium

   A data engineer runs a job that joins two large DataFrames on a high-cardinality key. The job is slow due to the shuffle. The engineer notices that the same two DataFrames are joined multiple times in the same job with the same join key. Which optimization reduces redundant shuffles?

   • AUse a broadcast hint on both DataFrames.
   • BRepartition both DataFrames by the join key and cache them before the joins.
   • CCache both DataFrames before the joins.
   • DIncrease `spark.sql.autoBroadcastJoinThreshold` to 1 GB.
   Reveal Solution Discussion Select an answer first
6. 5foundation · easy

   Which PySpark function returns the value of a column from the row immediately before the current row within a window?

   • AF.row_number()
   • BF.rank()
   • CF.lead()
   • DF.lag()
   Reveal Solution Discussion Select an answer first