DP-900 Describe core data concepts Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✗

  Single flat table containing all attributes

  Why it's wrong here

  A single flat table consolidates every business attribute into one massive, denormalized structure. This creates significant data redundancy—for example, customer or product details are repeated on every order row—and leads to update anomalies that compromise data consistency. As the table grows, analytical queries that scan the entire table become increasingly slow, and storage costs inflate unnecessarily. Without separate dimensions and facts, you also lose the ability to track historical changes per attribute, making it unsuitable for a warehouse workload.

• ✗

  Wide table with repeated customer attributes per order

  Why it's wrong here

  This design repeats customer information (such as name, segment, and location) on every order line, which is a specific form of denormalization that amplifies storage and maintenance burdens. Any customer detail change forces updates across potentially millions of rows, risking inconsistency if any are missed. The table becomes extremely wide and tall, degrading columnar compression and columnstore index efficiency in Azure Synapse Analytics. It also conflates transactional order details with descriptive customer data, preventing the clean separation of measures and dimensions that star schemas enforce.

• ✗

  Highly normalized design with many tables

  Why it's wrong here

  While normalization eliminates redundancy, it fragments business data into dozens of related tables requiring complex multi-table joins to answer even basic analytical questions. In a data warehouse, these joins are extremely expensive because they involve massive fact tables and numerous dimension lookups, undermining query performance and complicating ETL pipelines. Normalized schemas are optimized for OLTP write consistency, not for read-heavy analytics, and they do not leverage the denormalized dimension structures that accelerate aggregation and reporting. For Azure Synapse Analytics, the extra join overhead is particularly harmful due to network shuffles when distributed tables are joined.

• ✓

  Star schema with dimension and fact tables

  Why this is correct

  This design is the industry-standard dimensional model for data warehousing, consisting of a central fact table that stores numeric measures and foreign keys, surrounded by denormalized dimension tables that describe business entities. It minimizes redundancy because each dimension attribute is stored once, while the fact table remains lean and scalable. Queries benefit from star join optimizations, efficient use of columnstore indexes, and the ability to pre-aggregate facts at different grain levels. In Azure Synapse Analytics, designers can hash-distribute fact tables on a key and replicate dimension tables to reduce data movement, directly improving analytical query performance.

Go deeper

Related to this question