With the advent of "cloud" computing or Software as a Service (SaaS), many companies will find new and exciting requirements to push data out into the world. With this challenge comes the need to make sure that the data residing on the cloud application is synchronized with the data residing on the source home application. Since the cloud application is not directly connected to the source system, there's no ability to directly reconcile the data.

Standard applications would use CHAINs, Lookups, or FINDs to determine if the record is an Add, an Update, or a Delete. Cloud applications, on the other hand, have no direct connection to the source database and must create a mirror file to act as a virtual image of the data residing on the cloud. This mirror file is then compared to the current data to determine what will be an Add, an Update, or a Delete. This delta result is then consolidated and sent to the cloud application for processing.

The SQL Delta Process will run in two different states with the exact same SQL: the Initial Load state and the Delta Load state. The Initial Load State is the first run of the SQL Delta Process, where the SQL scripts will be establishing the mirror file image and creating the initial load of the customer data. The result of the initial load will be all records delivered to the cloud as "Add" records. The Delta Load State is the second through n runs of the SQL Delta Process, where there's an active comparison between the current image and the mirror image to define the Adds, Deletes, and Updates.

In the examples below, I will show the seven steps for the Initial Load State and then the same seven steps for the Delta Load State.

Initial Load State

Step 1: Take a Snapshot of the Current Image (Initial Load State)

In this step, the SQL script is selecting all the necessary Customer Master data that needs to be sent to the cloud application. The results of this SQL will be a "Current Image" file called CUST_CI. This is obviously a simple example; the real-world applications would be more complex and would consist of many more SQL steps.

The Initial Load State and the Delta Load State will yield the exact same results.

Figure 1: The Initial Load State gathers the required data.

***Editor's note: To see enlarged figures, download all figure files here.***

Step 2: Create the Mirror Image File (Initial Load State)

This step will create the mirror file and an exact duplicate of the current image file. The "WHERE" clause contains a deliberate FALSE statement of 1=2 so as to yield no records in the result. This step is intended only to create the mirror image file CUST_MI structure for the proceeding steps.

Figure 2: Create the mirror image file.

Step 3: Find the Records That Have Been Added (Initial Load State)

This step will find the records that have been added by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). If the record is in the current image file and not in the mirror image file, then the record was added today and should be sent as an Add. This is accomplished in SQL by creating a left join by joining the key from the current image file to the mirror image file and selecting the records WHERE the mirror image key is null.

Figure 3: Find added records.

Step 4: Find the Records That Have Been Deleted (Initial Load State)

This step will find the records that have been deleted by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). If the record is in the mirror image file but not in the current image file, then the record was Deleted today and should be sent as a Delete. This is accomplished in SQL by creating a Left join by joining the key from the mirror image file to the current image file and selecting the records WHERE the current image key is null.

Figure 4: Find deleted records.

Step 5: Find the Records That Have Been Updated (Initial Load State)

This step will find the records that have been updated by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). If the field in the current image file is different from the same fields in the mirror image file, then the record was updated and should be sent as an Update. This is accomplished in SQL by creating an inner join by joining the key from the current image file to the mirror image file and selecting the records WHERE the current image field(s) and the mirror image field(s) are not equal.

Figure 5: Find updated records.

Step 6: Send the Delta Records to the Cloud (Initial Load State)

This step will consolidate the Delta data together, flag the record action (Cust_Action), and send the results to the cloud application. This is accomplished in SQL by "UNION"ing the file containing Adds (CUST_ADD), the file containing Deletes (CUST_DEL), and the file containing Updates (CUST_UPD) into one file called CUST_DELTA. A new field is included called the Cust_Action to tell the cloud application how to process the record.

Figure 6: Send records to the cloud.

Step 7: Refresh Mirror File with Current Image (Initial Load State)

This step will take all the data from the current image file and copy it to the mirror image file as a full replace.

Figure 7: Refresh the mirror file.

Delta Load State

Step 1: Take snapshot of the current image (Delta Load State)

In this step, the SQL script is selecting a new "Current Image" and placing the result in the CUST_CI file. During the day, there were modifications to the CUST_CI file (see "Today's Changes") that need to be reported to the cloud application.

Figure 8: Take a snapshot of the current image.

Step 2: Create the Mirror Image File (Delta Load State)

Since the mirror file was created during the Initial State, this step in the Delta Load State is not needed. It can execute without generating any result, or it can be skipped altogether.

Figure 9: Create the mirror image file.

Step 3: Find the records that have been added (Delta Load State)

This step will find the records that have been added by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). "Today’s Changes" will be identified as one new record that exists in the current image file but not in the mirror image file.

Figure 10: Find added records.

Step 4: Find the records that have been deleted (Delta Load State)

This step will find the records that have been deleted by comparing the Current Image file (CUST_CI) to the Mirror Image file (CUST_MI). "Today’s Changes" will be identified as one deleted record because it exists in the mirror image file, but it is not in the current image file.

Figure 11: Find deleted records.

Step 5: Find the records that have been Updated (Delta Load State)

This step will find the records that have been updated by comparing the current image file (CUST_CI) to the mirror image file (CUST_MI). "Today’s Changes" will be identified as records that have different (non-key) values when comparing the current image file to the mirror image file.

Figure 12: Find updated records.

Step 6: Send the Delta records to the Cloud (Delta Load State).

This step will consolidate "Today’s Changes" together, flag the record action (Cust_Action), and send the results to the cloud application.

Figure 13: Send records to the cloud.

Step 7: Refresh Mirror File with Current Image (Delta Load State).

This step is exactly the same as it is in the Initial State, where all the data from the current image file is copied to the mirror image file as a full replace.

Figure 14: Refresh mirror file.

Conclusion

There are many different methods of identifying changes to a database using SQL, and I have presented one of them. It creates a mirror copy of the cloud data that can be used to identify adds, updates, and deletes. Since the mirror file is stored locally, it is a great resource for analysis and reconciliation. Hopefully, this information helps you in your journey to the cloud.