Amazon Athena & Redshift

Amazon Athena is an interactive query service that runs standard SQL (using the Trino and Presto engines) against data stored in S3. It is serverless, so there are no clusters to provision: you define a table schema in the AWS Glue Data Catalog (a managed metadata store that maps files in S3 to table and column definitions), point it at an S3 location, and query. Pricing is based on the amount of data scanned per query (about $5 per terabyte scanned), so reducing scanned data directly reduces cost. Athena suits ad hoc analysis, log querying, and infrequent reporting where standing up a warehouse is not justified.

Amazon Redshift is a fully managed data warehouse built for online analytical processing (OLAP, meaning aggregations and scans across large tables rather than many small transactional reads and writes). Data is loaded into a cluster of nodes (a leader node that plans queries plus compute nodes that store and process data) using columnar storage, compression, and massively parallel processing (MPP, where work is split across all compute nodes at once). You can run a provisioned cluster (priced per node hour) or Redshift Serverless (priced per usage). Redshift fits frequent, complex joins and aggregations over large, structured datasets, such as business-intelligence dashboards.

Redshift Spectrum lets a Redshift cluster run SQL against data stored in S3 without loading it into the cluster first. You keep hot, frequently joined data in Redshift tables and leave large, cold, or rarely queried data in S3, then join across both in one query. Spectrum uses a separate fleet of compute managed by AWS and is priced per terabyte scanned, like Athena. This is the standard pattern for extending a warehouse to a data lake (a large repository of raw data in S3) without growing the cluster.

For Athena and Spectrum, the dominant lever is reducing data scanned: store data column by column, compress it, and partition it (organize files into folders by a column such as date so the engine skips irrelevant partitions). Selecting only needed columns also lowers scan volume. For Redshift, performance comes from the distribution style (DISTKEY, which controls how rows spread across nodes to keep joins on the same node) and the sort key (SORTKEY, which orders rows on disk so range scans skip blocks). Choosing these well minimizes data shuffled between nodes and blocks read from disk.

Parquet and ORC (Optimized Row Columnar) are open columnar file formats: they store values column by column rather than row by row. Because Athena, Spectrum, and Redshift read only the columns a query needs, a columnar format scans far less data than row formats like CSV or JSON, which lowers both runtime and the per-terabyte-scanned bill. Both formats also support compression (such as Snappy) and store statistics that let engines skip blocks. Converting raw CSV or JSON to Parquet or ORC is one of the highest-impact optimizations for S3-based analytics.