SQL dialect differences - Prophecy Documentation

When you import Alteryx workflows into Prophecy, the workflow logic is translated into SQL and executed on the target engine configured for your environment. The resulting queries reflect the original transformations but follow the syntax and behavior of the selected platform, such as Databricks, BigQuery, or Snowflake. Differences in SQL dialects can affect how queries behave, how objects are referenced, and which SQL functions are generated. This page highlights key differences between supported SQL engines so you can better understand how imported workflows behave after migration.

At a glance

Area	Databricks SQL	BigQuery	Snowflake
Object naming	`catalog.schema.table`	`project.dataset.table`	`database.schema.table`
SQL dialect	Based on Spark SQL	GoogleSQL	Snowflake SQL
Data types	Spark-compatible types	GoogleSQL types	Snowflake types
Query execution	Runs on Databricks compute clusters or SQL warehouses	Runs on BigQuery managed execution	Runs on Snowflake virtual warehouses
Array indexing	1-based	0-based using `offset`	0-based
Regex engine	Java regex	RE2	POSIX extended regular expressions
Casting behavior	`cast`, with some function-specific workarounds	`cast` and `safe_cast`	`cast`, `try_cast`, and other `try_` conversion functions
Function naming	Examples: `substring`, `locate`, `date_format`	Examples: `substr`, `strpos`, `format_datetime`	Examples: `substr`, `position`, `to_char`

Naming and object references

Different engines organize database objects differently. Databricks SQL typically references tables using:

catalog.schema.table
~~~

BigQuery references tables using:

```sql
project.dataset.table

Snowflake typically references tables using:

database.schema.table

When migrating workflows, make sure the corresponding catalog, project, database, schema, or dataset exists in the target environment. Snowflake also has specific identifier behavior, including rules for quoted and unquoted identifiers. (Snowflake Docs) For details on configuring source and target connections, see Connection type mapping.

Functions and generated SQL

Most imported workflows keep the same pipeline structure across engines, but generated SQL functions can differ. Common differences include:

Date and timestamp parsing or formatting functions.
String and regex functions.
Safe casting functions.
Array access and positional logic.

For example, formatting a timestamp as a string may use different functions depending on the target engine:

Engine	Example function
Databricks SQL	`date_format`
BigQuery	`format_datetime` or `format_timestamp`
Snowflake	`to_char`

See Function mapping for detailed function-level mappings.

Array indexing and positional logic

Array indexing differs across engines:

Engine	Array indexing
Databricks SQL	1-based
BigQuery	0-based using `offset`
Snowflake	0-based

This matters for imported Alteryx logic that retrieves values by position, such as GetVal, GetPart, GetWord, or TOPNIDX. Snowflake array position functions return 0-based positions. (Snowflake Docs)

Regex behavior

Regex support differs by engine:

Engine	Regex behavior
Databricks SQL	Java regex
BigQuery	RE2
Snowflake	POSIX extended regular expressions

These differences can affect imported workflows that use the Alteryx RegEx tool or regex-based string functions. Snowflake regex comparison functions use POSIX extended regular expressions rather than SQL LIKE pattern syntax. (Snowflake Docs) If a workflow relies on advanced regex features, validate the generated SQL in the target engine.

Type coercion and casting

All supported engines support explicit casting, but error handling and supported conversions differ.

Engine	Casting behavior
Databricks SQL	Uses `cast`; some Alteryx conversions require function-specific workarounds.
BigQuery	Supports `cast` and `safe_cast`; `safe_cast` returns `null` instead of failing.
Snowflake	Supports `cast`, `try_cast`, and other error-handling conversion functions.

Snowflake try_cast returns null instead of raising an error when a supported conversion fails. (Snowflake Docs) See Data type mapping for details on how Alteryx data types translate into each SQL dialect.

Date and time behavior

Date and timestamp handling may vary across SQL engines. Potential differences include:

Default timezone behavior.
Timestamp precision.
Timestamp type variants.
Function names and argument order.
Format pattern syntax.

For workflows that perform extensive date manipulation, verify that the generated SQL behaves as expected in the target engine. See Function mapping for datetime function mappings.

NULL handling and comparisons

SQL engines generally use standard SQL null semantics, but behavior can still differ in specific expressions or generated functions. Examples to watch for include:

Ordering behavior for null values in order by.
Conditional expressions involving null.
Safe casting functions that return null instead of errors.
Functions that ignore null values by default, such as aggregates.

When preserving Alteryx behavior is important, validate workflows that rely heavily on missing-value handling.

Table creation and write behavior

Imported workflows may create or overwrite tables depending on Target gem configuration. Table creation behavior can differ by engine:

Engine	Common considerations
Databricks SQL	Supports `create or replace table`; object references may include catalog and schema.
BigQuery	Requires project and dataset context; datasets may need to exist before writes.
Snowflake	Requires database and schema context; execution also depends on the active warehouse and permissions.

For detailed behavior, see the relevant Source and Target gem documentation and connection mapping pages.

Performance and cost considerations

Execution models differ across engines, which can affect runtime performance and cost.

Engine	Execution model
Databricks	Cluster or SQL warehouse based execution. Performance depends on compute configuration.
BigQuery	Managed execution environment. Cost is commonly driven by data scanned and query usage.
Snowflake	Virtual warehouse based execution. Cost and performance depend on warehouse size, runtime, and scaling configuration.

These differences do not change the logical structure of your imported pipeline, but they may influence how you optimize workloads in production.

What remains consistent

Despite dialect differences, most aspects of imported workflows remain the same:

Tool mappings from Alteryx to Prophecy gems remain largely consistent across engines.
Pipeline structure and transformations remain unchanged.
The Prophecy authoring experience is the same regardless of execution engine.

In most cases, you can import an Alteryx workflow and run it on the selected SQL engine with minimal structural changes. Engine-specific differences usually appear in generated SQL expressions, object references, data types, or runtime behavior. For more detailed information, see:

​At a glance

​Naming and object references

​Functions and generated SQL

​Array indexing and positional logic

​Regex behavior

​Type coercion and casting

​Date and time behavior

​NULL handling and comparisons

​Table creation and write behavior

​Performance and cost considerations

​What remains consistent

​Related pages