There follows a short primer, at a very simplistic level, as to how you can use Table Comparisons and Map Operations to import your data and deal with the 3 record events (Insert/Update/Delete) that you are likely to see in any normal data flow. Obviously there are are a plethora of ways that you can then respond to this but we shall just cover a very simple scenario here for illustrative purposes.
Lets start by stating the data that we are interested in. We will start with a simple import table (DIGEST) that has:-
- A Key column (ID) unique to this row of data
- A date column (Inspection Date) to indicate when an inspection was last made
- And a random piece of information, in this instance a ProcessingStatus field.
This is what our source data looks like
We then want to import this data, as is, into our destination table (BODSDigestTest) and indicate via a WorkflowType column the last operation that was applied to this data. The values for the workflow Type Column are indicated thus:-
0 = Insert
1=Update
2=Delete
Configure the Workflow
Lets start by taking a look at the workflow we will use to accomplish this task:-
The first query specifies first the original SQL query which is selecting only the top 10 records from our data,; Secondly we add in a new Column called WorkflowType which we will use to determine the Operation applied to our data row. This will default to a value of 0 indicating that we are ‘Inserting’ the data
We then use a table comparison component to compare the data within our destination table with the data being processed. The component is configured thus:-
There are a couple of things to note here:-
- Our key field is defined as the ImportID field which is guaranteed to be unique.
- The only field that actually tells us whether our data has changed is the InspectionDate field which is changed upon an inspection occurring.
- We are also interested in identifying data that is deleted from the source that we may have previously imported.
We then want to change the default behavior of the results from the Table Comparison component. This default behavior is defined thus:-
- New records will be inserted
- Updated records will be updated
- Missing records will be deleted.
Items 1 + 2 are exactly as we would wish to do but we want to preserve deleted data and just note that it has been deleted. This is often wheat you encounter when recording a row as being ‘Logically Deleted’. We thus define our Map Operation component like this overriding the default behavior of deleted and instead marking it as an Update:-
If we take a look at the mapping table, on this same component, you can see how we set up the WorkFlow Type values according to the data passing through. Its important to note that any mappings logic utilises the state of the row as it was BEFORE being modified by the Map Operation defined above.
Thus in the following instance we have said that :-
- All incoming Inserted records are to have a workflow type of 0 and are to be inserted
- All incoming Updated records are to have a workflow type of 1 and are to be updated
- All incoming Deleted records are to be have a Workflow Type value of 2 and be updated, not deleted.
So, initially we ran the workflow and ended up with 10 bright and shining new records in the destination table that reflect the data we captured. As you can see they are all set to a Workflow Type of 0 indicating that the data was inserted.
Manipulating The Data
I then elect to manipulate the data so that we end up seeing an update record come through the process. This is achieved by changing the InspectionDate within the Destination (or Source) table. I changed the year in this example from 2021- 2099.
I also want to see a Delete record so I elect in this instance to change the source data such that one of the keys changes.
This has the effect of creating an Insert record as the record ending 295 does not exist within the destination data AND also creating a Delete record as the original record ending with 294 now no longer exists within the source data either.
Workflow In Action
We then debug the workflow, the following can be observed:-
The first record enters the Map Operation as an insert instruction and also exits it in the same manner. This is the record that we changed to 295 (from 294). This will result in an INSERT SQL statement being run.
The second record, where we changed the Inspection data, both enters and leaves the Map Operation component as an Update instruction. This will result in an UPDATE SQL statement being being run.
The final record as you can see, enters the Map Operation as a Delete instruction but is changed to an Update instruction. The workflow status is also updated to 2. This will also result in an UPDATE SQL statement being executed.
If we thus let the job execute to its completion you should see the following:-
Our record ending is 294 is marked as a delete, our record ending in 295 is marked as an insert and the third record is marked as an update.
Obviously this is a simplistic demonstration, but it gives you an idea of what can be accomplished with a simple workflow and is a jumping off point for developing more complex workflows of your own.