Data Engineering Data Warehouse Modeling 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?

Accepted Answer

To store quantitative measures of business events.

Answer

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?

Accepted Answer

Snowflake Schema

Answer

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?

Accepted Answer

SCD Type 2

Answer

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?

Accepted Answer

A subset of a data warehouse focused on a specific business line or department.

Answer

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?

Accepted Answer

Fact Constellation Schema

Answer

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]

Data Engineering Practice Test

Data Engineering Practice Test

Data Engineering Data Warehouse Modeling Questions and Answers