Custom materialization that will decide is it table or view based on model name

Help
, , ,
1

The problem I’m having

In my custom materialization, I used source code from here dbt-adapters/dbt/include/global_project/macros/materializations/models/view.sql at main · dbt-labs/dbt-adapters · GitHub
but it works differently from the default run.

The context of why I’m trying to do this

I want materialization that will work for files that end with _v or _view and make them view.
So I decided to make one custom materialization that will make model a table, or view if it match these conditions.

What I’ve already tried

It works, but for some reason it makes 4 step process, instead of 1 as with default view materialization.

Some example code or error messages

Below is the code for my materialization, it consists of simple filter and then code for table or view that I get from you source code.

On image red is the regular dbt run -s view_name
But yellow is my new materialization.

Is this expected behavior for custom materializations? Do I need to change something? Why is it different, if I am using source code?

image
image720×150 52.9 KB

{% materialization table_and_view, default %}

  {%- set materialization_type = 'table' %}
  {% if model.name.endswith('_v') or model.name.endswith('_view') %}
    {% set materialization_type = 'view' %}
  {% endif %}

  {% if materialization_type == 'view' %}

    {%- set existing_relation = load_cached_relation(this) -%}
    {%- set target_relation = this.incorporate(type='view') -%}
    {%- set intermediate_relation =  make_intermediate_relation(target_relation) -%}

    -- the intermediate_relation should not already exist in the database; get_relation
    -- will return None in that case. Otherwise, we get a relation that we can drop
    -- later, before we try to use this name for the current operation
    {%- set preexisting_intermediate_relation = load_cached_relation(intermediate_relation) -%}
    /*
      This relation (probably) doesn't exist yet. If it does exist, it's a leftover from
      a previous run, and we're going to try to drop it immediately. At the end of this
      materialization, we're going to rename the "existing_relation" to this identifier,
      and then we're going to drop it. In order to make sure we run the correct one of:
        - drop view ...
        - drop table ...

      We need to set the type of this relation to be the type of the existing_relation, if it exists,
      or else "view" as a sane default if it does not. Note that if the existing_relation does not
      exist, then there is nothing to move out of the way and subsequentally drop. In that case,
      this relation will be effectively unused.
    */
    {%- set backup_relation_type = 'view' if existing_relation is none else existing_relation.type -%}
    {%- set backup_relation = make_backup_relation(target_relation, backup_relation_type) -%}
    -- as above, the backup_relation should not already exist
    {%- set preexisting_backup_relation = load_cached_relation(backup_relation) -%}
    -- grab current tables grants config for comparision later on
    {% set grant_config = config.get('grants') %}

    {{ run_hooks(pre_hooks, inside_transaction=False) }}

    -- drop the temp relations if they exist already in the database
    {{ drop_relation_if_exists(preexisting_intermediate_relation) }}
    {{ drop_relation_if_exists(preexisting_backup_relation) }}

    -- `BEGIN` happens here:
    {{ run_hooks(pre_hooks, inside_transaction=True) }}

    -- build model
    {% call statement('main') -%}
      {{ get_create_view_as_sql(intermediate_relation, sql) }}
    {%- endcall %}

    -- cleanup
    -- move the existing view out of the way
    {% if existing_relation is not none %}
      /* Do the equivalent of rename_if_exists. 'existing_relation' could have been dropped
          since the variable was first set. */
      {% set existing_relation = load_cached_relation(existing_relation) %}
      {% if existing_relation is not none %}
          {{ adapter.rename_relation(existing_relation, backup_relation) }}
      {% endif %}
    {% endif %}
    {{ adapter.rename_relation(intermediate_relation, target_relation) }}

    {% set should_revoke = should_revoke(existing_relation, full_refresh_mode=True) %}
    {% do apply_grants(target_relation, grant_config, should_revoke=should_revoke) %}

    {% do persist_docs(target_relation, model) %}

    {{ run_hooks(post_hooks, inside_transaction=True) }}

    {{ adapter.commit() }}

    {{ drop_relation_if_exists(backup_relation) }}

    {{ run_hooks(post_hooks, inside_transaction=False) }}

    {{ return({'relations': [target_relation]}) }}
  
  {% else %}

    {%- set existing_relation = load_cached_relation(this) -%}
    {%- set target_relation = this.incorporate(type='table') %}
    {%- set intermediate_relation =  make_intermediate_relation(target_relation) -%}
    -- the intermediate_relation should not already exist in the database; get_relation
    -- will return None in that case. Otherwise, we get a relation that we can drop
    -- later, before we try to use this name for the current operation
    {%- set preexisting_intermediate_relation = load_cached_relation(intermediate_relation) -%}
    /*
        See ../view/view.sql for more information about this relation.
    */
    {%- set backup_relation_type = 'table' if existing_relation is none else existing_relation.type -%}
    {%- set backup_relation = make_backup_relation(target_relation, backup_relation_type) -%}
    -- as above, the backup_relation should not already exist
    {%- set preexisting_backup_relation = load_cached_relation(backup_relation) -%}
    -- grab current tables grants config for comparision later on
    {% set grant_config = config.get('grants') %}

    -- drop the temp relations if they exist already in the database
    {{ drop_relation_if_exists(preexisting_intermediate_relation) }}
    {{ drop_relation_if_exists(preexisting_backup_relation) }}

    {{ run_hooks(pre_hooks, inside_transaction=False) }}

    -- `BEGIN` happens here:
    {{ run_hooks(pre_hooks, inside_transaction=True) }}

    -- build model
    {% call statement('main') -%}
      {{ get_create_table_as_sql(False, intermediate_relation, sql) }}
    {%- endcall %}

    -- cleanup
    {% if existing_relation is not none %}
      /* Do the equivalent of rename_if_exists. 'existing_relation' could have been dropped
          since the variable was first set. */
      {% set existing_relation = load_cached_relation(existing_relation) %}
      {% if existing_relation is not none %}
          {{ adapter.rename_relation(existing_relation, backup_relation) }}
      {% endif %}
    {% endif %}

    {{ adapter.rename_relation(intermediate_relation, target_relation) }}

    {% do create_indexes(target_relation) %}

    {{ run_hooks(post_hooks, inside_transaction=True) }}

    {% set should_revoke = should_revoke(existing_relation, full_refresh_mode=True) %}
    {% do apply_grants(target_relation, grant_config, should_revoke=should_revoke) %}

    {% do persist_docs(target_relation, model) %}

    -- `COMMIT` happens here
    {{ adapter.commit() }}

    -- finally, drop the existing/backup relation after the commit
    {{ drop_relation_if_exists(backup_relation) }}

    {{ run_hooks(post_hooks, inside_transaction=False) }}

    {{ return({'relations': [target_relation]}) }}
  {% endif %}

{% endmaterialization %}