Question 210 A PySpark aggregation needs both total sales and number of distinct invoices per store. Which expression shape fits? groupBy("store").agg(count("sales"), sum("invoice_id")) orderBy("store") without aggregates groupBy("store").agg(sum("sales"), countDistinct("invoice_id")) distinct() on the full input as the only step Explanation Correct Answer C:This computes a monetary sum and unique-invoice cardinality at the same store grain. Incorrect Answers A:Counting amounts and summing identifiers do not match the required metrics. B:Sorting does not compute the store-level totals. D:A whole-row distinct does not calculate sales totals or invoice counts per store.
Question 211 A fact table has duplicate order IDs caused by replay, with one record marked as the latest by source version. What is a safe silver transformation? Remove every order ID seen more than once, even when one record has a newer source version Call distinct() across all columns Window by order ID and select the highest trusted source version Use the first physical Spark partition containing an order ID as the authoritative record Explanation Correct Answer C:A deterministic business-key/version rule retains the intended latest order state. Incorrect Answers A:Deleting all duplicate IDs loses legitimate orders. Rank versions by trusted source sequence and retain the latest valid record. B:Different versions are not whole-row duplicates and both may remain. D:Partition iteration order is not a reliable business event version, so the most recent correction may be discarded.
Question 212 A data engineer needs to create a column showing `category` when available, otherwise `"unspecified"`. Which column expression matches? coalesce(col("category"), lit("unspecified")) lit("unspecified") without reading category col("category") == "unspecified" drop("category") Explanation Correct Answer A:coalesce supplies the fallback only when category is null. Incorrect Answers B:A constant overwrites all known categories. C:This produces a boolean comparison, not a cleaned category value. D:Dropping removes the source category entirely.
Question 213 A silver pipeline must reject records whose transaction_date is later than tomorrow because of a source clock error. Which action is appropriate? Replace all future dates with the current date silently Validate the timestamp against the permitted range and quarantine violations Discard all historical records too Use a CAST to STRING on the date field and accept all values without comparing against the allowed date Explanation Correct Answer B:A range rule identifies implausible future events while retaining evidence for diagnosis. Incorrect Answers A:This invents event timing and hides the source clock defect. C:Valid past transactions need not be rejected because some future timestamps are invalid. D:Changing a column type does not enforce the stated transaction-date business rule and can hide invalid future dates.
Question 214 A table has three rows with nullable discount values [NULL, 5, 15]. Which aggregate produces an average discount over non-null entries? AVG(discount) returning 10 AVG(discount) returning 20/3 COUNT(*) returning 3 MAX(discount) returning 15 Explanation Correct Answer A:SQL AVG ignores null input values, averaging 5 and 15 to 10. Incorrect Answers B:Null does not contribute a third numeric denominator in AVG(column). C:COUNT(*) returns row count, not mean discount. D:Maximum is the largest discount, not the arithmetic mean.
Question 215 A PySpark DataFrame contains `sku`, `region`, and `qty`. What does `df.groupBy("region").sum("qty")` produce? Only a sorted list of region names One row per original SKU with no aggregation One aggregate row per unique sku-region pair One aggregate row per region with total quantity Explanation Correct Answer D:Grouping by region and summing qty computes a region-grain total. Incorrect Answers A:The expression sums numeric quantities rather than simply ordering labels. B:groupBy changes the output grain to regions, not individual source SKUs. C:SKU is not included in the groupBy keys, so it is aggregated across.
Question 216 A marketing model wants to count orders where shipping_country is non-null, including repeated values. Which SQL expression matches? COUNT(shipping_country) COUNT(DISTINCT shipping_country) COUNT(*) SUM(shipping_country) Explanation Correct Answer A:COUNT on a column counts its non-null occurrences without removing repeated country names. Incorrect Answers B:Distinct count measures unique countries, not number of populated order rows. C:COUNT(*) includes orders whose shipping_country is null. D:Shipping country is categorical text and cannot be summed as a count.
Question 217 A product fact DataFrame joins to a category lookup; a few product IDs have no category and need to be reported as an exception set. Which operation is most direct? UNION ALL of fact and lookup LEFT ANTI JOIN fact to lookup on category key INNER JOIN fact to lookup GROUP BY category with no join Explanation Correct Answer B:An anti join identifies facts whose lookup key has no matching reference row. Incorrect Answers A:Stacking schemas does not detect referential gaps. C:An inner join excludes unmatched products from the output. D:Grouping cannot reveal lookup absence without comparing the tables.
Question 218 A sensor data DataFrame has a nested array `readings` and each reading includes an `observed_at` timestamp. Which approach creates one record per reading? drop(readings) collect_list(readings) only explode(readings) then select the reading fields count(readings) only Explanation Correct Answer C:Exploding the array produces individual observations suitable for time-series analysis. Incorrect Answers A:Dropping the collection deletes the observations being requested. B:collect_list aggregates values into arrays rather than flattening elements to rows. D:COUNT gives row counts and loses individual sensor readings.
Question 219 A silver table requires a stable primary business key before loading a downstream materialized view. What should its validation check? Non-null key values and deterministic duplicate resolution Validate that the load completed successfully without checking key nulls or duplicates Apply a string trim to the key but never test whether duplicate keys remain Only the number of catalog folders Explanation Correct Answer A:A stable key avoids ambiguous grouping, joins and refresh results in downstream curated objects. Incorrect Answers B:Pipeline success does not prove uniqueness or non-null business keys expected by the materialized view. C:Normalization can help comparisons, but alone cannot enforce stable key uniqueness or resolve duplicates. D:Namespace count does not prove business-key quality.
Question 220 An analyst needs a daily report and wants to avoid lost detail from `GROUP BY` directly over raw inconsistent timestamps. What should the engineer do first? Group by raw string timestamps from each source without casting Normalize event timestamps and derive a business date at the intended timezone Use only COUNT(*) without a date key Assign a random date to missing timestamps Explanation Correct Answer B:A consistent date grain prevents incorrect grouping due to mixed formats or timezones. Incorrect Answers A:Inconsistent string formats create accidental separate groups for equivalent dates. C:A global count does not provide daily breakdowns. D:Inventing dates can corrupt day-level metrics.