Call a dataset persistent function with a ref

I’m migrating a view from bigquery and it uses a couple persistent functions written in javascript. Since they aren’t written in sql, I cannot use a macro to reproduce them, so I probably have to call them in my dbt model. Since I have to write my project_id and dataset_id in order to call the function, is there a way to add these as source and call the function using a ref, so the model respects modularity of sources?

Thank you!


  1. Define your function as source
  2. Use source() function in your dbt model, it compiles to the full object name in the database.


version: 2

  - name: function_source_name# this is the source_name
    database: function_project_id 
    schema: function_dataset_id
      - name: function_name # this is the your function_name


{{source('function_source_name','function_name ')}}(parm1,parm2) # Compiles to the full object name in the database

FROM table

Thanks for answering! Good to know that method works, but isn’t it a bit confusing to have functions and tables both in the same place treated as the same type of thing?

to avoid confusion create a separate source only for functions in your yml file

1 Like

I’m not familiar with BQ JS functions, but could you not also create a macro that just returns the name of the macro and the arguments, which would then be called as normal inside of your project? Something like this:

{% macro js_function(arg1, arg2) %}
    {{ this.database }}.{{this.schema}}.js_function({{ arg1 }}, {{ arg2 }})
{% endmacro %}

When you call it like this:

select col_1, 
{{ js_function('col_2', 'col_3') }}
from {{ ref('some_model') }}

it will render as

select col_1,
ANALYTICS_DEV.dbt_jlabes.js_function(col_2, col_3)
from ANALYTICS_DEV.dbt_jlabes.some_model

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.