Hello, and welcome to the latest installment of the phData Toolkit blog series. Between the festivities of the 4th of July and all the wonderful Toolkit updates we’ve made, this month has truly been a cause for celebration. We hope you had a great time being with family and friends.
Over the course of the last month, our product engineering team has been hard at work driving additional capabilities and features for our customers and delivery teams. We’ve been focusing on two key areas: Microsoft SQL Server to Snowflake Data Cloud SQL translations and our new Advisor tool within the phData Toolkit.
Let’s dive in.
Advisor Tool Updates
We’ve introduced a new tool into the phData Toolkit: the Advisor Tool. One of the most common jobs for any consulting company is to provide recommendations and industry best practices for customers. We are expected to be the experts in the services we provide, specifically with the products we leverage as part of our services. For phData, that product is most commonly Snowflake.
With the Advisor tool, you can quickly and easily identify opportunities to improve the configuration, security, performance, and efficiency of your Snowflake environment. We have worked closely with our Snowflake data and operations experts to ensure that our Snowflake Advisor delivers actionable recommendations based on best practices and phData’s vast real-world experience on the Snowflake Platform.
The Advisor tool comes with a pre-configured set of checks to identify opportunities and provide recommendations that ensure your Snowflake environment is following best practices and industry standards across a variety of categories, including:
Configuration Best Practices
Recommendations for your Snowflake environment configuration based on best practices and industry standards.
Security Enhancements
Identify and fix potential security risks in your account and maintain a compliant security posture going forward.
Operational Risks
identify operational risks such as data loss or failures in the event of an unforeseen outage or disaster.
Performance Optimization
identify and fix bottlenecks in your data pipelines so that you can get the most out of your Snowflake investment.
Resource Utilization
Optimize the use of resources, such as computing and storage, to ensure that you are not overpaying for resources you don’t need.
Environment Scale and Capacity
View critical metrics about the scale and features used in your account to help you understand your current state and plan for growth.
We provide a set of out-of-the-box checks along with the ability to customize any additional checks that your organization wants to perform. These results are surfaced through a user interface that contains rich interactive reports for consumers to drill into the various checks and determine how they want to address them.
We also provide the ability to run these checks on a schedule and alert if any checks fail, empowering our customers to operate their platform with confidence.
Interested in trying the advisor tool?
Check out our getting started page!
SQL Translation Updates
When customers are looking to migrate between platforms, there’s always a challenge in migrating existing code. Frequently, customers have hundreds to thousands of SQL files that need to be translated between different SQL dialects.
This is where the programmatic approach of our SQL translation tool is key in accelerating your speed-to-market and delivering business value on your new platform as-soon-as-possible.
As with any translation, there are source and target languages. Over the course of the last few months, we’ve been heavily focused on Microsoft SQL Server to Snowflake translations. This includes all kinds of things, from basic select statements all the way to database-specific operations like transactions, system functions, and permissions.
We’ve introduced a number of updates for things like features and bug fixes, but this month we’re going to focus on a few more interesting translations.
Grant Statements in Stored Procedures
One of the many features within most SQL databases and warehouses is stored procedures. These are essentially functions that are stored within a database that can take arguments, perform operations, and return values. These can widely vary between systems regarding what information is required, what operations are possible, and how data is returned.
Within SQL Server, for example, we aren’t required to define a return type for a procedure, but in Snowflake, we are. Therefore we have to make some assumptions and provide warnings when performing this translation, as shown below. We also have situations where features are not supported.
For example, execute is not a permission grant available on a schema or stored procedure. This would have to be translated into USAGE for these types of objects. While this will be supported eventually in our translation, you can see we provide an error saying this feature is not currently supported.
CREATE PROCEDURE AdventureWorks2012( @deptId VARCHAR(40) )
AS
BEGIN
GRANT EXECUTE ON OBJECT::HumanResources.uspUpdateEmployeeHireInfo
TO Recruiting11;
GRANT EXECUTE ON SCHEMA::HumanResources
TO Recruiting11;
END;
BEGIN TRANSACTION ;
-- WARNING: line 1, character 0: Syntax difference: STRING is added as a default return type by SQL Translation, as Snowflake expects a RETURN TYPE for PROCEDURES.
CREATE OR REPLACE PROCEDURE AdventureWorks2012(deptId VARCHAR(40))
RETURNS STRING
LANGUAGE SQL
as
$$
BEGIN
-- ERROR: line 0, character 0: Feature not supported: EXECUTE privilege in GRANT statement.
GRANT EXECUTE ON OBJECT HumanResources.uspUpdateEmployeeHireInfo TO Recruiting11;
-- ERROR: line 0, character 0: Feature not supported: EXECUTE privilege in GRANT statement.
GRANT EXECUTE ON SCHEMA HumanResources TO Recruiting11;
END;
$$
;
However, this presents an important value-add of programmatic SQL translation: knowing all the differences between SQL dialects is very challenging and error-prone when converting a significant number of SQL statements. Each database/warehouse has a significant number of features and different approaches to permissions, and mapping all of that out takes a lot of work.
System Functions
While some translations and system functions are complicated to map out, there are situations where functionality is easier to understand between systems. Let’s take a look at a much simpler example:
SELECT SUSER_NAME();
SELECT CURRENT_USER();
In this example, we’re trying to get the username of the user that’s executing the query. This is frequently used when trying to apply permissions for a row or column-level security or in situations where you want to track which user applied an update to the table for auditing purposes.
If you’d like to see a full list of updates, check out our change log!
Wrapping Up
We hope you’ve gained some additional insight into our phData Toolkit and the new functionality that we’ve added to the platform. As we identify common use cases and needs within our customers, we will continue to provide new/additional tooling to accelerate projects and help customers build reliable and observable systems.
If you’re interested in learning more about our Toolkit or getting a demo around the functionality and how it could help your organization, give us a shout!
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.
