Stay organized with collections Save and categorize content based on your preferences.

Snowflake SQL translation guide

This document details the similarities and differences in SQL syntax between Snowflake and BigQuery to help accelerate the planning and execution of moving your EDW (Enterprise Data Warehouse) to BigQuery. Snowflake data warehousing is designed to work with Snowflake-specific SQL syntax. Scripts written for Snowflake might need to be altered before you can use them in BigQuery, because the SQL dialects vary between the services. Use batch SQL translation to migrate your SQL scripts in bulk, or interactive SQL translation to translate ad-hoc queries.

Data types

This section shows equivalents between data types in Snowflake and in BigQuery.

Snowflake BigQuery Notes
NUMBER/ DECIMAL/NUMERIC NUMERIC The NUMBER data type in Snowflake supports 38 digits of precision and 37 digits of scale. Precision and scale can be specified according to the user.

The NUMERIC data type in BigQuery supports 38 digits of precision and 9 digits of scale. BigQuery does not support custom digit or scale bounds.
INT/INTEGER INT64
BIGINT INT64
SMALLINT INT64
TINYINT INT64
BYTEINT INT64
FLOAT/
FLOAT4/
FLOAT8
FLOAT64 The FLOAT data type in Snowflake establishes 'NaN' as > X, where X is any FLOAT value (other than 'NaN' itself).

The FLOAT data type in BigQuery establishes 'NaN' as < X, where X is any FLOAT value (other than 'NaN' itself).
DOUBLE/
DOUBLE PRECISION/

REAL
FLOAT64 The DOUBLE data type in Snowflake is synonymous with the FLOAT data type in Snowflake, but is commonly incorrectly displayed as FLOAT. It is properly stored as DOUBLE.
VARCHAR STRING The VARCHAR data type in Snowflake has a maximum length of 16 MB (uncompressed). If length is not specified, the default is the maximum length.

The STRING data type in BigQuery is stored as variable length UTF-8 encoded Unicode. The maximum length is 16,000 characters.
CHAR/CHARACTER STRING The CHAR data type in Snowflake has a maximum length of 1.
STRING/TEXT STRING The STRING data type in Snowflake is synonymous with Snowflake's VARCHAR.
BINARY BYTES
VARBINARY BYTES
BOOLEAN BOOL The BOOL data type in BigQuery can only accept TRUE/FALSE, unlike the BOOL data type in Snowflake, which can accept TRUE/FALSE/NULL.
DATE DATE The DATE type in Snowflake accepts most common date formats, unlike the DATE type in BigQuery, which only accepts dates in the format, 'YYYY-[M]M-[D]D'.
TIME TIME The TIME type in Snowflake supports 0 to 9 nanoseconds of precision, whereas the TIME type in BigQuery supports 0 to 6 nanoseconds of precision.
TIMESTAMP TIMESTAMP
TIMESTAMP_LTZ TIMESTAMP
TIMESTAMP_NTZ/DATETIME TIMESTAMP
TIMESTAMP_TZ TIMESTAMP
OBJECT STRUCT The OBJECT type in Snowflake does not support explicitly-typed values. Values are of the VARIANT type.

The STRUCT type in BigQuery only supports explicitly-typed values.
ARRAY ARRAY The ARRAY type in Snowflake can only support VARIANT types, whereas the ARRAY type in BigQuery can support all data types with the exception of an array itself. The Snowflake ARRAY types can support NULL values via VARIANT types, whereas BigQuery ARRAY types do not.

BigQuery also has the following data types which do not have a direct Snowflake analogue:

Snowflake also has the following data types which do not have a direct BigQuery analogue:

General reference

This section provides details on general references in Snowflake and their mapping to references in BigQuery.

Query syntax and query operators

This section addresses differences in query syntax between Snowflake and BigQuery.

SELECT statement

Most Snowflake SELECT statements are compatible with BigQuery. The following table contains a list of minor differences.

Snowflake BigQuery

SELECT TOP ...

FROM table

SELECT expression

FROM table

ORDER BY expression DESC

LIMIT number

SELECT

x/total AS probability,

ROUND(100 * probability, 1) AS pct

FROM raw_data


Note: Snowflake supports creating and referencing an alias in the same SELECT statement.

SELECT

x/total AS probability,

ROUND(100 * (x/total), 1) AS pct

FROM raw_data

SELECT * FROM (

VALUES (1), (2), (3)

)

SELECT AS VALUE STRUCT(1, 2, 3)

Snowflake aliases and identifiers are case-insensitive by default. To preserve case, enclose aliases and identifiers with double quotes (").

BigQuery supports the following expressions in SELECT statements, which do not have a Snowflake equivalent:

FROM clause

A FROM clause in a query specifies the possible tables, views, subquery, or table functions to use in a SELECT statement. All of these table references are supported in BigQuery.

The following table contains a list of minor differences.

Snowflake BigQuery

SELECT $1, $2 FROM (VALUES (1, 'one'), (2, 'two'));

WITH table1 AS
(
SELECT STRUCT(1 as number, 'one' as spelling)
UNION ALL
SELECT STRUCT(2 as number, 'two' as spelling)
)
SELECT *
FROM table1

SELECT*

FROM table

PIVOT

(aggregate(pivot_column)

FOR value_column IN

(pivot_value_1[,

pivot_value_2 ...]))

...


SELECT* FROM table

UNPIVOT

(value_column

FOR name_column in

(column_list))

...

BigQuery does not offer a direct alternative to Snowflake's PIVOT/UNPIVOT.

SELECT* FROM table SAMPLE(10)

SELECT* FROM table

TABLESAMPLE

BERNOULLI (0.1 PERCENT)

SELECT * FROM table1 AT(TIMESTAMP => timestamp) SELECT * FROM table1 BEFORE(STATEMENT => statementID)

SELECT * FROM table

FOR SYSTEM_TIME AS OF timestamp


Note: BigQuery does not have a direct alternative to Snowflake's BEFORE using a statement ID. The value of timestamp cannot be more than 7 days before the current timestamp.

@[namespace]<stage_name>[/path]

BigQuery does not support the concept of staged files.

SELECT*

FROM table

START WITH predicate

CONNECT BY

[PRIOR] col1 = [PRIOR] col2

[, ...]

...

BigQuery does not offer a direct alternative to Snowflake's CONNECT BY.

BigQuery tables can be referenced in the FROM clause using:

  • [project_id].[dataset_id].[table_name]
  • [dataset_id].[table_name]
  • [table_name]

BigQuery also supports additional table references:

  • Historical versions of the table definition and rows using FOR SYSTEM_TIME AS OF
  • Field paths, or any path that resolves to a field within a data type (that is, a STRUCT)
  • Flattened arrays

WHERE clause

The Snowflake WHERE clause and BigQuery WHERE clause are identical, except for the following:

Snowflake BigQuery

SELECT col1, col2 FROM table1, table2 WHERE col1 = col2(+)

SELECT col1, col2
FROM table1 INNER JOIN table2
ON col1 = col2

Note: BigQuery does not support the (+) syntax for JOINs

JOIN types

Both Snowflake and BigQuery support the following types of join:

Both Snowflake and BigQuery support theONandUSING clause.

The following table contains a list of minor differences.

Snowflake BigQuery

SELECT col1

FROM table1

NATURAL JOIN

table2

SELECT col1

FROM table1

INNER JOIN

table2

USING (col1, col2 [, ...])


Note: In BigQuery, JOIN clauses require a JOIN condition unless it is a CROSS JOIN or one of the joined tables is a field within a data type or an array.

SELECT ... FROM table1 AS t1, LATERAL ( SELECT*

FROM table2 AS t2

WHERE t1.col = t2.col )


Note: Unlike the output of a non-lateral join, the output from a lateral join includes only the rows generated from the in-line view. The rows on the left-hand side do not need to be joined to the right hand side because the rows on the left-hand side have already been taken into account by being passed into the in-line view.

SELECT ... FROM table1 as t1 LEFT JOIN table2 as t2

ON t1.col = t2.col

Note: BigQuery does not support a direct alternative for LATERAL JOINs.

WITH clause

A BigQuery WITH clause contains one or more named subqueries which execute every time a subsequent SELECT statement references them. Snowflake WITH clauses behave the same as BigQuery with the exception that BigQuery does not support WITH RECURSIVE.

GROUP BY clause

Snowflake GROUP BY clauses support GROUP BY, GROUP BY ROLLUP, GROUP BY GROUPING SETS, and GROUP BY CUBE, while BigQuery GROUP BY clauses supports GROUP BY and GROUP BY ROLLUP.

Snowflake HAVING and BigQuery HAVING are synonymous. Note that HAVING occurs after GROUP BY and aggregation, and before ORDER BY.

Snowflake BigQuery

SELECT col1 as one, col2 as two

FROM table GROUP BY (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY ROLLUP (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY ROLLUP (one, 2)

SELECT col1 as one, col2 as two

FROM table GROUP BY GROUPING SETS (one, 2)


Note: Snowflake allows up to 128 grouping sets in the same query block
BigQuery does not support a direct alternative to Snowflake's GROUP BY GROUPING SETS.

SELECT col1 as one, col2 as two

FROM table GROUP BY CUBE (one,2)


Note: Snowflake allows up to 7 elements (128 grouping sets) in each cube
BigQuery does not support a direct alternative to Snowflake's GROUP BY CUBE.

ORDER BY clause

There are some minor differences between Snowflake ORDER BY clauses and BigQuery ORDER BY clauses.

Snowflake BigQuery
In Snowflake, NULLs are ranked last by default (ascending order). In BigQuery, NULLS are ranked first by default (ascending order).
You can specify whether NULL values should be ordered first or last using NULLS FIRST or NULLS LAST, respectively. There's no equivalent to specify whether NULL values should be first or last in BigQuery.

LIMIT/FETCH clause

The LIMIT/FETCH clause in Snowflake constrains the maximum number of rows returned by a statement or subquery. LIMIT (Postgres syntax) and FETCH (ANSI syntax) produce the same result.

In Snowflake and BigQuery, applying a LIMIT clause to a query does not affect the amount of data that is read.

Snowflake BigQuery

SELECT col1, col2

FROM table

ORDER BY col1

LIMIT count OFFSET start


SELECT ...

FROM ...

ORDER BY ...

OFFSET start {[ROW | ROWS]} FETCH {[FIRST | NEXT]} count

{[ROW | ROWS]} [ONLY]


Note: NULL, empty string (''), and $$$$ values are accepted and are treated as "unlimited". Primary use is for connectors and drivers.

SELECT col1, col2

FROM table

ORDER BY col1

LIMIT count OFFSET start


Note: BigQuery does not support FETCH. LIMIT replaces FETCH.

Note: In BigQuery, OFFSET must be used together with a LIMIT count. Make sure to set the count INT64 value to the minimum necessary ordered rows for best performance. Ordering all result rows unnecessarily will lead to worse query execution performance.

QUALIFY clause

The QUALIFY clause in Snowflake allows you to filter results for window functions similar to what HAVING does with aggregate functions and GROUP BY clauses.

Snowflake BigQuery

SELECT col1, col2 FROM table QUALIFY ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2) = 1;

The Snowflake QUALIFY clause with an analytics function like ROW_NUMBER(), COUNT(), and with OVER PARTITION BY is expressed in BigQuery as a WHERE clause on a subquery that contains the analytics value.

Using ROW_NUMBER():

SELECT col1, col2

FROM ( SELECT col1, col2

ROW NUMBER() OVER (PARTITION BY col1 ORDER by col2) RN FROM table ) WHERE RN = 1;


Using ARRAY_AGG(), which supports larger partitions:

SELECT result.* FROM ( SELECT ARRAY_AGG(table ORDER BY table.col2 DESC LIMIT 1) [OFFSET(0)] FROM table

GROUP BY col1 ) AS result;

Functions

The following sections list Snowflake functions and their BigQuery equivalents.

Aggregate functions

The following table shows mappings between common Snowflake aggregate, aggregate analytic, and approximate aggregate functions with their BigQuery equivalents.

Snowflake BigQuery

ANY_VALUE([DISTINCT] expression) [OVER ...]


Note: DISTINCT does not have any effect

ANY_VALUE(expression) [OVER ...]

APPROX_COUNT_DISTINCT([DISTINCT] expression) [OVER ...]


Note: DISTINCT does not have any effect

APPROX_COUNT_DISTINCT(expression)


Note: BigQuery does not support APPROX_COUNT_DISTINCT with Window Functions

APPROX_PERCENTILE(expression, percentile) [OVER ...]


Note: Snowflake does not have the option to RESPECT NULLS

APPROX_QUANTILES([DISTINCT] expression,100) [OFFSET((CAST(TRUNC(percentile * 100) as INT64))]


Note: BigQuery does not support APPROX_QUANTILES with Window Functions

APPROX_PERCENTILE_ACCUMULATE (expression)

BigQuery does not support the ability to store intermediate state when predicting approximate values.

APPROX_PERCENTILE_COMBINE(state)

BigQuery does not support the ability to store intermediate state when predicting approximate values.

APPROX_PERCENTILE_ESTIMATE(state, percentile)

BigQuery does not support the ability to store intermediate state when predicting approximate values.

APPROX_TOP_K(expression, [number [counters]]


Note: If no number parameter is specified, default is 1. Counters should be significantly larger than number.

APPROX_TOP_COUNT(expression, number)


Note: BigQuery does not support APPROX_TOP_COUNT with Window Functions.

APPROX_TOP_K_ACCUMULATE(expression, counters)

BigQuery does not support the ability to store intermediate state when predicting approximate values.

APPROX_TOP_K_COMBINE(state, [counters])

BigQuery does not support the ability to store intermediate state when predicting approximate values.

APPROX_TOP_K_ESTIMATE(state, [k])

BigQuery does not support the ability to store intermediate state when predicting approximate values.

APPROXIMATE_JACCARD_INDEX([DISTINCT] expression)


You can use a custom UDF to implement MINHASH with k distinct hash functions. Another approach to reduce the variance in MINHASH is to keep
k of the minimum values of one hash function. In this case Jaccard index can be approximated as following:

WITH

minhash_A AS (

SELECT DISTINCT FARM_FINGERPRINT(TO_JSON_STRING(t)) AS h

FROM TA AS t

ORDER BY h

LIMIT k),

minhash_B AS (

SELECT DISTINCT FARM_FINGERPRINT(TO_JSON_STRING(t)) AS h

FROM TB AS t

ORDER BY h

LIMIT k)

SELECT

COUNT(*) / k AS APPROXIMATE_JACCARD_INDEX

FROM minhash_A

INNER JOIN minhash_B

ON minhash_A.h = minhash_B.h

APPROXIMATE_SIMILARITY([DISTINCT] expression)


It is a synonym for APPROXIMATE_JACCARD_INDEX and can be implemented in the same way.

ARRAY_AGG([DISTINCT] expression1) [WITHIN GROUP (ORDER BY ...)]

[OVER ([PARTITION BY expression2])]

Note: Snowflake does not support ability to IGNORE|RESPECT NULLS and to LIMIT directly in ARRAY_AGG.

ARRAY_AGG([DISTINCT] expression1

[{IGNORE|RESPECT}] NULLS] [ORDER BY ...] LIMIT ...])

[OVER (...)]

AVG([DISTINCT] expression) [OVER ...]

AVG([DISTINCT] expression) [OVER ...]


Note: BigQuery's AVG does not perform automatic casting on STRINGs.

BITAND_AGG(expression)

[OVER ...]

BIT_AND(expression) [OVER ...]

Note: BigQuery does not implicitly cast character/text columns to the nearest INTEGER.

BITOR_AGG(expression)

[OVER ...]

BIT_OR(expression)

[OVER ...]


Note: BigQuery does not implicitly cast character/text columns to the nearest INTEGER.

BITXOR_AGG([DISTINCT] expression) [OVER ...]

BIT_XOR([DISTINCT] expression) [OVER ...]


Note: BigQuery does not implicitly cast character/text columns to the nearest INTEGER.

BOOLAND_AGG(expression) [OVER ...]


Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero.

LOGICAL_AND(expression)

[OVER ...]

BOOLOR_AGG(expression)

[OVER ...]


Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero.

LOGICAL_OR(expression)

[OVER ...]

BOOLXOR_AGG(expression)

[OVER ([PARTITION BY <partition_expr> ])


Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero.
For numeric expression:

SELECT

CASE COUNT(*)

WHEN 1 THEN TRUE

WHEN 0 THEN NULL

ELSE FALSE

END AS BOOLXOR_AGG

FROM T

WHERE expression != 0


To use OVER you can run the following (boolean example provided):

SELECT

CASE COUNT(expression) OVER (PARTITION BY partition_expr)

WHEN 0 THEN NULL

ELSE

CASE COUNT(

CASE expression

WHEN TRUE THEN 1

END) OVER (PARTITION BY partition_expr)

WHEN 1 THEN TRUE

ELSE FALSE

END

END AS BOOLXOR_AGG

FROM T

CORR(dependent, independent)

[OVER ...]

CORR(dependent, independent)

[OVER ...]

COUNT([DISTINCT] expression [,expression2]) [OVER ...]

COUNT([DISTINCT] expression [,expression2]) [OVER ...]

COVAR_POP(dependent, independent) [OVER ...]

COVAR_POP(dependent, independent) [OVER ...]

COVAR_SAMP(dependent, independent)

[OVER ...]

COVAR_SAMP(dependent, independent)

[OVER ...]

GROUPING(expression1, [,expression2...])

BigQuery does not support a direct alternative to Snowflake's GROUPING. Available through a User-Defined Function.

GROUPING_ID(expression1, [,expression2...])

BigQuery does not support a direct alternative to Snowflake's GROUPING_ID. Available through a User-Defined Function.

HASH_AGG([DISTINCT] expression1, [,expression2])

[OVER ...]

SELECT
BIT_XOR(
FARM_FINGERPRINT(
TO_JSON_STRING(t))) [OVER]
FROM t

SELECT HLL([DISTINCT] expression1, [,expression2])

[OVER ...]


Note: Snowflake does not allow you to specify precision.

SELECT HLL_COUNT.EXTRACT(sketch) FROM (

SELECT HLL_COUNT.INIT(expression)

AS sketch FROM table )


Note: BigQuery does not support HLL_COUNT… with Window Functions. A user cannot include multiple expressions in a single HLL_COUNT... function.

HLL_ACCUMULATE([DISTINCT] expression)


Note: Snowflake does not allow you to specify precision.
HLL_COUNT.INIT(expression [, precision])

HLL_COMBINE([DISTINCT] state)

HLL_COUNT.MERGE_PARTIAL(sketch)

HLL_ESTIMATE(state)

HLL_COUNT.EXTRACT(sketch)

HLL_EXPORT(binary)

BigQuery does not support a direct alternative to Snowflake's HLL_EXPORT.

HLL_IMPORT(object)

BigQuery does not support a direct alternative to Snowflake's HLL_IMPORT.

KURTOSIS(expression)

[OVER ...]

BigQuery does not support a direct alternative to Snowflake's KURTOSIS.

LISTAGG(

[DISTINCT] aggregate_expression

[, delimiter]

)

[OVER ...]

STRING_AGG(

[DISTINCT] aggregate_expression

[, delimiter]

)

[OVER ...]

MEDIAN(expression) [OVER ...]


Note: Snowflake does not support ability to IGNORE|RESPECT NULLS and to LIMIT directly in ARRAY_AGG.

PERCENTILE_CONT(

value_expression,

0.5

[ {RESPECT | IGNORE} NULLS]

) OVER()

MAX(expression) [OVER ...]


MIN(expression) [OVER ...]

MAX(expression) [OVER ...]


MIN(expression) [OVER ...]

MINHASH(k, [DISTINCT] expressions)

You can use a custom UDF to implement MINHASH with k distinct hash functions. Another approach to reduce the variance in MINHASH is to keep k of the minimum values of one hash function: SELECT DISTINCT
FARM_FINGERPRINT(
TO_JSON_STRING(t)) AS MINHASH

FROM t

ORDER BY MINHASH

LIMIT k

MINHASH_COMBINE([DISTINCT] state)

FROM (
SELECT DISTINCT
FARM_FINGERPRINT(
TO_JSON_STRING(t)) AS h
FROM TA AS t
ORDER BY h
LIMIT k
UNION
SELECT DISTINCT
FARM_FINGERPRINT(
TO_JSON_STRING(t)) AS h
FROM TB AS t
ORDER BY h
LIMIT k
)
ORDER BY h
LIMIT k

MODE(expr1)

OVER ( [ PARTITION BY <expr2> ] )

SELECT expr1

FROM (

SELECT

expr1,

ROW_NUMBER() OVER (

PARTITION BY expr2

ORDER BY cnt DESC) rn

FROM (

SELECT

expr1,

expr2,

COUNTIF(expr1 IS NOT NULL) OVER

(PARTITION BY expr2, expr1) cnt

FROM t))

WHERE rn = 1

OBJECT_AGG(key, value) [OVER ...]

You may consider using TO_JSON_STRING to convert a value into JSON-formatted string

PERCENTILE_CONT(percentile) WITHIN GROUP (ORDER BY value_expression)

[OVER ...]

PERCENTILE_CONT(

value_expression,

percentile

[ {RESPECT | IGNORE} NULLS]

) OVER()

PERCENTILE_DISC(percentile) WITHIN GROUP (ORDER BY value_expression)

[OVER ...]

PERCENTILE_DISC(

value_expression,

percentile

[ {RESPECT | IGNORE} NULLS]

) OVER()

REGR_AVGX(dependent, independent)

[OVER ...]

SELECT AVG(independent) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_AVGY(dependent, independent)

[OVER ...]

SELECT AVG(dependent) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_COUNT(dependent, independent)

[OVER ...]

SELECT COUNT(*) [OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

REGR_INTERCEPT(dependent, independent)

[OVER ...]

SELECT

AVG(dependent) -

COVAR_POP(dependent,independent)/

VAR_POP(dependent) *

AVG(independent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_R2(dependent, independent)

[OVER ...]

SELECT

CASE

WHEN VAR_POP(independent) = 0

THEN NULL

WHEN VAR_POP(dependent) = 0 AND VAR_POP(independent) != 0

THEN 1

ELSE POWER(CORR(dependent, independent), 2)

END AS ...

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_SLOPE(dependent, independent)

[OVER ...]

SELECT

COVAR_POP(dependent,independent)/

VAR_POP(dependent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_SXX(dependent, independent)

[OVER ...]

SELECT COUNT(*)*VAR_POP(independent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

REGR_SYY(dependent, independent)

[OVER ...]

SELECT COUNT(*)*VAR_POP(dependent)

[OVER ...]

FROM table

WHERE (

(dependent IS NOT NULL) AND

(independent IS NOT NULL)

)

[GROUP BY ...]

SKEW(expression)

BigQuery does not support a direct alternative to Snowflake's SKEW.

STDDEV([DISTINCT] expression)

[OVER ...]

STDDEV([DISTINCT] expression)

[OVER ...]

STDDEV_POP([DISTINCT] expression)

[OVER ...]

STDDEV_POP([DISTINCT] expression)

[OVER ...]

STDDEV_SAMP([DISTINCT] expression)

[OVER ...]

STDDEV_SAMP([DISTINCT] expression)

[OVER ...]

SUM([DISTINCT] expression)

[OVER ...]

SUM([DISTINCT] expression)

[OVER ...]

VAR_POP([DISTINCT] expression)

[OVER ...]


Note: Snowflake supports the ability to cast VARCHARs to floating point values.

VAR_POP([DISTINCT] expression)

[OVER ...]

VARIANCE_POP([DISTINCT] expression)

[OVER ...]


Note: Snowflake supports the ability to cast VARCHARs to floating point values.

VAR_POP([DISTINCT] expression)

[OVER ...]

VAR_SAMP([DISTINCT] expression)

[OVER ...]


Note: Snowflake supports the ability to cast VARCHARs to floating point values.

VAR_SAMP([DISTINCT] expression)

[OVER ...]

VARIANCE([DISTINCT] expression)

[OVER ...]


Note: Snowflake supports the ability to cast VARCHARs to floating point values.

VARIANCE([DISTINCT] expression)

[OVER ...]

BigQuery also offers the following aggregate, aggregate analytic, and approximate aggregate functions, which do not have a direct analogue in Snowflake:

Bitwise expression functions

The following table shows mappings between common Snowflake bitwise expression functions with their BigQuery equivalents.

If the data type of an expression is not INTEGER, Snowflake attempts to cast to INTEGER. However, BigQuery does not attempt to cast to INTEGER.

Snowflake BigQuery

BITAND(expression1, expression2)

BIT_ADD(x) FROM UNNEST([expression1, expression2]) AS x expression1 & expression2

BITNOT(expression)

~ expression

BITOR(expression1, expression2)

BIT_OR(x) FROM UNNEST([expression1, expression2]) AS x


expression1 | expression2

BITSHIFTLEFT (expression, n)

expression << n

BITSHIFTRIGHT

(expression, n)

expression >> n

BITXOR(expression, expression)


Note: Snowflake does not support DISTINCT.

BIT_XOR([DISTINCT] x) FROM UNNEST([expression1, expression2]) AS x


expression ^ expression

Conditional expression functions

The following table shows mappings between common Snowflake conditional expressions with their BigQuery equivalents.

Snowflake BigQuery

expression [ NOT ] BETWEEN lower AND upper

(expression >= lower AND expression <= upper)

BOOLAND(expression1, expression2)


Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero.

LOGICAL_AND(x)

FROM UNNEST([expression1, expression2]) AS x


expression1 AND expression2

BOOLNOT(expression1)


Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero.

NOT expression

BOOLOR

Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero.

LOGICAL_OR(x) FROM UNNEST([expression1, expression2]) AS x


expression1 OR expression2

BOOLXOR

Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero.
BigQuery does not support a direct alternative to Snowflake's BOOLXOR.

CASE [expression] WHEN condition1 THEN result1 [WHEN condition2 THEN result2]

[...]

[ELSE result3]

END

CASE [expression] WHEN condition1 THEN result1 [WHEN condition2 THEN result2]

[...]

[ELSE result3]

END

COALESCE(expr1, expr2, [,...])


Note: Snowflake requires at least two expressions. BigQuery only requires one.

COALESCE(expr1, [,...])

DECODE(expression, search1, result1, [search2, result2...] [,default])

CASE [expression] WHEN condition1 THEN result1 [WHEN condition2 THEN result2]

[...]

[ELSE result3]

END

Note: BigQuery supports subqueries in condition statements. This can be used to reproduce Snowflake's DECODE. User must use IS NULL instead of = NULL to match NULL select expressions with NULL search expressions.

EQUAL_NULL(expression1, expression2)

BigQuery does not support a direct alternative to Snowflake's EQUAL_NULL.

GREATEST(expression1, [,expression2]...)

GREATEST(expression1, [,expression2]...)

IFF(condition, true_result, false_result)

IF(condition, true_result, false_result)

IFNULL(expression1, expression2)

IFNULL(expression1, expression2)

[ NOT ] IN ...

[ NOT ] IN ...

expression1 IS [ NOT ] DISTINCT FROM expression2

BigQuery does not support a direct alternative to Snowflake's IS [ NOT ] DISTINCT FROM.

expression IS [ NOT ] NULL

expression IS [ NOT ] NULL

IS_NULL_VALUE(variant_expr)

BigQuery does not support VARIANT data types.

LEAST(expression,...)

LEAST(expression,...)

NULLIF(expression1,expression2)

NULLIF(expression1,expression2)

NVL(expression1, expression2)

IFNULL(expression1,expression2)

NVL2(expr1,expr2,expr2)

IF(expr1 IS NOT NULL, expr2,expr3)

REGR_VALX(expr1,expr2)

IF(expr1 IS NULL, NULL, expr2)

Note: BigQuery does not support a direct alternative to Snowflake's REGR... functions.

REGR_VALY(expr1,expr2)

IF(expr2 IS NULL, NULL, expr1)


Note: BigQuery does not support a direct alternative to Snowflake's REGR... functions.

ZEROIFNULL(expression)

IFNULL(expression,0)

Context functions

The following table shows mappings between common Snowflake context functions with their BigQuery equivalents.

Snowflake BigQuery

CURRENT_ACCOUNT()

SESSION_USER()


Note: Not direct comparison. Snowflake returns account ID, BigQuery returns user email address.

CURRENT_CLIENT()

Concept not used in BigQuery

CURRENT_DATABASE()

SELECT schema_name

FROM INFORMATION_SCHEMA.SCHEMATA

CURRENT_DATE[()]


Note: Snowflake does not enforce '()' after CURRENT_DATE command to comply with ANSI standards.

CURRENT_DATE([timezone])


Note: BigQuery's CURRENT_DATE supports optional time zone specification.

CURRENT_REGION()

SELECT location

FROM INFORMATION_SCHEMA.SCHEMATA


Note: BigQuery's INFORMATION_SCHEMA.SCHEMATA returns more generalized location references than Snowflake's CURRENT_REGION(). Not a direct comparison.

CURRENT_ROLE()

Concept not used in BigQuery

CURRENT_SCHEMA()

SELECT schema_name

FROM INFORMATION_SCHEMA.SCHEMATA

CURRENT_SCHEMAS()

Concept not used in BigQuery

CURRENT_SESSION()

Concept not used in BigQuery

CURRENT_STATEMENT()

SELECT query

FROM INFORMATION_SCHEMA.JOBS_BY_*


Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for queries by job type, start/end type, etc.

CURRENT_TIME[([frac_sec_prec])]


Note: Snowflake allows for optional fractional second precision. Valid values range from 0-9 nanoseconds. Default value is 9. To comply with ANSI, this can be called without '()'.

CURRENT_TIME()

CURRENT_TIMESTAMP[([frac_sec_prec])]


Note: Snowflake allows for optional fractional second precision. Valid values range from 0-9 nanoseconds. Default value is 9. To comply with ANSI, this can be called without '()'. Set TIMEZONE as a session parameter.

CURRENT_DATETIME([timezone]) CURRENT_TIMESTAMP()


Note: CURRENT_DATETIME returns DATETIME data type (not supported in Snowflake). CURRENT_TIMESTAMP returns TIMESTAMP data type.

CURRENT_TRANSACTION()

SELECT job_id

FROM INFORMATION_SCHEMA.JOBS_BY_*

Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc.

CURRENT_USER[()]


Note: Snowflake does not enforce '()' after CURRENT_USER command to comply with ANSI standards.

SESSION_USER()


SELECT user_email

FROM INFORMATION_SCHEMA.JOBS_BY_*

Note: Not direct comparison. Snowflake returns username; BigQuery returns user email address.

CURRENT_VERSION()

Concept not used in BigQuery

CURRENT_WAREHOUSE()

SELECT catalg_name

FROM INFORMATION_SCHEMA.SCHEMATA

LAST_QUERY_ID([num])

SELECT job_id

FROM INFORMATION_SCHEMA.JOBS_BY_*


Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc.

LAST_TRANSACTION()

SELECT job_id

FROM INFORMATION_SCHEMA.JOBS_BY_*


Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc.

LOCALTIME()


Note: Snowflake does not enforce '()' after LOCALTIME command to comply with ANSI standards.

CURRENT_TIME()

LOCALTIMESTAMP()

CURRENT_DATETIME([timezone]) CURRENT_TIMESTAMP()


Note: CURRENT_DATETIME returns DATETIME data type (not supported in Snowflake). CURRENT_TIMESTAMP returns TIMESTAMP data type.

Conversion functions

The following table shows mappings between common Snowflake conversion functions with their BigQuery equivalents.

Keep in mind that functions that seem identical in Snowflake and BigQuery may return different data types.

Snowflake BigQuery

CAST(expression AS type)


expression :: type

CAST(expression AS type)

TO_ARRAY(expression)

[expression]


ARRAY(subquery)

TO_BINARY(expression[, format])


Note: Snowflake supports HEX, BASE64, and UTF-8 conversion. Snowflake also supports TO_BINARY using the VARIANT data type. BigQuery does not have an alternative to the VARIANT data type.

TO_HEX(CAST(expression AS BYTES)) TO_BASE64(CAST(expression AS BYTES))

CAST(expression AS BYTES)


Note: BigQuery's default STRING casting uses UTF-8 encoding. Snowflake does not have an option to support BASE32 encoding.

TO_BOOLEAN(expression)


Note:
  • INT64
    TRUE:
    otherwise, FALSE: 0
  • STRING
    TRUE: "true"/"t"/"yes"/"y"/"on"/"1", FALSE: "false"/"f"/"no"/"n"/"off"/"0"

CAST(expression AS BOOL)


Note:
  • INT64
    TRUE:
    otherwise, FALSE: 0
  • STRING
    TRUE: "true", FALSE: "false"

TO_CHAR(expression[, format])


TO_VARCHAR(expression[, format])


Note: Snowflake's format models can be found here. BigQuery does not have an alternative to the VARIANT data type.

CAST(expression AS STRING)


Note: BigQuery's input expression can be formatted using FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIME, or FORMAT_TIMESTAMP.

TO_DATE(expression[, format])


DATE(expression[, format])


Note: Snowflake supports the ability to directly convert INTEGER types to DATE types. Snowflake's format models can be found here. BigQuery does not have an alternative to the VARIANT data type.

CAST(expression AS DATE)


Note: BigQuery's input expression can be formatted using FORMAT, FORMAT_DATETIME, or FORMAT_TIMESTAMP.

TO_DECIMAL(expression[, format]

[,precision[, scale]]


TO_NUMBER(expression[, format]

[,precision[, scale]]


TO_NUMERIC(expression[, format]

[,precision[, scale]]


Note: Snowflake's format models for the DECIMAL, NUMBER, and NUMERIC data types can be found here. BigQuery does not have an alternative to the VARIANT data type.

ROUND(CAST(expression AS NUMERIC)

, x)


Note: BigQuery's input expression can be formatted using FORMAT.

TO_DOUBLE(expression[, format])


Note: Snowflake's format models for the DOUBLE data types can be found here. BigQuery does not have an alternative to the VARIANT data type.

CAST(expression AS FLOAT64)


Note: BigQuery's input expression can be formatted using FORMAT.

TO_JSON(variant_expression)

BigQuery does not have an alternative to Snowflake's VARIANT data type.

TO_OBJECT(variant_expression)

BigQuery does not have an alternative to Snowflake's VARIANT data type.

TO_TIME(expression[, format])


TIME(expression[, format])


Note: Snowflake's format models for the STRING data types can be found here. BigQuery does not have an alternative to the VARIANT data type.

CAST(expression AS TIME)


Note: BigQuery does not have an alternative to Snowflake's VARIANT data type. BigQuery's input expression can be formatted using FORMAT, FORMAT_DATETIME, FORMAT_TIMESTAMP, or FORMAT_TIME.

TO_TIMESTAMP(expression[, scale])


TO_TIMESTAMP_LTZ(expression[, scale])


TO_TIMESTAMP_NTZ(expression[, scale])


TO_TIMESTAMP_TZ(expression[, scale])


Note: BigQuery does not have an alternative to the VARIANT data type.

CAST(expression AS TIMESTAMP)


Note: BigQuery's input expression can be formatted using FORMAT, FORMAT_DATE, FORMAT_DATETIME, FORMAT_TIME. Timezone can be included/not included through FORMAT_TIMESTAMP parameters.

TO_VARIANT(expression)

BigQuery does not have an alternative to Snowflake's VARIANT data type.

TO_XML(variant_expression)

BigQuery does not have an alternative to Snowflake's VARIANT data type.

TRY_CAST(expression AS type)

SAFE_CAST(expression AS type)

TRY_TO_BINARY(expression[, format])

TO_HEX(SAFE_CAST(expression AS BYTES)) TO_BASE64(SAFE_CAST(expression AS BYTES))

SAFE_CAST(expression AS BYTES)

TRY_TO_BOOLEAN(expression)

SAFE_CAST(expression AS BOOL)

TRY_TO_DATE(expression)

SAFE_CAST(expression AS DATE)

TRY_TO_DECIMAL(expression[, format]

[,precision[, scale]]


TRY_TO_NUMBER(expression[, format]

[,precision[, scale]]


TRY_TO_NUMERIC(expression[, format]

[,precision[, scale]]

ROUND(

SAFE_CAST(expression AS NUMERIC)

, x)

TRY_TO_DOUBLE(expression)

SAFE_CAST(expression AS FLOAT64)

TRY_TO_TIME(expression)

SAFE_CAST(expression AS TIME)

TRY_TO_TIMESTAMP(expression)


TRY_TO_TIMESTAMP_LTZ(expression)


TRY_TO_TIMESTAMP_NTZ(expression)


TRY_TO_TIMESTAMP_TZ(expression)

SAFE_CAST(expression AS TIMESTAMP)

BigQuery also offers the following conversion functions, which do not have a direct analogue in Snowflake:

Data generation functions

The following table shows mappings between common Snowflake data generation functions with their BigQuery equivalents.

Snowflake BigQuery

NORMAL(mean, stddev, gen)

BigQuery does not support a direct comparison to Snowflake's NORMAL.

RANDOM([seed])

IF(RAND()>0.5, CAST(RAND()*POW(10, 18) AS INT64),

(-1)*CAST(RAND()*POW(10, 18) AS

INT64))


Note: BigQuery does not support seeding

RANDSTR(length, gen)

BigQuery does not support a direct comparison to Snowflake's RANDSTR.
SEQ1 / SEQ2 / SEQ4 / SEQ8 BigQuery does not support a direct comparison to Snowflake's SEQ_.

UNIFORM(min, max, gen)

CAST(min + RAND()*(max-min) AS INT64)


Note:Use persistent UDFs to create an equivalent to Snowflake's UNIFORM. Example here.
UUID_STRING([uuid, name])

Note: Snowflake returns 128 random bits. Snowflake supports both version 4 (random) and version 5 (named) UUIDs.

GENERATE_UUID()


Note: BigQuery returns 122 random bits. BigQuery only supports version 4 UUIDs.

ZIPF(s, N, gen)

BigQuery does not support a direct comparison to Snowflake's ZIPF.

Date and time functions

The following table shows mappings between common Snowflake date and time functions with their BigQuery equivalents. BigQuery data and time functions include Date functions, Datetime functions, Time functions, and Timestamp functions.

Snowflake BigQuery

ADD_MONTHS(date, months)

CAST(

DATE_ADD(

date,

INTERVAL integer MONTH

) AS TIMESTAMP

)

CONVERT_TIMEZONE(source_tz, target_tz, source_timestamp)


CONVERT_TIMEZONE(target_tz, source_timestamp)

PARSE_TIMESTAMP(

"%c%z",

FORMAT_TIMESTAMP(

"%c%z",

timestamp,

target_timezone

)

)


Note: source_timezone is always UTC in BigQuery

DATE_FROM_PARTS(year, month, day)


Note: Snowflake supports overflow and negative dates. For example, DATE_FROM_PARTS(2000, 1 + 24, 1) returns Jan 1, 2002. This is not supported in BigQuery.

DATE(year, month, day)


DATE(timestamp_expression[, timezone])


DATE(datetime_expression)

DATE_PART(part, dateOrTime)


Note: Snowflake supports the day of week ISO, nanosecond, and epoch second/millisecond/microsecond/nanosecond part types. BigQuery does not. See full list of Snowflake part types here.

EXTRACT(part FROM dateOrTime)


Note: BigQuery supports the week(<weekday>), microsecond, and millisecond part types. Snowflake does not. See full list of BigQuery part types here and here.

DATE_TRUNC(part, dateOrTime)


Note: Snowflake supports the nanosecond part type. BigQuery does not. See full list of Snowflake part types here.

DATE_TRUNC(date, part)


DATETIME_TRUNC(datetime, part)


TIME_TRUNC(time, part)


TIMESTAMP_TRUNC(timestamp, part[, timezone])


Note: BigQuery supports the week(<weekday>), ISO week, and ISO year part types. Snowflake does not.

DATEADD(part, value, dateOrTime)

DATE_ADD(date, INTERVAL value part)

DATEDIFF(

part,

expression1,

expression2

)


Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function.

DATE_DIFF(

dateExpression1,

dateExpression2,

part

)


DATETIME_DIFF(

datetimeExpression1,

datetimeExpression2,

part

)


TIME_DIFF(

timeExpression1,

timeExpression2,

part

)


TIMESTAMP_DIFF(

timestampExpression1,

timestampExpression2,

part

)


Note: BigQuery supports the week(<weekday>) and ISO year part types.

DAYNAME(dateOrTimestamp)

FORMAT_DATE('%a', date)


FORMAT_DATETIME('%a', datetime)


FORMAT_TIMESTAMP('%a', timestamp)

EXTRACT(part FROM dateOrTime)


Note: Snowflake supports the day of week ISO, nanosecond, and epoch second/millisecond/microsecond/nanosecond part types. BigQuery does not. See full list of Snowflake part types here.

EXTRACT(part FROM dateOrTime)


Note: BigQuery supports the week(<weekday>), microsecond, and millisecond part types. Snowflake does not. See full list of BigQuery part types here and here.

[HOUR, MINUTE, SECOND](timeOrTimestamp)

EXTRACT(part FROM timestamp [AT THE ZONE timezone])

LAST_DAY(dateOrTime[, part])

DATE_SUB( DATE_TRUNC(

DATE_ADD(date, INTERVAL

1 part),

part),

INTERVAL 1 DAY)

MONTHNAME(dateOrTimestamp)

FORMAT_DATE('%b', date)


FORMAT_DATETIME('%b', datetime)


FORMAT_TIMESTAMP('%b', timestamp)

NEXT_DAY(dateOrTime, dowString)

DATE_ADD(

DATE_TRUNC(

date,

WEEK(dowString)),

INTERVAL 1 WEEK)


Note: dowString might need to be reformatted. For example, Snowflake's 'su' will be BigQuery's 'SUNDAY'.

PREVIOUS_DAY(dateOrTime, dowString)

DATE_TRUNC(

date,

WEEK(dowString)

)


Note: dowString might need to be reformatted. For example, Snowflake's 'su' will be BigQuery's 'SUNDAY'.

TIME_FROM_PARTS(hour, minute, second[, nanosecond)


Note: Snowflake supports overflow times. For example, TIME_FROM_PARTS(0, 100, 0) returns 01:40:00... This is not supported in BigQuery. BigQuery does not support nanoseconds.

TIME(hour, minute, second)


TIME(timestamp, [timezone])


TIME(datetime)

TIME_SLICE(dateOrTime, sliceLength, part[, START]


TIME_SLICE(dateOrTime, sliceLength, part[, END]

DATE_TRUNC(

DATE_SUB(CURRENT_DATE(),

INTERVAL value MONTH),

MONTH)


DATE_TRUNC(

DATE_ADD(CURRENT_DATE(),

INTERVAL value MONTH),

MONTH)


Note: BigQuery does not support a direct, exact comparison to Snowflake's TIME_SLICE. Use DATETINE_TRUNC, TIME_TRUNC, TIMESTAMP_TRUNC for appropriate data type.

TIMEADD(part, value, dateOrTime)

TIME_ADD(time, INTERVAL value part)

TIMEDIFF(

part,

expression1,

expression2,

)


Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function.

DATE_DIFF(

dateExpression1,

dateExpression2,

part

)


DATETIME_DIFF(

datetimeExpression1,

datetimeExpression2,

part

)


TIME_DIFF(

timeExpression1,

timeExpression2,

part

)


TIMESTAMP_DIFF(

timestampExpression1,

timestampExpression2,

part

)


Note: BigQuery supports the week(<weekday>) and ISO year part types.

TIMESTAMP_[LTZ, NTZ, TZ _]FROM_PARTS (year, month, day, hour, second [, nanosecond][, timezone])

TIMESTAMP(

string_expression[, timezone] | date_expression[, timezone] |

datetime_expression[, timezone]

)


Note: BigQuery requires timestamps be inputted as STRING types. Example: "2008-12-25 15:30:00"

TIMESTAMPADD(part, value, dateOrTime)

TIMESTAMPADD(timestamp, INTERVAL value part)

TIMESTAMPDIFF(

part,

expression1,

expression2,

)


Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function.

DATE_DIFF(

dateExpression1,

dateExpression2,

part

)


DATETIME_DIFF(

datetimeExpression1,

datetimeExpression2,

part

)


TIME_DIFF(

timeExpression1,

timeExpression2,

part

)


TIMESTAMP_DIFF(

timestampExpression1,

timestampExpression2,

part

)


Note: BigQuery supports the week(<weekday>) and ISO year part types.

TRUNC(dateOrTime, part)


Note: Snowflake supports the nanosecond part type. BigQuery does not. See full list of Snowflake part types here.

DATE_TRUNC(date, part)


DATETIME_TRUNC(datetime, part)


TIME_TRUNC(time, part)


TIMESTAMP_TRUNC(timestamp, part[, timezone])


Note: BigQuery supports the week(<weekday>), ISO week, and ISO year part types. Snowflake does not.

[YEAR*, DAY*, WEEK*, MONTH, QUARTER](dateOrTimestamp)

EXTRACT(part FROM timestamp [AT THE ZONE timezone])

BigQuery also offers the following date and time functions, which do not have a direct analogue in Snowflake:

Information schema and table functions

BigQuery does not conceptually support many of Snowflake's information schema and table functions. Snowflake offers the following information schema and table functions, which do not have a direct analogue in BigQuery:

Below is a list of associated BigQuery and Snowflake information schema and table functions.

Snowflake BigQuery
QUERY_HISTORY

QUERY_HISTORY_BY_*
INFORMATION_SCHEMA.JOBS_BY_*

Note: Not a direct alternative.
TASK_HISTORY INFORMATION_SCHEMA.JOBS_BY_*

Note: Not a direct alternative.

BigQuery offers the following information schema and table functions, which do not have a direct analogue in Snowflake:

Numeric functions

The following table shows mappings between common Snowflake numeric functions with their BigQuery equivalents.

Snowflake BigQuery

ABS(expression)

ABS(expression)

ACOS(expression)

ACOS(expression)

ACOSH(expression)

ACOSH(expression)

ASIN(expression)

ASIN(expression)

ASINH(expression)

ASINH(expression)

ATAN(expression)

ATAN(expression)

ATAN2(y, x)

ATAN2(y, x)

ATANH(expression)

ATANH(expression)

CBRT(expression)

POW(expression, ⅓)

CEIL(expression [, scale])

CEIL(expression)


Note: BigQuery's CEIL does not support the ability to indicate precision or scale. ROUND does not allow you to specify to round up.

COS(expression)

COS(expression)

COSH(expression)

COSH(expression)

COT(expression)

1/TAN(expression)

DEGREES(expression)

(expression)*(180/ACOS(-1))

EXP(expression)

EXP(expression)

FACTORIAL(expression)

BigQuery does not have a direct alternative to Snowflake's FACTORIAL. Use a user-defined function.

FLOOR(expression [, scale])

FLOOR(expression)


Note: BigQuery's FLOOR does not support the ability to indicate precision or scale. ROUND does not allow you to specify to round up. TRUNC performs synonymously for positive numbers but not negative numbers, as it evaluates absolute value.

HAVERSINE(lat1, lon1, lat2, lon2)

ST_DISTANCE( ST_GEOGPOINT(lon1, lat1),

ST_GEOGPOINT(lon2, lat2)

)/1000


Note: Not an exact match, but close enough.

LN(expression)

LN(expression)

LOG(base, expression)

LOG(expression [,base])


LOG10(expression)


Note:Default base for LOG is 10.

MOD(expression1, expression2)

MOD(expression1, expression2)

PI()

ACOS(-1)

POW(x, y)


POWER(x, y)

POW(x, y)


POWER(x, y)

RADIANS(expression)

(expression)*(ACOS(-1)/180)

ROUND(expression [, scale])

ROUND(expression, [, scale])

SIGN(expression)

SIGN(expression)

SIN(expression)

SIN(expression)

SINH(expression)

SINH(expression)

SQRT(expression)

SQRT(expression)

SQUARE(expression)

POW(expression, 2)

TAN(expression)

TAN(expression)

TANH(expression)

TANH(expression)

TRUNC(expression [, scale])


TRUNCATE(expression [, scale])

TRUNC(expression [, scale])


Note: BigQuery's returned value must be smaller than the expression; it does not support equal to.

BigQuery also offers the following mathematical functions, which do not have a direct analogue in Snowflake:

Semi-structured data functions

Snowflake BigQuery
ARRAY_APPEND Custom user-defined function
ARRAY_CAT ARRAY_CONCAT
ARRAY_COMPACT Custom user-defined function
ARRAY_CONSTRUCT [ ]
ARRAY_CONSTRUCT_COMPACT Custom user-defined function
ARRAY_CONTAINS Custom user-defined function
ARRAY_INSERT Custom user-defined function
ARRAY_INTERSECTION Custom user-defined function
ARRAY_POSITION Custom user-defined function
ARRAY_PREPEND Custom user-defined function
ARRAY_SIZE ARRAY_LENGTH
ARRAY_SLICE Custom user-defined function
ARRAY_TO_STRING ARRAY_TO_STRING
ARRAYS_OVERLAP Custom user-defined function
AS_<object_type> CAST
AS_ARRAY CAST
AS_BINARY CAST
AS_BOOLEAN CAST
AS_CHAR , AS_VARCHAR CAST
AS_DATE CAST
AS_DECIMAL , AS_NUMBER CAST
AS_DOUBLE , AS_REAL CAST
AS_INTEGER CAST
AS_OBJECT CAST
AS_TIME CAST
AS_TIMESTAMP_* CAST
CHECK_JSON Custom user-defined function
CHECK_XML Custom user-defined function
FLATTEN UNNEST
GET Custom user-defined function
GET_IGNORE_CASE Custom user-defined function

GET_PATH , :

Custom user-defined function
IS_<object_type> Custom user-defined function
IS_ARRAY Custom user-defined function
IS_BINARY Custom user-defined function
IS_BOOLEAN Custom user-defined function
IS_CHAR , IS_VARCHAR Custom user-defined function
IS_DATE , IS_DATE_VALUE Custom user-defined function
IS_DECIMAL Custom user-defined function
IS_DOUBLE , IS_REAL Custom user-defined function
IS_INTEGER Custom user-defined function
IS_OBJECT Custom user-defined function
IS_TIME Custom user-defined function
IS_TIMESTAMP_* Custom user-defined function
OBJECT_CONSTRUCT Custom user-defined function
OBJECT_DELETE Custom user-defined function
OBJECT_INSERT Custom user-defined function
PARSE_JSON JSON_EXTRACT
PARSE_XML Custom user-defined function
STRIP_NULL_VALUE Custom user-defined function
STRTOK_TO_ARRAY SPLIT
TRY_PARSE_JSON Custom user-defined function
TYPEOF Custom user-defined function