Delta model and ALTER script

The feature to create an ALTER script from the differences between 2 Hackolade data models is only available for the technologies that allow such syntax and that we support, currently: BigQuery, Cassandra and DataStax, Databricks and Delta Lake, Hive, MariaDB and SkySQL, MySQL, Oracle and Autonomous Database, PostgreSQL and EnterpriseDB, Amazon RDS, Redshift, SQL Server and Azure SQL, Google Cloud SQL, ScyllaDB, Snowflake, and Synapse.

 

A delta model highlighting the differences between 2 data models in a structured manner can also be useful for other targets, even if the technology does not support ALTER statements.  It remains a machine-readable comparison of the 2 models.

 

The generation of an ALTER script is a 2-step process: first you initiate the comparison of 2 models to generate a "delta" model from the detected differences, then you forward-engineer the ALTER script from the delta model.  These operations are possible either from the GUI application, or from the Command Line, but only with the Professional Edition or the Workgroup Edition.

 

When comparing 2 models, one of the models is always the baseline with which the second one is being compared.  But there are different use cases for the origin of the second model.  The second model could be an evolution of the first one.  Or the second model could be issued from the reverse-engineering of a production instance for example.  There's an important nuance between these 2 approaches: when the second model is an evolution of the first one, the internal identifiers (automatically generated by the application) remain identical and provide a very reliable basis for the comparison.  Whereas when the second model was issued from a reverse-engineering process, then the internal identifiers are different, meaning that the comparison can only be done based on object names.  You may change the matching method from the default of internal to IDs, to business names or technical names.

 

VERY IMPORTANT: In ALTER scripts, the DROP command may appear if a database, schema, table, column, view, index, etc is deleted or renamed.  As this command can potentially result in loss of data, it is your responsibility as a user to decide whether such script command should be applied to the database.  By default, when Hackolade generates DROP commands in ALTER scripts, we comment the DROP statements.  As a user, you have to explicitly un-comment these lines, which signifies that you understand the consequences of applying such statements.

 

Generate delta model

There are 2 ways to generate a delta model: GUI or CLI.

 

Using the GUI application Compare & Merge functionality, you may review the differences in the side by side panel, then generate a delta model file.  Options in the left panel let you adjust the display filtering, the matching method (internal IDs, business names or technical names), and the choice to ignore negligible properties (schemaId, description, remarks, comments, default, dependencies, sample, examples.)

 

Important: the order of the Left and Right models is critical, as it determines whether differences become adds or deletes, and hence the resulting ALTER script.  The convention used is that the Left model is the basis for the comparison, and the Right model is the evolution.  An object present in the Left model and not the Right model will result in a DROP statement.  Conversely, an object in the Right model that is not present in the left model will result in a ADD statement.  The models can easily be swapped.

 

Important: Typically, models are compared using the internal Hackolade ID for the objects in the model.  That works well if both models have evolved from the same Hackolade base data model.  However, if you compare a reference Hackolade model with a new model derived from the reverse-engineering of a database instance for example, then this new model has new and different internal GUID for the objects.  As a result, the compare method must use object names matching, ideally technical names, otherwise business names.  The application attempts to automatically detect such situation.  You may easily change the matching method.

 

 

 

The delta model looks like any other model, but it is filled with objects that have been calculated during the comparison.  It helps visualize, for each level of objects, the differences between the Left and the Right model, using the Left model as a basis.

 

 

Using the Command Line Interface compMod command, you can compare, on a schedule, event, or trigger, two Hackolade models to detect differences, and optionally merge them.  Given identical parameters, the GUI and CLI generate identical delta models.

 

 

Forward-Engineer ALTER script

The generation of the ALTER script is a separate step which provides different results depending on the target and syntax.  For example, Cassandra does not support NOT NULL, so a change in this property will not result in any statement in the ALTER script.

 

Again here, you can generate the ALTER script either via the GUI or the CLI.

 

Using the the GUI application, you may review the ALTER script on screen first, then either apply it to your database instance from the lower script tab, or use the menu Tools > Forward-Engineer > Alter Script to output a file on your local file system.

 

As noted earlier, all DROP statements are commented by default, as they can potentially result in loss of data.  It is your responsibility as a user to decide whether such script commands should be applied to the database.  As a user, you have to explicitly un-comment these lines, which signifies that you understand the consequences of applying such statements.

 

 

Using the Command Line Interface forwEng command, you can generate, on a schedule, event, or trigger, the ALTER script to be stored in your repository or integrate in your DevOps CI/CD pipeline.