Materializations now create temp tables as regular tables not as #tmp tables in tempdb

[robertkosz]

This issue is present in the latest version and have performance impact with large tables.

Previous versions of synapse connector were derived from sqlserver connector. Sqlserver connector defined temp relation as follows (up to version 1.4.x):

{% macro sqlserver__make_temp_relation(base_relation, suffix) %}
    {% set tmp_identifier = '#' ~  base_relation.identifier ~ suffix %}
    {% set tmp_relation = base_relation.incorporate(
                                path={"identifier": tmp_identifier}) -%}

    {% do return(tmp_relation) %}
{% endmacro %}

Now synapse connector (and sqlserver too) is derived from fabric, which defines temp relation like this:

{% macro fabric__make_temp_relation(base_relation, suffix='__dbt_temp') %}
    {%- set temp_identifier = base_relation.identifier ~ suffix -%}
    {%- set temp_relation = base_relation.incorporate(
                                path={"identifier": temp_identifier}) -%}

    {{ return(temp_relation) }}
{% endmacro %}

Synapse connector does not override this.
Fabric does not have temp tables but sqlserver and dedicated pool do have and it is better to use tempdb tables than permanent tables for transient operations. Funny enough synapse__get_columns_in_relation still uses the temp table hack workaround to get the column definitions for a table:
synapse__get_columns_in_relation

[joaofonsecapromptly]

Any updates on this? Is this an expected workflow or is there a fix upcoming to move back to #tmp relations?

Additionally, this creates a dependency on the fact that the user will need create view permission