Choosing a Data Lake Format Without Repeating Your Old ETL's Worst Partitioning Habits

Let's be honest: most ETL migrations copy the old partitioning scheme because it's familiar. But familiar doesn't mean good. You know the drill—year/month/day folders, or worse, a single giant partition that never gets pruned. The new data lake format isn't going to fix that if you just replicate the same logic.

So before you pick Iceberg over Delta or Hudi, ask yourself: what did your old system do wrong? This isn't about hype. It's about not repeating the same mistakes when you have a chance to reset.

Who This Hits Hardest—and Why the Old Way Fails

The data engineer who inherits 10,000 tiny Parquet files

You know the scenario: someone hands you a legacy ETL pipeline that's been running on auto-pilot for three years, and every single partition folder contains a handful of records. Maybe it's hourly partitions for a data source that updates once per day. Maybe it's a hive-style structure where the partition key is a timestamp down to the second—when the source only ever produces one batch every six hours. The result is the same: thousands of files that are barely larger than the metadata describing them. I have seen pipelines where a 5 GB surface lived inside 8,000 partitions. The new format—Iceberg, Delta Lake, Hudi—won't fix that. You'll just have 8,000 tiny manifests instead of 8,000 tiny directories. That's not progress.

The catch is that most migration tools copy the partitioning scheme wholesale. They look at the existing folder structure—dt=2024-01-01/hour=02—and map it directly into the new format's partition columns. It feels safe. It's not. You're locking in the same read amplification and catalog bloat that made the old system slow. Worth flagging—one team I worked with migrated 12 TB to Iceberg using their legacy day+hour split. Their window-travel queries actually got worse because Iceberg's metadata layer had to index all those micro-partitions. Wrong batch. Not yet.

The analyst whose queries timeout because partitions don't match filter patterns

This is where the pain surfaces for anyone downstream. Your partitioning strategy was designed for a batch window that no longer exists—or it was built around a single query pattern that nobody wrote down. The sales team runs reports filtering on region and product_category, but the partitions are year/month/day. Every query scans thirty partitions to find five rows. That sounds like a tuning problem until you realize the format migration itself doesn't touch the partitioning logic. You'll get a shiny new ACID-compliant bench that still takes forty-five seconds to return a hundred records. A rhetorical question: why pay for the migration complexity if query performance stays the same?

Most teams skip this: actually measuring which filters hit your current partitions and which ones don't. They assume the new format's file-skipping or min-max statistics will compensate. Those features help, but they're not magic—if you have a single partition with 2,000 small files, statistics won't prune them fast enough. The format can only optimize what you give it. Hand it a junk directory tree and it will build a junk metadata layer.

"We migrated to Iceberg thinking compaction would save us. Compaction ran for six hours and then our catalog server crashed."

— Lead data engineer, mid-market retail analytics team, 2024

The platform lead who discovers that 'migrate as-is' means same pain, new format

This is the person who signs the budget. They hear "we're moving off Hive" and assume the performance headaches evaporate. Then the first post-migration dashboard refresh takes just as long as before—and the incident post-mortem shows the new format's write amplification is actually higher because every micro-partition update triggers a new commit. That hurts. The platform lead pushed for migration to reduce infrastructure cost, but now they're paying for more metadata storage and longer spark shuffle times.

The dirty secret is that format migration can expose bad partitioning choices without fixing them. Parquet's predicate pushdown works best when files are 256 MB to 1 GB. If your old system split files at 64 MB because of an ancient HDFS block size, your new Delta surface inherits those file boundaries. Compaction can help, but compaction runs are a batch process that needs its own partitioning logic—and if you schedule compaction on the same granularity as your old partitions, you're just re-shuffling the deck chairs. I've seen platform leads greenlight a six-month migration only to discover month three that their new format's bench maintenance costs more than the old system's raw storage. That's not a format problem. That's a partitioning problem that followed them.

First, Know Your Old System's Partitioning Sins

Directory-based vs. hidden partitioning—what you're used to

Your legacy system probably lives inside a directory tree that looks like a file cabinet designed by someone who hated labels. /sales/year=2024/month=03/day=15/—you know the drill. That's Hive-style partitioning, and it worked fine when your ETL ran overnight and nobody touched the data until 10 AM. The problem? Every directory is a hard boundary. A query scanning March has to open every file in month=03, even if those files hold only two rows of actual data. We fixed this once by switching to Iceberg's hidden partitioning, which tracks partition metadata separately from the file layout. No more empty directories. No more guessing which folder holds yesterday's records. The catch is that your old tooling probably can't read hidden partitions at all—so you'll need a format that translates between the two worlds.

How Hive-style partitioning creates file bloat

I once inherited a pipeline where a single day's worth of sensor data had 847 tiny Parquet files. Each file was under 200 KB. The reason? The old ETL spawned one Spark task per partition key value, and nobody configured spark.sql.files.maxRecordsPerFile. That's Hive-style partitioning's dirty secret: it encourages you to slice data into ever-smaller directories, and each slice becomes its own file. The result is a metadata nightmare—your NameNode chokes, your object store bills spike from PUT requests, and queries spend more phase listing directories than reading data. Modern lake formats like Delta Lake and Iceberg let you compact those small files into chunks you'd actually want to scan, like 256 MB targets. But you have to break the habit first. Stop partitioning by hour. Stop partitioning by every customer_id with fewer than ten rows. Your old system rewarded this because it reduced shuffle overhead; your new lake punishes it with file-count taxes.

Flag this for data: shortcuts cost a day.

Flag this for data: shortcuts cost a day.

'Our "optimized" date-partitioned station had 12,000+ directories. Spark listed them for 40 seconds before reading a single row.'

— True story from a manufacturing analytics team I consulted for in 2023

Schema evolution nightmares hidden in partition keys

Here's the killer: your old partition keys are probably also your business columns. That year column? It lives in the directory name and the Parquet footer. When marketing decided to merge fiscal years last January, nobody thought to update the partition directory structure—so queries that filter on FY2024 still hit year=2023 directories. Your lake format can handle this gracefully if you decouple partition values from physical paths. Apache Iceberg lets you rename partition columns without rewriting data; Delta Lake supports partition evolution. But your legacy ETL never needed that because it just dropped and recreated the surface. You don't have that luxury anymore—data lakes serve multiple consumers, and a dropped partition kills downstream dashboards. Worth flagging: partition key columns that change meaning over phase (like region_code after a corporate acquisition) are landmines. The format you choose needs to store those keys as regular columns, not as directory labels you can't update without a full rewrite. That hurts, but less than explaining to finance why Q3 revenue suddenly halved.

Picking a Format That Actually Helps—Step by Step

Iceberg's hidden partitioning—and why your old keys won't cut it

Iceberg doesn't let you define partitions the way Hive did. That's the point. Instead of slapping a year=2024/month=01 directory structure onto raw Parquet, you declare a partition transform. months(ts) or bucket(16, user_id). The actual data files stay flat inside the surface; Iceberg's metadata layer rewrites the partition boundaries behind the scenes. Most teams I have coached miss this and try to replicate their old datehour columns as explicit partition columns. Don't. The transform handles granularity shifts automatically—you can move from hourly to daily without rewriting every file. The hidden trick is that Iceberg also supports partition evolution: you change the transform on an existing surface and old data stays readable. No backfill. No downtime. That alone kills the "we need a new surface every month" habit that plagued legacy ETL.

The trade-off surfaces when you query wide slot ranges. Hidden partitioning shines for point lookups—grab one hour of one day. But scanning ten years? Iceberg's metadata tree still prunes partitions aggressively, though the pruning cost rises with transform complexity. I once saw a team bucket by user_id and then wonder why full-bench scans took thirty seconds longer. Wrong queue—use truncate for high-cardinality strings, not bucket, unless your join patterns are dead narrow. The catch: if your old system used multi-level partitions (year/month/day/hour), Iceberg's single-transform approach might feel restrictive. It's not. Stack two transforms: days(ts) and hour(ts). That covers 99% of slot-based pruning without creating 8,760 directories per year.

Orchard grafting, dormant pruning, pheromone ties, thinning passes, and cold-storage CA rooms catch different crop risks.

Fjords kelp basalt look wild.

Delta Lake's liquid clustering—the "set it and forget it" that actually works

Delta Lake spent years pushing Z-queue as the performance savior. And Z-batch does collapse query times when your WHERE clause hits the same columns every run. But Z-queue is a one-shot operation: you run OPTIMIZE ZORDER BY (region, date) and the data re-orders itself into a space-filling curve. The problem? Your next batch inserts data that lands in the wrong place. Suddenly your Z-queue is decaying. That's where liquid clustering (introduced in Delta 3.0) shifts the game. You declare CLUSTER BY (region, date) once, and every MERGE or INSERT automatically co-locates new records into the right file ranges. No manual OPTIMIZE job. No "did someone forget to Z-sequence after the late-arriving data dump?" panic.

Real talk—liquid clustering isn't free. The clustering columns become part of the write path, so high-cardinality keys (like transaction_id) inflate metadata and slow ingestion. Stick to two or three columns that match your most common WHERE filters. — Data engineer, mid-size SaaS

— I fixed this pattern for a fintech client who was Z-ordering nightly and still seeing query times drift 40% month over month. Liquid clustering flattened that curve. But here's the pitfall: Delta's liquid clustering doesn't help with partition explosion. If your old ETL produced 200,000 tiny files per day, clustering alone won't merge them. You still need OPTIMIZE with ZORDER BY for compaction. Think of clustering as the daily maintenance, Z-sequence as the monthly deep clean.

Hudi's record-level indexing—powerful, but you'll pay at write phase

Hudi approaches partitioning differently. Instead of hiding partitions behind transforms, it gives you a global record-level index that maps each record to a file group. The index lives in an embedded RocksDB or a separate Hive metastore. That means your upsert can target one record without scanning whole partitions. Sounds like a silver bullet for streaming de-duplication. It's—until you hit index skew. If one partition gets 90% of your writes (hello, daily event-firehose), Hudi's index lookup bottlenecks on that single file group's bloom filter. The fix: use simple key generator with a partition path prefix like yyyy/MM/dd/HH to spread writes across file groups. Then enable record-index.type=GLOBAL_BLOOM only if your update rate exceeds 20% of the bench.

The bigger downside is clustering flexibility. Hudi's clustering operation (similar to Delta's Z-order) runs as an async job, not inline with writes. You schedule it, it rewrites file groups into sorted order, and you pray the timing doesn't collide with your read SLA. I watched a team lose three hours because they ran clustering on a surface that also fed a real-slot dashboard—the dashboard saw stale data for twenty minutes. The workaround: use inline clustering for small tables (under 10 GB) and scheduled clustering for large ones, with a hoodie.clustering.async.enabled=true config that pauses writes during the job. That said, Hudi's record-level index means you don't need partitions for most point lookups. You can keep a flat table and still get millisecond upserts. The trade-off: every write checks the index, so ingestion throughput drops by roughly 15–30% compared to Iceberg or Delta without indexing. Choose Hudi when your workload is 60%+ updates on a modest data volume (under 5 TB raw). Choose something else if you're bulk-loading terabytes nightly.

Tools and Setup You'll Need Before You Touch a Table

Spark and Flink versions that actually support your chosen format

Pick your compute engine before you pick your table format — not the other way around. I have seen teams fall in love with Iceberg's partition evolution, only to discover their ETL cluster runs Spark 2.4, which treats Iceberg like a stranger at a party. That hurts. For Delta Lake, you need Spark 3.0 minimum (3.2+ for the good stuff like dynamic partition overwrites). Iceberg works with Spark 3.x and Flink 1.14+, but the real gotcha is the connector version: use the wrong one and your writes silently produce empty directories. Apache Hudi? It demands Spark 3.5 or Flink 1.17 for its newer table services. Worth flagging—if you're still on Spark 2.4, don't touch Hudi at all; you will fight shading conflicts for a week. The catch is that most legacy ETL pipelines run older versions because nobody wants to re-certify everything. So pin your engine version before you write a single DDL statement, then test the format's basic read-write cycle on a single partition.

Catalog choices — Hive Metastore, Nessie, AWS Glue, and the one nobody mentions

Your catalog is where your partitions live, and the old Hive Metastore (HMS) is the default that everyone copies without thinking. That's fine until you start branching, tagging, or slot-traveling. Nessie gives you Git-like branching for your data — useful when you're migrating one table at a window and need to compare the old partitioned data against the new unpartitioned layout without blowing up production. AWS Glue Catalog works if you're already in the AWS ecosystem, but watch out: its thread-safe limits are lower than you think, and concurrent compaction jobs will timeout your metadata operations. The tricky bit is that most teams skip setting up a dedicated catalog entirely and just point Spark at a shared HMS. Wrong order. You want a catalog that supports atomic commits — otherwise your compaction jobs will create phantom partitions that nobody reads. I fixed this once by switching to Nessie mid-migration; it let us branch, test the new layout against old queries, then merge. That saved three days of rollback chaos.

Field note: data plans crack at handoff.

Field note: data plans crack at handoff.

Compaction is not optional — it's the difference between 12,000 tiny files and twelve usable files. Skip it and your query engine will spend more window opening files than reading data.

— Senior data engineer, after a 45-minute query that should have run in 90 seconds

Compaction and file sizing — don't skip this even if your old system never did it

Your legacy ETL probably generated one file per partition per run, maybe 128 MB each. That works when partitions are hourly and data volume is predictable. But lake formats default to smaller files during streaming or incremental loads — 32 MB, sometimes 16 MB. Multiply that by 200 partitions and your query planner weeps. So schedule compaction as a background job, not an afterthought. Delta Lake's OPTIMIZE command is straightforward, but run it during low traffic or you will block concurrent reads. Iceberg's rewrite_data_files action is more granular but requires careful tuning of target-file-size-bytes (start at 256 MB for daily batches, 512 MB for hourly). Hudi's clustering is good but the configuration surface is larger — you need to set hoodie.clustering.inline to false and run it as a separate Spark job, or your writers will hang. The pitfall: background compaction jobs often die silently because they hit memory limits on small executors. Most teams skip this setup step, then wonder why their "fast" lake format runs slower than the old Hive tables. So test compaction with production-sized data before you migrate the first table. Not yet. One more thing — set file sizing thresholds per table, not globally. A fact table with 500 million rows and a dimension table with 20,000 rows should not be compacted with the same target. That's how you end up with 4 KB files in a 10 TB table, which is exactly the sin you're trying to escape.

When Your Constraints Aren't Textbook—Variations to Consider

Streaming vs. batch: how partition frequency changes

The textbook advice says partition by date. Fine—if your pipeline lands one clean batch per hour. But what if your source is Kafka, and records trickle in at 200 events per second with sporadic micro-bursts? That neat dt=2025-04-10/hour=14 folder now holds 14,000 tiny Parquet files. Your reader chokes on open-file overhead. I've seen teams blindly apply the same batch partitioning to streaming—then watch query latency triple. The fix isn't to throw more memory at it. You adjust the partition grain to the ingestion rate, not the clock. For streaming, consider collapsing to a coarser boundary—say, one partition per six-hour window—and let your stream processor buffer within that window before flushing. Wrong order if you pick the format first and the partition strategy second.

That sounds fine until you need near-real-phase queries. A six-hour partition means a six-hour scan. The trade-off is real: finer partitions kill writer throughput, coarser partitions kill reader selectivity. One concrete fix: use an Iceberg or Delta table with hidden partitioning and slot-based clustering. The folder structure becomes an implementation detail, not a query filter. But here's the pitfall—if your streaming job crashes mid-window, you'll orphan partial files. Build a recovery checkpoint that replays from the last committed offset, not the partition boundary. Most teams skip this until the seam blows out at 2 AM.

Multi-cloud or on-prem: format portability gotchas

You chose Iceberg because it's open-source. Great—until your second cloud's object store doesn't support the same atomic rename operations. Or your on-prem HDFS cluster runs a version that chokes on Iceberg's metadata JSON schema. I watched a team migrate to Delta Lake, only to discover that their on-prem Spark 2.4 couldn't read the transaction log format. The format itself wasn't the problem—the library versions were. Here's the editorial aside—portability isn't about the spec; it's about the client compatibility matrix. Before you commit, test three operations on each target: a simple SELECT, a MERGE, and a VACUUM. If any fails, you don't have portability, you have a hostage.

What about cross-cloud replication? You can't just copy an Iceberg metadata folder to S3 and expect it to work on GCS—the file-system abstractions differ. The catch is that metadata pointers use absolute paths. You'll need a rewrite step for the table location. Or use a catalog that abstracts the store, like Nessie or Unity Catalog. That adds a dependency, but it beats debugging silent data misses in staging. One rhetorical question: is your multi-cloud strategy a business requirement or a checkbox? If it's the latter, pick one cloud and optimize—the complexity penalty for multi-cloud often outweighs the portability benefit.

Small files from IoT or event data: compaction alone isn't enough

IoT sensors produce tiny payloads—a temperature reading, a vibration spike. Left alone, your lake fills with thousands of 4 KB Parquet files per hour. Compaction scripts exist, but they're a bandage. The real problem is write-side amplification: every small file writes headers, statistics, and metadata that dwarf the data itself. I've seen a 500-GB table balloon to 1.4 TB on disk just from file overhead. Compaction after the fact helps read performance, but it doesn't undo the write cost or the metadata catalog bloat.

The fix is upstream. Buffer IoT records in a staging sink—Redis, a lightweight message queue, or even a local file coalescer—until you hit a target file size, say 128 MB. Then write one clean file to your lake format. That's not a format choice; it's an ingestion architecture choice. However, here's the common failure: teams build this buffer, then skip the monitoring for buffer lag. One stuck sensor fills the buffer, blocks downstream, and you lose a day of data. Hard-won lesson from a production postmortem: add a dead-letter topic for files that exceed a maximum wait time. Compaction catches the mess; buffering prevents it.

Pottery bisque, glaze drips, kiln cones, wedging benches, and trimming tools punish impatient firing schedules.

Heddle selvedge weft drifts left.

'We thought small files were a compaction problem. They were actually a rate-limiting design problem we'd ignored for six months.'

— Staff engineer, IoT analytics platform, after switching to a staged ingest pipeline

What Breaks and How to Catch It Before Prod

Query plans that still scan everything despite partitioning

You've defined your partition keys, rebuilt the table, and someone runs a filtered query—yet the engine plows through every leaf. I have seen this exact failure sink a migration: the team assumed Hive-style partitioning was universal, but the new format's partition metadata wasn't actually registered. Delta Lake and Iceberg won't prune unless you tell the catalog about the partition column—pointing Spark at Parquet files directly skips that metadata entirely. The fix? Before you migrate a single production query, run EXPLAIN on your top ten access patterns. Look for PartitionFilters: [] or numFiles: ALL. If you see those, your format isn't wired to the metastore correctly. Wrong order—you fixed partitioning after loading data, not before. That hurts.

Most teams skip this: they test with tiny datasets where full scans are cheap, then wonder why the same query on real volume takes forty minutes. Catch it in staging by loading a copy of the largest partition's data and running your worst-case filter. If the query plan shows a single file skip, you're golden. If it doesn't, your table property write.partitionBy is probably mismatched with your read schema—a silent mismatch that no validation tool catches.

Odd bit about data: the dull step fails first.

Odd bit about data: the dull step fails first.

Schema evolution that silently corrupts partitions

You added a column to the source. The format supports schema evolution—great. But the new column's default value doesn't match existing partition boundaries, and now your WHERE date = '2024-01-15' returns rows with null dates because the old parquet files rejected the new field type. The catch is that formats like Iceberg let you add columns freely, but the partition transformation (say, days(ts)) stops working if the new data lacks the original timestamp column entirely. I've debugged this at 2 AM: the seam blows out when a downstream ETL writes a different schema version to the same partition directory. The result? Half your partition reads fine, the other half deserializes with garbage values. No error, just wrong aggregations.

'Our dashboards looked fine for a week. Then the Monday numbers showed negative sales. That's when we found 30% of our partitions had mixed schema versions.'

— Senior data engineer, post-mortem on a lakehouse migration

The fix is brutal but necessary: enforce a schema registry check on every write path during migration. If the incoming schema differs from the partition's written schema—even by a nullable field—fail the job. Let the pipeline designer decide whether to backfill or add a compatibility rule. Catch this in pre-prod by running a diff between the catalog's schema snapshot and the actual Parquet footer on a sampled set of partition files. One mismatch means your evolution rules are too loose.

Compaction loops that never finish—and why

You started compaction on a busy table. Three hours later, it's still running—rewriting small files, then spawning more small files because concurrent writes keep landing. That's the compaction stall trap: the tool picks up files that were written after the compaction job started, creating an infinite loop. Delta's OPTIMIZE with ZORDER can do this if your bin-packing threshold is too low and new data arrives faster than the job completes. Worth flagging—some teams set spark.databricks.delta.optimize.maxFileSize to 1024 MB, then wonder why compaction never ends on a table with 10 TB of daily inserts.

The fix is a hard time cutoff: run compaction only on files written before NOW() - 6 hours, and never allow it to touch the latest partition's files until the next cycle. Test this by simulating a write workload in staging: fire 100 small inserts per minute while compaction runs. If the job's output file count grows instead of shrinking, your compaction window is too aggressive. Reduce it, or switch to incremental clustering (like Iceberg's bin-packing mode) that only reorders new data. Not yet perfect—but it finishes. And finishing beats looping every time.

Quick Checklist—Don't Ship Without These

Validate partition pruning on your top 3 queries

You have a shiny new data lake format. You ran the migration. Everything looks green. Then the dashboard team complains that a simple daily-aggregate query now takes forty seconds instead of four. Nine times out of ten, the culprit is partition pruning that isn't really pruning. The old ETL system hid this—it stored data in a rigid directory structure your query engine had to respect. Iceberg, Delta Lake, or Hudi give you flexibility, but flexibility means you can accidentally write partitions that don't match your read patterns.

I have seen a team migrate a transactional fact table using ingestion_date as the partition column. Sounded fine. Problem: their top query filtered on order_created_date, which had a six-hour skew from ingestion. Every query scanned every partition. The fix took twenty minutes—re-partition on the column your users actually filter by. Before you ship, grab your three most expensive production queries, run EXPLAIN, and check the partition filter statistics. If a query says "Scanning 50 partitions" when it should hit 2, stop. Fix the partition spec or add a secondary partition transform like months(created_date).

'We validated pruning on staging, but the staging dataset was 5% of production. Prod had different data distribution. Everything broke.'

— Senior data engineer, post-migration postmortem

Test compaction with your actual data volume

Most teams test compaction on a 10-GB subset. Then they hit production with 2 TB of small files leftover from streaming micro-batches, and the compaction job runs for six hours, consumes half the cluster's memory, and fails with an out-of-memory error. That hurts. Worse: some formats like Hudi require incremental compaction that blocks concurrent reads if misconfigured. You need to simulate your worst-case file count—not your average. Grab a day's worth of production data or generate a synthetic dataset with the same file-size distribution. Run compaction with the same job profile (Spark executor memory, parallelism, shuffle partitions) you plan to use in prod.

The tricky bit is tuning the file target size. Aim for 256 MB to 1 GB per file for most query engines. Too small and your metadata layer chokes; too large and you lose parallelism. We fixed this by setting target-file-size-bytes=536870912 in our Iceberg table properties and running a REWRITE DATA FILES that took thirty-two minutes on our largest table. Acceptable? Yes. But only because we tested it before a Friday deploy rather than discovering it during the Monday morning report run.

Write a rollback plan for partition schema changes

Here's the scenario: you alter a table's partition spec from days(event_date) to months(event_date). Two hours later, the finance team's month-over-month comparison returns zero rows. Your new partition layout broke their query because it relied on a WHERE event_date BETWEEN '2024-01-01' AND '2024-01-31' filter that now maps to a different set of files. Iceberg and Delta support partition evolution, but the old data still lives under the old partition layout. Queries written before the change might not know how to reconcile.

Your rollback plan needs three things. First, a snapshot of the table metadata before the schema change—most formats support time-travel queries, so document the snapshot ID. Second, a revert script that restores the previous partition spec AND rewrites the table's metadata pointers to that snapshot. Third, a communication channel: ping the analytics team, tell them "Partition schema changed at 14:00 UTC—if your dashboard breaks, your query may need explicit partition hints until we rewrite the historical data." Without that plan, you roll back by restoring a backup from S3, which takes hours and loses any writes that happened in between. Not acceptable for a production pipeline.

Write the rollback script before you make the change. Test it on a copy of the table. Then run the migration with a safety net you've already proven works.