Databricks Data Engineer Professional Question 123
Select 3A data engineering team is tasked with creating a dimensional model in Databricks for a retail company. The model will be used for analytical queries on sales data. The team decides to implement a star schema. Which of the following are characteristics of a well-designed star schema in this scenario?
- A
Fact tables store quantitative data and are connected to dimension tables via foreign keys.
- B
Dimension tables contain descriptive attributes and typically have a denormalized structure.
- C
All tables in a star schema should be normalized to reduce data redundancy.
- D
Fact tables should have a composite primary key made up of foreign keys from dimension tables.
- E
Dimension tables must contain only numeric columns to improve query performance.
Show answer and explanation
Correct answers: A, B, D
Explanation
A well-designed star schema consists of a central fact table that stores quantitative data (e.g., sales, revenue) and is connected to dimension tables via foreign keys. Dimension tables are often denormalized to optimize query performance and contain descriptive attributes, such as product names or customer information. Fact tables typically include a composite primary key made up of foreign keys from the dimension tables. Normalization is not a goal for dimension tables in a star schema, and they can contain a variety of data types, not just numeric columns.
- A. Correct.
Correct: Fact tables store quantitative data (e.g., sales, revenue) and use foreign keys to connect to dimension tables for context.
- B. Correct.
Correct: Dimension tables typically contain descriptive data (e.g., product names, categories) and are often denormalized to optimize query performance.
- C. Incorrect.
Incorrect: Star schemas intentionally use denormalized dimension tables to improve query performance, even if it leads to some data redundancy.
- D. Correct.
Correct: Fact tables often use a composite primary key composed of foreign keys to uniquely identify rows.
- E. Incorrect.
Incorrect: Dimension tables can contain various data types, including text and dates, as they provide context for fact table data.