Skip to main content

ETL with Glue and querying with Athena

⏱ 35 min

Hook​

The raw clickstream is landing in bronze/ as messy JSON, and an analyst wants a clean SQL table by end of day. You do not want to stand up a database, load it, and babysit a server for what is really a transform-and-expose task. On AWS this is a two-move loop: a Glue job reshapes the raw data into curated Parquet, and Athena queries that Parquet as SQL — no server, billed per query. This lesson runs the loop end to end and then, just as importantly, tears it back down to nothing.

Concept​

The AWS curate-and-query loop has three moving parts, and you already know the hardest one. A Glue ETL job is managed PySpark: the exact DataFrame API from DE-202, running on a Spark runtime AWS provisions for the job's duration and then shuts down. You point it at bronze data in S3, apply the same reads, filters, joins, and writes you would write locally, and land the result as Parquet in silver or gold. You never manage a cluster; you pay per DPU-hour, where a DPU (Data Processing Unit) is a unit of Glue compute, billed only while the job runs.

For a query engine to see that output as a table, something must record its schema in the Glue Data Catalog. A crawler does this automatically: it scans an S3 prefix, infers columns and types, detects Hive-style partitions, and writes a table definition into the catalog. You run a crawler once after new data lands (or on a schedule), and from then on the table is queryable. For stable schemas, many teams skip the crawler and register the table with a one-time SQL CREATE EXTERNAL TABLE instead — cheaper and fully under version control.

With the schema catalogued, Athena runs standard SQL directly over the S3 files. It is serverless: you submit a query, Athena reads the referenced Parquet, returns rows, and bills you $5 per terabyte scanned — with every query rounded up to a 10 MB minimum. Nothing runs between queries, so an idle Athena costs nothing. This is why the medallion layout from the last lesson pays off here: partitioning and Parquet decide how many bytes each query scans, and bytes scanned are the bill.

That leaves the discipline this whole course insists on: teardown. S3 storage is the one thing that bills continuously — a forgotten bucket accrues charges every month it sits there. Glue and Athena bill only when they run, so they mostly zero out on their own, but the catalog entries and any saved query results in S3 linger. Teardown means deleting the bucket's objects, dropping the catalog tables, and confirming nothing is left. Done properly, an exercise returns your account to a $0 resting state.

A Glue job is ordinary PySpark. This is the shape of one, shown for reference — you run it on real AWS in the Cloud school, not here:

# Glue ETL job (illustration): bronze JSON -> curated Parquet.
orders = spark.read.json("s3://lake/bronze/orders/")
clean = (
    orders.filter(orders.status == "paid")
    .filter(orders.amount > 0)
    .dropDuplicates(["order_id"])
    .withColumn("order_date", orders.order_ts.substr(1, 10))
)
(
    clean.write.mode("overwrite")
    .partitionBy("order_date")
    .parquet("s3://lake/silver/orders/")
)

And this is how Athena exposes and queries the result — plain SQL over S3:

-- Register the curated table once, then query it per day.
CREATE EXTERNAL TABLE silver_orders (
    order_id   STRING,
    category   STRING,
    amount     DOUBLE
)
PARTITIONED BY (order_date STRING)
STORED AS PARQUET
LOCATION 's3://lake/silver/orders/';

SELECT category, SUM(amount) AS revenue
FROM silver_orders
WHERE order_date = '2026-07-01'
GROUP BY category;
ServiceUnit of costHow to reduce it
S3GB stored per monthLifecycle old bronze; delete on teardown
GlueDPU-hours per runFewer DPUs, shorter jobs, run on schedule
AthenaTB scanned per queryPartition, use Parquet, select few columns
🧠 Knowledge check
1. What is a Glue ETL job, in practical terms?
2. Athena bills you primarily on which meter?

Worked example​

Let me price the same query before and after good layout, then add the job cost, so the whole loop's economics are on the table. Scenario: a "revenue by category for one day" query. Before partitioning, it scans 400 GB of raw CSV; after partition pruning and Parquet, it scans 1.3 GB. Athena bills $5 per TB with a 10 MB floor; the Glue job that produced the Parquet ran 2 DPUs for 8 minutes at $0.44 per DPU-hour.

ATHENA_RATE = 5.0                    # dollars per TB
GLUE_DPU_HOUR = 0.44                 # dollars per DPU-hour
MIN_SCAN_BYTES = 10 * 1024 * 1024    # Athena's 10 MB per-query floor


def athena_cost(bytes_scanned):
    billable = max(bytes_scanned, MIN_SCAN_BYTES)
    return (billable / 1024**4) * ATHENA_RATE


before = 400 * 1024**3               # 400 GB, unpartitioned CSV
after = int(1.3 * 1024**3)           # 1.3 GB, pruned Parquet
glue_job = 2 * (8 / 60) * GLUE_DPU_HOUR   # 2 DPUs, 8 minutes

print(f"query before layout: ${athena_cost(before):.4f}")
print(f"query after layout:  ${athena_cost(after):.4f}")
print(f"glue job to curate:  ${glue_job:.4f}")
print("teardown target:     $0.00")
query before layout: $1.9531
query after layout:  $0.0063
glue job to curate:  $0.1173
teardown target:     $0.00

The curation job is a one-time cost of about twelve cents, and it pays for itself on the very first query: layout drops the per-query bill from nearly two dollars to two-thirds of a cent, a 300-fold cut that repeats for every query the table ever serves. That is the loop's whole economics — spend a little compute once to reshape the data, then query it cheaply forever. And the last line is the one that keeps this a $0 course: when the exercise is done, you delete the bucket objects and drop the catalog table, and the account bills nothing further. Compute that only runs on demand plus storage you actually delete is what a disciplined AWS pipeline costs at rest — nothing.

Hands-on​

Your turn, with different scan sizes. The exercise below gives you a query's bytes scanned and the current Athena rate and asks you to return its cost, respecting the 10 MB minimum, then report the percentage saved when a layout change shrinks the scan. It runs in Python Arena, so there is nothing to install.

▶ Python Arenade203-estimate-athena-scan-cost
Open in Python Arena ↗

Success criteria: your cost matches the expected figure (including the 10 MB floor for tiny scans), and your reported saving is the percentage reduction between the before and after byte counts. The Arena validates your function against a hidden set of queries automatically.

Recap​

  • You can describe the AWS curate-and-query loop: a Glue PySpark job writes curated Parquet, a crawler or CREATE EXTERNAL TABLE catalogs it, and Athena queries it as serverless SQL.
  • You can name each service's cost meter — S3 per GB-month, Glue per DPU-hour, Athena per TB scanned — and the lever that reduces each.
  • You can carry out teardown so an exercise returns to a $0 resting state, and explain why S3 is the resource that bills if you forget it.

Next up: the lab, where you build this entire loop yourself as a faithful local model — bronze objects, a pure-Python Glue job, and a query over gold — running on your own machine for $0.

🧠 Knowledge check
1. After running exercises on AWS, why is deleting the S3 bucket the most important teardown step?
2. A Glue job runs once to curate data, then a table is queried thousands of times. Where does the layout investment pay off?
📌 Key takeaways
  • The AWS loop is: Glue PySpark job curates bronze into Parquet, a crawler or CREATE EXTERNAL TABLE registers the schema, and Athena queries it as serverless SQL. - Each service has one meter — S3 per GB-month, Glue per DPU-hour, Athena per TB scanned — and layout (partitioning + Parquet) is the main lever on the Athena bill. - Teardown is part of done: delete S3 objects and drop catalog tables so the account returns to a $0 resting state, because S3 is what bills if you forget.