Free Microsoft Data Engineering DP-700 Actual Exam Questions - Question 3 Discussion
named DW1 that is modelled by using MD5 hash surrogate keys.
DW1 contains a single fact table that has grown from 200 million rows to 500 million rows during the
past year.
You have Microsoft Power BI reports that are based on Direct Lake. The reports show year-over-year
values.
Users report that the performance of some of the reports has degraded over time and some visuals
show errors.
You need to resolve the performance issues. The solution must meet the following requirements:
Provide the best query performance.
Minimize operational costs.
Which should you do?
It’s C, smaller keys cut storage and speed up joins without extra cost.
B vs C, increasing capacity might help speed but changing keys could reduce storage and improve performance more sustainably.
Option C makes sense because switching surrogate keys to a more efficient data type could reduce storage size and speed up joins, especially with such a massive fact table. MD5 hashes are 128-bit and might be bulkier than necessary, so using a smaller, simpler key could improve query performance without adding capacity costs. Plus, changing the key type targets the root cause if MD5 keys are slowing down queries rather than just throwing more hardware at the problem.
B imo, increasing capacity can directly improve query speed for huge fact tables without changing data models or keys, so it seems like the simplest way to fix performance issues without extra complexity.
D imo, creating views can optimize query logic without increasing costs or changing keys.
Makes sense to avoid just throwing more capacity at the problem. Changing surrogate keys to something smaller than MD5 hashes should cut storage and improve query speed without extra cost, so option C fits better.
The best bet seems to be C. Modifying surrogate keys to a more efficient data type should speed up queries and reduce storage without extra costs, which fits the requirements better than just scaling up the capacity (B). Changing the hash function or creating views won’t really address performance or cost effectively.
C, changing surrogate keys to a more efficient type can speed queries without extra cost.
C vs B – changing data type might reduce storage and speed, less cost than scaling.
It’s B, but does the question mention if we can scale the capacity or if budget is fixed? Increasing capacity might improve performance but could raise costs.