DEA-C01 Question 264
Select 3A data engineering team is designing a data warehouse solution on Amazon Redshift. The team needs to optimize query performance for analytical workloads and ensure the data model minimizes storage costs. Which of the following design choices align with best practices for data modeling in Amazon Redshift?
- A
Denormalize the data model to reduce the number of JOIN operations.
- B
Use a star schema with dimension and fact tables.
- C
Store frequently accessed data in JSON format to allow flexible querying.
- D
Sort data based on query patterns using SORTKEY.
- E
Use VARCHAR for all columns to allow flexibility in data types.
Show answer and explanation
Correct answers: A, B, D
Explanation
In Amazon Redshift, best practices for data modeling include denormalizing data to avoid expensive JOIN operations, using a star schema for performance and simplicity, and leveraging SORTKEY to optimize query performance based on access patterns. Storing data in JSON format or using generic data types like VARCHAR for all columns is discouraged as it can negatively impact performance and storage efficiency.
- A. Correct.
Correct: Denormalized data models are preferred in Amazon Redshift as they reduce the need for expensive JOIN operations, improving query performance.
- B. Correct.
Correct: A star schema is a widely recommended design for analytical workloads in Redshift as it balances performance and maintainability.
- C. Incorrect.
Incorrect: JSON is not an optimized storage format for query performance or cost in Redshift. Redshift is better suited for structured data.
- D. Correct.
Correct: Using SORTKEY allows Redshift to efficiently retrieve data based on query patterns, improving performance.
- E. Incorrect.
Incorrect: Using VARCHAR for all columns is inefficient and can lead to storage bloat and reduced query optimization. Appropriate column types should be used.