Detect Changes
  • Dark
    Light
  • PDF

Detect Changes

  • Dark
    Light
  • PDF

This article is specific to the following platforms - Snowflake - Redshift - BigQuery - Synapse.

Detect Changes Component

The Detect Changes component lets users scan two separate (but similar) tables, and insert a new column detailing if data has been inserted, deleted, changed, or even if the data is unchanged.

Any rows with key columns that contain NULL values will be ignored. NULL comparison values are considered equal.


Properties

Property Setting Description
Name String A human-readable name for the component.
Master Table Select Select a master table from the two inputs. This table is the one treated as default in the comparison with the second table.
Match Keys Multiple Select Select the key columns to join the two tables on. These columns must appear in both tables.
Compare Columns Multiple Select Select the columns that will be checked for changes. Just like the keys, these columns must appear in both tables; however, the two lists should not overlap.
Output Column Mapping Input Column Select input columns to map to output names. Sensible defaults are provided automatically; however, these can be changed.
Output Column Name output columns to which selected input columns will map.
Indicator Column String Input a name for the new column in the output. By default, this column is named "Indicator". This column contains an indicator that shows the status of each record:
C the record has been changed.
D the record has been deleted.
I the record is identical.
N the record is new.
Note: switching the master table in the Master Table property will reverse the meaning of new (N) and deleted (D).

Strategy

Detects changed, unchanged, added, or deleted data in a comparison table relative to the designated master table.


Indicators

Indicators are single-letter codes that indicate what the state of a row is with regard to Detect Changes. The table below shows all indicators and their meanings.

Indicator Description
C Changed: the record is present in both tables, with different values, but with the same ID.
D Deleted: the record is present in the master table, but not in the second table.
I Identical: the same record is present in both tables with no changes.
N New: the record is not present in the master table, but is present in the second table.


Example

In this simple example, two Fixed Flow components feed into a single Detect Changes component to easily view which rows are different between the two Fixed Flows, and also how the rows in the Modified Values flow differ from the Original Values flow. The Transformation Job is shown below.

When configuring the Detect Changes component, the Original Values flow is selected as the Master Table. The tables are to be matched by the ID column. The Indicator Column is left with the default name.

Here is the Original Values data:

And here is the Modified Values data:

Finally, we run the job, and sample the Detect Changes component. In the indicator column, it is clarified that IDs 1 and 3 have remained identical in both datasets; IDs 2,4, and 6 have changed; ID 5 has been deleted in Modified Values; and ID 7 is new.



Video


What's Next