Cube.js: Can I use time partitioning and incremental refreshKey for original SQL edit this page pre-aggregations

[devtothemoon]

Problem

I want to reduce the cost when the query in Google BigQuery. Can I use Time partitioning and incremental refresh key for Original SQL pre-aggregations

[Levhav]

Hi,
Yes, you can do it.
I will improve docs and send link to example in several days.

[Levhav]

Hi,
I prepared an example for you. I hope it will be useful.
If you have more questions let me know.

Original SQL pre-aggregation can be used with time partitioning and incremental refreshKey.

In this case, it can be used as follows:

cube(`Orders`, {
  sql: `select * from visitors WHERE ${FILTER_PARAMS.visitors.created_at.filter('created_at')}`,
  preAggregations: {
    main: {
      type: `originalSql`,
      timeDimensionReference: created_at,
      partitionGranularity: `month`,
      refreshKey: {
        every: `1 day`,
        incremental: true,
        updateWindow: `7 day`
      }
    }
  },
  dimensions: {
    id: {
      type: 'number',
      sql: 'id',
      primaryKey: true
    }, 
    created_at: {
      type: 'time',
      sql: 'created_at'
    },
  }
});