[Feature] Allow to define a different schema for tmp tables created during table materialization

[pierrebzl]

Is this your first time submitting a feature request?

• I have searched the existing issues, and I could not find an existing issue for this feature

Describe the feature

Following dbt-labs/dbt-athena#613 and dbt-labs/dbt-athena#659. I would like to extend this feature to any tmp table used during table materialization operations.

By defining a custom target temp_schema as config arg of the model, user will be able to isolate where tmp tables used for table materialization are being created. This will allow better access controle on those transient state tables.

Describe alternatives you've considered

No response

Who will this benefit?

User that materialize tables and desire to controle where temporary tables are going to be created. This schema control will allow to limit exposure of transient state tables to consumers.

Are you interested in contributing this feature?

Yes

Anything else?

According to dbt-labs/dbt-athena#659 (comment), 3 main use cases need to be cover.
I believe this can be achieve by passing a temp_schema argument from the model configuration then to the api.Relation.create call here https://github.com/dbt-athena/dbt-athena/blob/v1.8.1/dbt/include/athena/macros/materializations/models/table/table.sql#L30.