[ハンズオン] サクッと AWS CloudShell で dbt v2（dbt-oss）+ DuckDB のビルド・テスト・リネージまで体験する15分ハンズオン！

python3 --version

Python 3.13.15

python3 -m venv ~/dbt-venv
source ~/dbt-venv/bin/activate
pip install dbt-oss==2.0.5
dbt --version

Collecting dbt-oss==2.0.5
  Downloading dbt_oss-2.0.5.tar.gz (5.1 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
  Preparing metadata (pyproject.toml) ... done
...
Building wheels for collected packages: dbt-oss
  Building wheel for dbt-oss (pyproject.toml) ... done
  Created wheel for dbt-oss: filename=dbt_oss-2.0.5-cp311-abi3-manylinux_2_28_x86_64.whl size=69112585 sha256=6b4670ead375faf470e7a84841ae796c7bd2a0d2ef6b8c5e4315c34b56affd3a
...
Successfully built dbt-oss
Installing collected packages: typing_extensions, msgpack, mashumaro, dbt-oss
Successfully installed dbt-oss-2.0.5 mashumaro-3.22 msgpack-1.2.2 typing_extensions-4.16.0
dbt-oss 2.0.5

cd ~

mkdir -p duckdb_handson/seeds duckdb_handson/models/staging duckdb_handson/models/marts

cat > duckdb_handson/dbt_project.yml <<'DUCKDBTV2'
name: duckdb_handson
version: "1.0.0"
profile: duckdb_handson

seed-paths: ["seeds"]
model-paths: ["models"]

models:
  duckdb_handson:
    staging:
      +materialized: view
    marts:
      +materialized: table
DUCKDBTV2

cat > duckdb_handson/profiles.yml <<'DUCKDBTV2'
duckdb_handson:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ./warehouse.duckdb
      schema: main
      threads: 4
DUCKDBTV2

cat > duckdb_handson/seeds/raw_customers.csv <<'DUCKDBTV2'
customer_id,customer_name,prefecture
1,佐藤,東京都
2,鈴木,大阪府
3,高橋,福岡県
4,田中,北海道
DUCKDBTV2

cat > duckdb_handson/seeds/raw_orders.csv <<'DUCKDBTV2'
order_id,customer_id,order_date,status,amount
101,1,2026-09-01,completed,1200
102,1,2026-09-03,completed,800
103,2,2026-09-05,returned,1500
104,3,2026-09-10,completed,3000
105,3,2026-09-12,placed,450
106,1,2026-09-15,completed,2200
DUCKDBTV2

cat > duckdb_handson/models/staging/stg_customers.sql <<'DUCKDBTV2'
select
    customer_id,
    customer_name,
    prefecture
from {{ ref('raw_customers') }}
DUCKDBTV2

cat > duckdb_handson/models/staging/stg_orders.sql <<'DUCKDBTV2'
select
    order_id,
    customer_id,
    cast(order_date as date) as order_date,
    status,
    amount
from {{ ref('raw_orders') }}
DUCKDBTV2

cat > duckdb_handson/models/marts/customer_summary.sql <<'DUCKDBTV2'
with completed_orders as (
    select *
    from {{ ref('stg_orders') }}
    where status = 'completed'
)

select
    c.customer_id,
    c.customer_name,
    c.prefecture,
    count(o.order_id)          as order_count,
    coalesce(sum(o.amount), 0) as total_amount,
    max(o.order_date)          as last_order_date
from {{ ref('stg_customers') }} as c
left join completed_orders as o
    on c.customer_id = o.customer_id
group by all
DUCKDBTV2

cat > duckdb_handson/models/schema.yml <<'DUCKDBTV2'
models:
  - name: stg_customers
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null

  - name: stg_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id
      - name: status
        data_tests:
          - accepted_values:
              arguments:
                values: ['placed', 'completed', 'returned']

  - name: customer_summary
    columns:
      - name: customer_id
        data_tests:
          - unique
          - not_null
DUCKDBTV2

find duckdb_handson -type f | sort

duckdb_handson/dbt_project.yml
duckdb_handson/models/marts/customer_summary.sql
duckdb_handson/models/schema.yml
duckdb_handson/models/staging/stg_customers.sql
duckdb_handson/models/staging/stg_orders.sql
duckdb_handson/profiles.yml
duckdb_handson/seeds/raw_customers.csv
duckdb_handson/seeds/raw_orders.csv

cd ~/duckdb_handson
dbt debug

   dbt-oss 2.0.5
   Loading profiles.yml
 Debugging profile: dev
 Debugging dbt version: 2.0.5
 Debugging platform: linux x86_64 (unix)
 Debugging adapter type: duckdb (remote)
 Debugging dependencies:
  git: OK
 Debugging connection:
  "path": "./warehouse.duckdb",
  "schema": "main"
 Debugging connection test: OK (1.2s)
  Debugged All checks passed!

==================== Execution Summary =====================
Finished 'debug' successfully for target 'dev' [1.3s]

dbt build

   dbt-oss 2.0.5
   Loading profiles.yml
 Succeeded seed  main.raw_customers (table) [2 of 13 in 0.05s]
 Succeeded seed  main.raw_orders (table) [1 of 13 in 0.06s]
 Succeeded model main.stg_customers (view) [3 of 13 in 0.03s]
 Succeeded model main.stg_orders (view) [4 of 13 in 0.03s]
    Passed test  not_null_stg_customers_customer_id [5 of 13 in 0.01s]
    Passed test  unique_stg_customers_customer_id [6 of 13 in 0.02s]
    Passed test  not_null_stg_orders_order_id [9 of 13 in 0.05s]
    Passed test  relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ [11 of 13 in 0.05s]
    Passed test  unique_stg_orders_order_id [8 of 13 in 0.05s]
    Passed test  accepted_values_stg_orders_status__placed__completed__returned [7 of 13 in 0.05s]
 Succeeded model main.customer_summary (table) [10 of 13 in 0.03s]
    Passed test  not_null_customer_summary_customer_id [12 of 13 in 0.01s]
    Passed test  unique_customer_summary_customer_id [13 of 13 in 0.01s]

==================== Execution Summary =====================
Finished 'build' successfully for target 'dev' [1.1s]
Processed: 3 models | 8 tests | 2 seeds
Summary: 13 total | 13 success

dbt show --inline "select * from {{ ref('customer_summary') }} order by customer_id"

   dbt-oss 2.0.5
   Loading profiles.yml
Query show_sql_operation_inline
┌─────────────┬───────────────┬────────────┬─────────────┬──────────────┬─────────────────┐
│ customer_id ┆ customer_name ┆ prefecture ┆ order_count ┆ total_amount ┆ last_order_date │
╞═════════════╪═══════════════╪════════════╪═════════════╪══════════════╪═════════════════╡
│ 1           ┆ 佐藤          ┆ 東京都     ┆ 3           ┆ 4200         ┆ 2026-09-15      │
│ 2           ┆ 鈴木          ┆ 大阪府     ┆ 0           ┆ 0            ┆                 │
│ 3           ┆ 高橋          ┆ 福岡県     ┆ 1           ┆ 3000         ┆ 2026-09-10      │
│ 4           ┆ 田中          ┆ 北海道     ┆ 0           ┆ 0            ┆                 │
└─────────────┴───────────────┴────────────┴─────────────┴──────────────┴─────────────────┘
4 rows.
 Succeeded model main.inline (ephemeral) [1 of 1 in 0.03s]

==================== Execution Summary =====================
Finished 'show' successfully for target 'dev' [496ms]

cat >> seeds/raw_orders.csv <<'DUCKDBTV2'
107,9,2026-09-20,shipped,999
DUCKDBTV2

dbt build

   dbt-oss 2.0.5
   Loading profiles.yml
 Succeeded seed  main.raw_customers (table) [2 of 13 in 0.06s]
 Succeeded seed  main.raw_orders (table) [1 of 13 in 0.06s]
 Succeeded model main.stg_customers (view) [3 of 13 in 0.04s]
 Succeeded model main.stg_orders (view) [4 of 13 in 0.04s]
    Failed test  accepted_values_stg_orders_status__placed__completed__returned (models/schema.yml:23:13) [9 of 13 in 0.03s]
    Passed test  unique_stg_customers_customer_id [5 of 13 in 0.04s]
    Passed test  not_null_stg_customers_customer_id [6 of 13 in 0.04s]
    Passed test  not_null_stg_orders_order_id [10 of 13 in 0.04s]
    Passed test  unique_stg_orders_order_id [7 of 13 in 0.04s]
    Failed test  relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ (models/schema.yml:17:13) [8 of 13 in 0.04s]
   Skipped model main.customer_summary (table) [11 of 13]
   Skipped test 'not_null_customer_summary_customer_id', 'unique_customer_summary_customer_id'

==================== Test Failures =====================
Test failed (1 failed row(s)): accepted_values_stg_orders_status__placed__completed__returned
Test failed (1 failed row(s)): relationships_stg_orders_customer_id__customer_id__ref_stg_customers_

==================== Execution Summary =====================
Finished 'build' with 2 errors for target 'dev' [946ms]
Processed: 3 models | 8 tests | 2 seeds
Summary: 13 total | 8 success | 2 error | 3 skipped

cat > seeds/raw_orders.csv <<'DUCKDBTV2'
order_id,customer_id,order_date,status,amount
101,1,2026-09-01,completed,1200
102,1,2026-09-03,completed,800
103,2,2026-09-05,returned,1500
104,3,2026-09-10,completed,3000
105,3,2026-09-12,placed,450
106,1,2026-09-15,completed,2200
DUCKDBTV2

dbt build --generate-info-schema

   dbt-oss 2.0.5
   Loading profiles.yml
 Succeeded seed  main.raw_orders (table) [1 of 13 in 0.08s]
...
    Passed test  unique_customer_summary_customer_id [12 of 13 in 0.01s]

==================== Execution Summary =====================
Finished 'build' successfully for target 'dev' [1.3s]
Processed: 3 models | 8 tests | 2 seeds
Summary: 13 total | 13 success

ls target/info_schema/v1/ | wc -l
ls target/info_schema/v1/ | grep -E 'models|edges|run_results|views'

39

dbt.edges.parquet
dbt.models.parquet
dbt_rt.run_results.parquet
dbt.semantic_models.parquet
views.sql

dbt show --inline "select name, materialized, schema_name from read_parquet('target/info_schema/v1/dbt.models.parquet') order by name"

   dbt-oss 2.0.5
   Loading profiles.yml
Query show_sql_operation_inline
┌──────────────────┬──────────────┬─────────────┐
│ name             ┆ materialized ┆ schema_name │
╞══════════════════╪══════════════╪═════════════╡
│ customer_summary ┆ table        ┆ main        │
│ stg_customers    ┆ view         ┆ main        │
│ stg_orders       ┆ view         ┆ main        │
└──────────────────┴──────────────┴─────────────┘
3 rows.
 Succeeded model main.inline (ephemeral) [1 of 1 in 0.03s]

==================== Execution Summary =====================
Finished 'show' successfully for target 'dev' [583ms]

dbt show --inline "select parent_unique_id, child_unique_id from read_parquet('target/info_schema/v1/dbt.edges.parquet') where parent_unique_id not like 'macro.%' and child_unique_id like 'model.%' order by all"

   dbt-oss 2.0.5
   Loading profiles.yml
Query show_sql_operation_inline
┌────────────────────────────────────┬───────────────────────────────────────┐
│ parent_unique_id                   ┆ child_unique_id                       │
╞════════════════════════════════════╪═══════════════════════════════════════╡
│ model.duckdb_handson.stg_customers ┆ model.duckdb_handson.customer_summary │
│ model.duckdb_handson.stg_orders    ┆ model.duckdb_handson.customer_summary │
│ seed.duckdb_handson.raw_customers  ┆ model.duckdb_handson.stg_customers    │
│ seed.duckdb_handson.raw_orders     ┆ model.duckdb_handson.stg_orders       │
└────────────────────────────────────┴───────────────────────────────────────┘
4 rows.
 Succeeded model main.inline (ephemeral) [1 of 1 in 0.03s]

==================== Execution Summary =====================
Finished 'show' successfully for target 'dev' [609ms]

dbt show --inline "select split_part(unique_id, '.', 3) as node_name, status, failures from read_parquet('target/info_schema/v1/dbt_rt.run_results.parquet') where status in ('fail', 'skipped') order by node_name"

   dbt-oss 2.0.5
   Loading profiles.yml
Query show_sql_operation_inline
┌───────────────────────────────────────────────────────────────────────┬─────────┬──────────┐
│ node_name                                                             ┆ status  ┆ failures │
╞═══════════════════════════════════════════════════════════════════════╪═════════╪══════════╡
│ accepted_values_stg_orders_status__placed__completed__returned        ┆ fail    ┆ 1        │
│ customer_summary                                                      ┆ skipped ┆          │
│ not_null_customer_summary_customer_id                                 ┆ skipped ┆          │
│ relationships_stg_orders_customer_id__customer_id__ref_stg_customers_ ┆ fail    ┆ 1        │
│ unique_customer_summary_customer_id                                   ┆ skipped ┆          │
└───────────────────────────────────────────────────────────────────────┴─────────┴──────────┘
5 rows.
 Succeeded model main.inline (ephemeral) [1 of 1 in 0.03s]

==================== Execution Summary =====================
Finished 'show' successfully for target 'dev' [588ms]

dbt show --inline "select name, materialized, schema_name from {{ info_schema('models') }} order by name"

   dbt-oss 2.0.5
Query info_schema_inline
┌──────────────────┬──────────────┬─────────────┐
│ name             ┆ materialized ┆ schema_name │
╞══════════════════╪══════════════╪═════════════╡
│ customer_summary ┆ table        ┆ main        │
│ stg_customers    ┆ view         ┆ main        │
│ stg_orders       ┆ view         ┆ main        │
└──────────────────┴──────────────┴─────────────┘
3 rows.

==================== Execution Summary =====================
Finished 'show' successfully [501ms]

dbt docs generate --output-dir docs_site
zip -rq docs_site.zip docs_site
ls -lh docs_site.zip

Running compile; pass `--no-compile` to export the existing index instead.
   dbt-oss 2.0.5
 Succeeded seed  main.raw_orders (table) [11 of 13 in 0.00s]
...
 Generated docs_site (39 artifacts copied) — no column lineage; rerun with `--static-analysis strict` to include it
Host the contents of docs_site on any static file server.

==================== Execution Summary =====================
Finished 'docs' successfully [2.2s]
-rw-r--r--. 1 cloudshell-user cloudshell-user 2.6M Sep 25 08:12 docs_site.zip

/home/cloudshell-user/duckdb_handson/docs_site.zip

cd ~/Downloads
unzip -q docs_site.zip
python3 -m http.server 8000 --directory docs_site

sudo dnf install -y python3.11 python3.11-pip
python3.11 -m venv ~/dbt-venv
source ~/dbt-venv/bin/activate
pip install dbt-oss==2.0.5
dbt --version