SnowConvert AI - BigQuery - Operators¶

IS operators¶

IS operators return TRUE or FALSE for the condition they are testing. They never return NULL, even for NULL inputs. (BigQuery SQL Language Reference IS operators)

BigQuery	Snowflake
`X IS TRUE`	`NVL(X, FALSE)`
`X IS NOT TRUE`	`NVL(NOT X, TRUE)`
`X IS FALSE`	`NVL(NOT X, FALSE)`
`X IS NOT FALSE`	`NVL(X, TRUE)`
`X IS NULL`	`X IS NULL`
`X IS NOT NULL`	`X IS NOT NULL`
`X IS UNKNOWN`	`X IS NULL`
`X IS NOT UNKNOWN`	`X IS NOT NULL`

UNNEST operator¶

The UNNEST operator takes an array and returns a table with one row for each element in the array. (BigQuery SQL Language Reference UNNEST operator).

This operator will be emulated using the FLATTEN function, the VALUE and INDEX columns returned by the function will be renamed accordingly to match the UNNEST operator aliases

BigQuery	Snowflake
`UNNEST(arrayExpr)`	`FLATTEN(INPUT => arrayExpr) AS F0_(SEQ, KEY, PATH, INDEX, F0_, THIS)`
`UNNEST(arrayExpr) AS alias`	`FLATTEN(INPUT => arrayExpr) AS alias(SEQ, KEY, PATH, INDEX, alias, THIS)`
`UNNEST(arrayExpr) AS alias WITH OFFSET`	`FLATTEN(INPUT => arrayExpr) AS alias(SEQ, KEY, PATH, OFFSET, alias, THIS)`
`UNNEST(arrayExpr) AS alias WITH OFFSET AS offsetAlias`	`FLATTEN(INPUT => arrayExpr) AS alias(SEQ, KEY, PATH, offsetAlias, alias, THIS)`

SELECT * with UNNEST¶

When the UNNEST operator is used inside a SELECT * statement the EXCLUDE keyword will be used to remove the unnecessary FLATTEN columns.

Input:

SELECT * FROM UNNEST ([10,20,30]) AS numbers WITH OFFSET position;

Generated code:

 SELECT
* EXCLUDE(SEQ, KEY, PATH, THIS)
FROM
TABLE(FLATTEN(INPUT => [10,20,30])) AS numbers (
SEQ,
KEY,
PATH,
position,
numbers,
THIS
);

PIVOT operator¶

The PIVOT operator rotates rows into columns by aggregating values for each distinct pivot value. (BigQuery SQL Language Reference PIVOT operator).

Snowflake supports the same syntax directly, including the optional IN-list aliasing form ('Q1' AS first), per the Snowflake PIVOT documentation. SnowConvert AI passes single-aggregate PIVOT operators through unchanged.

Because the two engines can name the resulting columns differently, SnowConvert AI emits SSC-FDM-BQ0013 when the column names may diverge — that is, when at least one IN-list value is unaliased, or the aggregate function is aliased. The marker is suppressed when every IN-list value is aliased and the aggregate has no alias, since both engines then produce identical column names.

Input:

SELECT * FROM sales PIVOT(SUM(amount) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'));

Generated code:

SELECT
  *
FROM
  sales
  --** SSC-FDM-BQ0013 - BIGQUERY PIVOT OUTPUT COLUMN NAMES MAY DIFFER FROM SNOWFLAKE; DOWNSTREAM QUERIES THAT REFERENCE PIVOT OUTPUT COLUMNS MAY NEED UPDATES **
  PIVOT(
    SUM(amount)
    FOR quarter
    IN ('Q1', 'Q2', 'Q3', 'Q4')
  );

Note: Multi-aggregate PIVOT (PIVOT(agg1, agg2 FOR ...)) is not supported in Snowflake. SnowConvert AI currently passes the multi-aggregate form through unchanged; a dedicated EWI is planned as follow-up work.