Global Transformation in replicate

[suvbin]

can we add a new column to every table with load time of the table.

[john_wang]

Hi @suvbin ,

Now very sure what's your exact requirement. if you simply want to add a new column to every table with latest update time: 

1- if the row was never being Insert/Update/Delete but being Full Load only, then the new column value is the 'load time'

2- if the row was ever being I/U/D then the column value is the latest 'update time'

then it's much easier. you can add a global transform with expression:

DATETIME('NOW','localtime')

however if you do not wish to record the latest 'update time' but keep it as 'load time' only then it's some complex.

Regards,

John.

[suvbin]

can we do like this for the load time as shown in below screenshot

[john_wang]

Hi @suvbin ,

Yes. this is what I meant "Global Transformations...".

Regards,

John.

[suvbin]

But you were mentioning, that load time is complex . Can you please explain on that and how to do it

[john_wang · Accepted solution]

Hi Suvbin,

sorry for the delay. the simple one is as below.

hope it helps.

Regards,

John.

[suvbin]

how to accomplish the below one...

"however if you do not wish to record the latest 'update time' but keep it as 'load time' only then it's some complex."

[Nimmi]

@john_wang    TY John! Simple and elegant Solution! 

Question - In the solution, is the date time stored in the field, 

a. The time the data comes to Replicate Server

b. The time the data is committed to the  target database

The reason I ask is - we are using log stream tasks in our implementation and if there is any downtime in the target database, then there will be a  large difference in the timestamps - creation at source and posting into target.

Thanks

Nimmi

[john_wang]

Hi @suvbin ,

Sorry I missed the update.

Regarding "however if you do not wish to record the latest 'update time' but keep it as 'load time' only then it's some complex."

Basically we can use target_lookup() function to get the 'load time' and write it back to the target table whenever an update occurs on the row. This operation may slow down the target apply performance.

Regards,

John.

[john_wang]

Hello @Nimmi ,

The date/time comes from the Replicate Server OS system time.

Please take note that in your scenario:

1. In the "Log Stream Staging task", Global Transformations are not available 

2. Based on (1) you can only add the transformation in "Replication task"

3. you are right, if there is any downtime in the target database, then there will be a  large time gap

Regards,

John.