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

Questions 11–15

1. 11application · medium

   In an order fact table, the order ID is stored directly in the fact table without a separate dimension table. Analysts need to group by order ID to analyze order-level metrics. What is this design element called?

   • AConformed dimension
   • BDegenerate dimension
   • CJunk dimension
   • DSurrogate key
   Reveal Solution Discussion Select an answer first
2. 12application · medium

   A data modeler is considering a snowflake schema for a dimension with many attributes, such as a product dimension with category, subcategory, and brand. The team values storage efficiency and has a small number of analysts who are comfortable with complex joins. Which statement best describes the trade-off they are making?

   • AThey are trading query performance for reduced storage and easier maintenance of attribute changes.
   • BThey are trading data quality for query performance.
   • CThey are trading storage efficiency for faster query performance and simpler queries.
   • DThey are trading ease of use for better support of slowly changing dimensions.
   Reveal Solution Discussion Select an answer first
4. 13expert · hard

   A company tracks customer support tickets. Each ticket has a creation date, a resolution date, and a status (open, pending, resolved). Analysts need to report on the average time to resolution for tickets resolved in the last quarter, as well as the current backlog of open tickets. Which fact table design best supports both requirements?

   • AA single accumulating snapshot fact table with one row per ticket, including creation and resolution dates.
   • BTwo separate fact tables: one transactional for events and one accumulating for current state.
   • CA transactional fact table with one row per status change event.
   • DA periodic snapshot fact table with one row per ticket per day.
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   A retail company wants to analyze daily sales totals and inventory levels at the end of each day. Which fact table type should they use?

   • ATransactional fact table
   • BFactless fact table
   • CAccumulating snapshot fact table
   • DPeriodic snapshot fact table
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   In a dimensional model, what is the main benefit of bucketing a fact table on a frequently used join key?

   • AIt automatically creates indexes on all dimension columns.
   • BIt improves join performance by co-locating rows with the same key value in the same bucket.
   • CIt ensures that all rows have a unique primary key.
   • DIt reduces the storage space required for the fact table.
   Reveal Solution Discussion Select an answer first