Writing Parquet Files to S3 for Downstream Warehouse Ingestion

Getting Parquet files onto S3 in a way that a warehouse can actually load fast and cheap comes down to a handful of decisions made well before anyone runs a COPY INTO or LOAD DATA statement. Format, compression, partition layout, and file size all get baked in at write time, and a mistake at any one of those layers appears later as a slow, expensive query rather than a write-time error. The pipeline runs, the load succeeds, and the cost only becomes visible weeks later, which is what makes Parquet pipelines deceptively hard to get right.

Parquet earns its place as the default for warehouse-bound data because it stores by column instead of by row. A query that touches three columns out of forty only has to read those three, and the file format supports predicate pushdown and row-group skipping on top of that, so an engine can throw out whole chunks of a file before it even decompresses them. For engines that price by bytes scanned, like Athena and BigQuery, that isn't a performance nicety, it's a line item on the bill. And because Spark, Trino, Presto, Hive, ClickHouse, DuckDB, Polars, BigQuery, Snowflake, and Athena all read Parquet natively, there's no conversion step standing between the file sitting in S3 and a query running against it. Schema evolution is handled at the file-format level too, so old files with fewer columns and new files with more can sit in the same folder without breaking a read.

Compression codec and file-size decisions that affect every downstream load

The codec choice sounds like a minor setting and behaves like one, right up until file counts grow and query latency starts creeping. Snappy remains the most common default: it decompresses fast and compresses well enough, and for read-heavy workloads where every millisecond of query latency matters, it is a safe, boring choice. Zstd has been closing in on it and, for most new pipelines, is now the better pick, since it compresses harder than Snappy while matching or beating it on decompression speed. Gzip still shows up in older pipelines, but it's been outpaced by Zstd on compression ratio and it costs more in read latency, so it only makes sense where storage cost matters more than query speed, which is rarely the dominant concern for an active warehouse table.

File size matters just as much as codec, and the rule of thumb that keeps coming up is roughly 256 MB per file. Going much smaller causes metadata overhead to dominate. An engine has to open a file, read its footer, parse its schema, before it can do anything with the data inside, and that overhead is roughly fixed per file regardless of how much data the file holds. Accumulate enough small files, from a streaming job writing every few minutes, say, and query latency on an aggregated metrics table can climb something like 3x over a few months because the engine is now spending most of its time listing and opening thousands of tiny objects rather than scanning bytes. That's the file-count degradation risk: too many small files is the single most common way a columnar-storage-on-object-storage pipeline degrades silently. A pipeline that looks clean on day one can turn into a slow, expensive mess by month three if nothing is compacting those files back down.

Writing Parquet to S3: mechanics across four common tooling paths

There's no single correct way to get Parquet onto S3. The right tool depends on the size of the job and what's already running in the stack, and four paths cover most real deployments.

PySpark and AWS Glue are the workhorse combination for large batch jobs. The plain default write, something like .write.format("parquet").mode("overwrite").save("s3://your-bucket/path/"), works and supports multiple formats, but left on its own it tends to scatter a job's output across a large number of small files, which makes it a better fit for quick tests than for production tables. Adding partitionBy("year", "month") turns that same write into something Athena and Redshift can prune against, skipping whole partitions instead of scanning everything. Push partitioning too far, though, say down to a high-cardinality key, and both write time and S3 listing overhead start to climb, because Spark now has to manage far more directories and small output files per job. For large Spark jobs writing to S3 through EMRFS, the EMRFS S3-Optimized Committer, available starting with Amazon EMR 5.19.0, is worth turning on since it improves write throughput and consistency over the default committer.

DuckDB offers a much lighter path for teams that don't want to spin up a cluster for a modest export. It needs the httpfs extension, which can be loaded with LOAD httpfs. Once credentials are configured, writing is a single line: COPY table_name TO 's3://bucket/filename.parquet'. It's not built for petabyte-scale jobs, but for smaller exports or a step inside a larger pipeline, it does the job without the overhead of a Spark cluster.

Redpanda Connect represents a different pattern entirely: streaming data straight into Parquet rather than batching it after the fact. The parquet_encode processor takes an explicit schema, field names and types declared up front, and can be configured with a compression setting such as zstd along with batching settings that trade off latency against throughput. Because Parquet's BYTE_ARRAY type has to be told explicitly that a given field is a UTF8 string rather than raw bytes, schema declaration is mandatory. What this pattern makes possible is a single Redpanda topic serving two audiences at once, JSON going out to a web application and Parquet landing in S3 for analytics, without duplicating the underlying stream.

That last point about explicit schema isn't specific to Redpanda Connect. It holds across all four paths. Every one of them requires the name and data type of each field to be declared at write time. Skip that step and let a tool infer types instead: the mismatches surface later at warehouse load time, which is a far more expensive place to debug a typing error than the write step where it originated.

Partitioning strategy and S3 folder structure for warehouse query efficiency

Hive-style partitioning is the convention nearly every query engine expects: s3://data-lake/sales/year=2026/month=01/part-00000.parquet. That folder structure isn't cosmetic. It enables two separate layers of data skipping. Partition pruning lets the query engine ignore entire directories that don't match a filter before it reads a single byte, and row-group skipping then works inside whatever files remain, using each row group's stored min/max statistics to skip the ones that can't possibly match the predicate.

Picking the partition key is where judgment matters most. Time-based partitioning, by year, month, or day, fits naturally with logs and event data because it lines up with the filter most queries already use. Partitioning on something high-cardinality, a user ID or a transaction ID, looks appealing on paper but creates far more directories and small output files per job than is practical; this raises overhead at both write time and query planning time. The tradeoff there is real and doesn't go away with more compute.

Folder hygiene matters just as much as the partition scheme itself, particularly for warehouse connectors that infer table structure from S3 layout. Each table needs its own folder, since many S3 Parquet connectors use the folder structure to identify tables. Folder names, column names, and filenames all need to avoid spaces and special characters, because those get parsed downstream and inconsistent naming breaks that parsing. A folder also has to contain only Parquet files: mixing in a stray CSV export breaks ingestion for the whole folder, not just the one file. Within those constraints, file naming inside a folder is flexible, since files under a table's folder are generally read together as a single logical table.

Loading S3 Parquet into specific warehouses (patterns for Snowflake, Redshift, Athena, and BigQuery)

Each warehouse has its own idiom for pulling Parquet in from S3, and the differences matter for how a pipeline gets designed.

Snowflake treats S3 as the raw, pre-transformation layer through external stages: data lands there first, gets staged, and only then gets transformed inside Snowflake itself. That's the ELT pattern, load first, transform second, and it keeps the warehouse doing the transformation work rather than pushing that logic upstream. Snowflake Tasks can run those staged loads on a schedule automatically, which turns what would be a manual load step into a full ELT pipeline that runs on its own.

Redshift offers two distinct paths depending on how often the data actually gets queried. Redshift Spectrum queries Parquet files directly on S3 without loading them into the cluster at all, which fits historical data that gets touched occasionally but doesn't justify a permanent copy inside Redshift. For semi-structured data that doesn't map cleanly onto flat columns, Redshift's SUPER type and PartiQL give it a way to handle partially nested Parquet schemas without flattening everything first.

Athena runs serverless queries directly against Parquet sitting in S3, and its partition pruning and predicate pushdown are what keep bytes scanned, and therefore cost, down. Two ingestion patterns feed Athena from opposite directions. One is the AWS-native CDC pipeline: Aurora, RDS, or an on-prem database feeds AWS DMS, which does a full load plus ongoing change-data-capture into raw S3, Glue transforms it, and Athena or Redshift query the result. That pattern generally beats a nightly export, since it cuts down the staleness window and reduces load on the source database, rather than hitting it with one heavy batch job every night. The other pattern handles clickstream data: Kinesis Data Streams feeds Lambda, Lambda writes raw records to S3, Glue transforms them into curated Parquet, and Athena queries the curated layer.

BigQuery reads Parquet natively as well, and because its pricing is also bytes-scanned, the same logic applies: columnar storage and column pruning aren't just performance features there, but a direct line to a lower bill.

Limits of plain Parquet files on S3: the case for open table formats

Parquet is a file format. Delta Lake and Apache Iceberg are table formats, and they sit on top of Parquet rather than replacing it, using it as the actual storage layer underneath. That distinction matters because the two concepts are additive: a table format adds a management layer over a folder of Parquet files, it doesn't compete with Parquet for the same job.

Format, compression, partition layout, and file size all get baked in at write time, and a mistake at any one of those layers appears later as a slow, expensive query rather than a write-time error. ACID transactions mean concurrent writers can't corrupt a table the way two Spark jobs writing to the same raw Parquet folder can. Time travel lets a query reach back to how the table looked at a prior point, which raw Parquet has no native way to do. Schema evolution gets tracked at the table level instead of being a per-file accident, and row-level deletes become possible, which matters directly for GDPR and CCPA compliance, since plain append-only Parquet has no clean way to delete a single customer's row without rewriting the whole file.

Apache Iceberg has become the default choice for teams building a new, open lakehouse as of 2026. Every major cloud platform, AWS, Snowflake, Google, and Databricks, now reads and writes it, and AWS S3 Tables made Iceberg the native table format inside AWS itself. Iceberg v3 closes off some of the last real gaps against Delta Lake by adding deletion vectors, row lineage, and a VARIANT type, which cuts down on the workarounds teams previously needed for incremental processing and for semi-structured fields. Adoption backs this up: the share of enterprises evaluating Iceberg has risen substantially since 2024. The format's metadata layer also avoids S3 LIST calls at query time, and on a table with more than 100,000 files, that alone can strip 30 to 60 seconds off query planning before a single row gets scanned. Iceberg's hidden partitioning and partition evolution features let a table's partition scheme change over time without a full rewrite of existing data, which is the kind of operational flexibility raw Parquet simply doesn't offer.

Delta Lake isn't losing ground here either, particularly inside the Databricks and Microsoft Fabric ecosystems, where it remains the strongest fit. Delta's UniForm feature lets Iceberg and Hudi clients read Delta tables natively, and its Rust kernel is built to be embedded directly inside other engines rather than requiring a full Delta runtime. Iceberg v3's feature additions push the two formats' underlying data layers closer together as well, which is good news for any pipeline that has to interoperate across both.

Embedding S3-to-warehouse data delivery as a product feature rather than a one-off integration

For a SaaS company, everything covered above stops being internal infrastructure the moment a customer expects to receive their own data in their own warehouse. At that point the pipeline is a feature customers are paying for, and every decision made earlier, codec, file size, partition scheme, schema handling, table format, has to work not just once but repeatably, across however many customer warehouses are on the other end.

Multi-tenant delivery raises the bar on each of those decisions. Credentials need to be isolated per tenant rather than shared across all customers. Schema has to travel with the data itself instead of being assumed on the receiving end, because a receiving warehouse has no way to know what changed upstream unless the pipeline tells it explicitly. Partitioning has to become destination-aware, since Snowflake, Redshift, and BigQuery don't all perform the same way against the same partition structure, and a scheme tuned for one can be a poor fit for another. And idempotency has to hold at scale: replaying a failed batch for one tenant can't be allowed to touch another tenant's data, which grows more complex as the number of tenants scales than across one internal pipeline.

Pricing shapes the incentive on top of all this. A model that charges by data transferred punishes a customer for the exact thing the feature is supposed to deliver, more of their data, moved reliably. Pricing based on the destination instead lines the cost up with the value delivered rather than with bytes moved, which matters more the larger and more data-heavy a customer becomes. This is the argument for treating embedded data connectivity as its own category of infrastructure. Platforms offering an embeddable SDK or API, SOC 2 Type 2 certification, encryption end to end, and a private-cloud deployment option for customers with strict data-residency requirements are built to carry this operational weight so a product team can ship the feature itself, rather than spend its time maintaining a one-off integration for every new customer warehouse that shows up.