Skip to main content

QFSQL Reference

QFSQL (QuantFlow SQL) is an engine-agnostic expression language used in DataInfra field mappings. Each function is translated to engine-native SQL by adapter-specific translators — the same transformation string produces correct SQL across BigQuery, DuckDB, Snowflake, and Trino.

Cast / Convert​

cast_safe(expr, type)​

Safe type cast — returns NULL on failure instead of raising an error.

EngineTranslation
BigQuerysafe_cast(expr AS type)
DuckDBCAST(expr AS type) (no safe cast)
SnowflakeTRY_CAST(expr AS type)
TrinoCAST(expr AS type) (no safe cast, fallback)

cast(expr, type)​

Standard type cast — may error on invalid input.

EngineTranslation
AllCAST(expr AS type)

try_cast(expr, type)​

Explicit try-cast (alias for cast_safe on engines that support it).

EngineTranslation
BigQuerysafe_cast(expr AS type)
DuckDBTRY_CAST(expr AS type)
SnowflakeTRY_CAST(expr AS type)
TrinoCAST(expr AS type) (fallback)

to_string(expr) / to_integer(expr) / to_decimal(expr) / to_boolean(expr)​

Type-specific cast shorthands.

FunctionBigQueryDuckDBSnowflakeTrino
to_string(x)CAST(x AS STRING)CAST(x AS VARCHAR)CAST(x AS VARCHAR)CAST(x AS TEXT)
to_integer(x)CAST(x AS INT64)CAST(x AS INTEGER)CAST(x AS INTEGER)CAST(x AS INTEGER)
to_decimal(x)CAST(x AS NUMERIC)CAST(x AS DECIMAL)CAST(x AS NUMBER)CAST(x AS NUMERIC)
to_boolean(x)CAST(x AS BOOL)CAST(x AS BOOLEAN)CAST(x AS BOOLEAN)CAST(x AS BOOLEAN)

Date / Time​

timestamp_ms(expr) / timestamp_us(expr) / timestamp_ns(expr)​

Convert Unix milliseconds / microseconds / nanoseconds to a timestamp.

FunctionBigQueryDuckDBSnowflakeTrino
timestamp_ms(x)safe.timestamp_millis(x)TO_TIMESTAMP(x / 1000.0)TO_TIMESTAMP_LTZ(x / 1000)TO_TIMESTAMP(x / 1000.0)
timestamp_us(x)safe.timestamp_micros(x)TO_TIMESTAMP(x / 1000000.0)TO_TIMESTAMP_LTZ(x / 1000000)TO_TIMESTAMP(x / 1000000.0)
timestamp_ns(x)safe.timestamp_nanos(x)TO_TIMESTAMP(x / 1000000000.0)TO_TIMESTAMP_NTZ(x / 1000000000)TO_TIMESTAMP(x / 1000000000.0)

timestamp_seconds(expr)​

Convert Unix seconds to a timestamp.

EngineTranslation
BigQuerytimestamp_seconds(expr)
DuckDBTO_TIMESTAMP(expr)
SnowflakeTO_TIMESTAMP_LTZ(expr)
TrinoTO_TIMESTAMP(expr)
transformation: "timestamp_ms(cast_safe(trade_time, bigint))"

date_trunc(expr, unit)​

Truncate a timestamp to the specified unit.

EngineTranslation
BigQuerydate_trunc(expr, unit)
DuckDBDATE_TRUNC(unit, expr)
SnowflakeDATE_TRUNC(unit, expr)
TrinoDATE_TRUNC(unit, expr)

date_add(expr, interval, unit)​

Add an interval to a date or timestamp.

EngineTranslation
BigQuerydate_add(expr, INTERVAL interval unit)
DuckDBexpr + INTERVAL interval unit
SnowflakeDATEADD(unit, interval, expr)
Trinoexpr + INTERVAL 'interval unit'

Units: day, hour, minute, second, month, year

date_diff(date1, date2, unit)​

Compute the difference between two dates.

EngineTranslation
BigQuerydate_diff(date1, date2, unit)
DuckDBDATE_DIFF(unit, date2, date1)
SnowflakeDATEDIFF(unit, date2, date1)
TrinoEXTRACT(EPOCH FROM (date1 - date2))

extract(part, expr)​

Extract a date part from a timestamp.

EngineTranslation
AllEXTRACT(part FROM expr)

Parts: year, month, day, hour, minute, second

format_timestamp(expr, format)​

Format a timestamp as a string.

EngineTranslation
BigQueryformat_timestamp(format, expr)
DuckDBSTRFTIME(expr, format)
SnowflakeTO_CHAR(expr, format)
TrinoTO_CHAR(expr, format)

current_timestamp() / current_date()​

Return the current timestamp or date.

FunctionBigQueryDuckDBSnowflakeTrino
current_timestamp()CURRENT_TIMESTAMP()CURRENT_TIMESTAMPCURRENT_TIMESTAMP()CURRENT_TIMESTAMP
current_date()CURRENT_DATE()CURRENT_DATECURRENT_DATE()CURRENT_DATE

String​

str_concat(expr1, expr2, ...)​

Concatenate two or more strings. Null-safe.

EngineTranslation
BigQueryCONCAT(expr1, expr2, ...)
DuckDBCONCAT(expr1, expr2, ...)
SnowflakeCONCAT(expr1, expr2, ...)
Trino(expr1 || expr2 || ...)
transformation: "str_concat('binance_', cast_safe(trade_id, string))"

str_length(expr)​

Return the character length of a string.

FunctionBigQueryDuckDBSnowflakeTrino
str_length(x)LENGTH(x)LENGTH(x)LENGTH(x)LENGTH(x)

str_lower(expr) / str_upper(expr)​

Lowercase or uppercase a string.

EngineTranslation
AllLOWER(expr) / UPPER(expr)

str_trim(expr)​

Trim whitespace from both ends of a string.

EngineTranslation
AllTRIM(expr)

str_replace(source, from, to)​

Replace occurrences of from with to in source.

EngineTranslation
AllREPLACE(source, from, to)

str_substring(expr, start, length?)​

Extract a substring. start is 1-indexed. If length is omitted, extracts to end.

EngineTranslation
BigQuerySUBSTR(expr, start, length)
DuckDBSUBSTRING(expr, start, length)
SnowflakeSUBSTRING(expr, start, length)
TrinoSUBSTRING(expr FROM start FOR length)

Check if source contains search. Returns boolean.

EngineTranslation
BigQuerySTRPOS(source, search) > 0
DuckDBSTRPOS(source, search) > 0
SnowflakeCONTAINS(source, search)
TrinoSTRPOS(source, search) > 0

str_starts_with(source, prefix) / str_ends_with(source, suffix)​

Check if source starts/ends with a given string.

FunctionBigQueryDuckDBSnowflakeTrino
str_starts_with(s, p)STARTS_WITH(s, p)STARTS_WITH(s, p)STARTSWITH(s, p)s LIKE p || '%'
str_ends_with(s, p)ENDS_WITH(s, p)ENDS_WITH(s, p)ENDSWITH(s, p)s LIKE '%' || p

regexp_extract(source, pattern)​

Extract the first regex match.

EngineTranslation
BigQueryREGEXP_EXTRACT(source, pattern)
DuckDBREGEXP_EXTRACT(source, pattern)
SnowflakeREGEXP_SUBSTR(source, pattern)
Trino(SELECT (regexp_matches(source, pattern))[1])

regexp_replace(source, pattern, replacement)​

Replace text matching a regex pattern.

EngineTranslation
BigQueryREGEXP_REPLACE(source, pattern, replacement)
DuckDBREGEXP_REPLACE(source, pattern, replacement)
SnowflakeREGEXP_REPLACE(source, pattern, replacement)
TrinoREGEXP_REPLACE(source, pattern, replacement)

regexp_contains(source, pattern)​

Check if source matches a regex pattern.

EngineTranslation
BigQueryREGEXP_CONTAINS(source, pattern)
DuckDBREGEXP_MATCHES(source, pattern)
SnowflakeREGEXP_LIKE(source, pattern)
Trinosource ~ pattern

str_split(str, delimiter)​

Split a string into an array by delimiter.

EngineTranslation
BigQuerySPLIT(str, delimiter)
DuckDBstring_split(str, delimiter)
SnowflakeSPLIT(str, delimiter)
TrinoSTRING_TO_ARRAY(str, delimiter)

str_join(array, delimiter)​

Join an array into a string with delimiter.

EngineTranslation
BigQueryARRAY_TO_STRING(array, delimiter)
DuckDBARRAY_TO_STRING(array, delimiter)
SnowflakeARRAY_TO_STRING(array, delimiter)
TrinoARRAY_TO_STRING(array, delimiter)

Numeric / Aggregate​

coalesce(expr1, expr2, ...)​

Return the first non-NULL value.

EngineTranslation
AllCOALESCE(expr1, expr2, ...)
transformation: "coalesce(price, 0)"

null_if(expr1, expr2)​

Return NULL if expr1 == expr2, otherwise return expr1.

EngineTranslation
AllNULLIF(expr1, expr2)

if_null(expr, fallback)​

Return fallback if expr is NULL.

EngineTranslation
BigQueryIFNULL(expr, fallback)
DuckDBCOALESCE(expr, fallback)
SnowflakeIFNULL(expr, fallback)
TrinoCOALESCE(expr, fallback)

round(expr, decimals?)​

Round to decimals places (default 0).

EngineTranslation
AllROUND(expr, decimals)

floor(expr) / ceiling(expr)​

Floor or ceiling to nearest integer.

EngineTranslation
AllFLOOR(expr) / CEIL(expr) (BigQuery: CEIL, others: CEIL)

abs(expr)​

Absolute value. Identical across all engines: ABS(expr).

greatest(v1, v2, ...) / least(v1, v2, ...)​

Return the largest or smallest value among arguments.

EngineTranslation
AllGREATEST(v1, v2, ...) / LEAST(v1, v2, ...)

sum(expr) / avg(expr) / count(expr) / min(expr) / max(expr)​

Standard aggregate functions. Identical across all engines.

count_distinct(expr)​

Count distinct values. Translates to COUNT(DISTINCT expr) on all engines.

str_agg(expr, delimiter?)​

Aggregate strings with a delimiter.

EngineTranslation
BigQuerySTRING_AGG(expr, delimiter)
DuckDBSTRING_AGG(expr, delimiter)
SnowflakeLISTAGG(expr, delimiter)
TrinoSTRING_AGG(expr, delimiter)

Conditional​

if(condition, true_val, false_val)​

IF-THEN-ELSE expression.

EngineTranslation
BigQueryIF(condition, true_val, false_val)
DuckDBIF(condition, true_val, false_val)
SnowflakeIFF(condition, true_val, false_val)
TrinoCASE WHEN condition THEN true_val ELSE false_val END

case_when(when_clause)​

Multi-branch CASE expression. The argument is the body of a CASE ... END block — conditions and results supplied inline.

transformation: "case_when(WHEN price > 100 THEN 'high' WHEN price > 50 THEN 'mid' ELSE 'low')"
EngineTranslation
AllCASE when_clause END

Array / JSON​

array(elem1, elem2, ...)​

Create an array from elements.

EngineTranslation
BigQuery[elem1, elem2, ...]
DuckDBLIST[elem1, elem2, ...]
SnowflakeARRAY_CONSTRUCT(elem1, elem2, ...)
TrinoARRAY[elem1, elem2, ...]

array_length(arr)​

Return the length of an array.

EngineTranslation
BigQueryARRAY_LENGTH(arr)
DuckDBLEN(arr)
SnowflakeARRAY_SIZE(arr)
TrinoCARDINALITY(arr)

array_contains(arr, elem)​

Check if an array contains an element.

EngineTranslation
BigQueryelem IN UNNEST(arr)
DuckDBLIST_CONTAINS(arr, elem)
SnowflakeARRAY_CONTAINS(elem, arr)
Trinoelem = ANY(arr)

struct(field1, field2, ...)​

Create a struct/object from fields.

EngineTranslation
BigQuerySTRUCT(field1, field2, ...)
DuckDBSTRUCT(field1, field2, ...)
SnowflakeOBJECT_CONSTRUCT(field1, field2, ...)
TrinoROW(field1, field2, ...)

json_extract(json_str, path)​

Extract a value from a JSON string by path.

EngineTranslation
BigQueryJSON_EXTRACT(json_str, path)
DuckDBjson_extract_string(json_str, path)
SnowflakeJSON_EXTRACT_PATH_TEXT(json_str, path)
Trinojson_str -> path

json_serialize(expr)​

Serialize an expression to a JSON string.

EngineTranslation
BigQueryTO_JSON_STRING(expr)
DuckDBTO_JSON(expr)
SnowflakeTO_JSON(expr)
Trinoexpr :: TEXT

Hash​

md5(expr) / sha256(expr)​

Compute the MD5 or SHA-256 hash of a string.

FunctionBigQueryDuckDBSnowflakeTrino
md5(x)MD5(x)MD5(x)MD5(x)MD5(x)
sha256(x)SHA256(x)SHA256(x)SHA2(x, 256)ENCODE(DIGEST(x, 'sha256'), 'hex')

uuid()​

Generate a random UUID. Takes no arguments.

EngineTranslation
BigQueryGENERATE_UUID()
DuckDBUUID()
SnowflakeUUID_STRING()
TrinoGEN_RANDOM_UUID()::TEXT

DolphinDB-Specific Functions​

Available only when the target engine is DolphinDB. These are defined in the streaming expression mapper and use native DolphinDB timeseries functions.

FunctionDescription
cast_safe(expr, type)Safe type cast
timestamp_ms(expr)Unix ms → DolphinDB timestamp
timestamp_us(expr)Unix µs → DolphinDB timestamp
current_timestamp()Current DolphinDB timestamp
str_concat(...)String concatenation
array_element(arr, index)Array element by index
array_sum(arr)Sum of array elements
raw_expr(dolphindb_expr)Passthrough raw DolphinDB expression
# Example: raw DolphinDB expression passthrough
transformation: "raw_expr(ofi(price, size, 0.95))"

Literal Values​

QFSQL supports inline literal values directly in transformation strings:

# String literal
transformation: "'binance'"

# Numeric literal
transformation: "0.95"

# Boolean literal
transformation: "true"

# Current timestamp
transformation: "current_timestamp()"

Literals pass through unchanged to engine-native SQL.