Build a Retail Data Lake on AWS

Build a raw-to-curated retail data lake with S3, Glue, and Athena.

Introduction

30 Second Summary

Sales reports often begin with messy source files. A blank region or negative quantity can quietly distort the answer.

In this project, you will build a retail data lake on AWS that turns imperfect order records into a trustworthy regional sales report. The finished lake keeps the original rows alongside a clean dataset for analysis.

What You'll Build

Picture an Amazon Athena query returning a four-region sales report topped by East at 230.0 total revenue.

By the end of this project, you'll have:

A queryable raw dataset that preserves the original orders.csv in a private Amazon S3 location. You can preview all ten rows through metadata from the AWS Glue Data Catalog.
A repeatable quality check that exposes three invalid orders. Each rejection points to a negative quantity, a missing region, or a zero price.
A curated sales dataset containing seven valid records in Apache Parquet format. Your final query ranks all four regions by revenue.
Secret Mission: Preserve the three rejected records in a separate Parquet quarantine table for investigation.

Are there any prerequisites?

You need an AWS account with administrator access plus a Mac running Safari. Intermediate SQL is the only knowledge prerequisite.

Before We Start

You are committing to build a private raw-to-curated retail data lake that turns imperfect order records into a trustworthy sales report. Keeping raw Amazon S3 data separate from cleaned curated data preserves the source evidence behind every decision.

Set Up the Raw Data Zone

A retail data pipeline needs a controlled source before AWS can catalog it. A raw data zone in Amazon S3 preserves the original orders for traceability.

You'll use the AWS Management Console to anchor every service in one Region.

You'll build orders.csv in TextEdit. You'll then place it in private storage.

In this step, get ready to:

Keep your AWS services aligned to US East (N. Virginia).
Create orders.csv as a plain-text source file.
Store the source file beneath the private raw/orders/ prefix.

Select one AWS Region

AWS services create regional resources in the Region shown in the console. Keeping this project in us-east-1 prevents its storage from becoming separated from later services.

Press Cmd+Space to open the macOS search bar.
Type Safari to find Safari.
Press Enter to open Safari.
Open the AWS Management Console in Safari.
Sign in with your existing AWS administrator account.
Select US East (N. Virginia) from the Region menu in the console header.
Confirm that the selected Region has the code us-east-1.

Your first guardrail is in place. Every regional resource in this project now has one home Region.

Create the source CSV

A CSV stores tabular data as plain text. Plain text keeps the rows free from hidden document formatting.

Press Cmd+Space to return to the macOS search bar.
Type TextEdit to find TextEdit.
Press Enter to open TextEdit.
Click Format in the menu bar.
Click Make Plain Text.
Paste this retail dataset into the blank document:

order_id,order_date,region,product,quantity,unit_price
1001,2026-08-01,North,Laptop Stand,2,45.00
1002,2026-08-01,South,Mechanical Keyboard,1,85.00
1003,2026-08-02,North,USB Hub,-1,30.00
1004,2026-08-02,,Webcam,1,70.00
1005,2026-08-03,West,Monitor Arm,2,0.00
1006,2026-08-03,East,USB Hub,3,30.00
1007,2026-08-04,South,Laptop Stand,1,45.00
1008,2026-08-04,West,Mechanical Keyboard,2,85.00
1009,2026-08-05,East,Webcam,2,70.00
1010,2026-08-05,North,Monitor Arm,1,120.00

What does this data contain?

The first row names the six fields that describe each order.
The next ten rows provide the source records for the retail pipeline.
Keeping every row unchanged gives later quality checks a stable source to inspect.

Click File in the menu bar.
Click Save.
Enter orders.csv in the Save As field.
Choose your Desktop as the save location.
Click Save.
Confirm that the TextEdit title bar shows orders.csv.

File saved with extra formatting?

If TextEdit adds a rich-text extension, return to the Format menu. Choose Make Plain Text before saving the file again.

Still stuck? Help me save this dataset as a plain-text CSV in TextEdit.

✔️ Awesome, I've got everything!

Great. Double check that orders.csv is saved on your Desktop as a plain-text file.

ⓧ I'd like to double check the full code

Compare your orders.csv with this complete file line by line.

order_id,order_date,region,product,quantity,unit_price
1001,2026-08-01,North,Laptop Stand,2,45.00
1002,2026-08-01,South,Mechanical Keyboard,1,85.00
1003,2026-08-02,North,USB Hub,-1,30.00
1004,2026-08-02,,Webcam,1,70.00
1005,2026-08-03,West,Monitor Arm,2,0.00
1006,2026-08-03,East,USB Hub,3,30.00
1007,2026-08-04,South,Laptop Stand,1,45.00
1008,2026-08-04,West,Mechanical Keyboard,2,85.00
1009,2026-08-05,East,Webcam,2,70.00
1010,2026-08-05,North,Monitor Arm,1,120.00

How should the file match?

The file should contain one header row followed by ten order rows. Matching every comma preserves the six-column structure.

Create the private raw zone

An S3 bucket gives the raw zone a durable storage boundary. The source file remains available when later transformations create new datasets.

This creates a pay-as-you-go cloud resource. The file is tiny, but storage charges and request charges can apply until you clean up the bucket.

Why Amazon S3?

Amazon S3 stores the source as an object without requiring a database server. That keeps this pipeline focused on storage boundaries.

A warehouse introduces its own compute model before the raw file is cataloged. S3 gives the pipeline a simple starting point.

Draft a unique bucket name from the pattern nw-retail-lake-yourname-1234.
Record your finished bucket name here: nw-retail-lake-yourname-1234.

Your recorded name now gives the bucket a reusable identifier for later steps.

Switch back to Safari.
Enter S3 in the AWS Management Console search bar.
Select Amazon S3 from the search results.

You'll see the Amazon S3 console with its bucket navigation.

Click General purpose buckets in the left navigation pane.
Click Create bucket.

You'll see the form for a new general purpose bucket.

Keep the default general purpose bucket type.
Enter nw-retail-lake-yourname-1234 in the Bucket name field.

The bucket form now shows the unique name you recorded.

Select US East (N. Virginia) as the AWS Region.
Confirm that the Region code is us-east-1.

The bucket now matches the Region selected in the console header.

Keep Bucket owner enforced under Object Ownership.
Keep every Block Public Access setting enabled.

These settings keep the source data private under the bucket owner's control.

Click Create bucket.

That's your storage boundary created. You'll see the new bucket in the general purpose buckets list.

Bucket name unavailable?

S3 bucket names must be unique. Change the four digits in your name before trying again.

Update the recorded bucket name here: nw-retail-lake-yourname-1234.

Need help? Help me create a unique S3 bucket name from this project pattern.

Click nw-retail-lake-yourname-1234 in the bucket list.
Select the Objects tab.
Click Create folder.
Enter raw as the folder name.
Click Create folder.

You'll see the raw folder listed on the Objects tab.

Click the raw folder.
Click Create folder.
Enter orders as the folder name.
Click Create folder.

You'll see the orders folder inside raw.

What is an S3 prefix?

Amazon S3 stores each file as an object with a key. The console displays shared key prefixes as folders.

The raw/orders/ prefix groups source order objects. Uploading orders.csv inside it creates the object key raw/orders/orders.csv.

Click the orders folder.
Click Upload.
Click Add file.
Select orders.csv from your Desktop.
Click Open.
Click Upload.

You'll see a success message on the Upload: status page.

Before you refresh, what object do you expect to see inside raw/orders/?

Return to the raw/orders/ prefix using the bucket breadcrumb.
Press Cmd+R to refresh the page.
Confirm that orders.csv appears in the object list.

You should see orders.csv listed beneath raw/orders/. Your original retail dataset is now stored in its private raw zone.

Don't see orders.csv?

Check that the breadcrumb shows raw followed by orders.
Return to the upload status page to confirm that the upload completed successfully.
Upload the Desktop copy of orders.csv again if the object list is still empty.

Still missing the object? Help me find why orders.csv is not visible under my S3 raw/orders prefix.

Your source file now has a private home with a traceable object key. Next, you'll turn that stored file into a table that Athena can query.

Catalog and Preview the Raw Orders

Your raw CSV is safely stored in Amazon S3. The object contains the records, but it does not provide the queryable column definitions that your data lake needs.

In this step, you will use the AWS Glue Data Catalog to describe those columns. An AWS Glue crawler will register the table. Amazon Athena will then preview all ten source rows.

In this step, get ready to:

Create a custom CSV classifier with fixed headings and data types.
Run an on-demand crawler that registers the raw orders table.
Configure Athena query results and preview all ten orders.

Create a deterministic CSV classifier

A custom classifier tells Glue exactly how to interpret the CSV. Fixed headings prevent the catalog from falling back to generic names such as column numbers.

Switch back to the AWS Management Console tab in Safari.
Confirm the console header shows US East (N. Virginia).
Use the console search bar to select AWS Glue.
Select Classifiers from the left navigation.
Use the page control to create a classifier.
Choose CSV as the classifier type.
Enter retail_orders_csv as the classifier name.
Set the delimiter to a comma.

You should now see the CSV-specific heading settings and data type settings.

Choose Has headings for the column heading option.
Enter order_id,order_date,region,product,quantity,unit_price as the comma-delimited heading list.
Enter LONG,STRING,STRING,STRING,INT,DOUBLE as the comma-delimited custom datatype list.
Choose Lazy Simple SerDe as the SerDe.
Create the classifier using the page control.

You should see retail_orders_csv in the classifiers list. Its configuration now maps each CSV position to a named column with a defined data type.

Why Use a Custom Classifier?

A classifier reads a data source and produces its schema. Your custom classifier fixes the delimiter, headings, and data types before the crawler examines the file.

Glue tries custom classifiers before its built-in classifiers. This makes the resulting table predictable for every query in the project.

Classifier Details Not Saving?

Check that the heading list contains six values separated by commas.
Check that the datatype list contains six documented datatype values.
Confirm that the classifier name is exactly retail_orders_csv.

Still stuck? Help me check my AWS Glue CSV classifier settings.

Create and run the Glue crawler

The classifier defines how the file should be read. The crawler applies that definition to raw/orders/ and writes the resulting table metadata into the catalog.

Select Crawlers from the AWS Glue navigation.
Choose Create crawler.
Enter retail-orders-crawler as the crawler name.

The crawler properties stage should now show retail-orders-crawler as the crawler name.

Continue to the data sources and classifiers stage.
Choose Amazon S3 as the data source.
Browse to the raw/orders/ prefix inside your project bucket.
Select retail_orders_csv as the custom classifier.

The data source summary should point to the prefix containing orders.csv. It should also show your custom classifier.

The next stage creates an IAM role for the crawler. This role gives Glue permission to read the raw S3 location during the crawl.

Continue to the security settings stage.
Choose the option to create a new IAM role.
Enter RetailLakeCrawler as the role suffix.

Glue should show the complete role name as AWSGlueServiceRole-RetailLakeCrawler.

Continue to the output and scheduling stage.
Use the database control to create retail_lake.
Select retail_lake as the target database.
Enter retail_ as the table prefix.
Choose an on-demand schedule.

The output settings should now combine the retail_ prefix with the table name derived from orders/.

Continue to the review stage.
Create retail-orders-crawler.
Choose Run it now? to start the on-demand crawl.

This first crawl can take a while. Give the status about a minute before using the refresh control.

Refresh the crawler status until the run completes.
Select Tables from the Data Catalog navigation.
Open retail_orders from the retail_lake database.
Confirm that the table lists six named columns.

That is the cataloging work complete. Glue has turned the S3 object into a table definition named retail_orders.

What Did the Crawler Create?

The crawler read the raw object through retail_orders_csv. It stored the resulting table name and column definitions in the Data Catalog.

The CSV still lives under raw/orders/ in S3. The catalog gives Athena the metadata needed to query those bytes as rows.

Table Missing After the Crawl?

Check that the crawler run completed successfully before viewing the tables.
Confirm that the S3 include path ends with raw/orders/.
Confirm that retail_orders_csv is attached to the crawler data source.

Need another pair of eyes? Help me find why my Glue crawler did not create retail_orders.

Preview the raw table in Athena

Athena runs SQL against the table metadata stored in Glue. Its query output also needs an S3 location, so your project bucket will keep the results beside the raw data.

Use the AWS Management Console search bar to select Amazon Athena.
Select the primary workgroup.
Choose Settings.
Choose Manage.
Replace your-unique-bucket-name in s3://your-unique-bucket-name/athena-results/ with the exact bucket name from Step 1.
Enter the completed S3 path in Location of query result.
Choose Save.

The primary workgroup should now show your athena-results/ prefix as its query result location.

Return to the Athena query editor.
Select retail_lake as the database.
Switch back to TextEdit.
Create a new document.
Choose Format.
Choose Make Plain Text.
Add the raw preview query to the new document by pasting this code:

SELECT *
FROM retail_lake.retail_orders
ORDER BY order_id
LIMIT 10;

What Does This Query Do?

The SELECT * clause requests every cataloged column.
The FROM retail_lake.retail_orders clause points Athena to the raw orders table.
The ORDER BY order_id clause sorts the rows by order number.
The LIMIT 10 clause caps the preview at ten rows.

Choose File.
Choose Save.
Save the document as preview-raw-orders.sql beside orders.csv.
Confirm that the TextEdit title bar shows preview-raw-orders.sql.
Copy the query from TextEdit.
Return to the Athena query editor.
Paste the query into the editor.

TextEdit Changed the File Extension?

Confirm that the saved filename ends with .sql. Make sure the document remains plain text.

Still having trouble? Help me save a plain-text SQL file in TextEdit.

Before you run the query, how many rows do you expect Athena to return from the ten-row source file?

Choose Run to execute the query.

The Results pane should display all ten order rows in order_id order. You should see order IDs from 1001 through 1010.

You have made the raw CSV queryable without moving it out of S3. Athena can now read the cataloged source as a table.

Query Did Not Return Ten Rows?

Confirm that retail_lake is selected as the Athena database.
Check that the query references retail_lake.retail_orders exactly.
Confirm that the crawler used the S3 prefix containing orders.csv.

Still blocked? Help me troubleshoot my Athena raw orders preview query.

✔️ Awesome, I've got everything!

Great. Double check that preview-raw-orders.sql is saved. Confirm that Athena displays all ten source rows.

ⓧ I'd like to double check the full code

Here is the complete preview-raw-orders.sql file. Compare it with yours line by line.

SELECT *
FROM retail_lake.retail_orders
ORDER BY order_id
LIMIT 10;

Your raw data is cataloged and queryable. Next, you will test its quality rules and expose the three records that should never reach a sales report.

Find Invalid Orders

The raw orders are now queryable in Amazon Athena. AWS Glue metadata turns the Amazon S3 object into rows you can inspect.

Queryable data can still contain records that distort a sales report. In this step, you create a data quality check for missing regions plus quantities or prices that are not positive.

In this step, get ready to:

Translate three business rules into SQL predicates.
Run a repeatable quality query against retail_lake.retail_orders.
Trace each rejected row back to orders.csv.

Turn the business rules into predicates

A SQL predicate is a condition that evaluates each row. Combining conditions with OR keeps a row in the results when any quality rule fails.

Treat a missing region as invalid with region IS NULL or region = ''.
Reject non-positive quantities with quantity <= 0.
Reject non-positive prices with unit_price <= 0.

Why Check a Region Twice?

The condition region IS NULL catches a missing value. The condition region = '' catches an empty string.

Using both conditions protects the report from either representation of a missing region.

Run the quality check

The query returns every source column for any row that breaks at least one rule. This keeps each rejection traceable to the original order.

Select a blank query tab in the Athena query editor from earlier.
Create find-invalid-orders.sql by pasting this query into the blank editor:

SELECT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price
FROM retail_lake.retail_orders
WHERE region IS NULL
   OR region = ''
   OR quantity <= 0
   OR unit_price <= 0
ORDER BY order_id;

What Does This Query Do?

The SELECT list returns all six source fields so each rejected record retains its context.
The WHERE clause applies the quality rules to every order.
The OR conditions include a row as soon as one rule fails.
The ORDER BY order_id clause places the rejected orders in a predictable sequence.

Before you run the check, how many of the ten source rows do you think will fail at least one rule?

Choose Run in the Athena query editor.

The Results pane shows exactly three rows. Their order IDs are 1003, 1004, and 1005.

That check did its job. You now have a repeatable boundary between source data and trusted data.

Results Do Not Show Three Rows?

Confirm that retail_lake remains selected in the query editor.
Check that the source line reads FROM retail_lake.retail_orders.
Check that the empty-region predicate contains two single quotes in region = ''.

Still stuck? Help me troubleshoot why my Athena quality query does not return exactly three rows.

Trace the rejected rows to the source

A quality result becomes actionable when each rejection points to a specific source value. The matching lines in orders.csv show why all three orders failed.

Compare the three Athena result rows with these matching source lines:

1003,2026-08-02,North,USB Hub,-1,30.00
1004,2026-08-02,,Webcam,1,70.00
1005,2026-08-03,West,Monitor Arm,2,0.00

Why Each Order Fails

Order 1003 has quantity -1. It matches quantity <= 0.
Order 1004 has no region value. It matches one of the two missing-region predicates.
Order 1005 has unit price 0.00. It matches unit_price <= 0.

Return to the Athena Results pane from your quality query.
Confirm that the pane contains exactly three rejected rows.

You should see order IDs 1003, 1004, and 1005 in ascending order.

✔️ Awesome, I've got everything!

Great. Your quality query identifies the three invalid orders plus the source value behind each rejection.

ⓧ I'd like to double check the full code

Compare your complete find-invalid-orders.sql query with this version line by line.

SELECT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price
FROM retail_lake.retail_orders
WHERE region IS NULL
   OR region = ''
   OR quantity <= 0
   OR unit_price <= 0
ORDER BY order_id;

Your raw data now has a repeatable quality gate. Next, you will apply those rules to build a clean Parquet dataset for reporting.

Create the Curated Parquet Table

Your quality check exposed three invalid orders in the raw table. Those records would make the sales report unreliable.

You will use Amazon Athena to run a CTAS transformation. The query keeps the valid records while adding an analytics-ready date and revenue.

The result is an Apache Parquet dataset stored in Amazon S3. Its catalog table becomes the trusted source for the regional report.

In this step, get ready to:

Confirm the curated S3 destination contains no existing data.
Create a Parquet table containing the valid orders.
Verify that the curated table has seven rows and physical S3 output.

Confirm the curated destination is empty

A manually specified CTAS destination must contain no data. An empty location prevents Athena from mixing new output with files from an earlier run.

Switch back to the S3 console from earlier.
Choose Buckets.
Select the S3 bucket you created earlier.
Check the object list for curated/orders/.

The curated/orders/ path should be absent or contain zero objects. Athena creates the path when the CTAS query writes its output.

Create the curated Parquet table

A CTAS query creates table metadata in the AWS Glue Data Catalog. The same run writes the selected rows to the chosen S3 location.

Return to the Athena query editor from earlier.
Select the SQL left in the editor from your previous query.
Replace the selected SQL by pasting this query:

CREATE TABLE retail_lake.curated_orders
WITH (
    format = 'PARQUET',
    external_location = 's3://your-unique-bucket-name/curated/orders/'
)
AS
SELECT
    order_id,
    CAST(order_date AS DATE) AS order_date,
    region,
    product,
    quantity,
    unit_price,
    quantity * unit_price AS revenue
FROM retail_lake.retail_orders
WHERE region IS NOT NULL
  AND region <> ''
  AND quantity > 0
  AND unit_price > 0;

What does this query do?

CREATE TABLE retail_lake.curated_orders creates the catalog table for the clean dataset.
format = 'PARQUET' stores the output in a columnar format suited to analytics.
external_location connects the catalog table to its physical S3 output.
CAST(order_date AS DATE) converts each source date string into a typed date.
quantity * unit_price AS revenue calculates the sales value for each valid order.
The WHERE predicates keep rows with a present region.
The positive-value predicates exclude invalid quantities and prices.

Replace your-unique-bucket-name with the exact S3 bucket name you created earlier.
Confirm that the destination ends with curated/orders/.

Before you run this, which source records do you expect the quality predicates to exclude?

Choose Run in the Athena editor.

Athena should complete the query successfully. The retail_lake.curated_orders table is now available in the catalog.

The curated output is stored under curated/orders/. You have crossed the raw-to-curated boundary with clean records ready to verify.

CTAS query did not complete?

Cleaning a failed CTAS attempt can feel risky. Limit any cleanup to the generated curated/orders/ output and partial curated_orders metadata.

Remove existing objects under curated/orders/ if the destination contains output from an earlier attempt.
Remove the partial curated_orders table metadata if an earlier attempt created it.
Check that the bucket name in external_location exactly matches your project bucket.

Still stuck? Help me troubleshoot my Athena CTAS query.

✔️ Awesome, I've got everything!

Your CTAS query has created the curated table. The destination now holds the query output.

ⓧ I'd like to double check the full code

Compare your query with the complete reference below:

CREATE TABLE retail_lake.curated_orders
WITH (
    format = 'PARQUET',
    external_location = 's3://your-unique-bucket-name/curated/orders/'
)
AS
SELECT
    order_id,
    CAST(order_date AS DATE) AS order_date,
    region,
    product,
    quantity,
    unit_price,
    quantity * unit_price AS revenue
FROM retail_lake.retail_orders
WHERE region IS NOT NULL
  AND region <> ''
  AND quantity > 0
  AND unit_price > 0;

What should match?

This complete reference filters the invalid records before writing the curated Parquet dataset. Keep the placeholder in your reference file while using your actual bucket name in the Athena editor.

Verify the curated rows and output

The row count proves that the quality predicates excluded the three failed records. The S3 output proves that the catalog table has a physical Parquet dataset behind it.

Select the CTAS query in the Athena editor.
Replace it by pasting this verification query:

SELECT COUNT(*) AS clean_row_count
FROM retail_lake.curated_orders;

What does this query check?

COUNT(*) counts every record in the curated table.
clean_row_count gives the result a clear column name in the Results pane.
retail_lake.curated_orders targets the new table instead of the raw source.

Before you run this, what row count do you expect after excluding three records from the ten-row source?

Choose Run in the Athena editor.

The Results pane should show clean_row_count as 7. That is the quality gap closed: only valid orders reached the curated table.

Row count is not 7?

Confirm that the query reads from retail_lake.curated_orders.
Compare the CTAS predicates with the complete reference above.
Check that the original quality query identified order IDs 1003, 1004, and 1005.

Need another pair of eyes? Help me diagnose an unexpected curated row count.

✔️ Awesome, I've got everything!

Your verification query confirms that the curated table contains seven valid records.

ⓧ I'd like to double check the full code

Compare your verification query with the complete reference below:

SELECT COUNT(*) AS clean_row_count
FROM retail_lake.curated_orders;

What should match?

This complete query counts every row in retail_lake.curated_orders. Its result confirms whether the CTAS filter produced the expected dataset.

Switch back to the S3 console from earlier.
Choose Buckets.
Select the S3 bucket you created earlier.
Select curated/.
Select orders/.

You should see at least one generated object under curated/orders/. That object is the physical Parquet output created by the CTAS query.

No curated object listed?

Confirm that you selected the same bucket used in the CTAS external_location.
Check that you are viewing curated/orders/.
Return to Athena to confirm that the CTAS query completed successfully.

Still cannot find the output? Help me trace my Athena CTAS output in S3.

Your curated table now contains seven typed sales records with calculated revenue. Next up, you will turn those records into a regional sales report.

Produce the Regional Sales Report

Your curated_orders table now holds seven valid orders. Each row includes the revenue calculated in the previous step.

In this step, Amazon Athena uses SQL aggregation to group those orders into a regional sales report.

You will validate the ranking against the source rows. You will also connect the query details to Athena's data-scanned pricing model.

In this step, get ready to:

Aggregate curated orders into one row per region.
Validate the regional totals against orders.csv.
Review Athena query statistics for data scanned.

Build and run the regional summary

Aggregation collapses multiple order rows into one summary row for each region. Aliases give each metric a readable column name.

Switch back to the Amazon Athena editor from earlier.
Replace the current SQL with this regional summary:

SELECT
    region,
    COUNT(*) AS order_count,
    SUM(quantity) AS units_sold,
    SUM(revenue) AS total_revenue
FROM retail_lake.curated_orders
GROUP BY region
ORDER BY total_revenue DESC;

How does this query build the report?

GROUP BY region creates one result row for each sales region.
COUNT(*) counts the valid orders in each group.
SUM(quantity) totals the units sold in each group.
SUM(revenue) totals the sales value in each group.
ORDER BY total_revenue DESC places the highest-revenue group first.

Before you run the query, predict how many regional rows the seven curated orders will produce:

Choose Run in the Athena editor.

The Results pane displays four regional rows. The rows are ordered by total_revenue from highest to lowest.

Report Does Not Show Four Regions?

Confirm the editor is using the retail_lake database.
Check that the query references retail_lake.curated_orders.
Compare each query line with the full-code tab below.

Still stuck? Help me troubleshoot my Athena regional summary query.

✔️ Awesome, I've got everything!

Your regional summary query now returns four grouped rows. Keep it in the Athena editor for the final verification.

ⓧ I'd like to double check the full code

Here is the complete regional-sales-summary.sql query. Compare it with yours line by line.

SELECT
    region,
    COUNT(*) AS order_count,
    SUM(quantity) AS units_sold,
    SUM(revenue) AS total_revenue
FROM retail_lake.curated_orders
GROUP BY region
ORDER BY total_revenue DESC;

Trace the ranking to the source rows

A trustworthy report can be traced back to its source records. A manual check proves that the grouped totals match the seven accepted orders.

Press Cmd+Space to open your Mac search bar.
Type orders.csv into the search bar.
Press Enter to open the existing file in TextEdit.
Exclude order IDs 1003, 1004, and 1005 from your manual totals.
Total the remaining records by region.
Compare your totals with the four rows in the Athena Results pane.

Expected Regional Totals

East has 2 orders. It has 5 units. Its revenue is 230.0.
North has 2 orders. It has 3 units. Its revenue is 210.0.
West has 1 order. It has 2 units. Its revenue is 170.0.
South has 2 orders. It has 2 units. Its revenue is 130.0.

Inspect the query statistics

Athena records execution statistics for each completed query. These details connect the report to the amount of data scanned.

Return to the completed regional summary query in Athena.
Open the details for the completed query.
Find the statistic that reports the amount of data scanned.
Confirm the displayed statement references retail_lake.curated_orders.

The details show execution statistics for the query you just ran. The scanned-data statistic belongs to the curated table used by the report.

How Does Athena Pricing Connect?

Amazon Athena's pay-per-query SQL pricing is based on the amount of data scanned. The official pricing example uses $5 per TB scanned.

Actual rates can vary by Region. The query details connect each report run to this usage model.

Return to the Athena editor containing the regional summary.

Before the final run, predict which region should lead the report after the rows are sorted by revenue:

Choose Run in the Athena editor.

The Results pane orders the rows as East, North, West, and South.

The East row shows an order_count of 2. It shows units_sold of 5.

The same row shows total_revenue of 230.0. This confirms the report matches the valid source records.

That is a strong finish. Your seven clean orders now produce a regional report that you can trace back to its source.

Secret mission

Quarantine the Rejected Orders

Your clean report excludes three bad orders. Build a Parquet quarantine table that preserves those rows for investigation or reprocessing.

Clean Up Your Resources

Your AWS resources stay available after this project. Stored data and future activity can continue to create charges.

Decide whether to keep your resources running, pause them to come back later, or delete them entirely.

Cost warning

This project uses pay-as-you-go services. Amazon S3 storage and requests can continue to incur charges.

Each AWS Glue crawler run adds compute charges. Amazon Athena query charges depend on the amount of data scanned.

The first million metadata objects stored in the AWS Glue Data Catalog are free each month. Your crawler stays idle unless you start it because it uses an on-demand schedule.

Resources you used:

The nw-retail-lake-yourname-1234 Amazon S3 bucket containing every project data object.
The retail_lake AWS Glue database containing the raw table and the Parquet tables.
The retail_orders_csv AWS Glue custom CSV classifier.
The retail-orders-crawler on-demand AWS Glue crawler.
The AWSGlueServiceRole-RetailLakeCrawler IAM role used by the crawler.

Keep everything running

No action needed. Choose this if you want to keep querying the retail data lake or build another transformation from it.

Leave retail-orders-crawler on its on-demand schedule.
Keep the nw-retail-lake-yourname-1234 bucket available for the raw and Parquet datasets.
Return to Athena when you want to query retail_orders or curated_orders or rejected_orders again.

Pause - I'll come back to this later

Stop new processing activity while keeping the data lake available for later. Stored resources remain in your account.

Leave the AWS resources in place.
Avoid starting retail-orders-crawler.
Avoid running additional Athena queries.
Resume the project by returning to Athena when you are ready.

Delete - I don't want to use this again

Remove every cloud resource when you have finished with the project. This cleanup is permanent, so choose this path only when you no longer need the data lake.

Start with the crawler because the remaining AWS Glue resources no longer need to support it.

Return to AWS Glue in your existing AWS Management Console session.
Select Crawlers from the navigation menu.
Select retail-orders-crawler.
Choose Delete.
Complete the confirmation prompt.

You should no longer see retail-orders-crawler in the crawler list.

Go to the database list in the AWS Glue Data Catalog.
Select retail_lake.
Choose Delete.
Complete the confirmation prompt.

The retail_lake database disappears from the catalog. Its tables disappear with it.

The database deletion removes catalog metadata. Your S3 objects remain available until you empty the bucket.

Select Classifiers in AWS Glue.
Select retail_orders_csv.
Choose Delete.
Complete the confirmation prompt.

You should no longer see retail_orders_csv in the classifier list.

Return to IAM in the AWS Management Console.
Select Roles.
Select AWSGlueServiceRole-RetailLakeCrawler.
Choose Delete.
Enter AWSGlueServiceRole-RetailLakeCrawler when prompted.
Choose Delete to confirm.

You should no longer see AWSGlueServiceRole-RetailLakeCrawler in the role list.

Emptying the bucket removes the raw CSV and every Parquet object. It also removes the Athena query-result objects.

Return to Amazon S3 in the AWS Management Console.
Select General purpose buckets.
Select nw-retail-lake-yourname-1234.
Use the bucket emptying control to remove all objects.
Complete the permanent deletion confirmation.

The bucket is now empty. The raw, curated, quarantine, and query-result objects are permanently removed.

Return to General purpose buckets.
Select nw-retail-lake-yourname-1234.
Choose Delete.
Enter nw-retail-lake-yourname-1234 when prompted.
Choose Delete bucket.

You should no longer see nw-retail-lake-yourname-1234 in the bucket list. Your project's chargeable AWS resources are now removed.

Nice Work!

You did it! Your retail data lake now turns imperfect order records into a clean sales report led by East at 230.0 in total revenue.

What you learned:

Built a private raw-data zone in Amazon S3 that keeps the ten-row CSV source beneath raw/orders/.
Created deterministic schema metadata with a custom AWS Glue classifier. Ran an AWS Glue crawler to register retail_lake.retail_orders in the AWS Glue Data Catalog. Previewed all ten source rows in Amazon Athena.
Found three invalid records with a repeatable SQL quality check. Transformed the seven valid rows into an Apache Parquet table with calculated revenue. Produced a regional sales report that ranked East first.
Secret Mission: Preserved the three rejected records in retail_lake.rejected_orders beneath quarantine/orders/.

Ready to quiz yourself?