Question 240 A data quality owner says one null value in a nullable source column makes the entire SQL SUM result null. Which explanation is correct? SUM ignores null elements when non-null values exist Filter out all rows with any null column without assessing which fields may legitimately be null SUM converts all nulls to a random positive number SUM returns null whenever any row is null Explanation Correct Answer A:SQL aggregation of nullable numeric columns excludes null inputs from SUM and averages. Incorrect Answers B:Unconditional null elimination can discard valid nullable data, whereas the requested validation must respect the agreed column contract. C:The aggregate does not impute unrelated positive values. D:This incorrectly treats null as contaminating the entire group.
Question 241 A gold view joins daily revenue to customer region, but region can change over time. What modeling consideration is important for historically accurate reports? Convert all regions to one global label Choose effective-dated dimension logic when reporting past regional attribution Ignore customer IDs and join by row position Use only the latest region for all dates without business approval Explanation Correct Answer B:A time-aware join avoids applying current region retroactively to historical transactions. Incorrect Answers A:This removes the analytical dimension entirely. C:Row position is not a reliable relational key. D:Current-state joins can rewrite historical regional meaning.
Question 242 A source has two event types, Sale and Return. The gold layer needs net units. Which transformation respects the event type? Subtract every Sale twice Count Sale and Return both as positive units Aggregate signed units using a documented Sale-positive, Return-negative mapping Exclude all Return records from the model Explanation Correct Answer C:A business-defined sign rule creates net units without discarding return activity. Incorrect Answers A:Double subtraction has no basis in the event semantics. B:Treating returns as sales inflates net units. D:Ignoring returns overstates stock movement and sales.
Question 243 An engineer wants to produce a curated DataFrame with only customers whose last activity is within 30 days of a fixed report date. Which condition is needed? Filter on raw customer ID being greater than 30 Order by name and LIMIT 30 Count all customers without a date predicate Filter on a properly typed last_activity timestamp relative to the report cutoff Explanation Correct Answer D:A timestamp comparison against the fixed cutoff selects the intended active cohort. Incorrect Answers A:Customer identifiers do not measure activity recency. B:Selecting thirty alphabetic customers is not a 30-day activity condition. C:A total count cannot identify the recent-activity cohort.
Question 244 A SQL query needs the number of rows including rows with all nullable columns set to null. Which count expression is required? SUM(nullable_col) COUNT(nullable_col) COUNT(*) COUNT(DISTINCT nullable_col) Explanation Correct Answer C:COUNT(*) counts input rows irrespective of null values in individual columns. Incorrect Answers A:SUM adds numeric values and is not an all-rows count. B:COUNT(column) excludes rows where that column is null. D:Distinct column count excludes null and measures unique non-null values, not total rows.
Question 245 A dataset contains countries ["US", "US", "CA", NULL] and an analyst calls COUNT(DISTINCT country). What is the value? 2 4 1 3 Explanation Correct Answer A:The distinct non-null countries are US and CA; null is excluded from COUNT(DISTINCT). Incorrect Answers B:Four is the total number of rows, not the unique non-null country count. C:There are two non-null unique country codes, not one. D:Three counts two country values plus null, but SQL COUNT DISTINCT excludes null.
Question 246 A team needs to remove sensitive raw JSON payloads from gold while maintaining source lineage in bronze. What design matches? Delete all bronze source data whenever gold is produced Keep raw payloads in access-controlled bronze and project only authorized curated fields into gold Publish raw JSON to the same unrestricted gold dashboard Store complete raw JSON in gold and rely on report-level field hiding Explanation Correct Answer B:This separates raw evidence from the reporting contract while maintaining governed provenance. Incorrect Answers A:Removing bronze defeats replay and forensic lineage requirements. C:That exposes sensitive fields beyond the intended curated consumers. D:Report presentation controls can be bypassed by other gold consumers; sensitive raw payloads belong in a restricted bronze layer.
Question 247 A customer table stores multiple email addresses in an array, but a downstream reporting table requires one row per address. Which DataFrame transformation is relevant? Preserve the email array as a single string and treat it as one contact address drop(email_array) orderBy(email_array) explode the email array and retain customer_id Explanation Correct Answer D:Explode emits one row for each array element while preserving the customer relationship. Incorrect Answers A:A nested array serialized into one cell does not produce the individual email records the downstream process requires. B:Dropping the array discards the required values. C:Sorting arrays does not flatten them into rows.
Question 248 A BI analyst requires a lightweight saved SQL definition that always reflects the latest base table when queried. Which gold artifact fits the requirement? A one-time CTAS snapshot A normal SQL view A materialized view with no refresh An exported spreadsheet Explanation Correct Answer B:A view stores query logic and evaluates underlying data at query time, rather than materializing a copy. Incorrect Answers A:A CTAS table stores data as of the creation query and may become stale without refresh logic. C:A stored result without refresh does not guarantee latest-base-table output. D:A file extract does not inherently stay in sync with source table updates.
Question 249 An engineer uses Python `df.dropDuplicates(["customer_id"])` but later notices the retained `status` is not reliably the newest for each customer. What should change? Increase the number of shuffle partitions Randomly shuffle the DataFrame before deduplication Use explicit window ordering by trusted source version before choosing a customer row Use a full-row distinct instead Explanation Correct Answer C:dropDuplicates by key does not promise which conflicting row survives; explicit ordering creates a deterministic business result. Incorrect Answers A:Physical parallelism cannot specify business version precedence. B:Randomization makes survivor choice less predictable. D:Different status versions remain separate under whole-row distinct.
Question 250 A source dataset has 250 rows, and a transformation filters to 100 active rows before a left join with a unique-key dimension that matches 90 of them. How many left-side rows will remain? 90 250 190 100 Explanation Correct Answer D:A left outer join retains all 100 filtered fact rows, including ten with missing dimension matches. Incorrect Answers A:Ninety is the matched portion, which would be the inner-join count. B:The filter removed 150 inactive rows before the join. C:The join does not add the 90 matches to the 100 original left rows when the dimension key is unique.