Oftentimes, your pipeline will run perfectly cleanly for months until, suddenly, it doesn’t. An upstream system changes a field format. A new data source sends numbers as strings. A timestamp arrives without timezone information and gets stored as DATETIME instead of TIMESTAMP.
And you see the effects downstream: a dashboard starts showing wrong numbers – or worse, nothing at all – and your team wastes two days tracing the problem to a schema decision no one thought to document.
These data type mismatches are the common and unglamorous side of BigQuery failures. They’re easy to introduce and hard to fix before they do damage – not to mention genuinely painful to troubleshoot at scale because changing a column’s type in BigQuery is rarely a simple ALTER TABLE statement. While some type conversions are supported, many require recreating the table and rewriting the data.
For engineering managers overseeing data infrastructure, understanding BigQuery’s type system isn’t just a matter of technical details. It’s a requirement for building pipelines that don’t actually break and schemas that don’t leave you with piles of debt.
This guide will walk you through:
- Every BigQuery type
- Where teams commonly go wrong
- How type decisions directly impact query performance and system longevity
Why Data Types Matter So Much in BigQuery
In a traditional relational database, a type mistake is annoying but fixable with a quick schema migration. In BigQuery, the stakes are higher.
Why?
First, BigQuery is made for large-scale analytical workloads, not regular schema changes. Changing a column’s data type means you have to:
- Create a new table with the correct schema
- Backfill or transform the data into it
- Swap references or rename tables
And that process is time-consuming and often costly for large tables.
Second, BigQuery defaults to STRING if it can’t infer a type during auto-detection. That might sound safe, but it actually means numeric and boolean data can sneak into strings – then you won’t know until a query tries to aggregate or filter on a column that’s storing “true” instead of true, or “42” instead of 42. At that point, the table may have months of data in the wrong format.
Third – and this is most relevant if your team is focusing on costs – data type choices significantly impact query efficiency, filtering behavior, and join performance. Storing an integer ID as a STRING, for instance, can make joins less efficient and reduce the effectiveness of clustering and predicate pushdown. This is why storing financial values as FLOAT64 instead of NUMERIC introduces rounding errors that only compound over time.
The right BigQuery type choices prevent expensive and hard to fix problems. But making the best choice for your setup is trickier than you might expect.
Why Getting BigQuery Types Wrong Is So Easy
Before we get into all the types you need to know, let’s quickly address why this problem is so common.
The big culprit here is not your team. It’s BigQuery.
BigQuery’s schema auto-detection is convenient and frequently wrong. When you load a JSON or CSV file without specifying a schema, BigQuery samples a subset of rows and infers types from what it sees.
That means if the first 500 rows of a column contain only integers, BigQuery will call it INT64. If row 501 contains a string, the load job fail may fail unless you’ve configured schema relaxation or allowed type coercion during ingestion. For pipelines handling unpredictable upstream data, auto-detection is a liability, not a reliable feature.
Another common failure mode is a type mismatch at JOIN time. BigQuery does not reliably perform implicit casting during JOINs. When types differ, you typically need an explicit CAST to ensure the join works as intended.
That means if one table stores customer_id as STRING and another stores it as INT64, the JOIN may fail or produce unexpected results unless you explicitly cast one side to match the other.
This failure mode is especially dangerous because at first glance, it looks like a data problem, not a schema issue, which is why it can take so long to diagnose.
Now that you understand how important BigQuery types are and why they can be difficult to get right, let’s break down each type so you can keep your pipeline running smoothly.
Complete Guide to BigQuery Data Types
These are the kind of BigQuery types you’ll encounter:
- Numeric types
BigQuery Numeric Types
BigQuery’s numeric types cover a spectrum from compact integers to high-precision decimals. Choosing the wrong one creates problems that range from minor inefficiency to material inaccuracy. We’ll cover these five types:
- INT64
- FLOAT64
- NUMERIC
- BIGNUMERIC
- String types (STRING and BYTES)
- Boolean types (BOOL)
- Date and time types (DATE, TIME, DATETIME, TIMESTAMP)
- Nested and repeat types (ARRAY and STRUCT)
- JSON type
- Geography type
- SMALLINT/INT/BIGINT
INT64
This includes aliases like SMALLINT, INT, INTEGER, BIGINT, TINYINT. These store 64-bit signed integers – whole numbers between roughly -9.2 quadrillion and 9.2 quadrillion.
This is the right type for:
- IDs
- Counts
- Rankings
- Any column that will never contain a decimal value
For JOIN keys specifically, INT64 is much more efficient than STRING – integer comparisons are binary operations, while string comparisons require character-by-character evaluation.
If your schema is using STRING for numeric IDs, you’ve got a great performance optimization on your hands.
FLOAT64
FLOAT64 stores 64-bit floating-point numbers. This BigQuery type is best for:
- Scientific measurements
- Ratios
- Values where approximate precision is acceptable
The main mistake teams make here is using FLOAT64 for financial data. Floating-point arithmetic introduces rounding errors that compound over aggregations. For example, a column storing transaction amounts as FLOAT64 produces subtly incorrect totals – usually in the last few decimal places – which is exactly where financial calculations can’t afford to be wrong.
NUMERIC and BIGNUMERIC
These are BigQuery’s fixed precision decimal types:
- NUMERIC: Supports up to 29 digits of precision with nine decimal places. This is sufficient for most financial and monetary calculations.
- BIGNUMERIC: Extends past NUMERIC to 76 digits with 38 decimal places for cases that call for extreme precision.
If you’re storing prices, revenues, transaction amounts, or any value where exact arithmetic matters, NUMERIC is the best type. The storage cost difference compared with FLOAT64 is negligible at scale compared with the price of inaccurate reporting.
NOTE: If a column will ever be summed, averaged, or used in financial reporting, use NUMERIC. If it’s a measurement where approximate values are fine (sensor readings, probability scores), FLOAT64 is acceptable.
String Types: STRING and BYTES
STRING stores variable-length Unicode text with a maximum of 16MB per value. It’s one of the most flexible BigQuery types. But it’s also one of the most misused since BigQuery will default to STRING when it can’t determine a type. Developers will often use it as a catch-all to avoid dealing with type constraints during initial schema design.
The cost of the convenience of STRING?
- STRING columns often consume more storage
- Slower to filter and JOIN than their typed equivalents
- Incompatible with BigQuery’s date and numeric functions without explicit casting
A column storing “true” and “false” as strings can’t be filtered with WHERE active = true. It needs WHERE active = ‘true’. And if any upstream system sends “1” or “yes” instead, the filter will quietly miss those rows.
The most common STRING antipatterns to watch out for include:
- Boolean values stored as strings
- Numeric IDs stored as strings
- Data values stored as strings (we’ll get into this more in a moment)
- Status codes or enumerations stored as free-text strings without validation
BYTES, in comparison, store raw binary data. This type is best for:
- Cryptographic hashes
- Encoded content
- Binary file data
NOTE: BYTES is not interchangeable with STRING. BYTES values aren’t human-readable text. They require different functions to manipulate.
Boolean Types: BOOL
BOOL stores true or false. It’s straightforward – though it is worth mentioning because of how often boolean data gets stored as integers (0 and 1) or strings (“true”/”false”/”yes”/”no”) in reality.
Storing boolean values as BOOL instead of INT64 or STRING helps:
- Make queries more readable
- Prevent accidental arithmetic on flag columns
- Signals intent clearly to anyone working with the schema later
WHERE is_active = true is unambiguous. WHERE is_active = 1 works, but it also requires the reader to know the encoding.
Date and Time Types: DATE, TIME, DATETIME, and TIMESTAMP
Date and time types are where the biggest BigQuery pipeline breaks happen because there are four of them, they look similar, and their differences have significant consequences for data accuracy.
- DATE: Stores a calendar date with no time component (e.g., 2024-09-15). This is best for birthdays, report dates, transaction dates, and any value where the time of day is irrelevant or unknown.
- TIME: Stores a time of day with no date (e.g., 14:30:00). This is rarely used in analytical schemas but will appear in operational data, schedule tables, and time-of-day segmentation use cases.
- DATETIME: Stores a date and time with no timezone information (e.g., 2024-09-15 14:30:00). This type creates the most chaos and confusion because the value represents “civil time” – what a clock on the wall shows – with no reference to the clock’s timezone.
- TIMESTAMP: Stores an absolute point in time, internally normalized to UTC – 2024-09-15 14:30:00 UTC. This type is best for event timestamps, log entries, pipeline run times, and anything where you need to accurately compare across different time zones.
NOTE: The DATE type is usually the best type for partitioning columns. Partitioning on a TIMESTAMP column works well, but partitioning on a DATE-derived column often simplifies query patterns and makes partition pruning more predictable for many analytical workloads.
Nested and Repeated Types: ARRAY and STRUCT
ARRAY stores an ordered list of values of the same type. STRUCT stores a collection of named fields, each with their own type, essentially acting as a record or object within a row. Together, they enable BigQuery’s nested and repeated field structure, one of the most powerful and underused features in the platform.
Practically, ARRAY and STRUCT let you store related data together in a single table instead of spreading it across multiple tables and joining at query time. An order record can contain an array of line items, each a STRUCT with product ID, quantity, and price – all within a single row. Queries that would otherwise require a JOIN against a separate line items table can now run against a single denormalized table, which reduces query complexity and bytes scanned.
This feature isn’t just a convenience – it’s an architectural pattern that BigQuery was made for. BigQuery performs best with fewer, wider tables than with many narrow tables connected by JOINs. ARRAY and STRUCT are the types that make that architecture practical.
PRO TIP: Nested schemas are harder to query for analysts unfamiliar with BigQuery’s UNNEST function, and they require more careful schema design upfront. Teams that get this right from the start see significant cost and performance benefits.
JSON Type
JSON stores semi-structured data in a native JSON format. It’s best to work with schemaless or variable-structure data without converting it to strings. A JSON column can hold objects with varying keys across different rows, which is especially useful for event metadata, configuration data, and API response payloads where the structure varies by event type.
NOTE: STRUCT fields defined at schema creation time are type-checked. JSON fields are flexible but untyped – BigQuery will valide them, but it won’t index or type-check individual fields. For data with a consistent, known structure, STRUCT is more performant and easier to query. JSON is the best choice when the structure genuinely varies and you need to store it as-is without defining every possible field in the schema.
Geography Type: GEOGRAPHY
GEOGRAPHY stores geometric data representing points, lines, and polygons on the Earth’s surface. It’s the best type for:
- Coordinates
- Location data
- Spatial analysis
These are compatible with BigQuery’s suite of GIS functions for distance calculations, containment queries, and geospatial aggregations.
For teams in ecommerce, logistics, or any industry where location data matters, using GEOGRAPHY rather than storing latitude and longitude as separate FLOAT64 columns unlocks a set of spatial query capabilities that would otherwise require custom logic. It also makes the data’s purpose unambiguous to anyone reading the schema.
How to Connect Type Choices to Schema Design
Data types aren’t just isolated decisions. They compound. A schema with STRING where INT64 belongs, FLOAT64 where NUMERIC belongs, and DATETIME where TIMESTAMP belongs accumulates technical debt. And that debt leaves you with slow queries, incorrect aggregations, broken JOINs, and expensive retroactive fixes.
Getting types right during schema design is foundational to partitioning, clustering, and query optimization. Without it, you’re often stuck with:
- A TIMESTAMP column that’s actually storing civil times without timezone information, causing partition pruning to behave unexpectedly.
- INT64 JOIN keys that’s been stored at STRING that makes clustering less effective on that column
- FLOAT64 column storing financial data producing subtly wrong results in aggregation
And none of these errors announce themselves.
For engineering teams managing BigQuery at scale, this is where the real optimization starts: with schemas that are designed deliberately, typed correctly, and structured to support the query patterns they’re made for.
The Right Schema Foundation Is Only Half the Battle
Understanding the BigQuery types is a must-have. Keeping each schema correct and every pipeline type-safe as your data infrastructure scales – with each new table, source, analyst, and use case – is an entirely different problem.
At Yuki, we help engineering teams in fintech, gaming, ecommerce, and cybersecurity build and maintain data warehouse infrastructure that performs at scale – without requiring constant engineering attention.
And our customers see serious results:
- 37.6% saved on compute costs
- 500 million daily queries run more efficiently
- 30% reduction in infrastructure management overhead
If your team is spending time on schema maintenance, pipeline debugging, or cost management that should be handled at the infrastructure level, talk to our team. That’s exactly the problem we’re built to solve.
