Micro-batch ingestion turns a Hive table into a metadata problem

The 40–70 seconds "before any row is read" is the clue: planning and file listing dominate, execution does not. But three distinct causes produce that signature, and they have different fixes, so measure first.

Measurements:

1. Objects per partition. Use S3 inventory or CLI counts on a few dt prefixes. If the hot partition has tens of thousands of objects and the cold ones have hundreds, object count is the primary cause.
2. Table type. For a Hive table in CDW, determine whether it is ACID (transactional) or a plain external table. This decides whether compaction is even relevant - compaction moves and rewrites delta files for transactional tables; it does not apply to a non-ACID external table, so chasing compaction settings there wastes a week.
3. Compaction backlog, if transactional: list compaction state and look for a large queue of initiated or ready entries that never complete, plus many small delta directories in the partition location.
4. Query profile: compare planning/compilation time with execution time, and run the same predicate against one small partition as a control. If the small partition is fast, the problem scales with file count, not with cluster capacity - consistent with your already-failed warehouse resize.
5. Write pattern: confirm the two-minute cadence and how many files each batch creates; a batch that writes one file per reducer multiplies the count.

Only after those numbers do you commit to a remedy. Resizing a warehouse cannot fix a listing problem, which is why the first attempt did nothing.

Treat it as a rolling rewrite with a small blast radius, not a big-bang table rebuild.

Sequence:

1. Target file size first. Pick 128–256 MB as the goal; it dictates how many files each hot partition should have. Do the arithmetic on one partition before touching anything: current bytes ÷ target size = expected object count. That number is your acceptance test.
2. Rewrite partition by partition, newest last. For each hot partition, read it into a staging location with a repartition or coalesce step so the writer produces the target file count, then atomically replace the partition. Newest partitions are the most queried and the most volatile - doing them last means any mistake hits stale data first.
3. Do it off-peak and in batches of a few partitions, verifying query latency and row counts after each batch. If a dashboard breaks, you have lost one partition's work, not the table.
4. If the table is transactional, schedule or trigger compaction for the affected partitions in parallel with the rewrite, and check that the backlog actually drains rather than queues.
5. Fix the writer. Reduce ingestion cadence from two minutes to something the consumers actually need, and add a repartition/reduce step in the ingestion job so each batch writes a bounded number of files. Without this, the rewrite is undone within weeks.
6. Revisit partitioning if the dashboard predicates never align with dt; a rewrite is the cheapest moment to change it.

Report the measured improvement per partition - planning time and object count - so the fix is evidenced, not asserted.

The table degraded because nobody owned its physical layout. Assign that ownership explicitly and make it observable.

Guardrails:

• Table design standard: every new ingestion target specifies partition key, target file size, write cadence and maximum files per batch before the pipeline is approved. Review it like a schema change.
• A file-count monitor: a scheduled job that counts objects per partition for the top tables and alerts when a partition exceeds a threshold (for example, more than an order of magnitude above the target). This is cheap and catches the trend in week two instead of year two.
• Cadence challenge: two-minute micro-batches are a latency requirement that must be justified by a consumer. If dashboards refresh every 15 minutes, the cadence is paying S3 request costs for nothing.
• Compaction and housekeeping in the runbook, with an owner and a check that it is draining, not just scheduled.

Cost control:

• Track S3 GET/LIST request volume per bucket and per workload with cost allocation tags, so the next regression is attributable.
• Prefer larger files everywhere - the same rule reduces both listing time and per-request cost.
• Review lifecycle rules so old partitions are archived or moved to a cheaper class, which also shrinks the listing surface.

The judgement the exam is testing: query latency on cloud object storage is usually a layout and metadata property, not a compute-sizing property. Adding warehouse capacity buys nothing when the planner is enumerating 120,000 keys.