AWS Certified Data Engineer Associate DEA-C01 Practice Question

A data engineering team runs a nightly COPY command that bulk-loads 1 TB of data into the fact_sales table in Amazon Redshift. While the load is in progress, analysts report that their queries against fact_sales are unresponsive until the job commits. The team must prevent the load process from blocking read access, and they want a solution that keeps operational overhead and cost to a minimum. Which approach will meet these requirements?

Move the COPY operation to a dedicated WLM queue with higher concurrency so SELECT queries can run in parallel.
Add the ACCEPTINVCHARS parameter to the COPY command to reduce lock contention during the load.
Load the new data into a staging table and then run ALTER TABLE fact_sales APPEND FROM the staging table once the load completes.
Enable user activity logging to allow Redshift to release long-running locks automatically during COPY.

Report Issue

Answer Description

The COPY command acquires a ShareRowExclusiveLock on the target table until the transaction commits, which can block concurrent SELECT statements. A common best practice is to load incoming data into a staging table that has the same distribution style, sort keys, and column definitions as the production table. After the COPY finishes, the ALTER TABLE APPEND command moves the data blocks into the target table by updating metadata only, so the required ACCESS EXCLUSIVE lock is held for a very short time. This approach keeps dashboards available with negligible additional cost or complexity. Placing the COPY in a different WLM queue, enabling user activity logging, or changing COPY parameters does not resolve the lock contention because the COPY transaction would still hold a lock for its full duration.

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.