MotherDuckのMCP Serverを使ってCursorでのdbt ローカル開発を試してみた

# dbt SQL Style Guide (Best Practices)

## Principles
- Prioritize readability and maintainability.
- Use only SELECT statements for transformations (no INSERT/UPDATE/DELETE).
- Always reference tables with `ref` or `source` (e.g., `ref('core_users')`).

---

## Style & Formatting
- Use snake_case for all names (e.g., `user_id`, `order_amount`).
- SQL keywords in UPPERCASE, table/column names in lowercase.
- Indent with 2 or 4 spaces, consistently.
- One column per line in SELECT.
- Use CTEs (WITH) for complex logic, keep nesting shallow.
- Add clear comments for intent and caveats.
- Never nest Jinja curly braces (`{{ }}`).
- Do not add a semicolon at the end of dbt SQL files.

---

## Naming
- Use snake_case for tables, models, and columns.
- Use clear suffixes for aggregates (e.g., `_count`, `_sum`, `_avg`).
- Boolean columns start with `is_` or `has_` (e.g., `is_active`).

---

## Best Practices
- Always use `ref`/`source`, never hardcode table names.
- Minimize CTEs; avoid unnecessary intermediate tables.
- Handle NULLs explicitly (e.g., `coalesce`, `ifnull`).
- Use `distinct` only when needed for deduplication.
- Always pair aggregate functions with `group by`.
- Avoid excessive subqueries; use CTEs for clarity.

---

## Tests & Documentation
- Add `description` to all models and columns.
- Define tests like `not_null` and `unique` in YAML for key columns.

---

# Rules for CTE (Common Table Expression)

- Always break down complex queries using CTEs (WITH clause), and give each CTE a descriptive name indicating its role.
- Recommended structure: "import (external reference) → logical (transformation/aggregation) → final (output)".
- Each CTE should have a single responsibility to maximize reusability and readability.
- Use snake_case for CTE names, and make the purpose clear in the name.
- When using multiple CTEs, add a brief comment before each CTE.
- Avoid deep nesting or excessive dependencies between CTEs.
- If logic is reused, make it a CTE; if used across models, consider making it a separate model.
- Use dbt's {{ ref('...') }} in import CTEs at the top.

---

## Example

with import_orders as (
  select * from {{ ref('orders') }}
),
aggregate_orders as (
  select
    user_id,
    count(order_id) as order_count
  from import_orders
  where status = 'completed'
  group by 1
),
final as (
  select
    user_id,
    order_count,
    case when order_count >= 3 then 'super_buyer'
         when order_count = 2 then 'regular_buyer'
         else 'single_buyer' end as buyer_type
  from aggregate_orders
)
select * from final

- Be conscious of the order, naming, and responsibility of each CTE.

import pandas as pd
import numpy as np

# usersデータ（異常値・型揺らぎあり）
users_data = {
    "user_id": [1, 2, 3, 4, 5, 6],
    "name": ["Alice", "Bob", None, "Eve", "Mallory", "Oscar"],
    "age": [25, "thirty", 40.5, None, 28, 22],
    "is_active": [True, False, "yes", None, False, True],
    "signup_date": [
        "2023-01-01",
        "2023-02-15",
        None,
        "not_a_date",
        "2023-05-20",
        "2023-06-01",
    ],
    "comment": ["Good", None, "Bad", "Average", "", "Excellent"],
    "extra_col": [np.nan, 123, "text", True, 0.5, None],
}
users_df = pd.DataFrame(users_data)
users_df["age"] = users_df["age"].astype(str)
users_df["is_active"] = users_df["is_active"].astype(str)
users_df["extra_col"] = users_df["extra_col"].astype(str)
users_df.to_parquet("users.parquet", engine="pyarrow")
print("users.parquet 作成完了")

# ordersデータ（正常値のみ）
orders_data = {
    "order_id": [101, 102, 103, 104, 105, 106],
    "user_id": [1, 2, 1, 4, 5, 6],  # usersに存在するuser_idのみ
    "amount": [120.5, 90.0, 30.0, 200.0, 50.0, 60.0],  # floatのみ
    "order_date": [
        "2024-03-01",
        "2024-03-02",
        "2024-03-05",
        "2024-03-10",
        "2024-03-11",
        "2024-03-12",
    ],
    "status": ["completed", "cancelled", "completed", "completed", "completed", "completed"],
    "coupon_used": [True, False, False, False, True, False],
    "extra_info": ["", "gift", "", "", "", ""],
}
orders_df = pd.DataFrame(orders_data)
orders_df.to_parquet("orders.parquet", engine="pyarrow")
print("orders.parquet 作成完了")

with user_orders as (
  -- 有効なユーザーと正常な注文のみをJOIN
  select
    u.user_id,
    u.name,
    u.age,
    u.is_active,
    u.signup_date,
    o.order_id,
    o.amount,
    o.order_date,
    o.status,
    o.coupon_used
  from {{ ref('stg_users') }} u
  left join {{ ref('stg_orders') }} o
    on u.user_id = o.user_id
  where u.user_id is not null
    and o.status = 'completed'
),
user_sales_agg as (
  -- ユーザーごとの購入集計
  select
    user_id,
    name,
    age,
    is_active,
    signup_date,
    count(order_id) as order_count,
    coalesce(sum(amount), 0) as total_amount
  from user_orders
  group by user_id, name, age, is_active, signup_date
)
select * from user_sales_agg 

models:
  - name: mart_user_sales
    description: "ユーザーごとの合計購入金額・注文回数を集計したmartモデル"
    columns:
      - name: user_id
        description: "ユーザーID"
        tests:
          - not_null
          - unique
      - name: name
        description: "ユーザー名"
      - name: age
        description: "年齢"
      - name: is_active
        description: "アクティブフラグ"
      - name: signup_date
        description: "会員登録日"
      - name: order_count
        description: "注文回数"
      - name: total_amount
        description: "合計購入金額"

with import_orders as (
  -- Parquetファイルから注文データをインポート
  select *
  from read_parquet('<絶対パス>/motherduck-mcp-test/mcp_demo/data/orders.parquet')
),
cleaned_orders as (
  -- 型揺らぎ・異常値をクリーニング
  select
    cast(order_id as bigint) as order_id,
    cast(user_id as bigint) as user_id,
    cast(amount as double) as amount,
    case
      when order_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' then order_date
      else null
    end as order_date,
    lower(status) as status,
    coalesce(cast(coupon_used as boolean), false) as coupon_used,
    extra_info
  from import_orders
)
select * from cleaned_orders 

with import_users as (
  -- Parquetファイルからユーザーデータをインポート
  select *
  from read_parquet('<絶対パス>/motherduck-mcp-test/mcp_demo/data/users.parquet')
),
cleaned_users as (
  -- 型揺らぎ・異常値をクリーニング
  select
    cast(user_id as bigint) as user_id,
    coalesce(nullif(trim(name), ''), 'unknown') as name,
    case
      when try_cast(age as integer) is not null then cast(age as integer)
      when age ~ '^[0-9]+(\\.[0-9]+)?$' then cast(cast(age as double) as integer)
      else null
    end as age,
    case lower(is_active)
      when 'true' then true
      when 'yes' then true
      when 'false' then false
      when 'no' then false
      else null
    end as is_active,
    case
      when signup_date ~ '^[0-9]{4}-[0-9]{2}-[0-9]{2}$' then signup_date
      else null
    end as signup_date,
    comment,
    extra_col
  from import_users
)
select * from cleaned_users

models:
  - name: stg_users
    description: "Parquetユーザーデータの型揺らぎ・異常値をクリーニングしたstagingモデル"
    columns:
      - name: user_id
        description: "ユーザーID"
        tests:
          - not_null
          - unique
      - name: name
        description: "ユーザー名"
      - name: age
        description: "年齢（整数化済み、異常値はNULL）"
      - name: is_active
        description: "アクティブフラグ（真偽値化済み）"
      - name: signup_date
        description: "会員登録日（YYYY-MM-DD形式のみ）"
      - name: comment
        description: "コメント"
      - name: extra_col
        description: "追加カラム（用途不明）"

  - name: stg_orders
    description: "Parquet注文データの型揺らぎ・異常値をクリーニングしたstagingモデル"
    columns:
      - name: order_id
        description: "注文ID"
        tests:
          - not_null
          - unique
      - name: user_id
        description: "ユーザーID（stg_usersとJOIN可能）"
        tests:
          - not_null
      - name: amount
        description: "注文金額"
      - name: order_date
        description: "注文日（YYYY-MM-DD形式のみ）"
      - name: status
        description: "注文ステータス（小文字化済み）"
      - name: coupon_used
        description: "クーポン利用有無（真偽値）"
      - name: extra_info
        description: "追加情報（用途不明）"