Can Qlik Compose read Replicate targets , and automate updates to a Persistent Stage layer?

[dadumas]

We have qlik replicate and replicate data to a snowflake stage area

We wish to implement a persistent stage, but absolutely do NOT want to use Data Vault as a Persistent Stage

Our Persistent Stage will look exactly like Qlik replicate stage tables, but with extra columns like effective date, end date, Version Date (when inserted/updated/deleted in stage)

Does Qlik compose have the capability of automation in the form of reading new replicate records and then, in persistent stage, do the following?

Retire the old version in persistent stage record if that record exists (set the end date)

Insert a new version of the row, setting the effective date

If so, we will then have a data lake which can then be used to transform data into a dimensional layer

[robertcur]

Hi Dadumas 

I bit of a loaded question if I may.  If I understand what you want to do correctly, it might be possible to build such solution.  You will have to use a combination of Query based mappings in Compose and pre- and post ETL's using Snowflake Procedures .

Let me know if I'm off the point, but in short this how I would think it is possible.  

1. You replicate to Snowflake (this is as per normal your landing area and/or stage if you like);  You can add any columns using Replicate to this table (i.e. effective date, end date, version date);

2. In compose, you create a source to this Snowflake area (landing/stage) and by using queries for mappings rather than tables, you can then query the landing/stage area using parameters which give the ability to seem like a CDC type call (I.e. you can make use of PreETL and PostETL to update a running last date updated (or whatever you like).  In the compose query you refer to this date set in the previous PostETL and compare to your landing/stage and only read data after that. 

3. You can then using the postETL update your parameters (last date / runno / or whatever); but you can also kick off a procedure on Snowflake to insert versions numbers, update rows, or whatever you can think off.

Does this make sense?  Is this what you are looking for?  

[nojima]

Anyone here with some experience using Qlik/Attunity Compose and Replicate for building and maintaining a Data warehouse (on MS SQL)?

[robertcur]

Hi @nojima, not sure this is the correct thread for this discussion (maybe someone can move this to another thread), but yes there are some of us who has experience in Compose/Replicate with MS SQL.  What info do you require?  How can I help?