BigQuery data types: which to use, with limits and tested examples
BigQuery has about a dozen data types. This guide lists them with what they hold, how much storage they take and when to use each, then runs the behaviours that catch people out: FLOAT64 against NUMERIC for money, VARCHAR not existing, INT64 overflow, STRING measured in bytes, GEOGRAPHY as WKT, and INTERVAL and RANGE.
BigQuery has about a dozen data types, and most columns need one of five: STRING for text, INT64 for whole numbers, NUMERIC for money, FLOAT64 for measurements, and DATE or TIMESTAMP for time. The choice matters more than it looks: the wrong numeric type changes totals, and the wrong time type changes which day a row falls on.
| Type | Holds | Use it for |
|---|---|---|
STRING |
UTF-8 text, optionally with a maximum length, STRING(n) |
names, IDs that are not numbers, codes |
INT64 |
whole numbers, about ±9.2 × 10^18 | counts, numeric IDs |
NUMERIC |
exact decimals, 38 digits, 9 after the point | money, rates that must add up |
BIGNUMERIC |
exact decimals, about 76.8 digits, 38 after the point | very precise values, crypto amounts |
FLOAT64 |
approximate decimals | measurements, ratios, scientific data |
BOOL |
true or false | flags |
DATE, DATETIME, TIME, TIMESTAMP |
calendar dates and times | see the date types guide |
BYTES |
raw binary | hashes, encoded files |
GEOGRAPHY |
points, lines, polygons on Earth | locations, areas |
JSON |
a parsed JSON document | semi-structured data |
ARRAY<T> |
a list of values of one type | repeated values |
STRUCT<...> |
named fields of any types | nested records |
INTERVAL |
a duration | date arithmetic |
RANGE<T> |
a start and an end | validity periods |
Ranges are from Google’s data types reference, read in October 2026. Each type has a storage size per value, and the sum of those sizes is what storage and on-demand queries are billed by, so a wider type than the data needs costs money on every scan.
Note: Originally published in January 2024. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026.
FLOAT64 or NUMERIC: the money question
FLOAT64 or NUMERIC: the money question
SELECT
0.1 + 0.2 AS float_sum,
NUMERIC '0.1' + NUMERIC '0.2' AS numeric_sum;| float_sum | numeric_sum |
|---|---|
| 0.30000000000000004 | 0.3 |
A literal like 0.1 is FLOAT64, and FLOAT64 stores binary approximations. Over thousands of rows the error accumulates, and a revenue total stops matching the finance system by a few cents. Store money as NUMERIC, and write NUMERIC literals as NUMERIC '13.60'. Scale is not kept: NUMERIC '13.60' comes back as 13.6. Format it for display, not in storage.
FLOAT64 is right for things that are measured rather than counted: durations, coordinates, conversion rates, model scores.
Integer division and overflow
| Expression | Result |
|---|---|
1 / 3 |
0.33333333333333331 (FLOAT64) |
DIV(7, 2) |
3 |
9223372036854775807 + 1 |
error: “Integer Overflow” |
The / operator returns FLOAT64 between two integers (7 / 2 is 3.5) and NUMERIC when an operand is NUMERIC. For integer division use DIV. INT64 overflow fails the query rather than wrapping around; it is rare outside sums of very large IDs or of micros, such as ad costs stored in millionths.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
There is no VARCHAR
SELECT CAST('abc' AS VARCHAR);fails with “Type not found: VARCHAR”. BigQuery’s one text type is STRING. Schemas copied from another database need VARCHAR(n), CHAR, TEXT and NVARCHAR changed to STRING.
A maximum length is still possible: a column can be declared STRING(10), a parameterized type, and a value longer than that is rejected when it is written. The same works for NUMERIC(10, 2) and BYTES(n). A dry run of CREATE TABLE t (code STRING(5), price NUMERIC(10, 2)) returned a schema with maxLength 5 and precision 10, scale 2. Parameterized types belong to columns only: CAST('abc' AS STRING(5)) fails with “Parameterized types are not allowed in CAST expressions.”
STRING length is counted in characters, storage in bytes
| Expression | Result |
|---|---|
LENGTH('é') |
1 |
BYTE_LENGTH('é') |
2 |
Storage and query cost follow bytes, so a column of non-Latin text costs more than its character count suggests.
GEOGRAPHY and WKT
SELECT
ST_ASTEXT(ST_GEOGPOINT(30.52, 50.45)) AS wkt,
ST_DISTANCE(ST_GEOGPOINT(30.52, 50.45),
ST_GEOGPOINT(13.40, 52.52)) AS meters;| wkt | meters |
|---|---|
| POINT(30.52 50.45) | 1204564.78 |
ST_GEOGPOINT takes longitude first, then latitude, the reverse of how most people write coordinates. ST_ASTEXT turns a GEOGRAPHY into WKT text, the format BI tools such as Looker Studio and spreadsheets read for maps. More in the geospatial functions guide.
INTERVAL and RANGE
| Expression | Result |
|---|---|
CAST(INTERVAL 3 DAY AS STRING) |
0-0 3 0:0:0 |
RANGE(DATE '2026-10-01', DATE '2026-11-01') |
start 2026-10-01, end 2026-11-01 |
INTERVAL is what date arithmetic returns and accepts. RANGE stores a period as one value, such as a price’s or a contract’s validity, and range functions test overlap and containment. Both are newer and not every tool that reads BigQuery understands them; convert to two columns before exporting.
ARRAY, STRUCT and JSON
- ARRAY holds repeated values of one type. It replaces a child table when the list belongs to the row: the items of an order, the tags of a post. See the array functions guide.
- STRUCT groups named fields, and an
ARRAY<STRUCT<...>>is a nested table inside a row. The GA4 export’sevent_paramsis one. See JSON objects and arrays. - JSON stores a document whose shape varies. Fields used in every report are better extracted into typed columns. See JSON functions.
Converting between types
CAST(x AS type) converts or fails; SAFE_CAST returns NULL instead of failing. Which conversions are allowed and what each returns is in CAST and SAFE_CAST. The type is set in the table’s schema; how to specify it and which changes are allowed later is in the schema guide.
A type is a promise to every report
A type is a promise to every report
A column typed as FLOAT64 money, or as a STRING date, makes every query that reads it repeat a conversion and choose a rounding rule. Fix the types once, in the table or in the one query that prepares it, and have reports read that. In OWOX Data Marts each Data Mart’s output schema shows every column’s type next to its description, so a reader of the sheet or dashboard sees what the number is. OWOX Data Marts runs your SQL as written, in your own BigQuery project, and does not transform the data.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
Should I store JSON as a STRING or as the JSON type in BigQuery?
Use the JSON type for documents whose shape varies: it is parsed once, and fields can be read with dot notation. Fields that every report uses are better extracted into typed columns, so each query does not repeat the conversion.
Is there a VARCHAR type in BigQuery?
No. CAST(x AS VARCHAR) fails with "Type not found: VARCHAR". The text type is STRING. A column can still have a maximum length: declare it STRING(10), a parameterized type, and longer values are rejected when written. Parameterized types work on columns, not in CAST.
How do repeated fields work in BigQuery?
A repeated field is an ARRAY column: a list of values of one type in a single row. An ARRAY of STRUCT works as a nested table, like the event_params field in the GA4 export. UNNEST turns the array into rows when you need to filter or aggregate it.
How does the data type affect BigQuery cost?
Storage and on-demand queries are billed by the bytes a column holds and a query reads. A wider type than the data needs, or text where a number would do, costs on every scan. STRING storage follows UTF-8 bytes, not characters: 'é' is one character and two bytes.
What is a STRUCT in BigQuery?
A type that groups named fields, possibly of different types, into one value: STRUCT<id INT64, plan STRING>. Inside an ARRAY it represents a list of records in one row, which avoids a join to a child table.
What is the difference between NUMERIC and FLOAT64 in BigQuery?
NUMERIC stores exact decimals, 38 digits with 9 after the point, and suits money: NUMERIC '0.1' + NUMERIC '0.2' is 0.3. FLOAT64 stores binary approximations: 0.1 + 0.2 is 0.30000000000000004. Use FLOAT64 for measurements and ratios.
