Here's an interesting situation I've seen with BigQuery.

Say a source system provides JSON events with timestamps at microsecond grain (6 decimals, so something like 2024-01-01 14:00:00.123456).

This is cast using SAFE_CAST into a proper TIMESTAMP. All works just fine.

Until one day the source system sends the JSON with timestamps at NANOsecond grain.
Since timestamp has only MICROsecond precision, the cast quietly fails (no error), a null is returned from a seemingly correct looking timestamp.

Without proper monitoring this issue can go unnoticed quite a bit. So watch out 🤔

It just drives the point home on how important is to have proper monitoring in place and enforcing a robust data contract with data sources.

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.