feat: Allow custom schema def for tmp tables generated by incremental

[pierrebzl]

Description

As mentioned in https://github.com/dbt-athena/dbt-athena/issues/613, I would like for all the temporary intermediate tables to be created in a different schema that we could specify as a config argument of the model.

This would enable us to control and limit access or visibility of those tmp tables. In some cases of parallel processing using unique_tmp_table_suffix flag, we end up creating a lot of those transient tables and we would like to avoid them to appear under the production/staging schemas of our glue catalog.

Models used to test - Optional

Checklist

• [X] You followed contributing section
• [X] You kept your Pull Request small and focused on a single feature or bug fix.
• [ ] You added unit testing when necessary
• [X] You added functional testing when necessary

[nicor88]

Few notes/consideration:

• the integration tests failed because of an issue with snapshots, I retriggered the ci, but if the issue persist must be addressed. Edit: the issue persist, please have a look
• I'm wondering if such property could apply to materialized tables. There are few cases where tmp tables are created:
  • in case of hive ha tables
  • in case we have more than 100 partitions
  • in case of iceberg the initial table is a tmp table, then renamed - that is the only case where there is an extra layer of complexity, because we need to guarantee that the final S3 layout is properly specified

[pierrebzl]

Thank you for your feedback.

I'm wondering if such property could apply to materialized tables Yes, I think it definitely could be by adjusting the table materialization macro. I was thinking to add this

{% set tmp_schema = config.get('tmp_schema') %}

{%- if tmp_schema is not none -%} {%- set tmp_schema = tmp_schema -%} {% else %} {%- set tmp_schema = schema -%} {%- endif -%}

then pass `schema=tmp_schema` to `api.Relation.create(` https://github.com/dbt-athena/dbt-athena/blob/v1.8.1/dbt/include/athena/macros/materializations/models/table/table.sql#L30
Maybe it can be part of a separate PR, what do you think?

I would need to explore more, I'm not very familiar with all the different use case of materialized table you mentioned.
Also this would require to write new tests.

[nicor88]

Let's keep table materialization out, and even consider to use another issue to track that idea :)

[nicor88]

@pierrebzl looks good, few nits, also let's consider to use temp_schema as model parameter for consistency with the code implementation, thanks