---
# System prepended metadata

title: dbt Workshop 101 TAIWAN Taichung

---


##  duck_q.py ( update: 2024-03-09 07:47)

```  
import sys
import duckdb

con = duckdb.connect("dev.duckdb")
sql_cmd = " ".join(sys.argv[1:])
con.sql(sql_cmd).show()

```

- python duck_q.py  show tables 
- python duck_q.py  select * from my_first_dbt_model 
- python duck_q.py  select * from p01


----

# dbt Workshop 101 @ Taichung py

### 報名頁面 2024.03.09 (六) 10:00 - 12:00 
https://www.accupass.com/event/2402290318542860946270
- 2024.03.09 (六) 10:00 - 12:00 (GMT+8)加入行事曆
 
### 本頁面短網址: 
https://tinyurl.com/3yb5u9cf
https://pse.is/5nsuub

### Taichung py
https://www.facebook.com/groups/taichung.py
 

### 講者：
KuRin_J (クリーン_J)

聯絡方式: jumpmancoding@gmail.com

Senior Engineer，
擅長協助企業選擇適宜策略，提升資料素養、降低溝通成本。
願景是遇見不同的人，拓展視野，在各種細節中發現美好。


2024年的研究興趣在於(1)優化資料工程 (2)Microsoft Fabric (3)流行服飾文化 (4)日式餐飲。


### 活動前準備：

- 下載範例資料集: 擇一下載 只差別在資料筆數
    - Download Files: dbt101_small.zip ( 613KB )
        - https://drive.google.com/file/d/1kOEYP_HUqZAPQScQ1jLc6gzJYHJw0F7G/view?usp=drive_link
    - Download Files: dbt101_big.zip ( 26MB )
        - https://drive.google.com/file/d/1mTWJfUyhfb2NpYdRgQ04Q3PYnf4a6JQH/view?usp=drive_link
- 自備筆電且安裝Python 3.8.0 以上版本
- 建議筆電先行充電或自備延長線
- 建議先行在家完成套件安裝
  - 現場不提供 Wifi，若需現場安裝，請自行準備上網環境
- 了解如何使用建立及管理python虛擬環境(以下擇一)
  - pip
  - conda
 
### dbt 套件安裝：

- Python 3.8.0 以上版本
- 本次教學套件以 dbt-duckdb 1.6.1 為主
- 以下為安裝方式(以下擇一)
  - 套件安裝方式1→ pip (winddows)
  - 套件安裝方式2→ pip (Mac)
  - 套件安裝方式3→ Conda (Winddows or Mac)

 
### 套件安裝方式1→ pip (Winddows):

- 如果不理解以下指令，建議先行上網查詢相關背景知識
- mkdir workshop_202403
- cd workshop_202403
- python -m venv py38_dbt
- .\py38_dbt\Scripts\activate.bat
- pip install dbt-duckdb==1.6.1
- dbt --version


### 套件安裝方式2→ pip (Mac):

- 如果不理解以下指令，建議先行上網查詢相關背景知識
- mkdir workshop_202403
- cd workshop_202403
- python -m venv py38_dbt
- source py38_dbt/bin/activate
- pip install dbt-duckdb==1.6.1
- dbt --version


### 套件安裝方式3→ Conda (Winddows or Mac):
- 如果不理解以下指令，建議先行上網查詢相關背景知識
- mkdir workshop_202403
- cd workshop_202403
- conda create --name  py38_dbt python==3.8
- conda activate py38_dbt
- conda install   conda-forge::dbt-duckdb==1.6.1
- dbt --version

---
### Conda 安裝異常?
- pip install wheel

----
 
# dbt workshop Command

### dbt project name 

workshop101 

 
### dbt Command 

dbt init 
dbt debug
dbt run : Compile SQL and execute against the current target database.
```
dbt run --select sql_model
```

dbt seed 

dbt docs
```
dbt docs generate
- Generate the documentation website for your project
dbt serve 
- Serve the documentation website for your project
```


### dbt project name  : workshop101 

dbt init 

```   
Enter a name for your project (letters, digits, underscore): workshop101
01:13:28  
Your new dbt project "workshop101" was created!

For more information on how to configure the profiles.yml file,
please consult the dbt documentation here:

  https://docs.getdbt.com/docs/configure-your-profile

One more thing:

Need help? Don't hesitate to reach out to us via GitHub issues or on Slack:

  https://community.getdbt.com/

Happy modeling!

01:14:07  Setting up your profile.
Which database would you like to use?
[1] duckdb

(Don't see the one you want? https://docs.getdbt.com/docs/available-adapters)

Enter a number: 1
```

### Project Main Foler 

```
 Your_python_Path             
    ├─py38_dbt                
    └─workshop101             ---------  Your first dtb project Name    
        ├─models              ---------- Put Your SQL fils 
        │  └─example
        ├─seeds               ---------- Put Your CSV fils   
     
```

### Adding profiles.yml to workshop101 folder( Config: Connect to Database  ) 
```
workshop101:
  outputs:
    dev:
      type: duckdb
      path: dev.duckdb
      threads: 1
  target: dev

```

### dbt debug 


```
13:17:15  Running with dbt=1.6.10
13:17:15  dbt version: 1.6.10
13:17:15  python version: 3.10.7
13:17:15  adapter type: duckdb
13:17:15  adapter version: 1.6.1
13:17:15  Configuration:
13:17:15    profiles.yml file [OK found and valid]
13:17:15    dbt_project.yml file [OK found and valid]
13:17:15  Required dependencies:
13:17:15   - git [OK found]

13:17:15  Connection:
13:17:15    database: dev
13:17:15    schema: main
13:17:15    path: dev.duckdb
13:17:15    config_options: None
13:17:15    extensions: None
13:17:15    settings: None
13:17:15    external_root: .
13:17:15    use_credential_provider: None
13:17:15    attach: None
13:17:15    filesystems: None
13:17:15    remote: None
13:17:15    plugins: None
13:17:15    disable_transactions: False
13:17:15  Registered adapter: duckdb=1.6.1
13:17:15    Connection test: [OK connection ok]

13:17:15  All checks passed!
```

   

dbt run 

```

13:18:38  1 of 2 START sql table model main.my_first_dbt_model ........................... [RUN]
13:18:38  1 of 2 OK created sql table model main.my_first_dbt_model ...................... [OK in  
13:18:38  2 of 2 START sql view model main.my_second_dbt_model ........................... [RUN]
13:18:38  2 of 2 OK created sql view model main.my_second_dbt_model ...................... [OK in  
```

### write your SQL model

```
{{ config(materialized='table') }} 
    select random()  as num_1 , random()  as num_2 , random()  as num_3
```    

``` 
dbt run --select  your_SQL 

```

### SQL Temple
```
{{ config(materialized = 'table') }}
select *
from {{ ref('random900') }}
where num_1 > 10

```

```
{{ config(materialized = 'view') }} select * from {{ ref('p01') }} where num_2 > 30

```

```
{{ config(materialized = 'view') }} select * from {{ ref('p02') }} where num_3 > 70
```

```
{{ config(materialized = 'table') }} select * from {{ ref('p03') }} limit 700
```

```
{{ config(materialized = 'view') }}
select *, (num_3 * 2.5 + num_2 * 7.2 + num_1 * 2.31) as num_4
from {{ ref('p01') }}
```

```
{{ config(materialized = 'table') }} select num_4 from {{ ref('p05') }} order by num_4

```

---- 

# sample dataset

### coffee_sales.csv

```
Columns Name:
'p_date', 'c_id', 'p_id', 'p_q', 'xx1', 'xx2', 'xx3', 'xx4', 'xx5',
       'xx6', 'p_price', 'sales', 'day_name', 'first_day_of_week',
       'month_of_year', 'day_of_month', 'month_name', 'year_key'
       
       

         p_date  c_id  p_id  p_q   xx1    xx2      xx3   xx4  xx5 xx6  p_price  sales   day_name first_day_of_week  month_of_year  day_of_month month_name  year_key
0     2018-09-04    45     5    3  5JhL  ZVTxK  SF883ot  UQwZ  o4c  jJ      118    354    Tuesday        2018-09-03              9             4  September      2018
1     2018-07-03    38     5    8  vr64  iXAvX  V3zsSkr  yXD0  0KD  W4      118    944    Tuesday        2018-07-02              7             3       July      2018
2     2015-06-11    41     3    2  oovz  CIHlT  6HDzXaf  VmJ8  spC  DF       80    160   Thursday        2015-06-08              6            11       June      2015
3     2016-10-21    42     3    6  cyKp  sXiEl  D2PsGhX  Aar8  i48  7r       80    480     Friday        2016-10-17             10            21    October      2016
4     2013-04-06    38     2    6  WNoc  2STlO  M8sdmva  Mrt8  Vk8  xa       50    300   Saturday        2013-04-01              4             6      April      2013
```


### coffee_customer.csv
```
Columns Name:
'customer_name', 'address', 'phone', 'email', 'birthday', 'c_id'


       customer_name                                            address                  phone                       email    birthday  c_id
0      Raymond Gomez      5397 Ronald Keys Suite 813\nLeestad, TX 46384             7557206326  jeffreyswanson@example.com  1980-09-19     1
1   Christine Miller  194 Joseph Mountain Apt. 791\nFloresville, ME ...   +1-503-558-1030x9385         donna54@example.org  1947-04-25     2
2        Mark Nelson  76129 Michelle Mews Suite 600\nSinghport, WA 7...  001-541-702-5291x3501        vvasquez@example.org  1935-04-12     3
3      Kirk Thompson                             USNS Roy\nFPO AP 85218           456-803-6514       scardenas@example.org  1967-04-03     4
4           Amy Todd        246 James Streets\nWest Christina, GA 98801           394-971-6024     brownkelsey@example.org  1986-04-13     5
```


### coffee_product.csv
```
Columns Name:
'p_id', 'p_name_zh', 'p_name_en', 'p_area'



   p_id p_name_zh        p_name_en    p_area
0     1      美式咖啡  American Coffee  Colombia
1     2        拿鐵            Latte  Colombia
2     3      卡布奇諾       Cappuccino    Brazil
3     4      摩卡咖啡            Mocha    Brazil
4     5    義式濃縮咖啡         Espresso    Brazil
```