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. One billing trap: only Athena (and Spectrum on a provisioned cluster) charges per terabyte scanned. Redshift Serverless can query the same S3 files through Spectrum without loading them, but it bills the RPU-hours of compute the queries use, not the bytes they read.

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.

Subtotal rows need care. In a ROLLUP or CUBE result, the subtotal row for a region carries NULL in the rolled-up product_category column, and the grand-total row carries NULL in both. If the data itself has rows with a NULL category, a real group and a subtotal look the same. The GROUPING(column) function, which both Redshift and Athena support, removes the ambiguity: it returns 1 when that column was rolled up in the current row and 0 when the value is real data, including a real NULL.

SELECT region, product_category,
       GROUPING(product_category) AS is_category_subtotal,
       SUM(sales_amount) AS total_sales
FROM sales
GROUP BY ROLLUP (region, product_category);

Use GROUPING() to label subtotal rows (for example, CASE WHEN GROUPING(product_category) = 1 THEN 'All categories' END) or to filter them out. Testing product_category IS NULL cannot tell the two cases apart.

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.

A ROWS frame counts rows, not days. The seven-row frame equals seven days only when the series has exactly one row per day. If days with no sales are simply missing, a seven-row frame reaches back further than seven calendar days, and the average is wrong. Redshift window frames are defined in rows, so the fix is in the data, not the frame: build a calendar (a date spine with one row per day), LEFT JOIN the daily totals to it, and COALESCE missing totals to 0. The COALESCE matters because AVG ignores NULLs: a day left as NULL is skipped, so the result is the average of the days that had sales, not a seven-day average.

SELECT c.cal_date,
       AVG(COALESCE(d.daily_sales, 0)) OVER (
         ORDER BY c.cal_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS rolling_7day_avg
FROM calendar c
LEFT JOIN daily_sales_summary d ON d.order_date = c.cal_date;

The same OVER mechanics power LAG and LEAD, which compare a row with its predecessor or successor (month-over-month change), and the ranking functions covered in the next section. Both Redshift and Athena support them. If a question wants per-row context, such as each day's sales next to its trailing average, a window function is the answer. If it wants collapsed summary rows, GROUP BY is.

Rank rows within a group with ROW_NUMBER, RANK or DENSE_RANK OVER (PARTITION BY … ORDER BY …). The three differ only in how they treat ties, and that difference decides which one a requirement needs.

Window functions cannot appear in WHERE. SQL evaluates WHERE before the window functions are computed, so WHERE ROW_NUMBER() OVER (…) <= 3 is an error. Compute the rank in a subquery (or CTE) and filter in the outer query. Redshift also supports QUALIFY, which filters on a window function directly. Athena does not, so in Athena use the subquery.

-- Top 3 products by revenue in each region (exactly 3 rows each)
SELECT region, product, revenue
FROM (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn
  FROM product_revenue
) ranked
WHERE rn <= 3;

-- Redshift only: the same filter with QUALIFY
SELECT region, product, revenue
FROM product_revenue
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) <= 3;

Swap ROW_NUMBER for DENSE_RANK when the requirement is every product that holds one of the three highest revenue figures, ties included.

Deduplicating to the latest record per key is the same pattern. A change feed or repeated load often leaves several versions of each customer. Number each key's versions newest first and keep the first:

SELECT *
FROM (
  SELECT c.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_updates c
) v
WHERE rn = 1;

Use ROW_NUMBER here, not RANK: if two versions share the same updated_at, RANK gives both a 1 and the duplicate survives. Add a tie-breaker column to the ORDER BY when the choice between equal timestamps must be deterministic. SELECT DISTINCT is not a substitute, because it removes only rows that are identical in every column.

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'));

UNPIVOT rotates the other way: it turns columns into rows, producing a name column (which original column a value came from) and a value column. Two details matter. Its IN list takes column names (UNPIVOT (amount FOR sales_month IN (jan_sales, feb_sales, mar_sales))), whereas PIVOT's IN list takes values of the pivot column ('Jan', 'Feb'). And UNPIVOT skips NULL inputs by default, so a month with no figure produces no row at all; add INCLUDE NULLS (UNPIVOT INCLUDE NULLS (…)) when every column must produce a row, for example to show explicit gaps.

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. A materialized view also stores the query's result, so reads are fast but the stored result must be refreshed.

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

An Athena view is a convenience, not a security boundary. A standard Athena view runs with the permissions of the person querying it, so that person needs access to the underlying tables, and with that access they can query the tables directly. Hiding a sensitive column in a view therefore does not protect it. To restrict columns or rows, use AWS Lake Formation permissions (column-level and row-level filters) on the tables themselves. (Redshift views are different: Redshift checks the view owner's privileges on the base tables, so granting SELECT on a view alone can restrict access.) The exception on the Athena side is an AWS Glue Data Catalog view (created with CREATE PROTECTED MULTI DIALECT VIEW ... SECURITY DEFINER): it runs with the definer's permissions and is shared through Lake Formation, so querying users need access to the view, not to its base tables.

Materialized views in Redshift

A Redshift materialized view physically stores its precomputed result, so dashboards that hit an expensive aggregation read stored rows instead of recomputing joins. Three behaviours are tested:

• Refresh method. Redshift chooses incremental refresh (apply only the base-table changes) when the definition uses supported constructs: SELECT, FROM, inner joins, WHERE, GROUP BY, HAVING, and aggregates such as SUM, COUNT, AVG, MIN and MAX. Outer joins, set operations (UNION, INTERSECT, EXCEPT), window functions, subqueries and DISTINCT aggregates force a full refresh that re-runs the whole query.
• Auto refresh. With AUTO REFRESH YES, Redshift refreshes the view as soon as possible after base tables change, giving priority to user workloads. The view can therefore trail its base tables briefly. When results must be current at a specific moment, run REFRESH MATERIALIZED VIEW yourself (for example as the last step of the load) or schedule it.
• Automatic query rewrite. The optimizer can answer eligible queries written against the base tables from the materialized view, so a BI tool's existing SQL speeds up without being changed.

When a dashboard repeatedly runs a slow aggregation over warehouse tables and can accept figures that trail by a short time, a materialized view with auto refresh is the standard fix.

Materialized views and Athena

Athena SQL cannot CREATE or REFRESH a materialized view. Since late 2025, however, Athena can query AWS Glue Data Catalog materialized views: views created with Apache Spark on Amazon EMR or AWS Glue, whose precomputed results are stored as Apache Iceberg tables in S3. Athena reads them with ordinary SELECT statements. Creating, modifying and refreshing them happens in Spark or through the Glue Data Catalog API, not in Athena. So "materialized view" in an Athena scenario means either reading one that a Spark job maintains, or precomputing the result yourself with CTAS (next section).

Athena offers two different ways to avoid paying for the same expensive scan repeatedly: CTAS writes a new, optimized table, and query result reuse returns a cached result for a repeated identical query. They solve different problems.

CTAS (CREATE TABLE AS SELECT) runs a query once and writes its result to S3 as a real table, typically compressed, partitioned Parquet. Every later query, whatever its text, scans the smaller precomputed table instead of the raw data. Three rules shape how it is used:

• CTAS does not overwrite. It fails if the table already exists, and the target S3 location (external_location) must be empty. A nightly rebuild therefore drops the table and clears its S3 prefix before re-running the CTAS, or writes each run to a new dated location and repoints a view at the newest table. For an append-only daily aggregate, create the table once with CTAS and add each day with INSERT INTO.
• At most 100 partitions per query. A CTAS or INSERT INTO can write at most 100 partitions; a query that would write more fails with HIVE_TOO_MANY_OPEN_PARTITIONS. To backfill a daily-partitioned table from years of raw data, create it with a CTAS that covers up to 100 partitions, then add the rest with a series of INSERT INTO statements, each filtered to at most 100 partitions.
• It is a snapshot. The table does not refresh itself; something (a scheduled query, Step Functions, a Glue workflow) must re-run it.

Query result reuse is a per-query setting: when the same query text runs again within a maximum age you choose (up to seven days), Athena returns the stored result instead of scanning the data again, so there is no scan charge. It helps only when the query is repeated exactly, such as a dashboard tile re-run by many users. It does nothing for different queries over the same data, and the result can be as old as the maximum age. CTAS is the lever when many different queries hit the same data; result reuse is the lever when one query repeats.

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

SQL checks in Athena (and Redshift)

Athena is the cheapest automated verifier for data in S3, running SQL checks serverlessly right after a load:

• Nulls: COUNT(*) - COUNT(column) is the null count, because COUNT(column) skips NULLs. To list the rows, filter with WHERE column IS NULL; WHERE column = NULL never matches anything, since any comparison with NULL is unknown, not true.
• Duplicate keys: GROUP BY key HAVING COUNT(*) > 1 lists every key that appears more than once.
• Reconciliation: compare row counts with the source, but don't stop there. A load that drops 500 rows and duplicates 500 others has the right total. Compare COUNT(DISTINCT key) as well, and run the duplicate-key check, so offsetting errors cannot hide each other.

In-flight validation with Lambda

Lambda validates programmatically as data arrives. An S3 event notification on object creation, scoped with a prefix filter (for example incoming/) and optionally a suffix filter (.csv), invokes a function that checks schema, required fields and formats. It writes valid records to a curated location and failing records to a quarantine location, so bad data never reaches downstream tables. This is the pattern for catching empty fields while processing rather than after.

The classic mistake is a recursive invocation: if the function writes its output (clean files or rejects) back into the bucket and prefix that triggers it, each write fires the function again, in a loop that runs up invocation and request charges. Write outputs to a different bucket, or to prefixes outside the trigger's filter. Lambda's recursive loop detection can stop some of these loops after a number of repeats, but it is a safety net, not a design.

Exploration: notebooks and Data Wrangler

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, now delivered through Amazon SageMaker Canvas, wraps that workflow in a visual interface: connect to S3, Athena, or Redshift, generate a Data Quality and Insights report (missing values, outliers, duplicate rows, target leakage), apply built-in transforms without code, and export the flow to a processing pipeline.

No-code cleaning with DataBrew

AWS Glue DataBrew covers both halves without code. A profile job measures quality; a recipe job applies a saved recipe of transformations (fill or drop missing values, deduplicate, standardize formats) to the full dataset and can run on a schedule, which makes it the no-code answer for recurring cleaning. One trap: a profile job, by default, profiles only a sample of the first 20,000 rows. To check every row, set the job's sample to the full dataset. That job-level setting is separate from the sample used in the interactive project view and has nothing to do with the number of nodes the job uses. Rulesets and sampling strategy are covered in the data-quality lesson for Task 3.4.

Detect-only versus detect-and-fix

Several tools show problems without fixing them, a distinction questions about "no-code cleaning" rely on:

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 in a way summary statistics can miss, and its datasets support light preparation (calculated fields, filters, excluded values) on the way in.

The distinction to hold onto: automated, repeatable checks belong in Lambda, scheduled Athena queries, or DataBrew and Glue Data Quality rules; exploratory diagnosis belongs in notebooks and Data Wrangler; human visual verification belongs in QuickSight. Validating every incoming file automatically is not a notebook job.

Amazon QuickSight is the AWS business-intelligence service. Since October 2025 it is branded Amazon Quick Sight, part of Amazon Quick Suite; the exam guide still uses the name QuickSight, and the service is the same. It provides 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.

How fresh can SPICE be? Refresh schedules have floors, and they decide whether SPICE can meet a freshness requirement:

• Full refresh: daily, weekly or monthly in Standard and Enterprise editions; hourly is Enterprise edition only.
• Incremental refresh (Enterprise edition, SQL sources such as Redshift and Athena): re-queries only a look-back window based on a date column, and can run as often as every 15 minutes, the most frequent schedule SPICE offers.
• Timing: scheduled ingestions start within about 10 minutes of the scheduled time, so a schedule cannot promise to run at an exact minute.
• Event-driven refresh: to refresh right after a pipeline finishes, call the CreateIngestion API as the pipeline's last step (from Lambda or Step Functions) instead of guessing a schedule. Dashboards never read a half-loaded dataset, and the source is queried once per load.

A requirement fresher than every 15 minutes, or truly live, points to direct query.

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: you get an interactive PySpark session in seconds without provisioning a cluster, and explore data in S3 with DataFrame code where SQL alone falls short, such as complex sampling, statistical exploration and iterative cleaning experiments. Sessions bill for the compute the notebook uses.

Two setup facts are tested:

• Notebooks need a Spark workgroup. Athena workgroups have an analytics engine. A notebook can run only in a workgroup whose engine is Apache Spark; a SQL workgroup, including the default primary workgroup, cannot host notebooks. Create a Spark-enabled workgroup first.
• Two release versions, two entry points. The in-console, Jupyter-compatible notebook experience belongs to the PySpark engine version 3 release. The newer Apache Spark 3.5 release is used from Amazon SageMaker Unified Studio notebooks or from Spark Connect compatible clients, not from the Athena console notebook editor.

A scenario asking for the fastest way to explore lake data interactively with Spark and no infrastructure points to Athena notebooks. A scenario about authoring and testing ETL code that will run as a Glue job points to Glue interactive sessions.

Provisioned services allocate fixed capacity you manage and pay for while idle; serverless services allocate capacity per use and bill only for what runs. Skill 3.2.5 asks you to describe that trade-off. Provisioned services (a Redshift provisioned cluster, EMR on EC2) give predictable performance, deep tuning control (instance types, distribution keys, Spark configuration) and the best economics at sustained high utilization, reserved pricing included. In exchange you pay while idle, plan capacity, and own the scaling response. Serverless services (Athena, Redshift Serverless, EMR Serverless, Glue, Lambda, QuickSight) have zero idle cost, no capacity management, and fit spiky or unpredictable workloads. In exchange costs track usage (a runaway query bills accordingly), the tuning surface is smaller, and warm-up latency is possible. The rule of thumb: steady, heavy, predictable workloads justify provisioned; intermittent, spiky or exploratory workloads favor serverless.

Three cost details separate near-identical choices:

• Sizing a provisioned cluster for peak pays for peak capacity every hour. Concurrency scaling handles read bursts; elastic resize changes the node count in minutes; but in every case the base nodes bill while idle, unless the cluster is paused.
• Redshift Serverless bills RPU-hours whenever queries run, including Spectrum queries over S3, which it does not bill per terabyte scanned. A 24/7 workload is always running, so it pays for compute around the clock. Serverless Reservations (one-year RPU commitments) lower that rate, but for a steady 24/7 baseline, reserved provisioned nodes are usually still the cheaper option.
• Athena bills per terabyte scanned, so its cost depends on data layout (partitions, Parquet, compression) rather than on time.

Scenario

An analytics team starts with one analyst querying clickstream Parquet in S3 a few times a week, so 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 workload is constant, concurrent and latency-sensitive, which justifies loading hot data into Amazon Redshift (materialized views feeding QuickSight SPICE), while raw history stays in S3 for Athena and Spectrum. The workload changed category; neither service is simply better.