code ~/.dbt/profiles.yml

dutch_railway_network:

  outputs:
    dev:
      type: duckdb
      path: data/dutch_railway_network.duckdb
      extensions:
        - spatial
        - httpfs
      threads: 5
      attach:
        - path: 'https://blobs.duckdb.org/nl-railway/train_stations_and_services.duckdb'
          type: duckdb
          alias: external_db
  target: dev

この内容について解説します。
まずクエリ結果はテーブルとして保存されています。
そしてそのテーブルの中身はSELECTで記述されてますね。

- {{ dbt_utils.generate_surrogate_key(['id']) }} AS province_sk
いきなり複雑そうな感じですがid列を基に一意のサロゲートキー（province_sk）を生成...とのこと
まずdbt-utilsのヘルパー関数としてgenerate_surrogate_key というものがありこれで生成してるんですね👀
でサロゲートキーとは主キーとして使われる一意なIDのことらしいです。dbtだとこういう方法で主キーの作成の設定を行えるみたいです。(テーブル内のidを元に作成)
そしてそれをprovince_id列としてエイリアス設定しています。

- statnaam AS province_name,geom AS province_geometry
州の名前を含む列 statnaam を province_name,地理情報を含む列 geom を、province_geometry
としています。provinceは州という意味でnl_provincesから州の情報を引っ張ってきているのでわかりやすくこう言った名前にしているのだと思います。

- {{ common_columns() }}
この記述で複数テーブルの共通列を追加できるみたいです。便利ですね...


同様にdim_nl_municipalities と fact_services のデータもmaterializeします...と書いてるんですがdim_nl_municipalities.sqlの**記述がない...**
ので適当に作りました。
dim_nl_municipalities.sql```
{{ config(materialized='table') }}

SELECT
    {{ dbt_utils.generate_surrogate_key(['id']) }} AS municipality_sk,
    id AS municipality_id,
    gemeentenaam AS municipality_name,
    geom AS municipality_geometry,
    p.province_sk AS province_sk,
    {{ common_columns() }}
FROM st_read({{ source("geojson_external", "nl_municipalities") }}) AS src
LEFT JOIN {{ ref("dim_nl_provinces") }} AS p
      ON st_contains(
          p.province_geometry,
          st_centroid(src.geom)
      );

あとはdim_nl_train_stations.sqlは以下の通り?
dim_nl_train_stations.sql```
{{ config(materialized='table') }}

SELECT
    {{ dbt_utils.generate_surrogate_key(['tr_st.code']) }} AS station_sk, -- 駅の一意の識別子（サロゲートキー）
    tr_st.id AS station_id, -- 駅のID
    tr_st.code AS station_code, -- 駅のコード
    tr_st.name_long AS station_name, -- 駅の正式名称
    tr_st.type AS station_type, -- 駅のタイプ（例: 中央駅など）
    st_point(tr_st.geo_lng, tr_st.geo_lat) AS station_geo_location, -- 駅の緯度経度をジオメトリ形式で格納
    coalesce(dim_mun.municipality_sk, 'unknown') AS municipality_sk, -- 関連付けられた自治体の識別子。ない場合は "unknown"
    {{ common_columns() }} -- 共通列を追加
FROM {{ source("external_db", "stations") }} AS tr_st -- 外部ソース（stations）を参照
LEFT JOIN {{ ref ("dim_nl_municipalities") }} AS dim_mun -- dim_nl_municipalities テーブルと結合
       ON st_contains(
           dim_mun.municipality_geometry, -- 自治体のジオメトリ
           st_point(tr_st.geo_lng, tr_st.geo_lat) -- 駅の緯度経度をジオメトリ形式で指定
       )
WHERE tr_st.country = 'NL'; -- 駅の国がオランダの場合に限定

{{
    config(
        materialized='external',
        location="data/exports/nl_train_services_aggregate",
        options={
            "partition_by": "service_year, service_month",
            "overwrite": True
        }
    )
}}

SELECT
    year(service_date) AS service_year,
    month(service_date) AS service_month,
    service_type,
    service_company,
    tr_st.station_sk,
    tr_st.station_name,
    m.municipality_sk,
    m.municipality_name,
    p.province_sk,
    p.province_name,
    count(*) AS number_of_rides
FROM {{ ref ("fact_services") }} AS srv
INNER JOIN {{ ref("dim_nl_train_stations") }} AS tr_st
        ON srv.station_sk = tr_st.station_sk
INNER JOIN {{ ref("dim_nl_municipalities") }} AS m
        ON tr_st.municipality_sk = m.municipality_sk
INNER JOIN {{ ref("dim_nl_provinces") }} AS p
        ON m.province_sk = p.province_sk
WHERE service_year = {{ var('execution_year') }}
GROUP BY ALL

{{
    config(
        materialized='incremental',
        incremental_strategy='delete+insert',
        unique_key="""
            service_date,
            service_type,
            service_company,
            station_sk
        """
    )
}}

SELECT
    service_date,
    service_type,
    service_company,
    srv.station_sk,
    mn.municipality_sk,
    province_sk,
    count(*) AS number_of_rides,
    {{ common_columns() }}
FROM {{ ref ("fact_services") }} AS srv
INNER JOIN {{ ref("rep_dim_nl_train_stations") }} AS tr_st
        ON srv.station_sk = tr_st.station_sk
INNER JOIN {{ ref("rep_dim_nl_municipalities") }} AS mn
        ON tr_st.municipality_sk = mn.municipality_sk
WHERE NOT service_arrival_cancelled

  {% if is_incremental() %}
    AND srv.invocation_id = (
        SELECT invocation_id
        FROM {{ ref("fact_services") }}
        ORDER BY last_updated_dt DESC
        LIMIT 1
    )
  {% endif %}
GROUP BY ALL

{% if is_incremental() %}
    AND srv.invocation_id = (
        SELECT invocation_id
        FROM {{ ref("fact_services") }}
        ORDER BY last_updated_dt DESC
        LIMIT 1
    )
  {% endif %}

pg_isready

/tmp:5432 - accepting connections

psql postgres -c "CREATE DATABASE mydb;"


CREATE DATABASE

psql -c "CREATE USER ユーザー名 WITH PASSWORD 'ユーザーパスワード';"
CREATE ROLE

psql -d postgres -c "GRANT ALL PRIVILEGES ON DATABASE データベース名 TO ユーザー名;"

GRANT

code ~/.dbt/profiles.yml

ducktest:
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      threads: 1

    prod:
      type: duckdb
      path: prod.duckdb
      threads: 4

  target: dev

titanic:
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      threads: 1

    prod:
      type: duckdb
      path: prod.duckdb
      threads: 4

  target: dev

walmart:
  outputs:
    dev:
      type: duckdb
      path: /Users/nk/Desktop/renshu/dbt_tutrial/walmart/database/test.db
      threads: 1

    prod:
      type: duckdb
      path: prod.duckdb
      threads: 4

  target: dev

railway:
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      threads: 1

    prod:
      type: duckdb
      path: prod.duckdb
      threads: 4

  target: dev

dutch_railway_network:
  outputs:
    dev:
      type: duckdb
      path: data/dutch_railway_network.duckdb
      extensions:
        - spatial
        - httpfs
        - postgres
      threads: 5
      attach:
        - path: "https://blobs.duckdb.org/nl-railway/train_stations_and_services.duckdb"
          type: duckdb
          alias: external_db
        - path: "postgresql://postgres:{{ env_var('DBT_DUCKDB_PG_PWD') }}@localhost:5466/postgres"
          type: postgres
          alias: postgres_db
  target: dev

models:
  dutch_railway_network:
    transformation:
      schema: main
      +docs:
        node_color: 'silver'
    reverse_etl:
      database: postgres_db
      schema: public
      +docs:
        node_color: '#d5b85a'

duckdb data/dutch_railway_network.duckdb
v1.2.1 8e52ec4395
Enter ".help" for usage hints.
D "CREATE TABLE dummy (id INTEGER);"

pg_isready -h localhost -p 5466

brew services start postgresql@15

dbt run --model +reverse_etl

02:16:28  Running with dbt=1.9.4
02:16:28  Registered adapter: duckdb=1.9.2
02:16:28  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
There are 1 unused configuration paths:
- models.dutch_railway_network.transformation
02:16:28  Found 7 models, 4 data tests, 4 sources, 426 macros
02:16:28  
02:16:28  Concurrency: 5 threads (target='dev')
02:16:28  
02:19:30  
02:19:30  Finished running  in 0 hours 3 minutes and 1.48 seconds (181.48s).
02:19:30  Encountered an error:
Runtime Error
  IO Error: Cannot open database "https://blobs.duckdb.org/nl-railway/train_stations_and_services.duckdb" in read-only mode: database does not exist

duckdb
v1.2.1 8e52ec4395
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D ATTACH 'https://blobs.duckdb.org/nl-railway/train_stations_and_services.duckdb' AS external_db;
IO Error:
Cannot open database "https://blobs.duckdb.org/nl-railway/train_stations_and_services.duckdb" in read-only mode: database does not exist