之前，我們已經討論過，現代資料棧 (modern data stack) 用 ELT 取代 ETL ，所以資料轉換 (transformation) 的這一段工作，會透過 SQL 直接在資料倉儲裡完成。另一方面，之前在技術棧 (technical stack) 決策時，也提出了一些對 SQL 的質疑：

1. 該怎麼組合大量的 SQL 呢？
2. 如果說，應用案例，剛好需要動態地去生成 SQL ，比方說，要 union 三個不同的 SQL query 呢？
3. 該怎麼整合版本控管軟體呢？

接下來，我們就先從上述三個題目來討論解決方案。

組合 SQL

考慮軟體開發的類比

當我們開發有一定規模的軟體時，我們勢必需要做一定的模組化與抽象化。

以模組化來講：我們會把重複使用的程式碼，變成子程序 (sub program)，之後只要提供子程序一些引數 (argument) ，就可以呼叫這段子程序，如此一來，程式碼就不會一再地重複，程式的總行數就可以大幅縮短。

以抽象化來講：我們往往會把我們模組化的子程序，給予一些有意義的命名，這樣子，當我們在閱讀程式時候，只要讀到子程序的名稱，就可以大致上了解它在做什麼，如此，不但可以大幅減少人類認知的負擔，也可以讓子程序更容易被重複使用。

SQL 本來就有提供視圖 (view) 的機制：「我們可以把任意的 SQL 查詢 (query)，變成一個對應的視圖 (view)，並且為其命名。」從這個角度來看， SQL view 本身就是一種模組化與抽象化機制。

• 模組化機制：我們可以把某個查詢變成視圖，其它的查詢又可以利用這個視圖的結果。
• 抽象化機制：我們可以對視圖加以命名。

綜合上述，SQL 的 view 機制，就已足以讓我們靈活地組合小的查詢而生成複雜的資料轉換，同時，我們所需要的資料建模 (data modeling)，恰好可以由大量的視圖 (view) 來構成。

動態生成 SQL

假設某公司的軟體產品是 ERP 系統。ERP 資料庫裡，每個用戶的所有的資料都放在一個獨立的 database schema 之內。(註：此處的 database schema 是一種『命名空間』)

如果我們需要分析該公司所有用戶的 ERP 系統裡訂單 (invoice) 資料時，我們可以怎麼做呢？

先假設該公司所有的用戶只有 A, B, C 三個用戶，同時訂單表的欄位只有 w, x, y, z

SELECT w, x, y, z, "A" AS user_name FROM A.invoices
UNION ALL
SELECT w, x, y, z, "B" AS user_name FROM B.invoices
UNION ALL
SELECT w, x, y, z, "C" AS user_name FROM C.invoices

顯然，訂單 (invoice) 表的欄位有可能是有限的個數，而用戶的數量卻很有可能動態地不停地增加。也因此，考慮用戶的數量會一直變動，我們應該要動態生成 SQL。下方是利用 jinja 這樣子的樣板語言 (template language) 來動態生成 SQL。

{% set users = ["A", "B", "C"] %}

{% for user in users %}
  SELECT w, x, y, z, '{{user}}' AS user_name 
  FROM A.invoices
  {{ "UNION ALL" if not loop.last }}
{% endfor %}

在上述的 jinja 語言範例，如果日後用戶的數目改變，只需要改變 {% set users = ... } 這一行即可以，甚至 {% set users = ... } 這一行的資料來源，也可以從資料庫的查詢來取得。

輔助程式語言

要動態生成 SQL 可以選擇各式各樣的程式語言來做這件事，然而，該用什麼樣子的語言來輔助動態生成 SQL 最合理呢？自家公司最主要的通用型程式語言嗎？還有更好的選項嗎？

如果選擇像 jinja 這樣子的樣板語言 (template langauge) 而非通用型程式語言 (general purpopse language) 的話，可以有下列的優點：

1. 樣板語言的語法相對少，因為只適合處理樣板類的應用情境。
2. 語法相對少、所以也相對容易學習。
3. 專案會變得對通用型程式語言 (GPL) 顯得語言無關 (language agnostic)
4. 日後，負責維護此資料建模專案的人，進入門檻會比較低。

版本控管

如果要有系統地透過 SQL 來做出資料建模，我們需要做下列的事：

1. 準備一個資料建模資料夾，裡頭放的檔案都是一個又一個的 ${model_name}.sql 檔。而檔案的內容，每個都是一個 SQL 查詢，它會生成對應的 SQL 視圖。
2. 準備一個組態設置檔 (config.yaml)，裡頭放一些參數，這些參數可能是用來指定資料倉儲的連線方式，包含使用者名稱、密碼、主機名稱等。
3. 準備一隻程式，它會結合「組態設置檔」與「資料建模資料夾」內的檔案，透過執行 jinja 語法，來產生確實可以直接對資料倉儲執行的 SQL 檔。
4. 把步驟 3 產生的 SQL 檔，照著它們彼此相依 (dependency) 的順序，去對資料倉儲執行。

顯然，如果我們對專案做版本控管的話，只需要把前述的「資料建模資料夾」、「組態設置檔」納入版本控管即可。

結語

讀者讀到這邊，可能會覺得，「怎麼我覺得上述的這些作法，看似合理，就是要做好的話也是不少的程式碼要寫？」

這邊有一個好消息，你需要寫的程式碼可以非常少，因為你只要在電腦裡安裝 dbt (data build tool)，上述的很多事的細節，dbt 都幫你做好了。換言之：「組合 SQL 」、「動態生成 SQL 」、「資料夾的設計配置」，這些事，dbt 都有預設的範例與習慣作法。

其它資源

1. 對 dbt 或 data 有興趣 👋？歡迎加入 dbt community 到 #local-taipei 找我們，也有實體 Meetup 請到 dbt Taipei Meetup 報名參加
2. 歡迎訂閱 PruningSuccess 電子報，主要談論軟體開發、資料處理、資料分析等議題。