Cube+Cubestore - pre-aggregation only built for one tenant

[antbz]

Describe the bug

I am working on a production deployment of Cube+Cubestore with multi-tenancy. I use pre-aggregations in some models, which are working fine. However, one specific pre-aggregation is currently only being built for one tenant, which causes other tenant's queries that hit it to fail, with the API claiming that it can't find the expected pre-aggregation table:

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. Expected table name patterns: pre_aggregations_<tenant>.events_events_by_category_and_city*_5lcntksd_*

I can't seem to figure out the reason for this. Looking at cubestore logs, I see the trail of building all the other pre-aggregations for all tenants, but the specific one I am talking about is never uploaded for the tenants in question. Cube refresh workers also do not show any errors related to this pre-aggregation.

The following things are making the problem here unclear to me:

1. The rest of the cube is working -> thus it is able to find the data in the origin database, and filter it correctly by tenant
2. The rest of the pre-aggregations are working -> thus my preAggregationsSchema and scheduledRefreshContexts should be OK otherwise I would have no pre-aggregations (right?)
3. The pre-aggregation works in development mode, when connected to the same sources and tenants -> not 100% sure here, but this should mean that the pre-aggregation is able to run on the data
4. It works in one tenant -> thus it can't be that somehow the pre-aggregation isn't loaded in the production environment

I have tried:

• Deleting all cubestore cache and rebuilding everything from scratch -> no effect
• Configuring the instance with only a single tenant (which currently does not have the pre-aggregation) -> also does not build the pre-aggregation

To Reproduce

Steps to reproduce the behavior:

1. Create a multi tenant cube configuration, with a model that has a pre-aggregation
2. Deploy the configuration in a production environment, using workers+cubestore
3. Make a query using the pre-aggregation on two different tenants
4. One tenant works, the other one does not

Expected behavior

Both tenants should have the pre-aggregation built.

Minimally reproducible Cube Schema

cubes:
  - name: events
    sql_table: '"{COMPILE_CONTEXT.securityContext.tenantId}".events'
    data_source: default
    public: false

    dimensions:
      - name: event_id
        type: number
        sql: id
        primary_key: true

      - name: event_status
        type: string
        sql: status

    measures:
      - name: num_events
        type: count
        drill_members:
          - categories.category_name
          - cities.city_name
          - event_status

    pre_aggregations:
      - name: events_by_category_and_city
        measures:
          - CUBE.num_events
        dimensions:
          - categories.category_name
          - cities.city_name

  - name: categories
    sql_table: '"{COMPILE_CONTEXT.securityContext.tenantId}".categories'
    data_source: default
    public: false

    dimensions:
      - name: category_id
        type: number
        sql: id
        primary_key: true

      - name: category_name
        type: string
        sql: name

  - name: cities
    sql_table: '"{COMPILE_CONTEXT.securityContext.tenantId}".cities'
    data_source: default
    public: false

    dimensions:
      - name: city_id
        type: number
        sql: id
        primary_key: true

      - name: city_name
        type: string
        sql: name

Version:
v1.1.7

Additional context

Here is my cube.js configuration file:

const TENANTS = process.env.CUBEJS_TENANTS
  ? process.env.CUBEJS_TENANTS.split(",")
  : [];

module.exports = {
  contextToAppId: ({ securityContext }) =>
    `CUBE_APP_${securityContext.tenantId}`,
  contextToOrchestratorId: ({ securityContext }) =>
    `CUBE_APP_${securityContext.tenantId}`,
  scheduledRefreshContexts: () =>
    TENANTS.map((tenantId) => ({
      securityContext: {
        tenantId,
      },
    })),
  preAggregationsSchema: ({ securityContext }) => {
    return `pre_aggregations_${securityContext.tenantId.replace(/-/g, "_")}`;
  }
}

[igorlukanin]

Cube refresh workers also do not show any errors related to this pre-aggregation.

Configuring the instance with only a single tenant (which currently does not have the pre-aggregation) -> also does not build the pre-aggregation

I would focus on these parts. If the pre-aggregation is not built, there should be an error message in the refresh worker logs. It should not just silently skip it. I would suggest that you experiment with this toy setup that you've created and see that refresh worker starts building the pre-agg for this tenant. And if it's not, what the error shown is.

Also, if you can deploy this toy setup to Cube Cloud and see what happens there, you might get some insights as well. (I mean, there are tools for observing pre-aggregation builds there as well.)

[antbz]

I replicated my production deployment with this stack:

services:
  cube_api:
    restart: always
    image: cubejs/cube:latest
    ports:
      - 4000:4000
      - 15432:15432
    env_file:
      - .env.production
    environment:
      - CUBEJS_CUBESTORE_HOST=cubestore_router
    volumes:
      - .:/cube/conf
      - ~/.aws:/root/.aws
    depends_on:
      - cube_refresh_worker
      - cubestore_router
      - cubestore_worker_1
      - cubestore_worker_2
 
  cube_refresh_worker:
    restart: always
    image: cubejs/cube:latest
    env_file:
      - .env.production
    environment:
      - CUBEJS_CUBESTORE_HOST=cubestore_router
      - CUBEJS_REFRESH_WORKER=true
    volumes:
      - .:/cube/conf
      - ~/.aws:/root/.aws
 
  cubestore_router:
    restart: always
    image: cubejs/cubestore:arm64v8
    platform: linux/arm64
    env_file:
      - .env.production
    environment:
      - CUBESTORE_WORKERS=cubestore_worker_1:10001,cubestore_worker_2:10002
      - CUBESTORE_REMOTE_DIR=/cube/data
      - CUBESTORE_META_PORT=9999
      - CUBESTORE_SERVER_NAME=cubestore_router:9999
    volumes:
      - .cubestore:/cube/data
      - ~/.aws:/root/.aws
 
  cubestore_worker_1:
    restart: always
    image: cubejs/cubestore:arm64v8
    platform: linux/arm64
    env_file:
      - .env.production
    environment:
      - CUBESTORE_WORKERS=cubestore_worker_1:10001,cubestore_worker_2:10002
      - CUBESTORE_SERVER_NAME=cubestore_worker_1:10001
      - CUBESTORE_WORKER_PORT=10001
      - CUBESTORE_REMOTE_DIR=/cube/data
      - CUBESTORE_META_ADDR=cubestore_router:9999
    volumes:
      - .cubestore:/cube/data
      - ~/.aws:/root/.aws
    depends_on:
      - cubestore_router
 
  cubestore_worker_2:
    restart: always
    image: cubejs/cubestore:arm64v8
    platform: linux/arm64
    env_file:
      - .env.production
    environment:
      - CUBESTORE_WORKERS=cubestore_worker_1:10001,cubestore_worker_2:10002
      - CUBESTORE_SERVER_NAME=cubestore_worker_2:10002
      - CUBESTORE_WORKER_PORT=10002
      - CUBESTORE_REMOTE_DIR=/cube/data
      - CUBESTORE_META_ADDR=cubestore_router:9999
    volumes:
      - .cubestore:/cube/data
      - ~/.aws:/root/.aws
    depends_on:
      - cubestore_router

Based on the https://cube.dev/docs/product/deployment/core one but adjusting for AWS Athena as well as removing the export bucket. With this setup I was able to reproduce the error locally.

Obviously, my real data model is larger than what I showed in my original comment. I knew one of my cubes was technically invalid for some tenants, as it uses columns that weren't present in all of their schemas. This has happened for a while and wasn't affecting this pre-aggregation. Now, it seems that if I remove the pre-aggregation that causes the error, this pre-aggregation is built.

For now I'll move to conditionally including the cube depending on the tenant, as it is indeed better than having the error happening all the time. But I am still confused why it builds some pre-aggregations, but fails to build this one, because of the error in the other aggregation.

Is it doing the aggregations in sequence and stops when it fails one? Is folder hierarchy at play here, because it does all the pre-aggregations in my top level model/ models, but the one missing is in models/sub_directory? I'll get back to this next week after I do some more experiments.

[antbz]

So, I did some more experiments on this.

For context, my model is roughly organized as follows:

model/cubes
├── cube_a.yml
├── cube_b.yml.jinja
└── sub_folder
    ├── cube_c.yml.jinja
    └── cube_d.yml

Say cube_a is the one that contains the faulty pre-aggregation. In a tenant where the pre-aggregation fails, the following appears to be the behaviour:

• Pre-aggregations in cube_b run with no issues
• Pre-aggregations in cube_c fail
• Pre-aggregations in cube_d run with no issues

The only thing that is special about cube_c seems to be that it is a .jinja template and is inside a subfolder. All cubes in my model that fit these criteria seem to fail to compute pre-aggregations.

@igorlukanin IDK if this is an issue you believe warrants further investigation. Please let me know if I can help further.