2.7.1. SQL for Data Transformation and Query Optimization

💡 First Principle: SQL is the most powerful data transformation tool you already know. A well-written SQL query in Athena or Redshift can join, aggregate, pivot, and reshape data faster than writing equivalent Python code — because the query engine optimizes execution automatically. Understanding how the engine optimizes helps you write queries that run in seconds instead of minutes.

The exam tests practical SQL competence within the context of AWS services:

Athena SQL queries S3 data via the Glue Data Catalog. Key optimization techniques: use Parquet/ORC formats (columnar scan), partition data by commonly filtered columns (Athena prunes partitions that don't match the WHERE clause), use MSCK REPAIR TABLE to sync new S3 partitions, and enable partition projection to avoid catalog lookups entirely for time-series data.

Redshift SQL operates on the data warehouse. Key exam topics: materialized views (precomputed query results, refreshed on demand or incrementally), stored procedures (encapsulate complex transformation logic server-side), and distribution/sort key-aware joins (Redshift optimizes joins when distribution keys align).

Common SQL patterns on the exam: CTEs (WITH clauses) for readability, window functions (ROW_NUMBER, RANK, LAG, LEAD) for ranking and time-series calculations, CTAS (CREATE TABLE AS SELECT) for materializing query results as new tables, and UNLOAD for exporting Redshift data to S3.

⚠️ Exam Trap: Athena's partition projection and Glue crawlers both handle partitions, but they're different approaches. Crawlers update the Glue Catalog with discovered partitions (takes time, costs crawler DPU-hours). Partition projection skips the catalog entirely — Athena calculates partitions mathematically at query time based on rules you define. For high-partition-count time-series data, partition projection is faster and cheaper.

Reflection Question: A Redshift table has a billion rows. A dashboard query runs a GROUP BY on region and product_category. What Redshift feature would you use to precompute this aggregation so the dashboard loads instantly?