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

**URL:** <https://discourse.getdbt.com/t/custom-materialization-that-will-decide-is-it-table-or-view-based-on-model-name/15484>\
**Category:** Help\
**Tags:** jinja, snowflake, macros, dbt-core\
**Created:** [September 17, 2024, 6:59pm UTC](https://discourse.getdbt.com/t/custom-materialization-that-will-decide-is-it-table-or-view-based-on-model-name/15484 "2024-09-17T18:59:50Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![hopelesslord](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/hopelesslord/32/4583_2.png) [@hopelesslord](https://discourse.getdbt.com/u/hopelesslord)\
**Post date:** [September 17, 2024, 6:59pm UTC](https://discourse.getdbt.com/t/custom-materialization-that-will-decide-is-it-table-or-view-based-on-model-name/15484/1 "2024-09-17T18:59:50Z")

</div>

## The problem I’m having

In my custom materialization, I used source code from here [https://github.com/dbt-labs/dbt-adapters/blob/main/dbt/include/global\_project/macros/materializations/models/view.sql](https://github.com/dbt-labs/dbt-adapters/blob/main/dbt/include/global_project/macros/materializations/models/view.sql)  
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](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/2X/8/832d47691e09c3694033fc0471ef733c9ed78396.png)

```auto
{% 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 %}

```