Automating CSV Data Uploads to Amazon DynamoDB with AWS Services

Overview

The solution utilizes three fundamental AWS services. The first is Amazon S3, which holds uploaded CSV files. AWS Lambda manages CSV files as new uploads are received in Amazon S3, and Amazon DynamoDB functions as the target NoSQL database for the ingested records. The process is straightforward but effective. Uploading a CSV file to Amazon S3 invokes an AWS Lambda function, which imports the file, interprets its contents, and exports each row as an item to Amazon DynamoDB. This is event-driven and serverless, requiring no provisioning or infrastructure management and allowing automated, elastic data ingestion.

Introduction

Contemporary data pipelines must be fast, scalable, and have minimal human intervention. An ongoing problem is ingesting CSV files, which are ideally a favorite format for data exchange into scalable NoSQL databases like Amazon DynamoDB. AWS Lambda and Amazon S3 offer a serverless, event-driven model that makes this happen automatically. In this blog, we’ll discuss creating a solid workflow to import CSV data into DynamoDB through AWS Lambda, including architectural design, setup procedures, best practices, cost implications, error handling, and monitoring for a production-ready solution.

Prerequisites

Before you construct the pipeline, you should have the following:

• Active AWS account with Amazon S3, AWS Lambda, and Amazon DynamoDB access.
• An Amazon S3 bucket for loading CSV files.
• An Amazon DynamoDB table with an adequately designed schema for your data.
• AWS IAM roles and permissions for reading from Amazon S3 and writing to Amazon DynamoDB for AWS Lambda.
• Access to the AWS CLI or AWS Management Console.
• Prerequisites for basic knowledge of Python (or target AWS Lambda runtime) for creating AWS Lambda functions.

Solution Architecture and Workflow

Here is a step-by-step breakdown of the ingestion process:

1. Upload CSV to Amazon S3

• A user (or system) uploads a CSV file into a specified Amazon S3 bucket.
• Amazon S3 supports files up to 5TB, although AWS Lambda processing has other limits.

2. Amazon S3 Triggers AWS Lambda

• Amazon S3 bucket is set to trigger an AWS Lambda function whenever a new CSV file is uploaded (ObjectCreated event).
• Notifying the event is usually a matter of seconds, with a guarantee by Amazon S3 for at least one event delivery.

3. AWS Lambda Reads and Parses CSV

• The AWS Lambda function from Amazon S3 reads the CSV file using the event metadata.
• Line-by-line or in chunks for big files, CSV parsing, is performed with efficient parsing libraries such as pandas or the built-in CSV module.
• AWS Lambda processes the files up to its memory capacity (10GB) and timeout capacity (15 minutes).

4. Records to Amazon DynamoDB

• The CSV row is mapped one-to-one to an item in Amazon DynamoDB.
• For performance and cost, Lambda performs batch write operations (BatchWriteItem API, up to 25 items per call).
• If any items can’t be processed due to throughput, the AWS Lambda retries those items using exponential backoff.
• Amazon DynamoDB supports up to 40,000 WCUs per table in on-demand and more with a service limit increase.

5. Logging, Monitoring, and Error Handling

• AWS Lambda writes success and error data to Amazon CloudWatch Logs.
• Amazon CloudWatch Alarms and Amazon SNS can be set to notify us of errors or throughput anomalies.
• Common metrics include Amazon DynamoDB throttled requests, AWS Lambda errors, and execution duration.

6. Complex Flow: AWS Step Functions and Amazon SQS for Large Files

For extremely large CSV files (>100MB):

• Initial Processing: The large CSV is split into smaller chunks by a “splitter” Lambda function, which saves the chunks in Amazon S3.
• Orchestration: AWS Step Functions orchestrate the parallel processing of the chunks.
• Decoupling: Amazon SQS queues buffer the processing work, enabling retry features and avoiding data loss.
• Parallel Processing: Parallel Lambda functions process the chunks concurrently, each writing to DynamoDB.
• Aggregation: A last AWS Lambda function validates all chunks processed and modifies a status record.

This method can process CSV files of nearly any size without sacrificing the serverless model’s advantages.

Workflow Diagram:

Cost Considerations

Service-by-Service Cost Breakdown

AWS Lambda Costs

• Request pricing: $0.20 per 1 million requests
• Compute pricing: $0.0000166667 per GB-second
• Example: A 1GB AWS Lambda processing 100 files daily, averaging 30 seconds per execution:
  • Requests: 100 × 30 days × $0.20/1M = negligible
  • Compute: 100 × 30 days × 30 seconds × 1GB × $0.0000166667 = $1.50/month

Amazon DynamoDB Costs

• On-Demand: $1.25 per million write request units
• Provisioned: Starting at $0.00065 per WCU-hour (plus storage)
• Example: Ingesting 10 million records monthly (1KB each):
  • On-Demand: 10M × $1.25/1M = $12.50/month
  • Provisioned: ~4 WCUs × 24 × 30 × $0.00065 = ~$1.87/month (plus Auto Scaling buffer)
• Storage: $0.25 per GB-month

Amazon S3 Costs

• Storage: $0.023 per GB-month (Standard tier)
• PUT/COPY/POST/LIST: $0.005 per 1,000 requests
• GET: $0.0004 per 1,000 requests
• Example: 1GB of CSV files stored and processed monthly:
  • Storage: 1GB × $0.023 = $0.023/month
  • Requests: Typically, negligible for this use case

Monitoring Costs

• Amazon CloudWatch Logs: $0.50 per GB ingested
• Amazon CloudWatch Metrics: $0.30 per metric per month (first 10 metrics free)
• Amazon SNS (Simple Notification Service): $0.50 per million notifications (first 1 million free)

Conclusion

You can guarantee efficient, robust, and cost-efficient data ingestion by using best practices for chunking large files, batch writing, strong error handling, and end-to-end monitoring.

Drop a query if you have any questions regarding Amazon DynamoDB or AWS Lambda and we will get back to you quickly.

About CloudThat