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/ |
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/ 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 | |
---|---|---|
|
|
|
Note: Snowflake supports creating and referencing an alias in the same SELECT statement. |
|
|
|
|
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 | |
---|---|---|
|
WITH table1 AS |
|
|
|
|
|
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. |
|
|
BigQuery does not support the concept of staged files. |
|
|
BigQuery does not offer a direct alternative to Snowflake's |
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 Note: BigQuery does not support the (+) syntax for JOIN s |
JOIN
types
Both Snowflake and BigQuery support the following types of join:
[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN
CROSS JOIN
and the equivalent implicit "comma cross join"
Both Snowflake and BigQuery support theON
andUSING
clause.
The following table contains a list of minor differences.
Snowflake | BigQuery | |
---|---|---|
|
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. |
|
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. |
LATERAL JOIN s. |
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 | |
---|---|---|
|
|
|
|
|
|
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 . |
|
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, NULL s 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 | |
---|---|---|
Note: NULL , empty string (''), and $$$$ values are accepted and are treated as "unlimited". Primary use is for connectors and drivers. |
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 | |
---|---|---|
|
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
Using ARRAY_AGG() , which supports larger partitions:
|
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 |
---|---|
Note: DISTINCT does not have any effect |
|
Note: DISTINCT does not have any effect |
Note: BigQuery does not support APPROX_COUNT_DISTINCT with Window Functions |
Note: Snowflake does not have the option to RESPECT NULLS |
Note: BigQuery does not support APPROX_QUANTILES with Window Functions |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
Note: If no number parameter is specified, default is 1. Counters should be significantly larger than number. |
Note: BigQuery does not support APPROX_TOP_COUNT with Window Functions. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
BigQuery does not support the ability to store intermediate state when predicting approximate values. |
|
You can use a custom UDF to implement MINHASH with k distinct hash functions. Another approach to reduce the variance in MINHASH is to keepk of the minimum values of one hash function. In this case Jaccard index can be approximated as following:
|
|
It is a synonym for APPROXIMATE_JACCARD_INDEX and can be implemented in the same way. |
|
|
|
Note: BigQuery's AVG does not perform automatic casting on STRING s. |
|
INTEGER . |
|
Note: BigQuery does not implicitly cast character/text columns to the nearest INTEGER . |
|
Note: BigQuery does not implicitly cast character/text columns to the nearest INTEGER . |
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
For numeric expression:
To use OVER you can run the following (boolean example provided):
|
|
|
|
|
|
|
|
|
|
BigQuery does not support a direct alternative to Snowflake's GROUPING . Available through a User-Defined Function. |
|
BigQuery does not support a direct alternative to Snowflake's GROUPING_ID . Available through a User-Defined Function. |
|
SELECT BIT_XOR( FARM_FINGERPRINT( TO_JSON_STRING(t))) [OVER] FROM t |
Note: Snowflake does not allow you to specify precision. |
Note: BigQuery does not support HLL_COUNT… with Window Functions. A user cannot include multiple expressions in a single HLL_COUNT... function. |
Note: Snowflake does not allow you to specify precision. |
HLL_COUNT.INIT (expression [, precision]) |
|
HLL_COUNT.MERGE_PARTIAL (sketch) |
|
|
|
BigQuery does not support a direct alternative to Snowflake's HLL_EXPORT . |
|
BigQuery does not support a direct alternative to Snowflake's HLL_IMPORT . |
|
BigQuery does not support a direct alternative to Snowflake's KURTOSIS . |
|
|
Note: Snowflake does not support ability to IGNORE|RESPECT NULLS and to LIMIT directly in ARRAY_AGG. |
|
|
|
|
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 ( |
|
|
|
You may consider using TO_JSON_STRING to convert a value into JSON-formatted string |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
BigQuery does not support a direct alternative to Snowflake's SKEW . |
|
|
|
|
|
|
|
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
Note: Snowflake supports the ability to cast VARCHAR s to floating point values. |
|
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 |
---|---|
|
|
|
|
|
|
|
|
BITSHIFTRIGHT
|
|
Note: Snowflake does not support DISTINCT. |
|
Conditional expression functions
The following table shows mappings between common Snowflake conditional expressions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
|
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
BOOLOR Note: Snowflake allows numeric, decimal, and floating point values to be treated as TRUE if not zero. |
|
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. |
|
|
Note: Snowflake requires at least two expressions. BigQuery only requires one. |
|
|
DECODE . User must use IS NULL instead of = NULL to match NULL select expressions with NULL search expressions. |
|
BigQuery does not support a direct alternative to Snowflake's EQUAL_NULL. |
|
|
|
|
|
|
|
|
|
BigQuery does not support a direct alternative to Snowflake's IS [ NOT ] DISTINCT FROM. |
|
|
|
BigQuery does not support VARIANT data types. |
|
|
|
|
|
|
|
|
|
REGR... functions. |
|
Note: BigQuery does not support a direct alternative to Snowflake's REGR... functions. |
|
|
Context functions
The following table shows mappings between common Snowflake context functions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
Note: Not direct comparison. Snowflake returns account ID, BigQuery returns user email address. |
|
Concept not used in BigQuery |
|
This returns a table of project names. Not a direct comparison. |
|
Note: Snowflake does not enforce '()' after CURRENT_DATE command to comply with ANSI standards. |
Note: BigQuery's CURRENT_DATE supports optional time zone specification. |
Note: BigQuery's INFORMATION_SCHEMA.SCHEMATA returns more generalized location references than Snowflake's CURRENT_REGION() . Not a direct comparison. |
|
Concept not used in BigQuery |
|
This returns a table of all datasets (also called schemas) available in the project or region. Not a direct comparison. |
|
Concept not used in BigQuery |
|
Concept not used in BigQuery |
|
Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for queries by job type, start/end type, etc. |
|
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 '()'. |
|
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. |
Note: CURRENT_DATETIME returns DATETIME data type (not supported in Snowflake). CURRENT_TIMESTAMP returns TIMESTAMP data type. |
INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc. |
|
Note: Snowflake does not enforce '()' after CURRENT_USER command to comply with ANSI standards. |
|
Concept not used in BigQuery |
|
|
|
|
Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc. |
Note: BigQuery's INFORMATION_SCHEMA.JOBS_BY_* allows for searching for job IDs by job type, start/end type, etc. |
|
Note: Snowflake does not enforce '()' after LOCALTIME command to comply with ANSI standards. |
|
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 |
---|---|
|
|
|
|
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. |
Note: BigQuery's default STRING casting uses UTF-8 encoding. Snowflake does not have an option to support BASE32 encoding. |
Note:
|
Note:
|
Note: Snowflake's format models can be found here. BigQuery does not have an alternative to the VARIANT data type. |
Note: BigQuery's input expression can be formatted using FORMAT_DATE , FORMAT_DATETIME , FORMAT_TIME , or FORMAT_TIMESTAMP . |
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. |
Note: BigQuery's input expression can be formatted using FORMAT , FORMAT_DATETIME , or FORMAT_TIMESTAMP . |
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. |
Note: BigQuery's input expression can be formatted using 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. |
Note: BigQuery's input expression can be formatted using FORMAT. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
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. |
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 . |
Note: BigQuery does not have an alternative to the VARIANT data type. |
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. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
|
BigQuery does not have an alternative to Snowflake's VARIANT data type. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
BigQuery also offers the following conversion functions, which do not have a direct analogue in Snowflake:
CODE_POINTS_TO_BYTES
CODE_POINTS_TO_STRING
FORMAT
FROM_BASE32
FROM_BASE64
FROM_HEX
SAFE_CONVERT_BYTES_TO_STRING
TO_BASE32
TO_CODE_POINTS
Data generation functions
The following table shows mappings between common Snowflake data generation functions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
|
BigQuery does not support a direct comparison to Snowflake's NORMAL. |
|
Note: BigQuery does not support seeding |
|
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_. |
|
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. |
Note: BigQuery returns 122 random bits. BigQuery only supports version 4 UUIDs. |
|
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 |
---|---|
|
|
|
Note: source_timezone is always UTC in BigQuery |
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. |
|
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 . |
Note: BigQuery supports the week(<weekday>), microsecond, and millisecond part types. Snowflake does not. See full list of BigQuery part types here and here. |
Note: Snowflake supports the nanosecond part type. BigQuery does not. See full list of Snowflake part types here . |
Note: BigQuery supports the week(<weekday>), ISO week, and ISO year part types. Snowflake does not. |
|
|
Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function. |
Note: BigQuery supports the week(<weekday>) and ISO year part types. |
|
|
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 . |
Note: BigQuery supports the week(<weekday>), microsecond, and millisecond part types. Snowflake does not. See full list of BigQuery part types here and here. |
|
|
|
|
|
|
|
Note: dowString might need to be reformatted. For example, Snowflake's 'su' will be BigQuery's 'SUNDAY'. |
|
Note: dowString might need to be reformatted. For example, Snowflake's 'su' will be BigQuery's 'SUNDAY'. |
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. |
|
|
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. |
|
|
Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function. |
Note: BigQuery supports the week(<weekday>) and ISO year part types. |
|
Note: BigQuery requires timestamps be inputted as STRING types. Example: "2008-12-25 15:30:00" |
|
|
Note: Snowflake supports calculating the difference between two date, time, and timestamp types in this function. |
Note: BigQuery supports the week(<weekday>) and ISO year part types. |
Note: Snowflake supports the nanosecond part type. BigQuery does not. See full list of Snowflake part types here . |
Note: BigQuery supports the week(<weekday>), ISO week, and ISO year part types. Snowflake does not. |
|
|
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:
AUTOMATIC_CLUSTERING_HISTORY
COPY_HISTORY
DATA_TRANSFER_HISTORY
DATABASE_REFRESH_HISTORY
DATABASE_REFRESH_PROGRESS, DATABASE_REFRESH_PROGRESS_BY_JOB
DATABASE_STORAGE_USAGE_HISTORY
EXTERNAL_TABLE_FILES
EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY
LOGIN_HISTORY
,LOGIN_HISTORY_BY_USER
MATERIALIZED_VIEW_REFRESH_HISTORY
PIPE_USAGE_HISTORY
REPLICATION_USAGE_HISTORY
STAGE_STORAGE_USAGE_HISTORY
TASK_DEPENDENTS
VALIDATE_PIPE_LOAD
WAREHOUSE_LOAD_HISTORY
WAREHOUSE_METERING_HISTORY
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:
INFORMATION_SCHEMA.SCHEMATA
INFORMATION_SCHEMA.ROUTINES
INFORMATION_SCHEMA.TABLES
INFORMATION_SCHEMA.VIEWS
Numeric functions
The following table shows mappings between common Snowflake numeric functions with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Note: BigQuery's CEIL does not support the ability to indicate precision or scale. ROUND does not allow you to specify to round up. |
|
|
|
|
|
|
|
|
|
|
|
BigQuery does not have a direct alternative to Snowflake's FACTORIAL . Use a user-defined function. |
|
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. |
|
Note: Not an exact match, but close enough. |
|
|
|
Note:Default base for LOG is 10. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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:
IS_INF
IS_NAN
IEEE_DIVIDE
DIV
SAFE_DIVIDE
SAFE_MULTIPLY
SAFE_NEGATE
SAFE_ADD
SAFE_SUBTRACT
RANGE_BUCKET
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 |
|
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 |
---|---|
|
|
ASCII |
|
BASE64_DECODE_BINARY |
|
BASE64_DECODE_STRING |
|
BASE64_ENCODE |
|
BIT_LENGTH |
CHARACTER_LENGTH |
|
|
CHR,CHAR |
|
COLLATE |
Custom user-defined function |
COLLATION |
Custom user-defined function |
COMPRESS |
Custom user-defined function |
|
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 |
|
HEX_DECODE_STRING |
|
HEX_ENCODE |
|
ILIKE |
Custom user-defined function |
ILIKE ANY |
Custom user-defined function |
INITCAP |
INITCAP |
INSERT |
Custom user-defined function |
LEFT |
User Defined Function |
LENGTH |
|
LIKE |
LIKE |
LIKE ALL |
Custom user-defined function |
LIKE ANY |
Custom user-defined function |
LOWER |
|
LPAD |
|
LTRIM |
|
|
|
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 |
|
REPEAT |
|
REPLACE |
|
REVERSE
|
|
RIGHT |
User Defined Function |
RPAD |
RPAD |
RTRIM |
|
RTRIMMED_LENGTH |
Custom user-defined function |
SHA1,SHA1_HEX |
|
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 |
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 |
|
TRY_HEX_DECODE_BINARY |
|
TRY_HEX_DECODE_STRING |
|
UNICODE |
Custom user-defined function |
|
UPPER |
String functions (regular expressions)
Snowflake | BigQuery |
---|---|
REGEXP |
|
REGEXP_COUNT |
If position is specified:
Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax. |
REGEXP_INSTR |
If position is specified:
If occurrence is specified:
Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax. |
|
|
REGEXP_REPLACE |
If replace_string is specified:
If position is specified:
Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax. |
REGEXP_SUBSTR |
If position is specified:
If occurrence is specified:
Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax. |
RLIKE |
|
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 |
---|---|
|
|
|
|
|
Note: BigQuery supports standard unary minus, but does not convert integers in string format to INT64 , NUMERIC , or FLOAT64 type. |
|
|
|
|
|
|
|
|
|
|
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 |
---|---|
|
INTERSECT DISTINCT
|
Note: MINUS and EXCEPT are synonyms. |
|
|
|
Subquery operators
The following table shows mappings between Snowflake subquery operators with their BigQuery equivalents.
Snowflake | BigQuery |
---|---|
|
BigQuery does not support a direct alternative to Snowflake's ALL/ANY. |
|
|
|
|
|
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 |
---|---|
Note: BigQuery does not support inserting JSON objects with an INSERT statement. |
VALUES (DEFAULT [, ...]) Note: BigQuery does not support a direct alternative to Snowflake's OVERWRITE . Use DELETE instead. |
|
|
... 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 | |
---|---|---|
|
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
BigQueryDELETE
examples
in the DML documentation.
Snowflake | BigQuery |
---|---|
|
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 |
---|---|
Note: Snowflake supports a ERROR_ON_NONDETERMINISTIC_MERGE session parameter to handle nondeterministic results. |
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 |
---|---|
Note: Snowflake provides these requirements for naming databases. It allows only 255 characters in the name. |
Note: BigQuery has similar dataset naming requirements as Snowflake except that it allows 1024 characters in the name. |
|
Replacing the dataset is not supported in BigQuery. |
|
Creating temporary dataset is not supported in BigQuery. |
|
Concept not supported in BigQuery |
|
Cloning datasets is not yet supported in BigQuery. |
|
Time travel at the dataset level is not supported in BigQuery. However, time travel for table and query results is supported. |
|
Collation in DDL is not supported in BigQuery. |
|
|
|
Creating shared datasets is not supported in BigQuery. However, users can share the dataset via Console/UI once the dataset is created. |
Note: Snowflake provides the option for automatic background maintenance of materialized views in the secondary database which is not supported in BigQuery. |
|
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 |
---|---|
|
Renaming datasets is not supported in BigQuery but copying datasets is supported. |
|
Swapping datasets is not supported in BigQuery. |
|
Managing data retention and collation at dataset level is not supported in BigQuery. |
|
|
|
Concept not supported in BigQuery. |
|
Concept not supported in BigQuery. |
|
Concept not supported in BigQuery. |
|
Concept not supported in BigQuery. |
|
Concept not supported in BigQuery. |
|
Concept not supported in BigQuery. |
|
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 |
---|---|
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. |
-r is to remove all objects in the dataset
-d indicates datasetNote: 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 |
---|---|
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=prettyjsonand / or
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. |
Note: With the TERSE option, Snowflake allows to display only specific information/fields about datasets. |
Concept not supported in BigQuery. |
Time travel concept is not supported in BigQuery at the dataset level. | |
SHOW DATABASES
|
Filtering results by dataset names is not supported in BigQuery. However, filtering by labels is supported. |
SHOW DATABASES
Note: By default, Snowflake does not limit the number of results. However, the value for LIMIT cannot exceed 10K. |
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 |
---|---|
Note: UNIQUE and PRIMARY KEY constraints are informational and are not enforced by the Snowflake system. |
|
where table_constraints are:
Note: UNIQUE and PRIMARY KEY constraints are informational and are not enforced by the Snowflake system. |
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. |
|
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. |
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. |
where table_attributes are:
|
BigQuery supports clustering which allows storing keys in sorted order. |
|
|
|
|
BigQuery also supports the DDL statement CREATE OR REPLACE
TABLE
statement which overwrites a table if it already exists.
BigQuery's CREATE TABLE
statement 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 |
---|---|
|
|
|
Swapping tables is not supported in BigQuery. |
|
Managing data collation for tables is not supported in BigQuery. |
|
|
|
|
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 |
---|---|
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. |
-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. |
|
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
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. |
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. |
|
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 OR REPLACE VIEW
|
|
|
Not supported | CREATE VIEW IF NOT EXISTS
|
|
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:
|
---|
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 |
---|---|
|
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. |
|
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. |
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). |
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. |
|
Note: Function behaviour for null inputs is implicitly handled in BigQuery and need not be specified as a separate option. |
|
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
Note: Using single quotes or a character sequence like dollar quoting ($$) is not required or supported in BigQuery. BigQuery implicitly interprets the SQL expression. |
|
Note:Adding comments or descriptions in UDFs is currently not supported in BigQuery. |
Note: Snowflake does not support ANY TYPE for SQL UDFs. However, it supports using VARIANT data types. |
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 EXISTS
statement
which treats the query as successful and takes no action if a function with the
same name already exists.
BigQuery's CREATE FUNCTION
statement 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 |
---|---|
|
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 |
---|---|
Note: Snowflake requires that stored procedures return a single value. Hence, return data type is a required option. |
CREATE [OR REPLACE] PROCEDURE
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
Note: Procedure behavior for null inputs is implicitly handled in BigQuery and need not be specified as a separate option. |
CREATE [OR REPLACE] PROCEDURE
|
Note:Procedure volatility is not a configurable parameter in BigQuery. It's equivalent to Snowflake's IMMUTABLE volatility. |
CREATE [OR REPLACE] PROCEDURE
|
Note:Adding comments or descriptions in procedure definitions is currently not supported in BigQuery. |
CREATE [OR REPLACE] PROCEDURE
Note: Snowflake supports specifying the caller or owner of the procedure for execution |
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 |
---|---|
|
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 |
---|---|
|
BigQuery always uses Snapshot Isolation. For details, see Consistency guarantees elsewhere in this document. |
|
Not used in BigQuery. |
|
Not used in BigQuery |
|
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 |