S3 Data Lake Folder Structure and Partition Design

More than a million data lakes run on AWS today, and S3 is the quiet foundation under nearly all of them. That scale is why folder structure matters: get it wrong, and you're not looking at a nuisance, you're looking at a data swamp that only gets worse the more you feed it.

The swamp pattern is depressingly consistent. Data lands wherever it's convenient to write it, not where it's useful to read it. Partitions get chosen by whoever's writing the ingestion script that week. Millions of tiny files pile up quietly in the background, until one day a query that used to take ten seconds takes ten minutes. At ten million files, an Athena query has to open ten million files. Each one is its own API call to list, its own call to open, its own entry to catalog. No partition pruning means every query scans everything, every time. No catalog means no schema discovery, no access control, no lifecycle rules, just a bucket full of stuff nobody trusts.

The root cause is never volume. Volume is just what makes the mistake visible. Folder layout got treated as an afterthought, something to sort out later, instead of the first decision made. Good S3 data lake design is a partitioning problem before it's anything else. Zone layout, file format, naming conventions, catalog integration: all of it flows downstream from one question. How will this data get queried? Not how it arrived. How it'll get read, six months from now, by someone who wasn't in the room when it was ingested.

How the three-zone layout maps the data lifecycle before any partitioning begins

Most well-run data lakes split into three or four zones, and each one carries its own prefix structure and its own partitioning logic. Think of it less as a filing system and more as a pipeline with checkpoints.

The raw zone (sometimes called bronze, or landing) holds source data exactly as it arrived. JSON logs, CSV exports, binary files, images, whatever the source system produces. No transformation happens here. This is the audit trail. If something breaks three steps downstream, reprocessing starts here, always. Partitioning in raw is by the date the data showed up, not the date the underlying business event happened, and access is locked down to service principals only, enforced through IAM policies. No humans poking around.

The curated zone (silver, or processed) is where data actually gets cleaned up: deduplicated, typed, schema-enforced, converted to Parquet. Column names get standardized, timestamps get normalized to UTC, null handling gets made consistent across sources that used to disagree about what "missing" means. This zone partitions by business date, and users get access through AWS Lake Formation rather than raw bucket permissions. Snappy compression is the standard here for the best balance of read speed and file size, and the target file size is around 128 MB.

Consumption zone (gold, analytics) is the last stop: aggregated, denormalized tables built for specific workloads. Pre-computed KPIs, dimensional models, feature stores for machine learning. Athena, Redshift Spectrum, and Snowflake external tables all point here. Partitioning shifts to business dimensions, region, product line, customer segment, rather than time alone. Format stays Parquet with Snappy, file sizes climb up toward 128 MB to 1 GB.

There's a fourth zone that often gets left off the architecture diagrams entirely: logs. S3 access logs, CloudWatch logs, CloudTrail logs, all kept separate from the analytical data, partly for security reasons and partly because mixing operational logs with business data makes cost tracking a nightmare.

The zone a file lives in decides everything about it: partitioning logic, file format, compression, who can touch it, how long it sticks around before lifecycle rules delete or archive it. None of these are separate decisions made in isolation. They're one decision, made once, when you pick the zone.

The canonical folder structure and what each path segment does

The reference pattern, drawn from AWS's own architecture guidance, looks something like this:

data-lake-bucket/
  raw/<Line-of-Business>/<Database-Name>/<Table-Name>/Partition1/Partition2/.../Data-Files
  transformed/<Line-of-Business>/<Database-Name>/<Table-Name>/...
  curated/<Business-Unit>/<Database-Name>/<Table-Name>/partitions/data-files
  logs/s3-access-logs/  cloudwatch-logs/  cloudtrail-logs/

Every segment in that path is doing a job, not just sitting there for looks.

The zone prefix (raw/, curated/) is the access control boundary. It's also where lifecycle policies attach and where cost gets attributed back to a business unit. The line-of-business or business-unit segment drives IAM policy scoping, so a finance team's access doesn't accidentally bleed into marketing's tables. The database and table segment mirrors the source system, which makes lineage traceable when someone asks "where did this number come from" (and someone always asks). Everything below that, the partition segments, is the actual query-pruning mechanism. That's the part the query engine reads. Everything above it is just bookkeeping.

Naming conventions aren't cosmetic, either. Lowercase only, and partitions written as key-value pairs (year=2025, not just 2025) so that MSCK REPAIR TABLE can auto-discover them without manual intervention. This is the Hive-style convention, year=2025/month=12/day=07/, and it's recognized across the board: AWS Glue crawlers, Athena, Spark, Presto through the Hive Metastore. Skip the key-value format and auto-discovery is lost, full stop.

A few operational habits tend to travel with a well-built structure. Production buckets sit in a separate AWS account from non-production, so a bad staging script can't touch live data. Versioning stays enabled as a guard against accidental deletes. Data that needs cross-region replication gets its own bucket rather than getting bolted onto an existing one. And lifecycle rules at the bucket or prefix level handle retention and archival, which matters when business rules and folder structure inevitably drift apart over time.

Choosing partition keys: matching folder depth to query patterns

Partition by how people query the data, not by how it arrives or how the source system happens to organize it: that's the rule that decides everything downstream.

Start by looking at query filters, not table schemas. If 90% of queries filter on date, partition by year, month, day. If those same queries also filter by region, add region as a second partition: year=2026/month=02/region=us-east/. But if queries almost never filter on time and instead always filter on tenant or product, then time is the wrong primary partition key, no matter how natural it feels to organize by date.

Hive-style partitioning is the standard for a reason. Each partition is a folder, named column_name=value, and Athena, Redshift Spectrum, and Spark all recognize the pattern automatically. Query engines scan only the partitions that match the filter, which cuts both query time and query cost. The key-value format isn't optional flavor, either, it's what MSCK REPAIR TABLE needs to auto-discover new partitions without someone running a manual command every time new data lands.

Granularity is where a lot of otherwise sound designs go sideways. High-volume event streams, clickstream data, IoT sensor feeds, can justify hourly or even sub-hourly partitions, but only if each partition ends up holding hundreds of megabytes. Low-volume transactional data almost never earns daily partitions; monthly or quarterly keeps files big enough to be worth opening. A decent rule of thumb: if a daily partition holds 5 MB, partition by month instead. There's no glory in a folder structure that's technically correct and practically useless.

Raw and curated zones should partition by different dates, and conflating the two is one of the more common mistakes in the wild. Raw partitions by source process date, when the data physically arrived, because that's what preserves the audit trail and lets you reprocess a specific ingestion window if something upstream broke. Curated and consumption zones partition by business date, when the event actually happened, because that's how analysts think and query. These two dates diverge more often than people expect: a batch job that runs at 2 AM processing yesterday's transactions, a late-arriving record from a mobile app with spotty connectivity. Treating them as interchangeable causes headaches downstream that are annoying to untangle later.

Multi-dimensional partitioning (adding region on top of date, say) is valid, but it comes with a cost. Every extra dimension multiplies the number of partitions in the catalog. It's only worth it if queries reliably filter on both dimensions together. Adding a dimension nobody actually filters on creates more metadata overhead with zero pruning benefit, dead weight the catalog has to carry around forever.

The over-partitioning trap and the small-file problem it creates

Over-partitioning looks responsible on paper and turns into a mess in practice. Stacking year, month, day, hour, and minute into a partition key produces millions of directories, each holding a single tiny file. By most practitioner accounts, that's worse than not partitioning at all, which is a strange thing to say out loud but holds up under scrutiny.

Small files compound the problem in a very literal way. Every file needs a separate API call to list, a separate call to open, and its own catalog entry. A query engine forced to open millions of sub-megabyte files spends nearly all its time on metadata operations, listing, opening, closing, and almost none of it actually reading data. Add in the fact that Glue catalog costs scale with object count, and over-partitioning turns into a bill, not just a slowdown.

The fix is compaction, and it's not glamorous but it works. Merge small files into 128 MB to 1 GB Parquet files using Spark's coalesce() or repartition(), and run it as a scheduled job, nightly, against the curated zone. Set it, forget it, let it run while everyone's asleep.

Streaming pipelines are the usual culprit behind small-file sprawl. A Kafka or Kinesis consumer writes records as they arrive, and each micro-batch produces its own small file, sometimes dozens per minute. The workaround: buffer in Avro in the raw zone, where fast writes matter more because file size isn't the priority there, and compact to Parquet in curated on a schedule. Let raw be messy and fast. Let curated be clean and queryable.

Diagnosis is simple enough once you know what to look for. If a partition holds only a few megabytes, the partition key is too fine-grained. Coarsen it before the catalog balloons into something nobody wants to touch.

File format selection across zones: Parquet, Avro, and JSON each earn their place

Format isn't a matter of taste. Each one is suited to a specific spot in the pipeline, and picking the wrong one in the wrong zone causes a performance problem or a cost problem later.

Parquet belongs in curated and consumption. It's columnar, which typically delivers 5 to 10x better compression than plain CSV, and it supports predicate pushdown: the query engine can skip entire row groups that don't match the filter instead of reading past them. The payoff is concrete: Athena queries against Parquet run 3 to 5x cheaper than the same query against CSV, because Athena bills by data scanned, and Parquet just scans less. Pair it with Snappy compression for the best balance of speed and size.

Avro fits streaming ingestion pipelines, and lands naturally in the raw zone. It's row-based, carries its schema embedded in every file, and handles schema evolution well, which matters when an upstream API adds a field without warning. Writes are fast, which matters when records are arriving one at a time from Kafka or Kinesis. But Avro is a stopover, not a destination. Convert it to Parquet once it is in curated.

JSON has exactly one legitimate home: landing data in the raw zone from webhooks and APIs that only speak JSON. It's human-readable and flexible on schema, which is handy at the point of ingestion. But it offers no columnar pruning, compresses poorly, and parses slowly at any real scale. Leaving JSON in curated or consumption zones is a decision someone will eventually regret at query time.

CSV deserves a similar warning: fine for small reference files, a liability for anything bigger. No embedded schema, no default compression, and quoting or delimiter issues that appear at the worst possible moment. If a source only delivers CSV, land it in raw and convert it immediately, don't let it linger.

The economic case for Parquet is really the whole argument in miniature. A 3 to 5x reduction in Athena query cost isn't a rounding error at data lake scale: it separates analytics that gets funded next year from analytics that gets flagged as a line item to cut.

How S3's request-rate mechanics interact with prefix design

S3 supports at least 3,500 PUT, COPY, POST, or DELETE requests per second, and 5,500 GET or HEAD requests per second, for each partitioned prefix, and there's no cap on how many prefixes a bucket can have. Spread traffic across ten prefixes and the ceiling climbs to roughly 55,000 read requests per second in aggregate. Prefix design, in other words, determines throughput, not just tidiness. It's a throughput decision.

Those per-prefix limits aren't available instantly. The per-prefix limits are not necessarily available from the first request; S3 scales prefix throughput in response to load patterns over time. In practice, sustained traffic against a prefix may be needed before the full per-prefix ceiling is reliably reached. For a genuinely hot prefix, something feeding a machine learning training job with heavy, sustained read traffic, plan prefix distribution carefully in advance, rather than waiting to hit the wall first.

There's also a per-connection ceiling to keep in mind: each individual TCP connection to S3 tops out around 80 MiB per second, regardless of how much bandwidth the EC2 instance on the other end can actually push. A single GET or PUT can't punch through that number no matter how beefy the instance is. The workaround is the same one used everywhere in distributed systems: parallelize. Spread reads and writes across multiple connections and multiple prefixes at once, rather than betting everything on one connection to do the heavy lifting.

Put together, that's the whole design problem in one line. Zones set the lifecycle, partition keys set the query pattern, file size sets the metadata overhead, and prefix design sets the throughput ceiling. Get the folder structure right at the start, and the rest of the lake takes care of itself. Get it wrong, and no amount of compute thrown at Athena will save you from a swamp of your own making.