Create dynamic YML creation in DBT(BigQuery)

Question

I would like to create a dynamic YML file based on a source table.THe source table consists of JSON data and often the values inside those JSON can be different & my requirement is to put these changing JSON values as table columns, which I have done.So script-wise it is working fine with utils pivot function.

Issue is the generation of the YML file. With these changing column values in the model, Is it possible to create a YML file dynamically from the table? Means take the columns in the table and create the YML file(Not using python or any other language) using DBT.

Below is the structure of the YML file i'm looking for.

models:
  - name: orders
    description: '{{ doc("orders") }}'
    tests:
      - unique:
          column_name: "order_number"
    columns:
      - name: code
        description: '{{ doc("code") }}'
      - name: order_date
        description: '{{ doc("order_date") }}'

Below is my table name and the script which constructs the table. In the below script there is a table called events which have the un-nested JSON values & I'm pivoting it by below script, which is working fine.

So the values coming in name column is automatically pivoted(and there can be. alot of names in this column), so my challenge dis to create a YML file as above from the below script. The model name is order_details and I need to create the YML based on the columns in this model(without hardcoding the column names)

 select
        order_number,
        order_status_time,
        order_status,
        {{
            dbt_utils.pivot(
                "name",
                dbt_utils.get_column_values(
                        ref("events"),
                        "name"
                ),
                then_value="creation_time",
                else_value="NULL",
                agg="max"
            )
        }}
    from  {{ ref("events") }}
    {{ dbt_utils.group_by(3) }}

Answer 1

In order to create dynamic doc using only what DBT provides, my guess is that you should use codegen and especially the generate_model_yaml.

So you would have to execute :

dbt run-operation generate_model_yaml --args '{"model_names": ["orders"]}'

then if you want the file .yml to be automatically created with the "description" key filled with the name preceding it, i guess you don't have other choice than to do some text postprocessing.

Here is the previous command and some text postprocessing using gawk to pretty much achieve what you want :

dbt run-operation generate_model_yaml --args '{"model_names": ["orders"]}' \
| gawk '/version: 2/,0' \
| gawk 'match($0,/name: (.+)/,m) {name=m[1]} /description: ""/ {sub(/description: ""/, "description: \"{{ doc(\\\"" name "\\\") }}\""); print; next} 1' \
> orders.yml