Additional Translation Rules

This page describes how SnowConvert functionality can be extended via loading additional translation rules.

What are they?

SnowConvert goes through several steps in order to complete the conversion process. Generally speaking, the steps are the following:

  1. Parsing, where the input code is interpreted and ASTs (abstract syntax trees) representing that source code are created

  2. Translation, where the original ASTs are transformed, replaced, or modified into Snowflake equivalent ASTs

  3. Pretty printing, where the resulting ASTs are printed to files following certain indentation rules

The second step of the process is achieved via translation rules. In general, these rules are in charge of defining how each element in the input source code must be translated to make it as functionally equivalent as possible in Snowflake. A translation rule takes one kind of AST as input, performs some operations over it to make it compatible with Snowflake, and returns this resulting AST. To better illustrate this, let's talk about mapping Transact SQL built-in functions that have a direct Snowflake equivalent; by direct we mean that they perform the same operation taking the same number and type of arguments, but they might have a different name. For example, the ceiling function in Transact SQL maps to the ceil Snowflake function. A translation rule for this process would take the AST used to represent function calls, named SqlFunctionExpr as input, verify that the function name is one of these equivalent functions, and return an AST of the same type, with the same arguments but with the corresponding name in Snowflake.

Translation rules that are already part of the code of SnowConvert are considered core. We have enabled an extensibility mechanism in which developers can write their own translation rules and load them at runtime, these are called additional translation rules.

Use cases

Adding support for not (yet) translated elements

Creating an automatic translator from multiple SQL dialects (Teradata, Oracle, SQL Server, etc.) to Snowflake is no easy task and, even though Snowflake developers are working hard to make SnowConvert as complete as possible, there are still pending items to be implemented. As mentioned above, the translation phase needs the ASTs that the parser creates, in order to transform them into Snowflake equivalent ASTs. When adding support for a new SQL dialect into SnowConvert, it is typically the case that a whole syntax, like the CREATE TABLE syntax, is to be recognized (parsed) correctly before writing any translation rule for it. One of the multiple benefits of writing additional translation rules is that SnowConvert users (with some programming knowledge) can add support for elements that are currently parsed correctly but are not yet covered by core translation rules.

Customizing translation for specific scenarios

By taking advantage of additional translation rules, the user can personalize the migration of certain elements in their code. Core translation rules are made to consider all possibilities allowed by the grammar of the input platform, but additional translation rules can be created to handle only the existent cases in the code being migrated. Since additional translation rules have precedence over core rules, that means that the translation for certain elements can be tuned to match particular requirements specific to the workload at hand.

One use case of additional translation rules can be removing certain warnings in the generated code. Sometimes inserted warnings serve only for guidance in future development in Snowflake, but they do not represent an issue in the migrated code. The client might be aware, e.g. that the use of a certain function in their source code is bound only to regular use cases, and therefore there is no need for warnings that are added by SnowConvert only to handle extreme situations; that level of customization can be written in an additional translation rule.

Warnings are usually inserted in the output code because SnowConvert detects that the translation for a given element represents a difference in functionality between the input platform and Snowflake. Even though we would like to limit the presence of warnings to only the places where they can represent an issue, SnowConvert is a Static Code Analysis tool, much like any compiler/translator system. This implies that the amount of information it gathers is limited to what can be obtained from the input source code without executing it. Sadly, that means that sometimes there is no way to know if the translation of a given element in the input source code is going to be functionally different in Snowflake, so a warning is inserted.

Also, there might be multiple ways to successfully translate a certain element, and the user might prefer a different approach to the one written on SnowConvert. Having the possibility to write extra translation rules, gives users the chance to further personalize their migration.

Usage Warning

As mentioned before, additional translation rules have precedence over core translation rules and that means whatever code is written on them will be executed by SnowConvert. This is a double-edged sword, while it provides users the ability to personalize the translation of certain elements, it also enables the possibility that the written code is somewhat faulty and the final behavior of SnowConvert is not the expected one.

Snowflake engineers are always welcome to answer questions regarding this or any other subject, you can email us at snowconvert-support@snowflake.com.

How to use them?

SnowConvert is able to load additional translation rules that have been already compiled into a .dll file. SnowConvert only needs to know the folder where the assemblies are located. In the GUI, this is specified in the following screen.

And that's it. SnowConvert will automatically look for .dll files in the folder and load them at runtime.

Navigate through the following pages, where we go into more technical details about how to write additional translation rules.

Last updated