dbt Materializations 資料實體化策略：View, Table, Incremental 與 Ephemeral

dbt 預設會將 model 建立為 View。但在實際專案中，我們可能希望將資料實體化為 Table 以提升查詢效能，或者使用 Incremental 增量更新來處理大數據。

這些「將 SQL 轉換為資料庫物件的策略」，在 dbt 中就被稱為 Materializations。

dbt 內建了四種主要的 materialization 類型：

1. View (預設值)：每次查詢時即時運算。優點是快速建立、不佔空間；缺點是查詢慢。
2. Table：將結果儲存為實體資料表。優點是查詢快；缺點是每次 dbt run 都要重新建立，耗時且消耗資源。
3. Incremental (增量)：只更新或插入新資料到現有資料表中。適合處理大數據量。
4. Ephemeral (暫時)：不會在資料庫建立任何物件。像 CTE (Common Table Expression) 一樣，被引用的地方會直接展開 SQL。

如何設定 Materialization？

你有兩種方式可以設定 materialization：

1. 在 dbt_project.yml (全域/目錄層級)

這是最常見的做法，適合幫整個資料夾設定統一的規則。例如，我們希望 models/marts/ 資料夾下的所有 model 都存成 Table：

# dbt_project.yml

models:
  my_dbt_project:
    # 預設所有 model 為 view
    +materialized: view

    marts:
      # marts 資料夾下的 model 為 table
      +materialized: table

2. 在 Model 檔案內的 config() (個別層級)

如果你只想針對單一檔案設定，可以在 .sql 檔案的最上方使用 config() 區塊：

-- models/my_big_table.sql

{{ config(
    materialized='table'
) }}

select ...

config() 的優先級高於 dbt_project.yml。

自訂 Model 的 Table Name (Alias)

預設情況下，dbt 會使用 檔名 (Filename) 作為資料庫中的 View 或 Table 名稱。例如 models/marts/revenue.sql 會建立名為 revenue 的 table。

如果你希望資料庫中的表名與檔名不同（例如：檔名包含版本號，但表名要固定），可以使用 alias 設定。

使用方式

你可以在 config()區塊中設定 alias：

-- models/revenue_v2.sql

{{ config(
    materialized='table',
    alias='revenue' -- 強制指定表名為 revenue
) }}

select ...

或者在 dbt_project.yml 中設定：

models:
  my_project:
    marts:
      revenue_v2:
        +alias: revenue

關鍵注意事項：引用時仍使用檔名

這是最容易混淆的地方：當你給 Model 取了 Alias 後，其他 Model 在引用它時，ref() 內仍然要填寫「原始檔名」，而不是 Alias。

-- 正確：引用檔名
select * from {{ ref('revenue_v2') }}

-- 錯誤：不能直接引用 alias
-- select * from {{ ref('revenue') }}

dbt 會自動幫你處理對應關係，將其編譯為正確的資料庫表名 revenue。

Incremental Models (增量更新)

當資料量非常大 (例如數千萬筆 Log)，每次全量重建 Table 會非常花時間且昂貴。這時就需要 Incremental model。

運作原理

• 第一次執行：dbt 會建立表格並寫入所有資料 (同 Table)。
• 第二次以後執行：dbt 只會查詢並寫入「新」的資料。

如何實作

要使用 incremental，你需要在 SQL 中告訴 dbt 如何篩選新資料。這通常透過 is_incremental() 宏來判斷。

範例：

{{ config(
    materialized='incremental',
    unique_key='transaction_id' -- 避免重複寫入
) }}

select
    transaction_id,
    user_id,
    amount,
    created_at
from {{ ref('raw_transactions') }}

-- 只有在增量執行時，才加上這個篩選條件
{% if is_incremental() %}
  -- 這裡的篩選邏輯很重要，通常是篩選時間大於目前目標表的最大時間
  where created_at > (select max(created_at) from {{ this }})
{% endif %}

• {{ this }}：代表目標資料表本身。
• unique_key：(選填) 設定後，dbt 會先檢查 key 是否存在，若存在則更新 (Update)，不存在則插入 (Insert)。若不設定則一律插入 (Append-only)。

Ephemeral Models (暫存 CTE)

Ephemeral model 是一種特殊的 materialization，它 不會在資料庫中建立任何實體物件 (Table 或 View)。

當你 ref() 一個 ephemeral model 時，dbt 會在編譯階段，將該 model 的 SQL 程式碼提取出來，直接封裝成一個 Common Table Expression (CTE)，插入到引用它的下游 model 語法最上方。

這有點像是程式語言中的 inline function 或 import 模組，主要目的是為了程式碼重用 (Code Reuse) 與 邏輯模組化，同時保持資料庫乾淨。

適用場景

1. 輕量的中間邏輯：例如欄位重新命名、簡單的資料清洗，這些中間產物不需要開放給 BI 工具查詢。
2. 減少資料庫物件：避免在資料庫中產生數百個 view 或 table，讓架構更整潔。
3. 封裝複雜邏輯：將一段複雜的 WHERE 篩選或計算邏輯抽離出來，讓主程式碼更易讀。

範例：封裝「排除軟刪除用戶」邏輯

假設我們有一個常用的邏輯是「排除 deleted_at 不為空的用戶」，我們不想在每個 model 都重寫一次 where deleted_at is null，也不想為此特地建一個 View。

1. 定義 Ephemeral Model (models/base/base_users.sql)

在設定檔中指定 materialized='ephemeral'：

{{ config(
    materialized='ephemeral'
) }}

select
    id,
    username,
    email
from {{ source('raw', 'users') }}
where deleted_at is null

2. 下游 Model 引用它 (models/marts/active_user_metrics.sql)

像引用普通 model 一樣使用 ref()：

{{ config(materialized='table') }}

with active_users as (
    select * from {{ ref('base_users') }} -- 引用 ephemeral model
),

orders as (
    select * from {{ ref('stg_orders') }}
)

select
    u.id,
    count(o.id) as order_count
from active_users u
left join orders o on u.id = o.user_id
group by 1

3. 最終編譯出的 SQL (Compiled SQL)

當你執行 dbt run 時，dbt 會將 base_users 的程式碼直接展開，這就是送到資料庫執行的實際 SQL：

create table analytics.active_user_metrics as (

  -- dbt 自動注入的 Ephemeral CTE
  with __dbt__CTE__base_users as (
    select
        id,
        username,
        email
    from raw.users
    where deleted_at is null
  ),

  active_users as (
      select * from __dbt__CTE__base_users -- 直接使用上方的 CTE
  ),

  orders as (
      select * from analytics.stg_orders
  )

  select
      u.id,
      count(o.id) as order_count
  from active_users u
  left join orders o on u.id = o.user_id
  group by 1
);

(注意：dbt 實際生成的 CTE 名稱通常會帶有 __dbt__CTE__ 前綴以避免命名衝突)

注意事項

• 除錯較困難：因為邏輯被展開了，如果 SQL 有錯誤，報錯行數可能會與原始檔案對不上，需查看 target/compiled/ 下的檔案來 debug。
• 避免過度依賴：如果 Ephemeral model 被非常多下游引用，且邏輯複雜，可能會導致最終生成的 SQL 非常龐大，反而影響資料庫解析與執行效能。這時改用 View 或 Table 可能更好。

總結

選擇正確的 materialization 策略，是優化 dbt 專案效能的關鍵。