Referencing BigQuery UDF in dbt

Question

With BigQuery and dbt, we can reference existing tables using sources , how can I do to reference an existing Standard SQL UDF ?

# schema.yml

sources:
  - name: dataset
    tables:
      - name: table1
      - name: table2

/* view.sql */

SELECT * FROM {{ source('dataset', 'table1') }}

Thank you.

Ref: https://docs.getdbt.com/docs/building-a-dbt-project/using-sources

Have you tried to call it directly from your models ?? just consider it as any other persistent UDFs — PacMan
The aim is to track dependencies like in macros, and to be able to tell the documentation the link exists. — Michel Hua

Cylldby Cylldby · Accepted Answer · 2021-05-13T07:11:40

Browsing the dbt discourse you can come across this post

This shows a way to create UDFs (and therefore manage versions) from DBT. But this is not your question...

In order to access from DBT to information about your UDFs you can query the INFORMATION_SCHEMA (in a specific dataset or in a region, see here):

 SELECT * FROM myDataset.INFORMATION_SCHEMA.ROUTINES;

Since your objective seems to be able to manage your UDFs, I suppose you can import them through this query, incorporate them into your DBT project as macros (hence adding them to your documentation) and then control everything from the DBT side.

Referencing BigQuery UDF in dbt

1 Answers