Build a Serverless Retail Data Pipeline

Build an AWS pipeline that validates orders and creates curated Parquet data.

Introduction

30 Second Summary

A duplicate order can quietly turn a healthy sales report into the wrong business decision. The totals still look believable while the same sale gets counted twice.

In this project, you will build a serverless retail data pipeline on AWS. The pipeline converts a raw CSV batch with inflated revenue into a deduplicated Apache Parquet dataset with a verified SQL total.

What You'll Build

Your Amazon Athena results show the full story: six raw rows inflate revenue to 675, while five curated orders restore the correct total of 525.

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

A private raw zone in Amazon S3 where you can see the uploaded orders.csv batch.
A six-column catalog that stores the discovered order schema in AWS Glue Data Catalog. You can query it in Amazon Athena to expose duplicated order ID 1005.
A curated Apache Parquet table that proves the transformation worked with five unique orders. Its corrected revenue is 525.
Secret Mission: Upload a second daily batch. Incrementally add only its new orders to the curated table.

Are there any prerequisites?

Basic SQL knowledge helps you follow the quality checks.

You'll also need the Windows AWS environment from the prerequisites, including the AWS Command Line Interface (AWS CLI) plus permission to create the project resources.

Before We Start

First, lock in the retail pipeline you are about to build. Connecting duplicate order IDs to inflated revenue gives every later step a clear business purpose.

Prepare Your Windows Workspace

Your retail pipeline is about to move data from your Windows computer into AWS. A wrong command-line identity can direct later work toward the wrong account.

This step confirms that the AWS CLI can identify your account. You also create the local batch that starts the pipeline.

You finish by choosing one AWS Region in the AWS Management Console. Amazon S3, AWS Glue, and Amazon Athena use that same Region throughout the project.

In this step, get ready to:

Verify that the AWS CLI is ready for your account.
Create orders.csv with six raw retail records in Notepad.
Choose one AWS Region for every cloud service in the pipeline.

Verify your AWS CLI identity

The AWS CLI uses stored configuration to decide which credentials apply to a command. The identity response proves which account those credentials represent.

Press the Windows key to open search.
Type Command Prompt into search.
Select Command Prompt.
Check whether the AWS CLI is installed by running:

aws --version

What does this command show?

The command displays the installed AWS CLI version. It also reports environment details for your Windows installation.

✔️ I see AWS CLI details

Your AWS CLI installation is available. Continue with the configuration check below.

ⓧ The command does not run

Windows cannot access the AWS CLI yet. The official installation guide provides the supported Windows setup.

Install the AWS CLI by following the official Windows installation guide.
Return to Command Prompt from earlier.
Verify the installation by running:

aws --version

What confirms the installation?

A successful check prints the AWS CLI version. That output confirms Windows can find the command.

Still unable to run the command?

Close Command Prompt after the installation completes. Start a fresh Command Prompt session so Windows reloads the available commands.

If the command still fails, help me troubleshoot my AWS CLI installation.

Inspect the configuration values available to the CLI by running:

aws configure list

What does this configuration check show?

The output lists the configuration values the CLI can use. It also identifies the source of each value.

Before you check, predict which AWS account the CLI will report.

Request the identity associated with the current credentials by running:

aws sts get-caller-identity

What does the identity response contain?

The UserId field identifies the principal behind the credentials.
The Account field identifies the AWS account.
The Arn field identifies the user or role used by the command.

✔️ I see my AWS identity

That is the first checkpoint complete. Your command-line tools now point to a known AWS identity.

ⓧ The identity check fails

Configuration involves credentials, so keep every access key inside Command Prompt. Never paste those values into project notes or screenshots.

The configuration flow prompts for your AWS Access Key ID, AWS Secret Access Key, default Region, and output format. Use credentials supplied through your AWS account.

Start the AWS CLI configuration flow by running:

aws configure

What does this command configure?

The command collects the credentials used by later AWS CLI requests. It also records a default Region plus an output format.

Enter your AWS Access Key ID when prompted.
Enter your AWS Secret Access Key when prompted.
Enter the Region you plan to use for this project.
Enter your preferred output format when prompted.
Test the configured identity by running:

aws sts get-caller-identity

What confirms the fix?

A successful response contains UserId, Account, and Arn. These fields prove that the CLI can use the configured credentials.

Still unable to identify your account?

Confirm that every credential was copied without extra spaces. Ask your AWS account administrator whether the credentials are active.

If the failure continues, help me diagnose my AWS CLI identity check.

Create the raw orders file

A CSV file stores each order as one comma-separated row. This batch contains the duplicate that later exposes the inflated revenue total.

Press the Windows key to open search.
Type Notepad into search.
Select Notepad.
Add the raw retail batch by pasting this content:

order_id,order_date,region,product,quantity,unit_price
1001,2026-08-01,North,Keyboard,2,45.00
1002,2026-08-01,South,Mouse,3,20.00
1003,2026-08-01,East,Monitor,1,180.00
1004,2026-08-01,West,Keyboard,1,45.00
1005,2026-08-01,North,Dock,2,75.00
1005,2026-08-01,North,Dock,2,75.00

What is inside this batch?

The header defines six fields for each retail order.
The file contains six data rows beneath the header.
The final two rows share order ID 1005. This duplicate becomes the data-quality problem you measure later.

Click File in Notepad.
Choose Save As.
Choose a folder you can locate during the upload step.
Name the file orders.csv.
Choose All Files if Notepad tries to add the .txt extension.
Click Save.
Confirm the Notepad title bar shows orders.csv.

Did Notepad add a text extension?

Use Save As again if the title bar shows a .txt extension. Choose All Files before saving the file as orders.csv.

If the filename still looks wrong, help me save a CSV file correctly in Notepad.

✔️ Awesome, I've got everything!

Your six-row retail batch is saved. The duplicate order is ready for the pipeline to detect.

ⓧ I'd like to double check the full code

Compare your saved orders.csv with this complete file.

order_id,order_date,region,product,quantity,unit_price
1001,2026-08-01,North,Keyboard,2,45.00
1002,2026-08-01,South,Mouse,3,20.00
1003,2026-08-01,East,Monitor,1,180.00
1004,2026-08-01,West,Keyboard,1,45.00
1005,2026-08-01,North,Dock,2,75.00
1005,2026-08-01,North,Dock,2,75.00

What should match?

Confirm that the header appears once. Confirm that both final rows contain order ID 1005.

Choose one AWS Region

An AWS Region is the geographic area where your cloud resources operate. Keeping every service in one Region allows each stage of the pipeline to find the same data and metadata.

This sign-in handles account credentials, so keep every password inside the AWS sign-in flow.

Go to the AWS Management Console in your browser.
Sign in with your AWS account.
Use the Region control in the console header to select one Region that supports all three project services.
Record the selected Region here: your chosen AWS Region.

Why keep one Region?

The pipeline connects an S3 object to Glue metadata and Athena queries. A consistent Region keeps those resources together.

Before the final check, predict which version detail and identity fields the terminal will show.

Return to Command Prompt from earlier.
Confirm the CLI installation plus the active identity by running:

aws --version
aws sts get-caller-identity

What does this final check prove?

The first command proves that Windows can run the AWS CLI. The second command proves that its credentials resolve to an AWS account identity.

You should see AWS CLI version details first. The identity response should include Account plus Arn.

Missing one of the expected results?

Return to the matching outcome tab above for the failed command. Repeat its setup before running the final check again.

For a different failure, help me interpret my AWS CLI verification output.

Switch back to Notepad from earlier.
Confirm the title bar shows orders.csv.
Confirm six data rows appear beneath the header.
Confirm the final two rows contain order ID 1005.
Return to the AWS Management Console.
Confirm the Region control shows your chosen AWS Region.

Your Windows workspace now has a known AWS identity, a selected Region, and a saved retail batch. Next up, you place that batch into the pipeline's private raw data zone.

Land the Raw Orders in Amazon S3

Your AWS identity is verified. Your local orders.csv file contains six retail rows with duplicated order ID 1005.

A batch pipeline needs a durable raw zone before schema discovery can begin. In this step, you will place the file in a private Amazon S3 bucket within your selected Region.

In this step, get ready to:

Create a private general purpose S3 bucket in your selected Region.
Build the raw/orders/ prefix for incoming retail batches.
Confirm that orders.csv is stored at the expected object path.

Why Start with a Batch File?

Batch ingestion fits this small orders dataset because the entire file arrives together. A raw S3 zone preserves the source before any quality checks change it.

Streaming delivery adds continuous-delivery decisions that this lab does not need. Starting with one file keeps your focus on storage, metadata, validation, and transformation.

Create a private S3 bucket

An S3 bucket is the top-level container for your raw file. A general purpose bucket name must be globally unique within its AWS partition.

The account ID connects this bucket to your lab. A unique suffix prevents a naming collision with another bucket.

Know the Cost Boundary

Creating this bucket starts a persistent usage-billed resource. This lab keeps its stored data and request activity tiny.

Charges vary by Region and usage. The cleanup section shows you how to remove the bucket when you finish.

Switch back to the AWS Management Console session from earlier.
Use the console's top search bar to search for Amazon S3.
Select Amazon S3 from the search results.
Choose General purpose buckets from the navigation pane.
Choose Create bucket.

You will see the bucket creation form. This form controls the bucket's name, Region, ownership settings, and public access settings.

Select the same Region you chose in Step 1.
Enter a lowercase name in the Bucket name field using the pattern nextwork-retail-<your-account-id>-<unique-suffix>.

The form now shows your selected Region beside your unique bucket name.

Keep Bucket owner enforced selected.
Keep all four Block Public Access settings enabled.
Choose Create bucket.

That is the storage foundation in place. Your first batch now has a private landing point.

Record the exact bucket name here: nextwork-retail-<your-account-id>-<unique-suffix>.

Bucket Name Unavailable?

Choose a different lowercase suffix if Amazon S3 reports that the bucket name is unavailable.
Check that the bucket name starts with a letter or number.
Check that the bucket name ends with a letter or number.
Help me fix my S3 bucket name.

Create the raw prefix and upload the batch

An S3 prefix groups objects under a shared path. The console displays prefixes as folders.

The raw/orders/ prefix keeps source batches together for the crawler that discovers their schema.

Choose nextwork-retail-<your-account-id>-<unique-suffix> from the bucket list.
Choose Create folder on the Objects tab.

You will see the form for creating the first folder in your bucket.

Enter raw as the folder name.
Choose Create folder.

You will see the raw folder in the bucket's object list.

Choose the raw folder.
Choose Create folder.

You will see the form for creating a folder inside raw.

Enter orders as the folder name.
Choose Create folder.

You will see the orders folder inside raw.

Choose the orders folder.
Choose Upload.
Choose Add files.
Select the local orders.csv file from earlier.
Choose Open.
Choose Upload.

You will see a successful upload status for orders.csv. Your six-row batch is now stored in the raw zone.

Upload Missing or Misplaced?

Check that the selected filename ends with .csv.
Confirm that the breadcrumb shows raw/orders/ before repeating the upload.
Help me find my S3 upload.

Verify the raw object path

An object key identifies a file inside a bucket. The folder prefixes form part of that key.

Before you check, what should the breadcrumb and object list show if the upload landed in the correct place?

Return to your bucket's Objects tab.
Choose the raw folder.
Choose the orders folder.
Confirm that orders.csv appears in the object list.

You will see a breadcrumb ending in raw/orders/. The object list will contain orders.csv.

The full location is s3://nextwork-retail-<your-account-id>-<unique-suffix>/raw/orders/orders.csv.

Return to the bucket root by choosing the bucket name in the breadcrumb.
Confirm that the object list has no curated folder.

Your curated/orders/ destination remains absent. The bucket contains the raw batch without transformed output.

Why Leave the Curated Prefix Empty?

Later, Amazon Athena uses a CTAS transformation to write deduplicated data into curated/orders/.

The CTAS external_location must contain no data when the statement runs. Leaving the prefix absent gives Athena an empty destination.

That is your first pipeline checkpoint complete. Your private raw zone now holds the retail batch that drives the rest of the project.

Next, AWS Glue will discover the file's six-column schema and store it as reusable metadata.

Catalog the Raw Data with AWS Glue

Your private Amazon S3 raw zone now holds orders.csv. Amazon Athena needs a stable schema before it can query those rows reliably.

A custom classifier tells an AWS Glue crawler how to interpret the file. The crawler stores the result in AWS Glue Data Catalog.

In this step, get ready to:

Define a custom CSV classifier with deterministic column names and data types.
Configure an on-demand crawler with access to the raw S3 path.
Run the crawler to generate the six-column orders table.

Define the raw CSV schema

CSV files store every row as comma-separated text. The classifier fixes the headings and numeric types needed by later SQL queries.

Search for AWS Glue in the AWS Management Console search bar.
Select AWS Glue from the search results.

You should see the AWS Glue console in the Region selected earlier.

Select Classifiers from the left navigation.
Choose Add classifier.

You should see a form for defining the classifier.

Enter retail-orders-csv as the classifier name.
Choose CSV as the classifier type.

The form should now display the CSV-specific settings.

Set Column delimiter to ,.
Set Quote symbol to ".

Why define the columns?

The delimiter tells the classifier where each value ends. The quote symbol keeps quoted values together when they contain commas.

Explicit headings give every value a stable SQL name. Explicit data types keep revenue calculations predictable.

Choose Has headings under Column headings.
Enter order_id,order_date,region,product,quantity,unit_price as the headings.

The headings field should show six comma-separated column names.

Enter STRING,STRING,STRING,STRING,INT,DOUBLE under Custom datatype.
Choose Lazy Simple SerDe under CSV Serde.

The final two fields should show six custom types plus Lazy Simple SerDe.

Submit the completed form.

Good progress. You should see retail-orders-csv in the Classifiers list with its CSV configuration saved.

Classifier missing or incomplete?

Refresh the Classifiers list if retail-orders-csv does not appear.
Reopen retail-orders-csv to confirm that the headings contain six comma-separated values.
Check that the custom data types contain four STRING values followed by INT plus DOUBLE.
Ask for help with the classifier settings: Help me check my AWS Glue CSV classifier configuration.

Configure the crawler

The crawler needs permission to read the raw S3 path. An IAM service role gives it access to that source.

Select Crawlers from the AWS Glue left navigation.
Choose Create crawler.

You should see the crawler wizard open on its first configuration page.

Enter retail-orders-crawler in the Crawler name field.
Continue to the data source page.

The data source page connects the crawler to the file it needs to inspect.

Choose Amazon S3 as the data source.
Enter s3://nextwork-retail-<your-account-id>-<unique-suffix>/raw/orders/ in Include path.

The source should point to the raw/orders/ prefix that contains orders.csv.

Select retail-orders-csv in the custom classifiers setting.
Continue to the IAM role page.

How does the source stay scoped?

The include path limits this crawler to the raw orders folder. The classifier then applies the schema rules you just defined.

Choose Create an IAM role.
Enter retail-orders-crawler as the IAM role suffix.

What does the crawler role do?

The wizard adds AWSGlueServiceRole- to the suffix. The resulting role is AWSGlueServiceRole-retail-orders-crawler.

This role lets the crawler read the selected S3 source. It also lets the crawler write metadata to the Data Catalog.

Continue to the scheduling page.
Set Frequency to Run on demand.

The crawler now has an on-demand schedule. It only runs when you start it.

Continue to the output configuration page.
Choose Add database.

You should see a form for the Data Catalog database.

Enter retail_pipeline_db as the database name.
Choose Create.

The lowercase retail_pipeline_db database should now be selected as the crawler target.

Leave Prefix added to tables empty.
Continue to the crawler review page.

The review should show the raw S3 source plus the on-demand schedule.

Choose Finish to create the crawler.

You should see retail-orders-crawler in the Crawlers list. Its details should show AWSGlueServiceRole-retail-orders-crawler as the IAM role.

Crawler configuration not saving?

Confirm that the S3 include path ends with raw/orders/.
Confirm that the AWS Management Console still uses the Region selected earlier.
Check that your AWS identity can create the crawler-specific IAM role.
Ask for help with the crawler wizard: Help me troubleshoot my AWS Glue crawler configuration.

Generate the catalog table

Running the crawler turns the saved configuration into reusable table metadata. The crawler reads the CSV structure without changing orders.csv.

Keep the crawler run focused

Running a crawler uses paid compute. This lab keeps usage controlled with one tiny on-demand run.

AWS bills crawler time by the second with a 10-minute minimum. Pricing varies by Region.

Before you start, predict the six columns the crawler will catalog.

Select retail-orders-crawler from the Crawlers list.
Choose Run it now?.

The first run can stay in progress for several minutes. That pause is normal while Glue reads the source and updates the catalog.

Use the Refresh control until the crawler run completes.

That's the slow part done. The crawler should now show one successful completed run.

Open retail-orders-crawler from the crawler list.
Select the Crawler runs tab.

You should see one completed run. Its entry should also show the duration plus DPU hours.

Select Data Catalog from the left navigation.
Select Tables.

You should see an orders table inside retail_pipeline_db.

Open the orders table.

The table location should be s3://nextwork-retail-<your-account-id>-<unique-suffix>/raw/orders/.

You should see six columns. order_id, order_date, region, and product should use string.

The quantity column should use int. The unit_price column should use double.

Don't see the expected table?

Confirm that retail-orders-csv is attached to the crawler.
Confirm that the include path points directly to the raw/orders/ prefix.
Use the displayed table name in later SQL if Glue appended a suffix because another orders table already existed.
Ask for help with the catalog result: Help me diagnose why my AWS Glue crawler did not create the expected orders table.

Your raw order file now has reusable catalog metadata. Next, you'll query the table to expose the duplicated order that inflates revenue.

Expose the Duplicate-Order Problem

Your crawler has turned the raw orders file into a six-column table in the AWS Glue Data Catalog. You can now query those records without defining the schema again.

The raw zone contains a repeated business key that can inflate revenue. In this step, you'll use Amazon Athena to measure the damage and identify the duplicate.

In this step, get ready to:

Configure Athena to store query results in your private bucket.
Create a reusable SQL file and measure the raw dataset.
Find the duplicated order that inflated the revenue total.

Configure Athena query results

Every Athena query produces a result file in Amazon S3. A dedicated athena-results/ prefix keeps those files separate from the raw orders.

In the AWS Management Console from earlier, enter Amazon Athena in the search bar.
Select Amazon Athena from the search results.
Choose primary from the Workgroup selector.
Choose retail_pipeline_db from the Database selector.
Open Settings.
Choose Manage.
Enter s3://nextwork-retail-<your-account-id>-<unique-suffix>/athena-results/ in Location of query result.
Choose Save.

The primary workgroup now sends query results to s3://nextwork-retail-<your-account-id>-<unique-suffix>/athena-results/.

Keep Query Runs Focused

Athena charges for the data each query scans. This project uses one tiny CSV file to keep the scan small.

Each query also creates a result object in S3. Run only the statements listed in this project.

Create the SQL reference file

A local SQL file gives you a reusable record of every query in the pipeline. The first query compares the number of physical rows with the number of unique business keys.

You'll use Notepad to save the statements in athena_queries.sql.

Press the Windows key to open Start.
Type Notepad and press Enter to open it.
Add the raw metrics query to the blank document by pasting this code:

-- Run each statement from the Athena query editor after selecting retail_pipeline_db.

-- Raw metrics: the row count and business-key count do not agree.
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS unique_orders,
    SUM(quantity * unit_price) AS reported_revenue
FROM orders;

What does this query measure?

The COUNT(*) expression counts every physical row in the raw table.
The COUNT(DISTINCT order_id) expression counts unique business keys.
The SUM(quantity * unit_price) expression calculates the revenue reported by every raw row.

Click File in Notepad.
Select Save As.

You'll see the Save As window for the new SQL file.

Select the folder that contains orders.csv.
Enter athena_queries.sql in the File name field.

The save location now keeps both pipeline source files together.

Choose All Files from the file type list.
Choose Save.

The Notepad title bar should now show athena_queries.sql without an added .txt extension.

Select the raw metrics statement in athena_queries.sql.
Press Ctrl+C to copy the selected statement.
Return to the Athena query editor from earlier.
Press Ctrl+V to paste the statement into the editor.

Before you run it, will the physical row count match the unique order count? Keep your prediction in mind.

Choose Run to execute the raw metrics statement.

Under row_count, you'll see 6. Under unique_orders, you'll see 5.

Under reported_revenue, you'll see 675. The mismatched counts prove that the raw dataset contains an extra row.

You have surfaced the bad business result. The raw data reports more revenue than five unique orders should produce.

Query Did Not Return the Metrics?

Confirm that retail_pipeline_db is selected in the database selector.
Confirm that the table name in the query matches orders in the Data Catalog.
Check that the primary workgroup has the saved S3 result location.
Ask for help with an Athena raw metrics query that does not return results.

Find the duplicated order

A row-count mismatch proves that the dataset has an extra record. Grouping by order_id reveals the exact business key that appears more than once.

Return to athena_queries.sql in Notepad.
Place the cursor after the first SQL statement.
Leave one blank line below the first statement.
Add the duplicate-detection statement by pasting this code:

-- Find the duplicated business key.
SELECT
    order_id,
    COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

How does this query find duplicates?

The GROUP BY order_id clause collects rows that share the same order ID.
The COUNT(*) AS copies expression reports how many rows each order ID owns.
The HAVING COUNT(*) > 1 clause keeps only order IDs with multiple rows.

Save athena_queries.sql.
Confirm that the duplicate statement ends with HAVING COUNT(*) > 1.

Duplicate Query Looks Incomplete?

Check that GROUP BY order_id appears before the HAVING clause.
Check that the query reads from the orders table.
Ask for help with checking the duplicate-detection SQL.

The same reference file also carries the transformation statement for the curated layer. Saving it now gives the next step a complete SQL source.

Place the cursor after the duplicate-detection statement.
Leave one blank line below the duplicate-detection statement.
Append the curated table statement by pasting this code:

-- Create the curated table once. Replace the bucket placeholder before running.
CREATE TABLE curated_orders
WITH (
    format = 'PARQUET',
    external_location = 's3://your-unique-bucket-name/curated/orders/'
)
AS
SELECT DISTINCT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price
FROM orders;

What does the curated statement prepare?

The CREATE TABLE curated_orders statement defines the curated table used by the next transformation.
The format = 'PARQUET' property selects the curated storage format.
The SELECT DISTINCT clause removes exact duplicate rows from the raw table.

Keep your-unique-bucket-name unchanged in this step.
Save athena_queries.sql.
Confirm that the external_location line points to the empty curated/orders/ prefix.

Curated Destination Does Not Match?

Confirm that the destination ends with /curated/orders/.
Restore your-unique-bucket-name if the placeholder was changed during this step.
Ask for help with checking the CTAS destination in athena_queries.sql.

The final saved statement measures the curated table after the transformation. It uses the same three metrics as the raw query so the results remain directly comparable.

Place the cursor after the curated table statement.
Leave one blank line below the curated table statement.
Append the curated metrics statement by pasting this code:

-- Validate the corrected curated result.
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS unique_orders,
    SUM(quantity * unit_price) AS reported_revenue
FROM curated_orders;

Why reuse the same metrics?

Matching aliases make the raw and curated outputs easy to compare. Any change in row count or revenue comes from the transformation.

Save athena_queries.sql.
Confirm that the final query reads from curated_orders.

Final Statement Missing?

Check that the final statement appears below the CREATE TABLE curated_orders statement.
Check that all three aliases match row_count, unique_orders, and reported_revenue.
Ask for help with checking the completed Athena SQL file.

✔️ Awesome, I've got everything!

Your athena_queries.sql file now contains all four statements in the required order.

ⓧ I'd like to double check the full code

Compare your saved athena_queries.sql file with this complete version.

-- Run each statement from the Athena query editor after selecting retail_pipeline_db.

-- Raw metrics: the row count and business-key count do not agree.
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS unique_orders,
    SUM(quantity * unit_price) AS reported_revenue
FROM orders;

-- Find the duplicated business key.
SELECT
    order_id,
    COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Create the curated table once. Replace the bucket placeholder before running.
CREATE TABLE curated_orders
WITH (
    format = 'PARQUET',
    external_location = 's3://your-unique-bucket-name/curated/orders/'
)
AS
SELECT DISTINCT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price
FROM orders;

-- Validate the corrected curated result.
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS unique_orders,
    SUM(quantity * unit_price) AS reported_revenue
FROM curated_orders;

In Notepad, select the duplicate-detection statement from SELECT through HAVING COUNT(*) > 1.
Press Ctrl+C to copy the selected statement.
Return to the Athena query editor.
Press Ctrl+V to paste the statement into the editor.

Before you run it, predict how many order IDs will survive the duplicate filter. Keep your prediction in mind.

Choose Run to execute the duplicate-detection statement.

You'll see one result row. Its order_id is 1005.

The copies value is 2. This confirms that order 1005 appears twice.

You've found the exact row behind the bad metric.

The extra copy contributes 150 in revenue. That lifts the correct total of 525 to the raw total of 675.

Duplicate Result Did Not Appear?

Confirm that the query editor contains the duplicate-detection statement instead of the raw metrics statement.
Confirm that retail_pipeline_db remains selected.
Confirm that the query groups by order_id before applying the HAVING filter.
Ask for help with an Athena duplicate query that does not return order 1005.

Your raw-data check has exposed the duplicated order and its inflated revenue. Next up, you'll remove that duplicate and write a curated Parquet dataset.

Build the Curated Parquet Layer

Amazon Athena has exposed the duplicate in your raw data. The repeated 1005 order adds 150 to the reported revenue.

That flaw raises the raw result from 525 to 675. A trustworthy analytics table needs one row for each order.

This step uses CTAS to create a curated table from the raw rows. SELECT DISTINCT removes the exact duplicate before Athena writes the corrected data as Apache Parquet files in Amazon S3.

In this step, get ready to:

Personalize the CTAS statement with your bucket name.
Create the deduplicated curated table.
Validate the corrected metrics and inspect the Parquet output.

Why CTAS for this pipeline?

An AWS Glue ETL job adds Spark job setup to this six-row batch. CTAS keeps the transformation in SQL inside Athena.

The same statement creates the catalog table and writes the Parquet files. This keeps your attention on the data-quality result.

Create the curated table

The CTAS destination must contain no data when Athena writes to it. Your curated/orders/ prefix is empty at this point.

Keep This to One Run

This transformation scans the six-row source and writes new S3 objects. Both activities are usage-based.

The tiny source keeps this run small. The statement is intended for one execution in this step.

Replace the previous query in the Athena query editor with the CTAS template by pasting this code:

-- Create the curated table once. Replace the bucket placeholder before running.
CREATE TABLE curated_orders
WITH (
    format = 'PARQUET',
    external_location = 's3://your-unique-bucket-name/curated/orders/'
)
AS
SELECT DISTINCT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price
FROM orders;

What Does This Statement Do?

The CREATE TABLE curated_orders clause creates the curated table in retail_pipeline_db.
The format = 'PARQUET' property stores the transformed rows in a columnar format designed for analytics.
The external_location property sends those files to the curated/orders/ prefix in your bucket.
The SELECT DISTINCT clause keeps one copy of each complete order row.

Replace your-unique-bucket-name in the query editor with the bucket name you recorded earlier.
Run the CTAS statement once.

You should see Athena report a successful query. The curated_orders table is now registered.

That is the transformation written. Athena has created a curated table from your raw orders.

CTAS Query Failed?

Confirm retail_pipeline_db is still selected in Athena.
Check that the bucket name in external_location exactly matches your S3 bucket.
Treat a message about existing destination data as a sign that the curated/orders/ prefix is no longer empty.
Help me inspect a failed CTAS query before I change or delete anything.

Update the CTAS statement in athena_queries.sql by replacing your-unique-bucket-name with your actual bucket name.
Save athena_queries.sql in Notepad.

Your local SQL reference now points to the same curated destination that Athena used.

Validate the corrected metrics

A curated layer earns trust through measurable checks. The row count should now agree with the number of unique order IDs.

Replace the CTAS statement in the Athena query editor with the curated metrics statement by pasting this code:

-- Validate the corrected curated result.
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS unique_orders,
    SUM(quantity * unit_price) AS reported_revenue
FROM curated_orders;

What Does This Check Measure?

The COUNT(*) expression counts every curated row.
The COUNT(DISTINCT order_id) expression counts unique business keys.
The SUM(quantity * unit_price) expression recalculates revenue from the curated rows.

Before you run this check, do you expect the row count to match the unique-order count now?

Run the curated metrics statement.

You should see row_count equal 5. You should also see unique_orders equal 5.

The reported_revenue value should be 525.

Your raw result remains 6 rows with revenue of 675. The curated result proves that removing the duplicate corrected the business metric.

You have closed the data-quality gap. Every curated row now represents one unique order.

Metrics Do Not Show Five Orders?

Confirm the validation query reads from curated_orders.
Check that the CTAS statement contains SELECT DISTINCT.
Confirm the CTAS query completed successfully before running the metrics statement.
Help me explain why my curated Athena metrics do not show five rows and revenue of 525.

✔️ Awesome, I've got everything!

Your SQL reference contains the raw checks, the curated transformation, and the final validation.

ⓧ I'd like to double check the full code

Compare athena_queries.sql with this canonical reference:

-- Run each statement from the Athena query editor after selecting retail_pipeline_db.

-- Raw metrics: the row count and business-key count do not agree.
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS unique_orders,
    SUM(quantity * unit_price) AS reported_revenue
FROM orders;

-- Find the duplicated business key.
SELECT
    order_id,
    COUNT(*) AS copies
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Create the curated table once. Replace the bucket placeholder before running.
CREATE TABLE curated_orders
WITH (
    format = 'PARQUET',
    external_location = 's3://your-unique-bucket-name/curated/orders/'
)
AS
SELECT DISTINCT
    order_id,
    order_date,
    region,
    product,
    quantity,
    unit_price
FROM orders;

-- Validate the corrected curated result.
SELECT
    COUNT(*) AS row_count,
    COUNT(DISTINCT order_id) AS unique_orders,
    SUM(quantity * unit_price) AS reported_revenue
FROM curated_orders;

Your local external_location contains your actual bucket name. Every other line should match this reference.

Inspect the curated outputs

The query result proves the numbers are correct. The final check confirms that Athena also created the physical files and catalog entry.

Before you inspect the outputs, which S3 prefix and table name do you expect to find?

Switch back to your bucket in AWS Management Console.
Choose the Objects tab.

You should see the new curated/ prefix alongside your existing raw data and Athena results.

Open the curated/ prefix.
Open the orders/ prefix.

You should see one or more CTAS output objects. Their presence proves that Athena wrote the curated Parquet data to S3.

Return to the Tables page under AWS Glue Data Catalog.

You should see curated_orders alongside orders in retail_pipeline_db.

Your raw-to-curated pipeline is complete. It now produces five unique orders with validated revenue of 525.

Secret mission

Process a Second Daily Batch

A new daily file has arrived with two more orders. Extend the pipeline with an incremental load that keeps the curated totals unchanged when the insert runs again.

Clean Up Your Resources

Your pipeline uses persistent AWS resources that can keep incurring usage-based charges. Choose the cleanup option that matches what you want to do next.

Cost warning

The files in this lab are tiny. However, the services still use usage-based pricing.

Amazon S3 objects can continue to incur storage charges. New Amazon Athena queries can add scan charges.

The AWS Glue crawler only runs when you start it. Another crawler run can add compute charges.

Resources you used:

The private Amazon S3 bucket named nextwork-retail-<your-account-id>-<unique-suffix> containing objects under raw/orders/, athena-results/, plus curated/orders/.
The retail-orders-csv custom classifier.
The retail-orders-crawler on-demand crawler.
The AWS Glue Data Catalog database retail_pipeline_db containing the orders table plus the curated_orders table.
The IAM service role AWSGlueServiceRole-retail-orders-crawler.

Keep everything running

No action is needed. Choose this option if you are still using the pipeline or plan to extend it soon.

Keep every Block Public Access setting enabled for nextwork-retail-<your-account-id>-<unique-suffix>.
Leave retail-orders-crawler set to Run on demand.
Run the crawler only when new raw files need schema discovery.
Run additional Athena queries only when you need new results.
Expect the retained S3 objects to keep incurring storage charges.

Pause - I'll come back to this later

The crawler already runs on demand, so pausing means leaving the resources in place without starting new work. S3 storage charges can continue while the files remain.

Leave retail-orders-crawler idle.
Avoid running additional Athena queries.
Keep nextwork-retail-<your-account-id>-<unique-suffix> private.
Return to the same AWS Region when you resume the pipeline.

Delete - I don't want to use this again

Deletion is permanent for the AWS resources in this lab. Your local CSV files plus SQL files remain on Windows, so you can rebuild the pipeline later.

A crawler cannot be deleted while its state is RUNNING. Remove the discovery components after its run has finished.

Return to Crawlers in AWS Glue.
Confirm that retail-orders-crawler is not showing RUNNING.
Delete retail-orders-crawler from the crawler list.
Open Classifiers in AWS Glue.
Delete retail-orders-csv from the classifier list.

The discovery components are now gone. The catalog metadata is next.

Open Tables under Data Catalog in AWS Glue.
Delete the orders table.
Delete the curated_orders table.
Open the database list under Data Catalog.
Delete the retail_pipeline_db database.

The S3 bucket still holds the raw batches, Athena results, plus curated Parquet objects. Amazon S3 requires you to empty the bucket before deleting it.

Return to Amazon S3.
Open General purpose buckets.
Select nextwork-retail-<your-account-id>-<unique-suffix>.
Choose Empty.
Enter permanently delete in the confirmation field.
Choose Empty again to remove every object.
Select nextwork-retail-<your-account-id>-<unique-suffix> again.
Choose Delete.
Enter nextwork-retail-<your-account-id>-<unique-suffix> for the final confirmation.
Confirm the bucket deletion.

The crawler service role is no longer needed after the crawler has been removed. Deleting it completes the dependency cleanup.

Open IAM from the AWS console.
Open Roles.
Select AWSGlueServiceRole-retail-orders-crawler.
Choose Delete.

A final check confirms that the cleanup reached every persistent resource.

Return to Amazon S3 to confirm that nextwork-retail-<your-account-id>-<unique-suffix> no longer appears.
Return to AWS Glue Crawlers to confirm that retail-orders-crawler no longer appears.
Return to AWS Glue Classifiers to confirm that retail-orders-csv no longer appears.
Return to AWS Glue Data Catalog to confirm that retail_pipeline_db no longer appears.
Return to IAM Roles to confirm that AWSGlueServiceRole-retail-orders-crawler no longer appears.

That closes out every persistent AWS resource from this lab. Your local project files remain ready for another build.

Nice Work!

You did it! You built a serverless retail data pipeline that turns raw order files into a corrected curated dataset.

Your pipeline uncovered inflated revenue in the raw data. It now produces seven unique orders with reported revenue of 750 after the incremental load.

You've learned how to:

Build a private Amazon S3 raw zone that keeps daily order files ready for batch analytics.
Use a custom classifier with an AWS Glue crawler to discover a deterministic six-column schema in the AWS Glue Data Catalog.
Use Amazon Athena SQL to trace inflated revenue to duplicate order ID 1005. Create a deduplicated Apache Parquet layer with CTAS that returns five rows with revenue of 525.
Complete the optional Secret Mission by incrementally loading a second daily batch. Keep reruns idempotent so the curated totals remain at seven rows with revenue of 750.

Ready to quiz yourself?