AWS Builder Center

Builder Center

Building a Retail Data Lake on AWS with Snowflake Integration 📈

Built a modern retail data platform using AWS S3, AWS Glue, Snowflake, and Snowsight to ingest, transform, analyze, and visualize retail sales data.

Khushi Nandwani

📘 Introduction

In this guide, you’ll build a modern retail data platform that ingests, catalogs, transforms, and analyzes retail data using AWS and Snowflake. You’ll create a multi-zone data lake on Amazon S3, automate ETL workflows with AWS Glue, configure scoped access controls with Lake Formation, query data using Athena, trigger transformation with AWS Lambda, and integrate Snowflake for advanced analytics.

📋 Prerequisites

Before starting, ensure you have:
  • An AWS account with billing enabled
  • A Snowflake Trial account
  • Basic familiarity with SQL
  • Access to the AWS Management Console
  • Sample datasets (customers.csv, products.json, and orders.parquet)

🔧 What You Will Build

  • Multi-Zone Data Lake: Store and organize raw and curated retail datasets in Amazon S3.
  • Automated Data Cataloging: Use AWS Glue Crawlers to discover schemas and maintain a centralized data catalog.
  • Serverless Data Transformation: Transform raw retail data into analytics-ready datasets using AWS Glue ETL.
  • Access Control Configuration: Scope fine-grained SELECT permissions on curated data with AWS Lake Formation.
  • Serverless Analytics: Query curated datasets using Amazon Athena without managing infrastructure.
  • Event-Driven Automation: Automatically trigger the Glue transformation job when new order files arrive in S3.
  • Snowflake Integration: Connect Snowflake to the data lake and load curated datasets for downstream analytics.

⚙️ Key Technologies

  • Amazon S3 – Scalable data lake storage
  • AWS Glue Crawlers – Automated schema discovery
  • AWS Glue ETL Jobs – Serverless data transformation
  • AWS Glue Data Catalog – Centralized metadata management
  • AWS Lake Formation – Fine-grained data governance
  • Amazon Athena – Serverless SQL querying
  • AWS Lambda – Event-driven automation
  • AWS IAM – Secure access management
  • Snowflake – Cloud data warehousing
  • SQL – Data analysis and validation
  • PySpark – Large-scale data processing

🏛️ Architecture Diagram

🧩 Architecture Components

Ingestion & Storage

  • Amazon S3 (retail-analytics-data-lake-pipeline-2026) — central data lake, three zones: raw/ (customers, products, orders), curated/, athena-results/. Same bucket accessed by both the AWS pipeline and the Snowflake batch load, via different prefixes.

Cataloging & Transformation (Automated)

  • AWS Glue Crawler (raw) — scans raw/, populates Glue Data Catalog database retail_raw. Role: GlueRetailPipelineRole.
  • AWS Glue ETL Job (raw_to_curated, PySpark/Spark) — transforms raw data into curated format, writes to curated/. Role: GlueRetailPipelineRole.
  • AWS Glue Crawler (curated) — scans curated/, populates Glue Data Catalog database retail_curated. Requires manual re-run after each ETL job — not auto-triggered.
  • AWS Glue Data Catalog — centralized metadata store for both retail_raw and retail_curated databases.

Governance (Provisioned, Not Enforced in Query Path)

  • AWS Lake Formation — grants SELECT on retail_curated.curated to AnalystGlueAthenaAccessRole. Permission exists but isn’t assumed during actual query execution in this build.

Analytics (On-Demand)

  • Amazon Athena — queries retail_curated.curated, writes results to athena-results/. Role: AnalystGlueAthenaAccessRole.

Automation Trigger

  • Amazon S3 Event Notification — fires on new object under raw/orders/.
  • AWS Lambda (trigger-retail-pipeline) — invoked by the S3 event, starts the Glue ETL job only. Does not trigger the curated crawler, and does not trigger on customer or product uploads.

Identity & Access

  • GlueRetailPipelineRole — used by both Glue crawlers and the ETL job for S3 read/write.
  • AnalystGlueAthenaAccessRole — granted SELECT via Lake Formation; not assumed in the actual Athena query step.
  • SnowflakeS3AccessRole — cross-account IAM role trusted by Snowflake’s storage integration, scoped to read curated/ and raw/products/.

Snowflake Integration (Manual Batch, No Automated Sync)

  • Storage Integration (retail_s3_int) — Snowflake’s external-stage credential object referencing SnowflakeS3AccessRole.
  • External Stages — curated_stage (Parquet, points to curated/) and products_stage (JSON, points to raw/products/).
  • Landing Tables — orders_curated (structured columns) and products_raw (single VARIANT column for semi-structured JSON).
  • Load mechanism — manual COPY INTO run by hand in Snowsight; no Snowpipe, task, or scheduler.
  • Snowsight — SQL worksheet interface used to query both landing tables after load.

Compute

  • Snowflake Warehouse (retail_wh, XSMALL, auto-suspend 60s, auto-resume enabled) — compute for all Snowflake SQL operations.

🛠️ Execution Workflow

Step I: Create and Configure the Amazon S3 Data Lake

  • Navigate to Amazon S3 and click Create bucket.
  • Configure the following settings:
    • Bucket Name: <your-s3-bucket-name> (e.g., retail-analytics-data-lake-pipeline-2026``)
    • Object Ownership: ACLs disabled
    • Block Public Access: Enabled
    • Bucket Versioning: Disabled
    • Default Encryption: SSE-S3
  • Click Create bucket and verify successful creation.
  • Create the following root-level folders:
    • raw
    • curated
    • athena-results
  • Within the raw folder, create the following subfolders:
    • customers
    • products
    • orders
  • Expected Bucket Structure:
1
2
3
4
5
6
7
retail-analytics-data-lake-pipeline-2026/
├── raw/
│ ├── customers/
│ ├── products/
│ └── orders/
├── curated/
└── athena-results/

Step II: Upload Sample Data to the Data Lake

  • Open the raw/orders folder in your S3 bucket.
  • Create a new folder named dt=2026-08-10.
  • Upload orders.parquet into the raw/orders/dt=2026-08-10/ folder.
  • Open the raw/customers folder and upload customers.csv.
  • Open the raw/products folder and upload products.json.

Step III: Create and Configure the AWS Glue IAM Role

  • Navigate to IAM and open Roles.
  • Click Create role.
  • Select AWS Service as the trusted entity type and choose Glue as the use case.
  • Attach the AWSGlueServiceRole AWS-managed policy.
  • Name the role GlueRetailPipelineRole and click Create role.
  • Open GlueRetailPipelineRole and click Add permissions → Create inline policy.
  • Open the JSON editor and attach the following inline policy named RetailPipelineS3Access
  • Verify that the role contains both AWSGlueServiceRole and RetailPipelineS3Access.

Step IV: Create a Glue Crawler and Catalog Raw Data

  • Navigate to AWS Glue and open Crawlers.
  • Click Create crawler.
  • Enter retail-raw-crawler as the crawler name.
  • Click Next.
  • Add a new data source with the following configuration:
  • Data Source: Amazon S3
  • S3 Path: s3://<your-s3-bucket-name>/raw/
  • Crawl all sub-folders: Enabled
  • Click Next.
  • Select GlueRetailPipelineRole as the IAM role.
  • Click Next.
  • Create a new database named retail_raw.
  • Configure the crawler with the following settings:
  • Target Database: retail_raw
  • Schedule: On demand
  • Click Next.
  • Review the configuration and click Create crawler.
  • Select the retail-raw-crawler crawler and click Run crawler.
  • Wait for the crawler run to complete.
  • Verify that the crawler status changes to Ready.
  • Confirm that the last run status shows Succeeded.
After the crawler completes:
  • Navigate to AWS Glue Data Catalog → Tables.
  • Verify that the following tables have been created:
    • customers
    • orders
    • products
  • Open each table and review the detected schema.
  • Confirm that AWS Glue correctly identifies the source formats:
    • customers → CSV
    • orders → Parquet
    • products → JSON

Step V: Create and Run the AWS Glue ETL Job

  • Navigate to AWS Glue and create a new ETL job.
  • Select Script Editor as the authoring method.
  • Choose Spark as the engine and click Create.
  • Replace the default script with the provided PySpark transformation script.
  • Configure the job with the following settings:
    • Job Name: retail-transform-orders
    • IAM Role: GlueRetailPipelineRole
    • Job Type: Spark
    • Glue Version: Latest Available
    • Worker Type: G.1X
    • Number of Workers: 2
  • Add the following job parameters:
    • --source_database=retail_raw
    • --target_path=s3://<your-bucket-name>/curated/
  • Save the job configuration.
  • Run the retail-transform-orders job.
  • Monitor the job execution until the status changes to Succeeded.
  • Navigate to s3://<your-s3-bucket-name>/curated/.
  • Confirm that the transformed Parquet files have been generated successfully.

Step VI: Crawl and Catalog Curated Data

  • Navigate to AWS Glue and open Crawlers.
  • Click Create crawler.
  • Enter retail-curated-crawler as the crawler name.
  • Add an Amazon S3 data source with the following path:
    • s3://<your-s3-bucket-name>/curated/
  • Click Next.
  • Select GlueRetailPipelineRole as the IAM role.
  • Click Next.
  • Create a new database named retail_curated.
  • Configure the crawler with the following settings:
    • Target Database: retail_curated
    • Schedule: On demand
  • Click Next, review the configuration, and create the crawler.
  • Select the retail-curated-crawler crawler and click Run crawler.
  • Wait for the crawler run to complete.
  • Verify that the crawler status changes to Ready.
  • Confirm that the last run status shows Succeeded.
  • Navigate to AWS Glue Data Catalog and open the retail_curated database.
  • Verify that the curated table is created successfully.
  • Open the table and review the detected schema.
  • Confirm that the schema matches the transformed output generated by the AWS Glue ETL job.

Step VII: Configure Lake Formation Access Controls

  • Navigate to AWS Lake Formation.
  • If prompted, add yourself as a Data Lake Administrator.
  • Open Data Lake Locations.
  • Click Register Location.
  • Configure the location with the following settings:
    • Amazon S3 Path: s3://<your-s3-bucket-name>/curated/
    • IAM Role: GlueRetailPipelineRole
  • Click Register Location.
  • Navigate to IAM and create a new role.
  • Configure the role with the following settings:
    • Trusted Entity Type: AWS Service
    • Use Case: Glue
  • Attach the following managed policies:
    • AmazonAthenaFullAccess
    • AWSGlueConsoleFullAccess
  • Enter the role name:
    • AnalystGlueAthenaAccessRole
  • Review the configuration and click Create role.
  • Return to AWS Lake Formation and open Data Permissions.
  • Click Grant.
Configure the permission grant with the following settings:
  • Under Principal type, select Principals.
  • Under Principals, select IAM users and roles.
  • In the IAM users and roles field, choose:
    • AnalystGlueAthenaAccessRole
  • Verify that the selected principal appears in the permissions grant configuration.
  • Continue to the resource and permission settings.
  • Under LF-Tags or catalog resources, select Named Data Catalog resources.
  • Configure the catalog resource settings:
    • Catalog: Default catalog
    • Database: retail_curated
    • Table: curated
  • Verify that the selected catalog, database, and table appear in the permission grant configuration before proceeding.
  • Under Table permissions, select SELECT.
  • Leave all other table permissions unselected.
  • Under Grantable permissions, do not select any permissions.
  • Verify that only the SELECT permission is granted before proceeding.
  • Click Grant.
  • Navigate to Data Permissions.
  • Verify that AnalystGlueAthenaAccessRole has SELECT access on the curated table.
  • Confirm that no additional permissions have been granted.

Step VIII: Query Curated Data with Amazon Athena

  • Navigate to Amazon Athena and open the Query Editor.
  • Configure the query environment with the following settings:
    • Data Source: AwsDataCatalog
    • Database: retail_curated
  • Verify that the curated table is visible under the Tables and Views section before proceeding.
  • In the Query Settings tab, click Manage under Query result location.
  • Set the query results location to:
    s3://<your-s3-bucket-name>/athena-results/
  • Save the query settings.
  • Verify that the Query result location displays the configured S3 path before proceeding.
  • In the Athena query editor, run the following query to validate the curated dataset:
1
2
3
SELECT count(*) AS total_rows,
count(DISTINCT order_id) AS distinct_orders
FROM curated;
  • Execute the query and wait for it to complete successfully.
  • Verify that the query returns the total number of records and distinct order IDs.
  • Confirm that the results are generated without any errors.
  • Verify that the query results are stored in the athena-results S3 location.

Step IX: Automate Glue Job Execution with AWS Lambda

  • Navigate to AWS Lambda and create a new function.
  • Configure the function with the following settings:
    • Function Name: trigger-retail-pipeline
    • Runtime: Python 3.12
    • Execution Role: Create a new role with basic Lambda permissions
  • Review the configuration and click Create function.
  • After the function is created, verify that the Lambda execution role (trigger-retail-pipeline) includes the AWSLambdaBasicExecutionRole managed policy.
  • Click Create function.
  • In the Lambda function editor, replace the default function code with the provided Lambda trigger script.
  • Update the script configuration as required for your environment.
  • Click Deploy to save and publish the updated function code.
  • Go to Configuration → Permissions tab.
  • Open the Lambda execution role.
  • Create a policy that grants the following permission:
  • Create the policy with the name StartGlueJobPolicy.
  • Attach the policy to the Lambda execution role.
  • Return to the Lambda function.
  • Add a new trigger and select Amazon S3.
  • Configure the trigger with the following settings:
    • Bucket: <your-s3-bucket-name>
    • Event Type: All Object Create Events
    • Prefix: raw/orders/
  • Add the trigger.
  • Upload a test file to the following S3 location:
    • raw/orders/
  • Monitor the Lambda function execution.
  • Verify that the Lambda function is invoked successfully after the file upload.
  • Confirm that a new AWS Glue Job run is started automatically.
  • Open Amazon CloudWatch Logs and review the lambda log stream.

Step X: Set Up the Snowflake Environment

  • Select the following configuration during setup:
    • Cloud Provider: AWS
    • Region: Ohio
  • Complete the account setup process.
  • In Snowsight, click + Add New and create a new SQL worksheet.
  • Run the following SQL command to create the project resources:
1
CREATE DATABASE retail_pipeline;
  • Verify that the commands execute successfully.
Confirm that the following resources are created:
  • Database: RETAIL_PIPELINE
  • Schema: RAW
  • Warehouse: RETAIL_WH
1
CREATE SCHEMA retail_pipeline.raw;
  • Warehouse: retail_wh
  • Verify that the warehouse is configured with:
    • Warehouse Size: XSMALL
    • Auto Suspend: 60 seconds
    • Auto Resume: Enabled
1
2
3
4
5
CREATE WAREHOUSE retail_wh
WITH
WAREHOUSE_SIZE = 'XSMALL'
AUTO_SUSPEND = 60
AUTO_RESUME = TRUE;
  • Verify that the RETAIL_PIPELINE database, RAW schema, and RETAIL_WH warehouse are visible in Snowsight.

Step XI: Integrate Snowflake with Amazon S3

  • Navigate to IAM → Roles and click Create role.
  • Configure the role with the following settings:
    • Trusted Entity Type: AWS Account
    • Use Case: Another AWS Account
  • Enter your AWS Account ID as a temporary placeholder.
  • Skip the permissions configuration.
  • Enter the role name:
    • SnowflakeS3AccessRole
  • Review the configuration and click Create role.
  • Open the newly created SnowflakeS3AccessRole role.
  • Verify that the role has been created successfully and copy the Role ARN from the Summary section for use in the next step.

Create and Configure the Snowflake Storage Integration

  • In Snowsight, create a new SQL worksheet.
  • Use the Role ARN copied from the previous step and run the storage integration SQL script.
1
2
3
4
5
6
CREATE STORAGE INTEGRATION retail_s3_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<your-aws-account-id>:role/SnowflakeS3AccessRole'
STORAGE_ALLOWED_LOCATIONS = ('s3://<your-bucket-name>/curated/');
  • Replace <your-account-id> with your AWS account ID before executing the script.
  • Execute the script and verify that the retail_s3_int storage integration is created successfully.
  • Confirm that the integration allows access to the curated data location in Amazon S3.

Retrieve Snowflake Integration Credentials

  • In Snowsight, open a SQL worksheet and run the command to view the storage integration details.
1
DESC INTEGRATION retail_s3_int;
  • Review the query results and locate the following properties:
    • STORAGE_AWS_IAM_USER_ARN
    • STORAGE_AWS_EXTERNAL_ID
  • Copy and save both values.
  • These values will be used to configure the trust relationship for the SnowflakeS3AccessRole IAM role in AWS.
  • Verify that the integration details are returned successfully before proceeding to the trust policy configuration.

Configure the IAM Role Trust Relationship for Snowflake

  • In the AWS Console, navigate to IAM → Roles → SnowflakeS3AccessRole.
  • Open the Trust relationships tab and click Edit trust policy.
  • Replace the existing trust policy with the policy generated for your Snowflake storage integration.
  • Update the following placeholders using the values copied from the integration details:
    • STORAGE_AWS_IAM_USER_ARN
    • STORAGE_AWS_EXTERNAL_ID
  • Save the trust policy changes.
  • Verify that the Trusted entities section displays the Snowflake IAM user ARN and External ID successfully.
  • Confirm that the trust relationship has been updated before proceeding to the S3 access permissions configuration.
  • Replace the placeholders with the values returned by DESC INTEGRATION.

Configure S3 Access Permissions for the Snowflake IAM Role

  • In the AWS Console, navigate to IAM → Roles → SnowflakeS3AccessRole.
  • Click Add permissions → Create inline policy.
  • Open the JSON editor and replace the existing policy with the provided S3 access policy.
  • Update the S3 bucket ARN values to match your environment before saving the policy.
  • Create the policy with the following name:
    • SnowflakeS3ReadPolicy
  • Verify that the inline policy is attached successfully to the SnowflakeS3AccessRole.
  • Confirm that the role has permissions to list the bucket and read objects from the curated S3 data location before proceeding.

Create the Snowflake Stage

  • In Snowsight, open a SQL worksheet.
  • Run the SQL commands to create a Parquet file format and an external stage that points to the curated data in Amazon S3.
1
2
CREATE FILE FORMAT retail_pipeline.raw.parquet_fmt
TYPE = PARQUET;
  • Verify that the parquet_fmt file format is created successfully.
  • Verify that the curated_stage external stage is created successfully.
  • Confirm that the stage is configured with:
    • Storage Integration: retail_s3_int
    • S3 Location: s3://<your-s3-bucket-name>/curated/
    • File Format: retail_pipeline.raw.parquet_fmt
  • Review the query results and ensure that both resources are created without any errors before proceeding.
  • Run:
1
2
3
4
5
6
7
CREATE FILE FORMAT retail_pipeline.raw.parquet_fmt
TYPE = PARQUET;

CREATE STAGE retail_pipeline.raw.curated_stage
URL = 's3://<your-s3-bucket-name>/curated/'
STORAGE_INTEGRATION = retail_s3_int
FILE_FORMAT = retail_pipeline.raw.parquet_fmt;

Verify Access to the Curated Data

  • In Snowsight, open a SQL worksheet.
  • Run the command to list the files available in the external stage.
  • Execute the query and wait for the results to load.
1
LIST @retail_pipeline.raw.curated_stage;
  • Verify that the curated Parquet files are displayed successfully.
  • Confirm that the results show file details such as:
    • File Name
    • File Size
    • Last Modified Timestamp
  • Verify that Snowflake can successfully access the curated data stored in Amazon S3 through the retail_s3_int storage integration.
  • Confirm that at least one Parquet file is returned before proceeding to the data loading steps.

Step XII: Load and Transform Product JSON Data in Snowflake

  • To enable Snowflake to read product data stored in Amazon S3, update the existing storage integration and create a dedicated stage for the product dataset.

Update the Storage Integration

  • Modify the retail_s3_int storage integration to allow access to both the curated dataset and the raw product data location.
  • Run the updated storage integration statement and ensure the following S3 path is included in the STORAGE_ALLOWED_LOCATIONS list:
    s3://<your-s3-bucket-name>/raw/products/
  • After executing the command, verify that the integration is updated successfully.

Create a JSON File Format

  • Create a file format for reading JSON files from the product dataset.
1
2
CREATE FILE FORMAT retail_pipeline.raw.json_fmt
TYPE = JSON;
  • Verify that the JSON_FMT file format is created successfully.

Create an External Stage for Product Data

  • Create a Snowflake stage that points to the product data stored in Amazon S3.
1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE STORAGE INTEGRATION retail_s3_int
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = 'S3'
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::<your-aws-account-no>:role/SnowflakeS3AccessRole'
STORAGE_ALLOWED_LOCATIONS = (
's3://<your-bucket-name>/',
's3://<your-bucket-name>//raw/products/'
);

  • Verify that the PRODUCTS_STAGE stage is created successfully before proceeding to the data loading and transformation steps.

Create an External Stage for Product Data

  • Create a Snowflake stage that points to the product JSON files stored in Amazon S3 and uses the previously created json_fmt file format.
  • Run:
1
2
3
4
CREATE STAGE retail_pipeline.raw.products_stage
URL = 's3://<your-s3-bucket-name>/raw/products/'
STORAGE_INTEGRATION = retail_s3_int
FILE_FORMAT = retail_pipeline.raw.json_fmt;
  • After executing the statement, verify that Snowflake returns a success message similar to:
Stage area PRODUCTS_STAGE successfully created.
  • This confirms that the products_stage external stage is configured and ready to access product data from the S3 bucket.

Verify Access to the Product Data

  • Validate that Snowflake can successfully access the product files stored in Amazon S3 through the external stage.
  • Run:
1
LIST @retail_pipeline.raw.products_stage;
  • Review the results and confirm that the product file is returned.
  • If the file is listed successfully, the products_stage external stage is correctly configured and Snowflake has access to the product dataset stored in Amazon S3.

Create a Raw Table for JSON Data

  • Create a landing table to store the raw JSON records from the product dataset. The table uses Snowflake’s VARIANT data type, which is optimized for semi-structured data such as JSON.
  • Run:
1
2
3
CREATE OR REPLACE TABLE retail_pipeline.raw.products_raw (
raw_data VARIANT
);
  • After executing the statement, verify that Snowflake returns a success message.
  • This table will be used to ingest the raw product JSON data before transforming it into a structured relational format.

Update the IAM Policy for Product Data Access

  • To allow Snowflake to access both the curated Parquet files and the raw product JSON files, update the existing SnowflakeS3ReadPolicy attached to the SnowflakeS3AccessRole.
  • Navigate to:
    • IAM → Roles → SnowflakeS3AccessRole → Permissions → SnowflakeS3ReadPolicy
  • Update the policy to include access to the following S3 locations:
    • s3://<your-s3-bucket-name>/curated/
    • s3://<your-s3-bucket-name>/raw/products/
  • Ensure the policy grants:
    for both the curated and product data paths.
    • s3:GetObject
    • s3:GetObjectVersion
    • s3:ListBucket
  • After saving the policy, verify that the updated SnowflakeS3ReadPolicy is attached to the SnowflakeS3AccessRole and reflects permissions for both datasets.

Load Product Data into Snowflake

  • Load the JSON product records from the external stage into the products_raw table.
  • Run:
1
2
3
COPY INTO retail_pipeline.raw.products_raw
FROM @retail_pipeline.raw.products_stage
FILE_FORMAT = (FORMAT_NAME = retail_pipeline.raw.json_fmt);
  • After the command completes, verify that the load operation is successful. The results should show:
    • Status: LOADED
    • Rows Parsed: 40
    • Rows Loaded: 40
    • Errors Seen: 0
  • A successful load confirms that the product JSON data has been ingested from Amazon S3 into the products_raw table and is ready for transformation and analysis.

Validate the Product Data Load

  • Verify that all product records were successfully loaded into the products_raw table.
  • Run:
1
2
SELECT COUNT(*) AS total_records
FROM retail_pipeline.raw.products_raw;
  • Review the query results and confirm that the expected number of records has been loaded.
  • A count of 40 records confirms that the product data was loaded successfully and is ready for transformation into a structured table.

Verify and Query the Product Data

  • Run the following query to extract product details from the JSON data and expand the tags array into individual rows:
1
2
3
4
5
6
7
SELECT
data:product_id::string AS product_id,
data:name::string AS product_name,
data:category::string AS category,
f.value::string AS tag
FROM retail_pipeline.raw.products_raw,
LATERAL FLATTEN(input => data:tags) f;
Expected result:
  • Product data is successfully parsed from the VARIANT column.
  • The tags array is flattened into separate rows.
  • Columns returned include:
    • product_id
    • product_name
    • category
    • tag
  • Multiple rows may be returned for a single product if it contains multiple tags.

Step XIII: Load Curated Orders Data into Snowflake

  • Create a table to store the curated order data loaded from the Parquet files in the Snowflake stage.
  • Run the following SQL command:
1
2
3
4
5
6
7
8
9
CREATE TABLE retail_pipeline.raw.orders_curated (
order_id STRING,
customer_id STRING,
product_id STRING,
order_date DATE,
quantity INT,
unit_price NUMBER(10,2),
order_total NUMBER(10,2)
);
  • Verify that the table is created successfully.

Load Curated Order Data

  • Load the Parquet files from the curated stage into the orders_curated table.
  • Run:
1
2
3
COPY INTO retail_pipeline.raw.orders_curated
FROM @retail_pipeline.raw.curated_stage
MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Validate the Data Load

  • Run the following query to verify that the curated order data was loaded into Snowflake:
1
2
SELECT COUNT(*)
FROM retail_pipeline.raw.orders_curated;
  • Verify the results:
    • Snowflake returns 193 records.
    • Amazon Athena returns 193 total rows and 193 distinct orders.
  • A matching record count across Snowflake and Athena confirms that the curated dataset was loaded successfully and without data loss.

Step XIV: Generate Business Insights and Visualizations

Create visualizations from the curated dataset to identify revenue trends, product performance, and category distribution.

(A) Category Revenue Analysis

This chart compares total revenue generated across product categories. Toys produced the highest revenue, followed by Electronics, while Sportswear contributed the least revenue. The visualization helps identify which categories drive the most business value and overall sales performance.

(B) Top Revenue-Generating Products

This chart ranks products based on total revenue generated. It highlights the highest-performing products and helps identify which items contribute the most to overall sales. These insights can be used to prioritize inventory planning, marketing efforts, and future sales strategies.

(C) Order Distribution by Product Category

This chart shows the number of orders recorded across each product category. Toys received the highest order volume, followed by Electronics and Home & Kitchen, while Sportswear had the fewest orders.

Step XV: Clean Up AWS and Snowflake Resources

After completing the project, delete all provisioned resources to avoid unnecessary charges and keep your AWS and Snowflake environments clean.

AWS Resources

  • Delete the S3 bucket used for storing raw and processed retail data.
  • Remove all files and folders inside the bucket before deleting it.
  • Delete any IAM users, roles, or policies created specifically for this project.
  • Remove CloudWatch logs or monitoring resources if configured.
  • Delete any additional AWS resources created during testing.

Snowflake Resources

  • Drop the orders_curated table.
  • Drop the raw_orders table if it was created.
  • Remove Snowflake stages used for loading data.
  • Delete file formats created for ingestion.
  • Drop the database created for the project (retail_pipeline) if no longer needed.
  • Suspend and delete any warehouses created exclusively for this project.
This cleanup helps prevent ongoing storage, compute, and infrastructure costs after the project is completed.

⚡ Why This Solution Matters

  • Creates a scalable and cost-effective cloud-native data platform.
  • Eliminates infrastructure management through serverless AWS services.
  • Improves data accessibility through centralized cataloging.
  • Automates data transformation and downstream processing after ingestion.

💻 Source Code & Repository

For the complete implementation, SQL scripts, AWS Glue ETL jobs, Lambda functions, IAM policies, and Snowflake configurations used in this project, refer to the project repository:
🔗 GitHub Repository: modern-retail-data-platform 

🎯 Conclusion

This project demonstrates a modern retail analytics platform combining AWS and Snowflake, spanning ingestion, transformation, cataloging, scoped access control, and analytics. By combining Amazon S3, AWS Glue, Lake Formation, Athena, Lambda, and Snowflake, the solution enables data transformation, cataloging, scoped access control, and analytics across AWS and Snowflake. The architecture provides a scalable foundation for building enterprise data lakes and analytics workloads while showcasing industry-standard data engineering and cloud data platform practices.

🌐 Explore More

To see more of my projects and technical work, feel free to visit:
GitHub: https://github.com/Knandwani07 
Dev Community: https://dev.to/khushi_nandwani07 
Medium: https://medium.com/@khushinandwanii 
Portfolio Website: https://main.d1n4wt6uo5bfx6.amplifyapp.com/ 
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article