cube-js / cube-js/cube

Pre-aggregations don't respect `offset: start` in rolling window measures

Open
#8,580 0 comments 0 reactions 1 assignee Claimed by @paveltiunov View on GitHub
data modeling pre-aggregations
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
Cube returns different results when a rolling window measure with `offset` set to `start` is served directly from the data source vs. from a pre-aggregation. Looks like `offset` is ignored by pre-aggregations.

**To Reproduce**
Steps to reproduce the behavior:
1. Use the following data model:
```javascript
cube(`order_rolling`, {
sql: `
SELECT 10 AS value, '2023-11-01'::TIMESTAMP AS date UNION ALL
SELECT 10 AS value, '2023-12-01'::TIMESTAMP AS date UNION ALL
SELECT 10 AS value, '2024-01-01'::TIMESTAMP AS date UNION ALL
SELECT 30 AS value, '2024-02-01'::TIMESTAMP AS date UNION ALL
SELECT 50 AS value, '2024-03-01'::TIMESTAMP AS date UNION ALL
SELECT 80 AS value, '2024-04-01'::TIMESTAMP AS date
`,
dimensions: {
date: {
sql: `date`,
type: `time`
},
},
measures: {
current_month_sum: {
sql: `value`,
type: `sum`,
rolling_window: {
trailing: `3 month`,
offset: `start`
}
},
},
// preAggregations: {
// main: {
// measures: [order_rolling.current_month_sum],
// timeDimension: order_rolling.date,
// granularity: `day`,
// refresh_key: {
// every: `1 week`,
// },
// }
// }
});
```
2. Run the following query:
```json
{
"limit": 5000,
"timeDimensions": [
{
"dimension": "order_rolling.date",
"granularity": "month",
"dateRange": [
"2023-11-01",
"2024-07-01"
]
}
],
"measures": [
"order_rolling.current_month_sum"
]
}
```
3. Observe the result
4. Uncomment the pre-aggregation
5. Run the same query
6. Compare the result with the initial one, they will be different.

**Expected behavior**
With a pre-aggregation or not, the result should be the same.

**Screenshots**
No pre-aggregation:
Screenshot 2024-08-14 at 11 56 06

Pre-aggregation:
Screenshot 2024-08-14 at 11 56 47

**Version:**
0.35.69

**Additional context**
Reported in Slack: https://cube-js.slack.com/archives/C04KDTE2EF7/p1722975927331859

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.