Certified Data Engineer Associate Practice Test 9
Question 80
At a consumer-lending business, a governed bronze table receives new partitioned JSON drops, but the SQL ingestion job points at the wrong format and path. The dataset `patient_visits` is in scope, and an interrupted delivery must be recoverable without data loss. Before release, which acceptance test would actually demonstrate that the root problem is controlled?
- Check that the intended configuration exists for `patient_visits` and that a healthy run succeeded, without inducing the earlier failure.
- Run only a normal, unchanged `patient_visits` sample and review object path listing.
- Review a platform-level availability report for `patient_visits` without testing whether the source schema and storage location must match the command before loading can succeed.
- Load a new sample JSON file, compare parsed columns and row counts, then retry safely without duplicate rows
Explanation
Correct Answer
D:Load a new sample JSON file, compare parsed columns and row counts, then retry safely without duplicate rows. This reproduces the scenario-dependent condition, verifies object path listing, file type, COPY INTO query error, and authorized external location, and directly tests whether the source schema and storage location must match the command before loading can succeed is resolved. Observed results, not intent, determine approval.
Incorrect Answers
A:Check that the intended configuration exists for `patient_visits` 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 source schema and storage location must match the command before loading can succeed.
B:Run only a normal, unchanged `patient_visits` sample and review object path listing. A normal sample does not challenge the source schema and storage location must match the command before loading can succeed.
C:Review a platform-level availability report for `patient_visits` without testing whether the source schema and storage location must match the command before loading can succeed. Aggregate availability does not establish the behavior of affected `patient_visits` records. It cannot replace the direct exercise: load a new sample JSON file, compare parsed columns and row counts, then retry safely without duplicate rows.
Question 81
At a wholesale pharmacy group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `courier_events` is in scope, and the target must retain every valid late-arriving event. What evidence should the engineer collect to establish the cause rather than a coincidental symptom?
- Correlate source file identifiers, COPY INTO run results across affected and unaffected `R034-CASE-017` runs.
- Compare the current configuration and the signed change approval, without source-row evidence
- Review completed run status and aggregate cluster health without record-level correlation
- Inspect normal-period success metrics for the stated dataset without reproducing the source error
Explanation
Correct Answer
A:Correlate source file identifiers, COPY INTO run results across affected and unaffected `R034-CASE-017` runs.. By joining source file identifiers, COPY INTO run results, target Delta commits, and repeated-file counts across the affected and unaffected conditions, the investigation directly tests the recorded mechanism: a naive INSERT from all source files has no source-file idempotency tracking on reruns. It also sets a measurable baseline for run the identical COPY INTO twice against unchanged files and confirm only the first adds their records.
Incorrect Answers
B: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 source file identifiers, COPY INTO run results, target Delta commits, and repeated-file counts, the direct means of distinguishing a naive INSERT from all source files has no source-file idempotency tracking on reruns.
C:Review completed run status and aggregate cluster health without record-level correlation. A successful aggregate platform state can coexist with a naive INSERT from all source files has no source-file idempotency tracking on reruns. This check never observes the affected `courier_events` records or their causal path.
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 naive INSERT from all source files has no source-file idempotency tracking on reruns. The failing-path source file identifiers, COPY INTO run results, target Delta commits, and repeated-file counts has to be correlated before attributing cause.
Question 82
At a wholesale pharmacy group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `courier_events` is in scope, and the target must retain every valid late-arriving event. Which platform configuration or data-engineering change is most defensible?
- Truncate the destination table before every COPY INTO attempt; use that approach for `courier_events`.
- Append the entire S3 folder with INSERT INTO after every retry; use that approach for `courier_events`.
- Use COPY INTO on a Delta target with its default processed-file tracking and do not force reprocessing
- Run COPY INTO with forced reprocessing for every routine retry; use that approach for `courier_events`.
Explanation
Correct Answer
C:Use COPY INTO on a Delta target with its default processed-file tracking and do not force reprocessing. This directly addresses a naive INSERT from all source files has no source-file idempotency tracking on reruns. The design should be accepted only after run the identical COPY INTO twice against unchanged files and confirm only the first adds their records; the related operational boundary is to preserve file-level idempotency even if the scheduler attempts the command more than once.
Incorrect Answers
A:Truncate the destination table before every COPY INTO attempt; use that approach for `courier_events`. Truncation destroys previously committed history and is not a safe retry policy. For `courier_events`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
B:Append the entire S3 folder with INSERT INTO after every retry; use that approach for `courier_events`. Repeated append re-ingests historical files because it has no COPY INTO file history. For `courier_events`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
D:Run COPY INTO with forced reprocessing for every routine retry; use that approach for `courier_events`. Forcing reprocessing defeats default file-based deduplication. For `courier_events`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
Question 83
At a wholesale pharmacy group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `courier_events` is in scope, and the target must retain every valid late-arriving event. A teammate proposes: "Append the entire S3 folder with INSERT INTO after every retry." Which technical limitation is the decisive reason not to approve that specific proposal?
- Truncation destroys previously committed history and is not a safe retry policy. This would apply to another option for `courier_events`.
- Repeated append re-ingests historical files because it has no COPY INTO file history. The proposal does not repair `courier_events`.
- Forcing reprocessing defeats default file-based deduplication. This would apply to an alternative change for `courier_events`.
- Insufficient executor memory is the primary fault; source-file handling and ingestion checkpoints are correct.
Explanation
Correct Answer
B:Repeated append re-ingests historical files because it has no COPY INTO file history. The proposal does not repair `courier_events`. This identifies the known limitation of the exact proposed action. Repeated append re-ingests historical files because it has no COPY INTO file history. The underlying problem remains a naive INSERT from all source files has no source-file idempotency tracking on reruns; Use COPY INTO on a Delta target with its default processed-file tracking and do not force reprocessing instead targets that failure.
Incorrect Answers
A:Truncation destroys previously committed history and is not a safe retry policy. This would apply to another option for `courier_events`. This limitation belongs to the different alternative "Truncate the destination table before every COPY INTO attempt". The stem calls for the specific failure of "Append the entire S3 folder with INSERT INTO after every retry".
C:Forcing reprocessing defeats default file-based deduplication. This would apply to an alternative change for `courier_events`. This failure applies to the separate proposal "Run COPY INTO with forced reprocessing for every routine retry".
D:Insufficient executor memory is the primary fault; source-file handling and ingestion checkpoints are correct. The case provides a concrete failing data or control path; a mere alternate theory does not address a naive INSERT from all source files has no source-file idempotency tracking on reruns.
Question 84
At a wholesale pharmacy group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `courier_events` is in scope, and the target must retain every valid late-arriving event. What risk-and-cost compromise should the owner accept instead of the proposed shortcuts?
- Append the entire S3 folder with INSERT INTO after every retry
- Run COPY INTO with forced reprocessing for every routine retry
- Truncate the destination table before every COPY INTO attempt
- Preserve file-level idempotency even if the scheduler attempts the command more than once
Explanation
Correct Answer
D:Preserve file-level idempotency even if the scheduler attempts the command more than once. This explicitly bounds the compromise while still addressing a naive INSERT from all source files has no source-file idempotency tracking on reruns. Acceptance relies on run the identical COPY INTO twice against unchanged files and confirm only the first adds their records rather than simply declaring the change complete.
Incorrect Answers
A:Append the entire S3 folder with INSERT INTO after every retry. Repeated append re-ingests historical files because it has no COPY INTO file history. Monitoring after deployment does not make this approach a safe bounded tradeoff for `courier_events`.
B:Run COPY INTO with forced reprocessing for every routine retry. Forcing reprocessing defeats default file-based deduplication. Monitoring after deployment does not make this approach a safe bounded tradeoff for `courier_events`.
C:Truncate the destination table before every COPY INTO attempt. Truncation destroys previously committed history and is not a safe retry policy. Monitoring after deployment does not make this approach a safe bounded tradeoff for `courier_events`.
Question 85
At a wholesale pharmacy group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `courier_events` is in scope, and the target must retain every valid late-arriving event. Which direct test could falsify the proposed fix instead of only confirming deployment?
- Check that the intended configuration exists for `courier_events` and that a healthy run succeeded, without inducing the earlier failure.
- Run only a normal, unchanged `courier_events` sample and review source file identifiers.
- Run the identical COPY INTO twice against unchanged files and confirm only the first adds their records
- Review a platform-level availability report for `courier_events` without testing whether a naive INSERT from all source files has no source-file idempotency tracking on reruns.
Explanation
Correct Answer
C:Run the identical COPY INTO twice against unchanged files and confirm only the first adds their records. This reproduces the scenario-dependent condition, verifies source file identifiers, COPY INTO run results, target Delta commits, and repeated-file counts, and directly tests whether a naive INSERT from all source files has no source-file idempotency tracking on reruns is resolved. Observed results, not intent, determine approval.
Incorrect Answers
A:Check that the intended configuration exists for `courier_events` 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 naive INSERT from all source files has no source-file idempotency tracking on reruns.
B:Run only a normal, unchanged `courier_events` sample and review source file identifiers. A normal sample does not challenge a naive INSERT from all source files has no source-file idempotency tracking on reruns.
D:Review a platform-level availability report for `courier_events` without testing whether a naive INSERT from all source files has no source-file idempotency tracking on reruns. Aggregate availability does not establish the behavior of affected `courier_events` records. It cannot replace the direct exercise: run the identical COPY INTO twice against unchanged files and confirm only the first adds their records.
Question 86
At a digital publishing service, a streaming JSON feed added a new optional field and Auto Loader stopped at a schema evolution exception. The dataset `maintenance_events` is in scope, and bronze tables must remain governed by Unity Catalog. Which correlated observation is the strongest basis for the change decision?
- Examine only a normal successful run of `maintenance_events` and trend new file schema without recreating the fault.
- Compare the saved configuration with the signed change ticket for `maintenance_events` while omitting source and output records.
- Correlate new file schema, _schemas changes across affected and unaffected `R034-CASE-018` runs.
- Review aggregate cluster availability and job completion for `maintenance_events` without correlating the observed error path.
Explanation
Correct Answer
C:Correlate new file schema, _schemas changes across affected and unaffected `R034-CASE-018` runs.. By joining new file schema, _schemas changes, stream exception, and post-restart bronze column count across the affected and unaffected conditions, the investigation directly tests the recorded mechanism: Auto Loader records the new field and may stop the stream so its schema change can be applied on restart. It also sets a measurable baseline for inject a JSON field not present in the first batch and confirm the restarted stream retains it without losing records.
Incorrect Answers
A:Examine only a normal successful run of `maintenance_events` and trend new file schema without recreating the fault. A healthy-run trend can be useful, but it does not expose Auto Loader records the new field and may stop the stream so its schema change can be applied on restart. The failing-path new file schema, _schemas changes, stream exception, and post-restart bronze column count has to be correlated before attributing cause.
B:Compare the saved configuration with the signed change ticket for `maintenance_events` while omitting source and output records. A desired configuration and approval ticket are not runtime proof. In this case they omit new file schema, _schemas changes, stream exception, and post-restart bronze column count, the direct means of distinguishing Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
D:Review aggregate cluster availability and job completion for `maintenance_events` without correlating the observed error path. A successful aggregate platform state can coexist with Auto Loader records the new field and may stop the stream so its schema change can be applied on restart. This check never observes the affected `maintenance_events` records or their causal path.
Question 87
At a digital publishing service, a streaming JSON feed added a new optional field and Auto Loader stopped at a schema evolution exception. The dataset `maintenance_events` is in scope, and bronze tables must remain governed by Unity Catalog. What implementation should the engineer select for the failing workflow?
- Retain the schema location, use a supported add-new-columns evolution mode, and restart after Auto Loader updates the schema
- Use failOnNewColumns indefinitely while demanding automatic schema evolution; use that approach for `maintenance_events`.
- Delete both checkpoint and schema location and replay everything; use that approach for `maintenance_events`.
- Continue to parse only the previous schema and silently discard the new field; use that approach for `maintenance_events`.
Explanation
Correct Answer
A:Retain the schema location, use a supported add-new-columns evolution mode, and restart after Auto Loader updates the schema. This directly addresses Auto Loader records the new field and may stop the stream so its schema change can be applied on restart. The design should be accepted only after inject a JSON field not present in the first batch and confirm the restarted stream retains it without losing records; the related operational boundary is to accept a controlled stream restart for schema evolution rather than losing newly arriving attributes.
Incorrect Answers
B:Use failOnNewColumns indefinitely while demanding automatic schema evolution; use that approach for `maintenance_events`. Failing on every unknown field does not deliver the requested automatic adaptation. For `maintenance_events`, this cannot remove the causal limitation: Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
C:Delete both checkpoint and schema location and replay everything; use that approach for `maintenance_events`. Erasing progress and inference history risks replay and inconsistent schema discovery. For `maintenance_events`, this cannot remove the causal limitation: Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
D:Continue to parse only the previous schema and silently discard the new field; use that approach for `maintenance_events`. Ignoring the new field violates the requirement to retain the source attribute. For `maintenance_events`, this cannot remove the causal limitation: Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
Question 88
At a digital publishing service, a streaming JSON feed added a new optional field and Auto Loader stopped at a schema evolution exception. The dataset `maintenance_events` is in scope, and bronze tables must remain governed by Unity Catalog. A teammate proposes: "Delete both checkpoint and schema location and replay everything." Which technical limitation is the decisive reason not to approve that specific proposal?
- Ignoring the new field violates the requirement to retain the source attribute. This would apply to another option for `maintenance_events`.
- Failing on every unknown field does not deliver the requested automatic adaptation. This would apply to an alternative change for `maintenance_events`.
- Erasing progress and inference history risks replay and inconsistent schema discovery. The proposal does not repair `maintenance_events`.
- Insufficient executor memory is the primary fault; source-file handling and ingestion checkpoints are correct.
Explanation
Correct Answer
C:Erasing progress and inference history risks replay and inconsistent schema discovery. The proposal does not repair `maintenance_events`. This identifies the known limitation of the exact proposed action. Erasing progress and inference history risks replay and inconsistent schema discovery. The underlying problem remains Auto Loader records the new field and may stop the stream so its schema change can be applied on restart; Retain the schema location, use a supported add-new-columns evolution mode, and restart after Auto Loader updates the schema instead targets that failure.
Incorrect Answers
A:Ignoring the new field violates the requirement to retain the source attribute. This would apply to another option for `maintenance_events`. This limitation belongs to the different alternative "Continue to parse only the previous schema and silently discard the new field". The stem calls for the specific failure of "Delete both checkpoint and schema location and replay everything".
B:Failing on every unknown field does not deliver the requested automatic adaptation. This would apply to an alternative change for `maintenance_events`. This failure applies to the separate proposal "Use failOnNewColumns indefinitely while demanding automatic schema evolution".
D:Insufficient executor memory is the primary fault; source-file handling and ingestion checkpoints are correct. The case provides a concrete failing data or control path; a mere alternate theory does not address Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
Question 89
At a digital publishing service, a streaming JSON feed added a new optional field and Auto Loader stopped at a schema evolution exception. The dataset `maintenance_events` is in scope, and bronze tables must remain governed by Unity Catalog. Which decision preserves the stated data contract while keeping the change scope proportionate?
- Delete both checkpoint and schema location and replay everything; monitor results only after launch of `maintenance_events`.
- Continue to parse only the previous schema and silently discard the new field; monitor results only after launch of `maintenance_events`.
- Accept a controlled stream restart for schema evolution rather than losing newly arriving attributes
- Use failOnNewColumns indefinitely while demanding automatic schema evolution; monitor results only after launch of `maintenance_events`.
Explanation
Correct Answer
C:Accept a controlled stream restart for schema evolution rather than losing newly arriving attributes. This explicitly bounds the compromise while still addressing Auto Loader records the new field and may stop the stream so its schema change can be applied on restart. Acceptance relies on inject a JSON field not present in the first batch and confirm the restarted stream retains it without losing records rather than simply declaring the change complete.
Incorrect Answers
A:Delete both checkpoint and schema location and replay everything; monitor results only after launch of `maintenance_events`. Erasing progress and inference history risks replay and inconsistent schema discovery. Monitoring after deployment does not make this approach a safe bounded tradeoff for `maintenance_events`.
B:Continue to parse only the previous schema and silently discard the new field; monitor results only after launch of `maintenance_events`. Ignoring the new field violates the requirement to retain the source attribute. Monitoring after deployment does not make this approach a safe bounded tradeoff for `maintenance_events`.
D:Use failOnNewColumns indefinitely while demanding automatic schema evolution; monitor results only after launch of `maintenance_events`. Failing on every unknown field does not deliver the requested automatic adaptation. Monitoring after deployment does not make this approach a safe bounded tradeoff for `maintenance_events`.
Question 90
At a digital publishing service, a streaming JSON feed added a new optional field and Auto Loader stopped at a schema evolution exception. The dataset `maintenance_events` is in scope, and bronze tables must remain governed by Unity Catalog. What verification provides evidence of correct behavior under the original adverse condition?
- Check that the intended configuration exists for `maintenance_events` and that a healthy run succeeded, without inducing the earlier failure.
- Run only a normal, unchanged `maintenance_events` sample and review new file schema.
- Review a platform-level availability report for `maintenance_events` without testing whether Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
- Inject a JSON field not present in the first batch and confirm the restarted stream retains it without losing records
Explanation
Correct Answer
D:Inject a JSON field not present in the first batch and confirm the restarted stream retains it without losing records. This reproduces the scenario-dependent condition, verifies new file schema, _schemas changes, stream exception, and post-restart bronze column count, and directly tests whether Auto Loader records the new field and may stop the stream so its schema change can be applied on restart is resolved. Observed results, not intent, determine approval.
Incorrect Answers
A:Check that the intended configuration exists for `maintenance_events` 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 Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
B:Run only a normal, unchanged `maintenance_events` sample and review new file schema. A normal sample does not challenge Auto Loader records the new field and may stop the stream so its schema change can be applied on restart.
C:Review a platform-level availability report for `maintenance_events` without testing whether Auto Loader records the new field and may stop the stream so its schema change can be applied on restart. Aggregate availability does not establish the behavior of affected `maintenance_events` records. It cannot replace the direct exercise: inject a JSON field not present in the first batch and confirm the restarted stream retains it without losing records.