Optimize Redshift Spectrum Query Performance with Columnar Format and Partitioning
A retail company uses an Amazon Redshift data warehouse and an Amazon S3 bucket. The company ingests retail order data into the S3 bucket every day. The company stores all order data at a single path within the S3 bucket. The data has more than 100 columns. The company ingests the order data from a third-party application that generates more than 30 files in CSV format every day. Each CSV file is between 50 and 70 MB in size. The company uses Amazon Redshift Spectrum to run queries that select sets of columns. Users aggregate metrics based on daily orders. Recently, users have reported that the performance of the queries has degraded. A data engineer must resolve the performance issues for the queries. Which combination of steps will meet this requirement with LEAST developmental effort? (Choose two.)
Community Votes
67% of anonymous learners picked answer AC. Votes are pick records left by other test-takers — they are not the verified answer.
Community Insight
The core trap is ignoring the 'least developmental effort' constraint when choosing between modifying the source application versus using ETL jobs, while recognizing that format and partitioning are the primary drivers for Spectrum efficiency.
This question addresses optimizing Amazon Redshift Spectrum queries by leveraging columnar file formats and partitioning to reduce I/O overhead. It establishes that adopting a columnar format (like Parquet) and partitioning data by date are the most effective steps for performance improvement.
Many candidates choose B (AWS Glue ETL) instead of A, mistakenly believing that consolidating files via ETL is less effort than configuring the third-party app, or they overlook that columnar formats provide significant query pruning benefits over CSV.
Community Discussion (3 comments)
Comments & Corrections
No comments yet — spotted an error or have a note? Share it below.
Expert Analysis
Why the Answer Is Correct
Amazon Redshift Spectrum performs best with columnar data formats like Parquet or ORC because it can read only the specific columns needed for a query, significantly reducing I/O compared to row-based formats like CSV. Additionally, partitioning the data in S3 by a high-cardinality field like order date allows Spectrum to prune entire directories from the scan, further boosting performance. Option A ensures the data is stored efficiently, and Option C enables directory pruning; together, they address the root causes of degraded query performance.Why the Other Options Are Wrong
Option B involves developing an AWS Glue ETL job, which requires more developmental effort than simply configuring the source application (Option A). Furthermore, consolidating multiple small CSVs into one large CSV does not improve query performance as much as switching to a columnar format. Options D and E involve loading JSON into a SUPER type, which is a schema design change for semi-structured data and does not directly address the performance issues caused by inefficient storage formats and lack of partitioning in the existing CSV setup.Community Comment Notes
Community feedback strongly supports AC, noting that "using parqueet or ORC is efficient and so will be partitioning by order date." Some users initially considered B due to concerns about modifying third-party apps, but the consensus confirms that A is preferred for performance gains and aligns with AWS best practices for Spectrum optimization.Official Reference
Exam Strategy
When asked for 'least developmental effort' in cloud architecture questions, always compare the cost of changing infrastructure/code (ETL jobs) versus configuration changes (source settings). Prioritize options that leverage native engine optimizations like partitioning and columnar storage for performance-heavy workloads.
Frequently Asked Questions
Why is Parquet better than CSV for Redshift Spectrum?
Parquet is a columnar format that allows Spectrum to skip reading irrelevant columns, drastically reducing I/O and improving query speed compared to row-based CSV.
Does partitioning by date help if I query random dates?
Yes, partitioning allows Spectrum to prune entire directories. If you query a specific date range, it only scans those partitions, avoiding a full table scan.
Related Analysis
Practice All DEA-C01 Questions
Access 100 questions with complete answers and detailed explanations.
View Full DEA-C01 Practice Test →