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. Snowflake SQL is supported by both tools in preview.

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.

BigQuery supports NUMERIC and BIGNUMERIC with optionally specified precision and scale within certain bounds.
INT/INTEGER BIGNUMERIC INT/INTEGER and all other INT-like datatypes, such as BIGINT, TINYINT, SMALLINT, BYTEINT represent an alias for the NUMBER datatype where the precision and scale cannot be specified and is always NUMBER(38, 0)
BIGINT BIGNUMERIC
SMALLINT BIGNUMERIC
TINYINT BIGNUMERIC
BYTEINT BIGNUMERIC
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 DATETIME TIMESTAMP is a user-configurable alias which defaults to TIMESTAMP_NTZ which maps to DATETIME in BigQuery.
TIMESTAMP_LTZ TIMESTAMP
TIMESTAMP_NTZ/DATETIME DATETIME
TIMESTAMP_TZ TIMESTAMP
OBJECT JSON The OBJECT type in Snowflake does not support explicitly-typed values. Values are of the VARIANT type.
VARIANT JSON The OBJECT type in Snowflake does not support explicitly-typed values. Values are of the VARIANT type.
ARRAY ARRAY<JSON> 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.

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

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 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_AND(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 catalog_name

FROM INFORMATION_SCHEMA.SCHEMATA

This returns a table of project names. Not a direct comparison.

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

This returns a table of all datasets (also called schemas) available in the project or region. Not a direct comparison.

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
XMLGET Custom user-defined function

String and binary functions

Snowflake BigQuery

string1 || string2

CONCAT(string1, string2)

ASCII

TO_CODE_POINTS(string1)[OFFSET(0)]

BASE64_DECODE_BINARY

SAFE_CONVERT_BYTES_TO_STRING(

FROM_BASE64(<bytes_input>)

)

BASE64_DECODE_STRING

SAFE_CONVERT_BYTES_TO_STRING(

FROM_BASE64(<string1>)

)

BASE64_ENCODE

TO_BASE64(

SAFE_CAST(<string1> AS BYTES)

)

BIT_LENGTH

BYTE_LENGTH * 8

CHARACTER_LENGTH

CHARINDEX(substring, string)

STRPOS(string, substring)

CHR,CHAR

CODE_POINTS_TO_STRING([number])

COLLATE Custom user-defined function
COLLATION Custom user-defined function
COMPRESS Custom user-defined function

CONCAT(string1, string2)

CONCAT(string1, string2)

Note: BigQuery's CONCAT(...) supports concatenating any number of strings.
CONTAINS Custom user-defined function
DECOMPRESS_BINARY Custom user-defined function
DECOMPRESS_STRING Custom user-defined function
EDITDISTANCE Custom user-defined function
ENDSWITH Custom user-defined function
HEX_DECODE_BINARY

SAFE_CONVERT_BYTES_TO_STRING(

FROM_HEX(<string1>)

HEX_DECODE_STRING

SAFE_CONVERT_BYTES_TO_STRING(

FROM_HEX(<string1>)

HEX_ENCODE

TO_HEX(

SAFE_CAST(<string1> AS BYTES))

ILIKE Custom user-defined function
ILIKE ANY Custom user-defined function
INITCAP INITCAP
INSERT Custom user-defined function
LEFT User Defined Function
LENGTH

LENGTH(expression)

LIKE LIKE
LIKE ALL Custom user-defined function
LIKE ANY Custom user-defined function
LOWER

LOWER(string)

LPAD

LPAD(string1, length[, string2])

LTRIM

LTRIM(string1, trim_chars)

MD5,MD5_HEX

MD5(string)

MD5_BINARY Custom user-defined function
OCTET_LENGTH Custom user-defined function
PARSE_IP Custom user-defined function
PARSE_URL Custom user-defined function
POSITION

STRPOS(string, substring)

REPEAT

REPEAT(string, integer)

REPLACE

REPLACE(string1, old_chars, new_chars)

REVERSE

number_characters

)

REVERSE(expression)

RIGHT User Defined Function
RPAD RPAD
RTRIM

RTRIM(string, trim_chars)

RTRIMMED_LENGTH Custom user-defined function
SHA1,SHA1_HEX

SHA1(string)

SHA1_BINARY Custom user-defined function
SHA2,SHA2_HEX Custom user-defined function
SHA2_BINARY Custom user-defined function
SOUNDEX Custom user-defined function
SPACE Custom user-defined function
SPLIT SPLIT
SPLIT_PART Custom user-defined function
SPLIT_TO_TABLE Custom user-defined function
STARTSWITH Custom user-defined function
STRTOK

SPLIT(instring, delimiter)[ORDINAL(tokennum)]


Note: The entire delimiter string argument is used as a single delimiter. The default delimiter is a comma.
STRTOK_SPLIT_TO_TABLE Custom user-defined function
SUBSTR,SUBSTRING SUBSTR
TRANSLATE Custom user-defined function
TRIM TRIM
TRY_BASE64_DECODE_BINARY Custom user-defined function
TRY_BASE64_DECODE_STRING

SUBSTR(string, 0, integer)

TRY_HEX_DECODE_BINARY

SUBSTR(string, -integer)

TRY_HEX_DECODE_STRING

LENGTH(expression)

UNICODE Custom user-defined function

UPPER

UPPER

String functions (regular expressions)

Snowflake BigQuery
REGEXP

IF(REGEXP_CONTAINS,1,0)=1

REGEXP_COUNT

ARRAY_LENGTH(

REGEXP_EXTRACT_ALL(

source_string,

pattern

)

)


If position is specified:

ARRAY_LENGTH(

REGEXP_EXTRACT_ALL(

SUBSTR(source_string, IF(position <= 0, 1, position)),

pattern

)

)


Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax.
REGEXP_INSTR

IFNULL(

STRPOS(

source_string,

REGEXP_EXTRACT(

source_string,

pattern)

), 0)


If position is specified:

IFNULL(

STRPOS(

SUBSTR(source_string, IF(position <= 0, 1, position)),

REGEXP_EXTRACT(

SUBSTR(source_string, IF(position <= 0, 1, position)),

pattern)

) + IF(position <= 0, 1, position) - 1, 0)


If occurrence is specified:

IFNULL(

STRPOS(

SUBSTR(source_string, IF(position <= 0, 1, position)),

REGEXP_EXTRACT_ALL(

SUBSTR(source_string, IF(position <= 0, 1, position)),

pattern

)[SAFE_ORDINAL(occurrence)]

) + IF(position <= 0, 1, position) - 1, 0)


Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax.

REGEXP_LIKE

IF(REGEXP_CONTAINS,1,0)=1

REGEXP_REPLACE

REGEXP_REPLACE(

source_string,

pattern,

""

)


If replace_string is specified:

REGEXP_REPLACE(

source_string,

pattern,

replace_string

)


If position is specified:

CASE

WHEN position > LENGTH(source_string) THEN source_string

WHEN position <= 0 THEN

REGEXP_REPLACE(

source_string,

pattern,

""

)

ELSE

CONCAT(

SUBSTR(

source_string, 1, position - 1),

REGEXP_REPLACE(

SUBSTR(source_string, position),

pattern,

replace_string

)

)

END


Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax.
REGEXP_SUBSTR

REGEXP_EXTRACT(

source_string,

pattern

)


If position is specified:

REGEXP_EXTRACT(

SUBSTR(source_string, IF(position <= 0, 1, position)),

pattern

)


If occurrence is specified:

REGEXP_EXTRACT_ALL(

SUBSTR(source_string, IF(position <= 0, 1, position)),

pattern

)[SAFE_ORDINAL(occurrence)]


Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax.
RLIKE

IF(REGEXP_CONTAINS,1,0)=1

System functions

Snowflake BigQuery
SYSTEM$ABORT_SESSION Custom user-defined function
SYSTEM$ABORT_TRANSACTION Custom user-defined function
SYSTEM$CANCEL_ALL_QUERIES Custom user-defined function
SYSTEM$CANCEL_QUERY Custom user-defined function
SYSTEM$CLUSTERING_DEPTH Custom user-defined function
SYSTEM$CLUSTERING_INFORMATION Custom user-defined function
SYSTEM$CLUSTERING_RATIO — Deprecated Custom user-defined function
SYSTEM$CURRENT_USER_TASK_NAME Custom user-defined function
SYSTEM$DATABASE_REFRESH_HISTORY Custom user-defined function
SYSTEM$DATABASE_REFRESH_PROGRESS , SYSTEM$DATABASE_REFRESH_PROGRESS_BY_JOB Custom user-defined function
SYSTEM$GET_AWS_SNS_IAM_POLICY Custom user-defined function
SYSTEM$GET_PREDECESSOR_RETURN_VALUE Custom user-defined function
SYSTEM$LAST_CHANGE_COMMIT_TIME Custom user-defined function
SYSTEM$PIPE_FORCE_RESUME Custom user-defined function
SYSTEM$PIPE_STATUS Custom user-defined function
SYSTEM$SET_RETURN_VALUE Custom user-defined function
SYSTEM$SHOW_OAUTH_CLIENT_SECRETS Custom user-defined function
SYSTEM$STREAM_GET_TABLE_TIMESTAMP Custom user-defined function
SYSTEM$STREAM_HAS_DATA Custom user-defined function
SYSTEM$TASK_DEPENDENTS_ENABLE Custom user-defined function
SYSTEM$TYPEOF Custom user-defined function
SYSTEM$USER_TASK_CANCEL_ONGOING_EXECUTIONS Custom user-defined function
SYSTEM$WAIT Custom user-defined function
SYSTEM$WHITELIST Custom user-defined function
SYSTEM$WHITELIST_PRIVATELINK Custom user-defined function

Table functions

Snowflake BigQuery
GENERATOR Custom user-defined function
GET_OBJECT_REFERENCES Custom user-defined function
RESULT_SCAN Custom user-defined function
VALIDATE Custom user-defined function

Utility and hash functions

Snowflake BigQuery
GET_DDL Feature Request
HASH HASH is a Snowflake-specific proprietary function. Can't be translated without knowing the underlying logic used by Snowflake.

Window functions

Snowflake BigQuery
CONDITIONAL_CHANGE_EVENT Custom user-defined function
CONDITIONAL_TRUE_EVENT Custom user-defined function
CUME_DIST CUME_DIST
DENSE_RANK DENSE_RANK
FIRST_VALUE FIRST_VALUE
LAG LAG
LAST_VALUE LAST_VALUE
LEAD LEAD
NTH_VALUE NTH_VALUE
NTILE NTILE
PERCENT_RANK PERCENT_RANK
RANK RANK
RATIO_TO_REPORT Custom user-defined function
ROW_NUMBER ROW_NUMBER
WIDTH_BUCKET Custom user-defined function

BigQuery also supports SAFE_CAST(expression AS typename), which returns NULL if BigQuery is unable to perform a cast (for example, SAFE_CAST("apple" AS INT64) returns NULL).

Operators

The following sections list Snowflake operators and their BigQuery equivalents.

Arithmetic operators

The following table shows mappings between Snowflake arithmetic operators with their BigQuery equivalents.

Snowflake BigQuery

(Unary) (+'5')

CAST("5" AS NUMERIC)

a + b

a + b

(Unary) (-'5')

(-1) * CAST("5" AS NUMERIC)


Note: BigQuery supports standard unary minus, but does not convert integers in string format to INT64, NUMERIC, or FLOAT64 type.

a - b

a - b

date1 - date2


date1 - 365

DATE_DIFF(date1, date2, date_part) DATE_SUB(date1, date2, date_part)

a * b

a * b

a / b

a / b

a % b

MOD(a, b)

To view Snowflake scale and precision details when performing arithmetic operations, see the Snowflake documentation.

Comparison operators

Snowflake comparison operators and BigQuery comparison operators are the same.

Logical/boolean operators

Snowflake logical/boolean operators and BigQuery logical/boolean operators are the same.

Set operators

The following table shows mappings between Snowflake set operators with their BigQuery equivalents.

Snowflake BigQuery

SELECT ... INTERSECT SELECT ...

SELECT ...

INTERSECT DISTINCT

SELECT...

SELECT ... MINUS SELECT ...

SELECT ... EXCEPT SELECT …


Note: MINUS and EXCEPT are synonyms.

SELECT ... EXCEPT DISTINCT SELECT ...

SELECT ... UNION SELECT ...

SELECT ... UNION ALL SELECT ...

SELECT ... UNION DISTINCT SELECT ...


SELECT ... UNION ALL SELECT ...

Subquery operators

The following table shows mappings between Snowflake subquery operators with their BigQuery equivalents.

Snowflake BigQuery

SELECT ... FROM ... WHERE col <operator> ALL … SELECT ... FROM ... WHERE col <operator> ANY ...

BigQuery does not support a direct alternative to Snowflake's ALL/ANY.

SELECT ... FROM ...

WHERE [NOT] EXISTS...

SELECT ... FROM ...

WHERE [NOT] EXISTS...

SELECT ... FROM ...

WHERE [NOT] IN...

SELECT ... FROM ...

WHERE [NOT] IN...

SELECT * FROM table1

UNION

SELECT * FROM table2

EXCEPT

SELECT * FROM table3

SELECT * FROM table1

UNION ALL

(

SELECT * FROM table2

EXCEPT

SELECT * FROM table3

)


Note: BigQuery requires parentheses to separate different set operations. If the same set operator is repeated, parentheses are not necessary.

DML syntax

This section addresses differences in data management language syntax between Snowflake and BigQuery.

INSERT statement

Snowflake offers a configurable DEFAULT keyword for columns. In BigQuery, the DEFAULT value for nullable columns is NULL and DEFAULT is not supported for required columns. Most Snowflake INSERT statements are compatible with BigQuery. The following table shows exceptions.

Snowflake BigQuery

INSERT [OVERWRITE] INTO table

VALUES [... | DEFAULT | NULL] ...


Note: BigQuery does not support inserting JSON objects with an INSERT statement.

INSERT [INTO] table (column1 [, ...])

VALUES (DEFAULT [, ...])

Note: BigQuery does not support a direct alternative to Snowflake's OVERWRITE. Use DELETE instead.

INSERT INTO table (column1 [, ...]) SELECT... FROM ...

INSERT [INTO] table (column1, [,...])

SELECT ...

FROM ...

INSERT [OVERWRITE] ALL <intoClause> ... INSERT [OVERWRITE] {FIRST | ALL} {WHEN condition THEN <intoClause>}

[...]

[ELSE <intoClause>]

...

Note: <intoClause> represents standard INSERT statement, listed above.
BigQuery does not support conditional and unconditional multi-table INSERTs.

BigQuery also supports inserting values using a subquery (where one of the values is computed using a subquery), which is not supported in Snowflake. For example:

INSERT INTO table (column1, column2)
VALUES ('value_1', (
  SELECT column2
  FROM table2
))

COPY statement

Snowflake supports copying data from stages files to an existing table and from a table to a named internal stage, a named external stage, and an external location (Amazon S3, Google Cloud Storage, or Microsoft Azure).

BigQuery does not use the SQL COPY command to load data, but you can use any of several non-SQL tools and options to load data into BigQuery tables. You can also use data pipeline sinks provided in Apache Spark or Apache Beam to write data into BigQuery.

UPDATE statement

Most Snowflake UPDATE statements are compatible with BigQuery. The following table shows exceptions.

Snowflake BigQuery

UPDATE table SET col = value [,...] [FROM ...] [WHERE ...]

UPDATE table

SET column = expression [,...]

[FROM ...]

WHERE TRUE


Note: All UPDATE statements in BigQuery require a WHERE keyword, followed by a condition.

DELETE and TRUNCATE TABLE statements

The DELETE and TRUNCATE TABLE statements are both ways to remove rows from a table without affecting the table schema or indexes.

In Snowflake, both DELETE and TRUNCATE TABLE maintain deleted data using Snowflake's Time Travel for recovery purposes for the data retention period. However, DELETE does not delete the external file load history and load metadata.

In BigQuery, the DELETE statement must have a WHERE clause. For more information about DELETE in BigQuery, see the BigQueryDELETEexamples in the DML documentation.

Snowflake BigQuery

DELETE FROM table_name [USING ...]

[WHERE ...]



TRUNCATE [TABLE] [IF EXISTS] table_name

DELETE [FROM] table_name [alias]

WHERE ...


Note: BigQuery DELETE statements require a WHERE clause.

MERGE statement

The MERGE statement can combine INSERT, UPDATE, and DELETE operations into a single "upsert" statement and perform the operations automatically. The MERGE operation must match at most one source row for each target row.

BigQuery tables are limited to 1,000 DML statements per day, so you should optimally consolidate INSERT, UPDATE, and DELETE statements into a single MERGE statement as shown in the following table:

Snowflake BigQuery

MERGE INTO target USING source ON target.key = source.key WHEN MATCHED AND source.filter = 'Filter_exp' THEN

UPDATE SET target.col1 = source.col1, target.col1 = source.col2,

...


Note: Snowflake supports a ERROR_ON_NONDETERMINISTIC_MERGE session parameter to handle nondeterministic results.

MERGE target

USING source

ON target.key = source.key

WHEN MATCHED AND source.filter = 'filter_exp' THEN

UPDATE SET

target.col1 = source.col1,

target.col2 = source.col2,

...



Note: All columns must be listed if updating all columns.

GET and LIST statements

The GET statement downloads data files from one of the following Snowflake stages to a local directory/folder on a client machine:

  • Named internal stage
  • Internal stage for a specified table
  • Internal stage for the current user

The LIST (LS) statement returns a list of files that have been staged (that is, uploaded from a local file system or unloaded from a table) in one of the following Snowflake stages:

  • Named internal stage
  • Named external stage
  • Stage for a specified table
  • Stage for the current user

BigQuery does not support the concept of staging and does not have GET and LIST equivalents.

PUT and REMOVE statements

The PUT statement uploads (that is, stages) data files from a local directory/folder on a client machine to one of the following Snowflake stages:

  • Named internal stage
  • Internal stage for a specified table
  • Internal stage for the current user

The REMOVE (RM) statement removes files that have been staged in one of the following Snowflake internal stages:

  • Named internal stage
  • Stage for a specified table
  • Stage for the current user

BigQuery does not support the concept of staging and does not have PUT and REMOVE equivalents.

DDL syntax

This section addresses differences in data definition language syntax between Snowflake and BigQuery.

Database, Schema, and Share DDL

Most of Snowflake's terminology matches that of BigQuery's except that Snowflake Database is similar to BigQuery Dataset. See the detailed Snowflake to BigQuery terminology mapping.

CREATE DATABASE statement

Snowflake supports creating and managing a database via database management commands while BigQuery provides multiple options like using Console, CLI, Client Libraries, etc. for creating datasets. This section will use BigQuery CLI commands corresponding to the Snowflake commands to address the differences.

Snowflake BigQuery

CREATE DATABASE <name>


Note: Snowflake provides these requirements for naming databases. It allows only 255 characters in the name.

bq mk <name>


Note: BigQuery has similar dataset naming requirements as Snowflake except that it allows 1024 characters in the name.

CREATE OR REPLACE DATABASE <name>

Replacing the dataset is not supported in BigQuery.

CREATE TRANSIENT DATABASE <name>

Creating temporary dataset is not supported in BigQuery.

CREATE DATABASE IF NOT EXISTS <name>

Concept not supported in BigQuery

CREATE DATABASE <name>

CLONE <source_db>

[ { AT | BEFORE }

( { TIMESTAMP => <timestamp> |

OFFSET => <time_difference> |

STATEMENT => <id> } ) ]

Cloning datasets is not yet supported in BigQuery.

CREATE DATABASE <name>

DATA_RETENTION_TIME_IN_DAYS = <num>

Time travel at the dataset level is not supported in BigQuery. However, time travel for table and query results is supported.

CREATE DATABASE <name>

DEFAULT_DDL_COLLATION = '<collation_specification>'

Collation in DDL is not supported in BigQuery.

CREATE DATABASE <name>

COMMENT = '<string_literal>'

bq mk \

--description "<string_literal>" \

<name>

CREATE DATABASE <name>

FROM SHARE <provider_account>.<share_name>

Creating shared datasets is not supported in BigQuery. However, users can share the dataset via Console/UI once the dataset is created.

CREATE DATABASE <name>

AS REPLICA OF

<region>.<account>.<primary_db_name>

AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = { TRUE | FALSE }


Note: Snowflake provides the option for automatic background maintenance of materialized views in the secondary database which is not supported in BigQuery.

bq mk --transfer_config \

--target_dataset = <name> \

--data_source = cross_region_copy \ --params='

{"source_dataset_id":"<primary_db_name>"

,"source_project_id":"<project_id>"

,"overwrite_destination_table":"true"}'

Note: BigQuery supports copying datasets using the BigQuery Data Transfer Service. See here for a dataset copying prerequisites.

BigQuery also offers the following bq mk command options, which do not have a direct analogue in Snowflake:

  • --location <dataset_location>
  • --default_table_expiration <time_in_seconds>
  • --default_partition_expiration <time_in_seconds>

ALTER DATABASE statement

This section will use BigQuery CLI commands corresponding to the Snowflake commands to address the differences in ALTER statements.

Snowflake BigQuery

ALTER DATABASE [ IF EXISTS ] <name> RENAME TO <new_db_name>

Renaming datasets is not supported in BigQuery but copying datasets is supported.

ALTER DATABASE <name>

SWAP WITH <target_db_name>

Swapping datasets is not supported in BigQuery.

ALTER DATABASE <name>

SET

[DATA_RETENTION_TIME_IN_DAYS = <num>]

[ DEFAULT_DDL_COLLATION = '<value>']

Managing data retention and collation at dataset level is not supported in BigQuery.

ALTER DATABASE <name>

SET COMMENT = '<string_literal>'

bq update \

--description "<string_literal>" <name>

ALTER DATABASE <name>

ENABLE REPLICATION TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]

Concept not supported in BigQuery.

ALTER DATABASE <name>

DISABLE REPLICATION [ TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]]

Concept not supported in BigQuery.

ALTER DATABASE <name>

SET AUTO_REFRESH_MATERIALIZED_VIEWS_ON_SECONDARY = { TRUE | FALSE }

Concept not supported in BigQuery.

ALTER DATABASE <name> REFRESH

Concept not supported in BigQuery.

ALTER DATABASE <name>

ENABLE FAILOVER TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]

Concept not supported in BigQuery.

ALTER DATABASE <name>

DISABLE FAILOVER [ TO ACCOUNTS <snowflake_region>.<account_name>

[ , <snowflake_region>.<account_name> ... ]]

Concept not supported in BigQuery.

ALTER DATABASE <name>

PRIMARY

Concept not supported in BigQuery.

DROP DATABASE statement

This section will use BigQuery CLI command corresponding to the Snowflake command to address the difference in DROP statement.

Snowflake BigQuery

DROP DATABASE [ IF EXISTS ] <name>

[ CASCADE | RESTRICT ]


Note: In Snowflake, dropping a database does not permanently remove it from the system. A version of the dropped database is retained for the number of days specified by the DATA_RETENTION_TIME_IN_DAYS parameter for the database.

bq rm -r -f -d <name>


Where

-r is to remove all objects in the dataset

-f is to skip confirmation for execution

-d indicates dataset

Note: In BigQuery, deleting a dataset is permanent. Also, cascading is not supported at the dataset level as all the data and objects in the dataset are deleted.

Snowflake also supports UNDROP DATASET command which restores the most recent version of a dropped datasets. This is currently not supported in BigQuery at the dataset level.

USE DATABASE statement

Snowflake provides the option to set the database for a user session using USE DATABASE command. This removes the need for specifying fully-qualified object names in SQL commands. BigQuery does not provide any alternative to Snowflake's USE DATABASE command.

SHOW DATABASE statement

This section will use BigQuery CLI command corresponding to the Snowflake command to address the difference in SHOW statement.

Snowflake BigQuery

SHOW DATABASES


Note: Snowflake provides a single option to list and show details about all the databases including dropped databases that are within the retention period.
bq ls --format=prettyjson
and / or

bq show <dataset_name>


Note: In BigQuery, the ls command provides only dataset names and basic information, and the show command provides details like last modified timestamp, ACLs, and labels of a dataset. BigQuery also provides more details about the datasets via Information Schema.

SHOW TERSE DATABASES


Note: With the TERSE option, Snowflake allows to display only specific information/fields about datasets.
Concept not supported in BigQuery.

SHOW DATABASES HISTORY

Time travel concept is not supported in BigQuery at the dataset level.
SHOW DATABASES

[LIKE '<pattern>']

[STARTS WITH '<name_string>']

Filtering results by dataset names is not supported in BigQuery. However, filtering by labels is supported.
SHOW DATABASES

LIMIT <rows> [FROM '<name_string>']


Note: By default, Snowflake does not limit the number of results. However, the value for LIMIT cannot exceed 10K.

bq ls \

--max_results <rows>


Note: By default, BigQuery only displays 50 results.

BigQuery also offers the following bq command options, which do not have a direct analogue in Snowflake:

  • bq ls --format=pretty: Returns basic formatted results
  • *bq ls -a: *Returns only anonymous datasets (the ones starting with an underscore)
  • bq ls --all: Returns all datasets including anonymous ones
  • bq ls --filter labels.key:value: Returns results filtered by dataset label
  • bq ls --d: Excludes anonymous datasets form results
  • bq show --format=pretty: Returns detailed basic formatted results for all datasets

SCHEMA management

Snowflake provides multiple schema management commands similar to its database management commands. This concept of creating and managing schema is not supported in BigQuery.

However, BigQuery allows you to specify a table's schema when you load data into a table, and when you create an empty table. Alternatively, you can use schema auto-detection for supported data formats.

SHARE management

Snowflake provides multiple share management commands similar to its database and schema management commands. This concept of creating and managing share is not supported in BigQuery.

Table, View, and Sequence DDL

CREATE TABLE statement

Most Snowflake CREATE TABLE statements are compatible with BigQuery, except for the following syntax elements, which are not used in BigQuery:

Snowflake BigQuery

CREATE TABLE table_name

(

col1 data_type1 NOT NULL,

col2 data_type2 NULL,

col3 data_type3 UNIQUE,

col4 data_type4 PRIMARY KEY,

col5 data_type5

)


Note: UNIQUE and PRIMARY KEY constraints are informational and are not enforced by the Snowflake system.

CREATE TABLE table_name

(

col1 data_type1 NOT NULL,

col2 data_type2,

col3 data_type3,

col4 data_type4,

col5 data_type5,

)

CREATE TABLE table_name

(

col1 data_type1[,...]

table_constraints

)


where table_constraints are:

[UNIQUE(column_name [, ... ])]

[PRIMARY KEY(column_name [, ...])]

[FOREIGN KEY(column_name [, ...])

REFERENCES reftable [(refcolumn)]


Note: UNIQUE and PRIMARY KEY constraints are informational and are not enforced by the Snowflake system.

CREATE TABLE table_name

(

col1 data_type1[,...]

)

PARTITION BY column_name

CLUSTER BY column_name [, ...]


Note: BigQuery does not use UNIQUE, PRIMARY KEY, or FOREIGN KEY table constraints. To achieve similar optimization that these constraints provide during query execution, partition and cluster your BigQuery tables. CLUSTER BY supports up to four columns.

CREATE TABLE table_name

LIKE original_table_name

See this example to learn how to use the INFORMATION_SCHEMA tables to copy column names, data types, and NOT NULL constraints to a new table.

CREATE TABLE table_name

(

col1 data_type1

)

BACKUP NO


Note:In Snowflake, the BACKUP NO setting is specified to "save processing time when creating snapshots and restoring from snapshots and to reduce storage space."
The BACKUP NO table option is not used nor needed because BigQuery automatically keeps up to 7 days of historical versions of all your tables, without any effect on processing time nor billed storage.

CREATE TABLE table_name

(

col1 data_type1

)

table_attributes


where table_attributes are:

[DISTSTYLE {AUTO|EVEN|KEY|ALL}]

[DISTKEY (column_name)]

[[COMPOUND|INTERLEAVED] SORTKEY

(column_name [, ...])]

BigQuery supports clustering which allows storing keys in sorted order.

CREATE TABLE table_name

AS SELECT ...

CREATE TABLE table_name

AS SELECT ...

CREATE TABLE IF NOT EXISTS table_name

...

CREATE TABLE IF NOT EXISTS table_name

...

BigQuery also supports the DDL statement CREATE OR REPLACE TABLEstatement which overwrites a table if it already exists.

BigQuery's CREATE TABLEstatement also supports the following clauses, which do not have a Snowflake equivalent:

For more information about CREATE TABLE in BigQuery, see the BigQuery CREATE examples in the DML documentation.

ALTER TABLE statement

This section will use BigQuery CLI commands corresponding to the Snowflake commands to address the differences in ALTER statements for tables.

Snowflake BigQuery

ALTER TABLE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER TABLE [IF EXISTS] <name>

SET OPTIONS (friendly_name="<new_name>")

ALTER TABLE <name>

SWAP WITH <target_db_name>

Swapping tables is not supported in BigQuery.

ALTER TABLE <name>

SET

[DEFAULT_DDL_COLLATION = '<value>']

Managing data collation for tables is not supported in BigQuery.

ALTER TABLE <name>

SET

[DATA_RETENTION_TIME_IN_DAYS = <num>]

ALTER TABLE [IF EXISTS] <name>

SET OPTIONS (expiration_timestamp=<timestamp>)

ALTER TABLE <name>

SET

COMMENT = '<string_literal>'

ALTER TABLE [IF EXISTS] <name>

SET OPTIONS (description='<string_literal>')

Additionally, Snowflake provides clustering, column, and constraint options for altering tables that are not supported by BigQuery.

DROP TABLE and UNDROP TABLE statements

This section will use BigQuery CLI command corresponding to the Snowflake command to address the difference in DROP and UNDROP statements.

Snowflake BigQuery

DROP TABLE [IF EXISTS] <table_name>

[CASCADE | RESTRICT]


Note: In Snowflake, dropping a table does not permanently remove it from the system. A version of the dropped table is retained for the number of days specified by the DATA_RETENTION_TIME_IN_DAYS parameter for the database.

bq rm -r -f -d <dataset_name>.<table_name>


Where

-r is to remove all objects in the dataset
-f is to skip confirmation for execution
-d indicates dataset

Note: In BigQuery, deleting a table is also not permanent but a snapshot is currently maintained only for 7 days.

UNDROP TABLE <table_name>

bq cp \ <dataset_name>.<table_name>@<unix_timestamp> <dataset_name>.<new_table_name>


Note: In BigQuery, you need to first, determine a UNIX timestamp of when the table existed (in milliseconds). Then, copy the table at that timestamp to a new table. The new table must have a different name than the deleted table.

CREATE EXTERNAL TABLE statement

BigQuery allows creating both permanent and temporary external tables and querying data directly from:

Snowflake allows creating a permanent external table which when queried, reads data from a set of one or more files in a specified external stage.

This section will use BigQuery CLI command corresponding to the Snowflake command to address the differences in CREATE EXTERNAL TABLE statement.

Snowflake BigQuery
CREATE [OR REPLACE] EXTERNAL TABLE

table

((<col_name> <col_type> AS <expr> )

| (<part_col_name> <col_type> AS <part_expr>)[ inlineConstraint ]

[ , ... ] )

LOCATION = externalStage

FILE_FORMAT =

({FORMAT_NAME='<file_format_name>'

|TYPE=source_format [formatTypeOptions]})


Where:

externalStage = @[namespace.]ext_stage_name[/path]


Note: Snowflake allows staging the files containing data to be read and specifying format type options for external tables. Snowflake format types - CSV, JSON, AVRO, PARQUET, ORC are all supported by BigQuery except the XML type.

[1] bq mk \

--external_table_definition=definition_file \

dataset.table


OR


[2] bq mk \

--external_table_definition=schema_file@source_format={Cloud Storage URI | drive_URI} \

dataset.table


OR


[3] bq mk \

--external_table_definition=schema@source_format = {Cloud Storage URI | drive_URI} \

dataset.table


Note: BigQuery allows creating a permanent table linked to your data source using a table definition file [1], a JSON schema file [2] or an inline schema definition [3]. Staging files to be read and specifying format type options is not supported in BigQuery.

CREATE [OR REPLACE] EXTERNAL TABLE [IF EXISTS]

<table_name>

((<col_name> <col_type> AS <expr> )

[ , ... ] )

[PARTITION BY (<identifier>, ...)]

LOCATION = externalStage

[REFRESH_ON_CREATE = {TRUE|FALSE}]

[AUTO_REFRESH = {TRUE|FALSE}]

[PATTERN = '<regex_pattern>']

FILE_FORMAT = ({FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET} [ formatTypeOptions]})

[COPY GRANTS]

[COMMENT = '<string_literal>']

bq mk \

--external_table_definition=definition_file \

dataset.table


Note: BigQuery currently does not support any of the optional parameter options provided by Snowflake for creating external tables. For partitioning, BigQuery supports using the _FILE_NAME pseudocolumn to create partitioned tables/views on top of the external tables. For more information, see Query the _FILE_NAME pseudocolumn.

Additionally, BigQuery also supports querying externally partitioned data in AVRO, PARQUET, ORC, JSON and CSV formats that is stored on Google Cloud Storage using a default hive partitioning layout.

CREATE VIEW statement

The following table shows equivalents between Snowflake and BigQuery for the CREATE VIEW statement.

Snowflake BigQuery

CREATE VIEW view_name AS SELECT ...

CREATE VIEW view_name AS SELECT ...

CREATE OR REPLACE VIEW view_name AS SELECT ...

CREATE OR REPLACE VIEW

view_name AS SELECT ...

CREATE VIEW view_name

(column_name, ...)

AS SELECT ...

CREATE VIEW view_name

AS SELECT ...

Not supported CREATE VIEW IF NOT EXISTS

view_name

OPTIONS(view_option_list)

AS SELECT ...

CREATE VIEW view_name

AS SELECT ...

WITH NO SCHEMA BINDING

In BigQuery, to create a view all referenced objects must already exist.

BigQuery allows to query external data sources.

CREATE SEQUENCE statement

Sequences are not used in BigQuery, this can be achieved with the following batch way. For more information on surrogate keys and slowly changing dimensions (SCD), see the following guides:

INSERT INTO dataset.table SELECT *, ROW_NUMBER() OVER () AS id FROM dataset.table

Data loading and unloading DDL

Snowflake supports data loading and unloading via stage, file format and pipe management commands. BigQuery also provides multiple options for such as bq load, BigQuery Data Transfer Service, bq extract, etc. This section highlights the differences in the usage of these methodologies for data loading and unloading.

Account and Session DDL

Snowflake's Account and Session concepts are not supported in BigQuery. BigQuery allows management of accounts via Cloud IAM at all levels. Also, multi statement transactions are not yet supported in BigQuery.

User-defined functions (UDF)

A UDF enables you to create functions for custom operations. These functions accept columns of input, perform actions, and return the result of those actions as a value

Both Snowflake and BigQuery support UDF using SQL expressions and Javascript Code.

See the GoogleCloudPlatform/bigquery-utils/ GitHub repository for a library of common BigQuery UDFs.

CREATE FUNCTION syntax

The following table addresses differences in SQL UDF creation syntax between Snowflake and BigQuery.

Snowflake BigQuery

CREATE [ OR REPLACE ] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition

s

CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

AS sql_function_definition


Note: In BigQuery SQL UDF, return data type is optional. BigQuery infers the result type of the function from the SQL function body when a query calls the function.

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS TABLE (col_name, col_data_type[,..])

AS sql_function_definition


CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note:In BigQuery SQL UDF, returning table type is currently not supported but is on the product roadmap and will be available soon. However, BigQuery supports returning ARRAY of type STRUCT.

CREATE [SECURE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note: Snowflake provides secure option to restrict UDF definition and details only to authorized users (that is, users who are granted the role that owns the view).

CREATE FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note: Function security is not a configurable parameter in BigQuery. BigQuery supports creating IAM roles and permissions to restrict access to underlying data and function definition.

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[ { CALLED ON NULL INPUT | { RETURNS NULL ON NULL INPUT | STRICT } } ]

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note: Function behaviour for null inputs is implicitly handled in BigQuery and need not be specified as a separate option.

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[VOLATILE | IMMUTABLE]

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note:Function volatility is not a configurable parameter in BigQuery. All BigQuery UDF volatility is equivalent to Snowflake's IMMUTABLE volatility (that is, it does not do database lookups or otherwise use information not directly present in its argument list).

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS [' | $$]

sql_function_definition

[' | $$]

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note: Using single quotes or a character sequence like dollar quoting ($$) is not required or supported in BigQuery. BigQuery implicitly interprets the SQL expression.

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

[COMMENT = '<string_literal>']

AS sql_function_definition

CREATE [OR REPLACE] FUNCTION

function_name

([sql_arg_name sql_arg_data_type[,..]])

RETURNS data_type

AS sql_function_definition


Note:Adding comments or descriptions in UDFs is currently not supported in BigQuery.

CREATE [OR REPLACE] FUNCTION function_name

(x integer, y integer)

RETURNS integer

AS $$

SELECT x + y

$$


Note: Snowflake does not support ANY TYPE for SQL UDFs. However, it supports using VARIANT data types.

CREATE [OR REPLACE] FUNCTION function_name

(x ANY TYPE, y ANY TYPE)

AS

SELECT x + y



Note: BigQuery supports using ANY TYPE as argument type. The function will accept an input of any type for this argument. For more information, see templated parameter in BigQuery.

BigQuery also supports the CREATE FUNCTION IF NOT EXISTSstatement which treats the query as successful and takes no action if a function with the same name already exists.

BigQuery's CREATE FUNCTIONstatement also supports creating TEMPORARY or TEMP functions, which do not have a Snowflake equivalent. See calling UDFs for details on executing a BigQuery persistent UDF.

DROP FUNCTION syntax

The following table addresses differences in DROP FUNCTION syntax between Snowflake and BigQuery.

Snowflake BigQuery

DROP FUNCTION [IF EXISTS]

function_name

([arg_data_type, ... ])

DROP FUNCTION [IF EXISTS] dataset_name.function_name


Note: BigQuery does not require using the function's signature (argument data type) for deleting the function.

BigQuery requires that you specify the project_name if the function is not located in the current project.

Additional function commands

This section covers additional UDF commands supported by Snowflake that are not directly available in BigQuery.

ALTER FUNCTION syntax

Snowflake supports the following operations using ALTER FUNCTION syntax.

  • Renaming a UDF
  • Converting to (or reverting from) a secure UDF
  • Adding, overwriting, removing a comment for a UDF

As configuring function security and adding function comments is not available in BigQuery, ALTER FUNCTION syntax is currently not supported. However, the CREATE FUNCTION statement can be used to create a UDF with the same function definition but a different name.

DESCRIBE FUNCTION syntax

Snowflake supports describing a UDF using DESC[RIBE] FUNCTION syntax. This is currently not supported in BigQuery. However, querying UDF metadata via INFORMATION SCHEMA will be available soon as part of the product roadmap.

SHOW USER FUNCTIONS syntax

In Snowflake, SHOW USER FUNCTIONS syntax can be used to list all UDFs for which users have access privileges. This is currently not supported in BigQuery. However, querying UDF metadata via INFORMATION SCHEMA will be available soon as part of the product roadmap.

Stored procedures

Snowflake stored procedures are written in JavaScript, which can execute SQL statements by calling a JavaScript API. In BigQuery, stored procedures are defined using a block of SQL statements.

CREATE PROCEDURE syntax

In Snowflake, a stored procedure is executed with a CALL command while in BigQuery, stored procedures are executed like any other BigQuery function.

The following table addresses differences in stored procedure creation syntax between Snowflake and BigQuery.

Snowflake BigQuery

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

AS procedure_definition;


Note: Snowflake requires that stored procedures return a single value. Hence, return data type is a required option.
CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_mode arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


arg_mode: IN | OUT | INOUT


Note: BigQuery doesn't support a return type for stored procedures. Also, it requires specifying argument mode for each argument passed.

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

AS

$$

javascript_code

$$;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

statement_list

END;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[{CALLED ON NULL INPUT | {RETURNS NULL ON NULL INPUT | STRICT}}]

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


Note: Procedure behavior for null inputs is implicitly handled in BigQuery and need not be specified as a separate option.
CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[VOLATILE | IMMUTABLE]

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


Note:Procedure volatility is not a configurable parameter in BigQuery. It's equivalent to Snowflake's IMMUTABLE volatility.
CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[COMMENT = '<string_literal>']

AS procedure_definition;

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


Note:Adding comments or descriptions in procedure definitions is currently not supported in BigQuery.
CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

RETURNS data_type

[EXECUTE AS { CALLER | OWNER }]

AS procedure_definition;


Note: Snowflake supports specifying the caller or owner of the procedure for execution

CREATE [OR REPLACE] PROCEDURE

procedure_name

([arg_name arg_data_type[,..]])

BEGIN

procedure_definition

END;


Note: BigQuery stored procedures are always executed as the caller

BigQuery also supports the CREATE PROCEDURE IF NOT EXISTS statement which treats the query as successful and takes no action if a function with the same name already exists.

DROP PROCEDURE syntax

The following table addresses differences in DROP FUNCTION syntax between Snowflake and BigQuery.

Snowflake BigQuery

DROP PROCEDURE [IF EXISTS]

procedure_name

([arg_data_type, ... ])

DROP PROCEDURE [IF EXISTS] dataset_name.procedure_name


Note: BigQuery does not require using procedure's signature (argument data type) for deleting the procedure.

BigQuery requires that you specify the project_name if the procedure is not located in the current project.

Additional procedure commands

Snowflake provides additional commands like ALTER PROCEDURE, DESC[RIBE] PROCEDURE, and SHOW PROCEDURES to manage the stored procedures. These are currently not supported in BigQuery.

Metadata and transaction SQL statements

Snowflake BigQuery

BEGIN [ { WORK | TRANSACTION } ] [ NAME <name> ]; START_TRANSACTION [ name <name> ];

BigQuery always uses Snapshot Isolation. For details, see Consistency guarantees elsewhere in this document.

COMMIT;

Not used in BigQuery.

ROLLBACK;

Not used in BigQuery

SHOW LOCKS [ IN ACCOUNT ]; SHOW TRANSACTIONS [ IN ACCOUNT ]; Note: If the user has the ACCOUNTADMIN role, the user can see locks/transactions for all users in the account.

Not used in BigQuery.

Multi-statement and multi-line SQL statements

Both Snowflake and BigQuery support transactions (sessions) and therefore support statements separated by semicolons that are consistently executed together. For more information, see Multi-statement transactions.

Metadata columns for staged files

Snowflake automatically generates metadata for files in internal and external stages. This metadata can be queried and loaded into a table alongside regular data columns. The following metadata columns can be utilized:

Consistency guarantees and transaction isolation

Both Snowflake and BigQuery are atomic—that is, ACID-compliant on a per-mutation level across many rows.

Transactions

Each Snowflake transaction is assigned a unique start time (includes milliseconds) that is set as the transaction ID. Snowflake only supports the READ COMMITTED isolation level. However, a statement can see changes made by another statement if they are both in the same transaction - even though those changes are not committed yet. Snowflake transactions acquire locks on resources (tables) when that resource is being modified. Users can adjust the maximum time a blocked statement will wait until the statement times out. DML statements are autocommitted if the AUTOCOMMIT parameter is turned on.

BigQuery also supports transactions. BigQuery helps ensure optimistic concurrency control (first to commit wins) with snapshot isolation, in which a query reads the last committed data before the query starts. This approach guarantees the same level of consistency on a per-row, per-mutation basis and across rows within the same DML statement, yet avoids deadlocks. In the case of multiple DML updates against the same table, BigQuery switches to pessimistic concurrency control. Load jobs can run completely independently and append to tables. However, BigQuery does not yet provide an explicit transaction boundary or session.

Rollback

If a Snowflake transaction's session is unexpectedly terminated before the transaction is committed or rolled back, the transaction is left in a detached state. The user should run SYSTEM$ABORT_TRANSACTION to abort the detached transaction or Snowflake will roll back the detached transaction after four idle hours. If a deadlock occurs, Snowflake detects the deadlock and selects the more recent statement to roll back. If the DML statement in an explicitly opened transaction fails, the changes are rolled back, but the transaction is kept open until it is committed or rolled back. DDL statements in Snowflake cannot be rolled back as they are autocommitted.

BigQuery supports the ROLLBACK TRANSACTION statement. There is no ABORT statement in BigQuery.

Database limits

Always check the BigQuery public documentation for the latest quotas and limits. Many quotas for large-volume users can be raised by contacting the Cloud Support team.

All Snowflake accounts have soft-limits set by default. Soft-limits are set during account creation and can vary. Many Snowflake soft-limits can be raised through the Snowflake account team or a support ticket.

The following table shows a comparison of the Snowflake and BigQuery database limits.

Limit Snowflake BigQuery
Size of query text 1 MB 1 MB
Maximum number of concurrent queries XS Warehouse - 8
S Warehouse - 16
M Warehouse - 32
L Warehouse - 64
XL Warehouse - 128
100