Question 670 A dataset has values 6, 12, NULL, NULL. What does SQL AVG(value) return under normal NULL-ignoring aggregate semantics? 18 NULL 4.5 9 Explanation Correct Answer D:AVG ignores NULL values and divides the non-null sum 18 by the two non-null values. Incorrect Answers A:Eighteen is the non-null sum, not the average of the two observed values. B:The presence of NULL rows does not make AVG NULL when valid numeric values remain. C:This incorrectly divides 18 by all four rows, including two NULL entries.
Question 671 A product silver table has a tags array. The business rule keeps products whose tags include "fragile" without expanding one product into several rows. What expression is most direct? Group by product ID and take unrelated MAX values Explode tags and never regroup Use `size(tags) > 0` only Filter with `array_contains(tags, "fragile")` Explanation Correct Answer D:The array predicate tests membership and retains the original product row grain. Incorrect Answers A:Aggregation does not directly express membership of the desired string in each array. B:Explosion can multiply product rows and changes the required one-row-per-product result. C:A nonempty tags array does not prove the fragile label is present.
Question 672 A left join must keep all orders, including those with no qualifying active customer record. Where should a predicate restricting right-side customers to active be applied? As a filter requiring every order to have a customer ID By replacing the left join with a right join In the right-side input or the join condition before preserving the left rows In a post-join WHERE clause requiring right-side status = active Explanation Correct Answer C:Filtering the optional side within the join preserves orders lacking an active match as unmatched rows. Incorrect Answers A:Rejecting missing customers violates the requirement to retain all orders. B:A right join preserves the customer side instead of the requested complete order side. D:A post-join right-side filter drops NULL-extended unmatched orders and can effectively act like an inner join.
Question 673 An analyst wants one customer record per order, but the customer dimension contains two historical snapshots for the same customer. What should be resolved before a current-state join? Use a cross join so every snapshot remains visible Join directly and divide monetary totals by two afterward Keep the first arbitrary dimension row without a rule Choose one dimension row per customer using a documented current-record rule Explanation Correct Answer D:Selecting a unique current dimension row prevents duplicate historical matches from multiplying current orders. Incorrect Answers A:A Cartesian product compounds duplication rather than enforcing the desired current-state grain. B:A fixed correction fails when different customers have different numbers of historical versions. C:An arbitrary survivor can be stale and makes the join nondeterministic.
Question 674 A Delta MERGE fails because two source updates match the same target row and cannot be applied unambiguously. What should the engineer do before rerunning the merge? Resolve the source to one deterministic intended update per target key Retry the exact same duplicate source repeatedly Drop the target table and recreate only from the duplicate delta Disable transaction logging for the target Explanation Correct Answer A:A unique source match per target row removes the competing-update ambiguity in the merge. Incorrect Answers B:Retries do not fix the multiple-source-rows-per-target conflict. C:A partial change feed cannot safely replace all pre-existing target business rows. D:Removing the log would undermine Delta integrity without resolving ambiguous source matching.
Question 675 A merchant dashboard needs each transaction plus the sum of that merchant's current and two immediately preceding transactions ordered by event sequence. Which window frame fits? Partition only by event status with an unbounded frame Partition by merchant and use ROWS BETWEEN 2 PRECEDING AND CURRENT ROW Use RANGE UNBOUNDED PRECEDING for all merchants Group by merchant and output one sum for the whole history Explanation Correct Answer B:A row-based ordered frame spans exactly the current transaction and the two preceding merchant rows. Incorrect Answers A:Grouping by status crosses merchants and does not impose the requested three-transaction window. C:An unbounded range accumulates many transactions and is not exactly three physical rows. D:A grouped total loses the per-transaction rolling value requested by the dashboard.
Question 676 An operational feed appends sensor observations throughout the day. The gold layer needs managed incremental ingestion of those new observations into a persistent dataset. Which gold object fits this append-oriented requirement? Build a static materialized result that never processes newly arriving records Run a monthly full-file export into a gold table instead of consuming new observations incrementally A streaming table fed from the incremental source A standard SQL view over all source rows Explanation Correct Answer C:A streaming table maintains a persistent dataset by processing newly arriving input incrementally. Incorrect Answers A:A fixed snapshot does not continuously ingest the append-only observations into the persistent gold dataset. B:A monthly snapshot cannot provide the required ongoing incremental observations for the gold layer. D:A view queries current sources on demand and is not itself an incrementally maintained sink.
Question 677 A reporting team repeatedly queries daily sales totals from a large fact table, and the totals should be stored and refreshed as a managed derived result. What is appropriate? A source file notification trigger with no transformation A plain view with no materialized result A SQL query recorded only in a notebook output A materialized view of the grouped sales query Explanation Correct Answer D:A materialized view maintains a computed result that can be refreshed to serve repeated aggregate queries. Incorrect Answers A:Triggering ingestion does not materialize the daily sales aggregation. B:A standard view stores a query definition and does not by itself persist the aggregate result. C:Transient notebook output is not a managed reusable gold data object for many consumers.
Question 678 A gold summary recalculates average order value when old orders are corrected. Why may a materialized view be preferable to a pure append-only streaming table? It represents a maintained derived query result that can reflect source corrections It stores only Git repository metadata It guarantees every source correction can be ignored It prohibits source tables from receiving updates Explanation Correct Answer A:Summary logic may need to revise existing aggregate values when historical input changes. Incorrect Answers B:Materialized views represent query results, not repository change history. C:Ignoring corrected amounts would leave the derived average stale or incorrect. D:The business requirement explicitly includes corrections and cannot rely on immutable source data.
Question 679 Analysts use a lightweight gold projection with row filters, and they are comfortable querying its current underlying tables on demand. What should the engineer create? A new Auto Loader ingestion source for the projection A physical snapshot that is never refreshed A view defined with the needed SELECT and filters A scheduler that deletes source tables nightly Explanation Correct Answer C:A view resolves its query when accessed, avoiding a separately materialized copy for this lightweight projection. Incorrect Answers A:Auto Loader ingests external files and is not the logical projection artifact. B:A frozen copy can become stale relative to the requirement for current underlying records. D:Deletion is unrelated and would destroy the data to be projected.
Question 680 A continuously updated bronze feed contains immutable events. Gold must expose a cumulative per-device event count, including late arrivals. What design concern should be addressed explicitly? Maintain the aggregation with a supported incremental or refresh mechanism that accounts for late records Group only by input file name Overwrite the original bronze events once a count is computed Assume an append-only raw event never affects any prior grouped total Explanation Correct Answer A:Late input can alter an existing device total, so the gold maintenance strategy must allow the count to change. Incorrect Answers B:File boundaries do not represent the per-device business grain. C:Discarding the source events undermines the requested cumulative history and future corrections. D:Existing device groups need their totals revised as additional events arrive.