Lakehouses promised the scalability and cost-efficiency of object storage with the semantics and performance of data warehouses. For many organizations operating at the hundreds-of-terabytes to petabyte scale, that promise is still true — until metadata stops cooperating.

Metadata sprawl in a lakehouse shows up as millions of small files, long object-store list times, exploding manifest and snapshot stores, slow planning stages in query engines, and high catalog API costs. This analysis describes how to measure the problem, why it happens in production, and practical, operational strategies to control metadata and file-count bloat for environments with 10M+ objects and manifests.

Why metadata sprawl matters (beyond storage)

  • Query planning latency: Engines spend more time reading manifests, scanning manifest lists, and computing partition pruning decisions, which increases per-query latency even when actual data reads are small.
  • Operational cost: Object-store LIST and GET operations, catalog API calls and Glue/Hive/Metastore operations incur direct costs and throttling risks at scale.
  • Throughput limits: Excessive file counts and tiny files reduce I/O efficiency and increase syscall overhead on compute that reads data.
  • Maintenance complexity: Garbage collection, snapshot retention, and compaction windows become difficult to schedule without impacting freshness.

Root causes: how metadata balloons

  1. High-cardinality or over-granular partitioning: Partitioning by too many dimensions or highly-cardinal keys (user_id, device_id, transaction_id) creates many directories/partitions with few bytes each.
  2. Streaming or micro-batch writes with small files: Many streaming jobs append frequent small files rather than consolidating writes to target sizes.
  3. Uncontrolled snapshot history: Time-travel and snapshot retention keep manifests and metadata files indefinitely unless cleaned.
  4. Frequent schema evolution and partition spec changes: Each evolution can produce new metadata layers and split manifests.
  5. Uncoordinated ingestion producers: Multiple producers writing independently produce file-per-producer artifacts.

Measure the problem first: metrics and diagnostics

Before you tune, quantify. Key metrics that correlate with metadata pain:

  • Table-level file count and per-partition file count (files per partition)
  • Average and median file size
  • Manifest file count and cumulative size (for Iceberg-like tables)
  • Snapshot count and age distribution
  • Catalog API calls per minute and latency (Glue/Metastore/Unity Catalog)
  • Object-store LIST latency and cost per operation
  • Query planning time vs data scan time (time spent before read)

Collect these with your observability tooling (Prometheus/Grafana, built-in engine metrics) and store historical trends. Useful diagnostics include listing the top N partitions by file count and identifying partitions with median file size under a target (for example, rule of thumb thresholds are below).

Practical strategies to reduce metadata sprawl

1) Revisit partitioning

  • Prefer coarse-grained partitioning for large tables: date (day/week) or a small number of buckets rather than high-cardinality keys.
  • Use composite strategies: coarse top-level partition (date) with clustering (Z-order, sort) inside files for locality instead of many partitions.
  • When you need high-cardinality filters, prefer secondary indexes, bloom filters, or data skipping metadata rather than partitioning on those keys.

2) Set and enforce target file sizes

Small files are the most common metadata driver. Pick a target file size aligned to your query and storage patterns — often 64–512 MB for analytical workloads. Enforce this at write-time:

  • Batch or buffer streaming writes to produce larger output files.
  • Use SDKs or sinks that support file rolling based on size/time.
  • Provide ingestion SLAs: e.g., allow up to 10 minutes of lag to consolidate writes.

3) Automated compaction with context

Compaction is necessary but not free. Operational guidelines:

  • Schedule background compaction jobs with partition-awareness — prioritize partitions with many small files or recent activity.
  • Combine compaction with MVCC-cleanup: remove tombstones and obsolete files as part of compaction runs.
  • Apply incremental compaction: compaction windows that process hot partitions more frequently and cold partitions less often.

4) Metadata housekeeping and snapshot management

  • Define snapshot and history retention aligned to business needs. If unlimited time travel isn't required, expire snapshots aggressively.
  • Use table formats' built-in snapshot management: checkpointing/manifest consolidation where available, plus periodic orphan-file cleanup.
  • Archive older snapshots to cheaper metadata stores if regulatory or audit needs require long retention but not active query access.

5) Catalog architecture & caching

Catalogs are the control plane. Scaling advice:

  • Avoid making the catalog the canonical store of every micro-object; prefer metadata files in object storage that query engines can cache.
  • Use a cache layer for catalog calls in query engines (manifest caches, metastore caches) with explicit eviction TTLs.
  • Partition the catalog: separate hot tables from cold historic tables across different catalog instances or namespaces to reduce churn.

6) Engine-level optimizations

  • Enable manifest/metadata pruning features (manifest filtering, stats-based pruning) so planners read fewer manifests.
  • Increase parallelism for manifest processing if the planner can do it safely — but balance against API rate limits.
  • Tune query-engine cache sizes and TTLs based on observed catalog update rates.

Operational practices and runbook items

Operationalize the above with these concrete steps:

  1. Baseline: run a one-time audit that lists largest tables by file count, largest manifests, partitions with the most small files.
  2. Set SLOs: target average file size and maximum files per partition (team-specific; e.g., aim for avg file size > 64 MB, partitions with 1000 files).
  3. Automated alerts: trigger when file count growth rate > X% in 24h or when catalog API errors increase.
  4. Compaction cadence: create scheduled compaction jobs per table class (hot/daily, warm/weekly, cold/monthly).
  5. Review snapshot retention quarterly: document business reasons for time travel windows and adjust retention accordingly.

Trade-offs: freshness vs metadata efficiency

Every mitigation has costs. Larger files increase query throughput but can increase ingest latency. Aggressive snapshot expiration reduces storage and metadata sizes but limits time travel and rollback windows. The right balance depends on workload class:

  • Event streams and real-time analytics: allow slightly smaller files and shorter compaction windows to prioritize freshness.
  • Historical analytics and BI: favor bigger files, coarse partitions, and less snapshot history to optimize cost and latency.
  • Regulatory/audit needs: may force long retention; in that case, separate archival copies and a compacted “active” layer are sensible.

Tooling and features to watch

Most modern table formats and engines provide primitives for the tactics above. Look for:

  • Manifest or snapshot consolidation features (to reduce manifest file counts)
  • Built-in compaction operators that can be triggered incrementally
  • Metadata APIs that support filtered listing and fast manifest pruning
  • Catalogs with low-latency caching and rate-limiting controls

Checklist for the next 90 days

  1. Run the metadata audit to identify the top 10 tables causing most object-store pressure.
  2. Set conservative SLOs on average file size and files-per-partition; instrument and alert.
  3. Implement streaming write batching and deploy a compaction job for hot partitions.
  4. Adjust snapshot retention and implement routine snapshot cleanup for non-essential tables.
  5. Test catalog caching and tune TTLs to reduce planner metadata reads without breaking freshness.

Metadata sprawl is not a sudden failure; it's a chronic operational condition that worsens when teams scale quickly. The right mitigation combines careful partition and file-size design, automated compaction and snapshot housekeeping, catalog and engine tuning, and measurable SLOs. For data engineering teams, treating metadata as first-class operational telemetry — not a side effect of file writes — is the fastest path to predictable performance and cost at scale.