can't rename views in Azure Synapse Serverless

[dataders]

background

In Azure Synapse serverless you cannot:

• materialize tables only external tables and views, and
• rename views (because sp_rename and RENAME is not supported)

You can, however, do this for dedicated SQL pools, which is what most teams use.

question

@jtcohen6 is it possible to use a project-level boolean variable that will conditionally switch between using the default view materialization and the custom one below?

In my head I'm envisioning a custom materialization that would conditionally dispatch to either the dbt-core's default view materialization and this hacky, drop-and-recreate view materialization below based on a is_serverless variable that defaults to False.

-- override for view.sql

{% materialization view, adapter='synapse' %}
  {%- set identifier = model['alias'] -%}
  {%- set old_relation = adapter.get_relation(database=database,
                                              schema=schema,
                                              identifier=identifier) -%}
  {%- set target_relation = api.Relation.create(database=database,
                                                schema=schema,
                                                identifier=identifier,
                                                type='view') -%}
  {%- set exists_as_table = (old_relation is not none and old_relation.is_table) -%}
  {%- set exists_as_view = (old_relation is not none and old_relation.is_view) -%}

  {{ run_hooks(pre_hooks) }}

  {% if old_relation is not none %}
    {% do adapter.drop_relation(old_relation) %}
  {% endif %}

  -- build model
  {% call statement('main') -%}
    {{ create_view_as(target_relation, sql) }}
  {%- endcall %}
  {{ run_hooks(post_hooks) }}

  {% do persist_docs(target_relation, model) %}
  {{ return({'relations': [target_relation]}) }}
{% endmaterialization %}

[jtcohen6]

@swanderz All properties of the currently active connection profile are available from the target context variable.

I also believe it's possible to "call" a materialization as a macro, and thereby avoid duplicating logic. dbt turns materializations into macros with names like materialization_view_default. That's not exactly documented, though, or guaranteed to continue working the same in the future.

For clarity, anyways, I'd lean toward including both logical paths, with clear branching logic:

{% materialization view, adapter='synapse' %}
     ... common setup ...
  {% if target.is_serverless %}
     ... hacky drop and recreate ...
  {% else %}
     ... standard rename-swap-drop ...
{% endmaterialization %}