Indexing external pre-aggregation table doesn't works for timeDimensionReference

[contxtsio-jetson]

Describe the bug
I'd tried to create indexes for columns including timeDimensionReference and I found the timeDimensionReference get suffix when the pre-aggregation table is created.

Schema:

wk: {
  type: `rollup`,
  measureReferences: [m_exchange_rate],
  dimensionReferences: [
    token_address,
    currency,
    vs_currency,
    exchange_rate,
  ],
  timeDimensionReference: block_date,
  granularity: `week`,
  scheduledRefresh: !isPerUser,
  external: true,
  indexes: {
    cidx_token_address_vs_currency_block_date: {
      // FIXME: Jetson
      columns: [token_address, vs_currency, block_date],
    },
  },
},

Error log:

{
  "processingId": "21",
  "queueSize": 1,
  "queryKey": [
    [
      "SELECT\n      `erc20`.currency `erc20__currency`, `erc20`.exchange_rate `erc20__exchange_rate`, `erc20`.token_address `erc20__token_address`, `erc20`.vs_currency `erc20__vs_currency`, DATETIME_TRUNC(DATETIME(cast(block_date as timestamp), 'UTC'), WEEK(MONDAY)) `erc20__block_date_week`, avg(exchange_rate) `erc20__m_exchange_rate`\n    FROM\n      (\n    select\n      token_address,\n      currency,\n      lower(vs_currency) as vs_currency,\n      block_date,\n      exchange_rate,\n    from `contxtsio-nftbank.data_mart_by_game.erc20_exchange_rates`\n    order by token_address, vs_currency, block_date\n  ) AS `erc20`  GROUP BY 1, 2, 3, 4, 5",
      []
    ],
    [
      [
        {
          "f0_": "2021-01-14T14:22:29.660Z"
        }
      ]
    ]
  ],
  "queuePrefix": "SQL_PRE_AGGREGATIONS_cubejs-serverless-dev_default",
  "timeInQueue": 2
}
error: column "erc20__block_date" does not exist
    at Parser.parseErrorMessage (/Users/jisunglim/development/contxts-io/nftbank-cubejs-serverless/example/node_modules/pg-protocol/src/parser.ts:357:11)
    at Parser.handlePacket (/Users/jisunglim/development/contxts-io/nftbank-cubejs-serverless/example/node_modules/pg-protocol/src/parser.ts:186:21)
    at Parser.parse (/Users/jisunglim/development/contxts-io/nftbank-cubejs-serverless/example/node_modules/pg-protocol/src/parser.ts:101:30)
    at Socket.stream.on (/Users/jisunglim/development/contxts-io/nftbank-cubejs-serverless/example/node_modules/pg-protocol/src/index.ts:7:48)

Expected behavior
It should create an index for the column no matter it is timeDimension or simple dimension.

Version:

"@cubejs-backend/bigquery-driver": "^0.25.4",
"@cubejs-backend/postgres-driver": "^0.25.4",
"@cubejs-backend/server": "^0.25.4",
"@cubejs-backend/serverless": "^0.25.4",
"@cubejs-backend/serverless-google": "^0.25.4",

Additional context
Using bigquery for data source, postgres for external pre-aggregation db. Deployed on GCP cloud function.

[contxtsio-jetson]

I just fixed it with an ad-hoc solution.

wk: {
  type: `rollup`,
  measureReferences: [m_exchange_rate],
  dimensionReferences: [
    token_address,
    currency,
    vs_currency,
    exchange_rate,
  ],
  timeDimensionReference: block_date,
  granularity: `week`,
  scheduledRefresh: !isPerUser,
  external: true,
  indexes: {
    cidx_token_address_vs_currency_block_date: {
      // FIXME: 
      // issue: https://github.com/cube-js/cube.js/issues/1781
      // columns: [token_address, vs_currency, block_date],
      columns: [token_address, vs_currency, "erc20__block_date_week"],
    },
  },
},

[paveltiunov]

@contxtsio-jetson Hey Jetson! Thanks for posting this! Yep. It seems this case isn't supported right now.

[github-actions]

If you are interested in working on this issue, please leave a comment below and we will be happy to assign the issue to you.
If this is the first time you are contributing a Pull Request to Cube.js, please check our contribution guidelines.
You can also post any questions while contributing in the #contributors channel in the Cube.js Slack.

[igorlukanin]

Hi @contxtsio-jetson 👋

I believe this is more like a known issue rather than a bug and it has been since documented: https://cube.dev/docs/reference/data-model/pre-aggregations#indexes

Let me quote the docs:

In the case that you want to reference the time dimension in the index, the column name as a string in the following format can be used: <cube name>__<time dimension name>_<granularity>.

Here's a code example that defines the same pre-aggregation as above:

cube(`indices`, {
  sql: `
    SELECT 123 AS value, '2024-01-01T00:00:000Z' AS time
  `,

  dimensions: {
    token_address: {
      sql: `value`,
      type: `number`
    },


    currency: {
      sql: `value`,
      type: `number`
    },


    vs_currency: {
      sql: `value`,
      type: `number`
    },

    exchange_rate: {
      sql: `value`,
      type: `number`
    },

    block_date: {
      sql: `time`,
      type: `time`
    }
  },

  measures: {
    m_exchange_rate: {
      type: `count`
    }
  },

  pre_aggregations: {
    wk: {
      type: `rollup`,
      measures: [m_exchange_rate],
      dimensions: [
        token_address,
        currency,
        vs_currency,
        exchange_rate,
      ],
      timeDimension: block_date,
      granularity: `week`,
      indexes: {
        cidx_token_address_vs_currency_block_date: {
          columns: [
            token_address,
            vs_currency,
            'indices__block_date_week'
          ],
        },
      },
    },
  }
})

As you can see, it builds and the index builds as well:

Using bigquery for data source, postgres for external pre-aggregation db.

Lastly, let me note that using external databases for pre-aggregations has been deprecated. It's recommended to use Cube Store for that purpose.