dbt Snapshots 實作 SCD Type 2 (Slowly Changing Dimensions)

在深入 dbt Snapshots 之前，我們先用一個生活例子來理解 SCD (Slowly Changing Dimensions) 這個概念。

假設你經營一個電商網站，使用者 Alice 住在 台北市 (Taipei)。 這一天是 2024-01-01，資料庫裡的 users 表長這樣：

過了一個月 (2024-02-01)，Alice 搬家到了 台南市 (Tainan)。

這時候，資料庫該怎麼變？這取決於你的策略：

策略一：直接覆蓋 (SCD Type 1)

這是最直覺的做法，直接把 Taipei 改成 Tainan。

• 優點：簡單，省空間。
• 缺點 (致命傷)：歷史消失了！ 如果老闆今天問：「Alice 一月份 的訂單是送到哪裡？」，你撈出來的資料會顯示「Tainan」，這就導致 歷史報表失真。

策略二：保留歷史 (SCD Type 2)

這就是我們想要的！我們不覆蓋舊資料，而是新增一筆資料，並加上「生效時間區間」。

• 舊紀錄：City 是 Taipei，但在 2024-02-01 失效了。
• 新紀錄：City 是 Tainan，從 2024-02-01 開始生效，目前有效 (null)。

dbt Snapshots 就是用來自動幫你維護這種「SCD Type 2」表格的神器。 你不需要手寫複雜的 INSERT / UPDATE 邏輯，只要設定好，dbt 幫你搞定。

建立 Snapshot

Snapshot 檔案必須放在 snapshots/ 目錄下，並且使用 .sql 副檔名。

不同於一般 model，Snapshot 是一個特殊的 Jinja block {% snapshot %}。

範例：snapshots/customers_snapshot.sql

{% snapshot customers_snapshot %}

{{
    config(
      target_database='analytics',
      target_schema='snapshots',
      unique_key='id',

      strategy='timestamp',
      updated_at='updated_at',
    )
}}

select * from {{ source('jaffle_shop', 'customers') }}

{% endsnapshot %}

設定參數說明

• target_schema: Snapshot 表要存放在哪個 schema (通常會與一般 analytics schema 分開)。
• unique_key: 資料的主鍵 (用來判斷是同一筆資料)。
• strategy: 偵測變更的策略。
  • timestamp (推薦): 依賴來源表中的 updated_at 欄位。只要該時間變新，dbt 就會記錄新版本。
  • check: 如果來源表沒有更新時間，可以指定要監控哪些欄位 (cols)。只要這些欄位值有變，就記錄新版本。

執行與觀察

我們實際來跑一次流程，看看資料庫會發生什麼事。

1. 首次執行 (Day 1)

假設今天是 2024-01-01，原始資料表 customers 只有一筆資料 (Alice, Taipei)。

執行 dbt snapshot：

dbt snapshot

資料庫會產生 customers_snapshot 表格：

dbt_valid_to 為 null，代表這是目前有效的紀錄。

2. 資料變更與再次執行 (Day 2)

過了一個月 (2024-02-01)，原始資料表的 Alice 搬家到 Tainan 了。 這時我們再次執行 dbt snapshot。

dbt 會自動偵測到 id=1 的資料有變動，於是它會做兩件事：

1. 結案舊紀錄：把原本 Taipei 那筆的 dbt_valid_to 壓上現在時間。
2. 插入新紀錄：新增一筆 Tainan 的資料，dbt_valid_from 為現在時間。

結果表變成：

這就是 dbt 自動幫你完成的 SCD Type 2 歷史保存！

如何查询

當你要查詢某個時間點的狀態時，可以使用 dbt_valid_from 和 dbt_valid_to 來過濾。

例如，想知道 2024-01-15 (搬家前) 當下所有人的狀態：

select *
from {{ ref('customers_snapshot') }}
where dbt_valid_from <= '2024-01-15'
  and (dbt_valid_to is null or dbt_valid_to > '2024-01-15')

這個查詢會準確地回傳 City = Taipei 的那筆資料，因為在 2024-01-15 當時，Alice 還在台北。這就是 Snapshot 的威力！

總結

Snapshots 是處理變動資料的神器。建議針對所有重要的 Dimension Table (如使用者、產品) 都建立 Snapshot，這對於日後的歷史分析會非常有幫助。