Subquery containing correlated aggregate function can only appear in having or select clause

If you are just diving into doing incrementals using Snowflake as your DB of choice, here is a quick learning that I recently had (thank you @jerco for the assist).

If you copy and paste the example code from the incremental models page , then you are likely to get this error message.

The feedback from dbt is that snowflake is struggling with the potential recursive relationship here of a field having the same name both inside and outside of that correlated aggregate function.

the solve β†’ aliases

from :

where date_day >= (select max(date_day) from {{ this }})

to:

where *[main query table alias]*.date_day >= (select max(this.date_day) from {{ this }} as this)

Best of Luck !

7 Likes

That’s a tricky one! Thanks for sharing :star2:

This saved me! Thank you!