DP-900 Describe an analytics workload on Azure Practice Question
A company uses Azure Synapse Analytics for its data warehouse. They notice that query performance is degrading over time as data grows. Which action would most likely improve performance without requiring additional compute resources?
⚠ Common exam trap
Test-takers frequently confuse partitioning with indexing or scaling, and incorrectly assume that removing indexes or migrating to a different service is a valid optimization without considering the 'no additional compute resources' constraint.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Partition large tables based on date or other high-cardinality columns
Partitioning large tables on a high-cardinality column like date enables partition elimination, where queries only scan relevant partitions instead of the entire table. This reduces I/O and improves performance without requiring additional compute resources, as it optimizes data access patterns within the existing Synapse SQL pool.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Partition large tables based on date or other high-cardinality columns
Why this is correct
Partitioning large tables on a date or other high-cardinality column enables partition elimination, so a query only reads the relevant partitions instead of scanning the entire table. In Synapse dedicated SQL pools, this reduces I/O and improves response times for queries that filter by that column, and it also simplifies lifecycle operations like sliding-window data loads.
- ✗
Migrate to a star schema on a separate Azure SQL Database
Why it's wrong here
Migrating to a star schema on a separate Azure SQL Database is an unnecessary and disruptive change because star schemas can be built inside Synapse Analytics itself. Azure SQL Database is transaction-optimized, not analytics-optimized, so moving the warehouse there would likely reduce large-query performance and add data movement overhead, making it an ineffective quick optimization.
- ✗
Increase the Synapse SQL pool service level
Why it's wrong here
Increasing the Synapse SQL pool service level adds more compute resources, which may improve query performance but only by scaling up, not by improving the data design. This is a costly, brute-force approach that does not address inefficient scanning caused by poor partitioning; the most effective first step is a design change that reduces data read per query.
- ✗
Remove columnstore indexes from large tables
Why it's wrong here
Removing columnstore indexes from large tables would severely degrade analytical performance because columnstore indexes use columnar compression and batch mode processing, which are far more efficient for data warehouse scans than row-based storage. In Synapse dedicated pools, columnstore is the default index type, and dropping it forces more I/O and slower scans, so this is counterproductive.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Table
A table is a structured collection of data organized into rows and columns, used in databases and spreadsheets to store and manage information efficiently.
Key term
Column
A column is a vertical set of values in a database table that stores one specific type of attribute for every row.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-900 exam.