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.
Question 221 A code change moves an expensive `distinct` operation before a selective filter on a huge DataFrame. What performance principle should be checked? Increase executor count but leave DISTINCT before the selective filter Rename variables to make distinct faster Push safe selective filters earlier to reduce data entering expensive shuffles Always perform every shuffle before any filters Explanation Correct Answer C:Reducing rows before a wide distinct can lower shuffle and memory requirements when semantics permit. Incorrect Answers A:Additional compute may not offset an unnecessarily large shuffle input caused by postponing the safe filter. B:Variable naming does not determine Spark physical shuffle cost. D:Processing more unnecessary rows generally increases work.
Question 222 A dimension contains one row per customer, while the fact contains many orders per customer. Which join grain should the engineer expect? The output always has fewer rows than the fact The join automatically sums customer orders The result preserves one row per order when customer IDs are unique in the dimension Every customer appears exactly once in the result Explanation Correct Answer C:A many-to-one equijoin enriches each order without multiplying it when lookup keys are unique. Incorrect Answers A:A proper many-to-one inner join may have equal rows if every order finds a customer. B:A join enriches rows and does not aggregate unless an aggregation is added. D:Customers with multiple orders generate multiple enriched fact rows.
Question 223 A team wants to split one `path` field such as `region/store/file` into separate components. Which PySpark function is directly relevant? countDistinct("path") split(col("path"), "/") avg("path") orderBy("path") Explanation Correct Answer B:The delimiter-based split produces elements that can be selected as region, store, and file. Incorrect Answers A:Cardinality does not parse the path segments. C:A string path cannot be numerically averaged to create components. D:Sorting path strings does not create component fields.
Question 224 A gold table has aggregated one row per product, while a dashboard needs to show the original transaction-level line items as well. What should the design provide? Assume SUM(quantity) preserves all original invoice IDs Add transaction IDs to the product-level aggregate without retaining line items Keep an accessible detailed silver/fact table alongside the aggregated gold product table Delete the detailed source immediately after gold creation Explanation Correct Answer C:A product-grain aggregate cannot reconstruct individual line items that were collapsed during aggregation. Incorrect Answers A:An aggregate total loses details unless those fields are separately retained. B:A product-level summary does not preserve individual transaction details merely by adding one identifier field. D:Removing detail prevents later line-item drill-through.
Question 225 A SQL transformation must select only the highest-valued transaction in each store, breaking ties by latest timestamp. Which technique is most appropriate? GROUP BY store with MAX(value) only Global ORDER BY value DESC LIMIT 1 ROW_NUMBER partitioned by store, ordered by value descending then timestamp descending CROSS JOIN all stores with all transactions Explanation Correct Answer C:A deterministic window order selects one intended winner per store. Incorrect Answers A:The maximum value alone does not identify a unique transaction under ties. B:A global top row returns only one store's transaction. D:Cartesian expansion is unrelated to per-store ranking.
Question 226 A table ingests raw records with some negative quantities that may represent legitimate returns. The analyst wants to separate true returns from data-entry errors. Which rule is sound? Convert all negative quantities to zero Always discard negative rows Validate quantity against an explicit event type and route unexplained negatives for review Invert every positive quantity Explanation Correct Answer C:A negative quantity can be valid for returns, so the quality rule must use business context. Incorrect Answers A:Zeroing legitimate returns misstates stock movements. B:Discarding returns can inflate net sales and hide genuine business events. D:Changing valid sales into returns corrupts the dataset.
Question 227 A data pipeline must read a bronze table, clean values, and create a new silver Delta table instead of overwriting source raw records. Which write design fits? Overwrite bronze with silver rows and discard all input values Write an empty table without processing source Use a temporary DataFrame display result instead of writing a persistent Delta silver table Write validated output to a separate silver table with controlled lineage Explanation Correct Answer D:Keeping bronze and silver distinct preserves raw evidence and makes transformations auditable. Incorrect Answers A:This destroys the original source needed for replay or forensic checks. B:A schema-only output does not meet the cleaning requirement. C:A display action can preview cleaned rows but is not a durable curated Delta table with data quality and governance.
Question 228 A SQL report uses `SELECT region, SUM(amount)` without grouping region. Why is the intended region-level result not well-defined? ORDER BY automatically groups region rows Nonaggregated region needs appropriate GROUP BY when combined with SUM Rely on ORDER BY to make the SUM valid without grouping region The Delta table must be recreated first Explanation Correct Answer B:The report must state the aggregation grain before returning one sum per region. Incorrect Answers A:Sorting does not define groups for aggregate expressions. C:Sorting does not resolve a nonaggregated select expression that must appear in the grouping. D:Table recreation is not the missing query clause.
Question 229 A SQL query needs to return records common to two tables by customer ID but must output columns from both sides. Which join matches? UNION ALL LEFT ANTI JOIN INNER JOIN on customer_id LEFT SEMI JOIN Explanation Correct Answer C:An inner join produces combined rows for matching keys from both inputs. Incorrect Answers A:A union stacks rows rather than combining matching customers side by side. B:An anti join selects left rows without a match. D:A semi join only projects left-side columns even though it tests membership.
Question 230 An engineer wants to project `customer_id` and `order_total` but not several temporary parsing fields. Which DataFrame operation is direct? select("customer_id", "order_total") crossJoin another DataFrame groupBy("order_total") filter("customer_id") Explanation Correct Answer A:Selecting explicit columns creates a projection with just the required fields. Incorrect Answers B:A Cartesian join expands rows and fields, not a minimal projection. C:Grouping alters row grain and does not simply project the two fields. D:A filter determines which rows remain rather than choosing output columns.