Data Warehouse Modeling Flashcards

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?

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.

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?

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]

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?

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]

Which of the following best describes a data mart?

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]

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?

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]

When designing a dimensional model, what is the primary advantage of using a Star Schema compared to a Snowflake Schema?

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]