SnowConvert AI - BigQuery - Built-in functions¶

Translation reference for all the supported built-in functions by SnowConvert for BigQuery.

Note

For more information about built-in functions and their Snowflake equivalents, also see Common built-in functions.

Aggregate Functions¶

BigQuery	Snowflake
ANY_VALUE (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#any_value)	ANY_VALUE Note: Unlike BigQuery, Snowflake does not ignore NULLs . Additionally, Snowflake’s `OVER()` clause does not support the use of `ORDER BY` or explicit window frames.
ANY_VALUE (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#any_value)( expr1, HAVING MAX expr2) ANY_VALUE (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#any_value)( expr1, HAVING MIN expr2)	MAX_BY(expr1, expr1) MIN_BY(expr1, expr2)
AVG (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#avg)	AVG
COUNT (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#count)	COUNT
COUNTIF (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#countif)	COUNT_IF
LOGICAL_AND (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#logical_and)	BOOLAND_AGG
LOGICAL_OR (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#logical_or)	BOOLOR_AGG
MAX (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#max)	MAX
MIN (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#min)	MIN
SUM (https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions#sum)	SUM

Conditional Expressions¶

BigQuery	Snowflake
COALESCE (https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#coalesce)	COALESCE
IF (https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#if)	IFF
IFNULL (https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#ifnull)	IFNULL
NULLIF (https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions#nullif)	NULLIF

Conversion Functions¶

BigQuery	Snowflake
SAFE_CAST (https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions#safe_casting)	TRY_CAST

Date Functions¶

BigQuery	Snowflake
CURRENT_DATE (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#current_date) CURRENT_DATE (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#current_date)()	CURRENT_DATE CURRENT_DATE()
FORMAT_DATE (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#format_date)	TO_CHAR Note: For further details on this translation, please consult this page.

BigQuery

Snowflake

CURRENT_DATE (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#current_date)
CURRENT_DATE (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#current_date)()

CURRENT_DATE

CURRENT_DATE()

FORMAT_DATE (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#format_date)

TO_CHAR

Note: For further details on this translation, please consult this page.

Datetime Functions¶

BigQuery	Snowflake
CURRENT_DATETIME (https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#current_datetime) CURRENT_DATETIME (https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#current_datetime)()	CURRENT_TIMESTAMP :: TIMESTAMP_NTZ CURRENT_TIMESTAMP() :: TIMESTAMP_NTZ

BigQuery

Snowflake

CURRENT_DATETIME (https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#current_datetime)

CURRENT_DATETIME (https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#current_datetime)()

CURRENT_TIMESTAMP :: TIMESTAMP_NTZ
CURRENT_TIMESTAMP() :: TIMESTAMP_NTZ

Geography Functions¶

BigQuery	Snowflake
ST_GEOGFROMTEXT (https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromtext)	ST_GEOGFROMTEXT Note: For further details on this translation, please consult this page.
ST_GEOGPOINT (https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogpoint)	ST_POINT Note: For further details on this translation, please consult this page.

BigQuery

Snowflake

ST_GEOGFROMTEXT (https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromtext)

ST_GEOGFROMTEXT

Note: For further details on this translation, please consult this page.

ST_GEOGPOINT (https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogpoint)

ST_POINT

Note: For further details on this translation, please consult this page.

JSON Functions¶

BigQuery	Snowflake
JSON_VALUE (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_value) / JSON_EXTRACT_SCALAR (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_extract_scalar)	JSON_EXTRACT_PATH_TEXT Notes: SnowConvert automatically translates BigQuery JSON paths to their Snowflake equivalents.
LAX_INT64 (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#lax_int64)	PUBLIC.LAX_INT64_UDF Notes: SnowConvert generates a UDF to obtain an equivalent behavior.
LAX_BOOL (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#lax_bool)	PUBLIC.LAX_BOOL_UDF Notes: SnowConvert generates a UDF to obtain an equivalent behavior.

BigQuery

Snowflake

JSON_VALUE (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_value) / JSON_EXTRACT_SCALAR (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#json_extract_scalar)

JSON_EXTRACT_PATH_TEXT

Notes: SnowConvert automatically translates BigQuery JSON paths to their Snowflake equivalents.

LAX_INT64 (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#lax_int64)

PUBLIC.LAX_INT64_UDF

Notes: SnowConvert generates a UDF to obtain an equivalent behavior.

LAX_BOOL (https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#lax_bool)

PUBLIC.LAX_BOOL_UDF

Notes: SnowConvert generates a UDF to obtain an equivalent behavior.

Mathematical Functions¶

BigQuery	Snowflake
ABS (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#abs)	ABS
LEAST (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#least)	LEAST
MOD (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#mod)	MOD
ROUND (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#round)(X) ROUND (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#round)(X, Y) ROUND (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#round)(X, Y, ‘ROUND_HALF_EVEN’) ROUND (https://cloud.google.com/bigquery/docs/reference/standard-sql/mathematical_functions#round)(X, Y, ‘ROUND_HALF_AWAY_FROM_ZERO’)	ROUND(X) ROUND(X, Y) ROUND(X, Y, ‘HALF_TO_EVEN’) ROUND(X, Y, ‘HALF_AWAY_FROM_ZERO’)

Navigation Functions¶

BigQuery	Snowflake
FIRST_VALUE (https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#first_value)	FIRST_VALUE
LAG (https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#lag)	LAG
LEAD (https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#lead)	LEAD
LAST_VALUE (https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions#last_value)	LAST_VALUE

Numbering Functions¶

BigQuery	Snowflake
RANK (https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#rank)	RANK
ROW_NUMBER (https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions#row_number)	ROW_NUMBER

String Functions¶

BigQuery	Snowflake
CHARACTER_LENGTH (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#character_length) CHAR_LENGTH (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#char_length)	LENGTH
CONCAT (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#concat)	CONCAT
FROM_BASE64 (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#from_base64)	TRY_BASE64_DECODE_BINARY Notes: BigQuery defaults to BASE64 for binary data output, but Snowflake uses HEX. In Snowflake, you can use the `BASE64_ENCODE` function or set `BINARY_OUTPUT_FORMAT` to `’BASE64’` to view binary data in BASE64.
FROM_HEX (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#from_hex)	TRY_HEX_DECODE_BINARY Notes: BigQuery defaults to BASE64 for binary data output, but Snowflake uses HEX. In Snowflake, you can use the `BASE64_ENCODE` function or set `BINARY_OUTPUT_FORMAT` to `’BASE64’` to view binary data in BASE64.
LEFT (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#left)	LEFT
LENGTH (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#length)	LENGTH
LOWER (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#lower)	LOWER
LPAD (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#lpad)	LPAD
LTRIM (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#ltrim)	LTRIM
STARTS_WITH (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#starts_with)	STARTSWITH
REPLACE (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#replace)	REPLACE
RIGHT (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#right)	RIGHT
RPAD (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#rpad)	RPAD
RTRIM (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#rtrim)	RTRIM
SPLIT (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#split)	SPLIT
SUBSTR (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#substr)(string, position) SUBSTRING (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#substring)(string, position) SUBSTR (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#substr)(sttring, position, length) SUBSTRING (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#substring)(sttring, position, length)	SUBSTR(string, IFF(position < -LENGTH(string), 1, position)) SUBSTRING(string, IFF(position < -LENGTH(string), 1, position)) SUBSTR(sttring, IFF(position < -LENGTH(string), 1, position), length) SUBSTRING(sttring, IFF(position < -LENGTH(string), 1, position), length)
UPPER (https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#upper)	UPPER

Timestamp Functions¶

BigQuery	Snowflake
CURRENT_TIMESTAMP (https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#current_timestamp) CURRENT_TIMESTAMP (https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#current_timestamp)()	CURRENT_TIMESTAMP CURRENT_TIMESTAMP()

BigQuery

Snowflake

CURRENT_TIMESTAMP (https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#current_timestamp)
CURRENT_TIMESTAMP (https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#current_timestamp)()

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

FORMAT_DATE¶

Format_date function

Description¶

Formats a DATE value according to a specified format string.

For more information, please refer to FORMAT_DATE (https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#format_date) function.

Grammar Syntax¶

 FORMAT_DATE(format_string, date_expr)

Copy

Sample Source¶

BigQuery¶

CREATE TABLE TEST_DATE (col1 DATE);
SELECT FORMAT_DATE('%Y', col1);

Copy

Snowflake¶

CREATE TABLE TEST_DATE (col1 DATE);
SELECT
  TO_CHAR(col1, 'YYYY')
FROM
  TEST_DATE;

Copy

BigQuery Formats Equivalents¶

BigQuery	Snowflake
%A	PUBLIC.DAYNAME_LONG_UDF(date_expr) Note: Generate UDF in conversion for support.
%a	DY
%B	MMMM
%b	MON
%C	PUBLIC.CENTURY_UDF(date_expr) Note: Generate UDF in conversion for support.
%c	DY MON DD HH24:MI:SS YYYY
%D	MM/DD/YY
%d	DD
%e	DD
%F	YYYY-MM-DD
%G	YEAROFWEEKISO(date_expr)
%g	PUBLIC.ISO_YEAR_PART_UDF(date_expr, 2) Note: Generate UDF in conversion for support.
%H	HH24
%h	MON
%I	HH12
%J	PUBLIC.DAY_OF_YEAR_ISO_UDF(date_expr) Note: Generate UDF in conversion for support.
%j	DAYOFYEAR(date_expr)
%k	HH24
%l	HH12
%M	MI
%m	MM
%n	Not equivalent format
%P	pm
%p	AM
%Q	QUARTER(date_expr)
%R	HH24:MI
%S	SS
%s	Not equivalent format
%T	HH24:MI:SS
%t	Not equivalent format
%U	WEEK(date_expr)
%u	DAYOFWEEKISO(date_expr)
%V	WEEKISO(date_expr)
%W	WEEK(date_expr) Note: Unlike BigQuery, Snowflake results are dictated by the values set for the WEEK_OF_YEAR_POLICY and/or WEEK_START session parameters. So, results could differ from BigQuery based on those parameters.
%w	DAYOFWEEK(date_expr) Note: Unlike BigQuery, Snowflake results are dictated by the values set for the WEEK_OF_YEAR_POLICY and/or WEEK_START session parameters. So, results could differ from BigQuery based on those parameters.
%X	HH24:MI:SS
%x	MM/DD/YY
%Y	YYYY
%y	YY
%Z	Not equivalent format
%z	Not equivalent format
%Ez	Not equivalent format
%E<number>S	Not equivalent format
%E*S	Not equivalent format
%EY4	YYYY

Warning

In BigQuery, the format related to time is not applied when the type is DATE, but Snowflake applies the format with values in zero for HH:MI:SS usages.

Note

For more information, please refer to BigQuery DateTime formats (https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements#format_elements_date_time).

ST_GEOGFROMTEXT¶

Geography Function.

Description¶

Returns a GEOGRAPHY value that corresponds to the input WKT (link removed) representation.

For more information, please refer to ST_GEOGFROMTEXT (https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogfromtext) function.

SuccessPlaceholder

ST_GEOGFROMTEXT function is supported in Snowflake.

Grammar Syntax¶

 ST_GEOGFROMTEXT(wkt_string[, oriented])

Copy

Sample Source¶

The oriented parameter in the ST_GEOGFROMTEXT function is not supported in Snowflake.

BigQuery¶

 SELECT ST_GEOGFROMTEXT('POINT(-122.35 37.55)');
SELECT ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))', TRUE);

Copy

Snowflake¶

 SELECT ST_GEOGFROMTEXT('POINT(-122.35 37.55)');
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-BQ0006 - ORIENTED PARAMETER IN THE ST_GEOGFROMTEXT FUNCTION IS NOT SUPPORTED IN SNOWFLAKE. ***/!!! 
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))');

Copy

Please keep in mind that the default output format for geography data types is WKT (Well-Known Text) and in Snowflake WKB (Well-Known Binary). You can use the ST_ASWKT function or set the GEOGRAPHY_OUTPUT_FORMAT format if you want to view the data in WKT format.

Using ST_GEOGFROMTEXT function to insert geography data¶

This function is not allowed in the values clause and is not required in Snowflake.

BigQuery¶

 CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType VALUES
    (ST_GEOGFROMTEXT('POINT(-122.35 37.55)')), 
    (ST_GEOGFROMTEXT('LINESTRING(-124.20 42.00, -120.01 41.99)'));

Copy

Snowflake¶

 CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType
VALUES
    (
     --** SSC-FDM-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'POINT(-122.35 37.55)'),
    (
     --** SSC-FDM-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
     'LINESTRING(-124.20 42.00, -120.01 41.99)');

Copy

ST_GEOGPOINT¶

Geography Function.

Description¶

Creates a GEOGRAPHY with a single point. ST_GEOGPOINT creates a point from the specified FLOAT64 longitude (in degrees, negative west of the Prime Meridian, positive east) and latitude (in degrees, positive north of the Equator, negative south) parameters and returns that point in a GEOGRAPHY value.

For more information, please refer to ST_GEOGPOINT (https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions#st_geogpoint)function.

Note

The function ST_GEOGPOINT is translated to ST_POINT in Snowflake.

Grammar Syntax¶

 ST_GEOGPOINT(longitude, latitude)

Copy

Sample Source¶

BigQuery¶

 SELECT ST_GEOGPOINT(-122.0838, 37.3860);

Copy

Snowflake¶

 SELECT ST_POINT(-122.0838, 37.3860);

Copy

Using ST_POINT function to insert geography data¶

This function is not allowed in the values clause and is not required in Snowflake.

BigQuery¶

 CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
);

INSERT INTO test.geographyType
VALUES (ST_GEOGPOINT(-122.0838, 37.3860));

Copy

Snowflake¶

 CREATE OR REPLACE TABLE test.geographyType
(
  COL1 GEOGRAPHY
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": {  "major": 0,  "minor": 0,  "patch": "0" }, "attributes": {  "component": "bigquery",  "convertedOn": "04/03/2025",  "domain": "test" }}';

INSERT INTO test.geographyType
VALUES (
--** SSC-FDM-BQ0010 - THE FUNCTION 'ST_GEOGFROMTEXT' IS NOT REQUIRED IN SNOWFLAKE. **
'POINT(122.0838 37.3860)');

Copy

Related EWI’s¶

SSC-FDM-BQ0010: Geography function is not required in Snowflake.