Bulk Loading a CSV File Into DynamoDB Tables Properly

DynamoDB is a key-value and document database, and a CSV is a table, so the first thing to settle is what a row becomes. Each CSV row is an item with a partition key, optionally a sort key, and a set of other attributes. The loading mechanics come second, and there are only two real options: the managed import from S3, which is fast and creates a table, and a script that writes items, which is slower and works on a table you already have. This article walks through both, with the pitfalls of each: string-only attributes in the import, the Decimal requirement in the script, how empty cells behave and what a rerun does. It sits with the engine articles in loading CSV into cloud warehouses.

Decide the key first

Before any load, decide the primary key. In DynamoDB it is not a detail: the partition key decides how data is spread and how it can be read, and it cannot be changed on a table once created. For an orders table, order_id as the partition key gives you fast lookup by order and nothing else. A query such as "orders for a customer" needs a global secondary index on customer. Designing the access patterns comes before the CSV, and the CSV only has to supply the key columns without blanks or duplicates. Check the file for both (finding duplicate keys in a CSV), because DynamoDB overwrites on the key and the duplicate is lost without a message.

Key attribute types matter. A key is a string, a number or binary. A numeric-looking identifier such as order_id can be either, and the choice is hard to undo: if other systems send it as text, make it a string, and if it has leading zeros it must be a string. Zip codes and account numbers are strings.

Option one: import from S3

AWS can create a table from files in S3, in CSV, DynamoDB JSON or Amazon Ion. It does not consume write capacity, it is billed per GB imported, and it is the fastest way to start a table from a large file. The command names the source, the input format and the new table:

aws dynamodb import-table \
  --s3-bucket-source S3Bucket=example-exports,S3KeyPrefix=orders/ \
  --input-format CSV \
  --input-format-options '{"Csv":{"Delimiter":",","HeaderList":["order_id","customer","zip","total","note"]}}' \
  --table-creation-parameters '{
      "TableName":"Orders",
      "KeySchema":[{"AttributeName":"order_id","KeyType":"HASH"}],
      "AttributeDefinitions":[{"AttributeName":"order_id","AttributeType":"S"}],
      "BillingMode":"PAY_PER_REQUEST"}'

The limits are the point. The import creates a new table and cannot load into an existing one. With a CSV source, every attribute other than the key attributes is imported as a string, so total arrives as the text "120.50" and not a number, and any sort or arithmetic on it needs a conversion step afterward. The header names come from HeaderList when the file has no header, or from the file's first line otherwise. Rows that fail to import, because a key is missing or the wrong type, are recorded in a CloudWatch Logs group for the import, with a count in the import's description; check the error count before the table goes into use.

This option suits a first load of a big file into a fresh table, when strings are acceptable or you will transform later. It does not suit an incremental feed.

Option two: a script with batch_writer

For an existing table, or when you need typed attributes, write the items yourself. boto3's Table.batch_writer() wraps BatchWriteItem, which accepts up to 25 put or delete requests per call, and it buffers, flushes and resends unprocessed items for you:

import csv, sys
from decimal import Decimal

import boto3

def to_item(row):
    item = {
        "order_id": row["order_id"],                 # key stays a string
        "customer": row["customer"],
        "zip": row["zip"],                           # identifier: keep the zero
        "total": Decimal(row["total"]),              # boto3 rejects float
    }
    if row["note"] != "":                            # omit empty attributes
        item["note"] = row["note"]
    return item

table = boto3.resource("dynamodb").Table("Orders")
with open(sys.argv[1], newline="", encoding="utf-8-sig") as f, table.batch_writer() as batch:
    for n, row in enumerate(csv.DictReader(f), start=1):
        batch.put_item(Item=to_item(row))
print(f"wrote {n} items")

Check the conversion without touching AWS. Run on two rows, one with an empty note and one with a quoted comma:

{'order_id': '1001', 'customer': 'Acme Ltd', 'zip': '02134', 'total': Decimal('120.50')}
{'order_id': '1002', 'customer': 'Beta, Corp', 'zip': '90210', 'total': Decimal('89.90'), 'note': 'said "hi"'}

Three decisions are visible. The zip stayed '02134', a string. The total is a Decimal. The first item has no note at all, because the empty cell was omitted.

**Why Decimal.** DynamoDB's number type is a decimal of up to 38 digits, and boto3 refuses Python floats with a TypeError saying float types are not supported and to use Decimal. Build the Decimal from the text, not from a float: Decimal("89.90") is exact, and Decimal(89.9) is Decimal('89.900000000000005684341886080801486968994140625'), the float's binary expansion, which then goes into the database. Parse from the CSV string and the problem never arises.

Why omit blanks. DynamoDB lets non-key attributes hold empty strings now, and an empty string is still a value that takes space, shows up in filters and means "present but blank". Whether blank and absent should differ is a modeling choice. For a CSV where empty means "no value", omit the attribute, which is the common choice, and key attributes can never be empty.

What the batch writer does for you. It collects items into batches of 25, sends them, and resends any the service returns as unprocessed, which happens when throttled. It does not apply conditions, so there are no conditional puts in a batch, and it does not guarantee order. If a batch contains two items with the same key, the call fails with a validation error unless you tell the writer to deduplicate: table.batch_writer(overwrite_by_pkeys=["order_id"]) keeps the last item for each key in a buffer.

Throughput, cost and reruns

Writes cost write capacity units, one per kilobyte of item per write. On a table in on-demand mode, DynamoDB scales to the traffic, with a pay-per-request price, and a big load is a bill; on a provisioned table, a load faster than the provisioned write capacity is throttled, and the batch writer slows down as it retries. Loading millions of items from one process is limited by round-trip time, so for large files, split the file and run several writers, keeping the key space spread across partitions so that no single partition is hot.

Reruns are the good news. PutItem and the puts inside a batch replace an item with the same key, so loading the same file twice leaves the same table, which is idempotency by key. It cuts the other way as well: a corrected file overwrites old items, and a duplicate key in one file means the last row wins with no warning. Count distinct keys against rows in the file before loading. An item that no longer appears in a new full file is not deleted, since a put never deletes, so a snapshot feed needs a separate pass to remove the keys that disappeared. For a ledger of which files were loaded, use the pattern in idempotent CSV imports that are safe to run twice.

When DynamoDB is the wrong target for the file

DynamoDB is an access-pattern database, and a CSV is a table full of rows people want to query in ways nobody has planned. If the plan is "load the file, then run ad hoc analysis", the answer is not DynamoDB: use a SQL engine, or query the file in place (querying a CSV file with DuckDB instead of loading it). Aggregations, joins and range scans over arbitrary columns are expensive or impossible in DynamoDB without careful table design and indexes, and a full scan of a large table bills for every item read.

If the data is a reference table you read by key, millions of rows keyed by an ID and fetched one at a time, it is a fine fit, and the loads above are enough. The AWS documentation for the S3 import is the data import guide, and boto3's batch writer is described in its DynamoDB reference.