Microsoft DP-700 Practice Test 23
Question 220
A product category table has multiple effective-dated versions. Joining it to facts without date criteria doubles revenue. What should be added?
- Preaggregate events by site and day using a suitable grouped query.
- Resolve promotion allocation or bridge grain before aggregating additive fact measures.
- Keep transaction-level raw or curated detail alongside the aggregated serving table.
- Join the dimension on both business key and the applicable effective-date range.
Explanation
Correct Answer
D:Temporal joins identify the correct historical version and prevent multiplying facts across dimension versions. Check historical transactions around a version boundary.
Incorrect Answers
A:Aggregates reduce scanned rows for common reporting grains while retaining raw data elsewhere for detail.
B:Many-to-many joins multiply amounts unless the relationship and allocation are handled explicitly.
C:A summary supports fast trends but cannot replace detailed records needed to explain a total or investigate anomalies.
Question 221
A query groups amounts by month but source timestamps are in mixed local timezones. What should be standardized first?
- Normalize event timestamps to an agreed timezone before computing monthly buckets.
- Preaggregate events by site and day using a suitable grouped query.
- Materialize a controlled denormalized serving table at a declared order grain.
- Keep transaction-level raw or curated detail alongside the aggregated serving table.
Explanation
Correct Answer
A:Month boundaries can differ by timezone; consistent conversion prevents cross-region reporting discrepancies. Test an event near local midnight.
Incorrect Answers
B:Aggregates reduce scanned rows for common reporting grains while retaining raw data elsewhere for detail.
C:Denormalization can reduce repetitive read-time joins when duplicate dimension attributes are managed deliberately.
D:A summary supports fast trends but cannot replace detailed records needed to explain a total or investigate anomalies.
Question 222
A team creates a sales summary and loses the ability to audit individual refunds. What should the architecture preserve?
- Join the dimension on both business key and the applicable effective-date range.
- Keep transaction-level raw or curated detail alongside the aggregated serving table.
- Resolve promotion allocation or bridge grain before aggregating additive fact measures.
- Standardize category values using a maintained mapping before GROUP BY.
Explanation
Correct Answer
B:A summary supports fast trends but cannot replace detailed records needed to explain a total or investigate anomalies. Drill from one aggregate to its contributing transactions.
Incorrect Answers
A:Temporal joins identify the correct historical version and prevent multiplying facts across dimension versions.
C:Many-to-many joins multiply amounts unless the relationship and allocation are handled explicitly.
D:Canonical categories prevent semantic duplicates that would otherwise split business totals.
Question 223
An order fact is joined to a multivalued promotion list, inflating summed revenue. What model correction is needed?
- Preaggregate events by site and day using a suitable grouped query.
- Resolve promotion allocation or bridge grain before aggregating additive fact measures.
- Normalize event timestamps to an agreed timezone before computing monthly buckets.
- Join the dimension on both business key and the applicable effective-date range.
Explanation
Correct Answer
B:Many-to-many joins multiply amounts unless the relationship and allocation are handled explicitly. Verify total revenue remains unchanged when promotions are present.
Incorrect Answers
A:Aggregates reduce scanned rows for common reporting grains while retaining raw data elsewhere for detail.
C:Month boundaries can differ by timezone; consistent conversion prevents cross-region reporting discrepancies.
D:Temporal joins identify the correct historical version and prevent multiplying facts across dimension versions.
Question 224
A repeated free-text category spelling creates separate report groups. Which preparation step improves aggregation?
- Use COUNT DISTINCT on the customer key at the intended reporting grain.
- Materialize a controlled denormalized serving table at a declared order grain.
- Standardize category values using a maintained mapping before GROUP BY.
- Keep transaction-level raw or curated detail alongside the aggregated serving table.
Explanation
Correct Answer
C:Canonical categories prevent semantic duplicates that would otherwise split business totals. Compare distinct categories before and after mapping.
Incorrect Answers
A:Counting transaction rows overstates the population when a customer has multiple events.
B:Denormalization can reduce repetitive read-time joins when duplicate dimension attributes are managed deliberately.
D:A summary supports fast trends but cannot replace detailed records needed to explain a total or investigate anomalies.
Question 225
An input batch contains duplicate invoice IDs with different update times. Which record should be retained for the current-state table?
- Add a referential integrity quality check and explicit remediation queue for unmatched keys.
- Select the latest valid version per invoice using deterministic ordering before target upsert.
- Distinguish missing measures from genuine zero values and impute only under an approved rule.
- Reprocess affected prior periods under an explicit late-arriving-data correction policy.
Explanation
Correct Answer
B:Key plus version disambiguates duplicate business records; arbitrary first-row selection can preserve stale data. Test duplicate keys with reversed arrival order.
Incorrect Answers
A:An orphan detector separates data-quality failures from valid facts and enables controlled repair.
C:Null-to-zero replacement can conceal missing data and distort averages or quality indicators.
D:Late business events may require restating prior aggregates rather than being counted only in the day of arrival.
Question 226
An order feed contains blank required product identifiers. What is the safest quality handling before the curated fact write?
- Route invalid records to a quarantine or reject set and load only valid facts.
- Use event-time processing and an appropriate lateness allowance.
- Deduplicate by stable business key and ordered version, not only full-row identity.
- Reprocess affected prior periods under an explicit late-arriving-data correction policy.
Explanation
Correct Answer
A:Quarantine retains evidence for repair rather than silently inventing product keys or corrupting curated joins. Count rejected rows and verify the error reason.
Incorrect Answers
B:Event-time attribution follows business occurrence rather than ingestion delay and supports late-event windows.
C:Full-row DISTINCT does not enforce one current record per transaction when nonkey fields differ.
D:Late business events may require restating prior aggregates rather than being counted only in the day of arrival.
Question 227
A batch of invoices is late because the currency-rate reference has not arrived, and the finance SLA prohibits fabricated conversion rates. What should happen?
- Reprocess affected prior periods under an explicit late-arriving-data correction policy.
- Use event-time processing and an appropriate lateness allowance.
- Hold or quarantine the affected currency conversions and replay them when the authoritative rate arrives.
- Select the latest valid version per invoice using deterministic ordering before target upsert.
Explanation
Correct Answer
C:Waiting for valid reference values protects the financial measure; filling a rate with zero or guessing a rate creates incorrect facts. Reconcile the replayed invoices to the published rate table.
Incorrect Answers
A:Late business events may require restating prior aggregates rather than being counted only in the day of arrival.
B:Event-time attribution follows business occurrence rather than ingestion delay and supports late-event windows.
D:Key plus version disambiguates duplicate business records; arbitrary first-row selection can preserve stale data.
Question 228
A stream's event timestamps are ten minutes behind processing time. Which approach avoids classifying them solely by arrival hour?
- Use event-time processing and an appropriate lateness allowance.
- Route invalid records to a quarantine or reject set and load only valid facts.
- Distinguish missing measures from genuine zero values and impute only under an approved rule.
- Add a referential integrity quality check and explicit remediation queue for unmatched keys.
Explanation
Correct Answer
A:Event-time attribution follows business occurrence rather than ingestion delay and supports late-event windows. Inject delayed events around an hour boundary.
Incorrect Answers
B:Quarantine retains evidence for repair rather than silently inventing product keys or corrupting curated joins.
C:Null-to-zero replacement can conceal missing data and distort averages or quality indicators.
D:An orphan detector separates data-quality failures from valid facts and enables controlled repair.
Question 229
A downstream report shows unexpected zero sales after a nullable numeric field was filled with zero. What should be reconsidered?
- Select the latest valid version per invoice using deterministic ordering before target upsert.
- Use event-time processing and an appropriate lateness allowance.
- Deduplicate by stable business key and ordered version, not only full-row identity.
- Distinguish missing measures from genuine zero values and impute only under an approved rule.
Explanation
Correct Answer
D:Null-to-zero replacement can conceal missing data and distort averages or quality indicators. Compare missingness counts before and after cleansing.
Incorrect Answers
A:Key plus version disambiguates duplicate business records; arbitrary first-row selection can preserve stale data.
B:Event-time attribution follows business occurrence rather than ingestion delay and supports late-event windows.
C:Full-row DISTINCT does not enforce one current record per transaction when nonkey fields differ.
Question 230
A de-duplication job removes repeated whole rows but leaves records with the same transaction ID and different metadata. What should change?
- Route invalid records to a quarantine or reject set and load only valid facts.
- Add a referential integrity quality check and explicit remediation queue for unmatched keys.
- Deduplicate by stable business key and ordered version, not only full-row identity.
- Distinguish missing measures from genuine zero values and impute only under an approved rule.
Explanation
Correct Answer
C:Full-row DISTINCT does not enforce one current record per transaction when nonkey fields differ. Check a conflicting pair with equal IDs.
Incorrect Answers
A:Quarantine retains evidence for repair rather than silently inventing product keys or corrupting curated joins.
B:An orphan detector separates data-quality failures from valid facts and enables controlled repair.
D:Null-to-zero replacement can conceal missing data and distort averages or quality indicators.
