Recommended Practices for AWS Redshift

Answer - A and C.

The AWS Documentation mentions the following.

ETL transformation logic often spans multiple steps.

Because commits in Amazon Redshift are expensive, if each ETL step performs a commit, multiple concurrent ETL processes can take a long time to execute.

To minimize the number of commits in a process, the steps in an ETL script should be surrounded by a BEGIN…END statement so that a single commit is performed only after all the transformation logic.

Use UNLOAD to extract large results sets directly to S3

After it's in S3, the data can be shared with multiple downstream systems.

By default, UNLOAD writes data in parallel to multiple files according to the number of slices in the cluster.

All the compute nodes participate to quickly offload the data into S3.

The other options are invalid since for large data sets use the UNLOAD command and for copying data use the COPY command.

For more information on high performance for Redshift, please visit the url.

https://aws.amazon.com/blogs/big-data/top-8-best-practices-for-high-performance-etl-processing-using-amazon-redshift/

The two recommended practices from AWS for using Amazon Redshift in ETL processes are:

A. For various ETL processes which use AWS Redshift commits, use transaction handling C. Extract Large results sets from Redshift using the UNLOAD statement

Explanation:

A. For various ETL processes which use AWS Redshift commits, use transaction handling: Using transaction handling in ETL processes that use Amazon Redshift helps ensure data consistency and accuracy. Transaction handling can guarantee that all SQL statements within a transaction are executed or none of them are executed. If an error occurs during a transaction, all the changes made up to that point can be rolled back, ensuring data integrity. AWS recommends using transaction handling in ETL processes that use Redshift commits to ensure data consistency and accuracy.

C. Extract Large results sets from Redshift using the UNLOAD statement: When extracting large result sets from Amazon Redshift, it is recommended to use the UNLOAD statement instead of the SELECT statement. The UNLOAD statement can unload the data in parallel and write the output directly to Amazon S3, reducing the load on Redshift clusters. It also supports data compression and encryption. The SELECT statement, on the other hand, may cause performance issues if the result set is too large, as it may consume too many resources in the cluster.

B. Extract Large results sets from Redshift using the select query: Extracting large result sets from Redshift using the SELECT statement is not recommended as it can cause performance issues. The SELECT statement is not designed for bulk data movement, and it may consume too many resources in the cluster, affecting the overall performance of the system.

D. Copy data into Redshift using Insert queries: Copying data into Redshift using INSERT queries is not recommended for large data sets. Instead, it is recommended to use the COPY command, which is designed to load large data sets efficiently. The COPY command is faster and more efficient than the INSERT command, and it supports parallel data loading, compression, and encryption.

In conclusion, the recommended practices for using AWS Redshift for ETL processes are to use transaction handling for processes that use Redshift commits and to extract large result sets using the UNLOAD statement.