如何在AWS中实现销售CSV数据的自动化清洗并对接QuickSight与SageMaker？

Hey Ross, let’s break down your options and figure out the best approach to automate your sales data cleaning pipeline, tailored to your existing code and end goals with QuickSight and SageMaker.

Core Context First #

You already have a solid Python-based cleaning workflow (date parsing, feature generation, store splitting) for small-to-medium CSV files (721KB now, incremental later). Your key needs are:

• Auto-trigger cleaning when new files hit S3
• Make clean data accessible to QuickSight and SageMaker
• Reuse your existing code as much as possible

Option 1: AWS Lambda + S3 Event Trigger (My Top Pick) #

This is the simplest, most cost-effective path for your current scale, and lets you reuse your Jupyter code almost verbatim.

How to Set It Up #

1. Package your dependencies: Since Lambda doesn’t include pandas by default, create a Lambda Layer with pandas, pyarrow (for Parquet output), and any other libraries you use. Or use a pre-built public pandas layer from the AWS Serverless Application Repository.
2. Configure S3 event trigger: Set up your raw data S3 bucket to fire a PutObject event (when new CSVs are uploaded to a raw-data/ prefix) that triggers your Lambda function.
3. Adapt your Python code for Lambda:
   • Use boto3 or pandas’ native S3 support to read the uploaded file (e.g., pd.read_csv("s3://your-bucket/raw-data/new-file.csv"))
   • Run your existing cleaning logic: strip the "day " prefix from dates, convert to datetime, generate dayofweek/dayofmonth features, split data by store
   • Write clean data back to S3 (preferably as Parquet for better performance/cost) into a clean-data/ prefix, organized by store (e.g., s3://your-bucket/clean-data/store-1/, s3://your-bucket/clean-data/store-2/)
4. Add optional metadata management: If you want Athena/QuickSight to auto-detect the clean data, use Lambda to update the Glue Data Catalog (register the Parquet files as a table or partition).

Pros #

• Zero code rewrite: Your existing Jupyter logic works with minimal tweaks
• Cost-efficient: Lambda charges by execution time (processing your 721KB file will cost fractions of a cent)
• Fast to deploy: You can have this pipeline running in an hour
• Perfect for incremental small files: Fits your 13-store use case perfectly

Cons #

• Execution limits: Lambda maxes out at 15 minutes runtime—this won’t be an issue unless your files grow to GB-scale later
• Concurrency constraints: If you upload dozens of files at once, you might hit Lambda’s default concurrency limit, but this is adjustable or avoidable with batch processing

Option 2: AWS Glue ETL + S3 Event Trigger #

Choose this if you expect your data volume to grow significantly (GBs+) or need to add complex ETL steps later (e.g., joining with inventory data).

How to Set It Up #

1. Convert your code to a Glue Job: You can use either:
   • Glue Python Shell Job: Runs your existing pandas code (no Spark required) with access to S3
   • PySpark Job: Rewrite your logic to use Spark DataFrames for distributed processing (better for large files)
2. Trigger the job via S3 events: Use CloudWatch Events or S3’s direct Glue trigger to start the job when new raw files are uploaded.
3. Output clean data: Write to S3 as Parquet, and let Glue auto-register the data to its Data Catalog (so Athena/QuickSight can query it instantly).

Pros #

• Scalable: Handles large files or batches of files with distributed processing
• Built-in metadata management: Glue Catalog keeps your data organized for analytics tools
• Extensible: Easy to add steps like data validation, aggregation, or joining with other datasets later

Cons #

• Higher complexity: Configuring Glue requires more setup than Lambda
• Higher cost: Glue charges by DPU hours (more expensive than Lambda for small files)
• Potential code rewrite: If using PySpark, you’ll need to adjust your pandas logic to Spark’s API

Option 3: Athena + Glue Crawler (SQL-Based) #

This is a good fit if you prefer SQL over Python and don’t need to split stores into separate files.

How to Set It Up #

1. Use Glue Crawler: Scan your raw S3 bucket to create a Glue table with the raw data schema.
2. Write an Athena CTAS query: Use SQL to clean the data:

   CREATE TABLE clean_sales_data
   WITH (format = 'PARQUET', location = 's3://your-bucket/clean-data/')
   AS
   SELECT
     store #,
     DATE(SUBSTR(date, 5)) AS cleaned_date,
     DAY_OF_WEEK(DATE(SUBSTR(date, 5))) AS dayofweek,
     DAY_OF_MONTH(DATE(SUBSTR(date, 5))) AS dayofmonth,
     DAY_OF_YEAR(DATE(SUBSTR(date, 5))) AS dayofyear,
     manager-generated forecast,
     actual sales
   FROM raw_sales_data;
   

3. Auto-trigger: Use CloudWatch Events to run the CTAS query when the Glue Crawler finishes updating the raw table (triggered by S3 uploads).

Pros #

• No Python code needed: Great if you’re more comfortable with SQL
• Cost-effective: Athena charges by the amount of data scanned

Cons #

• Less flexibility: Splitting data into individual store datasets is clunky with SQL
• Trigger setup is more complex: Requires linking S3 → Crawler → CloudWatch → Athena

Final Recommendation #

Start with Lambda + S3 Event Trigger—it’s the fastest, cheapest way to reuse your existing code and meet your immediate needs. You can always migrate to Glue later if your data grows or your ETL needs get more complex.

Extra Tips for Success #

• Use Parquet for clean data: It’s compressed, columnar, and way faster for QuickSight/SageMaker to read than CSV. Use df.to_parquet() in your Lambda code.
• Organize clean data by store: Storing each store’s data in its own S3 directory makes it easier for SageMaker to load specific store datasets and for QuickSight to build store-specific dashboards.
• Add error handling: In Lambda, catch exceptions and send alerts via SNS, or move failed files to a failed-data/ bucket prefix for debugging.
• Lock down IAM permissions: Make sure Lambda/Glue only has access to the S3 buckets and Glue resources it needs—follow the principle of least privilege.

内容的提问来源于stack exchange，提问作者Ross Dickinson