Overview
A student club tracks fundraiser orders in a CSV file. Finance needs a corrected, queryable report without managing a database server. Build a small lakehouse exercise and document how a corrected order changes the report.
Architecture
Raw CSV objects live in a private S3 prefix. Athena reads table definitions from the Glue Data Catalog, then writes a separate Iceberg table in a curated prefix. SQL updates change the curated dataset; the raw source remains available for comparison.
This is a small analytical workflow, not a payment processing system. Explain why an analytical table is different from an application's transaction database.
Prerequisites
- Complete Query and correct orders with Athena.
- SQL fundamentals: SELECT, COUNT, SUM, GROUP BY.
- An AWS learning account permitted to use S3, Athena, and the Glue Data Catalog.
Steps
1. Establish a known baseline
Follow the prerequisite lab through its correction and verification steps. Keep its isolated prefixes and database names. Before cleanup, confirm three curated orders with revenue 287.00. If the lab has already been cleaned up, recreate it using its instructions.
2. Add a reproducible report
Run this query in the same workgroup:
SELECT customer, COUNT(*) AS orders, SUM(total) AS revenue
FROM cloudadhar_orders.orders
GROUP BY customer
ORDER BY revenue DESC;Record the output and the total bytes scanned reported by Athena. Explain what each output column means.
3. Apply a second correction
For this project's disposable dataset, change order 1002 from 149.00 to 159.00 using the lab's MERGE pattern. Run the summary again. The overall revenue should become 297.00 and the order count should remain three. Run the same correction a second time and verify unchanged results.
4. Compare source and curated data
Query the raw table and the curated table for order 1002. Explain why the raw CSV still reports the original value. Write down which dataset is the reporting source of truth for this project and how you would track corrections in production.
5. Prepare the evidence
Save your original diagram, SQL queries, redacted output, correction history, and observed scan sizes. Explain one scale-up consideration: partitioning, file sizes, access control, or duplicate source keys. Do not claim the tiny dataset proves production performance.
Verification
| Test | Expected |
|---|---|
| Baseline after prerequisite lab | 3 orders, 287.00 revenue |
| Second correction | 3 orders, 297.00 revenue |
| Repeat same correction | No additional change |
| Raw versus curated order 1002 | Different values with a documented reason |
Cost and cleanup
Athena queries scan data; S3 and catalog usage may also incur charges. Keep the dataset small and use the dedicated workgroup. Follow the prerequisite lab's exact teardown: remove the Iceberg and raw tables, database, S3 lab bucket contents and bucket, and workgroup. Confirm the resources are gone. Save results locally before teardown.