資料建模
目標平台：PostgreSQL、BigQuery、Snowflake。轉換工具：dbt。本地原型測試：DuckDB 2.0。
關於 dbt 的機制（模型配置、測試 YAML、快照、語意層、v2 遷移），請使用此 repo 的 de-vendors 技能引入的官方 dbt Labs 技能。本技能決定「要建模什麼」，而非 dbt 如何拼寫它。
流程
- 說明商業問題與消費者（儀表板、ML 特徵、財務結算）。
- 在定義任何欄位之前，先將粒度寫成一句話：「每個訂單行每天一行」。如果這句話需要「或」字，則代表需要分成兩個資料表。
- 選擇事實類型（如下），然後列出在該粒度下為真（true）的維度。
- 命名粒度鍵（自然鍵欄位或它們的雜湊值）。它需要具有 unique + not_null 約束。
- 在 DuckDB 上針對樣本資料建立原型，執行 Fan-out 檢查，然後再移植。
- 在宣告完成之前，加入「需要測試什麼」中列出的測試。
粒度
- 先定義粒度，再定義欄位。每個度量（measure）和每個外鍵（foreign key）在該粒度下必須是單值的；不屬於該粒度的欄位應放在另一個資料表中，因為它會默默地使資料行數倍增。
- 維持原始資料來源支援的最低粒度。彙總（aggregates）可以從明細（detail）推導出來，但明細無法從彙總推導出來。將彙總表建立為獨立的資料超市。
- 切勿在一個資料表中混合不同的粒度（例如在訂單行資料列上重複訂單標頭總和）。對訂單行資料列加總標頭金額會導致重複計算；應分別為標頭和明細建立事實表。
事實 vs 維度
- 事實：一個事件或測量值；應該是數值型、盡可能可加（additive），並以維度鍵加上退化維度（degenerate dimension，如訂單號碼）作為鍵值。
- 維度：用於篩選和分組的描述性上下文。寬且反正規化的維度是可以接受的。
- 對每個度量進行分類：可加（additive，可跨所有維度加總）、半可加（semi-additive，如餘額：不能跨時間加總）、不可加（non-additive，如比率：儲存分子和分母，在彙總後再進行除法）。
- 事實類型：
- 交易事實（Transaction）：每個事件一行。預設選擇。
- 定期快照（Periodic snapshot）：每個實體每個期間一行（如每日餘額）。當狀態很重要且事件稀疏或延遲送達時使用。
- 累積快照（Accumulating snapshot）：每個流程實例一行，每個里程碑有一個日期欄位，並在原地更新。用於具有已知生命週期的流程（例如已下單、已出貨、已送達）。預期會有更新；確保載入作業是等冪的（idempotent）。
- 無事實事實（Factless fact）：沒有度量的事件或覆蓋範圍（如出席率、資格）。計算資料列數。
綱要形狀
- 預設使用星狀綱要（Star Schema）：一個事實表，加上平坦的維度表。僅當子維度（sub-dimension）是共享的且體積龐大，或由另一個團隊維護時，才考慮使用雪花綱要（Snowflake，即正規化的維度表）；額外的 Join 會損害可讀性和 BI 工具的相容性。
- 一致維度（Conformed dimensions）：一個
dim_customer、dim_date被所有事實表共享，具有相同的鍵和屬性意義。只建立一次；如果有第二個規則不同的客戶表，這是一個缺陷，因為跨事實的報表會不一致。 - 角色扮演維度（Role-playing dimensions，例如下單日期、出貨日期）透過不同的外鍵欄位重複使用同一個
dim_date。 - 使用垃圾/小型維度（Junk/mini dimensions）來處理低基數的標誌欄位；不要在事實表上保留 8 個布林欄位。
- 未知成員（Unknown member）：在每個維度表中保留一列（鍵值為
-1或 'unknown' 的雜湊值），以便延遲送達的事實資料能保留其資料列。與維度表進行 Inner Join 會默默地丟棄事實資料；應使用 Left Join 並透過 coalesce 來使用未知鍵。
鍵
- 自然鍵（Natural key）：來源系統的商業識別碼。始終將其保留為一個屬性。
- 代理鍵（Surrogate key）：當自然鍵是複合鍵、可能變更、來自多個來源，或者維度是 SCD2（一個實體有多個版本）時，加入代理鍵。在 dbt 風格的資料倉儲中，相對於序號（sequences），偏好使用自然鍵（加上 SCD2 的 valid_from）的確定性雜湊（deterministic hash），這樣重新執行和平行建置才能保持一致。
- 不要將代理鍵作為識別碼暴露給商業使用者；它們會在重新建置時變更。
緩慢變更維度
- 類型 1（覆蓋）：當歷史記錄沒有分析價值，或者變更只是更正時使用。
- 類型 2（每個版本新的一行，包含 valid_from / valid_to / is_current）：當必須根據事件時間的屬性值來報告事實時使用（例如購買時的客戶等級）。
- 當來源不保留歷史記錄，且您只需要從現在開始捕捉變更時，快照（Snapshots）就足夠了；它們是捕捉機制，而 SCD2 維度仍然是建立在其上的模型。如果來源已經有歷史記錄或事件日誌，則應從中推導有效範圍，而不是進行快照。
- 將事實連接到 SCD2 的條件：鍵值加上
event_ts >= valid_from AND event_ts < valid_to（半開放區間，避免重疊）。對每個自然鍵測試是否有重疊或間隙的區間。 - 類型 3 或按屬性類型 2：僅當一個單一的「先前值」欄位就能回答問題時才使用；否則跳過。
Fan-out 與重複計算
原因：連線粒度不匹配的資料表，導致「一」的那一方實際上是「多」。症狀：總和大於原始來源，count(*) 在 Left Join 後增加，平均值改變。
- 偵測：在每次 Join 前後比較
count(*)和count(distinct grain_key)；在連接到維度表後，兩者必須相等。在維度端測試 Join 鍵的唯一性（select key from dim group by 1 having count(*) > 1）。 - 對帳：資料超市中某個度量的總和，在相同篩選條件下應等於原始來源的總和。
- 修復方法：在 Join 之前，先將多的一方彙總到事實的粒度，或者透過具有分配權重的橋接表（bridge table）進行 Join；切勿使用
distinct來隱藏結果。 - 多對多關係（客戶到帳戶，產品到類別）：使用帶有權重因子的橋接表，或者選擇一個主要的並加以說明。
- 這些檢查的 SQL：請參閱 references/fan-out-checks.md。
分層
- raw/staging：每個來源資料表對應一個 staging 模型；進行重新命名、型別轉換、去重，不做 Join，不包含商業邏輯。保持 raw 層不變。
- intermediate：進行 Join、重塑結構、以及被兩個以上資料超市共用的邏輯。不暴露給 BI。
- marts：事實表和維度表，每個資料超市對應一個商業流程，有文件、測試、合約（contract）。只有 marts 是公開的。
- 依賴關係是單向的；marts 不能讀取 raw 層，staging 層也不能讀取其他 staging 層。
- 將 staging 模型實體化為檢視（view），marts 實體化為資料表（對於大型且粒度鍵穩定的表，使用增量（incremental）模式）。
命名
stg_<source>__<entity>、int_<entity>_<verb>、fct_<process>、dim_<entity>；使用蛇形命名法（snake_case）；單數或複數保持一致（選擇一種）。- 鍵：
<entity>_id（自然鍵）和<entity>_key（代理鍵）；時間戳記：<event>_at（使用 UTC）；日期：<event>_date；布林值：is_/has_；金額需附帶單位或貨幣（amount_usd）。
需要測試什麼
- 粒度鍵：每個模型上都必須有 unique + not_null 約束；對於複合粒度，測試其組合。
- 事實表上的每個外鍵（FK）：not_null（或未知成員）以及連接到維度表的關係測試。
- 狀態/類型欄位的接受值測試；新值會失敗並發出警示，而不是被錯誤分類。
- 每個資料超市與其原始來源之間的資料列數和總和對帳，並說明允許的誤差範圍（財務相關為零誤差）。
- SCD2：沒有重疊的區間，每個自然鍵恰好有一行當前的記錄。
- 來源表的時效性（Freshness）測試。在樣本資料上斷言（break）每個測試一次，以證明它能夠失敗。
OLTP 綱要審查（PostgreSQL）
- 交易表正規化到 3NF；僅在經過衡量的讀取路徑下進行反正規化。每個資料表都必須有主鍵。
- 為外鍵欄位建立索引：PostgreSQL 不會自動為外鍵建立索引，未建立索引的外鍵會使父表刪除操作和 Join 操作掃描子表。
- 使用
timestamptz、numeric處理貨幣、text取代任意長度的 varchar；透過 NOT NULL、CHECK、UNIQUE 和外鍵約束來強制執行規則，而不是僅依賴應用程式程式碼。 - 對於大量插入的資料表，偏好使用
bigintidentity 或 UUIDv7 鍵，而非隨機的 UUIDv4（索引局部性考量）。 - 多租戶：將
tenant_id放在每個租戶擁有的資料表上，使其成為複合鍵和索引的前導欄位，並將其包含在外鍵中（複合外鍵），以便無法進行跨租戶引用，並配合列級安全性（RLS）。預設使用含 tenant_id 的共享綱要；僅在需要嚴格隔離或每個租戶獨立還原的需求下，才使用每個租戶獨立綱要或資料庫的方案。 - 軟刪除：使用部分唯一索引（
where deleted_at is null），以便唯一性約束仍然有效。
陷阱與觀察到的失敗模式：references/gotchas.md