Microsoft DP-700 Practice Test 15
Question 140
A transactional database emits committed insert, update, and delete events. Which ingestion pattern retains those changes efficiently?
- Resume extraction from the last successful committed watermark rather than the wall-clock restart time.
- Apply changes using a reliable sequence or version column, rejecting stale mutations.
- Persist the high watermark only after the target batch successfully commits.
- Consume supported change data capture events and apply them with ordered upsert and delete semantics.
Explanation
Correct Answer
D:CDC can represent all three mutation types; a max-ID-only extractor misses updates and deletes. Reconcile test insert, update, and delete sequences.
Incorrect Answers
A:The persisted successful boundary anchors recovery and captures records created during downtime.
B:Arrival order may differ from source commit order; version-aware application avoids overwriting newer state with older events.
C:Advancing state before a failed write can permanently skip records on the next run.
Question 141
An ETL job stores the last processed modification time but advances it before the target commit. What should be changed?
- Consume supported change data capture events and apply them with ordered upsert and delete semantics.
- Deduplicate by source transaction identifier or version and use idempotent apply logic.
- Persist the high watermark only after the target batch successfully commits.
- Use an inclusive boundary with stable tie-breaking and deduplicated target merges.
Explanation
Correct Answer
C:Advancing state before a failed write can permanently skip records on the next run. Force a failed target write and inspect watermark behavior.
Incorrect Answers
A:CDC can represent all three mutation types; a max-ID-only extractor misses updates and deletes.
B:Replayed change events should not cause repeated inserts or counters when they represent one source mutation.
D:Strict timestamp greater-than logic can miss equal-timestamp events; a deterministic tie-breaker or overlap preserves them.
Question 142
Two updates share the same source timestamp at a batch boundary. How should the watermark algorithm avoid losing one?
- Use an inclusive boundary with stable tie-breaking and deduplicated target merges.
- Store incremental checkpoint or watermark state in reliable persistent storage outside the ephemeral session.
- Persist the high watermark only after the target batch successfully commits.
- Apply changes using a reliable sequence or version column, rejecting stale mutations.
Explanation
Correct Answer
A:Strict timestamp greater-than logic can miss equal-timestamp events; a deterministic tie-breaker or overlap preserves them. Load two boundary-equal changes and verify both survive.
Incorrect Answers
B:Session variables disappear after restarts and cannot prove the last committed source position.
C:Advancing state before a failed write can permanently skip records on the next run.
D:Arrival order may differ from source commit order; version-aware application avoids overwriting newer state with older events.
Question 143
A source produces CDC events out of order for the same key. Which safeguard protects current state?
- Use an inclusive boundary with stable tie-breaking and deduplicated target merges.
- Apply changes using a reliable sequence or version column, rejecting stale mutations.
- Resume extraction from the last successful committed watermark rather than the wall-clock restart time.
- Persist the high watermark only after the target batch successfully commits.
Explanation
Correct Answer
B:Arrival order may differ from source commit order; version-aware application avoids overwriting newer state with older events. Replay reversed event arrival for one key.
Incorrect Answers
A:Strict timestamp greater-than logic can miss equal-timestamp events; a deterministic tie-breaker or overlap preserves them.
C:The persisted successful boundary anchors recovery and captures records created during downtime.
D:Advancing state before a failed write can permanently skip records on the next run.
Question 144
A pipeline missed three days while offline and has a persisted watermark. Which restart strategy prevents losing the gap?
- Consume supported change data capture events and apply them with ordered upsert and delete semantics.
- Apply changes using a reliable sequence or version column, rejecting stale mutations.
- Resume extraction from the last successful committed watermark rather than the wall-clock restart time.
- Use an inclusive boundary with stable tie-breaking and deduplicated target merges.
Explanation
Correct Answer
C:The persisted successful boundary anchors recovery and captures records created during downtime. Compare gap event counts with source history.
Incorrect Answers
A:CDC can represent all three mutation types; a max-ID-only extractor misses updates and deletes.
B:Arrival order may differ from source commit order; version-aware application avoids overwriting newer state with older events.
D:Strict timestamp greater-than logic can miss equal-timestamp events; a deterministic tie-breaker or overlap preserves them.
Question 145
A pipeline tracks ingestion progress using local notebook variables. What should be durable for recovery?
- Use an inclusive boundary with stable tie-breaking and deduplicated target merges.
- Store incremental checkpoint or watermark state in reliable persistent storage outside the ephemeral session.
- Persist the high watermark only after the target batch successfully commits.
- Consume supported change data capture events and apply them with ordered upsert and delete semantics.
Explanation
Correct Answer
B:Session variables disappear after restarts and cannot prove the last committed source position. Restart the job and confirm it resumes from persisted state.
Incorrect Answers
A:Strict timestamp greater-than logic can miss equal-timestamp events; a deterministic tie-breaker or overlap preserves them.
C:Advancing state before a failed write can permanently skip records on the next run.
D:CDC can represent all three mutation types; a max-ID-only extractor misses updates and deletes.
Question 146
An upstream service returns the same CDC transaction twice after retry. Which target process should absorb it safely?
- Use an inclusive boundary with stable tie-breaking and deduplicated target merges.
- Persist the high watermark only after the target batch successfully commits.
- Deduplicate by source transaction identifier or version and use idempotent apply logic.
- Resume extraction from the last successful committed watermark rather than the wall-clock restart time.
Explanation
Correct Answer
C:Replayed change events should not cause repeated inserts or counters when they represent one source mutation. Send a duplicate transaction and verify identical target state.
Incorrect Answers
A:Strict timestamp greater-than logic can miss equal-timestamp events; a deterministic tie-breaker or overlap preserves them.
B:Advancing state before a failed write can permanently skip records on the next run.
D:The persisted successful boundary anchors recovery and captures records created during downtime.
Question 147
A sales fact table contains several measures and repeated customer names. What data preparation improves a dimensional star design?
- Declare the intended fact grain and aggregate line measures to the correct order key.
- Create stable warehouse surrogate keys with source-system-aware business-key mapping.
- Use a controlled unknown or inferred-member dimension key and reconcile it later.
- Separate conformed customer attributes into a customer dimension and retain fact foreign keys.
Explanation
Correct Answer
D:Dimension separation reduces repeated descriptive attributes in high-volume facts and supports consistent slicing. Verify fact-to-dimension joins on customer keys.
Incorrect Answers
A:Fact grain determines which measures can be summed without duplication; an implicit join may multiply amounts.
B:A surrogate mapping prevents accidental joins across separate source identities that share numeric IDs.
C:An unknown member prevents orphaned foreign keys while preserving the pending fact for subsequent conformance.
Question 148
A customer changes postal region, but reporting must show the region as of each historical sale. Which history pattern is required?
- Maintain Type 2 dimension versions with effective dates and durable surrogate keys.
- Create stable warehouse surrogate keys with source-system-aware business-key mapping.
- Create one conformed date dimension containing agreed calendar and fiscal attributes.
- Declare the intended fact grain and aggregate line measures to the correct order key.
Explanation
Correct Answer
A:Type 2 history associates old facts with the dimension attributes valid at transaction time. Test a customer move with sales on both sides of the change.
Incorrect Answers
B:A surrogate mapping prevents accidental joins across separate source identities that share numeric IDs.
C:Shared date semantics keep financial reporting consistent across facts; per-report date parsing risks mismatched periods.
D:Fact grain determines which measures can be summed without duplication; an implicit join may multiply amounts.
Question 149
A product description typo is corrected and historical reporting should use the new spelling everywhere. Which dimensional update fits?
- Declare the intended fact grain and aggregate line measures to the correct order key.
- Create one conformed date dimension containing agreed calendar and fiscal attributes.
- Apply a Type 1 overwrite to the descriptive dimension attribute.
- Create stable warehouse surrogate keys with source-system-aware business-key mapping.
Explanation
Correct Answer
C:Type 1 correction updates current descriptive values without retaining alternative historical spelling versions. Confirm historic sales display the corrected text.
Incorrect Answers
A:Fact grain determines which measures can be summed without duplication; an implicit join may multiply amounts.
B:Shared date semantics keep financial reporting consistent across facts; per-report date parsing risks mismatched periods.
D:A surrogate mapping prevents accidental joins across separate source identities that share numeric IDs.
Question 150
Transactions arrive before their customer dimension record exists. What technique preserves fact ingestion without inventing a valid customer?
- Create one conformed date dimension containing agreed calendar and fiscal attributes.
- Apply a Type 1 overwrite to the descriptive dimension attribute.
- Use a controlled unknown or inferred-member dimension key and reconcile it later.
- Maintain Type 2 dimension versions with effective dates and durable surrogate keys.
Explanation
Correct Answer
C:An unknown member prevents orphaned foreign keys while preserving the pending fact for subsequent conformance. Load a late customer and check the related facts are updated.
Incorrect Answers
A:Shared date semantics keep financial reporting consistent across facts; per-report date parsing risks mismatched periods.
B:Type 1 correction updates current descriptive values without retaining alternative historical spelling versions.
D:Type 2 history associates old facts with the dimension attributes valid at transaction time.
