4.2.1. SQL Analysis with Athena and Redshift
💡 First Principle: Athena and Redshift both execute SQL, but their architectures serve different query patterns. Athena is serverless and scans S3 directly — ideal for infrequent, ad-hoc queries on data lake data with no upfront cost. Redshift maintains its own storage and query engine — ideal for frequent, complex, concurrent queries where sub-second response time and caching matter.
Athena key features: partition projection (calculate partitions mathematically instead of querying the Glue Catalog), workgroups (isolate queries by team with separate cost tracking and data usage controls — a per-query scan limit cancels any query that exceeds it, while workgroup-wide limits over a period trigger alerts), CTAS (create tables from query results — materialize an expensive aggregation once as a compressed Parquet table and refresh it on a schedule, so repeated reads scan the small result instead of the raw data), and federated query (query data in RDS, DynamoDB, and other sources via Lambda connectors). Views in Athena create reusable query abstractions without materializing data. Query result reuse returns the stored result of an identical earlier query in the same workgroup instead of scanning S3 again, as long as it is younger than the maximum age you set (default 60 minutes, up to 7 days). Athena doesn't check the source data for changes within that window, so use it when data changes less often than the reuse age.
Redshift SQL supports stored procedures for complex transformation logic, materialized views for precomputed aggregation, and workload management (WLM) queues for prioritizing different query types. Redshift ML lets analysts train a model with a SQL CREATE MODEL statement (Redshift uses SageMaker Autopilot behind the scenes) and call it as a SQL prediction function. The v1.1 syllabus explicitly tests SQL in both Redshift and Athena.
| Decision Factor | Choose Athena | Choose Redshift |
|---|---|---|
| Query frequency | Occasional, ad-hoc | Frequent, repeated |
| Data location | S3 (data lake) | Loaded into Redshift or via Spectrum |
| Concurrency | Low-moderate | High (hundreds of users) |
| Latency | Seconds to minutes | Sub-second (cached) |
| Cost model | Per TB scanned | Per cluster-hour or RPU-second |
| Setup | None (serverless) | Cluster or serverless configuration |
⚠️ Exam Trap: Athena's MSCK REPAIR TABLE and partition projection both handle partitions but differently. MSCK updates the Glue Catalog by scanning S3 (slow for many partitions). Partition projection calculates partitions at query time without the catalog (fast, no crawl needed). For time-series data with many date partitions, partition projection is strongly preferred.
Reflection Question: An analytics team runs 20 standard reports every morning at 8 AM, each joining 3 tables with billions of rows. Throughout the day, they also run occasional ad-hoc queries on historical data in S3. What's the optimal architecture?
