November 3, 2023

phData Toolkit October 2023 Update

By Nick Goble

Hello, and welcome to the October update of our phData Toolkit series!  Halloween is right around the corner, and we’re excited to see what costumes our friends, families, and trick-or-treaters come up with!

Our dedicated team has been hard at work to enhance your data management and integration experience. In this update, we’ve addressed various issues, introduced new features, and made improvements across different components. 

Let’s dive into what’s new!

SQL Translation Updates

Are you tired of spending endless hours manually converting SQL queries between different dialects? Do you want to ensure accuracy and save valuable time while working with complex translations? 

Look no further! Our SQL translation application is your answer. This revolutionary tool acts like a “Google Translate” for SQL dialects, making it a game-changer for database professionals and learners alike.

Over the course of the last month, we’ve made a number of updates to the platform to provide enhancements and fixes to a variety of translation pairings.

Translating From MSSQL to Snowflake


  • We’ve resolved the issue with the INTERSECT operator when used inside an EXISTS clause.

  • Parsing problems with GEOGRAPHY static methods are now fixed.


					CREATE TABLE table1(
col1 decimal(9, 6) NULL,
col2 decimal(9, 6) NULL,
col3  AS (geography::STPointFromText(((('POINT('+CONVERT(varchar(20),col2,(0)))+' ')+CONVERT(varchar(20),col1,(0)))+')',(4326)))
					CREATE TABLE table1 (
  col1 DECIMAL(9, 6),
  col2 DECIMAL(9, 6),
  -- ERROR: line 4, character 10: Function return value incompatibility: Computed column for GEOGRAPHY data type.
          'POINT(' || -- WARNING: line 4, character 49: Function argument incompatibility: STYLE argument for CONVERT function.
          LEFT(CAST(col2 AS VARCHAR), 20)
        ) || ' '
      ) || -- WARNING: line 4, character 85: Function argument incompatibility: STYLE argument for CONVERT function.
      LEFT(CAST(col1 AS VARCHAR), 20)
    ) || ')'

Translating From Oracle to Snowflake

New Features:

  • You can now translate CURSOR statements in Snowflake scripting.


  • We’ve addressed parsing issues within the CREATE TABLE statement for external table loading.

  • Our tool now handles ALTER MATERIALIZED VIEW and DROP MATERIALIZED VIEW statements.

  • Parsing issues related to function column types in the CREATE TABLE statement have been resolved.

  • You’ll no longer encounter issues with the OPAQUE clause in CREATE TABLE statements.

  • Parsing problems with the DATABASE LINK clause in SELECT TABLE statements are history.

  • We’ve fixed parsing issues with nested joins.

  • The FORALL statement can now be translated accurately, and variable declarations in Snowflake Scripting are handled correctly.

  • Parsing problems with PARTITION in CREATE TABLE statements are resolved.



-- ERROR: line 5, character 1: Feature not supported: EXTERNAL TABLE LOADING.


Translating From Teradata to Snowflake

New Features:

  • Our tool can now translate the GRANT statement.

  • The COALESCE function and INTERVAL data types are now supported for smoother translations.

In summary, these updates to our SQL Translation tool offer enhanced accuracy, efficiency, and versatility when converting SQL queries between different dialects. By addressing specific issues and introducing new features, we’ve streamlined the process, reduced the risk of errors, and added valuable functionality. 

This ultimately saves you time, minimizes the potential for translation mishaps, and empowers you to handle complex SQL translations with ease.

Toolkit CLI Updates

In our ongoing commitment to enhancing the functionality and reliability of our toolkit, we’re thrilled to introduce a host of exciting updates. We’ve implemented improved error handling for post-trigger provisioning operations, ensuring smoother and more error-resistant processes. 

Additionally, we’ve added support for new password policy properties, PASSWORD_MIN_AGE_DAYS, and PASSWORD_HISTORY, giving you more control over your security policies.

For those who enjoy a seamless user experience, we’ve included a confirmation prompt when running toolkit provision init with the --metadata flag in an interactive shell, providing you with additional initialization details.

To streamline your operations, we’ve made an important change in the default ownership grant behavior. It’s now set to COPY CURRENT GRANTS, which is a more reliable default option, especially when compared to the previous settings.

Our commitment to robustness continues with the fix for reader account provisioning, ensuring the successful migration of reader account metadata schemas in plan mode. We’ve also addressed the issues that occasionally arose with the new style account locator URL, making reader account provisioning a smoother process.

In response to user feedback, we’ve updated resource parameter delta calculations to consider the parameter level as well as its value, enhancing precision and customization. 

Additionally, for those who need to execute SQL scripts as part of provisioning, we’ve introduced preSql and postSql settings, allowing you to define paths to directories containing SQL files. preSql runs prior to provisioning, and postSql executes after successful provisioning, providing you with more flexibility in your workflow.

Lastly, for our Business Critical Edition Snowflake account users, we’ve added support for the share_restrictions parameter on Shares. This feature allows you to share data from a Business Critical account to a non-business-critical account.  For more information on this, check out the documentation.

The Journey Continues

Our recent round of updates and enhancements to our toolkit reflects our unwavering commitment to providing you with a top-notch, user-friendly experience. We’ve focused on refining the key aspects of error handling, security, and customization to ensure that your operations are not only smoother but also more secure and versatile. 

From improved error handling for post-trigger provisioning operations to the addition of new password policy properties and the introduction of confirmation prompts, these updates aim to streamline your workflow and enhance the reliability of your processes.

We’ve also been attentive to user feedback, resulting in changes such as the default ownership grant behavior and fixes for reader account provisioning. Our updates cater to both precision and flexibility by refining resource parameter delta calculations and introducing preSql and postSql settings for executing SQL scripts.

These improvements are the result of our continuous efforts to deliver a toolkit that empowers you to work more efficiently and with fewer errors. Your feedback has been invaluable in guiding our development, and we look forward to further refining our toolkit to meet your evolving needs. 

Thank you for choosing our toolkit, and we’re excited to continue working together towards even greater success in your data endeavors.

phData Toolkit

If you haven’t already explored the phData Toolkit, we highly recommend checking it out!

We encourage you to spend a few minutes browsing the apps and tools available in the phData Toolkit today to set yourself up for success in 2023. 

Be sure to follow this series for more updates on the phData Toolkit tools and features. 

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit