dbt の description を COMETA に反映させるチャレンジ

models:
  <resource-path>:
    +persist_docs:
      relation: true
      columns: true

select
    table_catalog as database,
    table_schema as schema,
    table_name as name,
    comment as description
from playground.information_schema.tables
where table_schema = 'PUBLIC_TEST';

select
    table_catalog as "database_name",
    table_schema as "schema_name",
    table_name as "table_name",
    comment as "logical_name",
    comment as "description"
from playground.information_schema.tables
where table_schema = 'PUBLIC_TEST';

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col
import pandas as pd
from functools import reduce

def main(session: snowpark.Session):
    data = []
    databases = ["SOURCE", "STAGING", "INTERMEDIATE", "MARTS"] # 自分でデータベースを選ぶ
    for database in databases:
        table = f"{database}.INFORMATION_SCHEMA.TABLES"
        table_df = session.table(table)[["TABLE_CATALOG", "TABLE_SCHEMA", "TABLE_NAME", "COMMENT"]].filter(col("TABLE_SCHEMA") != "INFORMATION_SCHEMA")
        df = table_df.with_column('"description"', table_df["COMMENT"])
        data.append(df)

    return reduce(snowpark.DataFrame.union, data).rename({
            col("TABLE_CATALOG"): '"database_name"',
            col("TABLE_SCHEMA"): '"schema_name"',
            col("TABLE_NAME"): '"table_name"',
            col("COMMENT"): '"logical_name"',
        })

{% docs test %}

# Iamque praecordiaque paratur hac haec cladibus

## Finierat timentia Phobetora visent pavens non feroxque

_Lorem_ markdownum lecta: iuvencos emeritis ima. Supremis quam quas _veteres_
annos tenet erat, inde distinxit modo limine **flammis**.

- hello
- world

### heading3

1. Madefactam ausus arsit tanges
2. Vulnere Tellus relinquam postquam exiguo
3. Incumbit ire faciem pariter suumque
4. Tuorum iam totis in non sed sceleri
5. Et est ipsi relinquar fortuna ignem lactentem
6. Peregrinum interea ille fecunda nunc serpens faciam

{% enddocs %}

version: 2

models:
  - name: my_first_dbt_model
    description: '{{ doc("test") }}'
    columns:
      - name: id
        description: "The primary key for this table"