Day 11 談到設備事件和感測讀數時，我開始注意資料格式背後的定義：溫度使用什麼單位、時間由設備還是接收系統記錄、重送事件如何辨認。等到真的要設計清洗規則，我才發現知道問題在哪裡，與知道該怎麼處理，是兩件不同的事。
一筆讀數無法轉成數字，我可以讓它不進分析表；但它可能是設備故障訊號，也可能是新版韌體改變了輸出方式。如果我只追求一張沒有空值的表，這些線索會在清洗時消失。我因此把問題改成：每筆異常資料究竟應該修正、隔離待查，還是拒絕進入下游資料表？
在這次 BigQuery SQL 清洗實作中，我先用 SELECT 預覽轉換結果，確認欄位的型別與值符合預期，再以 CREATE OR REPLACE TABLE AS SELECT 建立新的資料表。SQL 則用 CTE（Common Table Expression，通用資料表運算式）把工作分開：先解析欄位，再判斷資料是否有效，最後處理重複紀錄。BigQuery 官方文件也支援從查詢結果建立或重建資料表。
這種分段方式對我最有幫助的地方，是每一步都能被單獨檢查。如果解析後的時間不對，我先回頭看解析規則；如果輸出資料少了，我能追查是有效性條件，還是去重規則造成。把所有條件塞進最後一行 WHERE，雖然也可能得到一張表，卻很難解釋資料在哪一步改變。
延伸到設備事件情境，我會讓解析階段保留原始讀數與解析後讀數，先比較兩者；確認單位及格式後，再決定是否讓它進入供分析的表。這是設計上的延伸，不是宣稱我已經跑出一批設備資料的清洗結果。
實作中用到的 SAFE_CAST，能在轉型失敗時回傳 NULL，避免整個查詢因單一值而中斷。Google Cloud 官方文件也清楚區分：一般 CAST 可能讓查詢失敗；SAFE_CAST 對執行時的轉型錯誤回傳 NULL。
我起初很容易把「查詢跑完」看成「資料已處理」。但一個 NULL 至少可能有兩種來源：設備原本沒有送出讀數，或設備送出了內容、只是我目前的規則無法解析。兩者的處置不該相同。前者可能要檢查設備是否漏報；後者可能要更新格式規則。若轉型後只留下 NULL，不保留原值及失敗原因，下次看到異常時仍得重新猜測。
因此，安全轉型的用途是讓我有機會看完所有資料並找出問題，而不是安靜地把問題變成空值。
有些差異可以修正。例如來源已明確標示溫度單位，我就能按照約定換算，並保留原值供追查。有些資料則應先隔離：讀數存在，卻缺少能確認是哪台設備送出的識別資訊，我無法放心把它歸入某台設備的趨勢。有些值超出預期範圍，也未必該立刻刪除；它可能正是維修人員需要調查的事件。
我從實作中的篩選條件得到的提醒是：WHERE 後面每增加一個條件，都等於做了一個資料處置決定。正式使用時，我希望能記錄不符合條件的資料及原因，而不是只留下通過的部分。BigQuery data preparation 官方文件提供了相近的處理方向：驗證失敗的資料可導向 error table（錯誤表）；若未設定錯誤表，驗證失敗會使執行失敗。這是我對未來流程的延伸，並非本次已操作的功能。
有些差異可以修正。例如來源已明確標示溫度單位，我就能按照約定換算，並保留原值供追查。有些資料則應先隔離：讀數存在，卻缺少能確認是哪台設備送出的識別資訊，我無法放心把它歸入某台設備的趨勢。有些值超出預期範圍，也未必該立刻刪除；它可能正是維修人員需要調查的事件。
我從實作中的篩選條件得到的提醒是：WHERE 後面每增加一個條件，都等於做了一個資料處置決定。正式使用時，我希望能記錄不符合條件的資料及原因，而不是只留下通過的部分。BigQuery data preparation 官方文件提供了相近的處理方向：驗證失敗的資料可導向 error table（錯誤表）；若未設定錯誤表，驗證失敗會使執行失敗。這是我對未來流程的延伸，並非本次已操作的功能。
這次實作最值得保留的驗收方式，是對照清洗前後的資料，交代每筆未進入乾淨表的紀錄在哪個階段、因什麼規則被處理。把方法移到設備事件情境，我會分別記錄接收事件、成功解析事件、判定為重送的事件，以及隔離待查的事件。這些分類不能只在開發當天算一次；規則改變後也應重新核對。
當規則仍在修正，從保留的來源資料重建結果，比直接覆寫來源更容易檢查變更造成的影響。Google Cloud 的資料轉換文件也把 SQL 工作流程的測試、版本管理與依賴管理列為後續可採用的能力。
做完這一輪思考，我對「乾淨資料」的要求變得更具體：不只是每個欄位都有漂亮的型別，而是每筆資料為何被修正、保留或隔離，都有可檢查的理由。換到其他雲端平台，這個判斷仍然成立；工具可以執行規則，但規則的責任不能交給工具猜。
iThome鐵人賽
看影片追技術