Question 180 A 10-million-row fact table joins to a 2,000-row lookup that fits comfortably in executor memory. Which strategy can reduce shuffle work? Repartition both sides to one partition Broadcast the entire enormous fact table by default Broadcast the small lookup side when supported Cross join all rows before applying predicates Explanation Correct Answer C:Broadcasting a small dimension can avoid a full two-sided shuffle join. Incorrect Answers A:A single partition limits parallelism and may create a severe bottleneck. B:Broadcasting a very large table can exhaust memory and is not the intended optimization. D:Cartesian expansion increases intermediate records dramatically.
Question 181 A table has duplicate customer rows, and one should be selected by latest effective timestamp per customer. Which technique best expresses the requirement? SUM(customer_id) grouped by timestamp Global LIMIT 1 without partitioning ROW_NUMBER over PARTITION BY customer_id ORDER BY effective_at DESC DISTINCT on every column Explanation Correct Answer C:A window rank ordered by source effective time selects one deliberate latest record per key. Incorrect Answers A:Summing identifiers does not pick the most recent customer record. B:LIMIT 1 would keep just one row for the entire dataset, not one per customer. D:Distinct does not choose a latest version when rows have different timestamps.
Question 182 A warehouse fact table must report revenue separately by day and business unit. Which grouping is needed? Group by business date only and attribute the resulting total to every business unit GROUP BY business_date, business_unit with SUM(revenue) ORDER BY business_date, business_unit without GROUP BY Group by business unit only and assume that totals are still separated by reporting day Explanation Correct Answer B:Both dimensions are grouping keys so each day-unit combination has its own revenue total. Incorrect Answers A:Aggregating only by date combines all units, losing the per-unit revenue breakdown requested. C:Sorting does not aggregate amounts by the specified keys. D:A unit-level total across dates does not retain the daily dimension required by the report.
Question 183 Rows [region="west", amount=10], [region="west", amount=20], and [region="east", amount=7] are grouped by region with SUM(amount). What is west's total? 20 37 30 2 Explanation Correct Answer C:The west group contains amounts 10 and 20, giving 30. Incorrect Answers A:Twenty is the maximum west amount, not the sum. B:Thirty-seven sums both west and east and ignores the region grouping. D:Two is the number of west rows, not their monetary sum.
Question 184 A silver pipeline sees events with blank customer identifiers that cannot be joined to any dimension and must be quarantined for investigation. Which operation should be applied? Assign all blank IDs the same fake customer number Drop the entire source dataset Keep all records without a quality indicator Split valid and invalid rows using explicit null/blank ID predicates Explanation Correct Answer D:Separating invalid identifiers preserves recoverability while keeping the silver join key reliable. Incorrect Answers A:An invented shared key collapses unrelated entities and can corrupt joins. B:One class of invalid rows does not require discarding all valid events. C:Unmarked invalid identifiers can silently fail downstream dimension joins.
Question 185 A silver table stores event_time as STRING but analysts require timestamp windows. Which transformation is necessary? Parse or cast to a timestamp with a documented format and error handling Concatenate an arbitrary suffix to the string Order raw strings and assume every format sorts chronologically Convert timestamps to a random partition index Explanation Correct Answer A:Typed timestamp values enable correct temporal ordering and window operations while exposing invalid strings. Incorrect Answers B:String decoration does not create a valid temporal type. C:Inconsistent string formats need not sort by actual event time. D:An index cannot replace the source event-time semantics.
Question 186 A query needs to retain only orders whose customer ID exists in the customer reference but does not need any customer columns. Which join expresses that intent? Use a LEFT OUTER JOIN and keep orders without any matching reference customer CROSS JOIN without condition Use a LEFT ANTI JOIN so only orders with no matching customer remain LEFT SEMI JOIN on customer_id Explanation Correct Answer D:A left semi join keeps left-side rows with a matching right-side key without adding right-side columns. Incorrect Answers A:A left join retains unmatched orders unless an additional filter is applied; the request is to keep only matching order IDs. B:Cartesian join expands combinations without enforcing existence. C:An anti join does the reverse of the request: it returns orders lacking a corresponding reference match.
Question 187 An audit wants orders with customer_id values absent from a reference customer table. What is the direct Spark join type? INNER JOIN UNION ALL LEFT SEMI JOIN LEFT ANTI JOIN Explanation Correct Answer D:A left anti join selects left-side rows with no matching right-side key. Incorrect Answers A:Inner join retains matched pairs and discards missing-customer cases. B:Union stacks datasets rather than detecting unmatched keys. C:A semi join selects matched orders, the opposite of the requirement.
Question 188 A developer wants to remove the temporary `raw_payload` column after validated fields are derived. Which DataFrame operation is appropriate? filter(col("raw_payload") == "") orderBy("raw_payload") drop("raw_payload") groupBy("raw_payload") Explanation Correct Answer C:drop removes the named column without filtering the rows themselves. Incorrect Answers A:A filter removes records rather than the column. B:Sorting retains the raw payload field. D:Grouping changes aggregation semantics instead of removing the field.
Question 189 A PySpark DataFrame has salary values [100, 200, 300]. Which aggregate gives the arithmetic mean salary? count("salary") avg("salary") max("salary") sum("salary") Explanation Correct Answer B:The mean is (100 + 200 + 300)/3 = 200 and avg computes it directly. Incorrect Answers A:COUNT returns 3 non-null rows, not the average. C:MAX returns 300, the highest salary rather than mean. D:SUM returns 600, not 200.
Question 190 A cleaning notebook must normalize nullable country fields to an explicit "unknown" category while keeping known country values unchanged. Which expression fits? coalesce(col("country"), lit("unknown")) orderBy("country") countDistinct("country") dropna(subset=["country"]) Explanation Correct Answer A:coalesce chooses the existing non-null country value and otherwise emits the specified default. Incorrect Answers B:Sorting does not change nulls into the desired category. C:Distinct count summarizes countries rather than filling row-level null values. D:Dropping null-country rows loses them instead of supplying the requested explicit category.