AWS Certified Data Engineer Associate DEA-C01 Practice Question

An analytics team loads daily data into an Amazon Redshift table named sales using a COPY command that runs inside a long-lived transaction. While the load is in progress, several BI dashboards that query the same table become unresponsive. The data engineer must prevent user queries from being blocked during future loads while still ensuring atomic delivery of the new data. Which approach meets these requirements following Amazon Redshift best practices?

Issue COPY with the TRUNCATECOLUMNS option to reduce lock duration on the sales table.
Set the cluster-wide default isolation level to READ COMMITTED before running the COPY command.
Load the data into an empty staging table, then execute ALTER TABLE sales APPEND FROM staging within the same transaction.
Create a new table with the loaded data, drop the original sales table, and rename the new table to sales.

Report Issue

Answer Description

COPY acquires write locks on the target table for the duration of the transaction, so concurrent queries wait until the load commits. Loading into an identical staging table avoids locking the production table. After the COPY finishes, ALTER TABLE … APPEND swaps the underlying data blocks in a metadata operation that is almost instantaneous; user queries experience only a brief AccessExclusive lock during the append instead of being blocked for the entire load. Creating a view and renaming tables would still require locks on the base table, and COPY with TRUNCATECOLUMNS or lowering the isolation level does not remove the long write lock held by COPY.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.