Database Compare

This page shows the Test Automation Data Activity for Database Compare. Learn how to set-up and configure the Database Compare activity. Running a comparison can compare data across schemas, databases or using snapshots. 

About

Database comparison, or snapshotting, is a pivotal technique for managing technical debt and comprehending data transformations in databases. By capturing the state of all database components at two different instances, referred to as "before" and "after" snapshots, teams can verify the effects of changes, whether for mitigating technical debt manifested as inconsistent structures, poorly optimized queries, redundant data, and outdated schemas, or for executing complex data transformations like ETL processes. This method not only ensures intended changes are effective without adverse impacts but also acts as a robust debugging tool, allowing for swift issue resolution when problems arise post-event. Additionally, it aids in database synchronization across multiple databases, a key aspect when dealing with technical debt on a broader scale, leading to system consistency and improved data transformation processes.

A database activity is created by adding components into the activity including and not exclusively: a Definition version, Submit form, an existing Compare Configuration list. Also see the available specific Actions, including but not exclusively, for a user to Submit the compare, or generate a form to Snapshot a database. 

A Data Activity job can be executed from within the Data Activity space or from the VIP Executor (where a user normally requests jobs). The user then sees a spreadsheet to show the differences in rows of inserted or edited data before and after a test has finished.

There are 3 ways of running a Database Compare (1) as a High Watermark Comparison (8mins 14secs), as a Snapshot Comparison (7mins 42secs) and Database to Database (not shown - the mechanism is the same as Snapshot Comparison but using another connection profile rather then a Snapshot). Also see the benefits overview clip of  Database Compare (2 mins).

Database Compare Overview

Run a High Watermark Comparison

Tip for usage of High Watermark comparison method: when you don’t have control over the database to create a new schema or table, look for the most recently inserted data before and after a test has finished. 

What you’ll see covered in the clip - Initial set-up:

• Checking for a Database Scan and ensure you have a Definition
• Creating a new item and choose a type
• Attaching the pre-defined and most recent version of a database Definition
• Creating a configuration list to automatically generate the SQL statements
• Choosing and executing a specific schema
• Picking up a configuration list
• Choosing the run type
• Previewing the outcome

Then, you’ll see covered:

• Creating and checking for new rows only list
• Querying database with connection profile
• Picking off the recent/highest values in all the Primary Keys for the tables (at this point in time)
• Seeing the testing process 
• Detecting using the High Watermark method
• Comparing the configuring lists for changes
• Running a compare of newly inserted data
• Downloading database compare results to verify which rows of newly inserted data have been added before and after the process you want to test

Run a Snapshot Comparison

Snapshot an Oracle database as an example to compare data before and after testing is finished.

Tip for usage of Snapshot Comparison method: when you are going across a database, set the _SNAP to blank*. 

What you’ll see covered in the clip - Initial set-up:

• Selecting a database, a registered Definition
• Executing a build compare configuration list
• Previewing the tables you want to work with
• Seeing the attached component, i.e. list, in the Data Activity 
• Previewing the list of the information for the compare process to run with Primary Keys and source/target SELECT clauses
• Seeing and redefining _SNAP* which indicates database snapshots will be created to compare against

Then, you’ll see covered:

• Checking and previewing jobs and attachments in the activity audit in descending time order
• Seeing the SQL that’s been issues to Snapshot the database (5 different file types)
• Creating a SQL script and moving it to the server for use in a form for the user to create a Snapshot and Compare to the database, they normally don’t come into the Activity to run it
• Locating and modifying the parameters in the generated form 
• Executing the Snapshot
• Creating a Submit form for the compare activity