The Service Account ID associated with the

Google BigQuery V2

connection must have permissions to access Google Cloud Storage buckets and files.

You cannot enable full

SQL ELT optimization

for a mapping task when the target table contains columns of record data type or repeated columns.

You cannot enable full

SQL ELT optimization

for a mapping task when the task contains a mapping with a single transformation connected to multiple transformations downstream or multiple transformations connected to a single transformation.

You must ensure that the column header names in the Google Cloud Storage source file does not contain unicode characters. Otherwise, the mapping fails.

When you enable

SQL ELT optimization

for a mapping with multiple pipelines to write to the same

Google BigQuery

target and the Truncate target table option is enabled in each pipeline, the target table is truncated for each pipeline when data is inserted into the target.

When you configure a Filter transformation or specify a filter condition, you must ensure that you do not specify special characters. Use the ASCII value for the special character in the filter condition.

When you parameterize the

Google BigQuery V2

connection, source, target and provide values in the mapping task using a parameter file, the default values for the parameter are not overridden with the values in the parameter file.

If the Google Cloud Storage source file contains a column of Boolean data type,

SQL ELT optimization

query fails.

You must ensure that the Google BigQuery source object does not contain any partitions.

When you read from a

Google BigQuery

source and edit the metadata for the source fields, the Secure Agent ignores the changes to the metadata.

If the

Google BigQuery

source and target object resides in the same region other than US, do not specify the

Region ID

explicitly in the connection.

You must not specify a legacy SQL query in the

pre SQL

and

post SQL

advanced source or target properties.

A mapping run without

SQL ELT optimization

fails if any of the Pre-SQL and Post-SQL commands fail in a multi-statement query. Previously mappings were successful.

When you specify custom query as a source object and specify a dataset name in the

Source Dataset ID

source advanced property, the mapping runs without full

SQL ELT optimization

.

When you specify custom query as a source object and specify an SQL override query, you must specify a dataset name in the

Source Dataset ID

source advanced property.

When you specify custom query as a source object and specify an SQL override query with different column names, ensure that the data types and the order of the columns that appear in the SQL override query matches the data types and order in which they appear in the custom query.

When you select a view as a source object that contain columns of the Record data type or repeated columns and create a new target at runtime, a validation error appears in the session logs and the mapping runs without full

SQL ELT optimization

.

To load data into columns of date, time, datetime, or timestamp in a

Google BigQuery

target, you must pass the data through the TO_DATE() function as an expression and map the results to the target column.

When you set SCD Type 2 merge optimization context for a mapping, you cannot use filter, joiner, and custom SQL query.

If the mapping contains a Router transformation output connected to a Sequence Generator transformation, the mapping does not push down the mapping logic to the point where the transformation is supported and runs without

SQL ELT optimization

.

When you push down a Router transformation with IIF and IS_SPACE() functions in mapping that reads from and writes to

Google BigQuery

, and the Boolean values are 0 and 1, the mapping fails. When the Boolean values are true and false, the mapping runs successfully.

When you use multiple functions within a transformation and one of the functions cannot be pushed to

Google BigQuery

, the mapping runs without

SQL ELT optimization

.

When the mapping contains multiple pipelines and a function within one of transformations cannot be pushed to

Google BigQuery

, the mapping does not push down the mapping logic to the point where the transformation is supported and the mapping runs without

SQL ELT optimization

.

When you read from or write data to

Google BigQuery

objects associated with different projects in different Google service accounts that resides in different regions, the mapping runs without

SQL ELT optimization

.

When you use the data driven operation to write data to a

Google BigQuery

target and enable the

Disable Duplicate Update Rows

target advanced property, the Secure Agent ignores the

Disable Duplicate Update Rows

property.

When you read data from a

Google BigQuery

source that contains duplicate update keys and enable the

Disable Duplicate Update Rows

target advanced property, the Secure Agent ignores the

Disable Duplicate Update Rows

property.

When you configure a mapping that includes any of the following datetime scenarios, the mapping runs without

SQL ELT optimization

:

Map data from the TIME data type to any other date/time data type
Map data from the DATE data type to the TIME data type
Compare data of the TIME and TIMESTAMP data types with the DATE or DATETIME data types

When you use

$$$SESSSTARTTIME

variable in a custom query, the variable returns the session start time as a string value. Use the following syntax to convert the string values to timestamp or datetime:

When you set the

OptimizeCastsInPDO:true

custom property in the advanced settings for a

Google BigQuery V2

connection, the SESSSTARTTIME, SYSDATE, SYSTIMESTAMP, and TO_DATE functions return data of DATETIME data type.

When you configure a native filter in the Source transformation, ensure that you do not prefix the field name with the table name and dataset name in the filter expression. Otherwise, the mapping fails.

When you configure an insert operation and set the

Write Disposition

property as Write Truncate in the target transformation properties, the mapping appends the records to the target table instead of truncating the target table before loading data. To configure a truncation when you insert records, you need to select the

Truncate target table

option in the target advanced properties.

When you create a target table at runtime and perform DML operations, the mapping might fail if the expression port returns a null value of a non-integer data type.