AWS Certified Data Engineer – Associate (DEA-C01) — Question 137

A data engineer maintains a materialized view that is based on an Amazon Redshift database. The view has a column named load_date that stores the date when each row was loaded.

The data engineer needs to reclaim database storage space by deleting all the rows from the materialized view.

Which command will reclaim the MOST database storage space?

Answer options

• A. DELETE FROM materialized_view_name where 1=1
• B. TRUNCATE materialized_view_name
• C. VACUUM table_name where load_date<=current_date materializedview
• D. DELETE FROM materialized_view_name where load_date<=current_date

Correct answer: B

Explanation

The TRUNCATE command is the most efficient way to remove all rows from a materialized view because it doesn't log individual row deletions and reclaims space immediately. In contrast, the DELETE commands would process each row individually, which is less efficient for reclaiming space. The VACUUM command is used for reclaiming space from deleted rows but does not directly delete rows itself.