Question 170 A raw transactions table may contain identical rows more than once. Before a distinct-order count, the engineer must remove duplicate order IDs while retaining one row per order. Which operation fits? crossJoin with the distinct IDs groupBy("order_id").count() as the final order detail table dropDuplicates(["order_id"]) after defining which row wins dropDuplicates() on every column without regard to ID Explanation Correct Answer C:Key-specific dedup retains a row per order, but business ordering is needed when duplicates conflict. Incorrect Answers A:A Cartesian join would inflate rather than deduplicate order records. B:A count aggregate removes non-key order attributes and is not itself a complete row record. D:Rows with same order ID but different timestamps would not be collapsed.
Question 171 A dataset has customer IDs [7, 7, 8, 9, 9] with no nulls. What does `countDistinct(customer_id)` return? 2 5 3 1 Explanation Correct Answer C:The unique values are 7, 8 and 9, so the distinct count is three. Incorrect Answers A:Two is the number of values that happen to repeat, not the number of distinct IDs. B:Five is the total number of rows before deduplicating repeated IDs. D:No single ID accounts for the entire input collection.
Question 172 A table contains amounts [10, 15, NULL] for one merchant. What is the standard SQL `SUM(amount)` result for this group? 25 3 0 NULL Explanation Correct Answer A:SUM ignores null input values and adds the two non-null amounts 10 and 15. Incorrect Answers B:Three is the total row count, not the sum of the amount values. C:The two numeric amounts contribute 25, not zero. D:A null value among non-null numbers does not force SUM for the whole group to be null.
Question 173 A table contains amounts [10, NULL, 30, NULL]. Which expression returns the number of non-null amount entries? COUNT(*) MAX(amount) COUNT(amount) SUM(amount) Explanation Correct Answer C:COUNT(column) counts non-null expressions, so the result is two for this data. Incorrect Answers A:COUNT(*) counts all four rows, including those with null amount. B:MAX(amount) returns 30, not the number of populated amount cells. D:SUM(amount) returns 40, not the count of non-null values.
Question 174 A risk team needs approximate unique-device counts over billions of telemetry rows and tolerates small estimation error for lower resource use. Which aggregate suits? first(device_id) count(device_id) sum(device_id) approx_count_distinct(device_id) Explanation Correct Answer D:This aggregate estimates cardinality and can reduce cost compared with exact distinct enumeration at large scale. Incorrect Answers A:FIRST returns one ID and does not estimate population cardinality. B:COUNT measures non-null rows, not the number of unique devices. C:Device identifiers should not be numerically summed to estimate unique devices.
Question 175 A PySpark source has a fullname field such as "Ada Lovelace". The silver model needs two tokens for simple names. Which transformation can help? explode(col("age")) split(col("fullname"), " ") countDistinct("fullname") orderBy("fullname") Explanation Correct Answer B:Splitting on a delimiter produces an array that can be indexed for simple two-token names. Incorrect Answers A:Explode expects a collection and does not parse full names into tokens. C:This produces a cardinality aggregate, not first and last name components. D:Sorting leaves the full name as a single string.
Question 176 A DataFrame must rename the `acct_nbr` column to `account_id` without changing row values. Which method is designed for that? withColumnRenamed("acct_nbr", "account_id") groupBy("acct_nbr") drop("acct_nbr") filter(col("acct_nbr") == "account_id") Explanation Correct Answer A:This returns a DataFrame with the renamed field while retaining its values. Incorrect Answers B:Grouping changes the shape of analysis but not the original field label. C:Dropping the source column removes its values rather than renaming them. D:Filtering compares data values and does not change a column name.
Question 177 A curated table needs a new `net_amount` field equal to `gross_amount` minus `discount`. Which PySpark transformation is suitable? drop("gross_amount") orderBy("gross_amount") withColumn("net_amount", col("gross_amount") - col("discount")) countDistinct("discount") Explanation Correct Answer C:withColumn computes the row-wise difference and adds or replaces the output field. Incorrect Answers A:Removing the source field prevents the requested subtraction. B:Sorting does not calculate the new net amount field. D:Cardinality of discounts is not a row-level net amount.
Question 178 An engineer filters a DataFrame with `col("state").isNull()`. Which records are selected? Only records whose state value is null Compare the column to the literal string "null" with case-insensitive collation Use `state == ""` to select empty-string rows and assume that also includes NULL Every record with a non-empty state Explanation Correct Answer A:isNull explicitly detects missing values and does not select non-null state codes. Incorrect Answers B:A text value spelling NULL is not the SQL null marker tested by isNull(). C:An empty string is a non-null value; it is not selected by a null predicate. D:That is an isNotNull-style condition, not isNull.
Question 179 An inner join must match records only when both customer_id and region are equal. Which join condition is correct? Join only on customer_id Join on (left.customer_id = right.customer_id) AND (left.region = right.region) Join on customer_id equality OR region equality CROSS JOIN followed by no filter Explanation Correct Answer B:Both equality predicates enforce the composite business key and prevent cross-region matches. Incorrect Answers A:A shared customer ID across regions can produce unintended matches. C:OR admits records that share only one key component and can inflate matches. D:An unfiltered Cartesian join ignores both key requirements.
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.