Multiple Transformations

We wish to carry out multiple data transformation operations on the same table.

WITH step_1 AS
         (SELECT *,
                 NOT col_4       AS col_13,
                 ABS(col_1)      AS col_14,
                 ROUND(col_3, 2) AS col_15
          FROM refcon.dataset.table_1)
SELECT *,
       SAFE_DIVIDE(col_14, col_15) AS col_26
FROM step_1;

Here is how this works:

• We can create or modify multiple columns in a single SELECT statement. To do so, we specify multiple data transformation expressions, such as those covered in Common Transformation Scenarios, separated by commas.
• We cannot refer to columns created earlier in the same SELECT statement (more precisely; in the same data manipulation chain), we need to use a CTE to split the transformation into steps.

• In this example, the data transformation expressions in step_1 are:

  • NOT col_4 AS col_13 where we use the logical complement operator NOT to create a new column col_13 that is the logical complement of column col_4 which is a logical data type, i.e. it can take the values TRUE or FALSE.
  • ABS(col_1) AS col_14 where we create a new column col_14 whose values are the absolute values of the corresponding values of the numeric column col_1.
  • ROUND(col_3, 2) AS col_15 where we create a new column col_15 whose values are the rounding to 2 decimal places of the corresponding values of the numeric column col_3.

• We can use columns created earlier in step_1 CTE as inputs to data transformation expressions which we do here in SAFE_DIVIDE(col_14, col_15) AS col_26.

• The resulting columns will be added on the right end of the original table and in the same order they are defined. If a column already exists, a new column will be added with a _1 suffix.