When I reload a table is there anything captured to show that a specific table was reloaded at a specific time

[pkagel]

So we have an ETL Process that once the data comes in from our source into snowflake that process transforms the data.  We are using the replication date to know when we receive new data.  One issue we have run into is when we have to reload a table because of an error or whatever reason.  It then updates all of the replication dates to when the table was reloaded.  The problem is that those records already exist downstream and our process wants to reprocess all of those records.  I am trying to find out if there is any flag or message I can grab that would tell the downstream process that this table was just reloaded.  

Qlik Replicate 

[SwathiPulagam]

Hi @pkagel ,

You can find the information in the task log as below:

00011956: 2022-08-04T11:25:56 [TARGET_LOAD ]I: Load finished for table 'dbo'.'literal_pk' (Id = 1). 5 rows received. 0 rows skipped. Volume transferred 2224. (streamcomponent.c:3976)

00011956: 2022-08-04T11:25:56 [TARGET_LOAD ]I: Load finished for table 'dbo'.'literal_pk' (Id = 1). 5 rows received. 0 rows skipped. Volume transferred 2224. (streamcomponent.c:3976)00043892: 2022-08-04T11:25:59 [TASK_MANAGER ]I: Loading finished for table 'dbo'.'literal_pk' (Id = 1) by subtask 1. 5 records transferred. (replicationtask.c:3012)

Thanks,

Swathi

[pkagel]

I am looking for something that I can use on the target . I don't want it to be a manual process.

[SwathiPulagam]

Hi @pkagel ,

Using global transformation, you can add different columns to populate the Reload date, Insert time, and update time at Target.

Thanks,

Swathi

[pkagel]

So what field would tell me that it was a reload and not just a normal insert?  

[SwathiPulagam · Accepted solution]

Hi @pkagel ,

Go to the Expression Builder--> Variables--> Then select the $AR_V_TASK_RELOAD_TIME.

Below is the attachment for your reference:

[SachinB]

Hello @pkagel ,

You can also use the "AR_H_TIMESTAMP" in global transformation, When you initiate the full load it will just append the "Current time" when the actual load is done and For Change processing "The timestamp of the Change operation, which is represented as the local time on Replicate Server. If your database and Replicate Server are located in the same timezone, the timestamp will be approximately representative of the actual Change operation"

Here is the screenshot for your reference.

Hope this helps for you.

Regards,

Sachin B

[pkagel]

This field doesn't work when you only reload a table. Not a full reload of all the tables in the task I just want to know when a table gets reloaded.  Is there any other option?

[shashi_holla · Accepted solution]

Hi @pkagel 

Please correct me if I'm wrong but the end result we trying to achieve is that when we do a reload, downstream application should not process all the records from the table but just the ones which came in new. So the question is does the table maintain create_date/last_update date in the source? 

If Yes then we have to build the downstream logic based on the last_update column which is the right representation of when the record was changed.

If not then it would be difficult since the Full Load process might drop and recreate the table or truncate the existing data and reload which will wipe out any history we had before.

There is a Do Nothing option for Full Load which is documented to process only new changes but from my experience I've seen it creating duplicates and might not help. But can try it in a non-Prod environment and check if it helps.

Thank you,

[pkagel]

Thank you.  Unfortunately the application does not always properly populate those fields so we can't use them.  I was referring to the other suggestion about using the creation_date update_date fields