How to call a model from a macro

Help
1

The problem I’m having

I’d like to execute a particular model in a macro. Something along this way:

{%- macro foobar(model_name, target_name) -%}

dbt run -s {{ model_name }} -t {{ target_name }}

{%- endmacro -%}

The context of why I’m trying to do this

We want to help others running models safely in a dev schema. However, some models have many large upstream dependencies. Therefore, I am writing a macro which, given a model name I want to refresh, copies all production tables and views, basically all upstream models, into a dev schema.

I am able to get all the upstream models. Copying tables is also straightforward (create table dev_john.big_table as select * from prod_schema.big_table), however there is no such thing for a view.

Therefore, I am looking for a way to run models which are views from macro.

(I know about --defer and we use it, but we do not want to use it in this case as it sometimes leads to annoying transaction blocks and problems)

What I’ve already tried

The only thing I came up is to just create view dev_john.my_view as select * from prod_schema.my_view with no schema binding. But I’d rather to do dbt run -s my_view -t dev_john to really have no dependencies between prod and dev.

Any ideas would be greatly appreciated.
Thank you.

2

Not a direct answer, but would the “upstream-prod” package help you? https://hub.getdbt.com/lewisdavies/upstream_prod/latest/

Note: @Owen originally posted this reply in Slack. It might not have transferred perfectly.

3

Thanks! I saw that one. From what I understand, this is basically --defer without the artefacts.

It is close, but it reads production tables, while I need complete isolation. That is, having the prod tables in dev.

4

Any update on above requirement?i would like to run dbt model using macro

5

For myself, I simply get all the model info from dbt artifacts and then iterate over them like that:

{%- for upstream_model, configs in model_properties.items() -%}

      {{ dbt_utils.log_info("drop table if exists " ~ dev_schema ~ "." ~ configs['alias'] ~ " cascade;" ) }}
      {% do run_query("drop table if exists " ~ dev_schema ~ "." ~ configs['alias'] ~ " cascade;" ) %}

      {{ dbt_utils.log_info("begin; create table " ~ dev_schema ~ "." ~ configs['alias'] ~ " as select * from " ~ configs['schema'] ~ "." ~ configs['alias'] ~ " " ~ limit ~ "; commit;") }}
      {% do run_query("begin; create table " ~ dev_schema ~ "." ~ configs['alias'] ~ " as select * from " ~ configs['schema'] ~ "." ~ configs['alias'] ~ " " ~ limit ~ "; commit;") %}

Not perfect, but it works.
For my use-case, there is also now dbt clone which could be useful.
However, haven’t found any way to run a model by dbt run -s foo -t prod in a macro.