Loading a CSV From S3 Into a Database Without a Local Copy

When files live in S3, the reflex is to download one to a server and load it from the local disk. It works, and it adds a disk, a cleanup job and a place where a half-downloaded file can be mistaken for a whole one. There are cleaner paths, and which you choose depends on where the database runs and who may read the bucket. This article goes through four, in the order I would try them, and the checks that apply to all. It is the cloud-storage companion to automating CSV imports into a database, and the load commands themselves are covered in the PostgreSQL COPY article and the MySQL import article.

Option one: pipe the object into the client

If you can run a client next to the database, the simplest path is a pipe. The AWS CLI writes an object to standard output when the destination is -, and psql reads standard input for \copy:

aws s3 cp s3://example-exports/orders/orders-2026-10-02.csv - \
  | psql "$DATABASE_URL" -c "\copy orders_stage FROM STDIN WITH (FORMAT csv, HEADER true)"

Nothing is written to disk, memory stays flat because both ends stream, and the database does the CSV parsing under its own rules. The pipe has a failure mode: in a shell pipeline, the exit status is that of the last command unless you set set -o pipefail, so a failed download that produced a short stream can look like a successful load of a truncated file. Always run the pipeline with set -euo pipefail and check the row count afterward against what you expect (testing that a CSV export was not truncated). In MySQL the equivalent is LOAD DATA LOCAL INFILE from a named pipe or a downloaded file; since local_infile carries its own risks, I prefer downloading to a temporary directory for MySQL.

Option two: let the database read the bucket

Amazon RDS and Aurora for PostgreSQL ship the aws_s3 extension. After you enable it and attach an IAM role that can read the bucket to the instance, a SQL function does the import server-side:

CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE;

SELECT aws_s3.table_import_from_s3(
    'orders_stage',
    'order_id, customer, placed_on, total',
    '(FORMAT csv, HEADER true)',
    aws_commons.create_s3_uri('example-exports', 'orders/orders-2026-10-02.csv', 'eu-west-1')
);

The function returns a summary of the rows imported. It reads under the instance's role, so there are no credentials in the SQL or in your script, and the data travels from S3 to the database inside AWS. The cost is that the import runs on the database host, competing with queries, and that the role's permission is on the instance and not on the caller: anyone who can call the function can import any object the role can read. Scope the role tightly (the next section) and restrict who may execute the function. The behavior is described in the RDS PostgreSQL documentation for importing from S3. Aurora MySQL has LOAD DATA FROM S3 for the same purpose.

For PostgreSQL that you run yourself, there is no built-in S3 reader, and options one and three apply.

Option three: DuckDB as the reader

DuckDB reads CSV from S3 through its httpfs extension, which is useful when you want to look at or validate a file before it goes anywhere:

INSTALL httpfs;
LOAD httpfs;
CREATE SECRET s3_loader (TYPE s3, PROVIDER credential_chain, REGION 'eu-west-1');

SELECT count(*) AS n, min(placed_on) AS first_day, max(placed_on) AS last_day
FROM read_csv('s3://example-exports/orders/orders-2026-10-02.csv');

The credential_chain provider uses the usual AWS lookup (environment, profile, instance role), so nothing sensitive sits in the script. Because DuckDB reads only the byte ranges it needs from Parquet and streams CSV, a count over a large object does not require downloading it to disk first. A pattern I like for a daily feed is to run a DuckDB query as the validation step (row count, date range, null counts), and only then run the load through option one or two. DuckDB's CSV reading options lists the parameters, and a glob such as s3://example-exports/orders/orders-*.csv reads many objects at once (querying a folder of CSV files shows the same idea locally). DuckDB can also write the result into PostgreSQL through its extension, which makes it a complete loader for a one-off backfill.

Option four: download, then load from disk

Sometimes the plain approach is right. Download the object to a temporary directory, verify it, load it with the local file's tools, and delete it. It is the only choice for databases whose loaders need a file path (SQLite's .import, MySQL's LOAD DATA) and for jobs that checksum the file before loading:

aws s3 cp s3://example-exports/orders/orders-2026-10-02.csv "$TMPDIR/orders.csv"
sha256sum "$TMPDIR/orders.csv"

Use a directory that only the job can read, remove it in a trap on exit, and do not reuse a fixed file name, so that two concurrent runs do not read each other's download. The cost over a pipe is the disk and the cleanup, and the benefit is that a failure leaves a file you can inspect.

Permissions: read one prefix

Whichever option you choose, the identity that reads the bucket should be able to do one thing: get objects under one prefix. A policy for that looks like this:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["s3:GetObject"],
      "Resource": "arn:aws:s3:::example-exports/orders/*"
    },
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket"],
      "Resource": "arn:aws:s3:::example-exports",
      "Condition": { "StringLike": { "s3:prefix": ["orders/*"] } }
    }
  ]
}

GetObject on the prefix reads the files, and ListBucket limited to the prefix lets the job find new ones. Resist the shortcut of s3:* on *, which is how a loader's compromised credentials become a copy of every bucket in the account. Bucket-level controls matter too: block public access, require TLS and turn on versioning if you need to know when a key was overwritten.

Knowing which file you loaded

A key such as orders/latest.csv is overwritten every day, so the key alone cannot identify a load. Record something that changes with the content: the object's ETag (an MD5 for a simple upload, which is not a content hash for multipart uploads), its version ID if versioning is on, or a SHA-256 you compute yourself or get from the producer in object metadata. Put it in the ledger table keyed so a repeat is skipped, as in idempotent CSV imports that are safe to run twice. Prefer date-stamped keys for new feeds; they make the history a listing, and a rerun is a choice of key and not of luck.

Events can drive the job. An S3 event notification on ObjectCreated can post to a queue, and a worker loads each object once. The same idempotency rule applies: event delivery is at least once, so a message can arrive twice, and the ledger turns the second into a no-op. Make the worker check that the object is complete (a manifest object written last is the S3 version of the atomic rename).

When the data should not move at all

If the data sits in S3 and is queried a few times a day, the best load is none. Query it in place with DuckDB, Athena or the warehouse's external tables, and keep Parquet beside the CSV for repeated analysis (why Parquet beats CSV for repeated analysis). Loading is worth its cost when you need transactions, updates, joins with live tables or low-latency queries, and when you do, aim the load at a staging table and merge.

It is also not worth building if one person loads one file a quarter. A manual download and a \copy takes five minutes and has no moving parts. The AWS CLI's behavior is documented in the aws s3 cp reference and DuckDB's S3 support in its httpfs documentation.