Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 37 Β· Architecture and Setup

BigQuery Data Types

A complete guide to GoogleSQL data types in BigQuery: INT64, FLOAT64, NUMERIC, BOOL, STRING, BYTES, DATE, DATETIME, TIMESTAMP, ARRAY, STRUCT, JSON and GEOGRAPHY, with casting rules.

Every column in BigQuery has a type, and choosing it well affects correctness, storage cost and what functions you can use. BigQuery has fewer numeric types than many databases and a few types you may not know, like STRUCT. This lesson lists them all, explains the traps (especially dates versus timestamps and floating-point money), and shows how to convert between types.

Scalar types

Type Holds Example literal
INT64 64-bit integer (aliases INT, BIGINT, SMALLINT, TINYINT) 42
FLOAT64 Double-precision float 3.14
NUMERIC Exact decimal, 38 digits, 9 after the point NUMERIC '12.34'
BIGNUMERIC Exact decimal with far larger range BIGNUMERIC '1e30'
BOOL TRUE or FALSE TRUE
STRING Unicode text (UTF-8) 'hello'
BYTES Raw bytes b'abc'
DATE Calendar date DATE '2025-03-01'
TIME Time of day TIME '14:30:00'
DATETIME Date and time without a time zone DATETIME '2025-03-01 14:30:00'
TIMESTAMP An absolute instant (stored as UTC) TIMESTAMP '2025-03-01 14:30:00+00'
INTERVAL A span such as 3 months INTERVAL 3 MONTH
JSON Semi-structured JSON JSON '{"a": 1}'
GEOGRAPHY Points, lines and polygons on Earth ST_GEOGPOINT(2.35, 48.85)

Plus the compound types ARRAY<T> and STRUCT<...>, which get their own chapter.

There is only one integer type internally (INT64). INT, SMALLINT and the rest are aliases, so you cannot save space with a smaller integer; storage is based on actual values.

Money and exact decimals

FLOAT64 is binary floating point: 0.1 + 0.2 is not exactly 0.3. That is acceptable for measurements and terrible for prices. Use NUMERIC (or BIGNUMERIC for crypto-size values) for money. SQLite has no exact decimal type, so this stand-in shows the float problem itself, which is the reason NUMERIC exists:

SQL Stand-in: floating-point surprise Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Summing 10,000 prices as floats can drift by cents. In BigQuery you would declare price NUMERIC and the sums stay exact.

DATE, DATETIME and TIMESTAMP

These three are the most confused types:

Type Time zone? Use for
DATE none Birthdays, order dates, partition keys
DATETIME none (a "wall clock" reading) A local time in a known place, e.g. a store's opening
TIMESTAMP yes, an exact instant in UTC Events: clicks, logs, payments

Rule of thumb: log events as TIMESTAMP, derive DATE when you need a calendar day, and be explicit about the zone when converting:

SQL
SELECT
  event_ts,
  DATE(event_ts) AS day_utc,
  DATE(event_ts, 'America/New_York') AS day_new_york,
  DATETIME(event_ts, 'Europe/Berlin') AS berlin_wall_clock
FROM `my-project.app.events`;

If you use plain DATE(event_ts), you get the UTC day, so an evening order in California may land on "tomorrow". Time zones are the biggest source of off-by-one-day reporting errors.

Dates in SQLite are text, but the habit of splitting a timestamp into a day works the same:

SQL Stand-in: extracting the day from a timestamp Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Strings and bytes

STRING is Unicode, compared byte-by-byte, so comparisons are case-sensitive: 'a' = 'A' is false. Use LOWER() or a collation (COLLATE 'und:ci') for case-insensitive matches. LENGTH counts characters and BYTE_LENGTH counts bytes, which differ for non-ASCII text.

SQL Stand-in: case-sensitive comparison Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

Casting and safe casting

Convert with CAST(x AS type). If the value cannot convert, CAST raises an error and kills the whole query. SAFE_CAST returns NULL instead, which is how you clean messy columns:

SQL
SELECT
  CAST('42' AS INT64)          AS ok,
  SAFE_CAST('4x2' AS INT64)    AS null_not_error,
  CAST(7 AS FLOAT64) / 2       AS float_math,
  SAFE.PARSE_DATE('%d/%m/%Y', '31/02/2025') AS bad_date_is_null;

The SAFE. prefix works with many functions. Remember it. One bad row in a billion should not abort a nightly job.

SQLite has a lenient CAST that returns 0 for junk instead of failing, so this stand-in demonstrates the cast idea, but not the error behaviour:

SQL Stand-in: casting text to numbers Β· database: shop

Press Run (or Ctrl + Enter) to see the result here.

NULL, nullability and modes

Every column has a mode: NULLABLE (default), REQUIRED (no NULLs allowed) or REPEATED (an array). NOT NULL in DDL means REQUIRED. Primary and foreign keys can be declared but are not enforced; they exist as hints for the query planner and for documentation.

Choosing types: quick guide

  • IDs: INT64 or STRING (use STRING for IDs that have leading zeros or come from other systems).
  • Money: NUMERIC.
  • Event time: TIMESTAMP. Calendar day: DATE.
  • Flags: BOOL, not strings such as 'Y'.
  • Flexible attributes: JSON, or STRUCT when the fields are known.
  • Lists: ARRAY, covered in Arrays and UNNEST.

Check your understanding

Quick check Β· 4 questions

of 4 answered You scored / 4 Β· perfect!

  1. 1. Which type should hold prices to avoid rounding drift?

    NUMERIC is an exact decimal type; FLOAT64 is binary floating point.
  2. 2. What is the difference between DATETIME and TIMESTAMP?

    A DATETIME is a local wall-clock reading; a TIMESTAMP identifies one exact moment.
  3. 3. What does SAFE_CAST do on bad input?

    SAFE_CAST returns NULL, so one bad value does not fail the query.
  4. 4. Are primary key constraints enforced in BigQuery?

    BigQuery lets you declare keys but does not check them.

Summary

  • Core types: INT64, FLOAT64, NUMERIC, BOOL, STRING, BYTES, DATE, TIME, DATETIME, TIMESTAMP, JSON, GEOGRAPHY, plus ARRAY and STRUCT.
  • Use NUMERIC for money and TIMESTAMP for events; state time zones explicitly.
  • SAFE_CAST and the SAFE. prefix turn errors into NULL.
  • Keys are declared but not enforced.

Next: Loading and Exporting Data