Lesson 300 · AWS Learning Path

AWS 300: Amazon Athena and AWS Glue

Anil Jalela · Published Apr 21, 2026 · 18 min read

Why this lesson matters

An S3 bucket full of files is not yet an analytics platform. A useful platform needs trustworthy data layout, discoverable metadata, permissions, a query engine, controlled result handling, observability, cost limits, and an owner for schema change. Amazon Athena and AWS Glue often provide those pieces, but they solve different problems.

Athena is a serverless query service. For its common data-lake use case, it runs SQL directly against data in Amazon S3. You do not provision a database server, but you still design tables, files, partitions, workgroups, permissions, encryption, and query limits. AWS Glue is a serverless data-integration family. Its Data Catalog stores metadata; crawlers infer metadata; jobs transform data; and other Glue capabilities support workflows, data quality, schema handling, and integration. Athena commonly reads table definitions from the Glue Data Catalog, but neither service makes incorrect data trustworthy automatically.

This workshop starts with tiny CSV sales files, defines their schema deliberately, queries them through an enforced Athena workgroup, converts them to compressed Parquet, and compares bytes scanned. You will then decide when a Glue crawler, Glue ETL job, Athena CTAS statement, or a different platform is appropriate. The result is a small implementation plus an architecture decision, not a tour of Console menus.

What you will be able to do

By the end, you can:

• explain S3 data, Glue catalog metadata, Athena compute, and query-result storage as separate resources;
• distinguish a database, table, schema, SerDe, partition, crawler, classifier, job, bookmark, workgroup, and query execution;
• create a least-surprise raw table without treating crawler inference as data-quality proof;
• use a governed Athena workgroup to enforce result, encryption, metric, and scan controls;
• trace authorization through IAM, S3, KMS, the Data Catalog, and optional Lake Formation;
• convert row-oriented CSV to partitioned Parquet and measure the scan reduction;
• explain when to use Athena SQL, Glue ETL, a crawler, Redshift, EMR, OpenSearch, or an operational database;
• diagnose schema, partition, access, result-location, encryption, and small-file failures from evidence;
• estimate the full cost owner across Athena, Glue, S3, KMS, logs, transfer, and retained results; and
• clean up only learner-owned resources and prove that they are gone.

Before you start

• Complete the S3, IAM, KMS, CloudWatch, and CLI foundations earlier in this course.
• Use a learner-owned account and a non-root identity. Do not run this against employer or shared data.
• The example Region is ap-south-1. Keep the S3 buckets, Data Catalog objects, workgroup, and optional Glue job in one Region for this lab.
• Obtain cost approval before running a crawler or Glue ETL job. Athena queries and S3 requests can also incur charges.
• Use synthetic data only. Query output can reproduce sensitive source values.
• The required path uses S3, a manually defined Glue Catalog table, and Athena. The crawler and Glue ETL run are optional paid extensions.
• Record a unique lowercase lab suffix. Never copy another account's bucket name or delete a bucket you did not create.

Create a local evidence directory and set variables. Replace your-unique-suffix before running anything:

mkdir -p "$HOME/aws300-evidence"
cd "$HOME/aws300-evidence"

export AWS_DEFAULT_REGION="ap-south-1"
export LAB_SUFFIX="your-unique-suffix"
export RAW_BUCKET="nw-aws300-raw-${LAB_SUFFIX}"
export RESULT_BUCKET="nw-aws300-results-${LAB_SUFFIX}"
export DB_NAME="nw_aws300_${LAB_SUFFIX//-/_}"
export WG_NAME="nw-aws300-${LAB_SUFFIX}"

aws sts get-caller-identity --query Arn --output text
aws configure list

Redact the account portion of the caller ARN in shared evidence. Stop if either bucket name already belongs to someone else.

1. Build the correct mental model

Follow one query through six planes:

analyst or application
        |
        v
Athena API and governed workgroup
        |
        +------> Glue Data Catalog: database, table, columns, partitions, location
        |
        +------> IAM or Lake Formation authorization
        |
        +------> KMS authorization for encrypted objects or results
        |
        v
S3 source objects ----> Athena distributed SQL execution ----> query results
        |                                                   S3 or Athena-managed
        v
CloudTrail, Athena history, CloudWatch metrics, S3 and cost evidence

The Glue table usually stores metadata, not rows. Its Location points to S3. Athena reads metadata, lists and reads matching objects, executes the query, and writes or manages a result. Deleting a Catalog table does not normally delete its S3 data. Deleting source files does not automatically remove stale Catalog metadata.

Athena SQL and Glue jobs are serverless because AWS manages their fleets. Serverless does not mean free, limitless, stateless, or architecture-free.

2. Understand service history and current scope

Athena began as serverless SQL over S3 and now includes workgroups, engine-version management, federated connectors, Apache Iceberg support, SQL and Spark choices, provisioned-capacity options, and Athena-managed query results. Engine version 3 is based on Trino and can differ from older Presto behavior. Pin and test engine changes instead of assuming every function and type behaves identically.

Glue began around a shared Data Catalog, crawlers, and managed ETL. It now includes multiple job runtimes, interactive development, workflows, Data Quality, Schema Registry, connectors, and table optimizers for supported open table formats. A current Glue 5.0 Spark job uses a newer Spark and Python runtime than old examples. Runtime choice is an application dependency with lifecycle ownership.

These histories explain why the consoles contain many adjacent features. Select the smallest capability that meets the requirement. A catalog table does not require a crawler. A format conversion does not always require a Glue Spark job. Athena CTAS may be enough for a bounded SQL transformation.

3. Design the S3 data contract

The lab uses Hive-style date prefixes:

s3://RAW_BUCKET/sales_csv/sale_date=2026-09-20/part-000.csv
s3://RAW_BUCKET/sales_csv/sale_date=2026-09-21/part-000.csv
s3://RAW_BUCKET/sales_parquet/sale_date=2026-09-20/...

Create two small files. Each omits sale_date because that value comes from its partition path:

mkdir -p sale_date=2026-09-20 sale_date=2026-09-21

printf '%s\n' \
  'order_id,customer_region,item,quantity,unit_price' \
  'o-1001,west,keyboard,2,45.50' \
  'o-1002,south,mouse,1,18.25' \
  > sale_date=2026-09-20/part-000.csv

printf '%s\n' \
  'order_id,customer_region,item,quantity,unit_price' \
  'o-1003,west,monitor,1,210.00' \
  'o-1004,north,mouse,3,18.25' \
  > sale_date=2026-09-21/part-000.csv

sha256sum sale_date=*/part-000.csv | tee raw-sha256.txt

A production contract additionally defines producer, time zone, null rules, duplicate policy, allowed values, decimal precision, late arrivals, retention, classification, owner, and compatibility. CSV is readable but weakly typed and expensive to scan. Parquet is columnar, typed, compressed, and splittable. Avoid both millions of tiny objects and giant unsplittable compressed text files.

4. Create protected source and result buckets

Create the buckets, block public access, enable versioning, and upload the data:

for bucket in "$RAW_BUCKET" "$RESULT_BUCKET"; do
  aws s3api create-bucket \
    --bucket "$bucket" \
    --create-bucket-configuration LocationConstraint="$AWS_DEFAULT_REGION"
  aws s3api put-public-access-block \
    --bucket "$bucket" \
    --public-access-block-configuration \
      BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true
  aws s3api put-bucket-versioning \
    --bucket "$bucket" \
    --versioning-configuration Status=Enabled
done

aws s3 cp sale_date=2026-09-20/part-000.csv \
  "s3://${RAW_BUCKET}/sales_csv/sale_date=2026-09-20/part-000.csv"
aws s3 cp sale_date=2026-09-21/part-000.csv \
  "s3://${RAW_BUCKET}/sales_csv/sale_date=2026-09-21/part-000.csv"

aws s3api list-objects-v2 --bucket "$RAW_BUCKET" \
  --query 'Contents[].{Key:Key,Bytes:Size}' --output table | tee s3-inventory.txt

The location constraint differs for us-east-1. Production design must separately decide default encryption, KMS ownership, bucket policy, object ownership, access evidence, lifecycle, retention, and replication.

5. Create the Glue database and table deliberately

aws glue create-database \
  --database-input "{\"Name\":\"${DB_NAME}\",\"Description\":\"AWS300 synthetic sales lab\"}"

Create table-input.json. The partition key is separate from normal columns, and the location ends above the partition prefixes:

{
  "Name": "sales_csv",
  "TableType": "EXTERNAL_TABLE",
  "Parameters": {
    "classification": "csv",
    "skip.header.line.count": "1"
  },
  "PartitionKeys": [{"Name": "sale_date", "Type": "date"}],
  "StorageDescriptor": {
    "Columns": [
      {"Name": "order_id", "Type": "string"},
      {"Name": "customer_region", "Type": "string"},
      {"Name": "item", "Type": "string"},
      {"Name": "quantity", "Type": "int"},
      {"Name": "unit_price", "Type": "decimal(10,2)"}
    ],
    "Location": "REPLACE_RAW_LOCATION",
    "InputFormat": "org.apache.hadoop.mapred.TextInputFormat",
    "OutputFormat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat",
    "SerdeInfo": {
      "SerializationLibrary": "org.apache.hadoop.hive.serde2.OpenCSVSerde",
      "Parameters": {"separatorChar": ",", "quoteChar": "\""}
    }
  }
}

Replace the placeholder and create the table:

sed -i "s#REPLACE_RAW_LOCATION#s3://${RAW_BUCKET}/sales_csv/#" table-input.json
aws glue create-table --database-name "$DB_NAME" \
  --table-input file://table-input.json

aws athena start-query-execution \
  --work-group primary \
  --query-execution-context Database="$DB_NAME" \
  --result-configuration OutputLocation="s3://${RESULT_BUCKET}/bootstrap/" \
  --query-string "MSCK REPAIR TABLE sales_csv" \
  --query QueryExecutionId --output text | tee repair-query-id.txt

Wait for that query to reach SUCCEEDED, then prove metadata:

aws glue get-table --database-name "$DB_NAME" --name sales_csv \
  --query 'Table.{Location:StorageDescriptor.Location,Columns:StorageDescriptor.Columns,Partitions:PartitionKeys}' \
  --output json | tee catalog-table.json
aws glue get-partitions --database-name "$DB_NAME" --table-name sales_csv \
  --query 'Partitions[].{Values:Values,Location:StorageDescriptor.Location}' \
  --output table | tee catalog-partitions.txt

A crawler can automate discovery for numerous changing sources. Scope its target narrowly, review its IAM role and schema-change policy, and inspect proposed tables. It can infer string where the contract requires decimal, merge incompatible files, or create surprising tables. It discovers; it does not certify semantic quality.

6. Create an enforced Athena workgroup

Athena supports customer-owned S3 results and Athena-managed results. Managed results remove the result-bucket requirement, encrypt results, and remove them after the documented retention window, but do not support result reuse. This lab uses customer-owned results so you can inspect access and lifecycle behavior.

aws athena create-work-group \
  --name "$WG_NAME" \
  --description "AWS300 governed learner queries" \
  --configuration "ResultConfiguration={OutputLocation=s3://${RESULT_BUCKET}/athena/,EncryptionConfiguration={EncryptionOption=SSE_S3}},EnforceWorkGroupConfiguration=true,PublishCloudWatchMetricsEnabled=true,BytesScannedCutoffPerQuery=100000000,EngineVersion={SelectedEngineVersion='Athena engine version 3'}"

aws athena get-work-group --work-group "$WG_NAME" --output json \
  | tee athena-workgroup.json

The 100 MB cutoff is a safety ceiling, not a budget guarantee. Workgroup IAM controls who can start queries. S3 permissions still govern source and customer-owned results. A principal with direct s3:GetObject on a result object may retrieve it even if Athena GetQueryResults is denied. KMS permissions matter when a customer-managed key protects source or results.

7. Run bounded queries and capture evidence

Create a helper that starts a query, waits for a terminal state, and records statistics:

run_query() {
  query_text="$1"
  query_id=$(aws athena start-query-execution \
    --work-group "$WG_NAME" \
    --query-execution-context Database="$DB_NAME" \
    --query-string "$query_text" \
    --query QueryExecutionId --output text) || return 1

  while :; do
    state=$(aws athena get-query-execution --query-execution-id "$query_id" \
      --query QueryExecution.Status.State --output text) || return 1
    case "$state" in
      SUCCEEDED) break ;;
      FAILED|CANCELLED)
        aws athena get-query-execution --query-execution-id "$query_id" \
          --query QueryExecution.Status --output json
        return 1 ;;
    esac
    sleep 2
  done

  aws athena get-query-execution --query-execution-id "$query_id" \
    --query 'QueryExecution.{Id:QueryExecutionId,State:Status.State,Bytes:Statistics.DataScannedInBytes,EngineMs:Statistics.EngineExecutionTimeInMillis,Output:ResultConfiguration.OutputLocation}' \
    --output json
  aws athena get-query-results --query-execution-id "$query_id" --output table
}

run_query 'SELECT count(*) AS rows, sum(quantity * unit_price) AS revenue FROM sales_csv' \
  | tee query-all.txt

run_query "SELECT customer_region, sum(quantity * unit_price) AS revenue FROM sales_csv WHERE sale_date = DATE '2026-09-21' GROUP BY customer_region ORDER BY revenue DESC" \
  | tee query-partition.txt

Verify four facts: four records are read, revenue arithmetic is sensible, the date query targets one partition, and executions belong to the intended workgroup. SUCCEEDED proves execution, not completeness, freshness, uniqueness, or business correctness.

Use EXPLAIN (TYPE IO) before an unfamiliar expensive query. EXPLAIN does not scan source data, while EXPLAIN ANALYZE executes and can incur scan cost.

8. Convert CSV to partitioned Parquet with CTAS

Athena CTAS combines DDL and query behavior. It can convert format, compression, and partition layout, but writes new S3 objects and Catalog metadata. Use a new empty destination prefix.

run_query "CREATE TABLE sales_parquet WITH (format='PARQUET', parquet_compression='SNAPPY', external_location='s3://${RAW_BUCKET}/sales_parquet/', partitioned_by=ARRAY['sale_date']) AS SELECT order_id, customer_region, item, quantity, unit_price, sale_date FROM sales_csv" \
  | tee ctas.txt

run_query "SELECT customer_region, sum(quantity * unit_price) AS revenue FROM sales_parquet WHERE sale_date = DATE '2026-09-21' GROUP BY customer_region ORDER BY revenue DESC" \
  | tee query-parquet.txt

aws s3api list-objects-v2 --bucket "$RAW_BUCKET" --prefix sales_parquet/ \
  --query 'Contents[].{Key:Key,Bytes:Size}' --output table | tee parquet-inventory.txt

Compare DataScannedInBytes from equivalent CSV and Parquet queries. This tiny dataset is too small for a meaningful economic ratio, so explain direction rather than claiming production savings. At scale, column pruning, partition pruning, compression, and sensible file sizes reduce I/O. Over-partitioning and tiny files increase metadata and S3 request overhead.

Use Glue ETL when transformation needs Spark libraries, complex joins, large distributed processing, data-quality rules, reusable code, connectors, or workflow integration. Current Spark jobs use worker type and worker count; cost follows allocated DPU time. Pin the Glue version, dependencies, timeout, retries, execution class, and concurrency. Enable job bookmarks only after understanding source-specific semantics. A bookmark does not identify corrected old records automatically and does not guarantee exactly-once output.

9. Understand permission and governance layers

An IAM-controlled query generally needs permission to use the workgroup, read Glue metadata, read source objects, write and read the result location, and use relevant KMS keys. Bucket policies, endpoint policies, service control policies, permissions boundaries, session policies, and explicit denies can narrow those grants.

Lake Formation can govern Data Catalog resources and vend temporary credentials for registered locations. Named-resource or LF-tag grants can add table, column, row, and cell controls for supported integrations. Hybrid access mode lets selected principals use Lake Formation while others continue on IAM paths. Do not add broad S3 permissions blindly. Determine whether the principal and resource are opted into Lake Formation and whether the location is registered. AWS301 teaches that model deeply.

10. Design production operations

Separate exploratory, scheduled, sensitive, and application queries into workgroups. Enforce configuration, publish metrics, use per-query scan cutoffs, and monitor aggregate usage. Preserve history beyond the convenient service view when audit requirements demand it. CloudTrail records control-plane calls; Athena history and execution statistics show query state and bytes; CloudWatch shows workgroup metrics; S3 and KMS logs answer different access questions.

Treat schema as versioned code. Test additions, type widening, renames, partition changes, and producer rollback. Quarantine malformed records instead of silently casting them to null. Measure freshness, completeness, uniqueness, validity, and reconciliation against a source control total. Glue Data Quality can evaluate rules, but rule ownership and response remain human responsibilities.

For private Glue job connectivity, distinguish service API access from job data-plane access. A VPC-attached job uses elastic network interfaces and needs routes, security groups, DNS, and access to S3 and dependencies through NAT or endpoints. Athena querying S3 is not made private merely because an analyst connects through a VPC.

11. Choose the right service

Federated Athena connectors can query external sources, but connector execution, spill storage, source capacity, network paths, and pushdown behavior join the design. Do not use federation to hide an unbounded production database scan.

12. Diagnose failures from evidence

Use this order: query state and reason, workgroup, caller, Catalog object, governance mode, S3/KMS access, object layout, schema/SerDe, then service limits.

Never solve analytics access by granting s3:*, glue:*, athena:*, and kms:* to everyone. That hides the failed layer and creates an exfiltration path.

13. Cost, quotas, and lifecycle

Athena SQL commonly charges for bytes scanned, subject to current pricing rules and minimums. Provisioned capacity, Spark, federation, and other features have different dimensions. Glue crawlers and jobs charge for capacity and runtime; Catalog storage and requests, Data Quality, interactive sessions, and connectors can add cost. S3 storage, requests, retrieval, lifecycle, replication, KMS, logs, NAT processing, and transfer remain separate.

Model query frequency multiplied by bytes scanned, peak concurrency, retries, result retention, ETL DPU time, crawler schedule, object counts, and growth. Check current Regional prices and quotas instead of embedding a universal dollar figure.

Controls include workgroup isolation, enforced settings, scan cutoffs, budgets, anomaly alerts, partition and column pruning, Parquet or ORC, compression, compaction, compatible result reuse, lifecycle, bounded crawlers, right-sized workers, timeouts, and deletion of abandoned artifacts.

14. Clean up and prove it

Save query IDs, statistics, schemas, inventories, and the architecture decision. Then remove only exact recorded resources:

aws glue delete-table --database-name "$DB_NAME" --name sales_parquet
aws glue delete-table --database-name "$DB_NAME" --name sales_csv
aws glue delete-database --name "$DB_NAME"
aws athena delete-work-group --work-group "$WG_NAME" --recursive-delete-option

aws s3 rm "s3://${RAW_BUCKET}" --recursive
aws s3 rm "s3://${RESULT_BUCKET}" --recursive

Because versioning was enabled, those S3 commands add deletion markers and can leave billed versions. Permanently delete all versions and markers for these exact learner-owned buckets, then delete the buckets. Verify:

aws glue get-database --name "$DB_NAME" 2>&1 | tee cleanup-database.txt
aws athena get-work-group --work-group "$WG_NAME" 2>&1 | tee cleanup-workgroup.txt
aws s3api head-bucket --bucket "$RAW_BUCKET" 2>&1 | tee cleanup-raw-bucket.txt
aws s3api head-bucket --bucket "$RESULT_BUCKET" 2>&1 | tee cleanup-result-bucket.txt

Expected proof is not-found for the database and workgroup and missing-bucket responses for both names. Access denied is not deletion proof. Review Billing later because metering is delayed.

15. Practical submission

Submit an aws300-evidence/ package containing:

1. architecture.md with the six-plane request path and owners;
2. data-contract.md with schema, partition, quality, retention, and evolution;
3. source files and their SHA-256 manifest;
4. redacted bucket protection and versioning evidence;
5. Catalog table and partition evidence;
6. enforced workgroup configuration;
7. raw full-scan and filtered query IDs, results, and statistics;
8. CTAS, Parquet inventory, and equivalent-query comparison;
9. one intentionally diagnosed failure;
10. IAM, Lake Formation, S3, and KMS authorization analysis;
11. a monthly cost model with assumptions and sensitivity;
12. an Athena-versus-Glue-versus-alternative ADR; and
13. cleanup and delayed billing-review evidence.

If you do the optional Glue extension, include crawler or job role policy, runtime/version, workers, logs, duration, DPU evidence, bookmark/replay decision, reconciliation, and deletion proof.

Knowledge check

1. Where do rows live for an external Glue table? Usually in its S3 location; the Catalog stores metadata.
2. Why can a crawler-created table be wrong? Inference observes samples, not the business contract or every future record.
3. What does partition pruning require? Correct metadata or projection plus a partition-key predicate.
4. Why is Parquet often cheaper than CSV? Column pruning, compression, typed encoding, and splitting can reduce scans.
5. Does SUCCEEDED prove correct data? No. It does not prove freshness, completeness, or business validity.
6. Why can denying GetQueryResults be insufficient? Direct S3 permission can expose customer-owned result objects.
7. When is Glue ETL stronger than CTAS? For distributed code, connectors, complex transforms, quality, or workflow needs.
8. What does a bookmark not guarantee? CDC semantics, exactly-once output, or detection of corrected old input.
9. Why might broad S3 permission not fix a query? The deny can be in Athena, Glue, Lake Formation, KMS, SCPs, boundaries, or endpoint policy.
10. What remains after aws s3 rm on a versioned bucket? Versions and deletion markers can remain billable.

Lesson acceptance

Pass only when all are true:

• the implementation uses synthetic data and exact learner-owned names;
• source, metadata, execution, results, and governance are explained separately;
• table columns, SerDe, location, and partitions match actual objects;
• the workgroup enforces result, encryption, metrics, engine, and scan ceiling;
• raw and filtered results include query IDs and bytes-scanned evidence;
• CTAS produces partitioned Parquet and the result is reconciled;
• the tiny sample is not used to claim a universal savings percentage;
• crawler, job, bookmark, Lake Formation, and managed-result boundaries are accurate;
• one failure is diagnosed without broadening access blindly;
• the ADR includes at least three credible alternatives;
• cost includes Athena, Glue, S3, KMS, logging, network, retries, and retention; and
• cleanup proves Catalog, workgroup, versions, markers, and buckets are gone.

Official sources

• What is Amazon Athena?
• What is AWS Glue?
• AWS Glue Data Catalog and crawlers
• Athena workgroups and cost controls
• Athena managed query results
• Optimize Athena data
• Athena CTAS and INSERT INTO
• Athena EXPLAIN
• Glue Spark job properties
• AWS Glue version support
• Use Lake Formation with Athena
• Lake Formation hybrid access mode