Optimize Redshift Spectrum Query Performance with Columnar Format and Partitioning

DEA-C01 By islever Editorial Last updated

Automate data processing by using AWS services. Manage the lifecycle of data.

Short answer

AC Configure the third-party application to create files in a columnar format and partition the order data in the S3 bucket based on order date.

Question

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.)
  1. Configure the third-party application to create the files in a columnar format. Correct Answer
  2. Develop an AWS Glue ETL job to convert the multiple daily CSV files to one file for each day.
  3. Partition the order data in the S3 bucket based on order date. Correct Answer
  4. Configure the third-party application to create the files in JSON format.
  5. Load the JSON data into the Amazon Redshift table in a SUPER type column.

Key Takeaways

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.

Common mistake: 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.

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

https://docs.aws.amazon.com/redshift/latest/dg/c-using-spectrum-optimize.html

This link opens the official vendor documentation in a new tab.

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.

More DEA-C01 FAQ →

Community Votes

AC
67%
BC
33%

67% of anonymous learners picked answer AC. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 3 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 3

  1. italiancloud2025 👍 1
    No, porque la opción A implica modificar la aplicación de terceros para que genere archivos en formato columnar, lo cual puede ser más complejo o inviable, mientras que la opción B utiliza un job de Glue para consolidar los CSV sin tocar la fuente. La opción C sigue siendo esencial para particionar por fecha y optimizar las consultas.
  2. Ell89 👍 1
    using parqueet or ORC is efficient and so will be partitioning by order date so the range of data is lower
  3. emupsx1 👍 1
    https://docs.aws.amazon.com/redshift/latest/dg/r_SUPER_type.html

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Amazon Redshift Compound Sort Key Query Optimization

S-grade

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide