# dbt_project.yml

models:
  dbt_pj:
 
    work_dbt:
      materialized: ephemeral
      +schema: work_dbt

// keybindings.json
[
    {
        "key": "shift+enter",
        "command": "extension.multiCommand.execute",
        "args": { 
            "sequence": [
                "dbtPowerUser.executeSQL",
                "editor.action.formatDocument",
                "workbench.action.files.save",
            ],
        }
    }
]

 dbt test --select モデル名 --store-failures   

-- テストしない方をコメントアウトする
{# {% set check_flag = "not_null" %} #} 
{% set check_flag = "unique" %}

{% set table = "table" %}
{% set column = "id" %}

SELECT *
FROM {{ ref(table) }}

{%- if check_flag == "not_null" %}
  WHERE {{ column }} IS NULL
{% elif check_flag == "unique" %}
  WHERE {{ column }} IN (
    SELECT {{ column }}
    FROM {{ ref(table) }}
    GROUP BY {{ column }}
    HAVING COUNT({{ column }}) > 1
  )
{% endif -%}
ORDER BY {{ column }} ASC

dbt-osmosis

pip install -r requirements.txt 

dbt-osmosis yaml refactor ./models/~フォルダ名

models:
  jaffle_shop:
      +dbt-osmosis: "{model}.yml" # _{model}.ymlとアンダーバー前に入れるよりファイルの並び的に良い
      materialized: table
      staging:
        materialized: view

models:
  +persist_docs:
    relation: true
    columns: true

dbt-osmosis yaml refactor ./models/~フォルダ名
dbt-osmosis yaml refactor --project-dir ... --profiles-dir ...

dbt-osmosis yaml organize --project-dir ... --profiles-dir ...

dbt-osmosis yaml document --project-dir ... --profiles-dir ...

FROM python:3.9.12-slim-bullseye

ARG dbt_bigquery_ref=dbt-bigquery@v1.0.0

RUN apt-get update \
  && apt-get dist-upgrade -y \
  && apt-get install -y --no-install-recommends \
    git \
    ssh-client \
    software-properties-common \
    make \
    build-essential \
    ca-certificates \
    libpq-dev \
    vim \
  && apt-get clean \
  && rm -rf \
    /var/lib/apt/lists/* \
    /tmp/* \
    /var/tmp/*

ENV PYTHONIOENCODING=utf-8
ENV LANG=C.UTF-8

RUN python -m pip install --upgrade pip setuptools wheel --no-cache-dir

RUN python -m pip install --no-cache-dir "git+https://github.com/dbt-labs/${dbt_bigquery_ref}#egg=dbt-bigquery"

COPY profiles.yml /root/.dbt/profiles.yml

WORKDIR /usr/app/dbt/
VOLUME /usr/app
ENTRYPOINT tail -f /dev/null

User Error: The dbt templater does not support stdin input, provide a path instead

[sqlfluff:templater:dbt]
# 環境に合った設定をする
project_dir = ./
profiles_dir = ~/.dbt/ 
profile = dbt_project
target = dev

				"sqlfluff.linter.run": "onSave",
				"sqlfluff.experimental.format.executeInTerminal": true,
				"sqlfluff.format.enabled": true

Query: Invalid value for location: is not a valid value

"bigqueryRunner.location": "asia-northeast1",

dbt run -s state:modified+ --defer --state path/to/prod/artifacts
dbt test -s state:modified+ --defer --state path/to/prod/artifacts

    {{
        config(
            materialized='view'
        )
    }}

    select ...

{
  "field": "<field name>",
  "data_type": "<timestamp | date | datetime | int64>",
  "granularity": "<hour | day | month | year>"

  # Only required if data_type is "int64"
  "range": {
    "start": <int>,
    "end": <int>,
    "interval": <int>
  }
}

{% macro generate_alias_name(custom_alias_name=none, node=none, target=none) -%}
    {%- if custom_alias_name -%}
        {{ custom_alias_name | trim }}
    {%- else -%}
        {%- if node and target -%}
            {%- set model_name = node.name -%}
            {%- set schema_prefix = target.schema ~ "_" -%}
            {%- if model_name.startswith(schema_prefix) -%}
                {{ model_name[schema_prefix|length:] }}
            {%- else -%}
                {{ model_name }}
            {%- endif -%}
        {%- else -%}
            {{ none }}
        {%- endif -%}
    {%- endif -%}
{%- endmacro %}

{% macro generate_schema_name(custom_schema_name, node) -%}
   {%- if custom_schema_name is none -%}
       {{ target.schema }}
   {%- else -%}
       {{ custom_schema_name | trim }}
   {%- endif -%}
{%- endmacro %}

{% macro generate_alias_name(custom_alias_name, node) -%}
   {%- if custom_alias_name -%}
       {{ custom_alias_name | trim }}
   {%- else -%}
       {%- set schema_name = node.schema -%}
       {%- set model_name = node.name -%}
       {%- set schema_prefix = schema_name ~ "_" -%}
       {%- if model_name.startswith(schema_prefix) -%}
           {{ model_name[schema_prefix|length:] }}
       {%- else -%}
           {{ model_name }}
       {%- endif -%}
   {%- endif -%}
{%- endmacro %}

models
    └── mrt_ads(ディレクトリ)
                ├── mrt_ads_ga4(モデル: mrt_ads_ga4、エイリアス: ga4)
                └── mrt_ads_yahoo(モデル: mrt_ads_yahoo、エイリアス: yahoo)

mrt_ads(データセット)
     ├── ga4(テーブル)
     └── yahoo(テーブル)
dwh_ads(データセット)
     ├── ga4(テーブル)
     └── yahoo(テーブル)

select *
from {{ ref("mrt_ads_yahoo") }}

models:
  kawaii_dbt:
    dwh:
      +schema: dwh
      + tags:
        - "dwh"
    ml:
      +schema: ml
      +tags:
        - "ml"

    dataset_id:
      +materialized: table
      +database: project_id
      +schema: dataset_id

models
    └── mrt_ads(ディレクトリ)
                ├── mrt_ads_ga4(モデル: mrt_ads_ga4、エイリアス: ga4)
                └── mrt_ads_yahoo(モデル: mrt_ads_yahoo、エイリアス: yahoo)

{{ config(
    tags=["mst"]
) }}

select * from {{ ref('bbb') }}

seeds
    └── seeds_csv(ディレクトリ)
                ├── seeds_csv__list.csv(モデル: seeds_csv__list、エイリアス: list)
                └── seeds_csv__list.yml

version: 2

seeds:
  - name: seeds_csv__list
    description: |
        説明を書く

seeds:
  dbt_project:
    +full_refresh: true
    +persist_docs:
      relation: true
      columns: true
    seeds_csv:
     +schema: seeds_csv

df['last_login'] = pd.to_datetime(df['last_login']).dt.strftime('%Y-%m-%dT%H:%M:%S')

  Error while reading data, error message: CSV processing encountered too many errors, giving up. Rows: 7; errors: 7; max bad: 0; error percent: 0
  Error while reading data, error message: Invalid datetime string "2024-08-19T01:48:37.696484Z"; line_number: 2 byte_offset_to_start_of_line: 209 column_index: 3 column_name: "last_login" column_type: DATETIME value: "2024-08-19T01:48:..."
  You are loading data without specifying data format, data will be treated as CSV format by default. If this is not what you mean, please specify data format by --source_format.

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {
      'field': 'created_at',
      'data_type': 'date',
      'granularity': 'day',
      'copy_partitions': true
    }
) }}

SELECT *
FROM `ソーステーブル`
{% if is_incremental() %}
WHERE created_at BETWEEN '2024-04-05' AND '2024-04-06'
{% endif %}

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', creation_time, 'Asia/Tokyo') as jst,
  concat(
    referenced_table.project_id,"."
    ,referenced_table.dataset_id,"."
    ,referenced_table.table_id) as table,
  query
FROM `project_id`.`region-asia-northeast1`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(referenced_tables) AS referenced_table
WHERE DATE(creation_time) > DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
ORDER BY creation_time DESC
limit 10

{{ config(
    materialized = 'incremental',
    incremental_strategy = 'insert_overwrite',
    partition_by = {
      'field': 'created_at',
      'data_type': 'date',
      'granularity': 'day',
      'copy_partitions': true
    }
) }}

SELECT *
FROM `ソーステーブル`

03:21:24.855948 [debug] [Thread-1 (]: BigQuery adapter: Copying table(s) "/projects/project_id/datasets/dataset_id/tables/ad_report__dbt_tmp$20231205" to "/projects/project_id/datasets/dataset_id/tables/ad_report$20231205" with disposition: "WRITE_TRUNCATE"

03:35:24.902314 [debug] [Thread-1 (]: BigQuery adapter: Copying table(s) "/projects/project_id/datasets/dataset_id/tables/ad_report__dbt_tmp$202404" to "/projects/project_id/datasets/dataset_id/tables/ad_report$202404" with disposition: "WRITE_TRUNCATE"

{% macro log_order_items() %}

{%- call statement('count_order_items', fetch_result=True) -%}
    SELECT count(*) as count from {{ this }}
{%- endcall -%}

{%- set total_order_items = load_result('count_order_items') -%}

{% if execute %}
    {{ log(modules.datetime.datetime.now().strftime('%H:%M:%S') ~ ' | Number of rows transformed ' ~ total_order_items['data'][0][0] ~ ' rows into a table', True) }}
{% endif %}

select 1

{% endmacro %}

01:05:34  1 of 1 START sql incremental model work_dbt.aaaaa ............................. [RUN]
01:05:36  01:05:36 | Number of rows transformed 2714 rows into a table
01:05:45  1 of 1 OK created sql incremental model work_dbt.aaaaa ........................ [None (0 processed) in 10.92s]

models:
  +on_schema_change: "append_new_columns"

{% docs __overview__ %}
### Welcome!
dbtプロジェクトの自動生成ドキュメントへようこそ！
### Navigation
ウィンドウの左側にあるプロジェクトとデータベースのナビゲーションタブを使って、プロジェクト内のモデルを調べることができます。
#### Project Tab
プロジェクトタブはdbtプロジェクトのディレクトリ構造を反映しています。このタブでは、dbtプロジェクトで定義されたすべてのモデル、およびdbtパッケージからインポートされたモデルを見ることができます。
#### Database Tab
データベースタブにもモデルが表示されますが、よりデータベースエクスプローラーに近い形式で 表示されます。このビューには、データベーススキーマにグループ化されたリレーション（テーブルとビュー）が表示されます。エフェメラルモデルはデータベースに存在しないため、このインターフェースには表示されないことに注意してください。
### グラフ探索
ページの右下にある青いアイコンをクリックすると、モデルの系統グラフを見ることができます。

モデルのページでは、探索中のモデルの直接の親と子が表示されます。この血統ペインの右上にある「展開」ボタンをクリックすると、現在調べているモデルを構築するために使用された、あるいは構築されたすべてのモデルを見ることができます。

一度展開されると、`--select` や `--exclude` のモデル選択構文を使って、グラフ内のモデルをフィルタリングすることができるようになります。モデル選択の詳細については、dbtの[ドキュメント](https://docs.getdbt.com/reference/node-selection/syntax)を参照してください。

また、モデルを右クリックすることで、グラフをインタラクティブにフィルタリングしたり、探索したりすることもできます。

------
### More information
- [What is dbt](https://docs.getdbt.com/docs/introduction)?
- Read the [dbt viewpoint](https://docs.getdbt.com/docs/viewpoint)
- [Installation](https://docs.getdbt.com/docs/installation)
- Join the [dbt Community](https://www.getdbt.com/community/) for questions and discussion
{% enddocs %}

# ファイルの場所確認（パス確認、出力パスで以下実行）
git rev-parse --git-dir

# post-checkoutファイルの作成
touch .git/hooks/post-checkout

# 実行権限を付与
chmod +x .git/hooks/post-checkout

# ファイルを編集（VScodeの場合）
code .git/hooks/post-checkout

#!/bin/bash

# 現在のブランチ名を取得し、/を_に変換
export CURRENT_BRANCH=$(git rev-parse --abbrev-ref HEAD | sed 's/\//_/g')

# 環境変数をシェルにエクスポートするために ~/.bashrc に追加
echo "export CURRENT_BRANCH=$CURRENT_BRANCH" >> ~/.bashrc

# シェル環境をリロード
source ~/.bashrc

# 確認のために表示
echo "CURRENT_BRANCH is set to $CURRENT_BRANCH"

{% macro generate_schema_name(custom_schema_name, node) -%}
    {%- set default_schema = env_var('CURRENT_BRANCH') | target.schema -%}
    {%- if default_schema=='main' or default_schema=='develop' -%}
        {%- if custom_schema_name is none -%}
            {{ target.schema }}
        {%- else -%}
            {{ custom_schema_name | trim }}
        {%- endif -%}
    {%- else -%}
        {{ default_schema }}_{{ custom_schema_name | trim }}
    {%- endif -%}
{%- endmacro %}

  - package: dbt-labs/audit_helper
    version: 0.12.0

$ dbt deps

{{ 
  audit_helper.compare_all_columns(
    a_relation=api.Relation.create(database='DEMO', schema='CORE', identifier='dim_orgs'),
    b_relation=ref('dim_orgs'),
    primary_key='org_id',
    summarize=true
  ) 
}}

{% set old_relation = adapter.get_relation(
      database = "old_database",
      schema = "old_schema",
      identifier = "fct_orders"
) -%}

{% set dbt_relation = ref('fct_orders') %}

{{ audit_helper.compare_all_columns(
    a_relation = old_relation,
    b_relation = dbt_relation,
    primary_key = "order_id"
) }}

{% set old_relation = adapter.get_relation(
      database = "old_database",
      schema = "old_schema",
      identifier = "fct_orders"
) -%}

{% set dbt_relation = ref('fct_orders') %}
{% if execute %}
    {{ audit_helper.compare_all_columns(
        a_relation = old_relation,
        b_relation = dbt_relation,
        primary_key = "order_id"
    ) }}
{% endif %}

dbt run-operation audit_orders --args '{"old_database": "old_database_name", "old_schema": "old_schema_name", "old_identifier": "old_fct_orders", "dbt_identifier": "fct_orders"}'

{% macro audit_orders(old_database, old_schema, old_identifier, dbt_identifier) %}

    {% set old_relation = adapter.get_relation(
        database = old_database,
        schema = old_schema,
        identifier = old_identifier
    ) -%}

    {% set dbt_relation = ref(dbt_identifier) %}
    
    {% if execute %}
      {# 2つのリレーションが同一かどうかを比較 #}
      {% set check_query = audit_helper.quick_are_relations_identical(
          a_relation = old_relation,
          b_relation = dbt_relation,
          columns = None
      ) %}

      {% set results = run_query(check_query) %}
      {% set is_identical = results.columns[0].values()[0] %}
      {# お好みの表示どっちか #}
      {% do results.print_table() %}
      {% do log(results.column_names[0] ~ " : " ~ is_identical, info=True) %}
      
      {# 比較結果をログに出力 #}
      {% if is_identical %}
        {% do log(dbt_identifier ~ " : テーブルは同一です。", info=True) %}
      {% else %}
        {% do log(dbt_identifier ~ " : テーブルは一致していません。", info=True) %}
      {% endif %}
    {% endif %}

{% endmacro %}

{% macro queries_identical(old_model, new_model) %}

  {% set new_model_relation = ref(new_model) %}
  {% set column_list = adapter.get_columns_in_relation(new_model_relation) | map(attribute='name') | list %}
  {% do log("Comparing columns: " ~ column_list, info=True) %}

  {% set old_query %}
      select * from {{ ref(old_model) }}
  {% endset %}

  {% set new_query %}
      select * from {{ ref(new_model) }}
  {% endset %}

  {% if execute %}
    {# 比較用クエリを生成 #}
    {% set check_query = audit_helper.quick_are_queries_identical(
      query_a=old_query,
      query_b=new_query,
      columns=column_list
    ) %}

    {# 比較結果を取得 #}
    {% set results = run_query(check_query) %}
    {% set is_identical = results.columns[0].values()[0] %}

    {# 比較結果をログに出力 #}
    {% if is_identical %}
      {% do log("✔ クエリが一致しています: " ~ old_model ~ " = " ~ new_model, info=True) %}
    {% else %}
      {% do log("✘ クエリが一致していません: " ~ old_model ~ " ≠ " ~ new_model, info=True) %}
    {% endif %}
  {% endif %}

{% endmacro %}

dbt run-operation queries_identical --args '{"old_model": "old_model", "new_model": "new_model"}'

with query_a as (

)
, query_b as (

)

select count(distinct hash_result) = 1 as are_tables_identical
from (
    select bit_xor(farm_fingerprint(to_json_string(query_a))) as hash_result
    from query_a

    union all
    
    select bit_xor(farm_fingerprint(to_json_string(query_b))) as hash_result
    from query_b
) as hashes

WITH new_table AS (

),
old_table AS (

)

SELECT
  'new_table' AS source_table,
  *
FROM (
  SELECT * FROM new_table
  EXCEPT DISTINCT
  SELECT * FROM old_table
)

UNION ALL

SELECT
  'old_table' AS source_table,
  *
FROM (
  SELECT * FROM old_table
  EXCEPT DISTINCT
  SELECT * FROM new_table
)