nerdexam
Amazon

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…

The correct answer is B. TRUNCATE materialized_view_name. 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.

Data Store Management

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

How the community answered

(35 responses)
  • A
    3% (1)
  • B
    94% (33)
  • C
    3% (1)

Why each option

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.

ADELETE FROM materialized_view_name where 1=1

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.

BTRUNCATE materialized_view_nameCorrect

The TRUNCATE command is a Data Definition Language (DDL) operation that quickly removes all rows from a table or materialized view and immediately reclaims the disk space, making it the most efficient way to clear and reclaim space compared to DELETE.

CVACUUM table_name where load_date<=current_date materializedview

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.

DDELETE FROM materialized_view_name where load_date<=current_date

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

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

Topics

#Redshift#Materialized Views#Storage Management#SQL Commands

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full DEA-C01 Practice