# How to resolve "Subquery containing correlated aggregate function can only appear in having or select clause"

**URL:** <https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031>\
**Category:** Show and Tell\
**Tags:** incremental, snowflake\
**Created:** [October 7, 2022, 5:44pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031 "2022-10-07T17:44:19Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![ron.chipman](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ron.chipman/32/891_2.png) [@ron.chipman](https://discourse.getdbt.com/u/ron.chipman)\
**Post date:** [October 7, 2022, 5:44pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/1 "2022-10-07T17:44:19Z")

</div>

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](https://next.docs.getdbt.com/docs/build/incremental-models) , 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 :

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

```

to:

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

```

Best of Luck !

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [October 10, 2022, 3:25am UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/2 "2022-10-10T03:25:31Z")

</div>

That’s a tricky one! Thanks for sharing 🌟

---

<div class="post-metadata">

**Author:** ![aescay](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/aescay/32/584_2.png) [@aescay](https://discourse.getdbt.com/u/aescay)\
**Post date:** [November 24, 2022, 8:39am UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/3 "2022-11-24T08:39:58Z")

</div>

This saved me! Thank you!

---

<div class="post-metadata">

**Author:** ![Savage](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/savage/32/2592_2.png) [@Savage](https://discourse.getdbt.com/u/Savage)\
**Post date:** [June 22, 2023, 9:03pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/4 "2023-06-22T21:03:03Z")

</div>

I’m encountering this error exactly but having a bit of trouble implementing the solution. I’m newer to both incremental models and data modeling in general. Can you give an example with code for a full model?

Here’s my code:

```auto
{{ config (
      materialized = 'incremental'
  )
}}

with emails_raw as (
  select * from {{ source("SOURCE_SCHEMA", "EMAILS") }}
)

select  
  email_id, 
  created_at, 
  updated_at,
  email
from emails_raw 
{% if is_incremental() %}
  where emails_raw.created_at::date >= (select max(this.created_at::date) from {{ this }} as this) 
{% endif %}

```

I know something is wrong but I’m not sure exactly what it needs

---

<div class="post-metadata">

**Author:** ![joellabes](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/joellabes/32/3980_2.png) [@joellabes](https://discourse.getdbt.com/u/joellabes)\
**Post date:** [June 26, 2023, 5:06am UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/7 "2023-06-26T05:06:22Z")

</div>

@Savage this looks right! Is this the full version of the code that was giving you an error, or is it a cut-down version? The only thing I can see that’s different is the casting to dates, but I would be surprised to discover that’s the problem.

---

<div class="post-metadata">

**Author:** ![gbertimei](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/gbertimei/32/2789_2.png) [@gbertimei](https://discourse.getdbt.com/u/gbertimei)\
**Post date:** [July 27, 2023, 9:26am UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/8 "2023-07-27T09:26:29Z")

</div>

Had the same issue as @Savage - then I realised this is because I had on\_schema\_change= ‘append\_new\_columns’ at the top in the config, and LOAD\_DATE is something I added _after_ making the alias change, so it couldnt find the column and I would get an error “invalid identifier ‘THIS.LOAD\_DATE’”.  
so if anyone has this issue, first do a --full-refresh --select and then try again…

Code

{{  
config(  
materialized = ‘incremental’,  
tags = [‘PIPELINE\_WEEKLY’],  
on\_schema\_change= ‘append\_new\_columns’  
)  
}}

with final as (  
SELECT \*  
FROM {{ref(‘decile\_ranking’}}  
)

select load\_date, variable, rank  
from final

{% if is\_incremental() %}  
WHERE FINAL.LOAD\_DATE \> (  
SELECT MAX(THIS.LOAD\_DATE)  
FROM {{ this }} AS THIS  
)  
{% endif %}

---

<div class="post-metadata">

**Author:** ![mitch13](https://avatars.discourse-cdn.com/v4/letter/m/7ab992/32.png) [@mitch13](https://discourse.getdbt.com/u/mitch13)\
**Post date:** [July 28, 2023, 6:45pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/9 "2023-07-28T18:45:44Z")

</div>

I had the same issue and resolved it by doing this. It doesn’t like the MAX() aggregate, so just needed to use a different way.

```auto
            where
                (
                    {% if var("optional_start_date", default=None) is none %}
                        -- If optional_start_date is null, use the max date from the
                        -- existing model
                        last_modified > (
                            select last_modified
                            from {{ this }}
                            order by last_modified desc
                            limit 1
                        )

                    {% else %}
                        -- If optional_start_date is not null, use it as the lower
                        -- limit
                        last_modified >= '{{ var(' optional_start_date ') }}'::timestamp
                    {% endif %}
                    and {% if var("optonal_end_date", default=None) is none %}
                        -- If optonal_end_date is null, there is no upper limit
                        true
                    {% else %}
                        -- If optonal_end_date is not null, use it as the upper limit
                        last_modified < '{{ var(' optonal_end_date ') }}'::timestamp
                    {% endif %}
                )
        {% endif %}

```

---

<div class="post-metadata">

**Author:** ![Melanie](https://avatars.discourse-cdn.com/v4/letter/m/35a633/32.png) [@Melanie](https://discourse.getdbt.com/u/Melanie)\
**Post date:** [November 24, 2023, 1:19pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/11 "2023-11-24T13:19:59Z")

</div>

Hello,  
I am new to dbt applied to Snowflake, I feel like I am encountering a somewhat similar problem but what I do not understand for the moment is the link between the field of the incremental filter condition.  
I have an error similar to the one in the topic name of this forum.

> Subquery containing correlated aggregate function [MAX(EVENTS.TIMESTAMP)] can only appear in having or select clause

I feel more that my problem comes from ending my SQL query on a transformation of the field in question by encapsulating it in a DATE function for daily needs.  
I will continue reading the documentation.  
If it’s something obvious and you have the answer to, I’m all ears.

> {{  
> config(  
> materialized=‘incremental’,  
> unique\_key=[‘date’, ‘token’]  
> )  
> }}

> WITH session\_events\_aggregation AS (  
> SELECT  
> token,  
> timestamp,  
> [COUNT, SUM stuff …]  
> FROM {{ ref(‘events’) }}  
> WHERE type IN (‘extension:open’, ‘extension:close’)  
> {% if is\_incremental() %} ⟶ I would like to put it here so it’s filtered as early as it should  
> AND timestamp \>= (select max(timestamp) from {{ this }})  
> {% endif %}  
> ORDER BY timestamp  
> ),

> Other subquery,

> SELECT  
> DATE(timestamp) as date  
>   
> GROUP BY […]

I’ve tried

> AND session\_events\_aggregation.timestamp \>= (select max(this.timestamp) from {{ this }} as this)  
> Got invalid identifier ‘SESSION\_EVENTS\_AGGREGATION.TIMESTAMP’

But the query works with

> AND timestamp \>= (select max(timestamp) from {{ this }})

only if I remove the DATE(timestamp) in the last SELECT

---

<div class="post-metadata">

**Author:** ![ananthpillai](https://avatars.discourse-cdn.com/v4/letter/a/a9a28c/32.png) [@ananthpillai](https://discourse.getdbt.com/u/ananthpillai)\
**Post date:** [December 8, 2023, 1:01pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/12 "2023-12-08T13:01:27Z")

</div>

Thanks for Sharing this.  
Ananth

---

<div class="post-metadata">

**Author:** ![sactl](https://avatars.discourse-cdn.com/v4/letter/s/e19adc/32.png) [@sactl](https://discourse.getdbt.com/u/sactl)\
**Post date:** [December 12, 2023, 9:47am UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/13 "2023-12-12T09:47:09Z")

</div>

Hello guys,

I have the same problem as mentionned. error: Subquery containing correlated aggregate function [MAX(INTER\_ORDERED\_PRODUCT.ORDERDATE)] can only appear in having or select clause

I have tried the solution that the community propose but still i encounter an error.

Context i’m using snowflake and dbt cloud.

best,

here what i did:  
{{ config(schema=‘Marts’) }}  
{{ config(materialized=“incremental”) }}

with mart\_ordered\_product as (  
select \* from {{ref (“inter\_ordered\_product”)}}  
),

final as (  
select  
orderedkey,  
partkey,  
suppkey,  
linenumber,  
quantity,  
extendedprice,  
discount,  
tax,  
returnflag,  
linestatus,  
shipdate,  
commitdate,  
receitdate,  
shipinstruct,  
shipmode,  
li\_comment,  
customer\_key,  
orderstatus,  
totalprice,  
orderdate,  
orderpriority,  
clerk,  
shipriority,  
o\_comment  
from mart\_ordered\_product  
{%if is\_incremental() %}  
where mart\_ordered\_product.orderdate \>= (select max(this.orderdate) from {{this}} as this)  
{% endif%}  
)

select \* from final

---

<div class="post-metadata">

**Author:** ![alfonso777](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/alfonso777/32/3383_2.png) [@alfonso777](https://discourse.getdbt.com/u/alfonso777)\
**Post date:** [January 11, 2024, 11:09pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/14 "2024-01-11T23:09:09Z")

</div>

> [@Savage](#):
>
> ` where emails_raw.created_at::date >= (select max(this.created_at::date) from {{ this }} as this)`

Two ways:

- Use a alias to emails\_raw, e.g. `er` : `er.created_at::date >=`
- Remove table name prefix: `created_at::date >=`

---

<div class="post-metadata">

**Author:** ![Miriam](https://avatars.discourse-cdn.com/v4/letter/m/c89c15/32.png) [@Miriam](https://discourse.getdbt.com/u/Miriam)\
**Post date:** [June 4, 2024, 1:50pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/15 "2024-06-04T13:50:28Z")

</div>

Hi!

I been experiencing the same issue and I found a simpler solution 🙂

This is my use case:

```auto
{{
    config(
        materialized="incremental",
        unique_key="primary_key",
        sort="column_c",
}}

with source as (
    select * from {{ source('database', 'schema_name') }}
    {{ limit_data_incremental('column_c') }}
),

transformed as (
    select 
        column_a,
        column_b,
        column_c::datetime
    from source
)

select *
from transformed

```

For simplicity, I have encapsulated the `is_incremental()` macro within `limit_data_incremental`. For more info about `is_incremental()` please visit this [doc](https://next.docs.getdbt.com/docs/build/incremental-models#understand-the-is_incremental-macro).

Aliasing the column after casting its data type resolved the issue for me, so I didn’t need to modify the macro.

```auto
transformed as (
    select 
        column_a,
        column_b,
        column_c::datetime as column_c -- This is what I changed
    from source
)

```

Hope this helps 😁

---

<div class="post-metadata">

**Author:** ![Priest](https://avatars.discourse-cdn.com/v4/letter/p/9de0a6/32.png) [@Priest](https://discourse.getdbt.com/u/Priest)\
**Post date:** [February 21, 2025, 11:51pm UTC](https://discourse.getdbt.com/t/how-to-resolve-subquery-containing-correlated-aggregate-function-can-only-appear-in-having-or-select-clause/5031/16 "2025-02-21T23:51:37Z")

</div>

Getting this error:  
000904 (42000): SQL compilation error: error line 40 at position 50  
invalid identifier ‘THIS.dbt\_UPDATED\_AT’

Used:

```auto
{% if is_incremental() %}
    where main.DBT_UPDATED_AT::date > (select max(this.DBT_UPDATED_AT::date) from {{ this }} as this)
{% endif %}

```

Context:  
Source table here is a snapshot table.