How to extract table comments in the db and populate schema description with it?

Help
, ,
1

I have existing table and column comments in my Redshift database for each table within a schema. I would like to extract this information and use it as source descriptions in dbt instead of manually inputting the details again. How can I achieve this?

I’m using dbt-core==1.4.9 dbt-redshift==1.4.0 and python 3.7.16

Example

This query is used to enter comments on table and column

comment on table actor is 'Actor table';
comment on column actor.first_name is 'This is the first name of actor';

Now I want to extract this to schema.yml so as to look like this

sources:
  - name: dbname
    description: actors database
    database: acting
    schema: act
    tables:
    # From here on, the documentation should be autogenerated by extracting from the db
      - name: actor
        description: Actor table
        columns:
          - name: first_name
            description: This is the first name of actor
2

Hi @sru741. I am brand new to dbt, but am looking to do something similar. Have you managed to implement this yet?

3

Hi @sru741,

You can create a macro that receives a table and/or a column name as arguments and executes a query that fetches the data you need, depending on those arguments. Then, you can use that macro in your docs:

sources:
  - name: dbname
    description: actors database
    database: acting
    schema: act
    tables:
    # From here on, the documentation should be autogenerated by extracting from the db
      - name: actor
        description: {{ get_description(table='actor') }}
        columns:
          - name: first_name
            description: {{ get_description(table='actor', column='first_name') }}