Troubleshoot schema errors

This document helps you diagnose and resolve HTTP 400 Bad Request errors that occur when you modify BigQuery table schemas or when you define or load deeply nested schema structures. For information about how to use INFORMATION_SCHEMA views to troubleshoot BigQuery performance, see Troubleshoot with information schema.

Troubleshoot schema mismatch errors

BigQuery returns a Provided Schema does not match Table error (reason: invalid) when one of the following operations supplies a schema that conflicts with the target table's existing schema:

A load job
A query job with a destination table
A copy job
An ALTER TABLE DDL statement
A table metadata update (tables.update or tables.patch)

To identify the specific cause and resolution, match your error message to one of the following scenarios.

Field is missing in new schema

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Field FIELD_NAME is missing in new schema

Diagnosis

One of the following operations provides a schema definition that omits one or more columns that already exist in the destination table:

A load job
A DataFrame write operation
A tables.update or tables.patch API call

Resolution

BigQuery doesn't allow dropping columns implicitly by omitting them from an API schema payload or load job configuration. To resolve this error, do one of the following:

Update your pipeline or API request so that the provided schema includes all existing columns in the target table.
If you intend to remove the column from the table, run a DROP COLUMN DDL statement instead of omitting the column from a load or patch request:
```
ALTER TABLE `PROJECT_ID.DATASET.TABLE`
DROP COLUMN FIELD_NAME;
```
Replace the following:
- PROJECT_ID: the ID of the project that contains the table.
- DATASET: the name of the dataset that contains the table.
- TABLE: the name of the table.
- FIELD_NAME: the name of the column to drop.

Field has changed type

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Field FIELD_NAME has changed type from EXISTING_TYPE to NEW_TYPE

Diagnosis

Your request attempts to change an existing column's data type (for example, from STRING to INT64) in place during a load job or schema update.

Resolution

To resolve this error, do one of the following:

If the type change is a supported column data type conversion (such as INT64 to NUMERIC, INT64 to FLOAT64, or NUMERIC to BIGNUMERIC), use an ALTER TABLE ... ALTER COLUMN DDL statement:
```
ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ALTER COLUMN FIELD_NAME SET DATA TYPE NEW_TYPE;
```
Replace the following:
- PROJECT_ID: the ID of the project that contains the table.
- DATASET: the name of the dataset that contains the table.
- TABLE: the name of the table.
- FIELD_NAME: the name of the column to modify.
- NEW_TYPE: the target data type.
If the type conversion isn't supported in place, query the existing data with an explicit CAST expression and write the results to a new table or overwrite the existing table. For more information, see Change a column's data type.

Field has changed mode

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Field FIELD_NAME has changed mode from EXISTING_MODE to NEW_MODE

Diagnosis

Your request attempts an unsupported column mode change (for example, changing NULLABLE to REQUIRED or REPEATED to NULLABLE). This error also occurs if you use an inline schema definition in the bq command-line tool when relaxing a REQUIRED column on a table that contains REPEATED columns, because inline schema definitions in the bq command-line tool don't support modifying tables with REPEATED columns.

Resolution

The only mode change supported by in-place BigQuery schema updates is relaxing a column's mode from REQUIRED to NULLABLE. To resolve this error, do one of the following:

If you are relaxing a column from REQUIRED to NULLABLE, run an ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL DDL statement in GoogleSQL, or supply a JSON schema file if you are updating the schema with the bq update command or the tables.patch API method. For instructions, see Change a column's mode.
To change a column's mode from NULLABLE to REQUIRED or from REPEATED to NULLABLE, create a new table with the updated schema and copy or load the data into the new table. For instructions, see Modifying table schemas.

Cannot add fields

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Cannot add fields (field: FIELD_NAME)

Diagnosis

A load job or query job includes a column (FIELD_NAME) that doesn't exist in the destination table, and schema addition isn't enabled in the job configuration.

Resolution

Enable schema addition on the load or query job:

In the REST API or client libraries, include ALLOW_FIELD_ADDITION in the schemaUpdateOptions property of your load or query job configuration.
In the bq command-line tool, pass the --schema_update_option=ALLOW_FIELD_ADDITION flag.
Alternatively, add the column before running the job by using ALTER TABLE ... ADD COLUMN.

Cannot add required fields to an existing schema

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Cannot add required fields to an existing schema. (field: FIELD_NAME)

Diagnosis

Your request appends a new column to an existing table with the column mode set to REQUIRED (NOT NULL). Because existing rows in the table would receive NULL values for the newly added column, BigQuery rejects adding REQUIRED columns directly through schema updates.

Resolution

To resolve this error, do one of the following:

Add the new column with mode NULLABLE instead of REQUIRED, and then run a DML UPDATE statement to populate values for existing rows.
To add the column and assign a default value, complete the following steps:
1. Add the column as a NULLABLE column.
2. Set the column's default value so that future inserts receive a value automatically.
3. Update existing rows to populate the default value:
```
ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ADD COLUMN FIELD_NAME DATA_TYPE;

ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ALTER COLUMN FIELD_NAME SET DEFAULT DEFAULT_EXPRESSION;

UPDATE `PROJECT_ID.DATASET.TABLE`
SET FIELD_NAME = DEFAULT_EXPRESSION
WHERE TRUE;
```
  Replace the following:
  - PROJECT_ID: the ID of the project that contains the table.
  - DATASET: the name of the dataset that contains the table.
  - TABLE: the name of the table.
  - FIELD_NAME: the name of the new column.
  - DATA_TYPE: the column's data type.
  - DEFAULT_EXPRESSION: the default value expression for the column.

Field already exists in schema

Error message

Field FIELD_NAME already exists in schema

Diagnosis

The schema provided in the API request, JSON schema file, or source file header (such as CSV or Parquet) contains duplicate column names. Column names in BigQuery are case-insensitive, so specifying both Field_A and field_a in the same schema triggers this error.

Resolution

Inspect the source schema and remove or rename duplicate column names, including any case-insensitive name collisions.

Updating field collation is not supported

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Invalid schema update. Updating field collation is not supported. Field FIELD_NAME has changed collation from "EXISTING_COLLATION" to "NEW_COLLATION"

Diagnosis

You can't modify or remove the collation specification of an existing column during a schema update. This error commonly occurs when an application fetches a table's schema, modifies another field, and writes the schema back using an older client library version that doesn't preserve the collation field property (sending "" instead of "und:ci").

Resolution

To resolve this error, do the following:

Upgrade your BigQuery client library to the latest version so that existing collation attributes are preserved when reading and patching table metadata.
If you construct JSON schema payloads manually, ensure that the collation property on existing columns matches the table's current schema.

Narrowing type parameters is not compatible

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Narrowing type parameters is not compatible

Diagnosis

Your ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE statement attempts to reduce the maximum length, precision, or scale of a parameterized data type—for example, changing STRING(100) to STRING(50) or reducing NUMERIC(P, S) parameters.

Resolution

BigQuery only allows widening type parameters on an existing column in place (such as increasing STRING(50) to STRING(100)). To enforce narrower type parameters, create a new table defined with the narrower type parameter and copy or load the data into the new table by using a SELECT query that makes values fit the narrower type (for example, LEFT(FIELD_NAME, 50) for a STRING column). Writing a STRING or BYTES value longer than the column's maximum length, or a NUMERIC value with more digits than its precision allows, returns an error. NUMERIC values with more decimal digits than the column's scale are rounded. For more information, see Modifying table schemas.

Adding a field with a default value is not supported

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Add field with default value to an existing table schema is not supported; Field: FIELD_NAME. ...

Diagnosis

Your load job or tables.patch or tables.update API request attempts to add a new column to an existing table while specifying defaultValueExpression in the API schema payload. You can't add a new column with a default value to an existing table in a single API schema update.

Resolution

Instead of specifying defaultValueExpression when you add the column in the API request, add the column first and then set its default value (for more information, see Limitations):

If you haven't added the column yet, run the following statements, and then resume your loads without defaultValueExpression:
```
ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ADD COLUMN FIELD_NAME DATA_TYPE;

ALTER TABLE `PROJECT_ID.DATASET.TABLE`
ALTER COLUMN FIELD_NAME SET DEFAULT DEFAULT_EXPRESSION;

UPDATE `PROJECT_ID.DATASET.TABLE`
SET FIELD_NAME = DEFAULT_EXPRESSION
WHERE TRUE;
```
Replace the following:
- PROJECT_ID: the ID of the project that contains the table.
- DATASET: the name of the dataset that contains the table.
- TABLE: the name of the table.
- FIELD_NAME: the name of the new column.
- DATA_TYPE: the column's data type.
- DEFAULT_EXPRESSION: the default value expression for the column.
If you already added the column by resubmitting the request without defaultValueExpression, run only the ALTER COLUMN ... SET DEFAULT and UPDATE statements from the preceding example.

Updating an existing identity column's attributes is not supported

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Updating an existing identity column's attributes is not supported. Field FIELD_NAME already has identity column info.

Diagnosis

Your request attempts to modify the attributes of an existing identity column through a schema update or API patch.

Resolution

Identity column properties can't be modified in place after creation. Keep the existing identity column definition unchanged in your schema update payload, or create a new table with the updated identity column configuration and copy or load the data into the new table. For instructions, see Modifying table schemas.

Incompatible field change within RECORD column

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Incompatible field change within RECORD column

Diagnosis

Your ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE statement specifies a STRUCT type that isn't compatible with the column's existing type. For example, the new type adds or removes subfields, changes a subfield to an unsupported type, or narrows a subfield's type parameters.

Resolution

Compare the STRUCT type in your ALTER TABLE statement with the column's existing schema, and then do one of the following:

Keep the same subfields in the same order, and change subfield types only in ways that are supported for column data type changes.
DDL can't add nested subfields to an existing RECORD column. Instead, update the schema by using a JSON schema file with bq update or the tables.patch method. For more information, see Add a nested column to a RECORD column.
To remove or restructure nested subfields, see Modify nested column types.

Maximum limit on diverging physical schemas reached

Error message

Provided Schema does not match Table PROJECT_ID:DATASET.TABLE. Maximum limit on diverging physical schemas reached.

Diagnosis

Certain table schema modifications, such as dropping or renaming columns, can cause tables to have identical logical schemas in the API while having different underlying physical schema representations. When you copy data into a destination table across too many diverging physical schema versions, the table copy job can fail with this error. For more information, see Limitations on copying tables.

Resolution

Use the CREATE TABLE LIKE DDL statement to recreate the destination table so that its physical schema representation matches the source table's schema, and then copy or insert the data:

CREATE OR REPLACE TABLE `PROJECT_ID.DATASET.DESTINATION_TABLE`
LIKE `PROJECT_ID.DATASET.SOURCE_TABLE`;

INSERT INTO `PROJECT_ID.DATASET.DESTINATION_TABLE`
SELECT * FROM `PROJECT_ID.DATASET.SOURCE_TABLE`;

Replace the following:

PROJECT_ID: the ID of the project that contains the tables.
DATASET: the name of the dataset that contains the tables.
DESTINATION_TABLE: the name of the destination table to recreate.
SOURCE_TABLE: the name of the source table.

Troubleshoot schema, file, and routine nesting limit errors

BigQuery enforces a maximum nesting depth of 15 levels for STRUCT (RECORD) columns, structured load files, and nested routine argument and return types. Exceeding this 15-level limit returns an HTTP 400 Bad Request error with one of the following messages (where MAX_DEPTH and MAX_LEVEL are 15). For errors caused by exceeding the 16-level nesting limit on logical views or persistent user-defined functions (UDFs), see Too many nested views or persistent user-defined functions.

Error messages

Schema nested too deeply.
Schema nested too deeply for field FIELD_NAME, maximum allowed depth is MAX_DEPTH.
Field 'FIELD_NAME' is nested too deeply. It has definition level LEVEL while maximum allowed definition level is MAX_LEVEL.
Schema nested too deeply, at least LEVEL compared to the maximum allowed level of 15.
Routine TYPE_DESCRIPTION nested too deeply; max allowed depth: MAX_DEPTH

Diagnosis

Columns of type STRUCT (RECORD) can contain nested RECORD fields up to a maximum depth of 15 levels, regardless of whether the fields are scalar (NULLABLE or REQUIRED) or array-based (REPEATED). Similarly, JSON, Parquet, and Avro load jobs and external tables enforce the nesting limit when they parse nested structures. For Parquet files, the limit is expressed as a maximum definition level and a maximum repetition level.

In routine errors, TYPE_DESCRIPTION identifies the nested type: argument type, return type, or return table type.

Resolution

This limit can't be increased. To resolve this error, do one of the following:

Flatten the schema or routine type definition. Break deeply nested STRUCT or ARRAY hierarchies in your table schema or routine argument and return types into separate related tables and join them at query time, or promote deeply nested attributes to higher-level columns.
Use the built-in JSON data type. If your data requires more than 15 levels of nesting, store the deeply nested subtrees in a column of type JSON (or STRING). A JSON column is stored as a single column in the table schema—avoiding the 15-level RECORD schema limit—while still allowing you to extract deeply nested paths at query time by using JSON field access operators and functions.
Preprocess input files before loading. If the error occurs during a load job from JSON, Parquet, or Avro files in Cloud Storage, add an upstream preprocessing step to flatten or serialize structures deeper than 15 levels before loading them into BigQuery.