In a previous design, a view was built to directly query data from S3, causing slowdown. How did you address this performance issue?

In the previous design, a materialized view was built to query data directly from S3, which caused significant slowdown due to the high latency of S3 reads and lack of indexing. To address this, I first identified the query patterns and moved the data into Amazon Redshift, partitioning tables by date to reduce scan size. I also created materialized views in Redshift to pre‑aggregate frequently used metrics. For ad‑hoc queries, I leveraged Redshift Spectrum to query S3 data directly but with proper columnar storage (Parquet) and partitioning. Additionally, I implemented caching in the application layer for repeated queries. As a result, query latency dropped from an average of 30 seconds to under 2 seconds, and the system could handle a 5× increase in concurrent users.