DEA-C01 Question 254
Select 3A data engineering team is tasked with designing a data model for a retail application that ingests transactional data into an Amazon Redshift cluster. Over time, the schema is expected to evolve as new business requirements emerge. How should the team design the data model to handle schema evolution effectively while ensuring optimal performance?
- A
Use a star schema design and leverage late-binding views to accommodate schema changes without impacting downstream queries.
- B
Store all incoming data in a single wide table with many nullable columns to account for potential schema changes.
- C
Implement table partitioning and versioning to maintain historical schema changes without overwriting existing data.
- D
Use JSON or semi-structured data columns in Redshift to store evolving attributes, and parse them only when required.
- E
Design an append-only schema where older records are never updated, and only new records reflecting schema changes are added.
Show answer and explanation
Correct answers: A, C, D
Explanation
To design a data model that handles schema evolution effectively in Amazon Redshift, it is important to balance schema flexibility, performance, and maintainability. Using a star schema with late-binding views helps isolate schema changes from downstream queries. Table partitioning and versioning ensure historical data is preserved during schema updates. Additionally, semi-structured data columns offer flexibility for attributes that may change over time. These approaches provide a robust solution for managing schema evolution in a scalable way.
- A. Correct.
Using a star schema design and late-binding views allows the team to abstract schema changes from downstream dependencies, making it a robust option for handling schema evolution.
- B. Incorrect.
Storing all incoming data in a single wide table with many nullable columns is inefficient and can degrade performance over time. It also makes querying and maintenance complex.
- C. Correct.
Implementing table partitioning and versioning supports schema evolution by maintaining historical data and preventing overwrites, ensuring data consistency.
- D. Correct.
Using JSON or semi-structured data columns in Redshift is a flexible approach to handle evolving attributes without requiring frequent schema modifications.
- E. Incorrect.
An append-only schema does not directly address schema evolution requirements and could lead to data redundancy, making it less ideal for this use case.