SnowConvert AI - BigQuery - Built-in functions

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

Note

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

Aggregate Functions

BigQuerySnowflake
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)

AVGAVG
COUNTCOUNT
COUNTIFCOUNT_IF
LOGICAL_ANDBOOLAND_AGG
LOGICAL_ORBOOLOR_AGG
MAXMAX
MINMIN
SUMSUM

Array Functions

BigQuerySnowflake
ARRAY_AGGARRAY_AGG
ARRAY_CONCATARRAY_CAT
ARRAY_CONCAT_AGGARRAY_FLATTEN
ARRAY_TO_STRING(expr, delimiter)ARRAY_TO_STRING(ARRAY_COMPACT(expr), delimiter)
ARRAY_TO_STRING(expr, delimiter, null_text)

ARRAY_TO_STRING_UDF(expr, delimiter, null_text)

Notes: SnowConvert AI generates a UDF to handle the NULL replacement parameter which is not natively supported in Snowflake’s ARRAY_TO_STRING function.

SELECT ARRAY (SELECT query)

SELECT (SELECT ARRAY_AGG(*) FROM (SELECT query))

Notes: BigQuery’s ARRAY subquery syntax is transformed to use ARRAY_AGG with a subquery in Snowflake.

Conditional Expressions

BigQuerySnowflake

COALESCE

COALESCE

IF

IFF

IFNULL

IFNULL

NULLIF

NULLIF

Conversion Functions

BigQuerySnowflake
SAFE_CASTTRY_CAST

Date Functions

BigQuerySnowflake

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

BigQuerySnowflake

CURRENT_DATETIME

CURRENT_DATETIME()

CURRENT_TIMESTAMP :: TIMESTAMP_NTZ
CURRENT_TIMESTAMP() :: TIMESTAMP_NTZ

Geography Functions

BigQuerySnowflake
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

BigQuerySnowflake
JSON_VALUE / JSON_EXTRACT_SCALAR

JSON_EXTRACT_PATH_TEXT

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

JSON_VALUE_ARRAY

JSON_VALUE_ARRAY_UDF

Notes: SnowConvert AI generates a UDF to obtain an equivalent behavior for extracting arrays from JSON.

LAX_INT64

PUBLIC.LAX_INT64_UDF

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

LAX_BOOL

PUBLIC.LAX_BOOL_UDF

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

Mathematical Functions

BigQuerySnowflake
ABSABS
LEASTLEAST
MODMOD

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’)

BigQuerySnowflake
FIRST_VALUEFIRST_VALUE
LAGLAG
LEADLEAD
LAST_VALUELAST_VALUE

Numbering Functions

BigQuerySnowflake
RANKRANK
ROW_NUMBERROW_NUMBER

String Functions

BigQuerySnowflake
BYTE_LENGTH(expr)

LENGTH(TO_BINARY(HEX_ENCODE(expr)))

Notes: BigQuery’s BYTE_LENGTH returns the number of bytes in an encoded string. Snowflake equivalent converts to binary after hex encoding to get byte length.

CHARACTER_LENGTH
CHAR_LENGTH

LENGTH
CONCATCONCAT
ENDS_WITHENDSWITH
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.

LEFTLEFT
LENGTHLENGTH
LOWERLOWER
LPADLPAD
LTRIMLTRIM
REGEXP_CONTAINS(value, regexp)REGEXP_INSTR(value, regexp) > 0
REGEXP_EXTRACT_ALLREGEXP_SUBSTR_ALL
REPLACEREPLACE
RIGHTRIGHT
RPADRPAD
RTRIMRTRIM
SPLITSPLIT
STARTS_WITHSTARTSWITH

SUBSTR(string, position)

SUBSTRING(string, position)

SUBSTR(string, position, length)

SUBSTRING(string, position, length)

SUBSTR(string, IFF(position < -LENGTH(string), 1, position))

SUBSTRING(string, IFF(position < -LENGTH(string), 1, position))

SUBSTR(string, IFF(position < -LENGTH(string), 1, position), length)

SUBSTRING(string, IFF(position < -LENGTH(string), 1, position), length)

TO_HEXHEX_ENCODE
UPPERUPPER

Timestamp Functions

BigQuerySnowflake

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

SAFE.TIMESTAMP_MILLIS

IFF(expr BETWEEN -62135596800000 AND 253402300799999, TO_TIMESTAMP(expr / 1000), null)

Notes: Safe version with range validation to prevent overflow errors.

SAFE.TIMESTAMP_SECONDS

SAFE_TIMESTAMP_SECONDS_UDF(expr)

Notes: SnowConvert AI generates a UDF to provide safe timestamp conversion with error handling.

TIMESTAMP_MILLIS

TO_TIMESTAMP(expr / 1000)

Notes: Converts milliseconds since epoch to timestamp by dividing by 1000.

TIMESTAMP_SECONDS(expr)

DATEADD(‘seconds’, expr, ’1970-01-01’)

Notes: Adds seconds to Unix epoch start date.

UNIX_MICROS(timestamp)

DATE_PART(‘epoch_microsecond’, CONVERT_TIMEZONE(‘UTC’, timestamp))

Notes: Extracts microseconds since Unix epoch from timestamp converted to UTC.

UNIX_MILLIS(timestamp)

DATE_PART(‘epoch_millisecond’, CONVERT_TIMEZONE(‘UTC’, timestamp))

Notes: Extracts milliseconds since Unix epoch from timestamp converted to UTC.

UNIX_SECONDS(timestamp)

DATE_PART(‘epoch_seconds’, CONVERT_TIMEZONE(‘UTC’, timestamp))

Notes: Extracts seconds since Unix epoch from timestamp converted to UTC.

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)

Sample Source

BigQuery

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

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

BigQuery Formats Equivalents

BigQuerySnowflake
%APUBLIC.DAYNAME_LONG_UDF(date_expr)

Note: Generate UDF in conversion for support.
%aDY
%BMMMM
%bMON
%CPUBLIC.CENTURY_UDF(date_expr)

Note: Generate UDF in conversion for support.
%cDY MON DD HH24:MI:SS YYYY
%DMM/DD/YY
%dDD
%eDD
%FYYYY-MM-DD
%GYEAROFWEEKISO(date_expr)
%gPUBLIC.ISO_YEAR_PART_UDF(date_expr, 2)

Note: Generate UDF in conversion for support.
%HHH24
%hMON
%IHH12
%JPUBLIC.DAY_OF_YEAR_ISO_UDF(date_expr)

Note: Generate UDF in conversion for support.
%jDAYOFYEAR(date_expr)
%kHH24
%lHH12
%MMI
%mMM
%nNot equivalent format
%Ppm
%pAM
%QQUARTER(date_expr)
%RHH24:MI
%SSS
%sNot equivalent format
%THH24:MI:SS
%tNot equivalent format
%UWEEK(date_expr)
%uDAYOFWEEKISO(date_expr)
%VWEEKISO(date_expr)
%WWEEK(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.
%wDAYOFWEEK(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.
%XHH24:MI:SS
%xMM/DD/YY
%YYYYY
%yYY
%ZNot equivalent format
%zNot equivalent format
%EzNot equivalent format
%E<number>SNot equivalent format
%E*SNot equivalent format
%EY4YYYY

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.

Note

:class: tip ST_GEOGFROMTEXT function is supported in Snowflake.

Grammar Syntax


 ST_GEOGFROMTEXT(wkt_string[, oriented])

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);
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))');

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)'));
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)');
  1. SSC-EWI-BQ0006: Oriented parameter in the ST_GEOGFROMTEXT function is not supported in Snowflake.
  2. SSC-FDM-BQ0010: Geography function is not required in Snowflake.

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)

Sample Source

BigQuery

 SELECT ST_GEOGPOINT(-122.0838, 37.3860);
Snowflake

 SELECT ST_POINT(-122.0838, 37.3860);

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_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));
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)');
  1. SSC-FDM-BQ0010: Geography function is not required in Snowflake.