Watch out when using SAFE_CAST in BigQuery

UpdatedMarch 2, 2026

•1 min read

Watch out when using SAFE_CAST in BigQuery

Constantin Lungu

Senior Data Engineer • Contractor / Freelancer • GCP & AWS Certified

Part of seriesPractical SQL

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.

Comments

Join the discussion

No comments yet. Be the first to comment.

Practical SQL

Part 1 of 50

Short, practical posts on SQL and BigQuery — from core language features to advanced query patterns. A reference for data practitioners at every level.

More from this blog

pre_ and post_operations in Dataform

Here's a useful Dataform concept: pre_operations and post_operations. As the name implies, these represent a set of actions that run before and after the main operation (table, view, or SQL operations

May 20, 20261 min read

pre_ and post_operations in Dataform

BigQuery Object Tables: A Practical Introduction

BigQuery has always been a SQL engine for tabular data. Object tables add an interesting twist to that. Instead of rows containing values, an object table gives you one row per file — pointing at da

May 15, 20262 min read

BigQuery Object Tables: A Practical Introduction

BigQuery BigLake Tables Explained: What They Are and When to Use Them

Query your data lake with warehouse-grade security and performance — without moving a single file.

Apr 26, 20264 min read

BigQuery BigLake Tables Explained: What They Are and When to Use Them

BigQuery Saves Your Query Results — Here's How to Find Them

Ever run a heavy BigQuery SQL query, processed gigabytes of data — and then accidentally closed the tab or forgot to save the results? 😬 Don't re-run it. Your results are still there. BigQuery automa

Mar 5, 20261 min read

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

You can use query parameters in BigQuery hashtag#SQL (now in the console as well!) — but how are they different from variables, and when should you use each? Both parameters and variables act as place

Feb 7, 20261 min read

Parameters in BigQuery

D

Datawise — SQL, BigQuery & Python for Data Engineers

207 posts

Data Engineer with a passion for transforming complex data landscapes into insightful stories. Here on my blog, I share insights, challenges, and the ever-evolving dance of technology and business.