DEV Community

Cover image for Large CSV imports on AWS without home-grown queues and workers
Sascha Mirow-Stanitzok
Sascha Mirow-Stanitzok

Posted on Edited on Originally published at sascha-ms.de

Large CSV imports on AWS without home-grown queues and workers

A large CSV lands in S3 every night. One Lambda function reads it and pushes rows to SQS, a worker processes them, and the next morning someone asks a simple question: did every row make it? Nobody can say for sure, because the setup has no real start, no end and no summary. I have seen this improvised pipeline in more Serverless projects than I can count.

The Step Functions Distributed Map is my preferred answer to that question. You describe which file to read, how big the chunks are and how many may run in parallel, and Step Functions handles the fan-out, a failure threshold and a result report. My full tutorial, Step Functions Distributed Map: Process CSV with Lambda, builds a complete import with the Serverless Framework and TypeScript. Here is the condensed version.

Inline Map or Distributed Map?

The regular (inline) Map runs inside the parent execution, which brings tight limits: 256 KiB of input, one shared history capped at 25,000 entries and at most 40 parallel iterations. If any of these three limits gets in your way, it is time for the Distributed Map. Otherwise I would stay with the inline version, because it is simpler and needs fewer permissions.

In distributed mode, every item or batch becomes its own child workflow with its own history, and data is read straight from S3 (CSV, JSON, JSON Lines, Parquet and more). The parent has to be a Standard workflow, while the children can be Express, which suits short batch handovers to Lambda.

The few settings that matter

The example in the article is a nightly catalog import for a city library: a semicolon-separated export with ISBN, title, author, year and copies. The Map state configuration fits on one screen, and a handful of fields do the heavy lifting:

ItemReader:
  Resource: arn:aws:states:::s3:getObject
  ReaderConfig:
    InputType: CSV
    CSVHeaderLocation: FIRST_ROW
    CSVDelimiter: SEMICOLON
ItemBatcher:
  MaxItemsPerBatch: 500
MaxConcurrency: 20
ToleratedFailurePercentage: 5
Enter fullscreen mode Exit fullscreen mode

Batching is the biggest cost lever: 200,000 rows become 400 child workflows instead of 200,000. MaxConcurrency is the one setting I would never skip, because without it Step Functions may start up to 10,000 child workflows at once and your database will notice. ToleratedFailurePercentage lets the run continue despite a few bad items, and a ResultWriter writes the outcome to S3 instead of blowing up the execution output beyond 256 KiB.

Lessons from building it

A few points I would pass on to anyone trying this for the first time:

  • Separate business errors from technical ones. With batching, a thrown exception fails all 500 rows. The Lambda function therefore returns invalid rows (such as a wrong ISBN check digit) as rejected entries and only throws when something technical breaks.
  • Put retries inside the child workflow. A retry on the Map state itself starts a completely new Map Run, including the batches that already succeeded.
  • Expect IAM to be the hurdle. The state machine role needs states:StartExecution on its own state machine, because each child is technically a new execution, plus narrowly scoped S3 read and write access. Building the ARN as text avoids a circular dependency in CloudFormation.
  • Watch for Excel quirks. A byte order mark in the export can garble the first column name.

The Distributed Map is not the answer to everything. For loading a file into DynamoDB without logic, Bulk Put Items is simpler, and for an endless stream of events, SQS with partial batch response fits better. Its strength is a job with a clear end, where you want to know exactly what worked and what did not. That built-in bracket is what I value most.

What the full article covers

  • The complete serverless.yml with state machine, Express child workflows, bucket, table and all IAM permissions
  • TypeScript code for row validation and batched DynamoDB writes with backoff, plus Vitest tests and a CSV generator
  • How to deploy, start and monitor a run with 200,000 rows, and what the ResultWriter files contain
  • Common mistakes, a cost breakdown and a comparison with Bulk Put Items, SQS and the inline Map

Read the full tutorial: Step Functions Distributed Map: Process CSV with Lambda

📖 This is the condensed version. The full article on sascha-ms.de goes deeper – with all code samples, diagrams and a short knowledge check.


👉 Next step: Not sure which AWS service fits your use case? My AWS service finder asks a few questions and points you to the right one.

📬 Stay updated: New articles on serverless, AI and leading tech teams via RSS, browser push or the weekly newsletter (in German).

Originally published on sascha-ms.de. More articles on serverless, AI in everyday development and leading tech teams: sascha-ms.de/en

Top comments (0)