Exam AWS Certified Data Engineer - Associate DEA-C01 topic 1 question 62 discussion

https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html "A full vacuum doesn't perform a reindex for interleaved tables. To reindex interleaved tables followed by a full vacuum, use the VACUUM REINDEX option." A - "A full vacuum doesn't perform a reindex for interleaved tables."- from the docs above B- "A DELETE ONLY vacuum operation doesn't sort table data." - from the docs above D - "without reclaiming space freed by deleted rows. " - from the docs above

Selected Answer: A

VACUUM REINDEX makes an additional pass to analyze the interleaved sort keys. https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html#r_VACUUM_command-parameters

Reclaiming Space: After updates and deletes, Redshift tables can retain deleted data blocks, taking up space. The VACUUM REINDEX command: Reclaims the space taken up by the deleted rows. Rebuilds indexes on the sort key columns. Analyzing the Sort Key: Since the sort key column contains AWS Regions, rebuilding the indexes on this column will help cluster data according to region. This clustering can improve performance for queries that filter or group by region.

Correct Answer: C Requirements: 1. relcaim the disk space 2. analyze the sork key column Document: https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html#vacuum-reindex VACUUM FULL: A full vacuum doesn't perform a reindex for interleaved tables. To reindex interleaved tables followed by a full vacuum, use the VACUUM REINDEX option. VACUUM REINDEX: Analyzes the distribution of the values in interleaved sort key columns, then performs a full VACUUM operation.

FULL is the only one which claims space and sorts. FULL Sorts the specified table (or all tables in the current database) and reclaims disk space occupied by rows that were marked for deletion by previous UPDATE and DELETE operations. VACUUM FULL is the default. https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html

B is the answer

Option C Analyzes the distribution of the values in interleaved sort key columns, then performs a full VACUUM operation. If REINDEX is used, a table name is required. VACUUM REINDEX takes significantly longer than VACUUM FULL because it makes an additional pass to analyze the interleaved sort keys. The sort and merge operation can take longer for interleaved tables because the interleaved sort might need to rearrange more rows than a compound sort. If a VACUUM REINDEX operation terminates before it completes, the next VACUUM resumes the reindex operation before performing the full vacuum operation.

The answer should be B. The VACUUM DELETE ONLY command is used in Amazon Redshift to remove rows that have been marked for deletion due to updates and deletes in a table.