cube-js / cube-js/cube

Cannot use use_original_sql_pre_aggregations on Postgres

Open
#7,421 5 comments 1 reaction 0 assignees View on GitHub
driver:postgres help wanted pre-aggregations
Dominant language
Rust
Stars
20.8k
Forks
2.1k
Avg merge
1d 2h
Merged PRs (30d)
181

Description

**Describe the bug**
We would like to reuse an original_sql pre-aggregation stored in Postgres in a rollup pre-aggregation. Setting `use_original_sql_pre_aggregations: true` fails with the following error message during scheduled refresh:
> "... FROM pre_agg_tenant_123.base_positions_main AS \"main__base_positions\"..."
{"message":"Refresh Scheduler Error","error":"Error: relation \"pre_agg_tenant_123.base_positions_main\" does not exist",

When I introspect the database, I can see that Cube appends certain hashes to the original_sql table name. Those hashes are missing in the sql query to the database, which causes it to fail.

**Expected behavior**
It should be possible to reuse the original_sql pre-aggregation stored in Postgres, as stated in the docs. We have a heavy sql query in the original data model.

**Minimally reproducible Cube Schema**
```yaml
cubes:
- name: base_positions
public: false
sql: SELECT "id_1" AS position_id, "test" AS position_name
measures:
- name: position_count
type: count
dimensions:
- name: position_id
sql: position_id
type: string
primary_key: true
- name: position_name
sql: position_name
type: string
- name: position_date
sql: "date"
type: time

pre_aggregations:
- name: main
type: original_sql
external: false
- name: positions_per_month
measures:
- CUBE.position_count
time_dimension: CUBE.position_date
granularity: month
use_original_sql_pre_aggregations: true
```

**Version:**
0.34.19

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.