Enhance Your Data Generation with Tonic's Upsert Feature

Table of Contents

1. Introduction
2. Traditional Tonic Data Generation
   1. Source Database and Destination Database
   2. Replacing the Destination Database
3. Upsert and Its Process
   1. Intermediate Database
   2. Upserting Records
4. Enabling Upsert for a Workspace
   1. Configuring Upsert Settings
   2. Disabling User-Defined Triggers
   3. Running the Upsert Process
5. Upsert Options and Features
   1. Preserving Temporary Tables
   2. Handling Constraint Mismatches
   3. Providing Intermediate Database Connection Details
6. Generating Data with Upsert
   1. Enabling or Disabling Upsert for Data Generation
   2. Running Data Generation and Upsert Process Together
   3. Running Only Data Generation or Only Upsert Process
7. Viewing Job History
   1. Separating Data Generation and Upsert Jobs
8. Conclusion

Upsert Data Generation in Tonic: An Advanced Approach to Data Generation

In this article, we will Delve into the concept of upsert data generation in Tonic. We will explore how Tonic's traditional data generation process can be enhanced with the upsert feature to preserve existing records in the destination database. So, let's get started!

Introduction

Data generation is a crucial aspect of database management, especially in the Context of testing and maintaining consistent sets of records. Tonic offers a unique solution called upsert, which goes beyond the traditional data generation approach.

Traditional Tonic Data Generation

Source Database and Destination Database

In a traditional data generation process, de-identified data from a source database is used to populate a destination database. However, every time this process runs, the destination database is completely replaced with new records.

Replacing the Destination Database

While replacing the destination database is appropriate for certain use cases, it may not be ideal when external factors contribute to the records in that database. For instance, You might have tests that rely on a standard set of records or maintain a group of internal user account records. In such scenarios, manually replacing these records after every data generation becomes tedious and error-prone.

Upsert and Its Process

To address the limitations of traditional data generation, Tonic introduced the upsert feature. Upsert works differently by preserving existing records in the destination database.

Intermediate Database

When you enable upsert, Tonic performs the data generation process as usual but writes the de-identified data to an intermediate database. This intermediate database serves as a staging area for the upsert process.

Upserting Records

During the upsert process, Tonic compares each Record in the intermediate database with the records in the destination database using a primary key. If a matching record is found in the destination database, Tonic updates it. If the record does not exist, Tonic inserts it. This way, only the necessary changes are made to the destination database, ensuring the preservation of other records.

Enabling Upsert for a Workspace

Enabling upsert for a workspace is a straightforward process in Tonic. On the workspace details view, simply toggle on the "Enable Upsert" option. However, before doing so, it is important to review the upsert settings to ensure they Align with your requirements.

Configuring Upsert Settings

By default, Tonic disables any user-defined triggers before running the upsert process. These triggers are processes that typically run after an insert, update, or delete operation on a table. Disabling these triggers prevents the creation of duplicate records in the destination database.

Running the Upsert Process

By default, Tonic automatically runs the upsert process immediately after the data generation to the intermediate database. However, you have the option to disable this automatic run and manually trigger the upsert process.

You can also preserve temporary tables that contain records the most recent upsert job could not process due to unique constraint conflicts. These tables can be useful for troubleshooting and further analysis.

Furthermore, Tonic allows you to choose how mismatches in foreign key and unique constraints are handled during the upsert process. You can treat them as warnings instead of errors to prevent the upsert job from failing.

Generating Data with Upsert

Once upsert is enabled for a workspace, you have the flexibility to choose whether to enable upsert for each data generation. On the confirmed generation panel, you can easily toggle on or off the upsert option.

Running Data Generation and Upsert Process Together

If you want to run both the data generation process to the intermediate database and the upsert process to the destination database together, simply select the "Run Generation Plus Upsert" option. However, if you have disabled the automatic run of the upsert process after data generation, you need to select the "Run Generation Only" option to execute the data generation.

Running Only Data Generation or Only Upsert Process

If you only wish to run the upsert process from the Current intermediate database to the destination database, you can select the "Run Upsert Only" option. This option can be utilized to manually trigger the upsert process or to Rerun upsert in case of any initial failures.

Viewing Job History

In the job history view, Tonic provides separate listings for data generation and upsert jobs. This segregation allows you to track the progress and status of each job individually, ensuring a comprehensive overview of the entire process.

Conclusion

In conclusion, upsert data generation is a valuable capability offered by Tonic to enhance the traditional approach of populating destination databases. By preserving existing records and enabling targeted updates, Tonic ensures that databases stay consistent and Relevant over time. To learn more about upsert data generation and explore other aspects of Tonic, we encourage you to check out our videos, blogs, or the Tonic product documentation. Upgrade your data generation process with Tonic's upsert feature today!

Highlights:

• Tonic offers upsert data generation as an advanced approach to database management.
• Upsert preserves existing records in the destination database, enhancing data consistency.
• Configurable upsert settings provide control over user-defined triggers and constraint conflicts.
• Manual or automatic execution of the upsert process allows flexibility in workflow management.
• separating data generation and upsert jobs in the job history facilitates tracking and analysis.

FAQ

Q: What is the AdVantage of using upsert data generation in Tonic? A: Upsert data generation in Tonic allows for the preservation of existing records in the destination database, ensuring data consistency and eliminating the need for manual replacements.

Q: Can I enable upsert for specific data generations in a workspace? A: Yes, Tonic provides the flexibility to enable or disable upsert for each data generation within a workspace, allowing fine-grained control over the process.

Q: How does Tonic handle constraint conflicts during the upsert process? A: Tonic treats constraint conflicts, such as mismatches in foreign keys or unique constraints, as warnings by default, preventing the upsert job from failing and providing flexibility in managing data integrity.

Q: Are there any troubleshooting features available when using upsert in Tonic? A: Yes, Tonic allows you to preserve temporary tables containing records that cannot be processed due to unique constraint conflicts. These tables can be helpful in troubleshooting and resolving issues.

Q: Can I rerun the upsert process in case of initial failures? A: Yes, if the upsert process fails initially, you can manually trigger the upsert process again after addressing any schema conflicts or other issues in the destination database.