Updated date and inserted date in snowflake stage

[kalesha786]

Hi Guys,

we have requirement like when we load into snowflake stage area from oracle. we need to add 3 more columns operation , inserted date, updated date.

We are doing CDC process , when record already exist we need to update and updated date should be update. But inserted date should not change .

have tried but inserted date also becoming null.

Can you please help me on this.

Thanks,

Kalesha Shaik.

[SwathiPulagam · Accepted solution]

@kalesha786  

You can use the below transformation on the table level \ Global level. The transformation column(inserted date) datatype must be VARCHAR(XX)

CASE
WHEN $AR_H_OPERATION = 'INSERT' then
datetime('now')
ELSE
''
END

Also, you have to enable the internal parameter "emptyFieldAsNull" on the snowflake endpoint.

I will prepare an article and post it soon. Please try the above solution.

Thanks,

Swathi

[kalesha786]

If possible can you please share exactly with screen shots , how to achieve 

[Michael_Litz · Accepted solution]

Hi,

While Swathi is correct , you will also need to add code that does a target lookup to retrieve the existing date when the operation is an update.

Please check this article in Qlik community that discusses this.

https://community.qlik.com/t5/Knowledge/Transformation-Target-Lookup-Insert-Date/ta-p/1805653

You can also check out this webinar discussing the same things.

https://community.qlik.com/t5/Knowledge/Customizing-Data-with-Qlik-Replicate/ta-p/1948509

Let me know if this helps out.

Thanks,
Michael Litz

[kalesha786]

Thank you 

[SwathiPulagam · Accepted solution]

Hi @kalesha786 ,

Unfortunately, the Data Enrichment function like Target_lookup won't work for Snowflake target. You can follow the steps that I mentioned in the below article to fulfill your use case.  

https://community.qlik.com/t5/Knowledge/Qlik-Replicate-Transformation-column-INSERT-DATETIME-is/ta-p...

Thanks,

Swathi

[kalesha786]

@SwathiPulagam  ,  have tried still not working 

[SwathiPulagam]

Hi @kalesha786 ,

Please create a support case. I can have a working session with you to fix the issue.

Thanks,

Swathi

[Heinvandenheuvel]

Minor observation -

One  may want to make use of $AR_H_TIMESTAMP instead of datetime('now'), because one probably needs to know when the insert happened, not when it was processed by Replicate.

This is with respect to the suggested

CASE WHEN $AR_H_OPERATION = 'INSERT'
then datetime('now') ELSE '' END 

Hein.

[nigupta]

Any resolution on this topic ? I followed the recommendation but it is not working for me. During update I lose original insert_date. I am using PG as my source and Snowflake as Target.