How to CAST in BigQuery

Galaxy Glossary

How do I use CAST to convert data types in BigQuery?

CAST converts a value from one data type to another at query time in BigQuery.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why use CAST in BigQuery?

CAST converts values between types so you can compare, join, and aggregate columns that otherwise would be incompatible, all without changing the underlying table schema.

What is the exact CAST syntax?

Place CAST(value AS target_type) or use value::target_type inside SELECT, WHERE, JOIN, or GROUP BY clauses. You may also apply SAFE_CAST for NULL-on-error behavior.

How do I convert order dates stored as STRING to DATE?

Wrap the column with CAST or SAFE_CAST, then use the result in further logic.

SELECT id, CAST(order_date AS DATE) AS order_dt FROM Orders WHERE CAST(order_date AS DATE) > CURRENT_DATE() - INTERVAL 30 DAY;

Example join with different key types

If customer_id is STRING in Orders but INT64 in Customers, cast one side so they match.

SELECT c.name, o.total_amount FROM Orders o JOIN Customers c ON CAST(o.customer_id AS INT64) = c.id;

How to cast NUMERIC prices to FLOAT64 for math?

Use CAST when mathematical functions require floating-point values.

SELECT id, name, CAST(price AS FLOAT64) * 1.08 AS price_with_tax FROM Products;

Best practices for using CAST

Prefer SAFE_CAST when data can be dirty, cast columns once in CTEs to avoid repetition, and document any implicit assumptions about formats (e.g., YYYY-MM-DD).

What mistakes should I avoid?

Do not rely on implicit casts—BigQuery performs fewer automatic conversions than PostgreSQL. Also avoid casting inside filters on large tables without using partitions or clustered columns; it can disable pruning.

Is there an alternative to CAST?

Use PARSE_* functions for parsing formatted strings (e.g., PARSE_DATE, PARSE_TIMESTAMP) when you need format masks rather than straightforward type conversion.

Why How to CAST in BigQuery is important

How to CAST in BigQuery Example Usage


-- Identify high-value orders in last month when dates are stored as STRING
WITH recent_orders AS (
    SELECT id,
           CAST(order_date AS DATE) AS order_dt,
           total_amount,
           customer_id
    FROM   Orders
    WHERE  CAST(order_date AS DATE) BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
                                         AND CURRENT_DATE()
), high_value AS (
    SELECT *
    FROM   recent_orders
    WHERE  total_amount > 500
)
SELECT c.name,
       hv.total_amount,
       hv.order_dt
FROM   high_value hv
JOIN   Customers c
  ON   CAST(hv.customer_id AS INT64) = c.id
ORDER  BY hv.total_amount DESC;

How to CAST in BigQuery Syntax


CAST(value AS target_type)
value::target_type  -- PostgreSQL-style shorthand
SAFE_CAST(value AS target_type)  -- returns NULL instead of error

-- Ecommerce examples
SELECT CAST(order_date AS DATE)      -- Orders.order_date STRING → DATE
SELECT CAST(customer_id AS INT64)    -- Orders.customer_id STRING → INT64
SELECT SAFE_CAST(price AS FLOAT64)   -- Products.price NUMERIC → FLOAT64

Common Mistakes

Implicit casting assumptions: BigQuery won’t automatically convert STRING to INT64 in joins. Fix by wrapping the column in CAST or SAFE_CAST.
Casting partition columns in predicates: applying CAST(Order_date AS DATE) on an unpartitioned STRING column forces full table scans. Store dates as DATE or convert in ETL.

Frequently Asked Questions (FAQs)

Can I cast to ARRAY or STRUCT types?

No. CAST only supports scalar conversions. Use ARRAY or STRUCT constructors instead.

What happens when CAST fails?

Regular CAST raises an error and aborts the query. SAFE_CAST returns NULL so downstream logic can handle bad rows.

Is CAST deterministic?

Yes. Given the same input and target type, CAST returns the same output every time.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie