topic Global Rules - I need a column that shows when the record was written to the target in Qlik Replicate

Global Rules - I need a column that shows when the record was written to the target

pkagel — Tue, 27 Feb 2024 22:24:30 GMT

I currently am replicating data from sql server to snowflake. It looks like we may have had a huge delay in when the changes came into sql server and when they were actually written to snowflake. Is there any variable I can use that will set a date in a table in the target as to when it actually wrote to the target? Besides just setting a default date column on the snowflake side?

Re: Global Rules - I need a column that shows when the record was written to the target

Michael_Litz — Tue, 27 Feb 2024 22:40:47 GMT

Hi @pkagel ,

setting a default date column on the snowflake side is the best option since it will have the actual time the record was written,

I see this a lot with MS SQL target tables.

The other option would be to add a global transformation "Add Column" and put the date value there - it may end up still being different than the table field due to target side latency.

I hope this helps out.

Thanks,
Michael

Re: Global Rules - I need a column that shows when the record was written to the target

pkagel — Tue, 27 Feb 2024 22:43:54 GMT

Thank you. If I wanted to do it from Add Column which data value would I use?

Re: Global Rules - I need a column that shows when the record was written to the target

Michael_Litz — Tue, 27 Feb 2024 22:54:57 GMT

Hi @pkagel ,

Please try DATETIME('now') as the value in the add column expression.

Thanks,
Michael

Re: Global Rules - I need a column that shows when the record was written to the target

Heinvandenheuvel — Tue, 27 Feb 2024 23:41:21 GMT

Default dt column on target is best.

From Replicate all you can do is Datetime(now,localtime) to know when Replicate got around to process it. However, at that point It only gets stuck in the CSV file with thousands or millions of others. That CSV gets pushed over the wire and then stored table by table ALPHABETICALLY. That can take anywhere from 2 seconds to 2000 (or worse).

If you are truly interested in the details and do not want to add the default column on target then I urge you to get comfortable with the reptask logs. Set logging for TARGET_APPLY to DEBUG and you'll see when the store actions for your target tables are triggered and when they finish. I have published scripts to automate that review process some.