Microsoft DP-700 Practice Test 39
Question 380
A reporting query scans all transaction history despite needing one quarter. Which first rewrite can reduce work?
- Repair dimension uniqueness and join grain before attempting performance-only tuning.
- Maintain a validated serving aggregate at the report's stable monthly grain.
- Apply a selective sargable date predicate before expensive joins and aggregations.
- Normalize the join keys ahead of time and join on compatible native columns.
Explanation
Correct Answer
C:Early selective filtering reduces data scanned when the optimizer can use the predicate effectively. Compare execution duration and rows scanned.
Incorrect Answers
A:A logically incorrect join can multiply work and amounts; faster execution is not a substitute for accurate cardinality.
B:Preaggregated serving data can reduce repeated heavy group-by scans for predictable reporting patterns.
D:Complex per-row expressions can hinder optimization and produce avoidable compute work.
Question 381
A join uses functions on both key columns, limiting efficient matching. What should the designer consider?
- Apply a selective sargable date predicate before expensive joins and aggregations.
- Maintain a validated serving aggregate at the report's stable monthly grain.
- Inspect table and column statistics relevant to the filter and join.
- Normalize the join keys ahead of time and join on compatible native columns.
Explanation
Correct Answer
D:Complex per-row expressions can hinder optimization and produce avoidable compute work. Inspect query plan changes after normalization.
Incorrect Answers
A:Early selective filtering reduces data scanned when the optimizer can use the predicate effectively.
B:Preaggregated serving data can reduce repeated heavy group-by scans for predictable reporting patterns.
C:The optimizer relies on statistics to estimate cardinality and choose plans; stale or unrepresentative data can distort its estimates.
Question 382
A warehouse query chooses a poor plan after a large data distribution change. Which metadata should be investigated?
- Apply a selective sargable date predicate before expensive joins and aggregations.
- Inspect table and column statistics relevant to the filter and join.
- Use Warehouse Query Insights and execution diagnostics to identify the slow query shape and resource pressure.
- Maintain a validated serving aggregate at the report's stable monthly grain.
Explanation
Correct Answer
B:The optimizer relies on statistics to estimate cardinality and choose plans; stale or unrepresentative data can distort its estimates. Compare estimated and actual row counts in diagnostics.
Incorrect Answers
A:Early selective filtering reduces data scanned when the optimizer can use the predicate effectively.
C:Query Insights surfaces historical durations, frequent patterns, and expensive queries for evidence-led tuning.
D:Preaggregated serving data can reduce repeated heavy group-by scans for predictable reporting patterns.
Question 383
A business query recalculates the same expensive monthly summary for thousands of dashboard views. Which design may improve responsiveness?
- Apply a selective sargable date predicate before expensive joins and aggregations.
- Project only the required columns in the SQL query.
- Maintain a validated serving aggregate at the report's stable monthly grain.
- Normalize the join keys ahead of time and join on compatible native columns.
Explanation
Correct Answer
C:Preaggregated serving data can reduce repeated heavy group-by scans for predictable reporting patterns. Benchmark summary results and freshness against raw facts.
Incorrect Answers
A:Early selective filtering reduces data scanned when the optimizer can use the predicate effectively.
B:Column pruning reduces unnecessary data movement and read work when the engine supports it.
D:Complex per-row expressions can hinder optimization and produce avoidable compute work.
Question 384
A warehouse query applies SELECT * across a very wide table but only shows two measures. What should be rewritten?
- Maintain a validated serving aggregate at the report's stable monthly grain.
- Use Warehouse Query Insights and execution diagnostics to identify the slow query shape and resource pressure.
- Project only the required columns in the SQL query.
- Inspect table and column statistics relevant to the filter and join.
Explanation
Correct Answer
C:Column pruning reduces unnecessary data movement and read work when the engine supports it. Compare scanned bytes and resulting two-column output.
Incorrect Answers
A:Preaggregated serving data can reduce repeated heavy group-by scans for predictable reporting patterns.
B:Query Insights surfaces historical durations, frequent patterns, and expensive queries for evidence-led tuning.
D:The optimizer relies on statistics to estimate cardinality and choose plans; stale or unrepresentative data can distort its estimates.
Question 385
A dashboard joins a line-grain fact to a duplicated customer dimension and runs slowly with inflated totals. What should be fixed first?
- Maintain a validated serving aggregate at the report's stable monthly grain.
- Use Warehouse Query Insights and execution diagnostics to identify the slow query shape and resource pressure.
- Repair dimension uniqueness and join grain before attempting performance-only tuning.
- Apply a selective sargable date predicate before expensive joins and aggregations.
Explanation
Correct Answer
C:A logically incorrect join can multiply work and amounts; faster execution is not a substitute for accurate cardinality. Verify one dimension row per declared key.
Incorrect Answers
A:Preaggregated serving data can reduce repeated heavy group-by scans for predictable reporting patterns.
B:Query Insights surfaces historical durations, frequent patterns, and expensive queries for evidence-led tuning.
D:Early selective filtering reduces data scanned when the optimizer can use the predicate effectively.
Question 386
An Eventhouse query scans months of telemetry for an alert about the last five minutes. Which query change has highest leverage?
- Consider a supported materialized view for the repeated native-table aggregation.
- Add a narrow time filter and project the specific alert fields.
- Adjust the supported Eventstream throughput level after measuring source and sink capacity.
- Use KQL dcount() for an approximate cardinality estimate grouped by the required time bucket.
Explanation
Correct Answer
B:Time-series stores benefit when queries restrict the relevant temporal range instead of scanning historical events. Compare scanned extents and latency.
Incorrect Answers
A:Materialized views can precompute recurring results from eligible native data rather than repeating full aggregation work.
C:Throughput configuration can constrain processing, but increasing it helps only when downstream destinations can keep pace.
D:Approximate distinct counting reduces resource use when an estimation error is acceptable, unlike exact counting of all unique identifiers.
Question 387
An Eventstream is persistently behind incoming traffic, and its throughput setting is below observed sustained volume. What should be evaluated?
- Add a narrow time filter and project the specific alert fields.
- Consider a supported materialized view for the repeated native-table aggregation.
- Adjust the supported Eventstream throughput level after measuring source and sink capacity.
- Tune appropriate retention and caching policies to the business access window.
Explanation
Correct Answer
C:Throughput configuration can constrain processing, but increasing it helps only when downstream destinations can keep pace. Track backlog and output rate after controlled tuning.
Incorrect Answers
A:Time-series stores benefit when queries restrict the relevant temporal range instead of scanning historical events.
B:Materialized views can precompute recurring results from eligible native data rather than repeating full aggregation work.
D:Retention and hot-cache choices affect storage cost and query performance; policy should match historical access needs.
Question 388
A KQL dashboard repeats costly aggregations over unchanged historical logs. Which native optimization may help?
- Tune appropriate retention and caching policies to the business access window.
- Use KQL dcount() for an approximate cardinality estimate grouped by the required time bucket.
- Consider a supported materialized view for the repeated native-table aggregation.
- Adjust the supported Eventstream throughput level after measuring source and sink capacity.
Explanation
Correct Answer
C:Materialized views can precompute recurring results from eligible native data rather than repeating full aggregation work. Compare query latency and view freshness.
Incorrect Answers
A:Retention and hot-cache choices affect storage cost and query performance; policy should match historical access needs.
B:Approximate distinct counting reduces resource use when an estimation error is acceptable, unlike exact counting of all unique identifiers.
D:Throughput configuration can constrain processing, but increasing it helps only when downstream destinations can keep pace.
Question 389
An Eventhouse source table receives many rarely queried historical events that inflate hot storage costs. What should be reviewed?
- Consider a supported materialized view for the repeated native-table aggregation.
- Tune appropriate retention and caching policies to the business access window.
- Apply an eventstream Filter operation before the warning-only destination.
- Adjust the supported Eventstream throughput level after measuring source and sink capacity.
Explanation
Correct Answer
B:Retention and hot-cache choices affect storage cost and query performance; policy should match historical access needs. Review query lookback patterns and retained data.
Incorrect Answers
A:Materialized views can precompute recurring results from eligible native data rather than repeating full aggregation work.
C:Filtering early reduces unnecessary output traffic while leaving raw events available for other consumers.
D:Throughput configuration can constrain processing, but increasing it helps only when downstream destinations can keep pace.
Question 390
A KQL dashboard needs approximate unique device counts over massive telemetry volumes and an exact count is not required. Which query technique can reduce aggregation cost?
- Use KQL dcount() for an approximate cardinality estimate grouped by the required time bucket.
- Build an exact set of every device identifier with make_set() for each bucket.
- Use count() to count all telemetry rows in each time bucket.
- Project just the device column but still calculate count() over the rows.
Explanation
Correct Answer
A:Approximate distinct counting reduces resource use when an estimation error is acceptable, unlike exact counting of all unique identifiers. Compare the approximate result with a bounded exact sample.
Incorrect Answers
B:Materializing an exact device-ID set uses memory and may hit size limits; the requirement permits a scalable approximate unique count. The target requirement is different: a KQL dashboard needs approximate unique device counts over massive telemetry volumes and an exact count is not required.
C:KQL count() counts events, not distinct device identities, so a busy device is counted repeatedly.
D:Projecting away unused fields reduces columns but count() still measures event rows rather than distinct device values.
