search

Velir / dbt-ga4

dbt Package for modeling raw data exported by Google Analytics 4. BigQuery support, only.
MIT License
312 stars 134 forks source link

Database Error in model dim_ga4__sessions_daily #229

Closed QingFyndiq closed 1 year ago

QingFyndiq commented 1 year ago

Hi, I have updated the package to the latest one but meet a new database error duet to resources exceeded.

03:32:07  Database Error in model dim_ga4__sessions_daily (models/marts/core/dim_ga4__sessions_daily.sql)
03:32:07    Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: 123% of limit.
03:32:07    Top memory consumer(s):
03:32:07      sort operations used for analytic OVER() clauses: 100%
03:32:07    
03:32:07    compiled Code at target/run/ga4/models/marts/core/dim_ga4__sessions_daily.sql

Is it possible to optimize the query instead of changing the database configuration?

adamribaudo-velir commented 1 year ago

Is this during the initial load or full refresh? Can you try to limit the start_date variable and see if it runs? If so, you may need to find a way to batch load all your data by day first and then use incremental runs moving forward.

Otherwise, you're welcome to take a look at the SQL and optimize further.

QingFyndiq commented 1 year ago

Is this during the initial load or full refresh? Can you try to limit the start_date variable and see if it runs? If so, you may need to find a way to batch load all your data by day first and then use incremental runs moving forward.

Otherwise, you're welcome to take a look at the SQL and optimize further.

Yes, I batch load previous data several times and then use incremental runs moving forward. So now it works, thanks for your recommendations.