Analyzing Data on AWS: Athena, Redshift SQL, QuickSight & Spark Notebooks

Choose Amazon Athena when you want to query data where it already sits in S3, occasionally and without infrastructure; choose Amazon Redshift when queries are frequent, latency-sensitive, highly concurrent, or join large tables repeatedly — the warehouse's optimized storage, distribution, and caching earn their cost through repetition.

The cost models drive most exam answers. Athena charges per terabyte of data scanned, so its economics reward columnar formats, compression, and partition pruning — and punish daily heavy scans, which at some frequency become more expensive than a warehouse. Redshift charges for provisioned capacity (or RPU-based usage in Redshift Serverless), so its economics reward sustained, repeated workloads and punish a cluster that sits idle. The two are complementary, not rivals: the standard architecture keeps raw and infrequently queried data in S3 for Athena, loads hot, frequently joined data into Redshift, and bridges them with Redshift Spectrum.

Data aggregation means collapsing many rows into summary values — SUM, COUNT, AVG, MIN, MAX — and grouping defines the buckets those summaries are computed over. GROUP BY produces one output row per distinct combination of the grouping columns; every selected column must either be in the GROUP BY list or be wrapped in an aggregate function.

SELECT region,
       product_category,
       SUM(sales_amount)  AS total_sales,
       COUNT(*)           AS order_count
FROM sales
GROUP BY region, product_category
HAVING SUM(sales_amount) > 100000
ORDER BY total_sales DESC;

The WHERE versus HAVING distinction is a reliable exam point: WHERE filters rows before they are grouped, while HAVING filters groups after aggregation — a condition on SUM(sales_amount) can only live in HAVING, because the sum does not exist until grouping happens.

Both Redshift and Athena support grouping extensions that compute several levels of summary in one query: GROUPING SETS names the exact combinations you want, ROLLUP (region, category) produces progressively coarser subtotals (region and category, region alone, grand total), and CUBE produces every combination of the listed columns. When a question asks for subtotals and a grand total in a single query without UNIONing multiple SELECTs together, these extensions are the answer.

A rolling (moving) average smooths a time series by averaging each row with its neighbors — the seven-day average of daily sales, for example — and in SQL it is built with a window function: an aggregate computed OVER a window of related rows that, unlike GROUP BY, keeps every input row in the output.

SELECT order_date,
       daily_sales,
       AVG(daily_sales) OVER (
         ORDER BY order_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7day_avg
FROM daily_sales_summary;

Read the clause inside OVER in three parts. PARTITION BY (optional) restarts the window per group — add PARTITION BY store_id and each store gets its own independent rolling average. ORDER BY sequences the rows within each partition. The frame — ROWS BETWEEN 6 PRECEDING AND CURRENT ROW — defines which neighbors participate: the current row plus the six before it, which is exactly seven rows for a seven-day average. Omitting the frame while using ORDER BY gives you a cumulative (running) aggregate from the partition start to the current row — a running total rather than a rolling window, and mixing the two up is a designed-in distractor.

The same OVER mechanics power the other analytic functions the exam draws on: ROW_NUMBER, RANK, and DENSE_RANK for ordering and top-N-per-group questions (rank products by revenue within each region), and LAG and LEAD for comparing a row with its predecessor or successor (month-over-month change). Both Redshift and Athena support these; if a question wants per-row context — each day's sales next to its trailing average, each month next to the previous month — a window function is the answer, and if it wants collapsed summary rows, GROUP BY is.

Pivoting rotates data from tall to wide — distinct values of one column become columns of their own, with an aggregate filling the cells: monthly sales stored as one row per (product, month) becomes one row per product with a column per month. Unpivoting is the reverse, melting wide columns back into rows.

Amazon Redshift supports pivoting natively with PIVOT and UNPIVOT in the FROM clause:

SELECT *
FROM (SELECT product, sales_month, amount FROM monthly_sales)
PIVOT (SUM(amount) FOR sales_month IN ('Jan', 'Feb', 'Mar'));

Athena has no PIVOT keyword, so you pivot with conditional aggregation — CASE expressions inside aggregates — which also works in Redshift and is the portable pattern worth memorizing:

SELECT product,
       SUM(CASE WHEN sales_month = 'Jan' THEN amount ELSE 0 END) AS jan_sales,
       SUM(CASE WHEN sales_month = 'Feb' THEN amount ELSE 0 END) AS feb_sales,
       SUM(CASE WHEN sales_month = 'Mar' THEN amount ELSE 0 END) AS mar_sales
FROM monthly_sales
GROUP BY product;

Two practical notes. SQL pivoting requires the output columns to be known when the query is written — a dynamic set of pivot values is a signal to pivot in the presentation layer instead, and Amazon QuickSight's pivot table visual does exactly that, letting users rotate dimensions interactively without any SQL. And when the exam simply asks what pivoting is (Skill 3.2.6 definitions), the answer is the row-to-column rotation itself: restructuring data so that values become column headers, typically to compare categories side by side.

A view is a saved query that behaves like a table: it encapsulates cleaning logic, joins, and column selection so analysts query a stable, tidy interface instead of raw tables — and views are also an access-control tool, exposing only approved columns and rows of sensitive tables.

Both Athena and Redshift support standard (logical) views, which store no data and re-run their defining query each time they are referenced. In Athena, views are defined with CREATE VIEW and stored as metadata in the Glue Data Catalog — useful for presenting cleaned, renamed, filtered versions of lake tables to downstream users at no storage cost. Redshift additionally offers late-binding views (created WITH NO SCHEMA BINDING), which do not check underlying tables until query time — handy when ETL drops and recreates tables, and required for views over external Spectrum tables.

Materialized views exist in Redshift, not Athena. A Redshift materialized view physically stores its precomputed result, so dashboards hitting an expensive aggregation read stored rows instead of recomputing joins — with auto refresh keeping the result current as base tables change and automatic query rewrite letting the optimizer transparently answer eligible queries from the materialized view even when the query references the base tables. When an exam scenario says a dashboard repeatedly runs a slow aggregation over warehouse tables and needs faster response without changing the BI tool, a materialized view with auto refresh is the intended answer. In Athena, the closest equivalent is CTAS — CREATE TABLE AS SELECT materializes results into a real S3 table (compressed, partitioned Parquet) — but it is a snapshot you must re-create on a schedule, not a self-refreshing view.

Verifying data means proving it matches expectations — row counts, no unexpected nulls, no duplicates, values in range — and the task statement names five tools for it: Lambda, Athena, QuickSight, Jupyter notebooks, and SageMaker Data Wrangler. Each fits a different point in the pipeline.

Athena is the cheapest automated verifier for data in S3: SQL checks such as COUNT(*) compared against the source, COUNT(*) - COUNT(column) to expose nulls, and GROUP BY key HAVING COUNT(*) > 1 to expose duplicates run serverlessly right after a load. Lambda does programmatic, in-flight validation: a function triggered as files land checks schema, required fields, and formats, routing bad records to a quarantine prefix before they contaminate downstream tables — the pattern for catching empty fields while processing rather than after.

Jupyter notebooks (in SageMaker or Athena's Spark notebooks) are for interactive investigation: load a sample, compute distributions, chart outliers, and prototype the cleaning logic that later graduates into a Glue job. SageMaker Data Wrangler wraps that workflow in a visual interface: connect to S3, Athena, or Redshift, get automatic data-quality insights (missing values, outliers, duplicate rows), apply built-in transforms without code, and export the resulting flow to a processing pipeline. QuickSight verifies visually at the end of the line — a bar that dwarfs its neighbors or a gap in a time series makes bad data obvious to human eyes in a way summary statistics can miss — and its datasets support light preparation (calculated fields, filters, excluded values) on the way in.

The exam distinction to hold onto: automated, repeatable checks belong in Lambda or scheduled Athena queries (and DataBrew data-quality rules); exploratory diagnosis belongs in notebooks and Data Wrangler; human visual verification belongs in QuickSight. A question asking how to validate every incoming file automatically is not answered with a notebook.

Amazon QuickSight is the AWS business-intelligence service: serverless, browser-based dashboards over data from Athena, Redshift, S3, RDS, and many other sources. Its defining design choice is SPICE, the in-memory calculation engine — a dataset imported into SPICE serves dashboards from memory, fast and without re-hitting the source per viewer, refreshed on a schedule; direct query mode instead runs against the source live, trading latency and source load for up-to-the-moment freshness. The exam angle: many concurrent dashboard viewers over data that updates periodically points to SPICE; a requirement for real-time freshness points to direct query (and a source that can take the load). QuickSight rounds out with pivot table visuals, ML-powered insights such as anomaly detection and forecasting, natural-language querying, and embedded dashboards for applications — and per-session pricing for readers keeps wide distribution economical.

AWS Glue DataBrew visualizes to profile, not to report. A DataBrew profile job renders a dataset's shape — completeness per column, value distributions, correlations, duplicates — as visual statistics aimed at the data engineer deciding what cleaning a dataset needs. That is the distinction Task 3.2 wants: DataBrew's visuals characterize data quality during preparation; QuickSight's dashboards communicate business meaning to stakeholders. If the question's audience is executives tracking KPIs, the answer is QuickSight; if it is an engineer assessing a new dataset's null rates and outliers before transformation, DataBrew's profile view is the fit.

One integration note worth remembering: QuickSight commonly sits on Athena for lake data — which means the cost and performance disciplines from earlier (partitioning, columnar formats) directly shape dashboard speed and spend, and SPICE import is the standard way to stop a popular dashboard from re-scanning S3 all day.

Athena notebooks run Apache Spark serverlessly: from the Athena console you open a Jupyter-compatible notebook, get an interactive PySpark session in seconds without provisioning any cluster, and explore data in S3 with DataFrame code where SQL alone falls short — complex sampling, statistical exploration, iterative cleaning experiments. Sessions bill for the compute the notebook actually uses. Positioning against Amazon EMR is the tested contrast: Athena for Spark is for quick, interactive, no-setup exploration; EMR (on EC2 or EMR Serverless) is for production-scale Spark jobs, custom libraries and configurations, and long-running heavy processing. A scenario asking for the fastest way to interactively explore lake data with Spark and no infrastructure is Athena notebooks, not spinning up an EMR cluster.

That contrast is one instance of the general provisioned versus serverless trade-off Skill 3.2.5 asks you to describe. Provisioned services (a Redshift provisioned cluster, EMR on EC2) allocate fixed capacity you manage: you get predictable performance, deep tuning control (instance types, distribution keys, Spark configuration), and better economics at sustained high utilization — Reserved Instance pricing included — but you pay while idle, you capacity-plan, and you own the scaling response. Serverless services (Athena, Redshift Serverless, EMR Serverless, Glue, Lambda, QuickSight) allocate capacity per use: zero idle cost, no capacity management, and instant fit for spiky or unpredictable workloads — but costs track usage (a runaway query bills accordingly), tuning surface is smaller, and occasional warm-up latency exists. The exam's rule of thumb: steady, heavy, predictable workloads justify provisioned; intermittent, spiky, or exploratory workloads favor serverless.

Scenario

An analytics team starts with one analyst querying clickstream Parquet in S3 a few times a week — Athena is right: pay per query, nothing idle. Six months later, 150 users hit dashboards refreshed hourly with sub-second expectations, and finance wants predictable spend. Now the same workload profile — constant, concurrent, latency-sensitive — justifies loading hot data into Amazon Redshift (materialized views feeding QuickSight SPICE), while raw history stays in S3 for Athena and Spectrum. Recognizing that the workload changed category, not that one service is better, is precisely what Task 3.2 scores.