pre-aggregation tables in bigquery are being dropped immediately

[VictorAtTransmit]

Problem

I'm trying to run a simple test with a rollup pre aggregation.
I'm running the latest cube.js image in a docker container on my local machine, and configured it (using a service account) with a remote BigQuery dataset.
testing it using the playground seems to work - I can query data and even see the "Query was accelerated with pre-aggregation".

however, when looking at the dev-pre-aggregations dataset, I can notice sporadically aggregated tables being created but then they are being dropped immediately.
looking at the BQ jobs, I can clearly see the table creation, followed by a select and immediate drop - all within a 2-3 seconds.

relevant cubejs env vars:

CUBEJS_DB_BQ_PROJECT_ID=...
CUBEJS_DB_BQ_CREDENTIALS=...
CUBEJS_DB_TYPE=bigquery
CUBEJS_DEV_MODE=true
CUBEJS_SCHEMA_PATH=model
CUBEJS_DB_BQ_LOCATION=europe-west3
CUBEJS_LOG_LEVEL=trace
DEBUG_LOG=true

Related Cube.js schema

cubes:
  - name: people
    sql: |
      SELECT * 
      FROM victor_test_dataset.people2
      WHERE {FILTER_PARAMS.people.age.filter('age')}
    data_source: default

    joins: []

    dimensions:
      - name: age
        sql: age
        type: number

      - name: firstname
        sql: firstname
        type: string

      - name: lastname
        sql: lastname
        type: string

      - name: region
        sql: region
        type: string
    
      - name: timestamp
        sql: timestamp
        type: time

    measures:
      - name: count
        type: count

      - name: min_age
        sql: age
        type: min

      - name: max_age
        sql: age
        type: max        

    pre_aggregations:
      - name: age_region_hourly
        type: rollup
        measures:
          - count
          - min_age
          - max_age
        dimensions:
          - age
          - region
          
        external: true
        scheduledRefresh: true
        time_dimension: timestamp
        granularity: hour
        refresh_key:
          every: 1 minute

Related Cube.js generated SQL

SELECT
  `people__region` `people__region`,
  sum(`people__count`) `people__count`,
  max(`people__max_age`) `people__max_age`,
  min(`people__min_age`) `people__min_age`
FROM
  dev_pre_aggregations.people_age_region_hourly AS `people__age_region_hourly`
WHERE
  (
    (`people__age` > ?)
    AND (`people__age` < ?)
  )
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT
  10000

[VictorAtTransmit]

answering myself:
external: true actually means that the pre aggregation tables will be stored in cube store, and the short lived tables I saw in BigQuery are actually just temp tables.

changing to external: false made it persist the pre aggregation tables in BQ without dropping them.

---

however, now I'm facing a different problem.
now, the stale pre aggregation tables are not being dropped at all, even long after refreshed tables created.
it just piles more and more pre aggregation tables in BigQuery.
I'm running a single "refresher" docker container with these additional env vars:

CUBEJS_CACHE_AND_QUEUE_DRIVER=memory

CUBEJS_DEV_MODE=false
CUBEJS_REFRESH_WORKER=true

here's my updated cube config:

cubes:
  - name: people
    sql: |
      SELECT * 
      FROM victor_test_dataset.people2
      WHERE {FILTER_PARAMS.people.age.filter('age')}
    data_source: default

    joins: []

    dimensions:
      - name: age
        sql: age
        type: number

      - name: firstname
        sql: firstname
        type: string

      - name: lastname
        sql: lastname
        type: string

      - name: region
        sql: region
        type: string
     
      - name: timestamp
        sql: timestamp
        type: time

    measures:
      - name: count
        type: count

      - name: min_age
        sql: age
        type: min

      - name: max_age
        sql: age
        type: max        

    pre_aggregations:
      - name: age_region_hourly
        type: rollup
        measures:
          - count
          - min_age
          - max_age
        dimensions:
          # - age
          - region
          
        scheduledRefresh: true
        time_dimension: timestamp
        granularity: day
        partition_granularity: year # I added this
        refresh_key:
          # every: 2 minutes
          sql: SELECT MAX(timestamp) FROM victor_test_dataset.people2
        external: false # I changed this to false to persist pre-aggregations in the default data source

relevant log:

{
    "message": "Dropping orphaned tables completed",
    "queryKey":
    [
        [
            "SELECT\n      `people`.region `people__region`, TIMESTAMP(DATETIME_TRUNC(TIMESTAMP(DATETIME(`people`.timestamp, 'UTC')), DAY)) `people__timestamp_day`, count(*) `people__count`, max(`people`.age) `people__max_age`, min(`people`.age) `people__min_age`\n    FROM\n      (SELECT * \nFROM victor_test_dataset.people2\nWHERE (1 = 1)\n) AS `people`  WHERE (`people`.timestamp >= TIMESTAMP(?) AND `people`.timestamp <= TIMESTAMP(?)) GROUP BY 1, 2",
            [
                "2024-01-01T00:00:00.000000Z",
                "2024-12-31T23:59:59.999999Z"
            ],
            {}
        ],
        [
            [
                {
                    "f0_": "2025-10-09T15:43:10.224Z"
                }
            ]
        ]
    ],
    "query": "SELECT\n      `people`.region `people__region`, TIMESTAMP(DATETIME_TRUNC(TIMESTAMP(DATETIME(`people`.timestamp, 'UTC')), DAY)) `people__timestamp_day`, count(*) `people__count`, max(`people`.age) `people__max_age`, min(`people`.age) `people__min_age`\n    FROM\n      (SELECT * \nFROM victor_test_dataset.people2\nWHERE (1 = 1)\n) AS `people`  WHERE (`people`.timestamp >= TIMESTAMP(?) AND `people`.timestamp <= TIMESTAMP(?)) GROUP BY 1, 2",
    "values":
    [
        "2024-01-01T00:00:00.000000Z",
        "2024-12-31T23:59:59.999999Z"
    ],
    "targetTableName": "prod_pre_aggregations.people_age_region_hourly20240101_fxigmg1s_yuqiod3u_1kefm1r",
    "requestId": "scheduler-f4737aa6-5785-4a45-a0b0-04bfc09e17b8",
    "newVersionEntry":
    {
        "table_name": "prod_pre_aggregations.people_age_region_hourly20240101",
        "structure_version": "yuqiod3u",
        "content_version": "fxigmg1s",
        "last_updated_at": 1760024635912,
        "naming_version": 2
    },
    "buildRangeEnd": "2024-12-31T23:59:59.999999",
    "external": false,
    "tablesToDrop": "[]"
}