Changing Warehouse Connection: Queued Aggregate Builds

Note: this applies to C2024.1.x

SYMPTOM

Performance of large queries and aggregate builds is poor, leading to query timeouts and queued agg builds with huge build times, when warehouse connections are improperly configured (ie; Small Interactive Queries, Large Interactive Queries, & System Queries all being routed to the same x-small warehouse).
Even when the warehouse connections are appropriately sized, query performance may be acceptable, but the aggregate build queue does NOT get routed to the updated warehouse. This leaves the aggregate build queue in a “lost” state, and the C2024.1.x product lacks the ability to reset aggregate builds, clear the queue, or trigger a rebuild in the UI.

The screenshots below show the error states.

Error State: Aggregate instance status is Queued for all aggregates with huge Last Build Durations

 

Error State: After clearing queues, Last build Durations is equal to (time since) Last Build Started  (1.12 billion milliseconds = ~13 days → “Last build started” to Today)

ROOT CAUSE

Changing the warehouse connection in the Data Warehouses section of the settings panel while aggregate builds are queued causes the pending aggregates to be queued indefinitely.

RESOLUTION(s)

Note: Only tested on C2024.1.x

To clear the aggregate build queue:

  1. (as admin) Go into engine settings and turn off aggregates.
  2. Restart the Engine container to apply configuration.
  3. (as admin) Go into engine settings and turn aggregates back on.
  4. Restart the Engine container to apply configuration.

By following these steps above, new aggregates are allowed to be routed to the updated warehouse connection.

To prioritize the pending aggregate build queue:

  1. (as admin) Go into engine settings and change aggregate.queue.priority.mode to 0 (0 implies prioritizing new instances over instances that are part of a batch build).
  2. Restart the Engine container to apply configuration.
  3. (optional) Follow steps 1 & 2 to revert aggregate.queue.priority.mode to 1 (1  Prioritizes building batch instances before new instances).

By following these steps above, pending agg builds are prioritized.

Was this article helpful?

0 out of 0 found this helpful