Trouble getting fresh data with renewQuery

[andry-brill]

Problem

In my app, merchants use the CubeJS REST API to enrich data with calculated fields, sorting, and grouping.

Use case

• Loading all merchants with renewQuery = false
• A user adds a new merchant (fast)
• A query is sent with renewQuery = true to fetch fresh data
• The old data is returned, without the newly added merchant

It might be overkill, but I’m considering running two separate Cube instances:

• one configured for non-cached data (scheduledRefreshTimer = 2 and refreshKeyRenewalThreshold = 2, with always generated new refreshKey)
• and another for cached data for 30 sec.

Do you think there’s a better approach?

What I tried

refreshKey returning a new value for each query:

cube(`Cube`, {
  sql: `merchant`,
  
  refreshKey: {
    sql: `SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) * 10) as refresh_key`
  }  
  
})

Cube.js log

I expected to see a new refresh_key being generated for each query with renewQuery = true...

cube-1  | --
cube-1  |   SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) * 10) as refresh_key
cube-1  | --
cube-1  | Performing query completed: 79368bba-8dea-4feb-9309-528d35a578f7-span-1 (5ms)
cube-1  | Performing query: 79368bba-8dea-4feb-9309-528d35a578f7-span-1 
cube-1  | Executing SQL: 79368bba-8dea-4feb-9309-528d35a578f7-span-1 
cube-1  | --
cube-1  |   SELECT
cube-1  |       "merchant".id "merchant__id", "merchant"....
cube-1  | --
cube-1  | Performing query completed: 79368bba-8dea-4feb-9309-528d35a578f7-span-1 (6ms)
cube-1  | Load Request Success: 79368bba-8dea-4feb-9309-528d35a578f7-span-1 (200ms)
cube-1  | --
cube-1  | {
cube-1  |   "renewQuery": true,
cube-1  |   "measures": [
cube-1  |     "merchant.id__count",
cube-1  |     "activityLine.totalPrice__sum",
cube-1  |     "activityLine.totalPriceInWalletCurrency__sum"
cube-1  |   ],
cube-1  |   "dimensions": [
cube-1  |     "merchant.id",
cube-1  |     "merchant.referenceDate"
cube-1  |   ],
cube-1  |   "segments": [],
cube-1  |   "timeDimensions": [],
cube-1  |   "filters": [],
cube-1  |   "limit": 30,
cube-1  |   "offset": 0,
cube-1  |   "order": {
cube-1  |     "merchant.referenceDate": "desc"
cube-1  |   },
cube-1  |   "timezone": "UTC"
cube-1  | }
cube-1  | --
cube-1  | Load Request Success: e8ce5058-ba04-451e-8539-85cde8d59f4c-span-1 (186ms)
cube-1  | --
cube-1  | {
cube-1  |   "renewQuery": true,
cube-1  |   "measures": [
cube-1  |     "merchant.id__count",
cube-1  |     "activityLine.totalPrice__sum",
cube-1  |     "activityLine.totalPriceInWalletCurrency__sum"
cube-1  |   ],
cube-1  |   "dimensions": [
cube-1  |     "merchant.id",
cube-1  |     "merchant.referenceDate"
cube-1  |   ],
cube-1  |   "segments": [],
cube-1  |   "timeDimensions": [],
cube-1  |   "filters": [],
cube-1  |   "limit": 30,
cube-1  |   "offset": 0,
cube-1  |   "order": {
cube-1  |     "merchant.referenceDate": "desc"
cube-1  |   },
cube-1  |   "timezone": "UTC"
cube-1  | }
cube-1  | --
cube-1  | Performing query: scheduler-be3f20e2-adbd-4f3d-837d-c49db89a4ac3 
cube-1  | Executing SQL: scheduler-be3f20e2-adbd-4f3d-837d-c49db89a4ac3 
cube-1  | --
cube-1  |   SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) * 10) as refresh_key
cube-1  | --
cube-1  | Performing query completed: scheduler-be3f20e2-adbd-4f3d-837d-c49db89a4ac3 (48ms)
cube-1  | Performing query: scheduler-fb5cf1a3-50a7-43d4-bf58-ae3b355570ef 
cube-1  | Executing SQL: scheduler-fb5cf1a3-50a7-43d4-bf58-ae3b355570ef 
cube-1  | --
cube-1  |   SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) * 10) as refresh_key
cube-1  | --
cube-1  | Performing query completed: scheduler-fb5cf1a3-50a7-43d4-bf58-ae3b355570ef (44ms)