Build a Retail Data Lake with AWS Glue

Turn messy retail CSV data into queryable Parquet with Glue and Athena.

Introduction

30 Second Summary

Retail sales files rarely arrive perfectly clean. A single broken date or quantity can turn a routine revenue report into a misleading total.

In this project, you will build a serverless retail data lake that turns unreliable sales records into a trustworthy regional revenue report. The pipeline quarantines malformed rows before they can distort the total.

What You'll Build

Your final Amazon Athena report shows four regions with five valid orders totaling $612.43.

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

  • Recreated the data-lake control plane from an AWS CloudFormation template. Traced the raw retail CSV into Amazon S3. Inspected its discovered schema in the AWS Glue Data Catalog. Reproduced the malformed-value failure in Amazon Athena.
  • Run the managed transformation in AWS Glue. See five valid sales in partitioned Parquet. See three malformed sales in quarantine.
  • Returned a regional revenue report with four regions totaling $612.43. Inspected the Glue job state. Located its Amazon CloudWatch Logs groups.
  • Secret Mission: Add another sale before running the same pipeline twice. Prove overwrite-based idempotency keeps the final result at six orders and $702.42.

Are there any prerequisites?

Your AWS identity needs permission to create roles in AWS Identity and Access Management (IAM) plus project resources in AWS CloudFormation, Amazon S3, AWS Glue, Amazon Athena, and Amazon CloudWatch Logs. You also need the AWS CLI, Python, Visual Studio Code, and macOS Terminal ready on your Mac.

Before We Start

Before the hands-on work begins, define the retail data problem this pipeline solves. You are separating unreliable CSV input into raw, curated, and rejected layers so malformed values cannot affect the regional revenue report.

Set Up the Project and Land Raw Data

A serverless retail pipeline needs reproducible source files before Amazon S3 can receive a retail CSV. Without those files, the cloud resources have no dependable input.

The AWS CLI needs one known Region. Its identity result also reveals the account and ARN that receive the project's charges.

In this step, get ready to:
  • Prepare the local project files.
  • Confirm the intended AWS identity in us-east-1.
  • Upload the raw CSV and ETL script to a dedicated S3 bucket.
Create the local project files

The local project keeps the infrastructure template, transformation script, and sample data together. Visual Studio Code gives you one place to check each file before uploading anything.

  • Press Cmd+Space to search your Mac.
  • Type Terminal.
  • Press Enter to open macOS Terminal.
  • Build the three empty file paths inside ~/Projects/retail-sales-lakehouse by running these commands:
mkdir -p ~/Projects/retail-sales-lakehouse/data
cd ~/Projects/retail-sales-lakehouse
touch template.yaml etl.py data/sales.csv

What Did These Commands Create?

The first command creates the retail-sales-lakehouse folder with its nested data folder. The second command makes that project folder your active Terminal location.

The final command creates template.yaml, etl.py, and data/sales.csv.

  • Press Cmd+Space to search your Mac.
  • Type Visual Studio Code.
  • Press Enter to open Visual Studio Code.
  • Use the folder-opening control to choose a local folder.
  • Select ~/Projects/retail-sales-lakehouse.

You should see template.yaml, etl.py, and the data folder in the file list.

Cannot See the Project Files?

Confirm that Visual Studio Code opened ~/Projects/retail-sales-lakehouse. Selecting its parent ~/Projects leaves the files one level deeper.

Ask for help with finding the local project files.

The template.yaml file describes the project's control plane as infrastructure as code for AWS CloudFormation.

  • Select template.yaml from the file list.
  • Open the double-check tab below.
  • Replace the empty file contents with the displayed template.

✔️ Awesome, I've got everything!

Keep this tab selected if your template.yaml file already matches the project template.

ⓧ I'd like to double check the full code

Description: Serverless retail data lake resources for AWS Glue and Athena

Parameters:
  DataBucketName:
    Type: String
    Description: Existing S3 bucket that contains the source data and Glue script

Resources:
  GlueExecutionRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: "2012-10-17"
        Statement:
          - Effect: Allow
            Principal:
              Service:
                - glue.amazonaws.com
            Action:
              - sts:AssumeRole
      ManagedPolicyArns:
        - arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
      Policies:
        - PolicyName: RetailDataBucketAccess
          PolicyDocument:
            Version: "2012-10-17"
            Statement:
              - Effect: Allow
                Action:
                  - s3:ListBucket
                Resource: !Sub arn:aws:s3:::${DataBucketName}
              - Effect: Allow
                Action:
                  - s3:GetObject
                  - s3:PutObject
                  - s3:DeleteObject
                Resource: !Sub arn:aws:s3:::${DataBucketName}/*

  RawDatabase:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: retail_sales_raw
        Description: Raw retail sales metadata

  CuratedDatabase:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: retail_sales_curated
        Description: Curated retail sales metadata

  RawCrawler:
    Type: AWS::Glue::Crawler
    DependsOn: RawDatabase
    Properties:
      Name: !Sub ${AWS::StackName}-raw-crawler
      Role: !GetAtt GlueExecutionRole.Arn
      DatabaseName: retail_sales_raw
      Description: Discovers the raw retail CSV schema
      Targets:
        S3Targets:
          - Path: !Sub s3://${DataBucketName}/raw/sales/
      SchemaChangePolicy:
        UpdateBehavior: UPDATE_IN_DATABASE
        DeleteBehavior: LOG
      Configuration: '{"Version":1.0,"Grouping":{"TableGroupingPolicy":"CombineCompatibleSchemas"}}'

  CuratedCrawler:
    Type: AWS::Glue::Crawler
    DependsOn: CuratedDatabase
    Properties:
      Name: !Sub ${AWS::StackName}-curated-crawler
      Role: !GetAtt GlueExecutionRole.Arn
      DatabaseName: retail_sales_curated
      Description: Discovers the curated partitioned Parquet schema
      Targets:
        S3Targets:
          - Path: !Sub s3://${DataBucketName}/curated/sales/
      SchemaChangePolicy:
        UpdateBehavior: UPDATE_IN_DATABASE
        DeleteBehavior: LOG
      Configuration: '{"Version":1.0,"Grouping":{"TableGroupingPolicy":"CombineCompatibleSchemas"}}'

  RetailEtlJob:
    Type: AWS::Glue::Job
    Properties:
      Name: !Sub ${AWS::StackName}-etl
      Description: Validates retail sales and publishes curated Parquet
      Role: !GetAtt GlueExecutionRole.Arn
      GlueVersion: "6.0"
      Command:
        Name: glueetl
        ScriptLocation: !Sub s3://${DataBucketName}/scripts/etl.py
        PythonVersion: "3"
      DefaultArguments:
        "--job-language": python
        "--RAW_PATH": !Sub s3://${DataBucketName}/raw/sales/
        "--CURATED_PATH": !Sub s3://${DataBucketName}/curated/sales/
        "--REJECTED_PATH": !Sub s3://${DataBucketName}/rejected/sales/
        "--TempDir": !Sub s3://${DataBucketName}/temp/
      WorkerType: G.1X
      NumberOfWorkers: 2
      ExecutionProperty:
        MaxConcurrentRuns: 1
      MaxRetries: 0
      Timeout: 10

Outputs:
  GlueJobName:
    Value: !Ref RetailEtlJob
  RawCrawlerName:
    Value: !Ref RawCrawler
  CuratedCrawlerName:
    Value: !Ref CuratedCrawler
  RawDatabaseName:
    Value: retail_sales_raw
  CuratedDatabaseName:
    Value: retail_sales_curated
  GlueRoleArn:
    Value: !GetAtt GlueExecutionRole.Arn

What Does This Template Define?

The template accepts the existing bucket through DataBucketName. It defines the Glue execution role, two databases, two crawlers, and the ETL job.

The job points to scripts/etl.py in your bucket. Its arguments connect the raw, curated, rejected, and temporary S3 paths.

  • Save template.yaml.

The saved file should begin with the serverless retail data lake description. It should end with the GlueRoleArn output.

The etl.py file contains the AWS Glue 6.0 PySpark transformation. It parses fields safely before separating valid sales from rejected rows.

  • Select etl.py from the file list.
  • Open the double-check tab below.
  • Replace the empty file contents with the displayed script.

✔️ Awesome, I've got everything!

Keep this tab selected if your etl.py file already matches the complete transformation script.

ⓧ I'd like to double check the full code

import sys

from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from pyspark.sql import functions as F


args = getResolvedOptions(
    sys.argv,
    ["JOB_NAME", "RAW_PATH", "CURATED_PATH", "REJECTED_PATH"],
)

spark_context = SparkContext()
glue_context = GlueContext(spark_context)
spark = glue_context.spark_session
logger = glue_context.get_logger()

job = Job(glue_context)
job.init(args["JOB_NAME"], args)

raw = spark.read.csv(args["RAW_PATH"], header=True)

parsed = raw.select(
    F.trim("order_id").alias("order_id"),
    F.try_to_date("order_date", "yyyy-MM-dd").alias("order_date"),
    F.trim("customer_id").alias("customer_id"),
    F.trim("product").alias("product"),
    F.trim("region").alias("region"),
    F.col("quantity").try_cast("int").alias("quantity"),
    F.col("unit_price").try_cast("decimal(10,2)").alias("unit_price"),
)

valid_condition = (
    F.col("order_date").isNotNull()
    & F.col("quantity").isNotNull()
    & (F.col("quantity") > 0)
    & F.col("unit_price").isNotNull()
    & (F.col("unit_price") >= 0)
)

valid = parsed.filter(valid_condition).withColumn(
    "revenue",
    F.round(F.col("quantity") * F.col("unit_price"), 2),
)

rejected = parsed.filter(~valid_condition).withColumn(
    "reject_reason",
    F.expr(
        """
        CASE
          WHEN order_date IS NULL THEN 'invalid order_date'
          WHEN quantity IS NULL THEN 'invalid quantity'
          WHEN quantity <= 0 THEN 'quantity must be positive'
          WHEN unit_price IS NULL THEN 'invalid unit_price'
          WHEN unit_price < 0 THEN 'unit_price must be non-negative'
          ELSE 'unknown validation error'
        END
        """
    ),
)

valid.write.parquet(
    args["CURATED_PATH"],
    mode="overwrite",
    partitionBy="region",
)

rejected.write.csv(
    args["REJECTED_PATH"],
    mode="overwrite",
    header=True,
)

logger.info(
    f"valid_rows={valid.count()} rejected_rows={rejected.count()}"
)

job.commit()

How Does the Transformation Work?

The script reads the raw CSV with Glue job arguments. Safe date and numeric conversions turn malformed values into nulls that the validation condition can identify.

Valid rows receive a calculated revenue value. Rejected rows receive a specific reject_reason.

The two output writers use overwrite mode. This makes each completed run publish the latest valid and rejected state.

  • Save etl.py.

The saved script should begin with import sys. It should end with job.commit().

The sample data contains five valid sales and three malformed sales. The bad date, bad quantity, and negative quantity create the data-quality cases that the pipeline must handle.

  • Expand the data folder in the file list.
  • Select sales.csv.
  • Open the double-check tab below.
  • Replace the empty file contents with the displayed retail data.

✔️ Awesome, I've got everything!

Keep this tab selected if your data/sales.csv file already contains the header and eight sales rows.

ⓧ I'd like to double check the full code

order_id,order_date,customer_id,product,region,quantity,unit_price
1001,2026-09-01,C001,Keyboard,west,2,49.99
1002,2026-09-01,C002,Mouse,east,3,24.50
1003,2026-09-02,C001,Monitor,west,1,199.99
1004,not-a-date,C003,Desk,east,1,250.00
1005,2026-09-03,C004,Chair,south,bad,125.00
1006,2026-09-03,C005,USB Hub,south,4,19.99
1007,2026-09-04,C006,Laptop Stand,north,-1,59.00
1008,2026-09-04,C007,Webcam,north,2,79.50

Why Is the Source Deliberately Messy?

The file mixes trustworthy rows with malformed business data. The values not-a-date, bad, and -1 give the ETL job three distinct rejection cases.

Keeping those rows in the raw layer preserves the original source. The transformation decides which rows can contribute to analytics.

  • Save data/sales.csv.

You should see one header row followed by eight sales rows. The final row should begin with order ID 1008.

That is the local foundation ready for AWS. The project now has the exact template, transformation, and source data used by the rest of the pipeline.

Pin the Region and verify your identity

AWS CLI requests use the active Region and credentials from your Terminal session. Setting the Region explicitly keeps every project resource in us-east-1.

The identity check reveals the AWS account and ARN before you create a billable resource. This gives you a chance to confirm the intended destination.

  • Return to the Terminal session from earlier.
  • Set the default Region and retrieve the active identity by running these commands:
export AWS_DEFAULT_REGION=us-east-1
aws sts get-caller-identity

What Does the Identity Check Prove?

The environment variable sets us-east-1 as the default Region for later AWS CLI requests in this Terminal session.

The identity command returns UserId, Account, and Arn for the active credentials.

  • Compare the returned Account with the AWS account you intend to use.
  • Confirm that the returned Arn identifies the intended AWS user or role.

The output should identify the account that receives this project's charges. The active ARN should match the identity you expect to use.

Identity Does Not Match?

Stop before creating the bucket. Update the configured AWS credentials or profile so the command returns the intended account and ARN.

Ask for help with diagnosing the active AWS CLI identity.

Create the bucket and land the files

The raw landing zone preserves the source file exactly as received. A separate scripts path gives the managed Glue job a stable location for etl.py.

This billable bucket holds only the project's small files, which keeps expected storage cost low. The account ID and current timestamp make its name globally unique.

  • Generate the bucket name and create the S3 bucket by running these commands:
ACCOUNT_ID=$(aws sts get-caller-identity --query Account --output text)
BUCKET="retail-sales-lakehouse-${ACCOUNT_ID}-$(date +%s)"
aws s3 mb "s3://${BUCKET}"

How Is the Bucket Name Built?

The first command stores the active account number in ACCOUNT_ID. The second combines that account number with the current timestamp.

The resulting name is stored in BUCKET for every later command in this Terminal session. The final command creates the bucket.

The Terminal should confirm that it created the new bucket in us-east-1.

Bucket Creation Failed?

Confirm that the identity check returned the intended account. Check that the Terminal session still has AWS_DEFAULT_REGION set.

Ask for help with troubleshooting the S3 bucket creation.

  • Upload the retail source to the raw S3 path by running these commands:
aws s3 cp data/sales.csv "s3://${BUCKET}/raw/sales/sales.csv"
aws s3 cp etl.py "s3://${BUCKET}/scripts/etl.py"

Where Do the Files Land?

The first upload preserves the retail source at raw/sales/sales.csv. The second upload stores the transformation at scripts/etl.py.

These paths match the locations referenced by template.yaml. The next deployment can use the existing bucket without moving either file.

Before you run the final check, which two object paths do you expect the recursive listing to show?

  • Verify the landed files by running this command:
aws s3 ls "s3://${BUCKET}" --recursive

What Does the Final Check Prove?

The recursive listing shows every object currently stored in the bucket. Both required input paths must appear before the control plane is deployed.

You should see raw/sales/sales.csv and scripts/etl.py in the output. You have the first hard checkpoint done: both project inputs are now in Amazon S3.

One of the S3 Paths Is Missing?

Return to ~/Projects/retail-sales-lakehouse in the same Terminal session. Rerun the upload command for the missing file.

Confirm that BUCKET still contains the bucket name created earlier. A new Terminal session does not inherit that shell variable.

Ask for help with diagnosing the missing S3 object.

  • Keep this Terminal session open for the later commands that reuse BUCKET.

Your source data and ETL script now have stable local and S3 homes. Next, you will deploy the repeatable CloudFormation control plane that catalogs the raw data.

Deploy and Catalog the Raw Data Lake

Your raw retail file is already stored in Amazon S3. Now the data needs a repeatable control plane.

Building each AWS resource by hand creates room for configuration drift. AWS CloudFormation deploys the existing template as one repeatable control plane.

In this step, get ready to:
  • Deploy the CloudFormation stack with its required role capability.
  • Catalog the raw CSV with the raw crawler.
  • Inspect the crawler-generated schema.
Create the control plane

The stack includes an AWS Identity and Access Management (IAM) role that AWS Glue assumes later. The deployment capability explicitly allows CloudFormation to create that role.

Account-level role creation deserves care. The existing template.yaml limits this change to the project resources you already reviewed.

Expect the Terminal to stay busy for several minutes while CloudFormation creates the stack.

  • Deploy the project stack from the open Terminal session by running this command:
aws cloudformation deploy \
  --template-file template.yaml \
  --stack-name retail-sales-lakehouse \
  --capabilities CAPABILITY_IAM \
  --parameter-overrides DataBucketName="$BUCKET"

What Does This Command Do?

  • The template file defines every resource in the stack.
  • The CAPABILITY_IAM capability acknowledges that the stack creates an IAM resource.
  • The parameter override points DataBucketName at the existing bucket stored in BUCKET.

The Terminal should finish with a successful deployment message. The stack now manages the role plus the Glue databases, crawlers, and ETL job.

Before you inspect the stack, what state do you expect after a successful deployment?

  • Inspect the deployed stack status by running this command:
aws cloudformation describe-stacks \
  --stack-name retail-sales-lakehouse \
  --query 'Stacks[0].StackStatus' \
  --output text

What Does This Command Do?

  • The command requests the stack named retail-sales-lakehouse.
  • The query returns only its current stack status.
  • The text output keeps the result easy to read in the Terminal.

You should see a successful stack status. The account-level setup is complete.

CloudFormation Did Not Complete?

Confirm that the open Terminal still has AWS_DEFAULT_REGION set to us-east-1. Check that the active identity can create the resources defined by the stack.

Ask for targeted help with diagnosing the stack deployment.

Catalog the raw CSV

An AWS Glue crawler reads the raw S3 prefix. The AWS Glue Data Catalog stores the table metadata that the crawler discovers.

This on-demand crawler run uses billable AWS Glue capacity. One successful run is enough for this step.

Expect the crawler to spend several minutes discovering the file.

  • Start the raw crawler by running this command:
aws glue start-crawler --name retail-sales-lakehouse-raw-crawler

What Does This Command Do?

The command starts the CloudFormation-managed raw crawler. The crawler scans the CSV stored under the raw sales prefix.

Before you check the crawler, which two status fields should confirm that discovery has finished?

  • Check the crawler state by running this command:
aws glue get-crawler \
  --name retail-sales-lakehouse-raw-crawler \
  --query 'Crawler.{State:State,LastStatus:LastCrawl.Status}' \
  --output table

How Does the Status Check Work?

  • The command retrieves metadata for retail-sales-lakehouse-raw-crawler.
  • The query selects the crawler state plus the latest crawl status.
  • The table output places both fields beside their current values.
  • Wait briefly if the crawler has not reached its terminal state.
  • Repeat the status command above.

When discovery is complete, you should see READY for State. You should also see SUCCEEDED for LastStatus.

Crawler Did Not Succeed?

Confirm that the bucket still contains raw/sales/sales.csv. Check that the CloudFormation deployment finished successfully before retrying the crawler.

Ask for help with diagnosing the raw crawler run.

Inspect the generated schema

Crawler-generated table names can vary. Discovering the table dynamically keeps later commands tied to the table that AWS actually created.

Before you list the database, which CSV columns do you expect the crawler to discover?

  • List the raw database table plus its discovered columns by running this command:
aws glue get-tables \
  --database-name retail_sales_raw \
  --query 'TableList[].{Table:Name,Columns:StorageDescriptor.Columns}' \
  --output json

What Does This Command Show?

  • The command reads every table in retail_sales_raw.
  • The query returns each generated table name.
  • The same response includes the discovered column metadata.

You should see one generated table. Its columns should include order_id, order_date, customer_id, product, region, quantity, and unit_price.

That is the control plane working. Your raw CSV now has a cataloged schema for the next query.

Table or Columns Missing?

Confirm that the crawler status shows a completed successful run. Check that the command uses the retail_sales_raw database.

Ask for targeted help with finding the crawler-generated table.

Your raw file is cataloged. Next, you will ask Amazon Athena to calculate revenue directly from its mixed-quality values.

Reproduce the Raw Schema Failure

Your raw crawler has registered the retail CSV in the AWS Glue Data Catalog. The discovered table now exposes the source columns to SQL.

Cataloging preserves the source as it arrived. In this step, Amazon Athena tests whether a naive revenue calculation can trust every raw value.

In this step, get ready to:
  • Capture the dynamically generated raw table name.
  • Submit a revenue aggregation that casts every raw value.
  • Inspect the failed query state and its reason.
Discover the generated table name

AWS Glue crawler table names can be shortened or receive a hash suffix. Reading the name from the catalog keeps your query tied to the table that exists.

  • Read the raw table name into RAW_TABLE by running these commands in the Terminal session from earlier:
RAW_TABLE=$(aws glue get-tables \
  --database-name retail_sales_raw \
  --query 'TableList[0].Name' \
  --output text)

echo "$RAW_TABLE"

What Do These Commands Do?

  • The aws glue get-tables command reads the tables in retail_sales_raw.
  • The query expression selects the first generated table name.
  • The shell stores that name in RAW_TABLE.
  • The final command prints the stored name for inspection.

The terminal should print one table name. That value is now available through RAW_TABLE for the Athena query.

No Table Name Appeared?

Confirm that the raw crawler reached SUCCEEDED. Check that the database name is retail_sales_raw.

Ask for targeted help with finding the crawler-generated table.

Submit the naive revenue query

The quantity and unit_price fields came from CSV text. The naive aggregation asks Athena to cast every value before multiplication.

Before you submit the query, do you think every row can survive both casts?

  • Submit the raw revenue aggregation and capture its execution ID by running this command:
RAW_QUERY_ID=$(aws athena start-query-execution \
  --query-string "SELECT SUM(CAST(quantity AS INTEGER) * CAST(unit_price AS DECIMAL(10,2))) AS gross_revenue FROM \"${RAW_TABLE}\"" \
  --query-execution-context Database=retail_sales_raw,Catalog=AwsDataCatalog \
  --result-configuration "OutputLocation=s3://${BUCKET}/athena-results/" \
  --query 'QueryExecutionId' \
  --output text)

What Does This Command Do?

  • The query calculates SUM across every raw sales row.
  • The expression CAST(quantity AS INTEGER) requires every quantity to become an integer.
  • The expression CAST(unit_price AS DECIMAL(10,2)) requires every price to become a decimal value.
  • The shell stores Athena's returned query execution ID in RAW_QUERY_ID.
  • The result configuration writes Athena files under the Amazon S3 prefix s3://${BUCKET}/athena-results/.

Athena has accepted the calculation for processing. The execution ID in RAW_QUERY_ID lets you inspect its terminal state.

The Query Was Not Submitted?

Confirm that BUCKET still contains your bucket name. Check that RAW_TABLE contains the generated table name.

Ask for help with diagnosing the Athena submission.

Inspect the terminal query state

Athena runs queries asynchronously. The value in RAW_QUERY_ID identifies the exact execution you just submitted.

Before you check the terminal state, which raw value is most likely to decide the outcome?

  • Retrieve the query state and its reason by running this command:
aws athena get-query-execution \
  --query-execution-id "$RAW_QUERY_ID" \
  --query 'QueryExecution.Status.[State,StateChangeReason]' \
  --output table

What Does This Command Show?

  • The execution ID selects the raw revenue query you submitted.
  • The status query returns the current state.
  • The same response includes StateChangeReason for a failed execution.
  • Wait briefly if the state is QUEUED or RUNNING.
  • Repeat the status command above.

You will see FAILED after Athena reaches a terminal state. The reason describes an invalid cast or malformed source value.

You have reproduced the data-quality boundary. The catalog describes the raw columns without guaranteeing that every source value is safe for arithmetic.

Query Did Not Reach Failed?

If the state remains QUEUED or RUNNING, wait briefly before repeating the same status command.

If the query reaches SUCCEEDED, confirm that RAW_TABLE points to a table in retail_sales_raw. Check that the raw source still contains the bad quantity.

Ask for help with investigating the unexpected Athena state.

The raw report has exposed the malformed records in the calculation path. Next, you will use AWS Glue to separate usable sales from rejected records.

Transform and Quarantine Sales

Your failed Amazon Athena query proved that the raw retail file cannot support trustworthy revenue totals. Malformed values need a controlled path before analytics can use the data.

In this step, an AWS Glue 6.0 PySpark ETL job parses the raw records. Its validation rules calculate revenue only for usable sales.

The job writes five valid records as partitioned Parquet. It sends three invalid records to a rejected CSV path with a reason for each rejection.

In this step, get ready to:
  • Start the managed transformation job.
  • Poll the job run until it succeeds.
  • Inspect the job's published evidence.
Start the managed Glue job

The deployed AWS CloudFormation stack already defines the managed job. Its runtime arguments connect the raw source to the curated and rejected output zones.

How Does the Job Handle Bad Rows?

  • The try_to_date and try_cast conversions turn malformed dates or numbers into null values.
  • The valid_condition keeps rows with a valid date, positive quantity, and non-negative price.
  • The revenue column records each valid sale's calculated value.
  • The reject_reason column explains why each invalid sale entered quarantine.
  • Switch back to the Terminal session from earlier.
  • Start the job with a command that stores its returned run ID:
JOB_RUN_ID=$(aws glue start-job-run \
  --job-name retail-sales-lakehouse-etl \
  --query 'JobRunId' \
  --output text)

What Does This Command Do?

  • The command submits a run of retail-sales-lakehouse-etl.
  • The query extracts the returned JobRunId value.
  • The shell stores that value in JOB_RUN_ID for later status checks.

Job Did Not Start?

  • Confirm that the job name is exactly retail-sales-lakehouse-etl.
  • Check that the AWS CLI still uses us-east-1.
  • Confirm that your active AWS identity can start the deployed Glue job.

Ask for targeted help with diagnosing the failed Glue job start.

Wait for the transformation to succeed

The managed Spark runtime often takes several minutes to start. A quiet wait is normal during that time.

  • Check the current job state by running this command:
aws glue get-job-run \
  --job-name retail-sales-lakehouse-etl \
  --run-id "$JOB_RUN_ID" \
  --query 'JobRun.{State:JobRunState,Seconds:ExecutionTime,Error:ErrorMessage}' \
  --output table

What Does This Command Show?

  • The State value shows the current lifecycle stage.
  • The Seconds value reports execution time after processing begins.
  • The Error value exposes the job's failure detail when one exists.

You may first see STARTING or RUNNING.

  • Repeat the status command after short waits while the state is STARTING or RUNNING.

A SUCCEEDED state confirms that the transformation completed. That is the managed processing complete.

Job Did Not Succeed?

  • Remove the --query filter from the status command.
  • Rerun the edited command to inspect the ErrorMessage field.
  • Check that the raw CSV still exists under raw/sales/.
  • Check that the ETL script still exists under scripts/etl.py.

Ask for help with diagnosing the failed Glue run.

Inspect the published outputs

The two Amazon S3 output zones show how the job separated usable sales from malformed records. Amazon CloudWatch Logs provides the managed execution trail.

Before you inspect the results, where do you expect valid and invalid records to appear?

  • Inspect both output zones plus the Glue log groups by running these commands:
aws s3 ls "s3://${BUCKET}/curated/sales/" --recursive
aws s3 ls "s3://${BUCKET}/rejected/sales/" --recursive
aws logs describe-log-groups \
  --log-group-name-prefix /aws-glue/jobs \
  --query 'logGroups[].logGroupName' \
  --output text

What Do These Commands Check?

  • The first listing checks the region-partitioned Parquet output under curated/sales/.
  • The second listing checks the quarantined CSV output under rejected/sales/.
  • The final command returns Glue log group names that begin with /aws-glue/jobs.

You'll see Parquet objects under curated/sales/. You'll see CSV output under rejected/sales/.

You'll also see the Glue log group names. The successful run has published five valid sales and quarantined three invalid sales.

Output Path Is Empty?

  • Confirm that the latest job state is SUCCEEDED.
  • Check that BUCKET still contains the bucket name from the earlier Terminal session.
  • Inspect the job's ErrorMessage field if the run entered a failure state.

Ask for help with finding the missing Glue outputs.

Your raw failure now has a reliable transformation path. Next, you'll catalog the curated Parquet data and produce the regional revenue report.

Query the Curated Parquet Data

Your successful AWS Glue run turned five valid sales into region-partitioned Parquet. The malformed sales now sit in quarantine.

The curated crawler must register the transformed schema in the AWS Glue Data Catalog. It also registers the region partitions.

Amazon Athena can then query the curated Parquet files. This final report proves malformed records no longer affect revenue.

In this step, get ready to:
  • Run the curated crawler until it succeeds.
  • Submit a regional revenue query against the dynamically discovered table.
  • Retrieve the final four-region revenue report.
Run the curated crawler

An AWS Glue crawler inspects the curated files to discover their typed columns. That metadata gives Athena a table it can query.

  • Start the curated crawler with an immediate status check by running these commands:
aws glue start-crawler --name retail-sales-lakehouse-curated-crawler

aws glue get-crawler \
  --name retail-sales-lakehouse-curated-crawler \
  --query 'Crawler.{State:State,LastStatus:LastCrawl.Status}' \
  --output table

What Do These Commands Do?

The first command starts the curated crawler. The second command returns its current state.

The last crawl status shows whether schema discovery completed successfully. The crawler stores its table in retail_sales_curated.

Crawler startup can take several minutes, so a short wait is normal.

  • Wait a short time before checking the crawler again.
  • Repeat the status command shown above until the crawler finishes.

You will see State as READY. You will see LastStatus as SUCCEEDED.

Crawler Still Not Finished?

Give the crawler another short wait before repeating the status check. A completed state can take several checks to appear.

Ask for targeted help with diagnosing the curated crawler.

Submit the regional revenue query

Crawler-generated table names can vary between environments. Reading the name into CURATED_TABLE keeps the report independent of that generated name.

  • Discover the generated table before submitting the regional report by running these commands:
CURATED_TABLE=$(aws glue get-tables \
  --database-name retail_sales_curated \
  --query 'TableList[0].Name' \
  --output text)

CURATED_QUERY_ID=$(aws athena start-query-execution \
  --query-string "SELECT region, COUNT(*) AS orders, SUM(revenue) AS revenue FROM \"${CURATED_TABLE}\" GROUP BY region ORDER BY revenue DESC" \
  --query-execution-context Database=retail_sales_curated,Catalog=AwsDataCatalog \
  --result-configuration "OutputLocation=s3://${BUCKET}/athena-results/" \
  --query 'QueryExecutionId' \
  --output text)

What Do These Commands Do?

CURATED_TABLE stores the generated table name. The query groups valid sales by region.

CURATED_QUERY_ID stores the new Athena execution ID. Athena writes the query output under athena-results/ in your existing bucket.

Query Submission Failed?

Confirm the crawler reached its successful final state before retrying. Check that your Terminal session still contains BUCKET.

Ask for help with diagnosing the Athena query submission.

Retrieve the final results

Athena runs queries asynchronously. The execution ID lets you check when the regional aggregation is ready.

  • Check the current Athena query state by running this command:
aws athena get-query-execution \
  --query-execution-id "$CURATED_QUERY_ID" \
  --query 'QueryExecution.Status.State' \
  --output text

What Does This Command Do?

The command checks the execution identified by CURATED_QUERY_ID. Athena returns its current state as text.

  • Repeat the query state command after a short wait if it shows QUEUED.
  • Repeat the query state command after a short wait if it shows RUNNING.

The final state should be SUCCEEDED. Your regional report is now ready to retrieve.

Athena Did Not Succeed?

A FAILED state means Athena could not complete the curated query. Confirm that CURATED_TABLE came from retail_sales_curated.

Ask for help with diagnosing the curated Athena query.

Before you retrieve the rows, which region do you expect to appear first after sorting by revenue?

  • Retrieve the completed result rows by running this command:
aws athena get-query-results \
  --query-execution-id "$CURATED_QUERY_ID" \
  --query 'ResultSet.Rows[*].Data[*].VarCharValue' \
  --output table

What Does This Command Do?

The command retrieves rows from the completed Athena execution. The query filter selects each returned cell for a readable Terminal table.

West appears first with 2 orders. Its revenue is 299.97.

North appears next with 1 order. Its revenue is 159.00.

South appears with 1 order worth 79.96. East appears with 1 order worth 73.50.

Together, the four rows contain 5 valid orders. Their revenue totals 612.43.

Rows Missing From the Report?

Confirm the Athena state reached SUCCEEDED before requesting rows. Confirm that CURATED_TABLE came from the curated database.

Ask for help with checking the regional result rows.

You closed the pipeline loop. Athena now returns a trusted regional report from the curated data.

Secret mission

Prove the Pipeline Is Idempotent

Add one valid sale to the raw retail source. Run the job twice against the unchanged update. Use Athena to prove that repeated execution preserves exactly six orders with 702.42 in total revenue.

Clean Up Your Resources

Clean Up Your Resources

Choose a cleanup path based on whether you plan to keep building this data lake. Your Amazon S3 bucket plus retained Amazon CloudWatch Logs can continue to accrue small charges.

Cost warning

The main path is expected to cost roughly $0.45 to $0.60 in us-east-1.

Retries plus the Secret Mission add AWS Glue runs, so use $2 as a conservative project budget.

Retained S3 objects can continue to create storage charges.

New crawler runs plus Amazon Athena queries create additional usage charges.

Resources you used:

  • The S3 bucket stored in BUCKET, including objects under raw/, scripts/, curated/, rejected/, temp/, plus athena-results/.
  • The AWS CloudFormation stack named retail-sales-lakehouse.
  • The AWS Identity and Access Management (IAM) execution role managed by the stack.
  • The AWS Glue Data Catalog databases named retail_sales_raw plus retail_sales_curated.
  • The Glue crawlers named retail-sales-lakehouse-raw-crawler plus retail-sales-lakehouse-curated-crawler.
  • The AWS Glue 6.0 job named retail-sales-lakehouse-etl.

CloudWatch Logs Stay Separate

AWS-generated Glue log groups sit outside the CloudFormation stack. They remain after stack deletion.

Keep everything running

No action needed. Choose this if you plan to continue experimenting with the retail data lake.

  • Retain the S3 bucket stored in BUCKET.
  • Retain the retail-sales-lakehouse stack.
  • Reuse the curated Parquet output for future Athena reports.
  • Keep the local project files on your Mac.

No crawler or Glue job is scheduled. Charges from new compute activity only grow when you start another run.

Pause - I'll come back to this later

Leave the stack idle to avoid new Glue compute charges. Your bucket contents remain available for a later session.

  • Avoid starting new crawler runs.
  • Avoid starting new AWS Glue job runs.
  • Avoid submitting new Athena queries.
  • Keep the S3 bucket stored in BUCKET.
  • Keep the retail-sales-lakehouse stack deployed.

The data lake stays ready for another session. S3 storage plus retained CloudWatch logs can still create small charges.

Delete - I don't want to use this again

This cleanup permanently removes the cloud data lake. Your local project files stay on your Mac, so you can rebuild later.

  • Return to the Terminal session from earlier.
  • Empty the project bucket by running this command:
aws s3 rm "s3://${BUCKET}" --recursive

What Does This Command Do?

The AWS CLI recursively removes every object from the bucket stored in BUCKET.

This clears the raw data, ETL script, curated Parquet, rejected CSV, temporary objects, plus Athena results.

  • Start deletion of the CloudFormation stack by running this command:
aws cloudformation delete-stack --stack-name retail-sales-lakehouse

What Happens to the Stack?

CloudFormation starts removing the IAM role, two Data Catalog databases, two crawlers, plus the Glue job.

The command produces no terminal output when the deletion request is accepted.

  • Remove the empty S3 bucket by running this command:
aws s3 rb "s3://${BUCKET}"

Why Empty the Bucket First?

The bucket now contains no project objects. This command removes the empty bucket itself.

CloudFormation deletion continues after the deletion command returns. The final check confirms when the stack has disappeared.

  • Check whether stack deletion has finished by running this command:
aws cloudformation describe-stacks --stack-name retail-sales-lakehouse

What Should I See?

A ValidationError stating that the stack does not exist confirms deletion.

If the stack still appears, deletion remains in progress.

  • Repeat the stack check after a short wait if the stack still appears.
  • Return to the CloudWatch Logs view from earlier.
  • Confirm which AWS-generated Glue log groups remain outside the deleted stack.

Nice Work!

Nice Work!

You did it! Your serverless retail data lake now turns unreliable CSV rows into a trusted revenue report.

What you learned:

  • Build a repeatable data lake control plane with AWS CloudFormation and AWS Identity and Access Management (IAM). Organize raw, curated, and rejected layers in Amazon S3.
  • Reproduce a real data-quality failure in Amazon Athena from malformed raw values. Use AWS Glue 6.0 with PySpark to enforce data types. Quarantine three rejected rows. Publish five valid sales as region-partitioned Parquet.
  • Run a four-region revenue report in Athena. Confirm five valid orders with 612.43 in total revenue. Inspect AWS Glue execution state. Locate the job's log groups in Amazon CloudWatch Logs.
  • Secret Mission: Add order 1009 to data/sales.csv. Run the unchanged pipeline twice. Prove overwrite-based idempotency when Athena still reports exactly 6 orders with 702.42 total revenue.

Ready to quiz yourself? Test what stuck.