# Google BigQuery (v2) Data Loading Reference

Information note **Note**: This guide is specific to **version 2** of the Google BigQuery destination. For info about data loading for version 1, refer to [version 1 of this guide](https://help.qlik.com/en-US/stitch/replication/loading/destination-guides/google-bigquery/v1).

Learn how Stitch will load data from your integrations into version 2 of Stitch’s Google BigQuery destination.

In this guide, we’ll cover data loading scenarios involving:

* [Primary key columns](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#primary-key-scenarios)

* [Replication keys and how data is loaded as a result](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#replication-keys-scenarios)

* [Object identifiers in the destination, including naming limitations and transformations](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#object-name-scenarios)

* [Table creation and modification in the destination](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#table-scenarios)

* [Various data types, including how data is typed and structured in the destination](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#data-typing-scenarios)

* [Schema changes in the source or structural changes in the destination](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#schema-change-scenarios)

* [Modifications made to the destination, such as the application of workload/performance management features or user privilege changes](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#destination-change-scenarios)

## Primary Key scenarios

Scenarios involving Primary Key columns.

A table without a Primary Key is replicated.

| **IF** | A table without a Primary Key is replicated. |
| --- | --- |
| **THEN** | Table is created with an `__sdc_primary_key` column as the Primary Key. Data for the table will be loaded in an Append-Only manner, regardless of the loading behavior selected for the destination.<br><br>The Primary Key information for the table is stored in the `_sdc_primary_keys` table in the integration’s schema.<br><br>Refer to the [Understanding loading behavior guide](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior) for more info and examples. |

A table with a single Primary Key is replicated.

| **IF** | A table with a single Primary Key is replicated. |
| --- | --- |
| **THEN** | Table is created.<br><br>The Primary Key information for the table is stored in the `_sdc_primary_keys` table in the integration’s schema. |

A table with multiple Primary Keys is replicated.

| **IF** | A table with multiple Primary Keys is replicated. |
| --- | --- |
| **THEN** | Table is created with an `__sdc_primary_key` column as the Primary Key. Data for the table will be loaded in an Append-Only manner, regardless of the loading behavior selected for the destination.<br><br>The Primary Key information for the table is stored in the `_sdc_primary_keys` table in the integration’s schema.<br><br>Refer to the [Understanding loading behavior guide](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior) for more info and examples. |

The table's Primary Key(s) is/are changed.

| **IF** | The table’s Primary Key(s) is/are changed. |
| --- | --- |
| **THEN** | Changing a table’s Primary Key(s) is not permitted in Google BigQuery. This includes changing the Primary Key(s) in the source, or adding a Primary Key to a table that didn’t previously have one.<br><br>If Primary Key columns are changed, Stitch will stop processing data for the table. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>Primary key change is not permitted<br>```<br><br>`Primary key change is not permitted`Primary key change is not permitted |
| **FIX IT** | Re-instate the table’s Primary Key(s) to allow Stitch to continue processing data for the table. |

You remove the Primary Key column(s) for a table in Google BigQuery.

| **IF** | You remove the Primary Key column(s) for a table in Google BigQuery. |
| --- | --- |
| **THEN** | Changing a table’s Primary Key(s) is not permitted in Google BigQuery.<br><br>If Primary Key columns are changed, Stitch will stop processing data for the table. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>Primary key change is not permitted<br>```<br><br>`Primary key change is not permitted`Primary key change is not permitted |
| **FIX IT** | Re-instate the table’s Primary Key(s) to allow Stitch to continue processing data for the table. |

[Back to top](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#)

## Replication Key scenarios

Scenarios involving Replication Keys and how data is loaded as a result.

A table using Key-based Incremental Replication is replicated where the Replication Key column contains NULL values.

| **IF** | A table using Key-based Incremental Replication is replicated where the Replication Key column contains `NULL` values. |
| --- | --- |
| **THEN** | <ul> <li><strong>During the initial job</strong>, the table will be created and all rows will be replicated.</li> <li><strong>During subsequent jobs</strong>, only rows with populated Replication Keys will be replicated and persisted to Google BigQuery.</li></ul> |

[Back to top](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#)

## Object naming scenarios

Scenarios involving object identifiers in the destination, including naming limitations and transformations.

A table name contains more characters than allowed by Google BigQuery.

| **IF** | A table name contains more characters than allowed by Google BigQuery. |
| --- | --- |
| **THEN** | Google BigQuery will reject all data for the table. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>Table name [TABLE] is too long for Google BigQuery<br>```<br><br>`Table name [TABLE] is too long for Google BigQuery`Table name [[TABLE] is too long for for Google BigQuery<br><br><br>Rejected records will be logged in the `_sdc_rejected` table of the integration's schema. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). |
| **FIX IT** | If possible, change the table name in the source to be less than Google BigQuery’s character limit of 1,024 characters.<br><br><br>Use the [`_sdc_rejected` table](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) to identify the root of the issue. |

A column name contains more characters than allowed by Google BigQuery.

| **IF** | A column name contains more characters than allowed by Google BigQuery. |
| --- | --- |
| **THEN** | Google BigQuery will reject columns with names that exceed the column character limit. Other columns in the table will persist to Google BigQuery. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>Column name [COLUMN] is too long for Google BigQuery<br>```<br><br>`Column name [COLUMN] is too long for Google BigQuery`Column name [[COLUMN] is too long for for Google BigQuery<br><br><br>Rejected records will be logged in the `_sdc_rejected` table of the integration's schema. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). |
| **FIX IT** | If possible, change the column name in the source to be less than Google BigQuery’s character limit of 128 characters.<br><br><br>Use the [`_sdc_rejected` table](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) to identify the root of the issue. |

Two columns are replicated that canonicalize to the same name.

| **IF** | Two columns are replicated that canonicalize to the same name. |
| --- | --- |
| **THEN** | For example: A table containing both `CustomerId` and `customerid` columns.<br><br>Google BigQuery will reject the records and create a log for the rejected records in the `_sdc_rejected` table in that integration’s schema. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>Field collision on [COLUMN_NAME]<br>```<br><br>`Field collision on [COLUMN_NAME]`Field collision on [[COLUMN\_NAME]<br><br><br>Rejected records will be logged in the `_sdc_rejected` table of the integration's schema. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). |
| **FIX IT** | If possible, re-name one of the columns in the source so that both column names will be unique when replicated to Google BigQuery.<br><br><br>Use the [`_sdc_rejected` table](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) to identify the root of the issue. |

A column is replicated that has a mixed-case name.

| **IF** | A column is replicated that has a mixed-case name. |
| --- | --- |
| **THEN** | Google BigQuery will convert letters to lowercase. For example:<br><br><table> <thead> <tr> <th>Columns in Source</th> <th>Columns in Google BigQuery</th> </tr> </thead>  <tr> <td><code>CuStOmErId</code></td> <td><code>customerid</code></td> </tr> <tr> <td><code>customerID</code></td> <td><code>customerid</code></td> </tr> </table> |

A column is replicated that has a name with spaces.

| **IF** | A column is replicated that has a name with spaces. |
| --- | --- |
| **THEN** | Google BigQuery will convert spaces to underscores. For example:<br><br><table> <thead> <tr> <th>Columns in Source</th> <th>Columns in Google BigQuery</th> </tr> </thead>  <tr> <td><code>customer id</code></td> <td><code>customer_id</code></td> </tr> <tr> <td><code>CUSTOMER ID</code></td> <td><code>customer_id</code></td> </tr> </table> |

A column is replicated with a name that contains unsupported special characters.

| **IF** | A column is replicated with a name that contains unsupported special characters. |
| --- | --- |
| **THEN** | Google BigQuery will convert special characters to underscores. For example:<br><br><table> <thead> <tr> <th>Columns in Source</th> <th>&nbsp;</th> <th>Columns in Google BigQuery</th> </tr> </thead>  <tr> <td><code>customer!id</code></td> <td>&nbsp;</td> <td><code>customer_id</code></td> </tr> <tr> <td><code>!CUSTOMERID</code></td> <td>&nbsp;</td> <td><code>_customerid</code></td> </tr> </table> |

A column is replicated with a name that begins with a non-letter.

| **IF** | A column is replicated with a name that begins with a non-letter. |
| --- | --- |
| **THEN** | Google BigQuery will remove all leading non-letter characters with the exception of leading underscores. For example:<br><br><table> <thead> <tr> <th>Columns in Source</th> <th>&nbsp;</th> <th>Columns in Google BigQuery</th> </tr> </thead>  <tr> <td><code>123customerid</code></td> <td>&nbsp;</td> <td><code>_customerid</code></td> </tr> <tr> <td><code>_customerid</code></td> <td>&nbsp;</td> <td><code>_customerid</code></td> </tr> <tr> <td><code>_987CUSTOMERID</code></td> <td>&nbsp;</td> <td><code>_987CUSTOMERID</code></td> </tr> </table> |

[Back to top](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#)

## Table scenarios

Scenarios involving table creation and modification in the destination.

A table contains entirely NULL columns.

| **IF** | A table contains entirely `NULL` columns. |
| --- | --- |
| **THEN** | No table is created in Google BigQuery. At least one column must have a non-`NULL` value for Stitch to create a table in Google BigQuery. |

A table arrives with more columns than Google BigQuery allows.

| **IF** | A table arrives with more columns than Google BigQuery allows. |
| --- | --- |
| **THEN** | Google BigQuery will reject all data for the table. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>ERROR: too many columns<br>```<br><br>`ERROR: too many columns`ERROR: too many columns<br><br><br>Rejected records will be logged in the `_sdc_rejected` table of the integration's schema. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). |
| **FIX IT** | If possible, deselect some columns to allow Stitch to load data into Google BigQuery for the table. Google BigQuery has a limit of 10,000 columns per table.<br><br><br>Use the [`_sdc_rejected` table](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) to identify the root of the issue. |

[Back to top](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#)

## Data typing scenarios

Scenarios involving various data types, including how data is typed and structured in the destination.

Stitch detects multiple data types for a single column.

| **IF** | Stitch detects multiple data types for a single column. |
| --- | --- |
| **THEN** | To accommodate data of varying types, Stitch will create multiple columns to ensure data is loaded with the correct type. In the destination, this will look like the column has been “split”.<br><br>For example: Stitch first detected that `order_confirmed` contained `BOOLEAN` data, but during a subsequent job, detected `STRING` values. To accommodate data of varying types, Stitch will:<br><br><ol> <li> <p>Store data for the original data type in the original column. In this example, only <code>BOOLEAN</code> values will be stored in <code>order_confirmed</code>. The name of the original column will not change.</p> </li> <li> <p>Create additional columns to store the other data types - one for each data type detected - and append the data type to the column name. In this example, a <code>order_confirmed__st</code> column will be created to store <code>STRING</code> values.</p> </li></ol> |

Data is replicated to Google BigQuery that is nested, containing many top-level properties and potentially nested sub-properties.

| **IF** | Data is replicated to Google BigQuery that is nested, containing many top-level properties and potentially nested sub-properties. |
| --- | --- |
| **THEN** | Nested records and objects are maintained. Refer to the [Nested data structures in Google BigQuery guide](https://help.qlik.com/en-US/stitch/replication/loading/google-bigquery-storing-nested-data-structures) for more info and examples. |

A VARCHAR column is replicated to Google BigQuery.

| **IF** | A `VARCHAR` column is replicated to Google BigQuery. |
| --- | --- |
| **THEN** | Google BigQuery will store all `VARCHAR` data as `STRING`. |

VARCHAR data is loaded that exceeds the current maximum size for the column.

| **IF** | `VARCHAR` data is loaded that exceeds the current maximum size for the column. |
| --- | --- |
| **THEN** | No widening will occur. Google BigQuery will store all `VARCHAR` data as `STRING`. |

A column containing date data with timezone info is replicated to Google BigQuery.

| **IF** | A column containing date data with timezone info is replicated to Google BigQuery. |
| --- | --- |
| **THEN** | Google BigQuery has no support for timezones. |

A column contains timestamp data that is outside Google BigQuery's supported range.

| **IF** | A column contains timestamp data that is outside Google BigQuery’s supported range. |
| --- | --- |
| **THEN** | Google BigQuery will reject the records that fall outside the supported range. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>timestamp out of range for Google BigQuery on [TIMESTAMP]<br>```<br><br>`timestamp out of range for Google BigQuery on [TIMESTAMP]`timestamp out of range for for Google BigQuery on [[TIMESTAMP]<br><br><br>Rejected records will be logged in the `_sdc_rejected` table of the integration's schema. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). |
| **FIX IT** | To resolve the error, offending values in the source must be changed to be within Google BigQuery’s timestamp range.<br><br><br>Use the [`_sdc_rejected` table](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) to identify the root of the issue. |

A column contains integer data that is outside Google BigQuery's supported range.

| **IF** | A column contains integer data that is outside Google BigQuery’s supported range. |
| --- | --- |
| **THEN** | Google BigQuery will reject the records that fall outside the supported range. |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>integer out of range for Google BigQuery on [INTEGER]<br>```<br><br>`integer out of range for Google BigQuery on [INTEGER]`integer out of range for for Google BigQuery on [[INTEGER]<br><br><br>Rejected records will be logged in the `_sdc_rejected` table of the integration's schema. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). |
| **FIX IT** | To resolve the error, offending values in the source must be changed to be within Google BigQuery’s limit for integers.<br><br><br>Use the [`_sdc_rejected` table](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) to identify the root of the issue. |

A column contains decimal data.

| **IF** | A column contains decimal data. |
| --- | --- |
| **THEN** | Decimal values will be loaded to Google BigQuery as the data type [`NUMERIC`](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#numeric-type). |

A column contains decimal data that is outside Google BigQuery's supported range.

| **IF** | A column contains decimal data that is outside Google BigQuery’s supported range. |
| --- | --- |
| **THEN** | Google BigQuery will reject the records that fall outside the supported maximum range for the [`NUMERIC` data type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#numeric-type). |
| **AND** | The following error will display in the **Notifications** tab in Stitch:<br><br>```<br>decimal out of range for Google BigQuery on [DECIMAL]<br>```<br><br>`decimal out of range for Google BigQuery on [DECIMAL]`decimal out of range for for Google BigQuery on [[DECIMAL]<br><br><br>Rejected records will be logged in the `_sdc_rejected` table of the integration's schema. [Learn more](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table). |
| **FIX IT** | To resolve the error, offending values in the source must be changed to be within Google BigQuery’s limit for decimals.<br><br><br>Use the [`_sdc_rejected` table](https://help.qlik.com/en-US/stitch/replication/loading/system-tables-columns/rejected-records-system-table) to identify the root of the issue. |

[Back to top](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#)

## Schema change scenarios

Scenarios involving schema changes in the source or structural changes in the destination.

A new column is added in table already set to replicate.

| **IF** | A new column is added in table already set to replicate. |
| --- | --- |
| **THEN** | If the column has at least one non-`NULL` value in the source, the column will be created and appended to the end of the table in Google BigQuery.<br><br>**Note**: If the table using either Key- or Log-based Incremental Replication, backfilled values for the column will only be replicated if:<br><br><ol> <li>The records’ Replication Key values are greater than or equal to the last saved maximum Replication Key value for the table, or</li> <li>The table is reset and a historical re-replication is queued.</li></ol><br><br>Additionally, how records with new column values are loaded depends on the selected loading behavior:<br><br><ul> <li> <p><strong>Upsert</strong>: Existing records will be updated with the new column’s values, if the table has a defined Primary Key column. If the table uses either Key- or Log-based Incremental Replication, you may need to reset the table to backfill historical values for previously replicated records.</p> <p>If the table doesn’t have a Primary Key, data will be loaded in an Append-Only manner.</p> </li> <li> <p><strong>Append-Only</strong>: Existing records will not be updated with the new column’s values. Instead, records with new column values will be appended to the end of the table.</p> </li></ul><br><br>Refer to the [Understanding loading guide](https://help.qlik.com/en-US/stitch/replication/loading/understanding-loading-behavior) for more info and examples. |

A new column is added by you to a Stitch-generated table in Google BigQuery.

| **IF** | A new column is added by you to a Stitch-generated table in Google BigQuery. |
| --- | --- |
| **THEN** | Columns may be added to tables created by Stitch as long as they are nullable, meaning columns don’t have `NOT NULL` constraints. |

A column is deleted at the source.

| **IF** | A column is deleted at the source. |
| --- | --- |
| **THEN** | How a deleted column is reflected in Google BigQuery depends on the Replication Method used by the table:<br><br><ul> <li> <p><strong>Key-based Incremental:</strong> The column will remain in the destination, and default <code>NULL</code> values will be placed in it going forward.</p> </li> <li> <p><strong>Log-based Incremental:</strong> Changes to a source table - including adding or removing columns, changing data types, etc. - require manual intervention before replication can continue. Refer to the <a href="https://help.qlik.com/en-US/stitch/replication/replication-methods/log-based-incremental#limitation-3--structural-changes">Log-based Incremental Replication documentation</a> for more info.</p> </li> <li> <p><strong>Full Table</strong>: The column will remain in the destination, and default <code>NULL</code> values will be placed in it going forward.</p> </li></ul> |

You remove a column from a Stitch-replicated table in your destination.

| **IF** | You remove a column from a Stitch-replicated table in your destination. |
| --- | --- |
| **THEN** | The result of deleting a column from a Stitch-generated table depends on the **type** of column being removed:<br><br><ul> <li> <p><strong>Primary Key columns</strong>: Changing a table’s Primary Key(s) is not permitted in Google BigQuery. If Primary Key columns are changed, Stitch will stop processing data for the table.</p> </li> <li> <p><strong>General columns</strong>:  If new data is detected for the removed column, Stitch will re-create it in Google BigQuery. This refers to all columns that are not prepended by <code>_sdc</code> or suffixed by a data type. For example: <code>customer_zip</code>, but not <code>customer_zip__st</code>.</p> <p><strong>Note</strong>: An integration must support selecting columns AND you must deselect the column in Stitch for the column removal to be permanent.</p> </li> <li> <p><strong>_sdc columns</strong>: Removing a Stitch replication column will prevent Stitch from loading replicated data into Google BigQuery.</p> </li> <li> <p><strong>Columns with data type suffixes</strong>: Removing a column created as result of accommodating <a href="https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#multiple-data-types">multiple data types</a> will prevent Stitch from loading replicated data into the table. This applies to columns with names such as: <code>customer_zip__st</code>, <code>customer_zip__int</code>, etc.</p> </li></ul> |

[Back to top](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#)

## Destination changes

Scenarios involving modifications made to the destination, such as the application of workload/performance management features or user privilege changes.

Indices are applied to Stitch-generated columns in the destination.

| **IF** | Indices are applied to Stitch-generated columns in the destination. |
| --- | --- |
| **THEN** | Stitch will respect the index application. |

Partitioning is applied to Stitch-generated tables in the destination.

| **IF** | Partitioning is applied to Stitch-generated tables in the destination. |
| --- | --- |
| **THEN** | Stitch will respect the partitioning application. Refer to the [Apply table partitioning and clustering guide](https://help.qlik.com/en-US/stitch/destinations/google-bigquery/apply-table-partitioning-clustering) for more info and instructions. |

Clustering is applied to Stitch-generated tables in the destination.

| **IF** | Clustering is applied to Stitch-generated tables in the destination. |
| --- | --- |
| **THEN** | Stitch will respect the cluster application. Refer to the [Apply table partitioning and clustering guide](https://help.qlik.com/en-US/stitch/destinations/google-bigquery/apply-table-partitioning-clustering) for more info and instructions. |

You switch to a different destination of the same type.

| **IF** | You switch to a different destination of the same type. |
| --- | --- |
| **THEN** | This means the destination type is still Google BigQuery, Stitch may just be connected a different database in Google BigQuery.<br><br><ul> <li><strong>For tables using Key-based or Log-based Incremental Replication</strong>, replication will continue using the Replication’s Key last saved maximum value. To re-replicate historical data, resetting Replication Keys is required.</li> <li><strong>For tables using Full Table Replication</strong>, the table will be fully replicated into the new destination during the next successful job.</li> <li><strong>For webhook integrations,</strong> some data loss may be possible due to the continuous, real-time nature of webhooks. Historical data must either be backfilled or re-played.</li></ul> |

[Back to top](https://help.qlik.com/en-US/stitch/data-structure/bigquery-data-loading-behavior#)