Drop and Reload the DW but delaying the DM Drop

[ubanerjee]

In our production environment it takes a while to generate all ETL sets (around 2.5 Hrs) and over 10 hrs to load the DW, when we run a full load. Sometimes due to the number of changes we do the model its is much simpler to the full load than fix the data in DW. This could be the trend for the next few releases. In production when we reset the DW the only option available is to drop both the DW and DM together.  Ideally we would like to finish loading the DW first before touching the DM. This way our deployment can start during business hours without impacting the business. The DM creation and load takes  less than an hours and this would be an ok downtime.

We tried changing the DW schema to something else, prior to running the generate using the cli. However, it seems like compose still dropped the old DW. Is there another way to defer touching the DM till all work in the DW layer is complete?

We are using Snowflake so, one of the option that seems to work for us is to UNDROP table, but that causes additional issues with the DW recreate.

[Adam_Herman · Accepted solution]

Hi @ubanerjee,

Have you resolved this matter? or is it still relevant?  
If it's still relevant, then I want to confirm with you first, that I've understood the scenario right:

What you would like to do: is to drop and recreate the DWH after adding new data, without touching the DM, because you don't want the data to be deleted from DM (while recreating the DWH) as the data is being used for business purposes?

If this is the case, then what you can do as a workaround, is the following:

Create the whole project (including the DM)
When you get to the point, where you have to add new data and want to drop and recreate the DWH, before doing that:
Go to the connection, and change the DM schema, so in this case, when you recreate the DWH, it will reach out to the current DM Schema, without touching the data in the DM, because the data is saved under the "OLD" schema.
Then once the DWH is created, you can revert back to the "main" schema, and upload the data in the datamart under it.

For example:
Let's say I'm using DM schema called "Adam", which is my main schema.
Now, I've made a few changes to the Model, and want to recreate the DWH.
Before doing that, I change my DM schema to "Adam_temp" (created it in Snowflake, and then defined it in Compose in DWH connection).
Once I drop and recreate the DWH, it will reach out to the DM, and look for data under "Adam_temp" (there is none of course), and Compose will create new empty DM tables under "Adam_temp" schema.
In the meanwhile, the data in the DM under "Adam" schema is still available.
Once the DWH is ready, I change back to "Adam" schema, and continue working with the DM as you wish.

However, what we do recommend, if this workaround works for you of course, is to test the scenario first in a TEST ENVIRONMENT, before going forward with it to Production.
Nevertheless, I would like also to suggest, upgrading to the latest patch of Qlik Compose Aug, and testing the behavior of your project there, in regards to the time it's taking for the ETL (and maybe for the DWH) to be created.

Hope that helps and meets your requirements.
Please do not hesitate to contact me for any other queries you might have.

Regards,
Adam

[Nanda_Ravindra]

@ubanerjee  just checking to see if  Adma's suggestion worked for you. Let me know.

Thanks,

Nanda

[ubanerjee · Accepted solution]

@Nanda_Ravindra @Adam_Herman 

Thank you for your inputs. Yes, the solution mentioned would work for us. As our DW matures, we are moving past dropping of the DW for most of our projects and this makes it a little easier for us in terms of not having to reload DW. Additionally, the new version of Compose does not automatically drop and recreates the DM. This will also enable us to maintain business continuity as we work on our deployments during business hours. 

[Nanda_Ravindra]

@ubanerjee Yes, Our 2022, 05  release does not automatically drop and recreates the DM. I posed the link and the release notes to one of your support cases. Please update the case if you have any questions on the new 2022.05 release.

Thanks,

Nanda