Unexpected behavior using transform column and transform table, SymmetricDS

Question

I have the followings transformation columns

insert into SYM_TRANSFORM_COLUMN (
        transform_id, include_on, target_column_name, source_column_name, pk,
        transform_type, transform_expression, transform_order, last_update_time,
        create_time
) values (
        'send_source_node_id', '*', 'node_id', 'employee_id', 1,
        'bsh', 'String node = targetNode.getNodeId(); String value = currentValue; return value + "-" + sourceNodeId;', 1, current_timestamp, current_timestamp
);


insert into SYM_TRANSFORM_COLUMN (
        transform_id, include_on, target_column_name, source_column_name, pk,
        transform_type, transform_expression, transform_order, last_update_time,
        create_time
) values (
        'copy_employee_id', '*', 'employee_id', 'employee_id', 0,
        'copy', '', 10, current_timestamp, current_timestamp
);

insert into SYM_TRANSFORM_COLUMN (
        transform_id, include_on, target_column_name, source_column_name, pk,
        transform_type, transform_expression, transform_order, last_update_time,
        create_time
) values (
        'copy_employee_name', '*', 'name', 'name', 0,
        'copy', '', 10, current_timestamp, current_timestamp
);

With its transform tables

insert into SYM_TRANSFORM_TABLE (
        transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
        target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
        last_update_time, create_time
) values (
        'copy_employee_id', 'school', 'main-school', 'EXTRACT', 'employees',
        'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
         current_timestamp, current_timestamp
);

insert into SYM_TRANSFORM_TABLE (
        transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
        target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
        last_update_time, create_time
) values (
        'copy_employee_name', 'school', 'main-school', 'EXTRACT', 'employees',
        'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
         current_timestamp, current_timestamp
);

insert into SYM_TRANSFORM_TABLE (
        transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
        target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
        last_update_time, create_time
) values (
        'send_source_node_id', 'school', 'main-school', 'EXTRACT', 'employees',
        'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
         current_timestamp, current_timestamp
);

Structure of my source table

create table  employees (
    employee_id serial,
    name varchar(50),
    CONSTRAINT departments_pk PRIMARY KEY (employee_id)
);

Structure of my target table

create table  employees (
    master_employee_id uuid default uuid_generate_v1(),
    employee_id integer,
    name varchar(50),
    node_id varchar(50),
    CONSTRAINT employees_pk PRIMARY KEY (master_employee_id)
);

The synchronization works fine with other tables that are identical, but these tables do not work fine, because when a new employee is added in the source table two records will be added to the target table. I not idea about this behavior, can someone explain to me?

Example of the result in the target table

Answer 1

Each entry in sym_transform_table represents a table mapping that will process row changes for the source table. Since you have 3 table mappings for the same source table, each row change on that table will result in 3 transformed rows.

I think you wanted to use 1 table mapping in sym_transform_table, like this:

insert into SYM_TRANSFORM_TABLE (
    transform_id, source_node_group_id, target_node_group_id, transform_point, source_table_name,
    target_table_name, update_action, delete_action, transform_order, column_policy, update_first,
    last_update_time, create_time
) values (
    'employees_to_employees', 'school', 'main-school', 'EXTRACT', 'employees',
    'employees', 'UPDATE_COL', 'DEL_ROW', 1, 'IMPLIED', 0,
     current_timestamp, current_timestamp
);

Then change your entries in sym_transform_column to reference it by using the same "employees_to_employees" transform ID.