Function mapping

While importing Alteryx workflows to Prophecy pipelines, Prophecy converts Alteryx functions into equivalent Databricks SQL functions. The following tables list all the Alteryx functions that Prophecy supports and the corresponding Prophecy function they map to in Databricks SQL.

Conditional

When you import conditional Alteryx functions into Prophecy, the logic of your formulas stays the same, but the syntax changes, because Databricks SQL uses CASE expressions instead of IF/IIF/Switch. Prophecy’s import makes these conversions for you. When reading or debugging generated SQL, you should recognize the CASE functions below as the Databricks equivalent of Alteryx’s conditional functions.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`IF c THEN t ELSE f ENDIF`	`CASE WHEN THEN ELSE END`	`CASE_WHEN` is equivalent to `IF`; `ENDIF` is equivalent to `END`.
`IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF`	`CASE WHEN c THEN t WHEN c2 THEN t2 ELSE f END`	`CASE_WHEN` is equivalent to `IF`; `ENDIF` is equivalent to `END`. In Alteryx, you chain conditions with `ELSEIF`. In Databrick SQL, each `ELSEIF` becomes another `WHEN … THEN …`.
`IIF`	`CASE WHEN c THEN t ELSE f END`	In Alteryx, `IFF` serves as a shorthand for `IF ... ELSE`. In Databricks SQL, `IFF` is equivalent to a `CASE` with one condition.
`SWITCH`	`CASE WHEN c THEN t WHEN c2 THEN t2 ... END`	In Alteryx, `SWITCH` tests one case against several possibilities. In Databricks SQL, this is expressed as a `CASE` with multiple `WHEN … THEN …` clauses.

Conversions

Whereas Alteryx has a lot of specialized conversion functions, Databricks SQL tends to use general functions, such as conv for base conversions and cast for changing data types.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`BinToInt`	`conv()`	Databricks uses `conv` for all base conversions in the form `conv(expr, from_base, to_base)`.
`CharFromInt`	`char()`	`char` is equivalent to `CharFromInt` in Alteryx.
`CharToInt`	`ascii()`	`ascii` is equivalent to `CharToInt` in Alteryx.
`ConvertFromCodePage`	`convertFromCodePage()`	Functions are equivalent; note slightly different case in SQL function name.
`HexToNumber`	`conv()`	Databricks uses `conv` for all base conversions in the form `conv(expr, from_base, to_base)`.
`IntToBin`	`bin()`	Databricks uses `bin` for all binary conversions.
`IntToHex`	`conv()`	Databricks uses `conv` for all base conversions in the form `conv(expr, from_base, to_base)`.
`ToDegrees`	`degrees()`	Exact mapping.
`ToNumber`	`regexp_extract()`	In Databricks, `ToNumber` is approximated with `regexp_extract` . This extracts numeric substrings.
`ToRadians`	`radians()`	Exact mapping.
`ToString`	`cast(columnName as String)`	`ToString` becomes `CAST` to string.

DateTime

Alteryx provides many specialized datetime functions (each handling a specific operation), whereas Databricks SQL relies on a smaller set of general-purpose functions like date_add, date_trunc, and date_format, combined with SQL INTERVAL expressions for finer-grained control.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`DateTimeAdd`	`date_add()`	`date_add` only adds days. Alteryx `DateTimeAdd` supports units (minutes, hours, months, and so on). For these cases, use `ts + INTERVAL 3 HOURS`, `add_months(ts, n)`, or `date_add(date, nDays)` depending on unit.
`DateTimeDay`	`day()`	Exact mapping.
`DateTimeDiff`	`datediff()`	Returns dates only. For finer units, use expressions such as `unix_timestamp`/`timestampdiff` or `ts2 - ts1` with `INTERVAL`.
`DateTimeFirstOfMonth`	`date_trunc()`	Exact mapping.
`DateTimeFormat`	`date_format(ts, 'pattern')`	Exact mapping.
`DateTimeHour`	`hour()`	Exact mapping.
`DateTimeLastOfMonth`	`last_day()`	Exact mapping.
`DateTimeMinutes`	`minute()`	Exact mapping.
`DateTimeMonth`	`month()`	Exact mapping.
`DateTimeNow`	`current_timestamp()`	Exact mapping.
`DateTimeNowPrecise`	`current_timestamp()`	Exact mapping.
`DateTimeParse`	`to_date()`	Drops time. Alteryx parse can yield timestamp. Use `to_timestamp(str, 'pattern')`when you need time.
`DateTimeQuarter`	`quarter()`	Exact mapping.
`DateTimeSeconds`	`second()`	Exact mapping.
`DateTimeToday`	`current_date()`	Exact mapping.
`DateTimeToUTC`	`to_utc_timestamp(ts, tz)`	Exact mapping.
`DateTimeTrim`	`date_trunc()`	Exact mapping.
`DateTimeYear`	`year()`	Exact mapping.
`ToDate`	`to_date()`	Exact mapping.
`ToDateTime`	`to_timestamp()`	Outputs full SQL `timestamp`.

Math

Alteryx offers a large catalog of dedicated math functions, many of which map directly to Databricks SQL. Most Alteryx functions have 1:1 equivalents, with a few cases (random integers, logs) requiring light SQL expressions to reproduce exact behavior.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`ABS`	`abs()`	Exact mapping.
`ACOS`	`acos()`	Exact mapping.
`ASIN`	`asin()`	Exact mapping.
`ATAN`	`atan()`	Exact mapping.
`ATAN2`	`atan2()`	Exact mapping.
`Average`	`avg()`	`avg` ignores nulls. Use `AVG(col)` with `COALESCE(col, 0)` to treat nulls as 0.
`AverageNonNull`	`avg()`	`avg` ignores nulls by default.
`CEIL`	`ceil()`	Exact mapping.
`COS`	`cos()`	Exact mapping.
`COSH`	`cosh()`	Exact mapping.
`DISTANCE`	`haversine()`	Exact mapping.
`EXP`	`exp()`	Exact mapping.
`FACTORIAL`	`factorial()`	Exact mapping.
`FLOOR`	`floor()`	Exact mapping.
`LOG`	`ln()`	Alteryx `LOG()` returns the natural log (`ln`). In SQL, `ln(x)` is the natural log and `log10(x)` is base-10. Databricks SQL also supports arbitrary bases via `log(base, x)`.
`LOG10`	`log10()`	Exact mapping..
`Median`	`median`	Exact mapping.
`Mod`	`mod()`	Exact mapping.
`PI`	`pi()`	Exact mapping.
`POW`	`pow()`	Exact mapping.
`RAND`	`rand()`	Exact mapping.
`RandInt`	`rand()`	Alteryx `RandInt` returns an integer in an inclusive range. Databricks `rand()` returns a `DOUBLE` drawn uniformly from the interval `[0, 1)`, meaning it can return 0 but will never return 1. To mimic `RandInt(low, high)` exactly, use `cast(floor(rand() * (high - low + 1)) + low as int)`.
`Round`	`round()`	Exact mapping.
`SIN`	`sin()`	Exact mapping.
`SINH`	`sinh()`	Exact mapping.
`SQRT`	`sqrt()`	Exact mapping.
`TAN`	`tan()`	Exact mapping.
`TANH`	`tanh()`	Exact mapping.

Bitwise

Alteryx uses named bitwise functions (such as BinaryAnd and BinaryOr), while Databricks SQL relies primarily on native operators (&, |, <<, >>) and a small set of helper functions. The mappings are direct, with no semantic differences.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`BinaryAnd`	`\&`	No `Binary` keyword necessary in Databricks SQL.
`BinaryNot`	`bitwise_not`	No `Binary` keyword necessary in Databricks SQL.
`BinaryOr`	`\|`	No `Binary` keyword necessary in Databricks SQL.
`BinaryXOR`	`bitwiseXOR`	No `Binary` keyword necessary in Databricks SQL.
`ShiftLeft`	`<<`	Exact mapping.
`ShiftRight`	`>>`	Exact mapping.

Range and bound functions

There are a number of similarities between how Alteryx and Databricks SQL in terms of range and bound functions, but also some important differencees:

BETWEEN works directly; syntax just shifts to SQL form.
Bound requires an explicit CASE statement.
MAX() and MIN() are direct equivalents.
MaxIDX and MinIDX in Alteryx identify the position of a value, but in Databricks SQL you’d use sorting or window functions (ROW_NUMBER() OVER (...)) to achieve the same result.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`Between`	`between()`	In Alteryx, `Between([x], 10, 20)`; in Databricks SQL, `x BETWEEN 10 AND 20`.
`Bound`	`CASE WHEN x < min THEN min WHEN x > max THEN max ELSE x END`	Databricks SQL has no single `Bound` function; you replicate the behavior with a CASE expression to “clamp” the value inside a range.
`Max`	`max()`	In Databricks SQL, nulls are ignored by default.
`MaxIDX`	`array_max()`	Applies to arrays rather than datasets. To find the row containing the max value, you’d instead use `ORDER BY ... DESC LIMIT 1`.
`Min`	`min()`	In Databricks SQL, nulls are ignored by default.
`MinIDX`	`array_min()`	Like `array_max()`, applies to arrays. To find the row with the minimum value, use `ORDER BY ... ASC LIMIT 1`.

Operators

Alteryx Function	Equivalent Databricks SQL Function	Notes
Block comment	`/` `/`	Exact mapping.
Single-line comment	`--`	`//` does not work in Databricks SQL.
Boolean `AND` or `\&\&`	`AND`	`\&\&` is not supported in Databricks SQL. Use `AND`.
Boolean `NOT` or `!`	`!`	`!` is not supported in Databricks SQL. Use `NOT`.
Boolean `OR` or `\|\|`	`OR`	`\|\|` is not supported in Databricks SQL. Use `OR`.
Equality `==`, `=`	`=`	In Databricks SQL, equality is expressed as `=`, not `==`.
Non-equality `!=`	`!=`	Exact mapping.
Greater than, greater than or equal to, less than, less than or equal to	`>`, `>=`, `<`, `<=`	Exact mapping.
Addition, Subtraction, Multiplication, Division	`+`, `-`, `*`, `/`	Exact mapping.
Parentheses	`(` `)`	Exact mapping.
`value IN`	`IN`	Exact mapping.
`value NOT IN`	`NOT IN`	Exact mapping.

Specialized

Specialized functions in Databricks SQL have a number of similarities to those in Alteryx, but with some key differences:

Indexing: Alteryx uses zero-based indices; Databricks SQL arrays are 1-based.
Null handling: NULL() is a callable function in Alteryx, but a literal in SQL.
TOPNIDX: In Alteryx it finds the position of the top N value; in SQL you’ll use sorting or ROW_NUMBER() to replicate.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`Coalesce`	`coalesce()`	Alteryx evaluates left-to-right; Databricks SQL follows standard SQL semantics.
`GetVal`	`element_at()`	In Alteryx, `GetVal(list, index)` retrieves the element at a 0-based index. In Databricks SQL, `element_at(array, index)` is 1-based. So `GetVal([x], 0)` corresponds to `element_at(x, 1)` in Databricks SQL.
`NULL`	`coalesce()`	Alteryx allows `NULL()` as a function that returns a null value. In Databricks SQL, `NULL` is a literal (no parentheses). To substitute default values for nulls, use `coalesce()`.
`TOPNIDX`	`array_sort`(), `element_at()` and `array_position()`	No direct equivalent. `TOPNIDX` returns the 0-based index of the Nth largest value from a list. In Databricks SQL, you’d first build an array and then combine `sort_array()` (descending), `element_at()` to get the Nth largest value, and `array_position()` to find its (1-based) index. For top-N rows rather than values within a row, use `ORDER BY … LIMIT n` or a window function instead.
`UrlEncode`	`url_encode()`	Case sensitivity in Databricks SQL follows normal function naming (`url_encode`, not `UrlEncode`).

String

String functions in Databricks SQL have many similarities to Alteryx functions, with the following key differences:

Indexing: Alteryx string functions are zero-based; Databricks SQL uses 1-based positions.
Case sensitivity: Databricks SQL comparisons are case-sensitive unless normalized with lower() or upper().
Regex behavior: SQL uses standard Java regex syntax in regexp_* functions; no built-in “count” or “nth match.”
Missing functions: DecomposeUnicodeForMatch, STRCSPN, and STRSPN don’t exist; implement via UDF or external logic.
Trim and substring: simpler in SQL (trim, substring), but semantics differ slightly with whitespace and indexing.

Alteryx Function	Equivalent Databricks SQL Function	Notes
`Contains`	`contains()`	Databricks SQL is case-sensitive by default unless you apply `lower()` or `upper()`.
`CountWords`	`size()` and `split()`	No direct equivalent. In Databricks SQL, split the string into tokens and use `size(split(col, ' '))` to count space-delimited words.
`DecomposeUnicodeForMatch`	No native equivalent	Not implemented in Databricks SQL; Unicode normalization would need external processing or Python UDF.
`EndsWith`	`col LIKE '%pattern'`	SQL uses `LIKE`; remember to escape special characters and use `%` wildcards.
`FindNth`	No native equivalent	Databricks SQL does not have a built-in function to find the position of the Nth occurrence of a substring; you must use nested `locate()` calls or a regex workaround.
`FindString`	`locate(substring, col)`	`locate(substring, col)` is the Databricks SQL equivalent. It returns the 1-based position of the substring.
`GetLeft`/`Left`	`substring(col, 1, n)`	Use `substring(col, 1, n)` in Databricks SQL. Alteryx’s `Left`/`GetLeft` is 0-based, but SQL substring positions start at 1.
`GetPart`	`element_at(split(col, ' '), n)`	Split the string into an array, then use `element_at()` to retrieve the part. Databricks SQL arrays are 1-based: `GetPart(col, n)` → `element_at(split(col, ' '), n+1)` if Alteryx’s index is 0-based.
`GetRight`/`Right`	`substring(col, length(col)-n+1, n)`	SQL lacks `RIGHT()` by default; emulate with `substring()`.
`GetWord`	`element_at(split(col, ' '), n)`	Split into an array first, then use `element_at()` (1-based indexing).
`Length`	`length()`	Exact mapping.
`LowerCase`	`lower()`	Exact mapping.
`MD5_ASCII`,`MD5_UNICODE`, `MD5_UTF8`	`md5()`	Databricks SQL does not distinguish between ASCII, Unicode, or UTF-8 variants in its hash functions. `md5()` is the single equivalent for `MD5_ASCII`, `MD5_UNICODE`, and `MD5_UTF8`.
`PadLeft`	`lpad(col, n, 'char')`	Exact mapping.
`PadRight`	`rpad(col, n, 'char'`	Exact mapping.
`REGEX_CountMatches`	`size(split(col, regex)) - 1`	No built-in counter; emulate with `split()` and `size()`.
`REGEX_Match`	`regexp_extract(col, regex, 0)`	Extracts substring matching regex; returns empty string if no match.
`REGEX_Replace`, `Replace`, `ReplaceChar`, `ReplaceFirst`	`regexp_replace(col, pattern, replacement)`	Use `regexp_replace(col, pattern, replacement)`. Databricks SQL uses one function for all regex and substring replacements; it replaces all matches by default. There is no built-in “replace only the first occurrence” variant without extra logic.
`ReverseString`	`reverse()`	Exact mapping.
`StartsWith`	`col LIKE 'pattern%'`	Works with wildcards; case-sensitive by default.
`STRCSPN`	No native equivalent.	Not implemented in Databricks SQL; would require custom UDF.
`StripQuotes`	`regexp_replace(col, '["'']', '')`	Replace quotes with empty string via regex.
`STRSPN`	No native equivalent.	Also unavailable; emulate via regex or Python.
`Substring`	`substring(col, start, length)`	Positions are 1-based.
`TitleCase`	`initcap()`	Exact mapping.
`Trim`	`trim(col)`	Use `trim()` for both sides.
`TrimLeft`	`ltrim()`	Exact mapping.
`TrimRight`	`rtrim()`	Exact mapping.
`Uppercase`	`upper()`	Exact mapping.
`UuidCreate`	`uuid()`	Exact mapping.

Test

Alteryx Function	Equivalent Databricks SQL Function	Notes
`EqualStrings`	`=`	Comparisons in SQL are case-sensitive unless you explicitly apply `LOWER()` or `UPPER()`.
`IsEmpty`	`col IS NULL OR length(col) = 0`	Exact mapping requires two checks. Alteryx `IsEmpty()` treats both `""` and `Null` as empty; in Databricks SQL, you must test both: `col IS NULL OR length(col) = 0`.
`IsInteger`	`RLIKE '^[+-]?[0-9]+$'`	There’s no direct test; use a regular expression to check if the string represents an integer.
`IsLowerCase`	`col = lower(col)`	True if all characters in the column are lowercase.
`IsNull`	`isnull`	Exact mapping.
`IsNumber`	`col RLIKE '^[+-]?([0-9]*[.])?[0-9]+$'c`	Alteryx detects numerics automatically; in SQL you can approximate it with a regular expression for numeric formats.
`IsString`	`typeof(col) = 'STRING'`	Alteryx has explicit string typing; in SQL you check the column’s data type or cast if needed.
`IsUpperCase`	`col = upper(col)`	True if all characters are uppercase.

Import overview

Alteryx to SQL

Alteryx to Spark

Migration Analysis

Conditional

Conversions

DateTime

Math

Bitwise

Range and bound functions

Operators

Specialized

String

Test

Import overview

Alteryx to SQL

Alteryx to Spark

Migration Analysis

​Conditional

​Conversions

​DateTime

​Math

​Bitwise

​Range and bound functions

​Operators

​Specialized

​String

​Test

Conditional

Conversions

DateTime

Math

Bitwise

Range and bound functions

Operators

Specialized

String

Test