Add upstream to python models

[leo-schick]

What is the problem to be solved?

Currently, upstreams for python models are detected when calling dbt.ref(...) or dbt.source(...). These commands do not only set the upstream but return the dataframe object as well.

Now sometimes I use custom materialization in models e.g. to create a tabular function. These work well and create the required tabluar function. Now when I use the tabular function, I cannot use dbt.ref(...) because this returns the result of a table or view. When I try this, I get the following error:

AnalysisException: Table or view not found: dbt_dev.my_function_name;
10:41:58    'UnresolvedRelation [dbt_dev, my_function_name], [], false

I am able to use the SQL function by writing session.sql(" ... FROM dbt_dev.my_function_name") in the python model, but this does not add it as upstream.

So, I need a way to tell the python model that a specific dbt model (in my example the model which creates the SQL function) is upstream to the current model.

Possible solution

Add a new function which supports to add a specific model as upstream to the current model.

Example:

def model(dbt, session):
    dbt.add_upstream('my_custom_function')

    dbt.sql("INSERT INTO ... SELECT ... FROM dbt_dev.my_custom_function")

Or even better, add a function which resolves a model name and adds the model as upstream, but does not return the data frame (which does not exist for the SQL procedure).

Example:

def model(dbt, session):
    session.sql(f"INSERT INTO ... SELECT ... FROM {dbt.upstream_model('my_custom_function')}")

[jtcohen6]

@leo-schick Thanks for opening, and explaining the use case!

Your request sounds a lot like the one outlined here: #5995

[leo-schick]

Yes. Just usage looks a bit cumbersome. But I do not mind, it just needs to work out.

[leo-schick]

Here the definition for the proposed functions:

class dbtObj:
    def add_upstream(model_name: str) -> None:
        """
        Adds a model as upstream to the current python model

        Args:
            model_name:     The dbt model name to reference to.
        """
        raise NotImplementedError()

    def upstream_model(model_name: str) -> None:
        """
        Returns the database resolved reference to a model and adds it as upstream to the current python model.

        Args:
            model_name:     The dbt model name to reference to.
        """
        raise NotImplementedError()