Elementary question on how to write temporary BigQuery function as DBT macro

Question

I have some experience writing and using UDFs in the BigQuery editor, however I have little knowledge regarding their implementation as macros in DBT.

Concretely, I have in mind the following BQ UDF:

CREATE TEMP FUNCTION dtform(s STRING)
RETURNS DATE AS (DATE(TIMESTAMP(REPLACE(s, "Z", ""), "time_zone")));

required in order to readjust to the local timezone dates which are automatically set to UTC by the transfer tool I am using to transfer from the raw data source to the BQ warehouse.

My question is exactly how would I go about rendering this into a DBT macro? If anyone has the patience to answer in detail it will be very highly appreciated!

Answer 1

You can use the sql_header config in your model to declare the temp UDF:

{{ config(
    materialized="table",
    sql_header='''CREATE TEMP FUNCTION dtform(s STRING)
RETURNS DATE AS (DATE(TIMESTAMP(REPLACE(s, "Z", ""), "America/Sao_Paulo")));'''
) }}

SELECT dtform('') as parsed_date

If you want to abstract into a macro, take a look in this comment: https://github.com/dbt-labs/dbt-core/pull/1967#issuecomment-984108952