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; a filter on a non-partition column skips no partitions, so every file in the matching partitions must still be opened and evaluated — Parquet min/max statistics can skip some row groups, but only partition filters skip whole files), 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. In Athena, CTAS takes its storage settings in a WITH (...) clause, for example CREATE TABLE sales_curated WITH (format = 'PARQUET', partitioned_by = ARRAY['sale_date']) AS SELECT order_id, total, sale_date FROM sales_raw, and partition columns must come last in the SELECT list.
Window functions — func() OVER (PARTITION BY … ORDER BY …) computes a value for every row without collapsing rows (GROUP BY collapses them), so no self-join is needed. Omit PARTITION BY and the calculation runs across the whole result set; ORDER BY salary DESC makes the highest value rank 1.
| Function | Returns | Notes |
|---|---|---|
ROW_NUMBER() | Unique sequence per partition | Tied rows get different numbers |
RANK() | Rank with gaps | Ties share a rank; the next rank skips (1, 2, 2, 4) |
DENSE_RANK() | Rank without gaps | Ties share a rank; the next rank is consecutive (1, 2, 2, 3) |
PERCENT_RANK() | Relative rank from 0 to 1 | Not an integer ranking |
LAG(col, n) / LEAD(col, n) | Value from n rows before / after | Daily change: sales - LAG(sales, 1) OVER (ORDER BY date) |
SUM(col) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) | Running total | AVG(col) OVER (ORDER BY date ROWS 6 PRECEDING) = 7-row moving average |
Reusing query logic in Athena. Athena has no stored procedures. A CTE names a subquery inside one statement, a view saves the query definition (re-executed every time it is queried), and CTAS persists results to S3 as a new table — cheap to query repeatedly, but a point-in-time snapshot until you recreate it. Redshift materialized views, by contrast, can refresh incrementally and automatically (AUTO REFRESH YES).
Moving data in and out of Redshift. COPY events FROM 's3://…' IAM_ROLE '…' FORMAT AS PARQUET bulk-loads S3 files in parallel; UNLOAD ('SELECT …') TO 's3://…' IAM_ROLE '…' FORMAT AS PARQUET writes a query's results to S3 in parallel. Both require an authorization clause (IAM_ROLE with a role ARN, default or SESSION); without one the command fails. LOAD DATA INPATH and EXPORT TABLE are Hive syntax, not Redshift.
Idempotent loads. Redshift does not enforce PRIMARY KEY or UNIQUE constraints (they are planner hints only), so re-running a load after a failure that left some rows committed silently duplicates them. COPY into a staging table, then MERGE into the target on the business key (or delete matching keys and insert in one transaction), so a re-run produces the same result.
⚠️ 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?
