Owen Trask

Also check the actual used range rather than the rows you can see. Select a cell and hit ctrl+end. If it jumps to row 40000 because something was once pasted there, you have 32000 rows of invisible formatting being evaluated. Delete those rows properly rather than clearing them.

Four seconds is still slow for that shape. Worth carrying on down the list — the matrix cell count is probably doing the rest.

Helper column with &"" fixed it in about thirty seconds after two hours of trying to make QUERY behave. Thank you.

Worth knowing the accounting rule too: the cgroup counts page cache against your limit, so a container that reads or writes large files can be killed even though the application's own allocations are modest. If your workload touches big files, that is a real and very confusing source of OOMKills.

That is the single most common version of this. The JVM needs headroom above the heap for metaspace, thread stacks and native buffers — as a rule of thumb leave 25% of the container limit outside the heap.

That was it. Eleven missing days across the year, all Sundays and two public holidays. Rebuilt with CALENDAR and every month populates now. I had no idea it failed silently rather than complaining.

Be a bit careful lowering ndots globally — short in-cluster names like postgres rely on the search path. Setting it per workload is safer than cluster-wide.

Learned this the hard way on a table with 400k SKUs. The measure was correct and the report was unusable.

For anyone finding this later: the general rule is that every range inside an ARRAYFORMULA should either be the same height or be an absolute reference. A bare relative single cell in there means trouble.

If those CSVs are a monthly drop that several reports use, consider landing them once in a dataflow and pointing the reports at that. Credentials get configured in one place and you stop paying the file-parsing cost in every dataset.

If this is a workload you want to survive losing a zone, the storage choice is the real decision rather than the drain procedure. Zonal block storage plus a single-replica StatefulSet is a single point of failure by design. Either accept that and plan capacity per zone, or move to something replicated at the storage layer.

Left aligned means Sheets is storing them as text, so you are sorting alphabetically — "12" before "03" makes perfect sense to a string comparison.

The #VALUE pattern gives away the real problem: your export is almost certainly US format (MM/DD/YYYY) and your sheet is UK. Rows where the day is 13 or higher fail outright because there is no month 13. Rows where the day is 12 or lower silently convert to the wrong date, which is much worse — 03/11 becomes 3 November when it meant 11 March, and nobody notices until a quarterly total is off.

Parse it explicitly rather than trusting DATEVALUE:
=ARRAYFORMULA(IF(A2:A="","",DATE(INDEX(SPLIT(A2:A,"/"),,3), INDEX(SPLIT(A2:A,"/"),,1), INDEX(SPLIT(A2:A,"/"),,2))))
That reads position 3 as year, 1 as month, 2 as day. Swap the last two arguments if your source really is day first. Then format the output as a date and sort on that.

Honest counterpoint: if the report is genuinely one grain, nobody else builds on the dataset, and it fits comfortably in memory, a flat table is not a sin. The cost is not correctness, it is every future change. Adding a new attribute to a dimension touches one small table; adding it to a flat table is a 12 million row reload every time.

Good caveat. I put a last-recalculated cell at the top of anything built on IMPORTRANGE so people can judge for themselves.

Stack it, but stack it once into a real all-sites tab rather than doing it inside every summary formula.

The pattern that has held up for me: one hidden tab that is nothing but ={IMPORTRANGE(url1,"data!A2:F"); IMPORTRANGE(url2,"data!A2:F"); ...}, with a site name column carried through from each source, then every summary on the sheet is a QUERY against that one range. Adding site 31 is one line in one place instead of a new column in 40 formulas.

The VLOOKUP-per-tab approach does not fail gradually. It fails the day somebody renames a tab.