Question 200 A raw bronze field has values " US ", "us", and "Us" that should represent the same country code in silver. Which transformation is appropriate? Concat a random suffix to each code Drop every country containing lowercase letters trim then upper-case the field countDistinct before normalization Explanation Correct Answer C:Whitespace trimming and case normalization map these three representations to the common code US. Incorrect Answers A:Adding suffixes increases rather than removes inconsistency. B:That would discard otherwise valid records. D:This reports three distinct strings and does not create a consistent country code.
Question 201 A dimension table has effective start and end dates. A fact should match the dimension record that was valid on the fact event date. Which join logic is needed? Join by entity key and the fact date falling within the effective interval Join on the current dimension row for every historical fact Join only on entity ID and ignore dates CROSS JOIN and take arbitrary first Explanation Correct Answer A:A temporal validity predicate chooses the historically correct dimension version. Incorrect Answers B:Using only today's dimension state can misrepresent past attributes. C:This may match several historical versions for one fact. D:Random selection gives no historical validity guarantee.
Question 202 A SQL query groups invoices by customer and applies `HAVING SUM(amount) > 1000`. What does HAVING filter? Individual invoice rows above 1000 before aggregation Only customers whose names sort after 1000 All customers with more than 1000 invoice rows Customer groups whose summed invoice amount exceeds 1000 Explanation Correct Answer D:HAVING evaluates an aggregate criterion after grouping the invoice rows. Incorrect Answers A:That would be a WHERE amount > 1000 predicate, not aggregate HAVING. B:HAVING is not an alphabetical ordering predicate. C:Row count and summed amount are different metrics.
Question 203 A report requires the top-selling product within each region, allowing ties to share the same rank. Which window function is a candidate? ROW_NUMBER() with no partitioning RANK() OVER (PARTITION BY region ORDER BY sales DESC) Use DENSE_RANK() without a regional partition and rank all products globally COUNT(*) OVER () Explanation Correct Answer B:RANK assigns the same position to tied sales values within each regional partition. Incorrect Answers A:A global row number does not rank independently in each region and breaks ties arbitrarily. C:A global ranking does not identify top-selling products independently within each region, as requested by the report. D:A whole-table count does not order products by regional sales.
Question 204 A company wants to merge daily corrected product prices into a Delta dimension based on unique product ID. Which SQL operation is most direct? MERGE INTO using product ID with matched update and not-matched insert VACUUM on the dimension INSERT INTO without matching existing product IDs ORDER BY price without a write Explanation Correct Answer A:MERGE supports key-aware updates and inserts in one transactional statement. Incorrect Answers B:VACUUM is file cleanup and does not reconcile business rows. C:Blind inserts can create duplicate products instead of updating corrected prices. D:Sorting rows cannot change prices in the target table.
Question 205 A new curated Delta table should be created from the output of a SELECT transformation. Which SQL pattern fits? DROP TABLE ... SELECT ... GRANT SELECT ... AS TABLE CREATE TABLE with columns but no INSERT or SELECT CREATE TABLE ... AS SELECT ... Explanation Correct Answer D:CTAS creates the table from the query result and derives the output rows from the SELECT. Incorrect Answers A:A drop statement deletes rather than creates a populated table. B:A privilege command is not a table-creation data transformation. C:A schema-only CREATE TABLE does not populate the transformed rows.
Question 206 A target table contains a set of rejected duplicate records that must be removed by a documented condition. Which DML statement is relevant? DELETE FROM target WHERE rejection_flag = true Use MERGE with only INSERT branches to add the rejected rows into a new table name GRANT MODIFY ON target SELECT target WHERE rejection_flag = true Explanation Correct Answer A:DELETE removes rows that satisfy the explicit quality condition. Incorrect Answers B:An insert-only MERGE does not delete flagged existing duplicates from the current target. C:A privilege allows mutation but does not execute the specified row deletion. D:A SELECT can inspect bad rows but does not remove them.
Question 207 A silver DataFrame has a `status` column and needs to retain only confirmed records before gold aggregation. Which operation applies? countDistinct("status") withColumnRenamed("status", "confirmed") drop("status") filter(col("status") == "confirmed") Explanation Correct Answer D:A row predicate selects confirmed rows before downstream grouping or aggregation. Incorrect Answers A:A distinct count summarizes statuses rather than selecting only confirmed records. B:Renaming does not filter by status values. C:Dropping the column keeps all records and removes the evidence needed for filtering.
Question 208 An engineer sees a large shuffle after grouping a billion records by a highly skewed customer key. Which cause best explains the bottleneck? An uneven key distribution concentrates many records in a few reduce partitions Use more executors but keep the same highly skewed grouping key Every customer has identical frequency Only source CSV header names Explanation Correct Answer A:Highly skewed aggregation keys cause a subset of tasks to process disproportionate data. Incorrect Answers B:Scaling workers can increase capacity but one hot key may still dominate a reducer and limit parallel completion. C:Uniform distribution would not by itself explain a small set of extreme stragglers. D:Column headers do not cause large key-frequency imbalance.
Question 209 A Spark job shows that a join has a large shuffle and one side is a tiny dimension DataFrame. Which execution change may help? Increase the size of the tiny dimension artificially Order the notebook implementation steps alphabetically without changing the source table ordering Use a broadcast hash join when the small side and join type are suitable Force a Cartesian join Explanation Correct Answer C:Broadcasting the dimension can avoid shuffling the large fact table for the join. Incorrect Answers A:Padding the lookup table makes broadcasting harder, not easier. B:Notebook-cell order does not implement the record sort or sort keys that the business transformation requires. D:CROSS JOIN expands combinations and is not a substitute for key-based joins.
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.