Apply Industry-Standard Data Modeling Techniques, Such as Star, Snowflake, and Data Vault Schemas, to Analytical Workloads. DATA-ANALYST-ASSOCIATE Practice Questions (Page 4)

Questions 16–20

1. 16foundation · easy

   Which of the following is a characteristic of a fact table in a star schema?

   • AIt contains quantitative measures and foreign keys to dimension tables.
   • BIt stores only text attributes and no numeric values.
   • CIt is normalized to reduce data redundancy.
   • DIt contains descriptive attributes that provide context to measures.
   Reveal Solution Discussion Select an answer first
2. 17application · medium

   A company is designing a Databricks SQL table for sales analysis. They have a fact table with millions of rows and dimension tables with thousands of rows. Analysts frequently filter by date and join to the product dimension. Which table design best optimizes query performance?

   • ACreate a view that joins the fact and dimension tables at query time
   • BPartition the fact table by date and use Delta Lake with Z-ordering on product_id
   • CStore the fact table as a CSV file and load it into a temporary view
   • DPartition the dimension tables by product_id
   Reveal Solution Discussion Select an answer first
4. 18application · medium

   A company's product dimension has many attributes, some of which are shared across products (e.g., category, subcategory). They want to reduce data redundancy and storage costs while still supporting analytical queries. Which schema approach best meets this requirement?

   • AStar schema with a single denormalized product dimension
   • BA flat table with all product attributes repeated for each sale
   • CSnowflake schema with normalized dimension tables
   • DData vault schema with separate satellites for each attribute group
   Reveal Solution Discussion Select an answer first
5. 19foundation · easy

   In a data vault schema, what is the role of a hub table?

   • ATo store aggregated measures for analytical queries.
   • BTo define relationships between business entities using foreign keys.
   • CTo store unique business keys and serve as a central reference point for core business entities.
   • DTo store descriptive attributes and historical context for a business entity.
   Reveal Solution Discussion Select an answer first
6. 20foundation · easy

   What is a key advantage of a snowflake schema over a star schema?

   • AIt simplifies queries by reducing the number of joins required.
   • BIt reduces data redundancy and storage costs by normalizing dimension tables.
   • CIt provides better support for slowly changing dimensions.
   • DIt improves query performance by denormalizing all tables.
   Reveal Solution Discussion Select an answer first