> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL dialect differences

> Behavioral differences between Databricks SQL, BigQuery, and Snowflake when importing Alteryx workflows into Prophecy

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:

````sql theme={null}
catalog.schema.table
~~~

BigQuery references tables using:

```sql
project.dataset.table
````

Snowflake typically references tables using:

```sql theme={null}
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](https://docs.snowflake.com/en/sql-reference/identifiers?utm_source=chatgpt.com))

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](https://docs.snowflake.com/en/sql-reference/functions/array_position?utm_source=chatgpt.com))

## 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](https://docs.snowflake.com/en/sql-reference/functions/regexp?utm_source=chatgpt.com))

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](https://docs.snowflake.com/en/sql-reference/functions/try_cast?utm_source=chatgpt.com))

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.

## Related pages

For more detailed information, see:

* [Function mapping](/import-tool/alteryx/alteryx-to-prophecy-sql/function-mapping)
* [Data type mapping](/import-tool/alteryx/alteryx-to-prophecy-sql/data-type-mapping)
* [Connection type mapping](/import-tool/alteryx/alteryx-to-prophecy-sql/alteryx-connections)
* [Tool mapping](/import-tool/alteryx/alteryx-to-prophecy-sql/tool-mapping/alteryx-tool-mapping)