Is your team preparing Snowflake deployment scripts manually?
Is your team deploying database schema change deployment scripts manually?
Are your developers uploading the manually created scripts into Github or Azure DevOps?
Is your team struggling to coordinate changes among members in the development team working in different projects from different locations?
Is your Snowflake change erroring out or creating unwanted consequences that you are struggling to manage?
If answers to any of the above questions are “Yes” then please read below to see how you could benefit from the automated solution below.
As developers develop objects in dev databases, many times not all objects are promoted to QA or Prod at the same time. On top of it, different developers work on different projects and therefore don’t move the objects to higher environments at the same time. Many times members identify the objects manually, create scripts manually, have series of review meetings among the development teams and then prepare scripts. This process is a very time taking process and introduces many errors. Because of this manual process, teams don’t move objects to production that often and hence don’t really benefit out of the CI/CD process.
To solve the above problem, unfortunately there are not many automated solutions. As an alternative to a fully automated solution, many companies adopt manual processes which are labor intensive and error prone. Few teams implement solutions like Liquibase which doesn’t make life easy either because these kinds of solutions adopt Imperative methods not Declarative Methods. Imperative methods are inherently faulty and depend on many aspects that have to be right. If the sequence of changes are not right then the tool doesn’t give you the right result. At the same time managing sequence right contradicts the very advantage the tool should provide which is teams should be able to develop independently, move changes independently and tools should take care of the right order and implementation.
The 4DAlert cloud solution delivers a web interface for developers to compare schemas between two databases(such as dev vs QA/Prod) or Development database and source control tools such as GitHub or Azure DevOps. As the solution compares schemas, it displays the difference between source and target databases for different types of objects in the Snowflake database. The interface provides an easy UI view of the differences on a real time basis.
After schema comparisons, developers could select any object and sync up the object with GitHub or Azure DevOps. When 4DAlerts syncs up, it pushes the DDL of the objects to GitHub or Azure DevOps and stores the version of the object in the source control. Because the tool follows a declarative method, it provides the full history of the object during the whole life cycle of the object.
Anyone who works in databases knows that DDLs differ when we create new tables vs when we change a table such as add column or remove column or change data type etc… Depending on what we do, the deployment script differs from CREATE TABLE vs ALTER TABLE. 4DAlert handles both the scenarios i.e. it automatically recognizes the state of the object i.e. whether its a new object or change to an existing object and creates deployment script accordingly.
In any case, the developers have two options to create deployment scripts.
a)Creates deployment script when comparing schema between two databases.
When a developer compares the schemas between two databases, the developer has the option to simply download the deployment script to his computer and execute the script manually. That itself is a big time saver and a big value add. In this process, developer doesn’t create any deployment script manually rather 4DAlert create the right deployment script.
In this option, the latest object definitions stored in the source control tool(gitHub or Azure DevOps) are used to compare with the database. At that point in time, 4DAlert considers any difference between object definition in source control and database and then prepares the deployment script. This is the beauty of the DECLARATIVE method.
DECLARATIVE methods always takes the latest object definition and compares with target database and creates deployment script(i.e. ALTER table or CREATE table…). This is the most ideal method of deploying changes. In this method, developers need not have to worry about sequence of changes, tracking all changes one by one. Rather, the tool takes care of the sequence, dependencies and makes the deployment script error prone.
While organizations have the option to deploy database changes manually, coordinate scripts by asking developers to store all scripts in a shared folder etc.., but as the complexity grows, this is not the most ideal option for any analytics team. With multiple teams working on database changes and having multiple go-live dates close to each other, manual method of comparing schema, preparing deployment script and deploying changes in target database is not an easy and error free method. Therefore, matured companies need to look at the options to automate the process end to end.
4DAlert cloud based solution automates the schema compare, database change deployment and CI/CD process end to end. Many large Snowflake customers leverage 4DAlert to automate the CI/CD process. Tool not only supports Snowflake but also most RDBMS and cloud databases such as SQL Server, Oracle, Redshift, Azure Synapse. Tool could be set up in a few hours and enables the CI/CD process end to end in a couple of days.
For details please contact 4DAlert team at support@4DAlert.com or visit www.4DAlert.com
Author — Nihar Rout, Technology Strategist and Managing Partner at 4DAlert
Keep up on our always evolving product features and technology. Enter your e-mail and subscribe to our 4DAlert.
