SnowConvert AI - BigQuery - Built-in functions¶

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)

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 (SELECT ARRAY_AGG(*) FROM (SELECT query))

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

Equivalent TRY_* function (TRY_TO_DATE, TRY_TO_TIMESTAMP_TZ, TRY_CAST, etc.)

Notes: SnowConvert AI rewrites the SAFE-prefixed call to the corresponding TRY_* Snowflake function so failures return NULL instead of raising an error.

CURRENT_DATE
CURRENT_DATE()

CURRENT_DATE

CURRENT_DATE()

DATEADD(‘part’, n, date)

Notes: SnowConvert AI re-orders the arguments and converts the BigQuery INTERVAL n part literal into Snowflake’s (part_string, n, date) argument layout. Negative literals are preserved.

DATEADD(‘part’, -n, date)

Notes: Snowflake has no DATE_SUB; the call is rewritten to DATEADD with the integer expression negated. Literal negative intervals collapse, non-literals are wrapped with a unary minus.

DATEDIFF(‘part’, date_b, date_a)

Notes: BigQuery’s DATE_DIFF(a, b, part) computes a - b; Snowflake’s DATEDIFF('part', b, a) reverses the operand order. SnowConvert AI swaps the arguments so the result sign matches BigQuery. ISOWEEK, ISOYEAR and WEEK(<weekday>) are translated to the corresponding Snowflake date parts.

DATE_TRUNC(‘part’, date)

Notes: BigQuery uses (date, part) argument order; Snowflake uses ('part', date). SnowConvert AI swaps the arguments and quotes the part as a string literal.

DAYOFWEEK(date) + 1

Notes: BigQuery’s DAYOFWEEK returns 1..7 with Sunday = 1; Snowflake’s returns 0..6 with Sunday = 0. SnowConvert AI adds + 1 to keep the result aligned. See SSC-FDM-BQ0008 when the argument is influenced by the WEEK_START session parameter.

EXTRACT(WEEKISO FROM date)

Notes: BigQuery’s ISOWEEK date part is renamed to Snowflake’s WEEKISO.

TO_CHAR

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

TO_DATE(date_string, translated_format)

Notes: BigQuery format codes (%Y, %m, %d, etc.) are translated at compile time to Snowflake equivalents (YYYY, MM, DD, etc.). Non-literal format strings are passed through unchanged with a warning.

CURRENT_DATETIME

CURRENT_DATETIME()

CURRENT_TIMESTAMP :: TIMESTAMP_NTZ
CURRENT_TIMESTAMP() :: TIMESTAMP_NTZ

TIMESTAMP_NTZ_FROM_PARTS(year, month, day, hour, minute, second)

Notes: BigQuery’s DATETIME constructor maps to Snowflake’s TIMESTAMP_NTZ_FROM_PARTS. The single-argument form DATETIME(timestamp) and the DATETIME(date, time) form are also supported.

TO_TIMESTAMP_NTZ(datetime_string, translated_format)

Notes: BigQuery format codes are translated at compile time to Snowflake equivalents. Non-literal format strings are passed through unchanged with a warning.

TO_TIME(time_string, translated_format)

Notes: BigQuery format codes are translated at compile time to Snowflake equivalents. Non-literal format strings are passed through unchanged with a warning.

TO_CHAR(time_expr, translated_format)

Notes: BigQuery format codes are translated at compile time to Snowflake equivalents. Non-literal format strings are passed through unchanged with a warning.

DATEADD(‘part’, n, time_expr)

Notes: SnowConvert AI re-orders the arguments and converts the BigQuery INTERVAL n part literal into Snowflake’s (part_string, n, time_expr) argument layout.

DATEDIFF(‘part’, time2, time1)

Notes: BigQuery’s TIME_DIFF(a, b, part) computes a - b; SnowConvert AI swaps the arguments to match Snowflake’s (start, end) convention.

DATEADD(‘part’, -n, time_expr)

Notes: Snowflake has no TIME_SUB; SnowConvert AI rewrites the call to DATEADD with the interval count negated.

FORMAT_TIME(format, time_expr)

TO_CHAR(time_expr, converted_format)

Notes: BigQuery strptime-style format specifiers (e.g. %H, %M, %S, %T, %R) are mapped to Snowflake TO_CHAR format codes. Unsupported specifiers emit SSC-EWI-0006. Non-literal format arguments fall through to SSC-EWI-0073.

TIME_ADD(time_expr, INTERVAL n part)

DATEADD(‘part’, n, time_expr)

Notes: SnowConvert AI re-orders the arguments and converts the BigQuery INTERVAL n part literal into Snowflake’s argument layout.

TIME_DIFF(time1, time2, part)

DATEDIFF(‘part’, time2, time1)

Notes: SnowConvert AI swaps the arguments so the result sign matches BigQuery’s time1 - time2 semantics.

TIME_SUB(time_expr, INTERVAL n part)

DATEADD(‘part’, -n, time_expr)

Notes: Snowflake has no TIME_SUB; SnowConvert AI rewrites the call to DATEADD with the interval count negated.

ST_GEOGFROMTEXT

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

ST_POINT

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

ST_DISTANCE(geog_a, geog_b)

Notes: The two-argument form is a direct pass-through. The three-argument use_spheroid parameter has no Snowflake equivalent and is dropped; if its value is anything other than the literal FALSE, see SSC-FDM-BQ0014.

JSON_EXTRACT_PATH_TEXT

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

JSON_VALUE_ARRAY_UDF

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

PUBLIC.LAX_INT64_UDF

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

PUBLIC.LAX_BOOL_UDF

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

PARSE_JSON(json_string)

Notes: The single-argument form is a direct pass-through. The optional wide_number_mode argument has no Snowflake equivalent and is dropped; Snowflake preserves number precision up to 38 digits. See SSC-FDM-BQ0015.

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

CURRENT_USER

Notes: BigQuery’s SESSION_USER returns the email address of the authenticated IAM principal; Snowflake’s CURRENT_USER returns the Snowflake username (not an email). See SSC-FDM-BQ0016.

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

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.

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.

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)

UNICODE(string_expr)

Notes: Direct pass-through. Both functions return the Unicode code point of the first character of the input string.

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

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

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

SAFE_TIMESTAMP_SECONDS_UDF(expr)

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

TO_TIMESTAMP(expr / 1000)

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

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

Notes: Adds seconds to Unix epoch start date.

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

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

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

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

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

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

TO_TIMESTAMP_NTZ(timestamp_string, converted_format) — or, when a timezone argument is supplied, CONVERT_TIMEZONE(timezone, ‘UTC’, TO_TIMESTAMP_NTZ(…)) :: TIMESTAMP_TZ

Notes: BigQuery format elements (e.g. %Y-%m-%d %H:%M:%S) are translated to the equivalent Snowflake format. The 3-argument form emits SSC-EWI-BQ0018 because Snowflake interprets the timezone as the source rather than the result of the parse.

TO_VARCHAR(timestamp, converted_format) — or, when a timezone argument is supplied, TO_VARCHAR(CONVERT_TIMEZONE(timezone, timestamp), converted_format)

Notes: BigQuery format elements are translated to the equivalent Snowflake format. The 3-argument form first shifts the timestamp into the requested timezone before formatting.

PUBLIC.ERROR_UDF(message)

Notes: BigQuery’s ERROR raises a runtime error with the supplied message. SnowConvert generates a UDF wrapper that calls a Snowflake stored procedure to raise an equivalent exception, since Snowflake SQL does not provide a built-in ERROR function in scalar context.

UUID_STRING()

Notes: Both functions return an RFC 4122-compliant version 4 UUID as a string. Direct replacement.