Support Serverless Pool - change to macro sqlserver__rename_relation

[leo-schick]

Currently the Serverless Pool is not supported by this module, because the RENAME command is not supported, which is in use in macro sqlserver__rename_relation.

I found an alternative way to rename e.g. a SQL view by running

DECLARE @sql nvarchar(max);
SET @sql = replace(object_definition (object_id ('"dbt"."my_model__dbt_tmp"')), '"dbt"."my_model__dbt_tmp"', '"dbt"."my_model"');
EXEC('DROP VIEW "dbt"."my_model__dbt_tmp" }}')
EXEC('DROP VIEW IF EXISTS "dbt"."my_model" }}')
EXEC(@sql)

This only supports SQL VIEWs, but that is all what you will probably be doing. (ok, maybe external tables... but I don't know if dbt supports them).

I made my dbt project now work by using the macro:

{% macro synapse__rename_relation(from_relation, to_relation) -%}
  {% call statement('rename_relation') -%}

--    rename object {{ from_relation.include(database=False) }} to {{ to_relation.identifier }}

--  Azure Synapse Serverless Pool does not support RENAME, so we make this here manual:
    DECLARE @sql nvarchar(max);
    SET @sql = replace(object_definition (object_id ('{{ from_relation.include(database=False) }}')), '{{ from_relation.include(database=False) }}', '{{ to_relation.include(database=False) }}');
    EXEC('DROP VIEW {{ from_relation.include(database=False) }}')
    EXEC('DROP VIEW IF EXISTS {{ to_relation.include(database=False) }}')
    EXEC(@sql)

  {%- endcall %}
{% endmacro %}

Does someone have an idea how to implement this into package dbt-synapse?

[leo-schick]

Maybe by detecting if the script runs in a SQL serverless pool or not. I couldn't figure out how this is possible in TSQL.

[dataders]

@leo-schick what a clever workaround! honestly, implementing this macro in this repo would be a great first step tbh