The query builder will generally prevent you from running transforms that are invalid, and errors should be rare. However, some errors can only be detected as the query is run — in these cases the job will fail as soon as it encounters an error, logging the error message to the top of the transform builder.
Some common errors:
This error occurs when a query utilizes too much memory, yet it is often easily resolvable and due to unintended behavior within the transform. This error is caused by a certain component of the query not being parallelizable, which often occurs when combining and / or ordering many distinct values. We recommend investigating the following culprits:
- 1.Investigate any
orderclauses in your transform — either at the bottom of the transform or in a partitioned query. Often, attempts to order on hundreds of millions or billions of distinct values will fail. Note that ordering rows at the bottom of the transform does not affect your output, and is only useful in preparing your data for export.
- 2.Confirm that none of your aggregation methods are creating massive cells. For example, using the
String aggregatemethod on an exceptionally large partition can collapse and concatenate many values into one record — if the cell becomes too big, this error will be thrown.
When converting between variable types, all values must be appropriately formatted for conversion to the new type. For example, the value
"1,000"is not a valid integer and will throw an error when being converted from a string to an integer.
There are several options for getting around cast errors:
- 1.Choose the "Set incompatible values to null" option when changing types. Note that this will set all invalid values to
NULL, potentially causing unintended side effects. Use with caution.
- 2.Filter out all records that have incompatible values.
- 3.Create a new variable, using the
Casemethod to convert any invalid values to something that can be appropriately cast.
You can learn more about variable types and conversion here.