Using BlackDiamond Studio to Assess Snowflake Migrations

“BlackDiamond Studio (a.k.a. BDS) is the cross-language workbench for any code you have aimed at your Snowflake account.” (Mobilize.Net Corporation [that's us! we said that!], 2022).

As indicated in the illustrious quote above, BlackDiamond Studio (to be known simply as BDS for the rest of this blog post) can do a lot to help you be productive in Snowflake. But knowing that you can use BDS to manage your code working together with your Snowflake account does not mean that you need a Snowflake account to perform an assessment of your code. All you need to do is sign up for an account (which is both free and easy) and start working in BDS.

How does BlackDiamond Studio help with an assessment?

Performing an assessment in BlackDiamond Studio is the best way to assess your code before a migration. This is true for any code that you have. And the best part is, it's very simple to get started. Before we dive into the value added in BDS, let's walk through how to get an assessment in BDS. (As as a note, all of this is covered in the creating your first project section of the BlackDiamond Studio documentation.)

Setting up your assessment

First of all, BDS manages each repository as a project. So to start the assessment of your code, you can create a new project and begin by choosing the "Conversion" project workflow. Next, you will define a name for your project, and select your Git provider. You can choose to connect your own external repository. However, we recommend keeping the default selection and creating a new GitLab repository with BlackDiamond Studio.

After setting up your repository, BDS will ask you to set a Snowflake connection. This is optional to get your assessment. You can enter your Snowflake connection information or you can skip this step by choosing to "Set up connection later", and clicking "continue" to proceed with the assessment.

To being your assessment, you’ll need to click on 'Upload my source code" and upload a zipped folder with your code. This may take a few minutes. Once your code is uploaded, you’ll need to select the source platform that you want to assess. An assessment license will be assigned to this project. There are some settings you can affect, but if you're just trying out an assessment, you can click on "START CONVERSION"... and this is where the fun begins!

Running the assessment

Even though you selected start "conversion", an assessment will be run before the conversion. This assessment will begin by analyzing the code that you chose to upload your zip file. Note that this process can take a while depending on the size of your code. Once the analysis has completed, you will be able to review the results. Fortunately for all assessors, this coincides with the final step of the project setup! Click on "FINISH SETUP" to move on to the results.

Reviewing the results

Now that the assessment tool has analyzed your code and your repo has been created, you can review the results of this assessment. You can view the results in the IDE or in the repository. Let's use the repository this time. Click “Open Repository”. This will take you to the repository view that will be familiar if you've ever used GitLab or GitHub. If you click on the “Reports” folder, you will (finally!) find the results of your assessment.

Here you can find six files that summarize the assessment of your code. (Note that you may find more than these six files, but these files are key to understanding your assessment.) Here is a summary of those files:

• Assessment.YYYYMMDD.HHMISS.csv: This CSV file will store tabulated information with the details from the results of the assessment.
• Assessment.YYYYMMDD.HHMISS.html: This HTML contains a graphical representation of the results of the assessment. It is divided into sections the following sections: Next Steps, Estimated Savings and Action Items,
  Code Conversion Rate, Top Action Items, and there could be more depending on your source platform.
• AssessmentReport.YYYYMMDD.HHMISS.docx: This DOCX file is a document summarizing the technical considerations and overall code analysis in migrating SQL to Snowflake. These considerations either have an impact on the automated code conversion or cannot be handled by automated code conversion. This also contains a high-level inventory and a summary of the automation capability of the code that will need to be migrated.
• Issues.YYYYMMDD.HHMISS.csv: This CSV file contains the issues breakdown. This is one of the key artifacts in any assessment. This will break down the code, issue name, description, severity level, and where each issue was found in the code. These issues have internal codes to help identify the type of issue the analysis tool encountered. You can also see a description of each issue and the suggested troubleshooting steps suggested by Mobilize.Net.
• TopLevelObjects.YYYYMMDD.HHMISS.csv and TopLevelQueries.YYYYMMDD.HHMISS.csv: These CSV files have the inventory of objects or queries (depending on the report) found by SnowConvert as it performed its analysis. These files list the details of each object or query found by the analysis. Those files can be ingested by your preferred data analysis tools to get a more holistic analysis of the codebase.

Consider the following notes when evaluating the results of the assessment::

• You’ll find the SQL conversion rates and the lines of code are considering both identified objects and unrecognized elements to determine its value. As a result, the average for the conversion rates of each identified object in the object breakout below may differ from the overall rate given above.
• In the assessment report document (docx file), the Object Conversion Rate is considering only identified objects to determine its value. Additionally, objects with errors are not considered fully converted objects, and parsing errors that could not be attributed to an object category are shown in the “SQL – Files” table.
• If a hyphen (‘-’) is listed in the table mentioned above, it means no objects of that kind were found in the input folder. Also, if "N/A" is listed in the table above, SnowConvert is currently not converting that object type. It is likely listed for future conversion support.
• The conversion rate inside the "SQL – Identified Objects" table shows two conversion rates: one for objects and one for code.
  • The object conversion rate is calculated based on how many objects were converted without any errors no matter the severity (so, fully converted objects).
  • The code conversion rate is calculated using the overall converted lines of code for every processed object. This gives you an idea of the work needed to be done to resolve the issues once the validation of the converted code has begun.
  • It is important to note that the warnings are not considered in these rates because warnings do not produce any impact on the output converted code.

Utilizing these reports is one of the best ways to accelerate your migration to Snowflake. The repository populated by BDS contains not just your reports, but also your code. For any other change or improvement, you can keep using it as a repository. You can also use the IDE to perform as many assessments as you want! 

BlackDiamond Studio provides you all you need to store, work, asses, convert, deploy, and test your code regardless of whether you're moving to Snowflake or you've already found a home in the snow covered skies. BDS can help you fulfill the experience of moving your Data warehouse to the leading cloud data provider... Snowflake. BlackDiamond Studio is free to use, so give it a try today.