DEA-C01 Question #137: Real Exam Question with Answer & Explanation

Question

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?

Options

• ADELETE FROM materialized_view_name where 1=1
• BTRUNCATE materialized_view_name
• CVACUUM table_name where load_date<=current_date materializedview
• DDELETE FROM materialized_view_name where load_date<=current_date

Explanation

To reclaim the most database storage space immediately from a Redshift materialized view by deleting all its rows, the TRUNCATE command is the most effective.

Common mistakes.

• A. The DELETE command, even when deleting all rows, is a Data Manipulation Language (DML) operation that logically marks rows for deletion but does not immediately reclaim the disk space; the space will only be reclaimed later during a VACUUM operation.
• C. The VACUUM command is used to reclaim space from deleted rows and to sort tables, but it does not directly delete all rows from a materialized view and its syntax with a WHERE clause is incorrect for VACUUM.
• D. A DELETE command logically removes rows but does not immediately reclaim storage space, requiring a subsequent VACUUM operation to free up the space.

Concept tested. Redshift TRUNCATE vs DELETE for space reclamation

Reference. https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNCATE_TABLE.html

No community discussion yet for this question.