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	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( expr1, HAVING MAX expr2) ANY_VALUE( expr1, HAVING MIN expr2)	MAX_BY(expr1, expr1) MIN_BY(expr1, expr2)
AVG	AVG
COUNT	COUNT
COUNTIF	COUNT_IF
LOGICAL_AND	BOOLAND_AGG
LOGICAL_OR	BOOLOR_AGG
MAX	MAX
MIN	MIN
SUM	SUM

Conditional Expressions¶

BigQuery	Snowflake
COALESCE	COALESCE
IF	IFF
IFNULL	IFNULL
NULLIF	NULLIF

Conversion Functions¶

BigQuery	Snowflake
SAFE_CAST	TRY_CAST

Date Functions¶

BigQuery	Snowflake
CURRENT_DATE CURRENT_DATE()	CURRENT_DATE CURRENT_DATE()
FORMAT_DATE	TO_CHAR Note: For further details on this translation, please consult this page.

BigQuery

Snowflake

CURRENT_DATE
CURRENT_DATE()

CURRENT_DATE

CURRENT_DATE()

FORMAT_DATE

TO_CHAR

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

Datetime Functions¶

BigQuery	Snowflake
CURRENT_DATETIME CURRENT_DATETIME()	CURRENT_TIMESTAMP :: TIMESTAMP_NTZ CURRENT_TIMESTAMP() :: TIMESTAMP_NTZ

BigQuery

Snowflake

CURRENT_DATETIME

CURRENT_DATETIME()

CURRENT_TIMESTAMP :: TIMESTAMP_NTZ
CURRENT_TIMESTAMP() :: TIMESTAMP_NTZ

Geography Functions¶

BigQuery	Snowflake
ST_GEOGFROMTEXT	ST_GEOGFROMTEXT Note: For further details on this translation, please consult this page.
ST_GEOGPOINT	ST_POINT Note: For further details on this translation, please consult this page.

BigQuery

Snowflake

ST_GEOGFROMTEXT

ST_GEOGFROMTEXT

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

ST_GEOGPOINT

ST_POINT

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

JSON Functions¶

BigQuery	Snowflake
JSON_VALUE / JSON_EXTRACT_SCALAR	JSON_EXTRACT_PATH_TEXT Notes: SnowConvert automatically translates BigQuery JSON paths to their Snowflake equivalents.
LAX_INT64	PUBLIC.LAX_INT64_UDF Notes: SnowConvert generates a UDF to obtain an equivalent behavior.
LAX_BOOL	PUBLIC.LAX_BOOL_UDF Notes: SnowConvert generates a UDF to obtain an equivalent behavior.

BigQuery

Snowflake

JSON_VALUE / JSON_EXTRACT_SCALAR

JSON_EXTRACT_PATH_TEXT

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

LAX_INT64

PUBLIC.LAX_INT64_UDF

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

LAX_BOOL

PUBLIC.LAX_BOOL_UDF

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

Mathematical Functions¶

BigQuery	Snowflake
ABS	ABS
LEAST	LEAST
MOD	MOD
ROUND(X) ROUND(X, Y) ROUND(X, Y, ‘ROUND_HALF_EVEN’) 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’)

BigQuery	Snowflake
FIRST_VALUE	FIRST_VALUE
LAG	LAG
LEAD	LEAD
LAST_VALUE	LAST_VALUE

Numbering Functions¶

BigQuery	Snowflake
RANK	RANK
ROW_NUMBER	ROW_NUMBER

String Functions¶

BigQuery	Snowflake
CHARACTER_LENGTH CHAR_LENGTH	LENGTH
CONCAT	CONCAT
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	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	LEFT
LENGTH	LENGTH
LOWER	LOWER
LPAD	LPAD
LTRIM	LTRIM
STARTS_WITH	STARTSWITH
REPLACE	REPLACE
RIGHT	RIGHT
RPAD	RPAD
RTRIM	RTRIM
SPLIT	SPLIT
SUBSTR(string, position) SUBSTRING(string, position) SUBSTR(sttring, position, length) 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	UPPER

Timestamp Functions¶

BigQuery	Snowflake
CURRENT_TIMESTAMP CURRENT_TIMESTAMP()	CURRENT_TIMESTAMP CURRENT_TIMESTAMP()

BigQuery

Snowflake

CURRENT_TIMESTAMP
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 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.

ST_GEOGFROMTEXT¶

Geography Function.

Description¶

Returns a GEOGRAPHY value that corresponds to the input WKT representation.

For more information, please refer to 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 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.

SnowConvert AI - BigQuery - Built-in functions¶

Aggregate Functions¶

Conditional Expressions¶

Conversion Functions¶

Date Functions¶

Datetime Functions¶

Geography Functions¶

JSON Functions¶

Mathematical Functions¶

Navigation Functions¶

Numbering Functions¶

String Functions¶

Timestamp Functions¶

FORMAT_DATE¶

Description¶

Grammar Syntax¶

Sample Source¶

BigQuery¶

Snowflake¶

BigQuery Formats Equivalents¶

ST_GEOGFROMTEXT¶

Description¶

Grammar Syntax¶

Sample Source¶

BigQuery¶

Snowflake¶

Using ST_GEOGFROMTEXT function to insert geography data¶

BigQuery¶

Snowflake¶

Related EWI’s¶

ST_GEOGPOINT¶

Description¶

Grammar Syntax¶

Sample Source¶

BigQuery¶

Snowflake¶

Using ST_POINT function to insert geography data¶

BigQuery¶

Snowflake¶

Related EWI’s¶