How would you set ingest batching and compaction policy for a continuously loaded, dashboard-queried columnar table?

## Frame it as a budget, not a setting There is no correct compaction interval in the abstract. Three quantities trade against each other and the workload picks the point: - **Freshness** — how long after an event lands it may become visible. - **Read cost** — how much per-file overhead and dead data queries carry. - **Write amplification** — how many times the same bytes are written before they settle. Move one and at least one other moves. Longer ingest batches produce larger files, cutting both read cost and merge work, at the price of freshness. Aggressive compaction cuts read cost and pays with compute. Doing nothing is cheapest today and worst in six months. ## Start with the freshness requirement, honestly Ask what a stale dashboard actually costs. Most executive dashboards refresh hourly and are consumed daily; "real time" is usually an aspiration inherited from the requirements document rather than a decision anyone made. Operational alerting genuinely needs seconds, but it is often a different, much smaller table. Once the number is real, set the writer's flush trigger to it — flush at whichever comes first, the target file size or the freshness deadline. If arrivals are heavy enough that the size trigger fires first, you have won: files land well-sized and compaction has little to do. If the deadline fires first, you know in advance that compaction must clean up, and roughly how much. ## Sizing the merge policy Compaction reads small files and writes larger ones. The two classic shapes: - **Tiered/size-based**: merge files of similar size into one bigger file, repeatedly. Write amplification is roughly the number of levels the data passes through, which is low. Readers may face several overlapping files covering the same key range. - **Levelled/aggressive**: maintain a tighter, more ordered file layout by rewriting more often. Reads are cleaner and skipping is more effective; writes are rewritten more times. Two policy points matter more than the label. First, a **stop condition**: once a file reaches the target size, exclude it from further merging. Merging two 400 MB files into 800 MB rewrites 800 MB and buys nothing, and this omission is the most common way a compaction bill runs away. Second, **recency awareness**: recent data is small, fragmented, and hot; old partitions are settled and should be compacted once and then left alone. A policy that periodically re-examines every partition of a multi-year table wastes almost all of its work. ## Isolate the compute Compaction competes with ingestion for write bandwidth and with queries for CPU and I/O. Give it its own compute pool or its own window. This is also what makes the cost visible: when compaction runs on the same resources as dashboards, its cost hides inside "queries got slower" and nobody can reason about it. A separate pool turns it into a line item you can size, schedule and defend. ## Check partitioning before tuning anything else If the table is partitioned finely enough that each partition receives only a trickle per interval, no batching produces large files and compaction has nothing to merge across. Very fine partitioning is the most common root cause of a compaction policy that appears not to work. Coarsening the partition granularity — hour to day, or dropping a high-cardinality dimension from the partition key — fixes more small-file problems than any writer tuning. ## The hot/cold split when freshness genuinely is tight When a consumer truly needs sub-minute visibility and the table is also queried heavily over history, split the concerns: a small recent region accepting frequent small writes, and the historical body kept in well-sized files, with a scheduled promotion that folds the hot region into the cold one. Queries union the two. This costs a little query complexity and buys both freshness and read efficiency, and it localises the fragmentation to a region small enough that its overhead is bounded. ## Instrument the policy Make the budget observable or it will drift: - average file size and file count per partition, with an alert threshold; - planning time as a share of query time; - bytes written by compaction versus bytes ingested — this **is** the write-amplification factor, and it is the number to defend in a cost review; - compaction lag: how far behind the merge queue is running. ## What a strong answer sounds like It refuses to name an interval without first asking about freshness and arrival rate. It states the three-way trade explicitly, gives a stop condition for merging, separates hot recent data from settled history, insists on isolated compute so the cost is attributable, and names partition granularity as the thing to check before tuning anything. It also concedes that the right answer changes as volume grows, and specifies which metric would trigger revisiting it.