Data Engineering Data Warehouse Modeling Questions and Answers 1 — Questions and Answers
Question 1: A retail company wants to analyze sales performance. Their data model includes a central 'Sales' table with measures like 'quantity_sold' and 'total_amount'. This table is linked to other tables such as 'Product', 'Store', and 'Date' which contain descriptive attributes. What is the primary role of the 'Sales' table in this dimensional model?
- To provide descriptive context for the business process.
- To store quantitative measures of business events. (Correct answer)
- To maintain historical changes of dimensional attributes.
- To normalize hierarchical data to save storage space.
Correct answer: To store quantitative measures of business events.
The 'Sales' table is a fact table. The primary role of a fact table in a dimensional model is to store the quantitative, numeric measures of business events or transactions. [4, 8] In this scenario, 'quantity_sold' and 'total_amount' are the facts, while the linked tables ('Product', 'Store', 'Date') are dimension tables that provide context.
Question 2: A data engineering team is designing a data warehouse for a large financial institution. They need to prioritize storage efficiency and data integrity due to complex, multi-level hierarchies in their customer and account dimensions. Query performance is a secondary concern. Which schema design would be most appropriate for this scenario?
- Star Schema
- Data Vault
- Snowflake Schema (Correct answer)
- Flat Denormalized Model
Correct answer: Snowflake Schema
A Snowflake Schema is the most appropriate choice because it normalizes dimension tables into multiple related tables. This reduces data redundancy and improves data integrity, which is crucial for complex hierarchies. [1, 5] While this leads to more complex queries with more joins, it aligns with the stated priorities of storage efficiency and data integrity over query speed. [3, 6]
Question 3: A company needs to track the complete history of changes to its customer dimension, specifically their assigned sales representative. When a customer is reassigned to a new representative, a new record for that customer should be created with the updated information, and the previous record should be preserved. Which Slowly Changing Dimension (SCD) type should be implemented?
- SCD Type 1
- SCD Type 2 (Correct answer)
- SCD Type 0
- SCD Type 3
Correct answer: SCD Type 2
SCD Type 2 is designed to track the full history of changes by creating a new row for each change to a dimension attribute. [12] The old row is preserved, often with effective date columns or a 'current' flag, allowing for accurate historical analysis. SCD Type 1 overwrites the old value, Type 0 assumes no changes, and Type 3 adds a new column for the previous value, offering only limited history. [2, 22]
Question 4: Which of the following best describes a data mart?
- A large, centralized repository for all enterprise data from various sources.
- A schema design that uses multiple fact tables sharing common dimensions.
- A subset of a data warehouse focused on a specific business line or department. (Correct answer)
- An un-modeled, schema-on-read repository for raw structured and unstructured data.
Correct answer: A subset of a data warehouse focused on a specific business line or department.
A data mart is a smaller, focused subset of a data warehouse that is designed for the specific needs of a particular department or business function, such as sales, finance, or marketing. [7, 9, 10] This allows for faster, more tailored access to relevant data for a specific group of users. [15]
Question 5: In a data warehouse model for an e-commerce platform, analysts need to analyze sales transactions and website clickstream data together. Both datasets share common dimensions like 'Customer', 'Product', and 'Date'. Which schema design is specifically intended to model this scenario of multiple business processes sharing dimensions?
- Star Schema
- Snowflake Schema
- Normalized Schema
- Fact Constellation Schema (Correct answer)
Correct answer: Fact Constellation Schema
A Fact Constellation Schema, also known as a Galaxy Schema, is designed for this exact purpose. It features multiple fact tables (e.g., one for sales, one for clickstream events) that share one or more common dimension tables. [19, 21, 24] This allows for integrated analysis across different business processes. [18]
Question 6: When designing a dimensional model, what is the primary advantage of using a Star Schema compared to a Snowflake Schema?
- Reduced data redundancy and lower storage costs.
- Simplified data maintenance and updates.
- Faster query performance due to fewer joins. (Correct answer)
- Improved data integrity through normalization.
Correct answer: Faster query performance due to fewer joins.
The primary advantage of a Star Schema is its simplicity and faster query performance. Because dimension tables are denormalized and connect directly to the central fact table, queries require fewer joins, which typically results in faster data retrieval. [1, 3, 6] Snowflake schemas, while more storage-efficient, require more complex joins, which can slow down query performance. [5]
Data Engineering Data Warehouse Modeling Questions and Answers 1
A retail company wants to analyze sales performance.
Their data model includes a central 'Sales' table with measures like 'quantity_sold' and 'total_amount'.
This table is linked to other tables such as 'Product', 'Store', and 'Date' which contain descriptive attributes.
What is the primary role of the 'Sales' table in this dimensional model?