如何通过SQL自动将PostgreSQL列注释同步至dbt文档

Great question! Keeping your dbt docs in sync with PostgreSQL’s column comments eliminates redundant work and ensures consistency across your data stack. Here’s a practical, SQL-focused way to automate this:

Step 1: Extract PostgreSQL Comments with SQL #

First, write a query to pull all table and column comments from your PostgreSQL database. We’ll use the information_schema views for this—they’re standardized and easier to work with than raw system tables:

SELECT
    c.table_schema AS schema_name,
    c.table_name,
    c.column_name,
    -- Fallback to a default if no comment exists
    COALESCE(c.column_comment, 'No description provided') AS column_description,
    COALESCE(t.table_comment, 'No description provided') AS table_description
FROM
    information_schema.columns c
INNER JOIN
    information_schema.tables t
    ON c.table_schema = t.table_schema
    AND c.table_name = t.table_name
WHERE
    t.table_type = 'BASE TABLE' -- Exclude views if you don’t need them; remove to include
    AND c.table_schema NOT IN ('information_schema', 'pg_catalog') -- Skip system schemas
ORDER BY
    c.table_schema, c.table_name, c.ordinal_position;

Save this as a dbt model (e.g., models/util/stg_column_descriptions.sql). Run dbt run to materialize it—this gives us a structured dataset of all our table and column comments.

Step 2: Generate dbt’s Schema YAML with a Macro #

dbt relies on YAML files (typically schema.yml) to power its documentation. We’ll create a dbt macro that reads our stg_column_descriptions model and auto-generates this YAML for us.

Create a macro file (e.g., macros/generate_dbt_schema_yml.sql) with this Jinja-SQL code:

{% macro generate_dbt_schema_yml() %}
{% set comment_query %}
SELECT
    schema_name,
    table_name,
    table_description,
    -- Aggregate columns into a JSON array for easy looping
    array_agg(
        json_build_object(
            'name', column_name,
            'description', column_description
        )
    ) AS columns
FROM {{ ref('stg_column_descriptions') }}
GROUP BY schema_name, table_name, table_description
{% endset %}

{% set results = run_query(comment_query) %}

{% if execute %}
{% set schema_metadata = results.rows %}
{% else %}
{% set schema_metadata = [] %}
{% endif %}

version: 2

{% for entry in schema_metadata %}
models:
  - name: {{ entry.table_name }}
    schema: {{ entry.schema_name }}
    description: "{{ entry.table_description | replace('"', '\\"') }}"
    columns:
      {% for column in entry.columns %}
      - name: {{ column.name }}
        description: "{{ column.description | replace('"', '\\"') }}"
      {% endfor %}
{% endfor %}
{% endmacro %}

How to Use the Macro #

Run this terminal command to generate (or overwrite) your schema.yml file:

dbt run-operation generate_dbt_schema_yml > models/schema.yml

The macro handles escaping double quotes in comments (so your YAML stays valid) and structures everything exactly how dbt expects it.

Step 3: Refresh Docs and Maintain Sync #

Whenever you update comments in PostgreSQL:

1. Re-run dbt run to refresh the stg_column_descriptions model
2. Re-run the dbt run-operation command to update your YAML
3. Run dbt docs generate && dbt docs serve to view the updated documentation

Pro Tips #

• If you only need comments for specific schemas, add AND c.table_schema = 'your_target_schema' to the initial SQL query.
• To include views, remove the t.table_type = 'BASE TABLE' condition from the WHERE clause.
• For large databases, filter to only tables you’re modeling in dbt to keep the YAML clean and focused.

内容的提问来源于stack exchange，提问作者Mark