What is the correct setup for API instances VS worker instance in regards to CUBEJS_REFRESH_WORKER

[kevinleeTCA]

Problem

Our cube instance setup is following the production checklist:

1. We have 2 API instances with:

   CUBEJS_DEV_MODE=false (explicit setup)
   CUBEJS_REFRESH_WORKER = false (default)
   CUBEJS_ROLLUP_ONLY = false (default)

2. We have 1 worker instance with:

   CUBEJS_DEV_MODE=false (explicit setup)
   CUBEJS_REFRESH_WORKER = true (explicit setup)
   CUBEJS_ROLLUP_ONLY = false (default)

followed the recommendation here

But this setup is causing issue of

Error: No pre-aggregation partitions were built yet for the pre-aggregation serving this query and this API instance wasn't set up to build pre-aggregations. Please make sure your refresh worker is configured correctly, running, pre-aggregation tables are built and all pre-aggregation refresh settings like timezone match.

when we querying the related cube. However, what we expect is:

When executing a query, Cube will try to match and fulfill it with a pre-aggregation in the first place. If there's no matching pre-aggregation, Cube will query the upstream data source instead, unless the rollup-only mode is enabled.

As rollup only mode is false in the both type of instances, but it looks like the API instance is behaving like CUBEJS_ROLLUP_ONLY=true

I also noticed preAggregationsOptions.externalRefresh, we did not explicit set it up, so by default it is false

When running a separate instance of Cube to refresh pre-aggregations in the background, this option can be set on the API instance to prevent it from trying to check for rollup data being current - it won't try to create or refresh them when this option is true

But the API instance behaves like it is true, as it didn't try to create or refresh them.

Another thing I don’t know is how to toggle externalRefresh using ENV VARs, it doesn’t seem to have this ENV VAR, only available in orchestrator_options

Other cubejs related env (API instance):

CUBEJS_CACHE_AND_QUEUE_DRIVER=cubestore
CUBEJS_DB_TYPE=redshift
CUBEJS_DB_NAME=dev
CUBEJS_DB_PORT=5439
CUBEJS_DEV_MODE=false

Worker:
···
CUBEJS_CACHE_AND_QUEUE_DRIVER=cubestore
CUBEJS_DB_TYPE=redshift
CUBEJS_DB_NAME=dev
CUBEJS_DB_PORT=5439
CUBEJS_REFRESH_WORKER=true
CUBEJS_DEV_MODE=false
···