Design Dimensional Models for Analytical Workloads, Ensuring Efficient Querying and Aggregation. DATA-ENGINEER-PROFESSIONAL Practice Questions (Page 1)

Questions 1–5

1. 1foundation · easy

   In a slowly changing dimension (SCD) Type 2 implementation, how are changes to a dimension attribute handled?

   • AA new row is added for the dimension member with the new attribute values, and the old row is marked as inactive.
   • BThe dimension table is deleted and recreated with the new values.
   • CThe existing row is updated with the new value, losing the historical value.
   • DThe change is stored in a separate audit table, and the dimension table remains unchanged.
   Reveal Solution Discussion Select an answer first
2. 2foundation · easy

   What is the primary purpose of partitioning a large fact table in a dimensional model?

   • ATo denormalize dimension tables into a single table.
   • BTo reduce the amount of data scanned by pruning partitions that are not needed for a query.
   • CTo automatically create aggregate tables for common queries.
   • DTo enforce primary key constraints on the fact table.
   Reveal Solution Discussion Select an answer first
4. 3foundation · easy

   What is a primary trade-off when choosing a snowflake schema over a star schema?

   • ASnowflake schemas reduce data redundancy but increase query complexity due to more joins.
   • BSnowflake schemas require more storage space but provide faster queries.
   • CSnowflake schemas are easier to query but harder to maintain.
   • DSnowflake schemas eliminate the need for fact tables.
   Reveal Solution Discussion Select an answer first
5. 4application · medium

   A company has multiple business processes (sales, returns, inventory) that all reference the same 'customer' entity. They want to ensure that the customer dimension is consistent across all fact tables. Which dimension design concept should they apply?

   • AConformed dimension
   • BDegenerate dimension
   • CSlowly changing dimension
   • DJunk dimension
   Reveal Solution Discussion Select an answer first
6. 5foundation · easy

   What is a defining characteristic of a snowflake schema compared to a star schema?

   • ADimension tables are denormalized to reduce joins.
   • BDimension tables are normalized into multiple related tables.
   • CFact tables are split into multiple fact tables for each measure.
   • DAll tables are stored as views to save storage.
   Reveal Solution Discussion Select an answer first