Databricks Data Engineer Professional Question 121
Select 3You are designing a data model in Databricks for a retail company that tracks customer orders. The company requires a star schema design to optimize query performance for reporting use cases. Which of the following steps are essential to implement the star schema correctly in this scenario?
- A
Design dimension tables with denormalized data to minimize joins.
- B
Create a highly normalized fact table to store detailed transactional data.
- C
Establish foreign key relationships between the fact table and dimension tables.
- D
Ensure all dimension tables have surrogate keys for unique identification.
- E
Include aggregate pre-computed summaries in the fact table to improve performance.
Show answer and explanation
Correct answers: A, C, D
Explanation
A star schema design for analytics workloads focuses on having a central fact table containing transactional data and denormalized dimension tables for efficient querying. Dimension tables should have surrogate keys to uniquely identify records and establish relationships with the fact table. This design minimizes joins and provides better performance for analytical queries, which aligns with the requirements of the scenario.
- A. Correct.
Dimension tables in a star schema are denormalized to reduce the need for joins during query execution, which helps optimize performance for analytics workloads.
- B. Incorrect.
Fact tables in a star schema are typically designed to store transactional or event-level data in a denormalized format rather than following a highly normalized structure.
- C. Correct.
Foreign key relationships between the fact table and dimension tables are a fundamental part of the star schema design, enabling efficient querying and filtering.
- D. Correct.
Surrogate keys in dimension tables provide unique identifiers that simplify the relationship between fact and dimension tables and help prevent issues with natural key changes.
- E. Incorrect.
Fact tables should generally store raw transactional data rather than pre-computed summaries, as aggregations are typically calculated dynamically during queries.