Business Use case

A large Electronic manufacturing company with an annual USD 35 Billion revenue has approximately 3,000 database objects in its Snowflake platform. The company has data analytics development teams working on various business priorities from multiple locations in an agile fashion. Based on different business priorities, and other dependencies, the development team moves objects to higher landscape databases such as development to QA and QA to production at different times. In the absence of an automated schema comparison and CI/CD process production deployment process is manual. Members of the development team select individual object manually, prepares DDL(Ex CREATE and ALTER) scripts manually, and add the script to a text file. Manually preparing the deployment script is labor-intensive. This manual deployment script is then uploaded to a DevOps folder or share folder or sent to the database admin team for manual execution. Seems like a process of the 1950’s. The process is Manual…. Cumbersome… Error-prone.

How did the company improve the process?

To automate the CI/CD, schema compare, and database change deployment, the company looked for various options. Finally adopted the 4DAlert solution to automate and optimize the process. Let’s see how 4DAlert helped achieve the goal.

Schema Compare:

When it comes to Schema Compare, we think about two methods: a) Imperative and b) Declarative. Imperative and declarative schemas differ in how they describe what a program should do:

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.

Data Observability Frameworks

 

 

4DAlert solution focuses on declarative schema comparison. In this method, you just move the final state of the schema and the tool would automatically do the job for you. The only thing that gets checked into the source control is your final schema of how you want the objects to look.  4DAlert solution compares your to-be schema with the as-is schema and automatically creates the ALTER scripts. The solution doesn’t stop there. 4DAlert’s own API integrates with GitHub or DevOps actions and deploys the changes in the target schema. That is how the 4DAlert solution helped the company with 3,000 objects in the Snowflake platform to automate CI/CD, Schema compare, and Automatic Change deployment.

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