Domain 3 of 4 · Chapter 2 of 4

Data Analysis

Athena or Redshift: query in place, or load and query repeatedly

A team has six months of clickstream files sitting in S3 and one question to answer this afternoon. Pointing Amazon Athena[1] at the bucket and writing standard SQL answers it in minutes, because Athena is an interactive, serverless query service that reads data directly in S3 without any step to aggregate or load it first. Its engine is Trino/Presto, it scales automatically, and you pay only for the queries you run. That is the shape of an analysis question on this exam: when the data already lives in S3 and the query is ad hoc or infrequent, the answer leans Athena.

The contrast is a data warehouse. AWS positions Amazon Redshift[2] as the best choice when you pull data together from many sources into a common, highly structured model and run sophisticated reports, and its query engine is optimized for complex queries that join large numbers of very large tables. The deciding factor is not how big the data is, it is the workload pattern: a query-in-place service for occasional exploration, a loaded warehouse for the same heavy joins running again and again.

The one axis that decides it

Read the stem for two signals. First, where does the data live and is there a load step: "data in S3", "data lake", "no ETL", or "ad hoc / one-off / explore" point to Athena. Second, how does the work repeat: "complex joins across many large tables", "sophisticated business reports", "highly structured", or "runs continuously" point to Redshift. Athena and Redshift can both reach S3 (Redshift Spectrum lets a warehouse query lake data), so a question that mixes signals is usually testing which service you would make the center of gravity, not which one is technically capable.

Data in S3, queryad hoc or infrequent?YesNoAmazon AthenaServerless SQL, $5/TB scannedLoad steady, orspiky/unpredictable?SpikySteadyRedshift ServerlessScales in RPUs, pay when in useProvisioned RedshiftSized cluster, reserve itDecide by workload pattern, not by data size
Athena vs Redshift Serverless vs provisioned Redshift, modeled on the AWS "When should I use Athena?" guidance.

Athena cost levers: bytes scanned, CTAS, federation, and Spark

Athena charges $5 per TB of data scanned[3] per query, so the entire cost model reduces to one rule: pay for bytes read, then read fewer bytes. Nothing else (rows returned, query duration) moves the bill.

Cut bytes scanned at the storage layer

Three levers, applied together, shrink the bytes a query touches. Convert row-based CSV or JSON to columnar Parquet or ORC[4] so Athena reads only the columns the query names (column pruning) and skips files using min/max statistics. Partition the data, for example by date, so a WHERE clause on the partition key prunes whole S3 prefixes the query never opens. Compress the columnar files (Snappy, ZSTD) so each remaining file is smaller still. A query that scanned a year of raw JSON can end up scanning a few partitions of compressed Parquet, and the bill drops in proportion.

Materialize once with CTAS, then query the small copy

A CREATE TABLE AS SELECT (CTAS)[4] query writes the result of a SELECT to a brand-new table in S3 in one step, and as it writes it can convert the output to Parquet or ORC and partition or bucket it. The pattern is serverless ELT inside Athena: instead of rescanning raw data on every query, run CTAS once to produce a clean, columnar, partitioned dataset, then point all later queries at that smaller table for far fewer bytes scanned. INSERT INTO appends additional results to a CTAS-created table for incremental builds.

CREATE TABLE analytics.sales_parquet
WITH (format = 'PARQUET', partitioned_by = ARRAY['sale_date'])
AS SELECT region, amount, sale_date FROM raw.sales_csv;

Reach beyond S3, and run Spark when SQL is not enough

When the data you need is not in S3, Athena Federated Query[5] uses a data source connector that runs as an AWS Lambda function to query other sources (such as DynamoDB, RDS and other JDBC databases, or CloudWatch Logs) from the same SQL, joining them with S3 data. Federated and other non-S3 connectors carry a 10 MB minimum data-scanned charge per query. When a problem is iterative or needs full Spark rather than SQL, Athena Spark notebooks[1] run Apache Spark with Python in a simplified notebook, serverlessly, billed at $0.35 per DPU-hour[3] for driver and worker compute, with no cluster to size.

Raw CSV / JSONscans everythingColumnar Parquetcolumn pruningPartition by dateprune prefixesCompresssmaller filesFewer bytes scanned = lower $5/TB cost
Each storage-layer step cuts the bytes a query scans, and Athena bills per TB scanned.

QuickSight, SPICE, and the SQL analytics building blocks

Once data is queryable, two jobs remain: present it, and compute the summaries that drive the presentation. Presenting it is Amazon QuickSight[6], the managed BI service for dashboards and visualizations (now delivered as Amazon Quick Sight within Amazon Quick; existing QuickSight APIs keep working unchanged). When a question is about charts, dashboards, or business reports rather than running SQL, the answer is QuickSight.

Direct query vs SPICE

QuickSight reads a dataset one of two ways, and the choice is a speed-and-cost trade. A direct query runs SQL against the source every time someone views the dashboard, so the data is always live but each view hits (and on a per-query source like Athena, pays for) the source. Importing into SPICE (Super-fast, Parallel, In-memory Calculation Engine)[7], QuickSight's in-memory engine, copies the data into memory so dashboards respond fast and the source is queried only when SPICE refreshes, not on every view. Reach for SPICE for fast, frequently-viewed dashboards on data that does not need to be second-fresh; use direct query when viewers must always see live source data. QuickSight also adds ML Insights such as anomaly detection and forecasting on top of either mode.

Aggregation, grouping, rolling averages, and pivoting

The analytics themselves are SQL, and the blueprint names the specific operations. Aggregation collapses many rows into one summary value with functions like SUM, COUNT, and AVG. GROUP BY runs that aggregation per group, for example total sales per region. A rolling (moving) average smooths a series over a sliding span and is computed with a window function, AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), which, unlike GROUP BY, keeps every row while adding the windowed value. Pivoting turns row values into columns (long to wide) to make a cross-tab; in QuickSight a pivot table does this visually, and in SQL you express it with conditional aggregation. These run identically in Athena and Redshift, because both speak standard SQL.

SELECT region,
       SUM(amount) AS total,                 -- aggregation
       AVG(amount) OVER (PARTITION BY region
         ORDER BY sale_date
         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM sales
GROUP BY region, sale_date, amount;
Source (Athena, RDS)Direct queryImportSQL on every viewAlways live, hits sourceSPICE in-memoryFast, refresh on scheduleQuickSight dashboard
QuickSight reads a dataset via direct query (live) or SPICE (in-memory, refreshed on a schedule).

Verifying and cleaning data, and reading the analysis question

Before analysis you often verify and clean the data, and the exam tests which tool fits which audience. Match the tool to who does the work and what the output is.

The verify-and-clean tool tier

For SQL-shaped checks and fixes, query and clean in Athena or Redshift SQL, or use a small AWS Lambda for event-driven, per-file cleanup. For visual, no-code preparation, AWS Glue DataBrew[8] lets analysts clean and normalize data without writing code, offering over 250 ready-made transformations and reducing prep time by up to 80% versus custom code; its steps are saved as a reusable recipe and output is written to S3. For preparing and featurizing data specifically for machine learning, Amazon SageMaker Data Wrangler[9] provides a visual data-flow with built-in transforms and quality insights, importing from S3, Athena, Redshift, and more (it is now part of SageMaker Canvas). Jupyter notebooks, including Athena Spark notebooks, cover code-first exploratory analysis. The dividing lines: DataBrew for no-code analyst cleanup, Data Wrangler for ML feature prep, SQL/Lambda for programmatic fixes.

Provisioned vs serverless, stated as a trade

The blueprint asks you to describe the tradeoff between provisioned and serverless. Provisioned services (a sized Redshift cluster, an EMR cluster) give predictable, reservable capacity at the lowest rate per unit of work for steady, always-on load, at the cost of managing and right-sizing them. Serverless services (Athena, Redshift Serverless) remove capacity management and bill by use ($5/TB scanned, or RPU-seconds), which wins for spiky, unpredictable, or intermittent load but can cost more per unit on a flat-out, always-busy workload. The rule: steady and continuous favors provisioned-and-reserved; variable or occasional favors serverless.

Exam-pattern recognition

Stems map cleanly to answers once you spot the signal. "Ad hoc SQL on data in S3, no ETL" is Athena. "Reduce Athena cost" is convert to Parquet/ORC, partition, and compress (read fewer bytes), often via a CTAS. "Complex joins across many large tables, repeatedly" is Redshift. "Variable warehouse load, do not manage a cluster" is Redshift Serverless. "Build a dashboard / visualize" is QuickSight, and "make the dashboard fast" is import into SPICE. "Clean data without code" is DataBrew; "prepare features for ML" is SageMaker Data Wrangler; "query DynamoDB or an RDS table from Athena SQL" is Athena Federated Query via a Lambda connector.

Who cleans, for what?Code / SQLNo-code analystML featuresAthena/Redshift SQLor Lambda per fileGlue DataBrew250+ transforms, recipesData Wranglervisual ML feature prepThen visualize in QuickSight (SPICE for speed)
Choosing a verify-and-clean tool by audience and output, then visualizing in QuickSight.

Athena vs Redshift Serverless vs provisioned Redshift for SQL analysis

Decision axisAmazon AthenaRedshift ServerlessProvisioned Redshift
What it isServerless query of S3 in place (Trino/Presto SQL)Auto-scaling serverless data warehouseSized data-warehouse cluster you manage
Data locationStays in S3, no load stepLoaded into managed storage (can query S3)Loaded into the cluster (can query S3)
Capacity modelFully serverless, none to manageAuto-provisions/scales in RPUsYou size and tune the cluster
Billing$5 per TB scanned per queryRPU-seconds, pay only when in usePer cluster-hour; reserve for the lowest rate
Best fitAd hoc, infrequent queries on lake dataSpiky or unpredictable warehouse loadSteady, always-on complex joins at scale
Main cost leverBytes scanned: columnar + partition + CTASBase RPU capacity and active durationRight-sizing and reservation commitment

Decision tree

Visualize, or query?DashboardQueryAmazon QuickSightSPICE for fast dashboardsData in S3, ad hocor infrequent?YesNoAmazon AthenaServerless SQL, $5/TB scannedWarehouse load:steady or spiky?SpikySteadyRedshift ServerlessRPUs, pay when in useProvisionedRedshiftReduce Athena cost: Parquet, partition, compress, CTAS
Cheat sheet

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Reach for Athena to run ad hoc SQL on data already in S3

Amazon Athena is an interactive, serverless query service that runs standard SQL (its engine is Trino/Presto) directly against data in S3, with no servers to manage and no step to aggregate or load the data first. That makes it the right call for one-off exploration, querying data-lake files, and troubleshooting logs. Reach for a data warehouse instead only when the same heavy analytics repeat on loaded, structured data.

Trap Loading the S3 data into a Redshift cluster first just to run a single exploratory query, which adds an ETL step Athena is designed to skip.

Choose Redshift when complex joins across large tables repeat

Amazon Redshift is a data warehouse, and AWS positions it as the best choice when you pull data from many sources into one structured model and run sophisticated reports whose queries join large numbers of very large tables. Its engine is optimized for exactly those repeated complex joins, which is where it beats querying raw files in place. The deciding signal is the recurring, highly-structured workload, not the raw size of the data.

Trap Picking Redshift because the dataset is large; size alone does not decide it, an infrequent ad hoc query on S3 still favors Athena.

Athena bills per terabyte scanned, so read fewer bytes

Athena charges $5 per TB of data scanned per query, so cost tracks bytes read, not rows returned or query duration. Every cost-optimization answer for Athena reduces to reading fewer bytes. That is why storage format and layout, not query tuning alone, are the levers the exam tests.

Trap Assuming faster or shorter queries cost less; a quick query that still scans a full table is billed for the full scan.

Convert to columnar Parquet or ORC to cut Athena scan cost

Storing data as columnar Parquet or ORC lets Athena read only the columns a query names (column pruning) and skip files using min/max statistics, instead of scanning entire row-based CSV or JSON files. Because Athena prices per byte scanned, this directly shrinks the bill on selective queries. Pair it with compression so each remaining file is smaller still.

Trap Leaving data as CSV/JSON and trying to save money by selecting fewer columns; a row format still reads every column off disk regardless of the SELECT list.

Partition Athena data so the engine prunes whole S3 prefixes

Partitioning data on a column you filter by, for example date, organizes it into separate S3 prefixes so a WHERE clause on the partition key skips every prefix the query does not touch (partition pruning). Combined with a columnar format this can turn a year-long scan into a few partitions read. The partition key should match how queries filter, or the pruning never kicks in.

Trap Partitioning on a high-cardinality column queries rarely filter on, which creates many tiny partitions and adds overhead without reducing bytes scanned.

Materialize a clean copy once with CTAS instead of rescanning raw data

A CREATE TABLE AS SELECT (CTAS) query writes the result of a SELECT to a new table in S3 in one step and can convert the output to Parquet or ORC and partition or bucket it as it writes. Run it once to produce a compact, columnar, partitioned dataset, then point all later queries at that smaller table for far fewer bytes scanned. INSERT INTO appends more results to a CTAS-created table for incremental, serverless ELT inside Athena.

Use Athena Federated Query to reach sources other than S3

Athena Federated Query uses a data source connector that runs as an AWS Lambda function to query non-S3 sources such as DynamoDB, RDS and other JDBC databases, or CloudWatch Logs, and join them with S3 data in one SQL statement. It extends Athena's reach without copying the source data into S3 first. Federated and other non-S3 connectors carry a 10 MB minimum data-scanned charge per query.

Trap Building a separate ETL job to copy DynamoDB into S3 just so Athena can read it, when a federated connector queries DynamoDB in place.

Run Athena Spark notebooks when SQL is not enough

Athena offers a simplified notebook experience that runs Apache Spark with Python serverlessly, so you can explore data interactively without sizing or managing a cluster. It is billed at $0.35 per DPU-hour for driver and worker compute. Reach for it when analysis is iterative or needs full Spark rather than the SQL that standard Athena queries provide.

Pick serverless for spiky load and provisioned-reserved for steady load

Athena, Redshift Serverless, and provisioned Redshift run the same SQL; the difference is capacity management and billing. Serverless services (Athena per TB scanned, Redshift Serverless per RPU-second) remove cluster management and charge by use, which wins for variable, unpredictable, or intermittent workloads. A provisioned, reserved cluster gives the lowest rate per unit of work for steady, always-on load at the cost of sizing and managing it.

Trap Defaulting to a provisioned cluster for a bursty, occasionally-used workload, where it sits idle and costs more than a serverless option that bills only when in use.

Use Redshift Serverless to avoid managing a warehouse for variable load

Redshift Serverless automatically provisions data-warehouse capacity and scales it in seconds, measured in Redshift Processing Units (RPUs), and you pay only when the warehouse is in use. It gives the same Redshift SQL and performance without setting up, tuning, or managing a cluster, which fits unpredictable or volatile workloads. Choose provisioned Redshift instead when the load is steady enough to size and reserve a cluster for a lower steady-state rate.

Use QuickSight to build dashboards and visualizations

Amazon QuickSight is the managed business intelligence service for building dashboards, charts, and business reports on top of analyzed data, and it also offers ML Insights such as anomaly detection and forecasting. When a question is about visualizing results or building a dashboard rather than running SQL, the answer is QuickSight, not a query engine. It is now delivered as Amazon Quick Sight within Amazon Quick, with existing QuickSight APIs unchanged.

Trap Answering a visualization or dashboard question with Athena or Redshift, which run the SQL behind a dashboard but are not the BI presentation layer.

Import into SPICE for fast dashboards; use direct query for live data

QuickSight reads a dataset either by direct query, running SQL against the source on every view, or by importing it into SPICE (Super-fast, Parallel, In-memory Calculation Engine), its in-memory engine. SPICE makes dashboards respond fast and hits the source only on refresh rather than on every view, so it also limits cost against per-query sources like Athena. Use direct query when viewers must always see live source data; use SPICE for fast, frequently-viewed dashboards that tolerate scheduled freshness.

Trap Using direct query against Athena for a heavily-viewed dashboard, so every view re-scans S3 and re-incurs the $5/TB charge that a SPICE import would pay only on refresh.

Use GROUP BY to aggregate per group, not over the whole table

Aggregation collapses many rows into one value with functions like SUM, COUNT, and AVG, and GROUP BY runs that aggregation once per group, for example total sales per region. Every non-aggregated column in the SELECT must appear in the GROUP BY. This is the building block behind most summary reports, and it runs identically in Athena and Redshift because both speak standard SQL.

Compute a rolling average with a window function, not GROUP BY

A rolling (moving) average smooths a series over a sliding span and is expressed with a window function, such as AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). Unlike GROUP BY, a window function keeps every row and adds the windowed value alongside it, which is exactly what a per-row trailing average needs. Aggregating with GROUP BY instead would collapse the rows and lose the per-day series.

Trap Reaching for GROUP BY to compute a rolling average; it collapses rows into one value per group, so the per-row moving series disappears.

Pivoting turns row values into columns for a cross-tab

Pivoting reshapes data from long to wide, turning distinct row values into separate columns to make a cross-tabulation, for example one column per month. In QuickSight a pivot table does this visually, and in SQL you express it with conditional aggregation (SUM with a CASE per target column). It is a presentation reshape, not a new computation, so the underlying numbers come from the same aggregation.

Use SageMaker Data Wrangler to prepare and featurize data for ML

Amazon SageMaker Data Wrangler provides a visual data-flow with built-in transforms and data-quality insights to import, prepare, transform, and featurize data specifically for machine learning, importing from S3, Athena, Redshift, and other sources with little to no code. It is the ML-oriented prep tool and is now part of SageMaker Canvas. Choose it when the goal is feature engineering for a model, not general analyst cleanup.

EMR managed scaling plus Spark dynamic allocation auto-sizes a cluster with no custom rules

EMR managed scaling resizes a cluster between minimum and maximum capacity units by sampling YARN workload metrics, requiring no hand-written scaling policies or workload prediction. Keeping Spark dynamic resource allocation (the default) enabled lets executors scale with the managed cluster; pairing Spot task nodes with On-Demand core nodes adds cost savings while protecting HDFS.

Trap Putting Spot Instances on core nodes; an interruption there loses HDFS data, so reserve Spot for task nodes.

5 questions test this
Container killed by YARN for exceeding memory limits points to executor memory overhead

The off-heap memory holding Java NIO buffers, thread stacks, and native libraries is spark.executor.memoryOverhead, which defaults to about 10% of executor memory (minimum 384 MB) and is heavily used during shuffles. When containers are killed for exceeding YARN memory, raising memoryOverhead is the first fix, not raising spark.executor.memory.

Trap Only increasing spark.executor.memory; that grows the heap but leaves off-heap shuffle memory short, so the kills continue.

4 questions test this
maximizeResourceAllocation lets EMR auto-size Spark executors to the node hardware

Setting maximizeResourceAllocation to true in the spark configuration classification makes EMR compute the maximum compute and memory for one executor on a core-group instance and set spark-defaults accordingly. It is the low-effort choice for a Spark-only cluster on uniform instance groups.

Trap Using it with instance fleets of mixed sizes; it sizes from a single instance type, so set spark.executor settings manually for heterogeneous fleets.

4 questions test this
Author row-level calculated fields in data prep so SPICE materializes them

A calculated field built in the dataset data-preparation phase that is row-level (no aggregation or parameters) is computed once and stored in SPICE during ingestion, removing the per-render cost. Move such ifelse or string-manipulation fields out of the analysis to speed up slow visuals on large datasets.

Trap Putting an aggregate- or parameter-based field in data prep expecting materialization; only non-aggregate row-level calculations are materialized, so leave those in the analysis.

5 questions test this
Set the external table's numRows so Redshift Spectrum plans the join correctly

Redshift does not analyze external tables, so without statistics it assumes an external table is larger than any local table and picks poor join strategies. Use ALTER TABLE to set the TABLE PROPERTIES numRows to the real row count so the optimizer can compare sizes and avoid broadcasting the wrong side.

Trap Running ANALYZE on the external table; Redshift cannot gather its statistics, so numRows must be set by hand.

4 questions test this
Merge tiny S3 files past 64 MB and use Parquet to speed up Spectrum

Many tiny files hurt Redshift Spectrum because each query then scans far more, smaller objects with little parallelism benefit; AWS advises keeping file sizes larger than 64 MB and avoiding data-size skew by keeping files about the same size, so a query can be processed in parallel across evenly sized files. Converting to columnar Parquet lets Spectrum prune unneeded columns from the scan (column pruning) and apply predicate pushdown, and partitioning on a commonly filtered column enables partition pruning to limit data scanned.

Trap Only converting format while leaving thousands of tiny, skewed files; AWS still calls for files larger than 64 MB of roughly equal size, so query parallelism stays poor until the small files are merged.

4 questions test this

References

  1. What is Amazon Athena?
  2. When should I use Athena?
  3. Amazon Athena pricing
  4. Create a table from query results (CTAS)
  5. Create a data source connection (Athena federated query)
  6. What is Amazon QuickSight?
  7. Importing data into SPICE
  8. What is AWS Glue DataBrew?
  9. Prepare ML Data with Amazon SageMaker Data Wrangler