Question 630 A producer delivers a data file twice under different names with identical content. The contract requires keeping both deliveries in the audit log but counting the business events once. What pattern fits? Discard both files and forget their arrival Aggregate by arrival hour only Retain delivery provenance and apply event-level deduplication under a stable source identity Use filename alone as the unique business-event key Explanation Correct Answer C:Operational delivery logs and logical event identity serve distinct purposes and can be preserved independently. Incorrect Answers A:This loses valid events and delivery evidence. B:Hourly aggregation cannot identify identical events among valid distinct data. D:Different names would defeat filename-only duplicate protection.
Question 631 A data feed includes a strict SLA for arrival, but there is no new file on a holiday when the source is officially closed. What should freshness monitoring incorporate? The documented business calendar and expected source delivery schedule Only warehouse worker count A universal rule that every calendar day requires a file Only a fixed table name suffix Explanation Correct Answer A:A correct freshness SLA must distinguish an expected source holiday from an unplanned missing delivery. Incorrect Answers B:Compute sizing does not define source delivery obligations. C:Calendar-day expectation can falsely flag legitimate source closures. D:Naming conventions are not sufficient to infer holiday schedules.
Question 632 A nullable campaign_code represents an unknown source code in both datasets. For a reconciliation specifically requiring two NULL codes to compare equal, which Spark SQL expression should be used? `a.campaign_code = b.campaign_code` `a.campaign_code != b.campaign_code` `a.campaign_code <=> b.campaign_code` `a.campaign_code IS NULL` Explanation Correct Answer C:The null-safe equality operator returns true when both operands are NULL while still comparing non-null values normally. Incorrect Answers A:Ordinary SQL equality returns unknown for NULL operands and therefore cannot match two missing codes. B:Inequality does not implement null-safe equality and also evaluates to unknown for NULL operands. D:This tests only the left field and would pair it with every right-hand value, including non-null ones.
Question 633 An orders dataset has one row with line_items=NULL. An audit requires one output row for every order even if no line items exist. Which PySpark operation best preserves that order? `explode_outer(line_items)` `size(line_items)` `array_distinct(line_items)` `explode(line_items)` Explanation Correct Answer A:Outer explosion retains a row with a null item when the array is null or empty, so the order remains visible. Incorrect Answers B:Array size measures elements; it does not create item-level records while preserving empty orders. C:Removing repeated elements does not emit rows or preserve the required null-item audit record. D:Ordinary explode emits no row for a null or empty array, violating the order-level audit requirement.
Question 634 A grouped SQL query must return only accounts with at least three completed transactions, after restricting input rows to status = completed. Which clause filters the computed grouped count? `ORDER BY COUNT(*) >= 3` `WHERE COUNT(*) >= 3` `SELECT DISTINCT status` `HAVING COUNT(*) >= 3` Explanation Correct Answer D:HAVING evaluates after grouping and can filter the number of completed records in each account group. Incorrect Answers A:ORDER BY changes result ordering; it does not remove accounts below the required threshold. B:WHERE filters input rows before aggregation and cannot directly test the resulting group count. C:Distinct status values neither group by account nor apply a minimum transaction count.
Question 635 Invoice numbers are unique only within a regional subsidiary. A fact table and a payment table both store region_id and invoice_number. How should they be joined to avoid cross-region matches? Join on region_id only Join on invoice_number only Cross join both tables then order by invoice_number Join on both region_id and invoice_number Explanation Correct Answer D:The composite key identifies the same invoice within the same region and prevents unrelated subsidiaries from matching. Incorrect Answers A:A region contains many invoices, so the join would multiply unrelated invoice and payment rows. B:The same invoice number can occur in another region, creating false combinations between unrelated records. C:Ordering cannot undo Cartesian combinations and does not implement the required equality predicates.
Question 636 A customer billing team needs invoice IDs that exist in the invoice table but not in the settled-payments table. Which Spark join returns only these unmatched invoice rows? Inner join invoices to settled payments Left semi join from invoices to settled payments Left anti join from invoices to settled payments Cross join invoices to settled payments Explanation Correct Answer C:A left anti join keeps left-side rows for which the right-side join condition has no match. Incorrect Answers A:An inner join discards the invoices missing from the payment side, the exact records requested. B:A semi join returns invoices that do have at least one matching settled payment. D:A Cartesian product provides every pair and does not express unmatched-record detection.
Question 637 A leaderboard has scores 99, 99, and 95. The desired ranks are 1, 1, and 2 rather than 1, 1, and 3. Which window function fits? `rank()` over descending score `count()` grouped by score `row_number()` over descending score `dense_rank()` over descending score Explanation Correct Answer D:Dense rank gives tied values the same rank and does not leave a rank gap after a tie. Incorrect Answers A:Regular rank leaves a gap after tied first-place rows, giving the next distinct score rank three. B:A grouped count returns group sizes and not the requested per-row ordered ranking. C:Row number gives every row a different sequential number even when scores are tied.
Question 638 A migration auditor wants to see customer keys present only in source, only in target, and in both systems in a single comparison result. Which join type is appropriate? Inner join on customer key Cross join without key conditions Left semi join on customer key Full outer join on customer key Explanation Correct Answer D:A full outer join preserves unmatched rows on either side as well as matched rows for reconciliation. Incorrect Answers A:An inner join removes keys that occur in just one system, concealing migration omissions. B:A Cartesian product creates unrelated row pairs and cannot identify unmatched keys directly. C:A semi join returns only existence matches from one side and not both sets of unmatched records.
Question 639 A table contains purchase amounts 10 for completed, 20 for canceled, and 30 for completed. What sum should a pipeline produce for completed purchases only? 20 40 60 30 Explanation Correct Answer B:Filtering to the two completed rows and summing their amounts yields 10 + 30 = 40. Incorrect Answers A:The amount 20 belongs to the canceled row that must be excluded. C:This includes the canceled purchase, contradicting the explicit status filter. D:This ignores the first completed purchase and is not the grouped completed total.
Question 640 Two incremental extracts overlap by one stable event_id because of a late retry. The silver table must contain one logical row per event after combining both extracts. Which sequence fits? `unionByName` followed by deduplication on event_id with a defined survivor rule `unionByName` alone Deduplicate each extract separately but never across the combined result `crossJoin` followed by `distinct` Explanation Correct Answer A:Union combines compatible inputs; event-key deduplication then removes the repeated logical event deterministically. Incorrect Answers B:A union operation preserves duplicated events from overlapping extracts unless deduplication follows. C:The same event may appear once in each extract and survive both local deduplication steps. D:A Cartesian join changes the data grain and is not a reliable event-combination technique.