Learn How a Large Electronic Manufacturing Company with More Than $35 Billion in Annual Revenue is Automating CI/CD, Schema Compare, Database Change Deployment in Its Snowflake Platform.

Business Use case 

 

 

How did the company improve the process?

 

 

 

 

Schema Compare:

 

 

a) Imperative Method

 

In this method, we describe how the program should do something by explicitly specifying each instruction. For example, imperative programming might say “Marinate the chicken, put them into the oven, turn the oven to 400 degrees”.

 

b) Declarative Method

 

In this method, we describe what the program should do, but don’t specify the control flow. For example, a declarative language might say “Prepare me chicken for dinner”.

Imperative-based schema comparison is prevalent and there are various ways people adopt this, but the Imperative method is complex and prone to more errors. There should be a better way to do schema comparison. That’s where declarative schema comparison comes into the picture.

 

 

 

 

1. Database agnostic — Solution integrates with most databases

 

4DAlert automated solution is database agnostic. It seamlessly connects with Snowflake, Redshift, Synapse, Postgres, Oracle, SQL Server, Postgres, etc., and tracks the database changes for all databases. This company had a large Snowflake implementation with approx. 5,000 snowflake objects.

 

 

2. Intuitive web-based UI helps automate schema compare:

 

4DAlert provides an intuitive Web-based User interface that connects to both cloud and on-prem databases and various types of source control tools to enable the team to compare schema and show the differences automatically. Schema comparison takes into almost all types of objects that are relevant to the technology (for example for Snowflake technologies the solution includes below objects.

 

 

3. Special Scenarios and Complex Schema Changes:

 

In most complex analytics platforms, there would be always special schema change scenarios. Certain schema changes require data manipulation and data to be dropped and re-inserted. 4DAlert gives multiple options to manage those scenarios. In every case, the solution provides clear warnings and choices/options to users on what they would prefer. In NO cases the solution DROPs data unless used specifically tells the system to drop the data.
For example, if we reduce the width of a column that is of data type STRING from width 40 to 20, it would generate a warning to update the data outside the script and then issue the ALTER statement. Similarly, if we rename the column then before issuing the DROP column statement, it would show a clear warning for the DROP statement and user acknowledgment and the issue of the ALTER statement. At no point, would the solution drop any data from any table without user acknowledgment and consent.

 

4. Integration with DevOps, GitHub, and Other Source control tools:

 

As part of the CI/CD process, one of the key requirements was that the company should be able to compare schema between the database and source control(which could be either DevOps or GitHub or Bitbuckets or something similar). The 4DAlert solution addressed the need very well. Based on database types, the solution supports most object types for the relevant database technology (such as Snowflake Redshift or Synapse) and allows the development team to push the changes to source control. In this case the solution compared Snowflake and DevOps. When changes are pushed, source control always keeps the most recent DDL definition of the object as the default version. If anyone wants to see the changed history of the object, then you could get that form object history which would show the snapshot of the object at any point in time in the history

 

The above functionality helped the company on various fronts.
a) The team was able to leverage the DevOps change history for each object and see the different changes moved at various points in time.
b)  At any point the team wanted to rollback, they were able to go back to a point in time
c) If any of the team wanted to replicate the object using DDL scripts, those scripts were readily available.

 

5. Generate deployment script automatically (CREATE vs ALTER Script):

 

Many times we want to keep a version of the objects in source control with the structure at the point in time. After schema comparison, 4DAlert saves these CREATE statements in the source control tool of your choice. Then when you merge the code with your target branch, 4DAlert’s API automatically creates the ALTER script which eventually gets executed in the database automatically. In this process, there is no need to create pointers or markers. The tool would automatically create the ALTER statement based on the source and target comparison. This process saves a lot of time among developers, creates error-free deployment, and automates the deployment process end to end. This was a huge help for the team in overall productivity improvement, efficiency, and error-free deployment.

 

6. Automatic Code review before code deployment:

 

Many times organizations have their own coding standard and naming conventions. Without any automatic tools, the code review process is manual and labor-intensive. 4DAlert solution lets you define your coding standards and naming conventions. Based on this setup, 4dAlert’s APIs check the objects and highlight any object that doesn’t follow a predefined naming convention.

 

7. SOX Compliance with Database comparison history and deployment log:

 

Many times companies have a requirement to produce any database deployment, changes, and structure for SOX audit purposes. 4DAlert helps you in the process

 

8. Additional features – Data Quality check and Data Reconciliation:

 

Along with Database schema compare CI/CD, and Automatics change deployment, 4DAlert also integrates Data reconciliation features for any database before and after change. I will cover that in a subsequent blog.

 

The infrastructure required to run 4DAlert

 

You do not need any special hardware to run 4DAlert. Isn’t that great? A small Virtual machine with 4 or 8 CPUs that has either Windows or Linux operating system is enough to support a large Analytics environment. The solution could run either on-prem or in the cloud and connect to both on-premise and cloud databases. The solution also could be containerized and deployed to a Kubernetes environment.
In Summary, the company with more than 5,000 objects in the Snowflake platform, multiple development teams working from multiple locations, and business users with multiple priorities was able to automate the CI/CD processes, standardize Schema comparisons, and automate database change deployment within the analytics landscape. 4DAlert was able to save effort in database change management by 80%, reduce human error by 95%, and bring operational efficiency to the analytics development team across the organization.

—————————————————————————–
This post was written by Nihar Rout, Managing Partner, and Chief Technology Officer@ 4DAlert.

Want to try schema compare features that will help you continuously deploy changes with no error? Request a demo with one of our experts at https://www.4dalert.com 

Like this article?

Share on Facebook
Share on Twitter
Share on Linkdin
Share on Pinterest