[Feature] allow `incremental_predicates` to support self-referential subqueries

### Is this your first time submitting a feature request?

- [x] I have read the [expectations for open source contributors](https://docs.getdbt.com/docs/contributing/oss-expectations)
- [x] I have searched the existing issues, and I could not find an existing issue for this feature
- [x] I am requesting a straightforward extension of existing dbt functionality, rather than a Big Idea better suited to a discussion

### Describe the feature

As described in https://dev.to/superpayments/improve-dbt-incremental-performance-on-snowflake-using-custom-incremental-strategy-3ag3:

> #### Desired Solution
> To limit the data read in the target table above. We can make use of [incremental_predicates](https://docs.getdbt.com/docs/build/incremental-strategy#about-incremental_predicates) in the model config. This will add SQL to filter the target table.
>
> DBT model config:
>
> ```yaml
> - name: model_name
> config:
> materialized: "incremental"
> incremental_strategy: "delete+insert"
> on_schema_change: "append_new_columns"
> unique_key: ["dw_order_created_skey"]
> cluster_by: ["to_date(order_created_at)"]
> incremental_predicates:
> - "order_created_at >= (select dateadd(hour, -24, min(order_created_at)) from DBT_INTERNAL_SOURCE)"
> ```
>
> #### Issues with this
>
> The [incremental_predicates](https://docs.getdbt.com/docs/build/incremental-strategy#about-incremental_predicates) docs states _dbt does not check the syntax of the SQL statements_, so it does not change anything in the SQL.
> We get an error when it executes on snowflake: `Object 'DBT_INTERNAL_SOURCE' does not exist or not authorized.`
> We cannot hardcode the snowflake table name in the incremental_predicates, as its dynamically generated by DBT.
>

#### TLDR
[Super Payments](https://dev.to/superpayments) wanted to limit the incremental logic to only consider the last 24 hours, but [`incremental_predicates`](https://docs.getdbt.com/docs/build/incremental-strategy#about-incremental_predicates) doesn't support self-referential subqueries out-of-the-box, so they inserted their own string substitution logic in the middle by using a [custom incremental strategy](https://docs.getdbt.com/docs/build/incremental-strategy#custom-strategies) (see ["Solution Implementation" of their blog post for details](https://dev.to/superpayments/improve-dbt-incremental-performance-on-snowflake-using-custom-incremental-strategy-3ag3#solution-implementation)).

### Relevant code

If we were to implement something similar to [their custom strategy](https://dev.to/superpayments/improve-dbt-incremental-performance-on-snowflake-using-custom-incremental-strategy-3ag3#:~:text=macro%20get_incremental_custom_delete_insert_sql), here is the relevant code locations:
- [`get_incremental_delete_insert_sql`](https://github.com/dbt-labs/dbt-adapters/blob/95949407fbbfafa45d20eb34805e76ef4fc1b0ee/dbt-adapters/src/dbt/include/global_project/macros/materializations/models/incremental/strategies.sql#L15-L26)
- [`snowflake__get_delete_insert_merge_sql`](https://github.com/dbt-labs/dbt-adapters/blob/95949407fbbfafa45d20eb34805e76ef4fc1b0ee/dbt-snowflake/src/dbt/include/snowflake/macros/materializations/incremental/merge.sql#L35-L38)
- [`get_delete_insert_merge_sql`](https://github.com/dbt-labs/dbt-adapters/blob/95949407fbbfafa45d20eb34805e76ef4fc1b0ee/dbt-adapters/src/dbt/include/global_project/macros/materializations/models/incremental/merge.sql#L55-L89)