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

Questions 11–15

1. 11foundation · easy

   What is the role of clustering (or clustering keys) in a data warehouse table?

   • ATo enforce uniqueness of rows in the table.
   • BTo physically order rows in a table based on the clustering columns, improving the performance of queries that filter or group by those columns.
   • CTo create a copy of the table for backup purposes.
   • DTo compress the table to reduce storage costs.
   Reveal Solution Discussion Select an answer first
2. 12foundation · easy

   What is the main trade-off of normalizing a data warehouse schema to 3NF compared to using a denormalized star schema?

   • A3NF makes the schema easier to query for business users because it mirrors the source system exactly.
   • B3NF increases data redundancy but improves query performance by reducing the number of tables.
   • C3NF reduces data redundancy but typically requires more joins, which can slow down analytical queries.
   • D3NF eliminates the need for primary keys, simplifying the schema.
   Reveal Solution Discussion Select an answer first
4. 13application · medium

   A telecommunications company stores call detail records (CDRs) in BigQuery. Analysts need to query call volume by hour, region, and customer segment. The table is large and partitioned by date. Which architecture is most effective for these queries?

   • APartition the table by region and cluster by hour
   • BCluster the table by region and customer segment
   • CCreate an index on hour and region
   • DUse a separate table for each customer segment
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   What is the main advantage of a snowflake schema over a star schema?

   • AIt eliminates the need for fact tables.
   • BIt simplifies queries by reducing the number of joins.
   • CIt reduces data redundancy by normalizing dimension tables.
   • DIt is better suited for OLTP workloads.
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   In the context of data warehouse design, what is the role of a primary key in a dimension table?

   • AIt is a column that stores aggregated measures such as total sales or count of orders.
   • BIt defines the relationship between two dimension tables by storing the primary key of another table.
   • CIt uniquely identifies each row in the dimension table and is referenced by foreign keys in fact tables.
   • DIt is an attribute that describes a business entity, such as customer name or product color.
   Reveal Solution Discussion Select an answer first