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 2)

Questions 6–10

1. 6expert · hard

   A data engineer needs to compute daily, weekly, and monthly total sales from a large `sales` table with a `sale_date` column. The table is partitioned by `sale_date`. The engineer wants to minimize the number of full scans of the table. Which approach is most efficient?

   • AUse a single query with `GROUP BY GROUPING SETS ((sale_date), (weekofyear(sale_date)), (month(sale_date)))`.
   • BRun three separate aggregation jobs: one grouped by day, one by week, one by month.
   • CUse a window function with `PARTITION BY sale_date` to compute daily totals, then aggregate weekly and monthly from the result.
   • DCache the table, then run three aggregation jobs.
   Reveal Solution Discussion Select an answer first
2. 7foundation · easy

   In Spark SQL, which function calculates a running total over a specified window?

   • ASUM(column) OVER (PARTITION BY ... ORDER BY ...)
   • BROW_NUMBER() OVER (ORDER BY ...)
   • CCOUNT(column) OVER (PARTITION BY ...)
   • DAVG(column) OVER (PARTITION BY ...)
   Reveal Solution Discussion Select an answer first
4. 8application · medium

   A finance team needs a report with total sales per year, per quarter, and per month, all in a single result set. The `sales` table has `sale_date` and `amount`. Which SQL clause produces this hierarchical aggregation?

   • AGROUP BY GROUPING SETS ((year(sale_date), quarter(sale_date), month(sale_date)))
   • BGROUP BY CUBE (year(sale_date), quarter(sale_date), month(sale_date))
   • CGROUP BY year(sale_date), quarter(sale_date), month(sale_date) WITH CUBE
   • DGROUP BY ROLLUP (year(sale_date), quarter(sale_date), month(sale_date))
   Reveal Solution Discussion Select an answer first
5. 9foundation · easy

   Which join type returns only the rows from the left table that have at least one matching row in the right table, without returning any columns from the right table?

   • ALeft semi join
   • BLeft outer join
   • CInner join
   • DLeft anti join
   Reveal Solution Discussion Select an answer first
6. 10application · medium

   A data pipeline joins a large `transactions` table (2 TB, 5000 partitions) with a small `currency_rates` lookup table (5 MB). The current job runs slowly because the default shuffle join redistributes the large table. The data engineer wants to minimize shuffle and speed up the job. Which action should be taken?

   • AUse a cross join and filter with a WHERE clause.
   • BAdd a broadcast hint: `/*+ BROADCAST(currency_rates) */` on the join.
   • CRepartition the `transactions` table by `currency_code` before the join.
   • DIncrease `spark.sql.shuffle.partitions` to 5000.
   Reveal Solution Discussion Select an answer first