Be Careful with @run_date in BigQuery Scheduled Queries – Especially in KST/JST!

In Google BigQuery, the @run_date function automatically sets the reference date for a query to the time it is executed, but only when run manually from the Scheduled Queries UI. When set to run automatically at a scheduled time without explicitly setting the timezone, the query runs in UTC. This can lead to unexpected behavior, especially if the query runs across midnight UTC, resulting in @run_date evaluating to the previous date in local time. This is particularly problematic for countries like South Korea or Japan, which are UTC+9. To avoid this issue, it is essential to set the timezone explicitly when scheduling queries. Additionally, using @run_time instead of @run_date allows for controlling the exact time with timezone. When using @run_time, it is not necessary to wrap with DATE() unless required. Examples of setting the timezone include DATE(@run_time, 'Asia/Seoul') and DATE(@run_time, 'Asia/Tokyo'). Many datamarts built without timezone awareness will require fixes, but setting the timezone explicitly can prevent such problems.