dbt-covesを用いてdbt modelファイルの生成

uv add dbt-coves==1.10.5

[tool.uv]
override-dependencies = [
    "pydantic>=2.7"
]

.
├── dbt_project.yml
├── error.log
├── Makefile
├── models
│   └── blog
│       ├── sources
│       │   ├── _searchconsole__exportlog.yml
│       │   ├── _searchconsole__searchdata_site_impression.yml
│       │   └── _searchconsole__searchdata_url_impression.yml
│       └── staging
│           ├── stg_searchconsole__exportlog.sql
│           ├── stg_searchconsole__exportlog.yml
│           ├── stg_searchconsole__searchdata_site_impression.sql
│           ├── stg_searchconsole__searchdata_site_impression.yml
│           ├── stg_searchconsole__searchdata_url_impression.sql
│           └── stg_searchconsole__searchdata_url_impression.yml
├── package-lock.yml
├── packages.yml
├── profiles.yml
├── selectors.yml
└── .dbt_coves
    ├── config.yml
    └── templates
        ├── model_props.yml
        ├── source_props.yml
        ├── staging_model_props.yml
        └── staging_model.sql

name: 'data'
version: '1.0'
config-version: 2
 
model-paths: ["models"]
seed-paths: ["seeds"]
test-paths: ["tests"]
analysis-paths: ["analyses"]
 
flags:
  require_explicit_package_overrides_for_builtin_materializations: false
 
models:
  data:
    blog:
      staging:
        +materialized: table
        +schema: staging
        +schema_suffix: ''
 
      intermediate:
        +materialized: table
        +schema: intermediate
        +schema_suffix: ''
 
      marts:
        +materialized: table
        +schema: marts
        +schema_suffix: ''

blog:
  target: local
  outputs:
    local:
    type: bigquery
    method: service-account
    project: "{{ env_var('BIGQUERY_PROJECT_ID', 'dummy-project') }}"
    schema: blog
    keyfile: .config/searvice-account.json
    timeout_seconds: 300
    location: US

dbt-coves generate sources --project-dir ./ --profiles-dir ./ --target local \
	--schemas searchconsole --update-strategy update \
	--sources-destination models/blog/sources/_{{schema}}__{{relation}}.yml \
 	--models-destination models/blog/staging/stg_{{schema}}__{{relation}}.sql \
 	--model-props-destination models/blog/staging/stg_{{schema}}__{{relation}}.yml \
	--templates-folder .dbt_coves/templates \
 
     _ _     _
  __| | |__ | |_       ___ _____   _____  ___
 / _` | '_ \| __|____ / __/ _ \ \ / / _ \/ __|
| (_| | |_) | ||_____| (_| (_) \ V /  __/\__ \
 \__,_|_.__/ \__|     \___\___/ \_/ \___||___/
 
 
dbt-coves v1.10.5                   dbt v1.9.1
 
19:39:13  Registered adapter: bigquery=1.9.0
? Which sources would you like to generate? (Use arrow keys to mov
e, <space> to select, <a> to toggle, <i> to invert)
 » ○ [searchconsole] ExportLog
   ○ [searchconsole] searchdata_site_impression
   ○ [searchconsole] searchdata_url_impression
 
 
...
models/blog/staging/stg_searchconsole__searchdata_site_impression.sql created
Property file
models/blog/staging/stg_searchconsole__searchdata_site_impression.yml created
Property file
...

version: 2
 
sources:
  - name: searchconsole
    tables:
      - name: searchdata_site_impression
        description: ""

with raw_source as (
    select *
    from {{ source('searchconsole', 'searchdata_site_impression') }}
),
 
final as (
    select
        cast(data_date as date) as data_date,
        cast(site_url as string) as site_url,
        cast(query as string) as query,
        cast(is_anonymized_query as boolean) as is_anonymized_query,
        cast(country as string) as country,
        cast(search_type as string) as search_type,
        cast(device as string) as device,
        cast(impressions as int64) as impressions,
        cast(clicks as int64) as clicks,
        cast(sum_top_position as int64) as sum_top_position
 
    from raw_source
)
 
select * from final
 

version: 2
 
models:
  - name: searchdata_site_impression
    description: ""
    columns:
      - name: data_date
      - name: site_url
      - name: query
      - name: is_anonymized_query
      - name: country
      - name: search_type
      - name: device
      - name: impressions
      - name: clicks
      - name: sum_top_position
 

generate:
  sources:
    database:
      - "{{ env_var('BIGQUERY_PROJECT_ID', 'dummy-project') }}"
    # どのスキーマを対象にするか（複数指定可）
    schemas:
      - searchconsole
 
    exclude_relations:
      - ExportLog
 
    # ファイルの出力先と命名規則
    # schemaはdataset名、relationはtable名
    sources_destination: "models/blog/sources/_{{schema}}__{{relation}}.yml"
    models_destination: "models/blog/staging/stg_{{schema}}__{{relation}}.sql"
    model_props_destination: "models/blog/staging/stg_{{schema}}__{{relation}}.yml"
 
    # 既存ファイルがある場合の挙動
    update_strategy: update
 
    # SQLの中身（生成するmodelファイルのテンプレート）
    templates_folder: ".dbt_coves/templates"
 
    # JSONデータがあったらフラット化するか聞いてくれる
    flatten_json_fields: ask

dbt-coves generate properties --select tag:dbt-coves
...
The selection criterion 'tag:dbt-coves' does not match any
enabled nodes
No nodes selected!
 

#!/bin/bash
set -e
 
# osmosisタグの付いたモデルのスキーマを対象としてコマンド実行する
targets=$(dbt ls --quiet --select tag:osmosis --resource-type model | \
  sed 's/^[^.]*\.//' | \
  sed 's/\.[^.]*$//' | \
  sort | \
  uniq)
 
for fqn_prefix in $targets; do
    echo "processing selector: $fqn_prefix"
    dbt-osmosis yaml refactor --target prod --fqn "$fqn_prefix"
done

#!/bin/bash
 
TARGET_TAG="codegen"
OUTPUT_DIR="./generated_models"
 
# 出力ディレクトリがなければ作成
if [ ! -d "$OUTPUT_DIR" ]; then
  mkdir -p "$OUTPUT_DIR"
  echo "created output_dir: $OUTPUT_DIR"
fi
 
# list for target model
MODELS=$(dbt ls --select "tag:$TARGET_TAG" --resource-type model --quiet)
 
if [ -z "$MODELS" ]; then
  echo "not found, target model"
  exit 0
fi
 
for model in $MODELS; do
  echo "generating... $model -> $OUTPUT_DIR/_$model.yml"
 
  dbt --quiet --no-use-colors run-operation generate_model_yaml \
    --args "{\"model_names\": [\"$model\"], \"upstream_descriptions\": true}" \
    | sed -n '/^version:/,$p' > "$OUTPUT_DIR/_${model}.yml"
 
done
 

export DATACOVES__DBT_HOME="$(pwd)"
dbt-coves setup
...
Generating artifacts using our the Datacoves template.
 
🎤 Select what you want to set up?
   done (4 selections)
🎤 Enter the dbt project directory
   .
🎤 Enter a name for your dbt project?
   dbt_coves_project
...
 

# .pre-commit-config.yaml
files: ^./models/
 
repos:
 
  - repo: https://github.com/dbt-checkpoint/dbt-checkpoint
    rev: v2.0.7
 
    hooks:
      - id: check-source-table-has-description
      - id: check-script-semicolon
      - id: check-script-has-no-table-name
      - id: check-script-ref-and-source
      - id: check-model-has-description
      - id: check-model-has-properties-file
      - id: check-model-has-all-columns
 
  - repo: https://github.com/sqlfluff/sqlfluff
    # this is the version of sqlfluff, needs to be updated when using a new sqlfluff version (pip show sqlfluff)
    rev: 3.1.1
    hooks:
      - id: sqlfluff-lint
        language: python
        # Need these two dependencies.
        #   sqlfluff-templater-dbt should match the version of sqlfluff above in rev (pip show sqlfluff-templater-dbt)
        #   dbt-snowflake needs to match the version in transform tab of Datacoves (pip show dbt-snowflake)
        additional_dependencies:
          [
            sqlfluff-templater-dbt==3.1.1,
            dbt-bigquery==1.9,
            dbt-core==1.9.1,
          ]
        args: [--config, "./.sqlfluff"]
 
  - repo: https://github.com/adrienverge/yamllint.git
    rev: v1.37.1
    hooks:
      - id: yamllint
        args: [-c=.yamllint]
        exclude: ^./.dbt_coves/templates