Certified Data Engineer Associate Practice Test 31
Question 300
At a census statistics agency, a Spark SQL aggregation creates a few oversized shuffle partitions while most tasks finish quickly. The dataset `account_statements` is in scope, and downstream dashboards cannot accept fabricated values. Before release, which acceptance test would actually demonstrate that the root problem is controlled?
- Review a platform-level availability report for `account_statements` without testing whether the long tail is caused by uneven shuffle partition sizes, not only an insufficient total number of executors.
- Check that the intended configuration exists for `account_statements` and that a healthy run succeeded, without inducing the earlier failure.
- Run the aggregation before and after the selected partitioning change and compare long-tail duration and spill
- Run only a normal, unchanged `account_statements` sample and review Spark stage task-time spread.
Explanation
Correct Answer
C:Run the aggregation before and after the selected partitioning change and compare long-tail duration and spill. This reproduces the scenario-dependent condition, verifies Spark stage task-time spread, max/median shuffle bytes, spill metrics, and skewed key frequencies, and directly tests whether the long tail is caused by uneven shuffle partition sizes, not only an insufficient total number of executors is resolved. Observed results, not intent, determine approval.
Incorrect Answers
A:Review a platform-level availability report for `account_statements` without testing whether the long tail is caused by uneven shuffle partition sizes, not only an insufficient total number of executors. Aggregate availability does not establish the behavior of affected `account_statements` records. It cannot replace the direct exercise: run the aggregation before and after the selected partitioning change and compare long-tail duration and spill.
B:Check that the intended configuration exists for `account_statements` and that a healthy run succeeded, without inducing the earlier failure. Installed configuration and a healthy run are insufficient to prove the failure mode is gone. The original issue was the long tail is caused by uneven shuffle partition sizes, not only an insufficient total number of executors.
D:Run only a normal, unchanged `account_statements` sample and review Spark stage task-time spread. A normal sample does not challenge the long tail is caused by uneven shuffle partition sizes, not only an insufficient total number of executors.
Question 301
At a logistics carrier, a small dimension join is unexpectedly expensive after the broadcast threshold was lowered in a new runtime configuration. The dataset `fraud_signals` is in scope, and raw records must remain available for replay. What evidence should the engineer collect to establish the cause rather than a coincidental symptom?
- Correlate dimension size, physical join plan across affected and unaffected `R034-CASE-061` runs.
- Review completed run status and aggregate cluster health without record-level correlation
- Compare the current configuration and the signed change approval, without source-row evidence
- Examine only a normal successful run of `fraud_signals` and trend dimension size without recreating the fault.
Explanation
Correct Answer
A:Correlate dimension size, physical join plan across affected and unaffected `R034-CASE-061` runs.. By joining dimension size, physical join plan, shuffle read, and query duration before and after tuning across the affected and unaffected conditions, the investigation directly tests the recorded mechanism: the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast. It also sets a measurable baseline for reproduce the query at known dimension sizes and confirm the intended join strategy plus measured improvement.
Incorrect Answers
B:Review completed run status and aggregate cluster health without record-level correlation. A successful aggregate platform state can coexist with the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast. This check never observes the affected `fraud_signals` records or their causal path.
C:Compare the current configuration and the signed change approval, without source-row evidence. A desired configuration and approval ticket are not runtime proof. In this case they omit dimension size, physical join plan, shuffle read, and query duration before and after tuning, the direct means of distinguishing the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
D:Examine only a normal successful run of `fraud_signals` and trend dimension size without recreating the fault. A healthy-run trend can be useful, but it does not expose the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast. The failing-path dimension size, physical join plan, shuffle read, and query duration before and after tuning has to be correlated before attributing cause.
Question 302
At a logistics carrier, a small dimension join is unexpectedly expensive after the broadcast threshold was lowered in a new runtime configuration. The dataset `fraud_signals` is in scope, and raw records must remain available for replay. Which platform configuration or data-engineering change is most defensible?
- Disable all adaptive query optimizations globally; use that approach for `fraud_signals`.
- Force the larger fact DataFrame as the broadcast side; use that approach for `fraud_signals`.
- Double executor memory and stop without checking the new plan; use that approach for `fraud_signals`.
- Inspect the physical join plan and input sizes, then set a safe broadcast strategy and remeasure the join
Explanation
Correct Answer
D:Inspect the physical join plan and input sizes, then set a safe broadcast strategy and remeasure the join. This directly addresses the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast. The design should be accepted only after reproduce the query at known dimension sizes and confirm the intended join strategy plus measured improvement; the related operational boundary is to tune the affected join in a bounded manner rather than changing unrelated global compute limits.
Incorrect Answers
A:Disable all adaptive query optimizations globally; use that approach for `fraud_signals`. Removing adaptive choices may degrade other workloads without fixing the local threshold. For `fraud_signals`, this cannot remove the causal limitation: the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
B:Force the larger fact DataFrame as the broadcast side; use that approach for `fraud_signals`. Replicating a large fact DataFrame risks out-of-memory errors. For `fraud_signals`, this cannot remove the causal limitation: the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
C:Double executor memory and stop without checking the new plan; use that approach for `fraud_signals`. Memory alone does not prove the optimizer selected the intended join method. For `fraud_signals`, this cannot remove the causal limitation: the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
Question 303
At a logistics carrier, a small dimension join is unexpectedly expensive after the broadcast threshold was lowered in a new runtime configuration. The dataset `fraud_signals` is in scope, and raw records must remain available for replay. A teammate proposes: "Disable all adaptive query optimizations globally." Which technical limitation is the decisive reason not to approve that specific proposal?
- Removing adaptive choices may degrade other workloads without fixing the local threshold. The proposal does not repair `fraud_signals`.
- Memory alone does not prove the optimizer selected the intended join method. This would apply to another option for `fraud_signals`.
- The dashboard cached the prior result; no DataFrame operation or underlying data requires correction.
- Replicating a large fact DataFrame risks out-of-memory errors. This would apply to an alternative change for `fraud_signals`.
Explanation
Correct Answer
A:Removing adaptive choices may degrade other workloads without fixing the local threshold. The proposal does not repair `fraud_signals`. This identifies the known limitation of the exact proposed action. Removing adaptive choices may degrade other workloads without fixing the local threshold. The underlying problem remains the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast; Inspect the physical join plan and input sizes, then set a safe broadcast strategy and remeasure the join instead targets that failure.
Incorrect Answers
B:Memory alone does not prove the optimizer selected the intended join method. This would apply to another option for `fraud_signals`. This limitation belongs to the different alternative "Double executor memory and stop without checking the new plan". The stem calls for the specific failure of "Disable all adaptive query optimizations globally".
C:The dashboard cached the prior result; no DataFrame operation or underlying data requires correction. The case provides a concrete failing data or control path; a mere alternate theory does not address the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
D:Replicating a large fact DataFrame risks out-of-memory errors. This would apply to an alternative change for `fraud_signals`. This failure applies to the separate proposal "Force the larger fact DataFrame as the broadcast side".
Question 304
At a logistics carrier, a small dimension join is unexpectedly expensive after the broadcast threshold was lowered in a new runtime configuration. The dataset `fraud_signals` is in scope, and raw records must remain available for replay. What risk-and-cost compromise should the owner accept instead of the proposed shortcuts?
- Double executor memory and stop without checking the new plan
- Disable all adaptive query optimizations globally
- Force the larger fact DataFrame as the broadcast side; monitor results only after launch of `fraud_signals`.
- Tune the affected join in a bounded manner rather than changing unrelated global compute limits
Explanation
Correct Answer
D:Tune the affected join in a bounded manner rather than changing unrelated global compute limits. This explicitly bounds the compromise while still addressing the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast. Acceptance relies on reproduce the query at known dimension sizes and confirm the intended join strategy plus measured improvement rather than simply declaring the change complete.
Incorrect Answers
A:Double executor memory and stop without checking the new plan. Memory alone does not prove the optimizer selected the intended join method. Monitoring after deployment does not make this approach a safe bounded tradeoff for `fraud_signals`.
B:Disable all adaptive query optimizations globally. Removing adaptive choices may degrade other workloads without fixing the local threshold. Monitoring after deployment does not make this approach a safe bounded tradeoff for `fraud_signals`.
C:Force the larger fact DataFrame as the broadcast side; monitor results only after launch of `fraud_signals`. Replicating a large fact DataFrame risks out-of-memory errors. Monitoring after deployment does not make this approach a safe bounded tradeoff for `fraud_signals`.
Question 305
At a logistics carrier, a small dimension join is unexpectedly expensive after the broadcast threshold was lowered in a new runtime configuration. The dataset `fraud_signals` is in scope, and raw records must remain available for replay. Which direct test could falsify the proposed fix instead of only confirming deployment?
- Check that the intended configuration exists for `fraud_signals` and that a healthy run succeeded, without inducing the earlier failure.
- Reproduce the query at known dimension sizes and confirm the intended join strategy plus measured improvement
- Review a platform-level availability report for `fraud_signals` without testing whether the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
- Run only a normal, unchanged `fraud_signals` sample and review dimension size.
Explanation
Correct Answer
B:Reproduce the query at known dimension sizes and confirm the intended join strategy plus measured improvement. This reproduces the scenario-dependent condition, verifies dimension size, physical join plan, shuffle read, and query duration before and after tuning, and directly tests whether the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast is resolved. Observed results, not intent, determine approval.
Incorrect Answers
A:Check that the intended configuration exists for `fraud_signals` and that a healthy run succeeded, without inducing the earlier failure. Installed configuration and a healthy run are insufficient to prove the failure mode is gone. The original issue was the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
C:Review a platform-level availability report for `fraud_signals` without testing whether the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast. Aggregate availability does not establish the behavior of affected `fraud_signals` records. It cannot replace the direct exercise: reproduce the query at known dimension sizes and confirm the intended join strategy plus measured improvement.
D:Run only a normal, unchanged `fraud_signals` sample and review dimension size. A normal sample does not challenge the optimizer may choose a shuffle join after a threshold change even when a small dimension could be broadcast.
Question 306
At a hospital analytics group, finance analysts repeatedly execute an expensive stable aggregate over a slowly changing silver Delta table. The dataset `shipment_status` is in scope, and downstream dashboards cannot accept fabricated values. Which correlated observation is the strongest basis for the change decision?
- Correlate silver change timestamps, query cost across affected and unaffected `R034-CASE-062` runs.
- Review completed run status and aggregate cluster health without record-level correlation
- Compare the current configuration and the signed change approval, without source-row evidence
- Inspect normal-period success metrics for the stated dataset without reproducing the source error
Explanation
Correct Answer
A:Correlate silver change timestamps, query cost across affected and unaffected `R034-CASE-062` runs.. By joining silver change timestamps, query cost, materialized view refresh state, and freshness SLA across the affected and unaffected conditions, the investigation directly tests the recorded mechanism: a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately. It also sets a measurable baseline for update one silver partition, refresh the materialized view, and compare gold totals with the source.
Incorrect Answers
B:Review completed run status and aggregate cluster health without record-level correlation. A successful aggregate platform state can coexist with a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately. This check never observes the affected `shipment_status` records or their causal path.
C:Compare the current configuration and the signed change approval, without source-row evidence. A desired configuration and approval ticket are not runtime proof. In this case they omit silver change timestamps, query cost, materialized view refresh state, and freshness SLA, the direct means of distinguishing a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.
D:Inspect normal-period success metrics for the stated dataset without reproducing the source error. A healthy-run trend can be useful, but it does not expose a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately. The failing-path silver change timestamps, query cost, materialized view refresh state, and freshness SLA has to be correlated before attributing cause.
Question 307
At a hospital analytics group, finance analysts repeatedly execute an expensive stable aggregate over a slowly changing silver Delta table. The dataset `shipment_status` is in scope, and downstream dashboards cannot accept fabricated values. What implementation should the engineer select for the failing workflow?
- Publish a governed materialized view with an appropriate refresh cadence and measure result freshness
- Use a plain view and assume its aggregate output is stored persistently; use that approach for `shipment_status`.
- Copy the query result into an unmanaged CSV folder for each analyst; use that approach for `shipment_status`.
- Use a streaming table for an unrelated continuously arriving feed when no stream exists; use that approach for `shipment_status`.
Explanation
Correct Answer
A:Publish a governed materialized view with an appropriate refresh cadence and measure result freshness. This directly addresses a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately. The design should be accepted only after update one silver partition, refresh the materialized view, and compare gold totals with the source; the related operational boundary is to accept controlled refresh work in exchange for lower repeated dashboard-query compute.
Incorrect Answers
B:Use a plain view and assume its aggregate output is stored persistently; use that approach for `shipment_status`. A standard SQL view stores its definition, not a materialized aggregate result. For `shipment_status`, this cannot remove the causal limitation: a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.
C:Copy the query result into an unmanaged CSV folder for each analyst; use that approach for `shipment_status`. Unmanaged copies lose standardized governance and refresh semantics. For `shipment_status`, this cannot remove the causal limitation: a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.
D:Use a streaming table for an unrelated continuously arriving feed when no stream exists; use that approach for `shipment_status`. A streaming abstraction is not needed for the stated periodic stable aggregate. For `shipment_status`, this cannot remove the causal limitation: a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.
Question 308
At a hospital analytics group, finance analysts repeatedly execute an expensive stable aggregate over a slowly changing silver Delta table. The dataset `shipment_status` is in scope, and downstream dashboards cannot accept fabricated values. A teammate proposes: "Use a streaming table for an unrelated continuously arriving feed when no stream exists." Which technical limitation is the decisive reason not to approve that specific proposal?
- A standard SQL view stores its definition, not a materialized aggregate result. This would apply to an alternative change for `shipment_status`.
- The dashboard cached the prior result; no DataFrame operation or underlying data requires correction.
- Unmanaged copies lose standardized governance and refresh semantics. This would apply to another option for `shipment_status`.
- A streaming abstraction is not needed for the stated periodic stable aggregate. The proposal does not repair `shipment_status`.
Explanation
Correct Answer
D:A streaming abstraction is not needed for the stated periodic stable aggregate. The proposal does not repair `shipment_status`. This identifies the known limitation of the exact proposed action. A streaming abstraction is not needed for the stated periodic stable aggregate. The underlying problem remains a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately; Publish a governed materialized view with an appropriate refresh cadence and measure result freshness instead targets that failure.
Incorrect Answers
A:A standard SQL view stores its definition, not a materialized aggregate result. This would apply to an alternative change for `shipment_status`. This failure applies to the separate proposal "Use a plain view and assume its aggregate output is stored persistently".
B:The dashboard cached the prior result; no DataFrame operation or underlying data requires correction. The case provides a concrete failing data or control path; a mere alternate theory does not address a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.
C:Unmanaged copies lose standardized governance and refresh semantics. This would apply to another option for `shipment_status`. This limitation belongs to the different alternative "Copy the query result into an unmanaged CSV folder for each analyst". The stem calls for the specific failure of "Use a streaming table for an unrelated continuously arriving feed when no stream exists".
Question 309
At a hospital analytics group, finance analysts repeatedly execute an expensive stable aggregate over a slowly changing silver Delta table. The dataset `shipment_status` is in scope, and downstream dashboards cannot accept fabricated values. Which decision preserves the stated data contract while keeping the change scope proportionate?
- Copy the query result into an unmanaged CSV folder for each analyst
- Use a plain view and assume its aggregate output is stored persistently
- Use a streaming table for an unrelated continuously arriving feed when no stream exists
- Accept controlled refresh work in exchange for lower repeated dashboard-query compute
Explanation
Correct Answer
D:Accept controlled refresh work in exchange for lower repeated dashboard-query compute. This explicitly bounds the compromise while still addressing a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately. Acceptance relies on update one silver partition, refresh the materialized view, and compare gold totals with the source rather than simply declaring the change complete.
Incorrect Answers
A:Copy the query result into an unmanaged CSV folder for each analyst. Unmanaged copies lose standardized governance and refresh semantics. Monitoring after deployment does not make this approach a safe bounded tradeoff for `shipment_status`.
B:Use a plain view and assume its aggregate output is stored persistently. A standard SQL view stores its definition, not a materialized aggregate result. Monitoring after deployment does not make this approach a safe bounded tradeoff for `shipment_status`.
C:Use a streaming table for an unrelated continuously arriving feed when no stream exists. A streaming abstraction is not needed for the stated periodic stable aggregate. Monitoring after deployment does not make this approach a safe bounded tradeoff for `shipment_status`.
Question 310
At a hospital analytics group, finance analysts repeatedly execute an expensive stable aggregate over a slowly changing silver Delta table. The dataset `shipment_status` is in scope, and downstream dashboards cannot accept fabricated values. What verification provides evidence of correct behavior under the original adverse condition?
- Review a platform-level availability report for `shipment_status` without testing whether a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.
- Update one silver partition, refresh the materialized view, and compare gold totals with the source
- Run only a normal, unchanged `shipment_status` sample and review silver change timestamps.
- Check that the intended configuration exists for `shipment_status` and that a healthy run succeeded, without inducing the earlier failure.
Explanation
Correct Answer
B:Update one silver partition, refresh the materialized view, and compare gold totals with the source. This reproduces the scenario-dependent condition, verifies silver change timestamps, query cost, materialized view refresh state, and freshness SLA, and directly tests whether a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately is resolved. Observed results, not intent, determine approval.
Incorrect Answers
A:Review a platform-level availability report for `shipment_status` without testing whether a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately. Aggregate availability does not establish the behavior of affected `shipment_status` records. It cannot replace the direct exercise: update one silver partition, refresh the materialized view, and compare gold totals with the source.
C:Run only a normal, unchanged `shipment_status` sample and review silver change timestamps. A normal sample does not challenge a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.
D:Check that the intended configuration exists for `shipment_status` and that a healthy run succeeded, without inducing the earlier failure. Installed configuration and a healthy run are insufficient to prove the failure mode is gone. The original issue was a conventional view recalculates the expensive query on use while a precomputed result can be refreshed deliberately.