Skip to main content
The Alteryx Formula tool and the Prophecy Reformat gem both create new columns or update existing columns using custom expressions. Alteryx uses its proprietary expression language, whereas Prophecy uses SQL expressions. Prophecy focuses on SQL-native transformations; some Alteryx-specific functions may require re-expression using SQL equivalents or additional gems. See Alteryx Function mapping to Databricks SQL for a comparison of Alteryx functions and Prophecy functions.

​
Automated migration results

When you import a workflow containing an Alteryx Formula tool, Prophecy generates a Reformat gem with equivalent functionality. Prophecy attempts to translate each Alteryx expression into an equivalent SQL expression. Most common expressions, such as IF/THEN logic, concatenation, type conversion, and arithmetic, will migrate cleanly. During migration:
  • Column creation, overwrite operations, and basic functions (such as math, string, date, conditionals) are converted when an equivalent exists. (If no equivalent exists, Prophecy displays an error in the imported pipeline; you will need to rewrite logic after import.)
  • Column references and names are preserved during migration.

​
Manually replicate in Prophecy

To manually recreate an Alteryx Formula tool:
  1. Add a Reformat gem to your pipeline.
  2. Use the visual expression builder to create or modify columns using expressions.
  3. Preview results in the Data Explorer to validate output types and values. (You can also preview results using the Data button in the gem itself.)
Prophecy provides function menus, syntax highlighting, and AI assist for SQL expressions to smooth the transition from Alteryx’s formula language.

​
Configuration options

In Alteryx, configuration includes:
  • Adding columns to create or update.
  • Writing formulas in the Alteryx expression language, using IF/THEN/ELSE logic, function libraries, and specialized functions such as REGEX.
  • Choosing return types and ensuring expressions match schema requirements.
In Prophecy, configuration includes:
  • Writing SQL expressions that define the new or updated values, using built-in SQL functions for string, numeric, date, and conditional logic.
  • Viewing derived columns in the Reformat gem’s list of output fields.
  • Implementing regular expressions through SQL functions such as regexp_replace or regexp_extract.
The configuration goal is the same, but the expression syntax follows SQL rules instead of Alteryx’s proprietary ones.

​
Output behavior

The Alteryx Formula tool outputs transformed records with new or modified fields, executing operations row by row within Designer or on an Alteryx Server. The Prophecy Reformat gem outputs a dataset where transformations are applied using SQL expressions at execution time. Values are recalculated by the SQL Warehouse as part of the SELECT clause generated by the gem.

​
Known caveats

  • Alteryx REGEX functions may not be mapped directly; SQL regex equivalents may require rewritten patterns.
  • Conditional logic is written using SQL CASE WHEN rather than Alteryx’s IF/THEN.
  • Alteryx DateTime functions may require translation to SQL-standard date functions.
  • Some Alteryx-specific functions lack direct SQL counterparts, and their logic may need to be rewritten after import.
  • Alteryx often auto-casts between types; in Prophecy, some expressions may require explicit casts.
  • Multi-field formulas in Alteryx may require multiple expressions or gems in Prophecy.
  • Complex nested conditional expressions may require manual review.

​
Examples

​
Simple Alteryx Formula tool example

Goal: Create a full_name column by concatenating first and last names, and assign “Unknown” when values are null. Alteryx formula: IF IsNull([FirstName]) OR IsNull([LastName]) THEN "Unknown" ELSE [FirstName] + " " + [LastName] ENDIF

​
Simple Prophecy Reformat gem example

Goal: Perform the same transformation in Prophecy.
  1. In the Reformat gem, define a new column: CASE WHEN first_name IS NULL OR last_name IS NULL THEN 'Unknown' ELSE first_name || ' ' || last_name END
  2. Add input columns to Target Column so that these appear in the output.
  3. Preview in the Data Explorer to validate the new full_name field.

​
More complex Alteryx Formula tool example

Goal: Compute a debt-to-income ratio (dti_ratio) and assign a simple risk label (risk_band). Alteryx formulas: 
IF IsNull([MonthlyIncome]) OR [MonthlyIncome] = 0 THEN Null() ELSE [MonthlyDebt] / [MonthlyIncome] ENDIF
IF IsNull([MonthlyIncome]) OR [MonthlyIncome] = 0 THEN "Unknown" ELSEIF ([MonthlyDebt] / [MonthlyIncome]) > 0.5 THEN "High" ELSE "Low" ENDIF

​
More complex Prophecy Reformat gem example

Goal: Perform the same transformations in Prophecy. In the Reformat gem, define the new columns: 
CASE WHEN monthly_income IS NULL OR monthly_income = 0 THEN NULL ELSE monthly_debt / monthly_income END AS dti_ratio
CASE WHEN monthly_income IS NULL OR monthly_income = 0 THEN 'Unknown' WHEN (monthly_debt / monthly_income) > 0.5 THEN 'High' ELSE 'Low' END AS risk_band
Preview the results in the Data Explorer to validate both new fields.