<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Alvin Lau on Medium]]></title>
        <description><![CDATA[Stories by Alvin Lau on Medium]]></description>
        <link>https://medium.com/@alvinlau?source=rss-df17508db1f9------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*KmEEhN_qJsUhWxuEpkpSdQ.png</url>
            <title>Stories by Alvin Lau on Medium</title>
            <link>https://medium.com/@alvinlau?source=rss-df17508db1f9------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 06:13:18 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@alvinlau/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[BigQuery 101：Assert 斷言｜如何設計 SQL 的 Unit Test？]]></title>
            <link>https://medium.com/@alvinlau/bigquery-101-assert-%E6%96%B7%E8%A8%80-%E5%A6%82%E4%BD%95%E8%A8%AD%E8%A8%88-sql-%E7%9A%84-unit-test-5fe5ef736af5?source=rss-df17508db1f9------2</link>
            <guid isPermaLink="false">https://medium.com/p/5fe5ef736af5</guid>
            <dc:creator><![CDATA[Alvin Lau]]></dc:creator>
            <pubDate>Mon, 17 Apr 2023 13:51:08 GMT</pubDate>
            <atom:updated>2023-05-06T07:48:24.462Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*CW6nP-F80q-2ZJtr.png" /><figcaption>PC：<a href="https://levelup.gitconnected.com/">https://levelup.gitconnected.com</a></figcaption></figure><h3>為什麼 SQL 需要 Unit Test？</h3><blockquote>我怎麼知道資料有錯？</blockquote><p>在去年的 6 月，我會覺得「對資料的敏感度」很重要。當資料出來的量級不對（如：平台只有 200 萬用戶，人均用時 1 hr，但 6 月的總使用時長是 300 萬小時！？），或資料筆數異常（如：LEFT JOIN 之後筆數增加到 n * 20！？顯然 JOIN 的表有一對多的問題！），就要趕快回頭 debug。</p><p>身為資料工程師，幾乎每天都在面對資料品質（Data Quality）的挑戰，這不奇怪。不過，總是要一個一個項目思考，有時候也很累。針對這種事，<em>Google 的軟體之道 </em>就提到，敏捷開發的其中一個原則，就是 DRY Principle，也就是：</p><blockquote>Don’t Repeat Yourself.</blockquote><p>因此，為了替資料建立足夠的可靠度，Unit Test 是我在開發過程中累積「敏感度」的好夥伴。任何我曾經遇過的麻煩，我都可以將他們自動化整理起來。在之後的開發 / 重構的過程中，他們就能幫我確保 output 的正確性，建立可靠的資料品質。</p><h3>什麼是斷言（Assert）？</h3><p>BigQuery 的語法中，將語句（Statement）分成 5 大類（DDL、DML、DCL、Procedure Language &amp; Debugging Statement）。</p><p>其中，Debugging Statement 裡面的 ASSERT，就是建立 Unit Test 時很好的工具。我們先來看看 BigQuery 的文件中，如何規範這個語句：</p><pre>ASSERT expression [AS description]</pre><ul><li>expression 必須返回 bool 值</li><li>description 必須是 string 值（中括號 [] 代表可選）</li><li>若 expression 返回 false / null，則 ASSERT 會報錯（default）或返回 description</li><li>若 expression 返回 true，則不會顯示任何結果</li></ul><pre>ASSERT<br>  EXISTS(<br>    SELECT X<br>    FROM UNNEST([7877, 7879, 7883, 7901, 7907]) AS X<br>    WHERE X = 7919<br>  )<br>AS &#39;Column X must contain the value 7919&#39;;</pre><p>舉例而言，EXISTS 會返回一個 bool 值，而透過 EXISTS，我可以檢驗 SQL 回傳的狀態，是否符合我的預期。</p><h3>實際案例：活動資料查詢表</h3><p>業務需求：「Hi Data Team，目前行銷正在舉辦一場活動。不過，我需要請你設計一張查詢表，讓我快速查詢報名用戶在平台上的身分訊息。請放心，能報名活動的用戶，必須是註冊用戶才行。」</p><pre>-- BigQuery Syntax  <br>   SELECT *<br>     FROM registration_form<br>LEFT JOIN user_data<br>    USING (user_id)</pre><p>通常，我們會做幾個檢查。</p><ol><li>報名表的 user_id 不能重複</li><li>報名表的筆數，與 JOIN 完之後的筆數要相同</li><li>每一個用戶都應該有 JOIN 出東西才對</li></ol><pre>-- BigQuery Syntax  <br>CREATE OR REPLACE TABLE `dataset.campaign_full_table` AS<br>   SELECT *<br>     FROM registration_form<br>LEFT JOIN user_data<br>    USING (user_id);<br><br>-- user_id should be unique<br>ASSERT(<br>  (<br>  SELECT COUNT(*)<br>    FROM registration_form<br>  )<br>  =<br>  (<br>  SELECT COUNT(DISTINCT user_id)<br>    FROM registration_form<br>  )<br>) AS &quot;user_id in registration_form should be UNIQUE&quot;;<br><br>-- row numbers should remain the same after joining<br>ASSERT(<br>  (<br>  SELECT COUNT(*)<br>    FROM registration_form<br>  )<br>  =<br>  (<br>  SELECT COUNT(*)<br>    FROM campaign_full_table<br>  )<br>) AS &quot;row numbers should remain the same after joining&quot;;<br><br>-- every player should have their first_visit_date<br>ASSERT(<br>  (<br>  SELECT COUNT(*)<br>    FROM registration_form<br>  )<br>  =<br>  (<br>  SELECT COUNT(first_visit_date)<br>    FROM campaign_full_table<br>  )<br>) AS &quot;every player should have their first_visit_date&quot;;</pre><p>接著，BigQuery 會依照順序執行斷言。如果第一個 test 沒有通過，就會報錯。如果三個都順利通過，則會亮起三個綠燈。</p><p>讓我們用簡單的數學邏輯來示範：</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u0HpZtkFZeJlqnrTcQl6sg.png" /><figcaption>第一個就報錯</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Rx4VFBTzFQ_tszA3EeGKkA.png" /><figcaption>這次到第三個 test 才出錯</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*980GGKxaPP945DoqaccO4Q.png" /><figcaption>三個 test 都順利通過</figcaption></figure><h3>最後：如何將 Test 加入 Data Pipeline？</h3><p>如果每次都要重新寫 test，那我還不如就手動觀察就好。我自己偷懶的方法，是寫 UDF（user defined function），來把 test code 存起來，並且將 SQL Statement 設成參數，傳入 function 當中。</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5fe5ef736af5" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>