100% Latest & Updated Databricks Certified Data Engineer Associate Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 180
A payroll bronze table has event timestamps saved as arbitrary text from two time zones. The team observes: A shift worked across midnight appears on the wrong payroll date. What is the most likely mechanism behind the observed defect?
- Planner thresholds or statistics prevented a beneficial broadcast choice.
- A materialized object was not refreshed and should have been a view for this need.
- The pipeline used duplicate-eliminating UNION rather than UNION ALL semantics.
- The transform interpreted local clock text without its source offset.
Explanation
Correct Answer
D:The transform interpreted local clock text without its source offset. Apply a documented timestamp parser and timezone normalization while writing typed silver columns.
Incorrect Answers
A:A large fact-to-small-dimension join repeatedly performs an avoidable shuffle. In those circumstances, “Planner thresholds or statistics prevented a beneficial broadcast choice” fits. The transform interpreted local clock text without its source offset.
B:When a finance dashboard reads a curated metric that should always reflect the latest base table without storing a separately maintained aggregate, “A materialized object was not refreshed and should have been a view for this need” could be relevant. The transform interpreted local clock text without its source offset.
C:If two quarterly datasets have identical columns but their records must be vertically appended and duplicates preserved, “The pipeline used duplicate-eliminating UNION rather than UNION ALL semantics” may be the appropriate response. The transform interpreted local clock text without its source offset.
Question 181
A health feed has duplicate late-arrival patient observations but raw arrivals must remain reviewable. Which approach most directly satisfies the requirement?
- Use UNION ALL or DataFrame unionByName rather than a relational join.
- Keep all landing events in bronze while writing validated and keyed latest records into silver.
- Use a streaming table or incremental pipeline target rather than a manually overwritten static table.
- Use a deterministic window ordering by event time and sequence to choose the last version.
Explanation
Correct Answer
B:Keep all landing events in bronze while writing validated and keyed latest records into silver. Each observation key has one selected silver version and inspectors can find every original bronze arrival.
Incorrect Answers
A:When two quarterly datasets have identical columns but their records must be vertically appended and duplicates preserved, “Use UNION ALL or DataFrame unionByName rather than a relational join” could be relevant. Keep all landing events in bronze while writing validated and keyed latest records into silver.
C:A downstream pipeline needs incremental consumption of a continuous events feed into a governed table. In those circumstances, “Use a streaming table or incremental pipeline target rather than a manually overwritten static table” fits. Keep all landing events in bronze while writing validated and keyed latest records into silver.
D:When a customer dataset must retain the latest update per business key even when events arrive out of order, “Use a deterministic window ordering by event time and sequence to choose the last version” could be relevant. Keep all landing events in bronze while writing validated and keyed latest records into silver.
Question 182
A health feed has duplicate late-arrival patient observations but raw arrivals must remain reviewable. Which configuration or engineering action would most directly implement the requirement?
- Join accounts LEFT OUTER JOIN payments on account_id and preserve missing payment fields.
- Use dropDuplicates on event_id after defining the desired event window or batch boundary.
- Partition by customer_id, order by timestamp and event sequence descending, and keep row_number=1.
- Parse event time and use a deterministic latest-per-key rule with a tie-breaker, retaining the raw rows.
Explanation
Correct Answer
D:Parse event time and use a deterministic latest-per-key rule with a tie-breaker, retaining the raw rows. Each observation key has one selected silver version and inspectors can find every original bronze arrival.
Incorrect Answers
A:If a subscriptions dataset must preserve every account even when no payment has arrived, “Join accounts LEFT OUTER JOIN payments on account_id and preserve missing payment fields” may be the appropriate response. Parse event time and use a deterministic latest-per-key rule with a tie-breaker, retaining the raw rows.
B:A click event feed contains retries with identical event_id and should count each business event once. In those circumstances, “Use dropDuplicates on event_id after defining the desired event window or batch boundary” fits. Parse event time and use a deterministic latest-per-key rule with a tie-breaker, retaining the raw rows.
C:When a customer dataset must retain the latest update per business key even when events arrive out of order, “Partition by customer_id, order by timestamp and event sequence descending, and keep row_number=1” could be relevant. Parse event time and use a deterministic latest-per-key rule with a tie-breaker, retaining the raw rows.
Question 183
A health feed has duplicate late-arrival patient observations but raw arrivals must remain reviewable. Which test would establish that the change worked as intended?
- Only new eligible event rows advance the target after a restart.
- The executed plan shows a broadcast join and no large dimension repartition overhead.
- The accepted plus rejected counts reconcile to the original source batch.
- Each observation key has one selected silver version and inspectors can find every original bronze arrival.
Explanation
Correct Answer
D:Each observation key has one selected silver version and inspectors can find every original bronze arrival.
Incorrect Answers
A:A downstream pipeline needs incremental consumption of a continuous events feed into a governed table. In those circumstances, “Only new eligible event rows advance the target after a restart” fits. Each observation key has one selected silver version and inspectors can find every original bronze arrival.
B:When a small static country table enriches a huge events DataFrame and a large shuffle must be avoided when eligible, “The executed plan shows a broadcast join and no large dimension repartition overhead” could be relevant. Each observation key has one selected silver version and inspectors can find every original bronze arrival.
C:If a silver quality gate must reject records missing a transaction identifier, but analysts still need an error count, “The accepted plus rejected counts reconcile to the original source batch” may be the appropriate response. Each observation key has one selected silver version and inspectors can find every original bronze arrival.
Question 184
A health feed has duplicate late-arrival patient observations but raw arrivals must remain reviewable. The team observes: Two mutually inconsistent observations remain active for the same event key. Which configuration or design gap best explains the symptom?
- The denominator included rows without a valid numeric price.
- An equijoin or filtering condition unintentionally removed Cartesian pairs.
- Partitioning is too coarse for the available worker parallelism.
- The transformation lacked deterministic timestamp and tie-breaking logic.
Explanation
Correct Answer
D:The transformation lacked deterministic timestamp and tie-breaking logic. Parse event time and use a deterministic latest-per-key rule with a tie-breaker, retaining the raw rows.
Incorrect Answers
A:When a sales dashboard needs mean transaction value per region while excluding null prices, “The denominator included rows without a valid numeric price” could be relevant. The transformation lacked deterministic timestamp and tie-breaking logic.
B:If a risk team needs all combinations of five scenario assumptions and six stress categories, “An equijoin or filtering condition unintentionally removed Cartesian pairs” may be the appropriate response. The transformation lacked deterministic timestamp and tie-breaking logic.
C:A Spark job has many tiny input partitions and inefficient parallel task utilization. In those circumstances, “Partitioning is too coarse for the available worker parallelism” fits. The transformation lacked deterministic timestamp and tie-breaking logic.
Question 185
A marketing bronze feed arrives with mixed currency columns, but conversion rates are supplied in a governed reference table. Which architecture or engineering decision best fits this scenario?
- Tune shuffle partition count using observed data volume and task metrics rather than a fixed guess.
- Use a documented data quality constraint with a quarantine or reject accounting strategy.
- Explode the product-code array into one output row per array element.
- Validate currencies and join the approved conversion reference before writing standardized silver amounts.
Explanation
Correct Answer
D:Validate currencies and join the approved conversion reference before writing standardized silver amounts. An audit sample reproduces each converted value using the effective-date exchange rate.
Incorrect Answers
A:If a heavily shuffled ETL join creates thousands of tiny shuffle partitions while tasks spend time scheduling, “Tune shuffle partition count using observed data volume and task metrics rather than a fixed guess” may be the appropriate response. Validate currencies and join the approved conversion reference before writing standardized silver amounts.
B:A silver quality gate must reject records missing a transaction identifier, but analysts still need an error count. In those circumstances, “Use a documented data quality constraint with a quarantine or reject accounting strategy” fits. Validate currencies and join the approved conversion reference before writing standardized silver amounts.
C:When a line-item DataFrame contains an array of product codes per order and downstream rows must be per product, “Explode the product-code array into one output row per array element” could be relevant. Validate currencies and join the approved conversion reference before writing standardized silver amounts.
Question 186
A marketing bronze feed arrives with mixed currency columns, but conversion rates are supplied in a governed reference table. Which implementation step is most appropriate before production use?
- Supply both key equality predicates to the Spark join condition.
- Join on the product key with an inner join and select the disambiguated columns.
- Join by currency and effective date, reject unmatched rates, and create typed base-currency amount columns.
- Define a supported materialized view in Unity Catalog and configure refresh according to requirements.
Explanation
Correct Answer
C:Join by currency and effective date, reject unmatched rates, and create typed base-currency amount columns. An audit sample reproduces each converted value using the effective-date exchange rate.
Incorrect Answers
A:A customer match requires both organization identifier and customer identifier, since a customer number is reused by different businesses. In those circumstances, “Supply both key equality predicates to the Spark join condition” fits. Join by currency and effective date, reject unmatched rates, and create typed base-currency amount columns.
B:When a product-order dataset needs one enriched row only when a matching dimension key exists, “Join on the product key with an inner join and select the disambiguated columns” could be relevant. Join by currency and effective date, reject unmatched rates, and create typed base-currency amount columns.
D:A busy BI dashboard repeatedly runs an expensive grouped query and tolerates refresh-based staleness. In those circumstances, “Define a supported materialized view in Unity Catalog and configure refresh according to requirements” fits. Join by currency and effective date, reject unmatched rates, and create typed base-currency amount columns.
Question 187
A marketing bronze feed arrives with mixed currency columns, but conversion rates are supplied in a governed reference table. Which acceptance test provides the strongest evidence that the intended outcome has been achieved?
- An audit sample reproduces each converted value using the effective-date exchange rate.
- Sample control totals match by period and partition without unexplained duplicate keys.
- The executed plan shows a broadcast join and no large dimension repartition overhead.
- Orders with valid matching products appear once according to dimension key uniqueness; unmatched orders are absent.
Explanation
Correct Answer
A:An audit sample reproduces each converted value using the effective-date exchange rate.
Incorrect Answers
B:If an insurance gold fact table combines multiple silver sources with different dates and must remain reconcilable, “Sample control totals match by period and partition without unexplained duplicate keys” may be the appropriate response. An audit sample reproduces each converted value using the effective-date exchange rate.
C:A small static country table enriches a huge events DataFrame and a large shuffle must be avoided when eligible. In those circumstances, “The executed plan shows a broadcast join and no large dimension repartition overhead” fits. An audit sample reproduces each converted value using the effective-date exchange rate.
D:When a product-order dataset needs one enriched row only when a matching dimension key exists, “Orders with valid matching products appear once according to dimension key uniqueness; unmatched orders are absent” could be relevant. An audit sample reproduces each converted value using the effective-date exchange rate.
Question 188
A marketing bronze feed arrives with mixed currency columns, but conversion rates are supplied in a governed reference table. The team observes: Some amounts are multiplied by today's rate instead of the transaction-date rate. Which root cause should be addressed first given this evidence?
- The transform stored the whole array as a scalar rather than expanding it.
- The transform used a left outer join or failed to filter unmatched records.
- The partition or memory design exceeds available per-task memory.
- The silver transformation joined rates without the effective-date condition.
Explanation
Correct Answer
D:The silver transformation joined rates without the effective-date condition. Join by currency and effective date, reject unmatched rates, and create typed base-currency amount columns.
Incorrect Answers
A:If a line-item DataFrame contains an array of product codes per order and downstream rows must be per product, “The transform stored the whole array as a scalar rather than expanding it” may be the appropriate response. The silver transformation joined rates without the effective-date condition.
B:A product-order dataset needs one enriched row only when a matching dimension key exists. In those circumstances, “The transform used a left outer join or failed to filter unmatched records” fits. The silver transformation joined rates without the effective-date condition.
C:When a large join spills to disk repeatedly although executor memory pressure is high, “The partition or memory design exceeds available per-task memory” could be relevant. The silver transformation joined rates without the effective-date condition.
Question 189
A product-order dataset needs one enriched row only when a matching dimension key exists. Which technical design is most appropriate for the stated constraint?
- Project only the explicitly approved fields rather than carrying the full source schema.
- Consider an eligible broadcast join and the autoBroadcastJoinThreshold setting.
- Choose an inner join between order facts and the matching product dimension.
- Standardize state labels and treat an optional null independently of required identity fields.
Explanation
Correct Answer
C:Choose an inner join between order facts and the matching product dimension. Orders with valid matching products appear once according to dimension key uniqueness; unmatched orders are absent.
Incorrect Answers
A:An account table has three irrelevant vendor-internal columns that must not reach the published silver table. In those circumstances, “Project only the explicitly approved fields rather than carrying the full source schema” fits. Choose an inner join between order facts and the matching product dimension.
B:When a large fact-to-small-dimension join repeatedly performs an avoidable shuffle, “Consider an eligible broadcast join and the autoBroadcastJoinThreshold setting” could be relevant. Choose an inner join between order facts and the matching product dimension.
D:A CRM ingestion contains two representations of the same state name and optional phone fields that are sometimes null. In those circumstances, “Standardize state labels and treat an optional null independently of required identity fields” fits. Choose an inner join between order facts and the matching product dimension.
Question 190
A product-order dataset needs one enriched row only when a matching dimension key exists. Which action should the engineer perform to establish the intended control?
- Group the relevant rows and apply approx_count_distinct to device_id.
- Apply a documented timestamp parser and timezone normalization while writing typed silver columns.
- Join on the product key with an inner join and select the disambiguated columns.
- Use dropDuplicates on event_id after defining the desired event window or batch boundary.
Explanation
Correct Answer
C:Join on the product key with an inner join and select the disambiguated columns. Orders with valid matching products appear once according to dimension key uniqueness; unmatched orders are absent.
Incorrect Answers
A:When a reporting table must count unique devices across billions of events with an acceptable approximation, “Group the relevant rows and apply approx_count_distinct to device_id” could be relevant. Join on the product key with an inner join and select the disambiguated columns.
B:If a payroll bronze table has event timestamps saved as arbitrary text from two time zones, “Apply a documented timestamp parser and timezone normalization while writing typed silver columns” may be the appropriate response. Join on the product key with an inner join and select the disambiguated columns.
D:When a click event feed contains retries with identical event_id and should count each business event once, “Use dropDuplicates on event_id after defining the desired event window or batch boundary” could be relevant. Join on the product key with an inner join and select the disambiguated columns.
Certified Data Engineer Associate Practice Tests