How to Write Pandas to Parquet and Partition an S3 Export by Date

Why a flat Parquet key kills partition pruning

The export serializes a DataFrame with df.to_parquet(buf) and pushes the bytes to S3. That part is fine - pandas to Parquet is exactly the right format for a data lake. The problem is the key it writes to:

key = "orders.parquet"
s3.put_object(Bucket="lake", Key=key, Body=buf.getvalue())

A single flat object, overwritten daily. When the analytics team points Athena, Spark, or Trino at s3://lake/, there is no partition column in the path, so a query like WHERE year = 2026 AND month = 5 cannot skip anything - it reads every byte of every file under the prefix. On a dataset with months or years of history, that is the single most expensive mistake you can make, because Athena bills by data scanned.

The engines all understand one universal layout: Hive-style partitions, where each partition column is encoded into the directory path as key=value/.

s3://lake/orders/year=2026/month=05/day=24/orders.parquet
s3://lake/orders/year=2026/month=05/day=25/orders.parquet

Now WHERE year = 2026 AND month = 5 touches only the matching directories and prunes the rest before reading a single row.

Compute the Hive-partitioned key from today's date

The DataFrame is already being serialized correctly - all that changes is the S3 key. today is a datetime.date computed just above the key. Build the path from its fields, zero-padding month and day to two digits (the convention every engine expects):

from datetime import date

today = date.today()

key = (
    f"orders/year={today.year}/month={today.month:02d}/"
    f"day={today.day:02d}/orders.parquet"
)

s3.put_object(Bucket="lake", Key=key, Body=buf.getvalue())

The :02d format spec is what turns month 5 into month=05. Athena registers partition values as strings, and mixing month=5 with month=05 creates two distinct partitions - so pad consistently everywhere.

Run the export and confirm the layout:

cd /workspace && python3 export.py
aws s3 ls --recursive s3://lake/

You should see a single object under orders/year=.../month=.../day=.../orders.parquet and nothing at the bucket root.

Clean up the old flat object

If a previous run wrote s3://lake/orders.parquet at the root, it lingers - S3 keys are independent, so writing a new partitioned key does not remove the old one. A stray flat file at the prefix root confuses partition discovery, so delete it:

aws s3 rm s3://lake/orders.parquet

How the pieces fit for readers

The partition values live only in the path, not inside the Parquet file. When DuckDB or Athena crawls the tree it reconstructs year, month, and day as virtual columns from the directory names. That is why the whole scheme works with zero extra metadata:

-- DuckDB reads the Hive layout natively
SELECT * FROM 's3://lake/orders/**/*.parquet'
WHERE year = 2026 AND month = 5;

Production exports almost always partition by load date this way, and often by tenant or region on top. Tools like dbt with Iceberg or Delta Lake generate these exact paths for you - the layout you wrote by hand is what those frameworks produce under the hood.