Skip to Content
Cloud Engineering Academy
7. Data Engineering & ETL Pipelines

Lab 7.3: Athena on CSV versus Parquet, by bytes scanned

Upload the same transactions as CSV and as partitioned Parquet to S3, query both through a capped, encrypted Athena workgroup, and compare the bytes each query scans, with partition projection instead of a crawler.

intermediateโฑ 1 h 15 min๐ŸŸก Low costDEA-C01SAA-C03

Assumes: Amazon Athena engine v3 ยท AWS CDK v2 (C#) ยท AWS CLI v2 ยท Region us-east-1 ยท profile learn-sandbox ยท labs/m07-transaction-pipeline/sql/athena/01_lab73_csv_vs_parquet.sql. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lab you can:

  • Deploy the module's data-lake stack and identify what it creates: encrypted zone buckets, Glue databases and an Athena workgroup with a scan limit.
  • Create an Athena table over CSV (OpenCSVSerDe) and a partitioned Parquet table with partition projection, without running a crawler.
  • Run the same business question against both tables and read Data scanned from the query statistics, with the CLI and in the console.
  • Explain why the bill is identical for tiny queries (the 10 MB minimum) while the bytes scanned are not, and when the difference becomes money.
  • Use CTAS to let Athena write Parquet, and know what an enforced workgroup does to external_location.
  • Tear everything down and check that nothing tagged for the lab remains.

Prerequisites

  • Lab 7.1 and Lab 7.2: you need a CSV file and the Parquet folder parquet_snappy that Lab 7.2 wrote.
  • Amazon Athena and Glue Data Catalog and crawlers.
  • AWS CLI v2 and aws sso login --profile learn-sandbox; the .NET 10 SDK, Node.js and the AWS CDK CLI v2 for the deploy; an account bootstrapped with cdk bootstrap. See cost guardrails first: the sandbox should have a budget alarm.

Cost label

๐ŸŸก Credits. The standing cost while the stack exists is small: a customer managed KMS key (about USD 1 per month, prorated) and one Secrets Manager secret (about USD 0.40 per month โš  VERIFY), plus pennies of S3. Athena is USD 5 per TB scanned with a 10 MB minimum per query, so a lab query is a fraction of a cent. A full session costs well under USD 0.10 if torn down the same day.

Free alternative: do the design half without AWS. Take the sizes from Lab 7.2, and for each of the two queries in Part C write down which files and columns an engine would have to read. You miss the real Data scanned numbers but keep the reasoning.

Scenario

Finance asks Harbourline's analytics team: "How many transactions did we book in the first week of November, per currency, and what do they total?" Today the data lands as CSV, and each such question reads the entire file. Before you convince the team to move to Parquet, you want evidence from their own workgroup: the same question, two tables, and the bytes each one scanned.

Architecture

Both zone buckets use SSE-KMS with the key alias/hl-m07-data and a TLS-only policy. The workgroup enforces its settings: scan cutoff 1,000,000,000 bytes per query, results to the encrypted results bucket, CloudWatch metrics on. Athena engine version 3 is selected.

The files

FileRole in the lab
infra/cdk/.../DataLakeStack.csCreates the buckets, key, Glue databases, security configuration and the workgroup
sql/athena/01_lab73_csv_vs_parquet.sqlAll the SQL of this lab, with 111122223333 as the account id placeholder
local/out/parquet_snappy/Your Lab 7.2 output

Part A: deploy the lake (no Lambda needed)

Deploy only the data-lake stack

cd labs/m07-transaction-pipeline/infra/cdk
aws sso login --profile learn-sandbox
cdk deploy HlM07-DataLake --profile learn-sandbox -c owner=<your-name>

Without dotnet publish output the CDK app prints that it is skipping the ingest API and the streaming stack, which is what you want here. cdk deploy shows an IAM and security-group change summary and asks for confirmation: read it. Note the outputs RawBucketName and GoldBucketName.

Set the shell variables used below

export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1 MSYS_NO_PATHCONV=1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
RAW="hl-m07-raw-${ACCOUNT}"; CUR="hl-m07-curated-${ACCOUNT}"

Part B: upload the two copies

Upload the CSV under lab73/, not incoming/

cd ../..   # back to labs/m07-transaction-pipeline
aws s3 cp data/samples/transactions.csv "s3://${RAW}/lab73/csv/transactions.csv"

The prefix matters. Any object under incoming/ triggers the module project's pipeline through EventBridge. lab73/ does not.

Upload the Parquet partitions, leaving out Spark's marker files

aws s3 sync local/out/parquet_snappy "s3://${CUR}/lab73/parquet/" --exclude "_SUCCESS" --exclude "*.crc" --exclude ".*"
aws s3 ls "s3://${CUR}/lab73/parquet/" --recursive | head -5

You should see keys like lab73/parquet/year=2026/month=10/day=20/part-00000-....snappy.parquet. The uploads use the bucket's default SSE-KMS encryption automatically.

Part C: create the tables and run the comparison

The SQL file holds every statement. Athena runs one statement per query, so run them one at a time. This helper runs a statement through the CLI and prints the result and the statistics.

q() {
  local id state
  id=$(aws athena start-query-execution --work-group hl-m07-analysts --query-string "$1" --query QueryExecutionId --output text)
  while :; do
    state=$(aws athena get-query-execution --query-execution-id "$id" --query QueryExecution.Status.State --output text)
    [ "$state" = QUEUED ] || [ "$state" = RUNNING ] || break
    sleep 1
  done
  echo "[$state] $id"
  aws athena get-query-execution --query-execution-id "$id" \
    --query "QueryExecution.Statistics.{scannedBytes:DataScannedInBytes,millis:EngineExecutionTimeInMillis}" --output table
  [ "$state" = SUCCEEDED ] && aws athena get-query-results --query-execution-id "$id" --output text --query "ResultSet.Rows[].Data[].VarCharValue" | head -20
}

Create the database and the CSV table

q "CREATE DATABASE IF NOT EXISTS hl_m07_lab"
q "CREATE EXTERNAL TABLE IF NOT EXISTS hl_m07_lab.transactions_csv (
  transaction_id string, account_id string, booking_date string, value_date string, amount string,
  currency string, type string, channel string, counterparty_name string, counterparty_iban string,
  merchant_category_code string, reference string, status string, idempotency_key string, created_at string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',', 'quoteChar' = '\"')
LOCATION 's3://${RAW}/lab73/csv/'
TBLPROPERTIES ('skip.header.line.count' = '1')"

Why every column is a string: OpenCSVSerDe reads fields as text and cannot type them for you, which suits a raw zone. skip.header.line.count hides the header row. A table over raw files does not copy data: it is a description of where the files are and how to read them.

Create the Parquet table with partition projection

q "CREATE EXTERNAL TABLE IF NOT EXISTS hl_m07_lab.transactions_parquet (
  transaction_id string, account_id string, booking_date date, value_date date, amount decimal(18,2),
  currency string, type string, channel string, counterparty_name string, counterparty_iban string,
  merchant_category_code string, reference string, status string, idempotency_key string, created_at timestamp)
PARTITIONED BY (year string, month string, day string)
STORED AS PARQUET
LOCATION 's3://${CUR}/lab73/parquet/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.year.type' = 'integer', 'projection.year.range' = '2025,2027',
  'projection.month.type' = 'integer', 'projection.month.range' = '1,12', 'projection.month.digits' = '2',
  'projection.day.type' = 'integer', 'projection.day.range' = '1,31', 'projection.day.digits' = '2',
  'storage.location.template' = 's3://${CUR}/lab73/parquet/year=\${year}/month=\${month}/day=\${day}')"

Partition projection tells Athena how partition values are generated (integers in a range, two digits wide) so it computes the folder paths itself and never asks the Glue catalog for partitions. No crawler and no MSCK REPAIR TABLE is needed when new folders appear. Two details to notice: the template escapes \${year} in the shell so the literal ${year} reaches Athena, and digits = 2 makes month 1 appear as the folder name 01, matching the zero-padded folders Spark wrote in Lab 7.2. Projection works only for Athena: Glue jobs and Redshift Spectrum do not use it.

The comparison

Question: transactions and total per currency for 1 to 7 November 2026.

# CSV: Athena must read every byte of the file.
q "SELECT currency, count(*) AS n, sum(cast(amount AS decimal(18,2))) AS total
   FROM hl_m07_lab.transactions_csv
   WHERE booking_date BETWEEN '2026-11-01' AND '2026-11-07' GROUP BY currency"

# Parquet: only 7 day-folders, and only 2 of the 15 columns (currency, amount).
q "SELECT currency, count(*) AS n, sum(amount) AS total
   FROM hl_m07_lab.transactions_parquet
   WHERE year = '2026' AND month = '11' AND day BETWEEN '01' AND '07' GROUP BY currency"

Both return the same groups (GBP, EUR) with the same counts and totals. Compare scannedBytes.

What you should see, qualitatively:

  • CSV scanned bytes equal the size of the file you uploaded (220,622 for the small sample). There is no way to skip anything.
  • Parquet scanned bytes are a fraction of the Parquet folder's size, because partition pruning skips the other day folders and the columnar layout skips the other 13 columns.
  • On the 1,012-row sample the Parquet number may not be dramatically smaller, because each 20-row file is dominated by its footer (Lab 7.2). On the medium dataset (100,146 rows) the gap is large. I did not run these queries in an AWS account while writing this lab, so I give no measured Athena numbers: use your own, and compare ratios, not absolutes.

Read the billing rule honestly

Athena charges USD 5 per TB scanned with a 10 MB minimum per query. Every query in this lab scans less than 10 MB, so both cost exactly the same: the minimum. The lab teaches the bytes scanned number because it scales: at 1 TB of CSV a dashboard that scans the whole table 100 times a day costs about USD 500 a day, while the Parquet equivalent that reads 3 of 40 columns and one month of partitions can cost a hundredth of that. Use the small lab to learn what to measure, then extrapolate.

Optional: let Athena write the Parquet (CTAS)

q "CREATE TABLE hl_m07_lab.transactions_ctas
   WITH (format = 'PARQUET', write_compression = 'SNAPPY', partitioned_by = ARRAY['booking_month'])
   AS SELECT transaction_id, account_id, cast(amount AS decimal(18,2)) AS amount, currency, type,
             substr(booking_date, 1, 7) AS booking_month
   FROM hl_m07_lab.transactions_csv"

CTAS (CREATE TABLE AS SELECT) reads the CSV table, writes new Parquet files and registers a table in one statement. Note what the statement does not contain: an external_location. Because the workgroup enforces its result location, Athena writes the new table under the results bucket in a tables/<query-id>/ folder, and AWS documents that an external_location can be used only when the workgroup does not override the location. The partition column must come last in the SELECT list. Athena limits CTAS to 100 partitions written per statement; this one writes two (2026-10 and 2026-11).

Part D: the same in the console

  1. Athena โ†’ Query editor โ†’ Workgroup hl-m07-analysts (top right). Check Settings shows the results location and the 1,000,000,000-byte limit.
  2. Paste each statement from 01_lab73_csv_vs_parquet.sql, after replacing 111122223333 with your account id (sed -i "s/111122223333/${ACCOUNT}/g" sql/athena/01_lab73_csv_vs_parquet.sql works on a copy), and run them one at a time.
  3. Under each result, the Query stats panel shows Data scanned and Run time. Recent queries keeps the history; you can also compare executions there.

Security considerations

โš  Learning shortcut โ€” not for production

The lab queries as your own administrator SSO role, the tables sit directly over raw files with counterparty_iban and counterparty_name in clear text, and the CSV table points at the raw bucket. In the project, analysts never query raw data: they get curated, tokenised data through Lake Formation filters (Lab 7.5) and run in a workgroup that only they and the pipeline can use. Raw access is a pipeline privilege.

  • The workgroup is a guard rail. EnforceWorkGroupConfiguration means a user cannot swap in an unencrypted results bucket or remove the scan cutoff. A query that would scan more than 1 GB is cancelled.
  • Results are data. Athena writes every result set to S3. The results bucket is encrypted with the same customer managed key and expires objects after seven days. Who can read it matters as much as who can query.
  • Everything is TLS-only. The buckets deny requests that do not use HTTPS.
  • Query history exposes query text. Separate analysts into workgroups if some query strings (with column names or filter values) are sensitive.

Common mistakes

  • Uploading the CSV under incoming/ โ†’ it starts the project pipeline and a Glue job (cost). Use lab73/.
  • Forgetting skip.header.line.count โ†’ the header becomes a data row whose amount is the text amount, and the cast fails.
  • Wrong partition digits โ†’ Spark wrote day=05, but a projection without digits = 2 looks for day=5, so single-digit days return zero rows with no error. Match the folder names exactly.
  • Running two statements in one query โ†’ Athena accepts one statement per query.
  • Comparing bytes from one tiny file and concluding Parquet "does not help" โ†’ test at volume.
  • Using SELECT * on the CSV table "to look" โ†’ it scans everything, every time. Use LIMIT carefully (it does not reduce the scan on CSV) or query a small copy.

Troubleshooting

SymptomLikely causeFix
HIVE_BAD_DATA or cast failure on amountHeader row read as dataCheck skip.header.line.count; make sure only one file is under the prefix
Parquet table returns zero rowsProjection range or digits do not match the folders, or wrong bucket/prefix in the templateaws s3 ls the prefix; compare folder names with the projection properties
Access denied on s3://hl-m07-...Your role cannot use the KMS key, or the bucket policy deniesUse the same SSO role that deployed the stack, or add key permissions
Query exhausted resources at this scale factor or cancelled with a scan-limit messageWorkgroup cutoff of 1 GBNarrow the query; do not raise the limit "to get it working"
CTAS fails with a location errorexternal_location used in an enforcing workgroupRemove it, as in the lab SQL
Table already exists on CTAS rerunCTAS cannot overwriteDROP TABLE first and delete the output prefix
CLI helper prints nothing after [FAILED]The statement failedaws athena get-query-execution --query-execution-id ID --query QueryExecution.Status.StateChangeReason

Cost and cleanup

# 1. Drop the lab tables and database (the data stays in S3 until deleted below).
q "DROP TABLE IF EXISTS hl_m07_lab.transactions_ctas"
q "DROP TABLE IF EXISTS hl_m07_lab.transactions_parquet"
q "DROP TABLE IF EXISTS hl_m07_lab.transactions_csv"
q "DROP DATABASE IF EXISTS hl_m07_lab"

# 2. Delete the uploaded data and the CTAS files.
aws s3 rm "s3://${RAW}/lab73/" --recursive
aws s3 rm "s3://${CUR}/lab73/" --recursive
aws s3 rm "s3://hl-m07-athena-results-${ACCOUNT}/" --recursive

# 3. Keep the stack for Lab 7.4 and the project, or remove it now (also deletes the buckets and the key schedule):
cd infra/cdk && cdk destroy HlM07-DataLake --profile learn-sandbox

# 4. Nothing left behind?
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
  --query "ResourceTagMappingList[?contains(ResourceARN, 'hl-m07')].ResourceARN"

Raw has versioning on, so s3 rm leaves delete markers and old versions; cdk destroy empties the buckets because the stack uses autoDeleteObjects, which is a learning shortcut. The customer managed key enters a seven-day pending-deletion window after destroy (the minimum), and the key stops billing when scheduled for deletion โš  VERIFY the exact point billing stops.

Exercises

  • ๐ŸŸข Guided: Run the CSV and Parquet queries and record Data scanned for each, plus the size of the file and of the Parquet folder. Compute the ratio and write one sentence on why it is not 100 times at this size.
  • ๐ŸŸก Challenge: Generate the medium dataset (Lab 7.1 Part C), convert it with Lab 7.2, upload both under lab73/medium/ and repeat the comparison. Add a third query that selects * from the Parquet table with the same filters and explain the difference between the 2-column and * scans.
  • ๐Ÿ”ด Live problem: The finance dashboard's Athena bill tripled this month although the data volume grew only 20 percent. You find the dashboard runs SELECT * over the whole table every five minutes against CSV. Propose the changes in order of cost saving per hour of effort, and say how you would prove each saving with the workgroup metrics.

Assignment

Write a one-page Athena cost note for your portfolio repository: a table of your measured bytes scanned for CSV, Parquet and SELECT * Parquet, the monthly cost at 1 TB for each at 100 queries a day, and a recommendation. State your assumptions and the date of the price.

Solutions

Knowledge check

Interview questions

  • "How do you make Athena cheaper?" โ€” Use partitioned, compressed columnar formats; name columns; filter on partitions; set workgroup scan limits; avoid many small files; reuse results or move repeated dashboards to SPICE or a warehouse.
  • "What is a workgroup?" โ€” A boundary for settings, access, query history and cost control: result location, encryption, scan cutoffs and metrics, enforced per workgroup.
  • "When would you not use partition projection?" โ€” When engines other than Athena (Glue, Redshift Spectrum) must read the same table, or when partition layouts are irregular and cannot be described by ranges or patterns.

Certification practice

Completion checklist

  • I deployed HlM07-DataLake and read the CDK change summary
  • I created both tables and ran both queries
  • I recorded Data scanned for each and explained the ratio and the 10 MB minimum
  • I ran or read the CTAS and know why it has no external_location
  • I dropped the tables, deleted the objects and checked the tag query (or kept the stack for Lab 7.4 knowingly)

Key takeaways

  • Athena bills by bytes scanned; columnar, partitioned, compressed Parquet scans a fraction of what CSV does.
  • At lab scale the bill is the 10 MB minimum for both, so measure bytes, not dollars.
  • Partition projection removes the crawler and the partition metadata for regular layouts, but only Athena uses it.
  • An enforcing workgroup caps scans and fixes the encrypted result location, which also constrains CTAS.
  • Keep lab prefixes (lab73/) away from the pipeline trigger prefix (incoming/).

Further reading

Independent learning resource โ€” not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.

On this page