BigQuery schema: how to specify, view and change a table schema

Four ways to specify a schema

1. In the console

In the Create table dialog, under Schema, add fields one by one with Add field, or switch on Edit as text and paste the schema as a list:

order_id:INTEGER,
order_date:DATE,
amount:NUMERIC,
channel:STRING

2. In SQL

CREATE TABLE mydataset.orders (
  order_id INT64 NOT NULL,
  order_date DATE,
  amount NUMERIC,
  channel STRING OPTIONS (description = 'Lowercase channel name')
);

NOT NULL is how SQL writes the REQUIRED mode. This statement creates a table, so it was not executed for this guide; it follows Google’s DDL reference. More statements are in CREATE and DROP TABLE.

3. With the bq tool

Inline, for simple tables:

bq load --source_format=CSV --skip_leading_rows=1 \
  mydataset.orders ./orders.csv \
  order_id:INTEGER,order_date:DATE,amount:NUMERIC,channel:STRING

Or with a JSON schema file, which is the only form that can express modes, descriptions and nested fields:

[
  {"name": "order_id", "type": "INTEGER", "mode": "REQUIRED"},
  {"name": "order_date", "type": "DATE"},
  {"name": "items", "type": "RECORD", "mode": "REPEATED",
   "fields": [
     {"name": "sku", "type": "STRING"},
     {"name": "quantity", "type": "INTEGER"}
   ]}
]

4. Auto-detect

For CSV and JSON files, tick Auto detect, or pass --autodetect. BigQuery samples the file and infers names and types. Avro, Parquet and ORC files carry their own schema, so nothing needs detecting.

When auto-detect is safe

Auto-detect looks at a sample of rows, not at the whole file. It is fine for a quick look and risky for a table you will keep:

• IDs with leading zeros become integers and lose the zeros.
• A column that is empty in the sample becomes STRING, whatever comes later.
• Dates in a local format, such as 07/10/2026, become STRING, or worse, a valid but wrong date.
• A column that is numeric in the sample and has “n/a” further down fails the load.

For anything recurring, write the schema down. The load then fails loudly when the file changes shape, which is what you want.

How to view a table’s schema

In the console, click the table and open the Schema tab. In SQL, every dataset has a view that lists columns:

SELECT column_name, data_type, is_nullable
FROM mydataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'searchdata_site_impression'
ORDER BY ordinal_position
LIMIT 5;

Run on a Search Console export in our project on 7 October 2026, this returned:

The same view answers practical questions across a dataset, for example “which tables have a column called user_id”. From the command line, bq show --schema --format=prettyjson mydataset.orders prints the schema as JSON, ready to reuse.

Nested and repeated columns

BigQuery schemas are not limited to flat columns. A column can be a STRUCT (a RECORD in the API), which groups fields, and any column can be REPEATED, which makes it an array. The GA4 export uses both: event_params is a repeated record of keys and values.

Nesting keeps related data in one row and avoids joins. It also means such a column has to be unpacked with UNNEST before it can be filtered or summed. See JSON objects and arrays and the array functions guide.

Which changes are allowed later

The statements and their limits are in how to modify tables in BigQuery. The types themselves are compared in BigQuery data types.

Before renaming or dropping, remember that every saved query, view and dashboard that names the column will break. BigQuery does not tell you which ones.

A schema is also documentation

Types and modes tell BigQuery how to store a column. They do not tell a colleague what amount means: with tax or without, in which currency, refunds included or not. The description field is there for that, and it is empty in most tables.

For the tables that reports are built on, write the descriptions. In OWOX Data Marts the output schema of each Data Mart carries an alias and a description per column, and those travel with the data: into Google Sheets as notes on the header cells, and into any other destination. OWOX Data Marts does not transform your data; it keeps the meaning of each column next to the numbers.

To load data into the table you have defined, see loading data into BigQuery.

Google BigQueryBigQuery Aggregate Functions: COUNT, SUM, STRING_AGG, ARRAY_AGG and MoreOctober 16, 2026→Google BigQueryBigQuery Array Functions: Syntax and Tested ExamplesOctober 6, 2026→Google BigQueryBigQuery CAST and SAFE_CAST: Convert Data Types Without ErrorsOctober 6, 2026→

See all articles →