Answer-first summary for fast verification
Answer: Partition the sales transaction table by product, customer, and time to allow the query to scan only relevant partitions, thereby reducing the amount of data processed and improving performance without significantly increasing storage or maintenance complexity.
Option C is the best solution because partitioning the sales transaction table by product, customer, and time allows the query to scan only the relevant partitions, significantly reducing the amount of data processed and improving query performance. This approach is scalable and does not introduce significant storage or maintenance complexity, making it compliant with data governance policies. While indexing (Option A) and materialized views (Option B) can improve performance, they come with drawbacks such as increased storage requirements and challenges with large or frequently changing datasets. Denormalization (Option D) improves performance but at the cost of increased data redundancy and maintenance complexity, which may not be ideal for scaling and compliance.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
As a Microsoft Fabric Analytics Engineer Associate, you are optimizing a data warehouse query that aggregates sales transaction data by product, customer, and time. The query is currently slow and resource-intensive. The data warehouse is expected to scale significantly over the next year, and the solution must ensure compliance with data governance policies. Considering these constraints, which of the following optimization techniques would you recommend as the BEST solution and why? Choose one option.
A
Implement indexing on the product, customer, and time columns to enhance query performance, despite the potential increase in storage requirements and impact on insert operations.
B
Create a materialized view of the query results to store them in a separate table for faster access, acknowledging the challenges with large datasets or frequently changing data.
C
Partition the sales transaction table by product, customer, and time to allow the query to scan only relevant partitions, thereby reducing the amount of data processed and improving performance without significantly increasing storage or maintenance complexity.
D
Denormalize the sales transaction table by including product, customer, and time information directly in the fact table to improve query performance, despite the potential increase in data redundancy and maintenance complexity.