add descriptions to fields in BigQuery using SQL

user_5609444 0
Viewed 1107

I would like to add descriptions to each field in a table. My issue is that we are using dbt and this tool recreates the table each time you run a job causing the descriptions to be deleted if they are there. I am able to control the datatypes casting the fields in the last SELECT statement but I am not sure if I can add the description using SQL.

I have been googling for a while and I am not able to see whether descriptions can be added using SQL this way.

I've thought a workaround which would be to create the table and then insert but this in theory is bad practice using dbt.

Thanks!

dbt google-bigquery sql
2 Answers

just want to post the solution in case someone has the same problem. dbt didn't update the descriptions in BQ itself. However, they released this new feature last month: https://github.com/fishtown-analytics/dbt/releases/tag/v0.17.0

Docs can be generated as usual and BQ will show the descriptions of tables and columns. You will only need to add the below to your dbt_project.yml file:

+persist_docs:
  relation: true
  columns: true
user_56094440

You can insert decriptions two ways.

Using the schema.yml file

{version: 2

models:
  - name: events
    description: This table contains clickstream events from the marketing website

    columns:
      - name: event_id
        description: This is a unique identifier for the event
        tests:
          - unique
          - not_null

      - name: user-id
        quote: true
        description: The user who performed the event
        tests:
          - not_null
}

You can also use jinga templating within the SQL.

{% docs table_events %}

This table contains clickstream events from the marketing website.

The events in this table are recorded by [Snowplow](http://github.com/snowplow/snowplow) and piped into the warehouse on an hourly basis. The following pages of the marketing site are tracked:
 - /
 - /about
 - /team
 - /contact-us

{% enddocs %}
user_107974280
Related