3.2 Planning for Using a Data Warehouse PROFESSIONAL-DATA-ENGINEER Practice Questions (Page 1)

Questions 1–5

1. 1foundation · easy

   A business requirement states: 'We need to analyze total sales by product category and by month.' In a dimensional model, what is the measure in this requirement?

   • ATotal sales
   • BProduct category
   • CMonth
   • DSales transactions
   Reveal Solution Discussion Select an answer first
2. 2foundation · easy

   When designing a data model for a data warehouse, what is the primary purpose of a fact table?

   • ATo store metadata about the data warehouse schema, such as table names and column types.
   • BTo store quantitative measures and foreign keys that link to dimension tables.
   • CTo store descriptive attributes about business entities such as customers and products.
   • DTo store the primary keys of all dimension tables in a single location for easy lookup.
   Reveal Solution Discussion Select an answer first
4. 3application · medium

   A media streaming company stores viewer activity data in BigQuery. Analysts frequently query by viewer ID and content genre, and they also need to run time-series analyses over the last 30 days. The table is partitioned by date. Which additional architecture should be used to optimize these queries?

   • APartition the table by viewer ID
   • BCreate a secondary index on viewer ID
   • CCluster the table by viewer ID and content genre
   • DUse a separate table for each content genre
   Reveal Solution Discussion Select an answer first
5. 4foundation · easy

   A data warehouse is designed for a sales analytics application where users frequently run queries that join sales facts with product and store dimensions. The product dimension has a hierarchy: product -> category -> department. Which normalization degree is most appropriate for this dimension to balance query performance and maintainability?

   • AStore the dimension as a flat file and load it into memory for each query.
   • BFully normalize the dimension to 3NF to eliminate all redundancy.
   • CDenormalize the dimension into a single table with all attributes, including category and department.
   • DUse a snowflake schema with separate tables for product, category, and department.
   Reveal Solution Discussion Select an answer first
6. 5foundation · easy

   When deciding the degree of normalization for a data warehouse, which factor most strongly favors a denormalized schema?

   • AThe desire to mirror the source operational system exactly.
   • BThe need to enforce strict data integrity through foreign key constraints.
   • CThe need to minimize storage costs by eliminating redundant data.
   • DThe requirement to support frequent, complex analytical queries with minimal joins.
   Reveal Solution Discussion Select an answer first