ExamTopics Logo
Mail Us[email protected]
Menu
Amazon Discussions
exam questions

Exam AWS Certified Database - Specialty All Questions

View all questions & answers for the AWS Certified Database - Specialty exam
Go to Exam

Exam AWS Certified Database - Specialty topic 1 question 347 discussion

Exam question from Amazon's AWS Certified Database - Specialty
Question #: 347
Topic #: 1
[All AWS Certified Database - Specialty Questions]

A retail company uses Amazon Redshift for its 1 PB data warehouse. Several analytical workloads run on a Redshift cluster. The tables within the cluster have grown rapidly. End users are reporting poor performance of daily reports that run on the transaction fact tables.

A database specialist must change the design of the tables to improve the reporting performance. All the changes must be applied dynamically. The changes must have the least possible impact on users and must optimize the overall table size.

Which solution will meet these requirements?

  • A. Use the STL_SCAN view to understand how the tables are getting scanned. Identify the columns that are used in filter and group by conditions. Create a temporary table with the identified columns as sort keys and compression as Zstandard (ZSTD) by copying the data from the original table. Drop the original table. Give the temporary table the same name that the original table had.
  • B. Run an explain plan to analyze the queries on the tables. Consider recommendations from Amazon Redshift Advisor. Identify the columns that are used in filter and group by conditions. Convert the recommended columns from Redshift Advisor into sort keys with compression encoding set to RAW. Set the rest of the column compression encoding to AZ64.
  • C. Run an explain plan to analyze the queries on the tables. Consider recommendations from Amazon Redshift Advisor. Identify the columns that are used in filter and group by conditions. Convert the recommended columns from Redshift Advisor into sort keys with compression encoding set to LZO. Set the rest of the column compression encoding to Zstandard (ZSTD).
  • D. Run an explain plan to analyze the queries on the tables. Consider recommendations from Amazon Redshift Advisor. Identify the columns that are used in filter and group by conditions. Create a deep copy of the table with the identified columns as sort keys and compression for all columns as Zstandard (ZSTD) by using a bulk insert. Drop the original table. Give the copy table the same name that the original table had.
Show Suggested Answer Hide Answer
Suggested Answer: B 🗳️
by marll88 at Nov. 26, 2023, 8:16 a.m.
Disclaimers:
  • - ExamTopics website is not related to, affiliated with, endorsed or authorized by Amazon.
  • - Trademarks, certification & product names are used for reference only and belong to Amazon.

Comments

Chosen Answer:
This is a voting comment (?). It is better to Upvote an existing comment if you don't have anything to add.
Switch to a voting comment New
Submit Cancel
tsangckl
1 year, 1 month ago
Selected Answer: B
Running an explain plan to analyze the queries on the tables. It helps to understand how the queries are being executed and where the performance bottlenecks might be. Considering recommendations from Amazon Redshift Advisor is a smart move. Redshift Advisor analyzes all the clusters in your account to generate tailored recommendations on how to optimize performance and decrease operating costs. Identifying the columns that are used in filter and group by conditions and converting them into sort keys can significantly improve query performance. Sort keys determine the order in which the data is physically stored in a table and can greatly reduce the amount of data that needs to be read from disk during query execution. Setting the compression encoding for the recommended columns to RAW and the rest of the column compression encoding to AZ64 is a good strategy. RAW encoding disables compression and can be useful for frequently accessed columns, while AZ64 is a high-performance compression encoding optimized for analytic queries and is suitable for the rest of the columns.
upvoted 1 times
...
Doox
1 year, 1 month ago
B https://docs.aws.amazon.com/prescriptive-guidance/latest/query-best-practices-redshift/best-practices-tables.html Avoid compressing the sort key column.
upvoted 1 times
Doox
1 year, 1 month ago
You can compress the distribution key, <b>but you must avoid compressing the sort key column (especially the first column of the sort key). </b>
upvoted 1 times
...
...
MultiAZ
1 year, 4 months ago
D You cannot change the compression of a column, without recreating the table
upvoted 1 times
Skarlex77
1 year, 3 months ago
wrong https://aws.amazon.com/about-aws/whats-new/2020/10/amazon-redshift-supports-modifying-column-comprression-encodings-to-optimize-storage-utilization-query-performance/?nc1=h_ls
upvoted 1 times
...
...
rrshah83
1 year, 4 months ago
Selected Answer: B
https://aws.amazon.com/about-aws/whats-new/2020/10/amazon-redshift-supports-modifying-column-comprression-encodings-to-optimize-storage-utilization-query-performance/ AZ64, a new compression encoding that consumes 5-10% less storage than ZSTD and enables queries to run 70% faster. Previously, customers who wanted to take advantage of new encoding algorithms such as AZ64 needed to recreate the entire table. Since Redshift recommends that columns defined as SORT keys should not be compressed, previously customers who apply sort keys to existing tables needed to recreate the entire table.
upvoted 3 times
...
calduck
1 year, 5 months ago
B. Redshift now supports modifying column compression encodings to optimize storage utilization and query performance. https://aws.amazon.com/about-aws/whats-new/2020/10/amazon-redshift-supports-modifying-column-comprression-encodings-to-optimize-storage-utilization-query-performance/
upvoted 2 times
...
marll88
1 year, 5 months ago
I think D. Not B and C RedShift does not allow ALTER TABLE to change column compression type
upvoted 1 times
...
Community vote distribution
A (35%)
C (25%)
B (20%)
Other
Most Voted
A voting comment increases the vote count for the chosen answer by one.

Upvoting a comment with a selected answer will also increase the vote count towards that answer by one. So if you see a comment that you already agree with, you can upvote it instead of posting a new comment.

SaveCancel
exam
Someone Bought Contributor Access for:
SY0-701
London, 1 minute ago