DEA-C01 Question 253
Single answerA company is using Amazon Redshift to store their customer data in a star schema format. Recently, they decided to add a new column to one of the dimension tables to track customer loyalty level. They want to ensure the schema evolution does not disrupt existing queries or require significant downtime. What is the most suitable way to handle this schema change in Amazon Redshift?
- A
Use the ALTER TABLE command to add the new column to the dimension table.
- B
Drop the existing dimension table and recreate it with the new column.
- C
Export the existing data, modify the schema offline, and re-import the data into a new table.
- D
Create a new table with the updated schema and use a UNION query to combine it with the old table.
Show answer and explanation
Correct answer: A
Explanation
Amazon Redshift makes schema evolution straightforward by supporting the ALTER TABLE command, which allows users to add new columns to tables without downtime or disruption. This capability is designed to handle such changes efficiently, making it the most suitable approach in this scenario.
- A. Correct.
This is the correct approach. Amazon Redshift supports schema evolution by allowing you to use the ALTER TABLE command to add columns without disrupting existing queries or requiring downtime.
- B. Incorrect.
Dropping and recreating the table would cause disruption and potential data loss unless the data is backed up and reloaded. This is not an optimal solution for schema evolution.
- C. Incorrect.
Exporting data, modifying the schema offline, and re-importing it is a time-consuming and complex process. It is unnecessary when schema evolution can be handled directly within Amazon Redshift.
- D. Incorrect.
Using a UNION query to combine tables is not a practical solution for schema evolution. It increases complexity and does not address the underlying schema change directly.