Track Data Changes
You can use these techniques for tracking changes to your datasets over time.
Create Backup
After you have created the flow and the datasets within the flow and before applying recipe steps to change the data, create a duplicate of the flow. This becomes a snapshot of your original dataset. Since the imported datasets are not affected, the storage overhead for creating backups is relatively low.
Track Source Filepath and Filename
When you first load your dataset in the Transformer page, you can add the following to capture the full path to the original file that is the source of the data:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | $filepath |
Parameter: New column name | sourceRowNumber |
With a few extra steps, you can extract the filename from the above output.
Track Source Row Information
You can mark the original row numbers of your source data. In the first step in your recipe after initial parsing, add the following:
Transformation Name | |
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | $sourcerownumber |
Parameter: New column name | sourceRowNumber |
This step generates a new column that contains the source row number from the source dataset.
Nota
Source row information can become invalid if you perform multi-dataset operations such as lookups, unions, and joins. For more precise tracking of source information, you should consider creating multi-column keys, including the source row number information. For more information, see Generate Primary Keys.
Track Steps Affecting a Column
To see all of the steps in your current recipe that reference a specific column, select Show related steps... from the column menu.
All steps are highlighted in the Recipe panel.
Nota
If another column is dependent on the selected column, all steps pertaining to that column are highlighted as well.
Track Column Value Changes
Designer Cloud enables you to easily move between steps in your transform recipe so that you can check the state of your dataset at any point during the transformation. In some cases, you may want to be able to track the changes made to an individual column side-by-side with the original column. This section provides a generalized approach for tracking column changes in this manner.
Nota
Use this task only if it is important to monitor which values have changed in a column. For most use cases, the Transformer page provides sufficient visibility over your sample data to manage column values.
Steps:
In the following sequence, the original column is called String. For numeric columns, you can perform more detailed analysis between original and modified column values.
After you have completed your general setup steps of your transform, create a copy of the original column:
Transformation Name
New formulaParameter: Formula type
Single row formula
Parameter: Formula
String
Parameter: New column name
String_orig
You now have a copy of the original column before any manipulations were applied to it.
Add any transforms to your recipe, including any that change the values of
String. In the example below, the following transform has been applied:Transformation Name
Edit with formulaParameter: Columns
String
Parameter: Formula
TRIM(String)
At the point in your recipe where you would like to test the column for changes, insert the following:
Transformation Name
New formulaParameter: Formula type
Single row formula
Parameter: Formula
String <> String_orig
Parameter: New column name
String_changes
The
String_changescolumn now containstruevalues where the values inStringhave been changed from their original values (String_orig).To see just the values that are different, sort in descending order.
Sugerencia
You can reposition this test anywhere in your recipe after you have created the
String_origcolumn.Before you run your recipe, you may want to remove the tracking columns that you generated (
String_origandString_changesin our example).
Figure: Example tracking column changes
Track Row Changes
Steps:
In Flow View, create a copy of the flow. Create a name for it that identifies it as your original.
In the other flow, create your recipes as normal.
When done, you can add the following steps:
Union the two datasets together.
Sort them by a key column.
Add the
deduplicatetransform.Nota
This method may not work if your recipe includes joins or added or removed columns.
If the rows are exact duplicates, they are removed. The remaining rows contain data that has been changed.