Certified Data Engineer Associate Practice Test 8
Question 70
At a parcel sorting company, a governed bronze table receives new partitioned JSON drops, but the SQL ingestion job points at the wrong format and path. The dataset `courier_events` is in scope, and an interrupted delivery must be recoverable without data loss. What verification provides evidence of correct behavior under the original adverse condition?
- Check that the intended configuration exists for `courier_events` and that a healthy run succeeded, without inducing the earlier failure.
- Load a new sample JSON file, compare parsed columns and row counts, then retry safely without duplicate rows
- Review a platform-level availability report for `courier_events` without testing whether the source schema and storage location must match the command before loading can succeed.
- Run only a normal, unchanged `courier_events` sample and review object path listing.
Explanation
Correct Answer
B: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 `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 the source schema and storage location must match the command before loading can succeed.
C:Review a platform-level availability report for `courier_events` 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 `courier_events` 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.
D:Run only a normal, unchanged `courier_events` 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.
Question 71
At a hospital analytics group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `soil_measurements` is in scope, and the target must retain every valid late-arriving event. Which investigation best tests the stated constraint with direct data?
- Correlate source file identifiers, COPY INTO run results across affected and unaffected `R034-CASE-015` runs.
- Compare the saved configuration with the signed change ticket for `soil_measurements` while omitting source and output records.
- Review aggregate cluster availability and job completion for `soil_measurements` without correlating the observed error path.
- Examine only a normal successful run of `soil_measurements` and trend source file identifiers without recreating the fault.
Explanation
Correct Answer
A:Correlate source file identifiers, COPY INTO run results across affected and unaffected `R034-CASE-015` 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 saved configuration with the signed change ticket for `soil_measurements` while omitting source and output records. 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 aggregate cluster availability and job completion for `soil_measurements` without correlating the observed error path. 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 `soil_measurements` records or their causal path.
D:Examine only a normal successful run of `soil_measurements` and trend source file identifiers without recreating the fault. 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 72
At a hospital analytics group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `soil_measurements` is in scope, and the target must retain every valid late-arriving event. Which option targets the actual technical dependency rather than an incidental symptom?
- Run COPY INTO with forced reprocessing for every routine retry; use that approach for `soil_measurements`.
- Truncate the destination table before every COPY INTO attempt; use that approach for `soil_measurements`.
- Append the entire S3 folder with INSERT INTO after every retry; use that approach for `soil_measurements`.
- Use COPY INTO on a Delta target with its default processed-file tracking and do not force reprocessing
Explanation
Correct Answer
D: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:Run COPY INTO with forced reprocessing for every routine retry; use that approach for `soil_measurements`. Forcing reprocessing defeats default file-based deduplication. For `soil_measurements`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
B:Truncate the destination table before every COPY INTO attempt; use that approach for `soil_measurements`. Truncation destroys previously committed history and is not a safe retry policy. For `soil_measurements`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
C:Append the entire S3 folder with INSERT INTO after every retry; use that approach for `soil_measurements`. Repeated append re-ingests historical files because it has no COPY INTO file history. For `soil_measurements`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
Question 73
At a hospital analytics group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `soil_measurements` is in scope, and the target must retain every valid late-arriving event. A teammate proposes: "Truncate the destination table before every COPY INTO attempt." Which technical limitation is the decisive reason not to approve that specific proposal?
- Forcing reprocessing defeats default file-based deduplication. This would apply to another option for `soil_measurements`.
- Insufficient executor memory is the primary fault; source-file handling and ingestion checkpoints are correct.
- Repeated append re-ingests historical files because it has no COPY INTO file history. This would apply to an alternative change for `soil_measurements`.
- Truncation destroys previously committed history and is not a safe retry policy. The proposal does not repair `soil_measurements`.
Explanation
Correct Answer
D:Truncation destroys previously committed history and is not a safe retry policy. The proposal does not repair `soil_measurements`. This identifies the known limitation of the exact proposed action. Truncation destroys previously committed history and is not a safe retry policy. 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:Forcing reprocessing defeats default file-based deduplication. This would apply to another option for `soil_measurements`. This limitation belongs to the different alternative "Run COPY INTO with forced reprocessing for every routine retry". The stem calls for the specific failure of "Truncate the destination table before every COPY INTO attempt".
B: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.
C:Repeated append re-ingests historical files because it has no COPY INTO file history. This would apply to an alternative change for `soil_measurements`. This failure applies to the separate proposal "Append the entire S3 folder with INSERT INTO after every retry".
Question 74
At a hospital analytics group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `soil_measurements` is in scope, and the target must retain every valid late-arriving event. What operational boundary should be documented for approval?
- Truncate the destination table before every COPY INTO attempt; monitor results only after launch of `soil_measurements`.
- Run COPY INTO with forced reprocessing for every routine retry; monitor results only after launch of `soil_measurements`.
- Preserve file-level idempotency even if the scheduler attempts the command more than once
- Append the entire S3 folder with INSERT INTO after every retry; monitor results only after launch of `soil_measurements`.
Explanation
Correct Answer
C: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:Truncate the destination table before every COPY INTO attempt; monitor results only after launch of `soil_measurements`. 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 `soil_measurements`.
B:Run COPY INTO with forced reprocessing for every routine retry; monitor results only after launch of `soil_measurements`. Forcing reprocessing defeats default file-based deduplication. Monitoring after deployment does not make this approach a safe bounded tradeoff for `soil_measurements`.
D:Append the entire S3 folder with INSERT INTO after every retry; monitor results only after launch of `soil_measurements`. 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 `soil_measurements`.
Question 75
At a hospital analytics group, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `soil_measurements` is in scope, and the target must retain every valid late-arriving event. Which validation should be required in the release record?
- Run only a normal, unchanged `soil_measurements` sample and review source file identifiers.
- Review a platform-level availability report for `soil_measurements` without testing whether a naive INSERT from all source files has no source-file idempotency tracking on reruns.
- Check that the intended configuration exists for `soil_measurements` and that a healthy run succeeded, without inducing the earlier failure.
- Run the identical COPY INTO twice against unchanged files and confirm only the first adds their records
Explanation
Correct Answer
D: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:Run only a normal, unchanged `soil_measurements` 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.
B:Review a platform-level availability report for `soil_measurements` 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 `soil_measurements` records. It cannot replace the direct exercise: run the identical COPY INTO twice against unchanged files and confirm only the first adds their records.
C:Check that the intended configuration exists for `soil_measurements` 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.
Question 76
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 choosing a remedy, which investigation actually distinguishes the failure mechanism from a green dashboard?
- 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
- Correlate object path listing, file type across affected and unaffected `R034-CASE-016` runs.
- Review aggregate cluster availability and job completion for `patient_visits` without correlating the observed error path.
Explanation
Correct Answer
C:Correlate object path listing, file type across affected and unaffected `R034-CASE-016` runs.. By joining object path listing, file type, COPY INTO query error, and authorized external location across the affected and unaffected conditions, the investigation directly tests the recorded mechanism: the source schema and storage location must match the command before loading can succeed. It also sets a measurable baseline for load a new sample JSON file, compare parsed columns and row counts, then retry safely without duplicate rows.
Incorrect Answers
A: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 object path listing, file type, COPY INTO query error, and authorized external location, the direct means of distinguishing the source schema and storage location must match the command before loading can succeed.
B: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 the source schema and storage location must match the command before loading can succeed. The failing-path object path listing, file type, COPY INTO query error, and authorized external location has to be correlated before attributing cause.
D:Review aggregate cluster availability and job completion for `patient_visits` without correlating the observed error path. A successful aggregate platform state can coexist with the source schema and storage location must match the command before loading can succeed. This check never observes the affected `patient_visits` records or their causal path.
Question 77
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. Which implementation best resolves the stated defect without breaking the business constraint?
- Turn on FORCE and leave the invalid file source unchanged; use that approach for `patient_visits`.
- Change the job to read all files as CSV regardless of extension; use that approach for `patient_visits`.
- Alter the target table owner to a broad admin group without inspecting the source path; use that approach for `patient_visits`.
- Correct COPY INTO source path and FILEFORMAT for the incoming objects and verify Unity Catalog storage access
Explanation
Correct Answer
D:Correct COPY INTO source path and FILEFORMAT for the incoming objects and verify Unity Catalog storage access. This directly addresses the source schema and storage location must match the command before loading can succeed. The design should be accepted only after load a new sample JSON file, compare parsed columns and row counts, then retry safely without duplicate rows; the related operational boundary is to resolve the precise source and credential configuration rather than widening permissions blindly.
Incorrect Answers
A:Turn on FORCE and leave the invalid file source unchanged; use that approach for `patient_visits`. Reprocessing previously discovered files cannot fix a path or format mismatch. For `patient_visits`, this cannot remove the causal limitation: the source schema and storage location must match the command before loading can succeed.
B:Change the job to read all files as CSV regardless of extension; use that approach for `patient_visits`. A CSV parser cannot reliably decode the stated JSON payloads. For `patient_visits`, this cannot remove the causal limitation: the source schema and storage location must match the command before loading can succeed.
C:Alter the target table owner to a broad admin group without inspecting the source path; use that approach for `patient_visits`. Changing ownership does not correct a nonexistent input path or wrong parser. For `patient_visits`, this cannot remove the causal limitation: the source schema and storage location must match the command before loading can succeed.
Question 78
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. A teammate proposes: "Change the job to read all files as CSV regardless of extension." Which technical limitation is the decisive reason not to approve that specific proposal?
- Reprocessing previously discovered files cannot fix a path or format mismatch. This would apply to an alternative change for `patient_visits`.
- Insufficient executor memory is the primary fault; source-file handling and ingestion checkpoints are correct.
- Changing ownership does not correct a nonexistent input path or wrong parser. This would apply to another option for `patient_visits`.
- A CSV parser cannot reliably decode the stated JSON payloads. The proposal does not repair `patient_visits`.
Explanation
Correct Answer
D:A CSV parser cannot reliably decode the stated JSON payloads. The proposal does not repair `patient_visits`. This identifies the known limitation of the exact proposed action. A CSV parser cannot reliably decode the stated JSON payloads. The underlying problem remains the source schema and storage location must match the command before loading can succeed; Correct COPY INTO source path and FILEFORMAT for the incoming objects and verify Unity Catalog storage access instead targets that failure.
Incorrect Answers
A:Reprocessing previously discovered files cannot fix a path or format mismatch. This would apply to an alternative change for `patient_visits`. This failure applies to the separate proposal "Turn on FORCE and leave the invalid file source unchanged".
B: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 the source schema and storage location must match the command before loading can succeed.
C:Changing ownership does not correct a nonexistent input path or wrong parser. This would apply to another option for `patient_visits`. This limitation belongs to the different alternative "Alter the target table owner to a broad admin group without inspecting the source path". The stem calls for the specific failure of "Change the job to read all files as CSV regardless of extension".
Question 79
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. Which bounded tradeoff would preserve the core outcome and control operational risk?
- Alter the target table owner to a broad admin group without inspecting the source path
- Turn on FORCE and leave the invalid file source unchanged
- Resolve the precise source and credential configuration rather than widening permissions blindly
- Change the job to read all files as CSV regardless of extension; monitor results only after launch of `patient_visits`.
Explanation
Correct Answer
C:Resolve the precise source and credential configuration rather than widening permissions blindly. This explicitly bounds the compromise while still addressing the source schema and storage location must match the command before loading can succeed. Acceptance relies on load a new sample JSON file, compare parsed columns and row counts, then retry safely without duplicate rows rather than simply declaring the change complete.
Incorrect Answers
A:Alter the target table owner to a broad admin group without inspecting the source path. Changing ownership does not correct a nonexistent input path or wrong parser. Monitoring after deployment does not make this approach a safe bounded tradeoff for `patient_visits`.
B:Turn on FORCE and leave the invalid file source unchanged. Reprocessing previously discovered files cannot fix a path or format mismatch. Monitoring after deployment does not make this approach a safe bounded tradeoff for `patient_visits`.
D:Change the job to read all files as CSV regardless of extension; monitor results only after launch of `patient_visits`. A CSV parser cannot reliably decode the stated JSON payloads. Monitoring after deployment does not make this approach a safe bounded tradeoff for `patient_visits`.
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.