Jobs API reports 'done' for pre-aggregation builds that imported zero rows, leaving ready-but-empty (ghost) partitions that shadow good data

[tete17]

Describe the bug

The pre-aggregations jobs API (POST /cubejs-api/v1/pre-aggregations/jobs) can report builds as done that in fact imported zero rows, leaving "ghost" partition tables in Cube Store: registered, ready, empty. Cube then serves these empty tables as the newest version — shadowing older correct tables — and the failure is invisible: no error in the job status, no error in logs, dashboards silently show empty/zero data for the affected partitions.

We hit this in production (Cube v1.6.57, databricks-jdbc source, Cube Store external storage, separate API instances + refresh worker on Kubernetes): triggering a force-build of 4 monthly partitions produced 3 empty-but-ready tables. Databricks' own query history shows all 4 source SELECTs FINISHED with full row counts (426k/479k/507k/286k rows), yet 3 of the 4 Cube Store tables ended up with 0 rows, done status, and no logged error. The jobs API even reported the 3 builds done ~17 minutes before their source queries finished executing.

Reading the v1.6.57 source, this is a combination of four defects that turn any client-side import failure into silent data corruption:

1. Job status conflates "table exists" with "build succeeded"

preAggregationsJobsGET resolves a job's terminal state via getPreAggJobResultStatus → orchestrator.isPartitionExist(...) — i.e. does a table with the target versioned name exist:
https://github.com/cube-js/cube/blob/v1.6.57/packages/cubejs-api-gateway/src/gateway.ts (getPreAggJobResultStatus)

Since the versioned table becomes visible in Cube Store before/independently of a completed import (see #3), done does not imply the data landed. In our incident, done was reported while the source query was still running on the warehouse.

2. Job build failures never reach the job status (fire-and-forget)

In the forceBuild && isJob branch of PreAggregationLoader.loadPreAggregationWithKeys, the build is kicked off asynchronously and its rejection is only logged — the PRE_AGG_JOB_<token> cache entry is never updated to failure:
https://github.com/cube-js/cube/blob/v1.6.57/packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationLoader.ts (the if (this.isJob) branch: this.executeInQueue(...).catch(e => { this.logger('Pre-aggregations build job error', ...) }))

A failed or killed build is therefore indistinguishable from a successful one through the API (combined with #1, it reads done).

3. Cube Store import can leave a "ready" empty table

CubeStoreDriver.importRows runs CREATE TABLE first and then batch-INSERTs. If the downloaded row set is empty (or the inserting process dies at the right moment), the result is a legitimate-looking, queryable, empty table:
https://github.com/cube-js/cube/blob/v1.6.57/packages/cubejs-cubestore-driver/src/CubeStoreDriver.ts (importRows)

In our case the JDBC download delivered an empty result set without raising (the warehouse-side statements provably produced full data; suspected silent cloud-fetch/JVM-memory failure under 4 concurrent large downloads — we previously saw the same ghost signature from an OOM-killed refresh worker). The empty import "succeeded", and the new empty table shadowed the previous good table for that partition. Note the same mechanism also blocks self-healing: the scheduler sees a ready table for the partition and never rebuilds it, so the ghost persists indefinitely (for sealed/update_window-expired partitions, forever).

4. isJob bypasses the externalRefresh guard

loadPreAggregation short-circuits into the build path when isJob is true, before the externalRefresh check is reached:
https://github.com/cube-js/cube/blob/v1.6.57/packages/cubejs-query-orchestrator/src/orchestrator/PreAggregationLoader.ts (if (this.isJob || !(notLoadedKey && !this.waitForRenew)))

So an API instance configured to never build pre-aggregations (externalRefresh: true, the default for API workers without CUBEJS_PRE_AGGREGATIONS_BUILDER) will still execute jobs-API builds in-process. API instances are routinely restarted (deploys, config rollouts, autoscaling), which makes these builds much more likely to be killed mid-import than refresh-worker builds — feeding defect #3.

To Reproduce

Hard to reproduce deterministically because it requires the import to fail client-side while the build "completes"; the shape that bit us:

1. Partitioned rollup (monthly), external: true (Cube Store), databricks-jdbc source, separate api + refresh worker instances.
2. POST /cubejs-api/v1/pre-aggregations/jobs with action: post selecting the pre-aggregation (several closed/sealed monthly partitions get scheduled at once, all builds run concurrently on the receiving API instance).
3. Have the import fail without an exception (large concurrent result sets through a memory-constrained JVM; or kill/restart the executing instance mid-download).
4. action: get on the job tokens → done. Cube Store now holds ready 0-row tables for those partitions and queries serve empty results from them; older correct tables are shadowed and later GC'd.

Expected behavior

• done should mean the build completed and the import landed (e.g. verify import completion / persist the imported row count on the version entry, not mere table existence).
• A failed/killed jobs-API build should surface as failure in the job status (persist the error in the .catch).
• An empty import for a partition should not produce a ready table that shadows a previous non-empty version silently (at minimum, log/flag it; ideally make table-create + import + ready atomic).
• The jobs API should respect externalRefresh / instance roles, or route builds to the refresh worker rather than executing them on API instances.

Version:
1.6.57 (cubejs/cube:v1.6.57-jdk), Cube Store, databricks-jdbc driver, Kubernetes (3 API replicas + 1 refresh worker, shared Cube Store queue).

Additional context

Timeline evidence from our incident (all times UTC, from Databricks system.query.history + Cube Store information_schema):

• 14:45 jobs POSTed (4 monthly partitions of one rollup);
• 14:52 jobs GET: 3 of 4 report done;
• 15:09 all 4 source SELECTs finish on the warehouse with full row counts;
• result: the 3 "done" partitions exist as ready 0-row tables; the 4th imported correctly;
• the same partitions rebuilt correctly minutes later via the scheduler path (drop table → scheduled refresh rebuilds), single-build-at-a-time on the refresh worker.