Create a Sales Summary with Athena

Introduction

The shop needs a monthly sales summary that can be shared as a CSV file. Use Athena to calculate the totals, retrieve the actual query output from S3, then correct a sales value and produce a new summary. Compare the two outputs to see how results relate to the input at execution time.

Complete Query Partitioned Sales Data first. This independent environment supplies two monthly S3 files, both registered Glue partitions, a configured AWS identity and a local corrected September file. You do not need a personal AWS account or earlier VM resources. Use Terminal for the official AWS CLI and AWS View to inspect actual files, catalog locations and query results.

Certification Relevance

Certification	Exam task	Practice
Cloud Practitioner (CLF-C02)	Task 3.7	Use Athena and Glue to analyze S3 data and retrieve computed results.

Calculate the Monthly Summary

In this step, calculate a summary from the current sales files. Glue records the schema and each monthly location; Athena reads the corresponding data and groups the records by month. Each query execution has its own ID and result file.

Enter the project directory and confirm your identity:

cd /home/labex/project
aws sts \
  get-caller-identity

The ARN ends in labex-an04-operator. Read this environment's resource names. jq -r extracts JSON strings; $(...) saves each result in a shell variable:

INPUT_BUCKET=$(jq -r .input_bucket resources.json)
echo "$INPUT_BUCKET"
DATABASE=$(jq -r .database resources.json)
echo "$DATABASE"
RESULT_LOCATION=$(jq -r .output_location resources.json)
echo "$RESULT_LOCATION"

Check the registered monthly locations:

aws glue \
  get-partitions \
  --database-name "$DATABASE" \
  --table-name sales \
  --query 'Partitions[*].{Month:Values[0],Location:StorageDescriptor.Location}'

Both September and October are registered. The CSV records contain cents, so summing total_cents avoids rounding currency values. COUNT(*) counts orders, GROUP BY month creates one group per month, and ORDER BY month makes the output easy to compare. A quoted heredoc writes the SQL literally to a file:

cat > monthly-summary.sql <<'EOF'
SELECT month, SUM(total_cents) AS revenue_cents, COUNT(*) AS orders
FROM sales
GROUP BY month
ORDER BY month;
EOF

Submit the query using the prepared database and S3 output prefix. file:// reads your SQL file; the CLI's --query and --output text select the execution ID:

SUMMARY_QUERY_ID=$(aws athena \
  start-query-execution \
  --query-string file://monthly-summary.sql \
  --query-execution-context "Database=$DATABASE" \
  --result-configuration "OutputLocation=$RESULT_LOCATION" \
  --query QueryExecutionId \
  --output text)
echo "$SUMMARY_QUERY_ID"

Check execution status before reading the results. Repeat this command if the state is QUEUED or RUNNING; continue only at SUCCEEDED. If it is FAILED, inspect Reason:

aws athena \
  get-query-execution \
  --query-execution-id "$SUMMARY_QUERY_ID" \
  --query 'QueryExecution.{State:Status.State,Reason:Status.StateChangeReason}'

Retrieve the computed rows. The nested [*] projection preserves each row's columns in the terminal table:

aws athena \
  get-query-results \
  --query-execution-id "$SUMMARY_QUERY_ID" \
  --query 'ResultSet.Rows[*].Data[*].VarCharValue' \
  --output table

September has 3500 cents and 3 orders; October has 2000 cents and 3 orders. In AWS View, expand this query's SQL and result rows to compare the calculation with the two registered monthly locations.

Download the Actual S3 Result File

In this step, retrieve the CSV produced by that execution. The Athena results API displays rows; the execution metadata also tells you where its output was stored in S3. Read that exact location instead of guessing a bucket name or query ID.

Extract and display the output URI:

SUMMARY_OUTPUT=$(aws athena \
  get-query-execution \
  --query-execution-id "$SUMMARY_QUERY_ID" \
  --query QueryExecution.ResultConfiguration.OutputLocation \
  --output text)
echo "$SUMMARY_OUTPUT"

The URI ends in /queries/<your-execution-id>.csv. The ID links this file to the query you just ran. Download it to summary.csv:

aws s3 \
  cp "$SUMMARY_OUTPUT" summary.csv

Inspect the downloaded contents:

cat summary.csv

The CSV header and two records are:

month,revenue_cents,orders
2026-09,3500,3
2026-10,2000,3

These values match the API result. This is a real S3 output file you downloaded, rather than a report you typed manually. Keep summary.csv and the original S3 output for comparison in the next step.

Recompute After Correcting the Input

In this step, correct one September amount and run the same SQL again. An existing query result records the earlier execution; it does not automatically refresh when an S3 input changes. A new execution must read the current input to produce updated totals.

Inspect the supplied corrected file:

cat september-revised.csv

Order S01 now has 1100 cents instead of 1000. The other September records are unchanged, so its expected total is 3600 cents. Upload this correction to the existing September object. Keep October and reference/notes.txt unchanged:

aws s3 \
  cp september-revised.csv "s3://$INPUT_BUCKET/sales/month=2026-09/orders.csv"

Read the stored input back to confirm the correction:

aws s3 \
  cp "s3://$INPUT_BUCKET/sales/month=2026-09/orders.csv" -

The trailing - prints the object. Its location and schema did not change, so the existing Glue partition still describes it. Submit the same summary SQL as a new execution:

REVISED_QUERY_ID=$(aws athena \
  start-query-execution \
  --query-string file://monthly-summary.sql \
  --query-execution-context "Database=$DATABASE" \
  --result-configuration "OutputLocation=$RESULT_LOCATION" \
  --query QueryExecutionId \
  --output text)
echo "$REVISED_QUERY_ID"

Confirm the new execution succeeded, repeating the status command if it is queued or running:

aws athena \
  get-query-execution \
  --query-execution-id "$REVISED_QUERY_ID" \
  --query 'QueryExecution.{State:Status.State,Reason:Status.StateChangeReason}'

Retrieve its rows:

aws athena \
  get-query-results \
  --query-execution-id "$REVISED_QUERY_ID" \
  --query 'ResultSet.Rows[*].Data[*].VarCharValue' \
  --output table

September is now 3600 cents with 3 orders; October remains 2000 cents with 3 orders. The changed input changed the computed result.

Retrieve this new execution's own S3 output location:

REVISED_OUTPUT=$(aws athena \
  get-query-execution \
  --query-execution-id "$REVISED_QUERY_ID" \
  --query QueryExecution.ResultConfiguration.OutputLocation \
  --output text)
echo "$REVISED_OUTPUT"

Download and inspect the new CSV without overwriting your original local summary:

aws s3 \
  cp "$REVISED_OUTPUT" summary-revised.csv

cat summary-revised.csv

Compare the original output still stored in S3:

aws s3 \
  cp "$SUMMARY_OUTPUT" -

Its September total is still 3500. The two different execution IDs point to separate output files: one describes the old input, the other describes the corrected input. AWS View shows the current September source and both executions' actual rows. This exercise demonstrates input/result correctness; it does not measure production scan costs or performance.

Example AWS View: the corrected source contains S01=1100 cents, the new query returns September=3600, and the earlier result remains 3500. Your bucket and execution IDs will differ.

Summary

You computed monthly totals from registered S3 data, downloaded the exact Athena output CSV, corrected a source value and recomputed the summary. September changed from 3500 to 3600 cents while October stayed at 2000. Earlier result files retain their earlier values, so a summary must be rerun when its input changes.

For further reading, see the AWS reference on Locate output files by query execution; SELECT filters, groups, and ordering.