Certified Data Engineer Associate Practice Test 7
Question 60
At a regional banking group, a point-of-sale application continuously emits order events that downstream fraud monitors must see within minutes. The dataset `population_records` 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?
- Force a restart during ingestion and confirm bounded end-to-end latency with no unaccounted source events
- Review a platform-level availability report for `population_records` without testing whether an end-of-day whole-directory batch cannot satisfy the event-latency bound or robust replay handling.
- Check that the intended configuration exists for `population_records` and that a healthy run succeeded, without inducing the earlier failure.
- Run only a normal, unchanged `population_records` sample and review event arrival timestamps.
Explanation
Correct Answer
A:Force a restart during ingestion and confirm bounded end-to-end latency with no unaccounted source events. This reproduces the scenario-dependent condition, verifies event arrival timestamps, source offsets/files, checkpoint state, and bronze freshness lag, and directly tests whether an end-of-day whole-directory batch cannot satisfy the event-latency bound or robust replay handling is resolved. Observed results, not intent, determine approval.
Incorrect Answers
B:Review a platform-level availability report for `population_records` without testing whether an end-of-day whole-directory batch cannot satisfy the event-latency bound or robust replay handling. Aggregate availability does not establish the behavior of affected `population_records` records. It cannot replace the direct exercise: force a restart during ingestion and confirm bounded end-to-end latency with no unaccounted source events.
C:Check that the intended configuration exists for `population_records` 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 an end-of-day whole-directory batch cannot satisfy the event-latency bound or robust replay handling.
D:Run only a normal, unchanged `population_records` sample and review event arrival timestamps. A normal sample does not challenge an end-of-day whole-directory batch cannot satisfy the event-latency bound or robust replay handling.
Question 61
At a freight port operator, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `patient_visits` 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-013` runs.
- Inspect normal-period success metrics for the stated dataset without reproducing the source error
- Compare the current configuration and the signed change approval, without source-row evidence
- Review aggregate cluster availability and job completion for `patient_visits` without correlating the observed error path.
Explanation
Correct Answer
A:Correlate source file identifiers, COPY INTO run results across affected and unaffected `R034-CASE-013` 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: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.
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 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.
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 a naive INSERT from all source files has no source-file idempotency tracking on reruns. This check never observes the affected `patient_visits` records or their causal path.
Question 62
At a freight port operator, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `patient_visits` is in scope, and the target must retain every valid late-arriving event. Which platform configuration or data-engineering change is most defensible?
- Run COPY INTO with forced reprocessing for every routine retry; use that approach for `patient_visits`.
- Append the entire S3 folder with INSERT INTO after every retry; use that approach for `patient_visits`.
- Use COPY INTO on a Delta target with its default processed-file tracking and do not force reprocessing
- Truncate the destination table before every COPY INTO attempt; use that approach for `patient_visits`.
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:Run COPY INTO with forced reprocessing for every routine retry; use that approach for `patient_visits`. Forcing reprocessing defeats default file-based deduplication. For `patient_visits`, 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 `patient_visits`. Repeated append re-ingests historical files because it has no COPY INTO file history. For `patient_visits`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
D:Truncate the destination table before every COPY INTO attempt; use that approach for `patient_visits`. Truncation destroys previously committed history and is not a safe retry policy. For `patient_visits`, this cannot remove the causal limitation: a naive INSERT from all source files has no source-file idempotency tracking on reruns.
Question 63
At a freight port operator, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `patient_visits` is in scope, and the target must retain every valid late-arriving event. A teammate proposes: "Run COPY INTO with forced reprocessing for every routine retry." Which technical limitation is the decisive reason not to approve that specific proposal?
- Forcing reprocessing defeats default file-based deduplication. The proposal does not repair `patient_visits`.
- Repeated append re-ingests historical files because it has no COPY INTO file history. This would apply to another option for `patient_visits`.
- Truncation destroys previously committed history and is not a safe retry policy. 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.
Explanation
Correct Answer
A:Forcing reprocessing defeats default file-based deduplication. The proposal does not repair `patient_visits`. This identifies the known limitation of the exact proposed action. Forcing reprocessing defeats default file-based deduplication. 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
B:Repeated append re-ingests historical files because it has no COPY INTO file history. This would apply to another option for `patient_visits`. This limitation belongs to the different alternative "Append the entire S3 folder with INSERT INTO after every retry". The stem calls for the specific failure of "Run COPY INTO with forced reprocessing for every routine retry".
C:Truncation destroys previously committed history and is not a safe retry policy. This would apply to an alternative change for `patient_visits`. This failure applies to the separate proposal "Truncate the destination table before every COPY INTO attempt".
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 64
At a freight port operator, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `patient_visits` 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
- Preserve file-level idempotency even if the scheduler attempts the command more than once
- Truncate the destination table before every COPY INTO attempt
- Run COPY INTO with forced reprocessing for every routine retry; monitor results only after launch of `patient_visits`.
Explanation
Correct Answer
B: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 `patient_visits`.
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 `patient_visits`.
D:Run COPY INTO with forced reprocessing for every routine retry; monitor results only after launch of `patient_visits`. Forcing reprocessing defeats default file-based deduplication. Monitoring after deployment does not make this approach a safe bounded tradeoff for `patient_visits`.
Question 65
At a freight port operator, a scheduled S3 import reruns after a scheduler timeout and duplicates sales rows from previously processed files. The dataset `patient_visits` 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?
- Run only a normal, unchanged `patient_visits` sample and review source file identifiers.
- Check that the intended configuration exists for `patient_visits` and that a healthy run succeeded, without inducing the earlier failure.
- Review a platform-level availability report for `patient_visits` without testing whether a naive INSERT from all source files has no source-file idempotency tracking on reruns.
- 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 `patient_visits` 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: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 a naive INSERT from all source files has no source-file idempotency tracking on reruns.
C:Review a platform-level availability report for `patient_visits` 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 `patient_visits` 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 66
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. Which correlated observation is the strongest basis for the change decision?
- Inspect normal-period success metrics for the stated dataset without reproducing the source error
- Compare the current configuration and the signed change approval, without source-row evidence
- Correlate object path listing, file type across affected and unaffected `R034-CASE-014` runs.
- Review completed run status and aggregate cluster health without record-level correlation
Explanation
Correct Answer
C:Correlate object path listing, file type across affected and unaffected `R034-CASE-014` 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: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.
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 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.
D:Review completed run status and aggregate cluster health without record-level correlation. 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 `courier_events` records or their causal path.
Question 67
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 implementation should the engineer select for the failing workflow?
- Change the job to read all files as CSV regardless of extension; use that approach for `courier_events`.
- Correct COPY INTO source path and FILEFORMAT for the incoming objects and verify Unity Catalog storage access
- Alter the target table owner to a broad admin group without inspecting the source path; use that approach for `courier_events`.
- Turn on FORCE and leave the invalid file source unchanged; use that approach for `courier_events`.
Explanation
Correct Answer
B: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:Change the job to read all files as CSV regardless of extension; use that approach for `courier_events`. A CSV parser cannot reliably decode the stated JSON payloads. For `courier_events`, 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 `courier_events`. Changing ownership does not correct a nonexistent input path or wrong parser. For `courier_events`, this cannot remove the causal limitation: the source schema and storage location must match the command before loading can succeed.
D:Turn on FORCE and leave the invalid file source unchanged; use that approach for `courier_events`. Reprocessing previously discovered files cannot fix a path or format mismatch. For `courier_events`, this cannot remove the causal limitation: the source schema and storage location must match the command before loading can succeed.
Question 68
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. A teammate proposes: "Alter the target table owner to a broad admin group without inspecting the source path." Which technical limitation is the decisive reason not to approve that specific proposal?
- Changing ownership does not correct a nonexistent input path or wrong parser. The proposal does not repair `courier_events`.
- Insufficient executor memory is the primary fault; source-file handling and ingestion checkpoints are correct.
- Reprocessing previously discovered files cannot fix a path or format mismatch. This would apply to another option for `courier_events`.
- A CSV parser cannot reliably decode the stated JSON payloads. This would apply to an alternative change for `courier_events`.
Explanation
Correct Answer
A:Changing ownership does not correct a nonexistent input path or wrong parser. The proposal does not repair `courier_events`. This identifies the known limitation of the exact proposed action. Changing ownership does not correct a nonexistent input path or wrong parser. 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
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:Reprocessing previously discovered files cannot fix a path or format mismatch. This would apply to another option for `courier_events`. This limitation belongs to the different alternative "Turn on FORCE and leave the invalid file source unchanged". The stem calls for the specific failure of "Alter the target table owner to a broad admin group without inspecting the source path".
D:A CSV parser cannot reliably decode the stated JSON payloads. This would apply to an alternative change for `courier_events`. This failure applies to the separate proposal "Change the job to read all files as CSV regardless of extension".
Question 69
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. Which decision preserves the stated data contract while keeping the change scope proportionate?
- Resolve the precise source and credential configuration rather than widening permissions blindly
- Alter the target table owner to a broad admin group without inspecting the source path
- Change the job to read all files as CSV regardless of extension
- Turn on FORCE and leave the invalid file source unchanged
Explanation
Correct Answer
A: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
B: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 `courier_events`.
C:Change the job to read all files as CSV regardless of extension. A CSV parser cannot reliably decode the stated JSON payloads. Monitoring after deployment does not make this approach a safe bounded tradeoff for `courier_events`.
D: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 `courier_events`.
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.