Non-materialized (Views)

Dalton_Ruer · Jun 19, 2023 10:49:52 AM

SaaS Development

For many developing in the Cloud can be confusing enough. They can't see/feel/touch the physical storage to know if it is really there or not. While you and I are savvy and we understand all of it, imagine just for a minute the person who is already a little apprehensive starting to build a Transformation task in their Qlik Cloud Data Integration project. They go to the settings screen and they are greet with the following major choice:

Well now they are in a quandary. What in the world would it mean in a Cloud to not have their datasets materialized. They want to implement Rules that will transform the data. They want to add new fields. That has to be "materialized" somewhere doesn't it? It can't just be vapor? Can it?

Probably as confusing as hearing my voice in a video, but then finding out it wasn't me. I mean, either I'm there, or I'm not.

For the purposes of this post assume I have 2 Transformation tasks. One that uses the "Non-materialized (Views only) option, and the other uses the Materialized (Tables) option. I have prepared and ran both of those tasks so let's take a look at what each produces in our Snowflake target.

Target

Just as expected the "Non-materialized (Views only)" option has generated SQL Views for each of my 3 datasets. If you have been following along with my series you will already understand what the __changes and __history tables are. If not, be sure to read my previous post What in the word is Type 2 and why isn't there a Type 1 post.

Now let's go ahead an browse the schema that I created for the "Materialized (Tables)" option. Notice that it has created ... give me a second to think about this.

I have to admit that is curious. The option said it would create Tables, but I see views just like I did for the Non-materialized option. Maybe we need to look at the SQL behind the Views to see what they do. The view created for the "Non-materialized (Views only)" option has a Select clause that will read the data from the Storage schema for the project. Looking further we can see that the original fields are being aliased to have "GH_" as a prefix to each of them which is what my rule did. We also see a Substring function being executed to pull the first 3 characters of the Patient_Phone field and then calling that "Area Code."

While there is a View created for our "Materialized (Tables)" option, the SQL code generated is quite different. Notice that it is selecting data from the transform_totable__internal schema rather than the storage schema. Note that it is directly reading the "AreaCode" field. (For the materialized task I did NOT put the rule in the for the GH_ prefix just to keep it clear the two were different for you.)

Aha, so there is a physical Table after all. Now we can all breath easier. I mean they can breath easier, we are the savvy ones and had already guessed this was probably what Qlik Cloud Data Integration would do for us when we saw those 2 options.

Why 2 options?

The logical question that "those other people are asking" now, is why in the world are there 2 different options?

The question that we, the savvy ones, would ask them is "How do you intend to read/use the data from the transform tasks?" Because the biggest difference between the two is "when the CPU work is done to apply rules, add new fields etc." Thus at a high level, you could surmise that if your transformation task will be referred to by further transformation tasks/data marts, it might be best to just leave the data in views, and only invoke the CPU when it is really needed. However, if the transformation task is the end of the line and if your applications/users will read it many times, it makes the most sense to materialize the work to a table so that the CPU work only occurs one time and can be read faster.

Another consideration might be that iff you are leveraging a lot of custom SQL and need to track Type 2 history for that custom SQL for use in generating Type 2 dimensions, then it is recommended you materialize to tables so that you can track the changes.

Providing the Non-materialized Views or the Tables allows you to materialize at any point in the Qlik Cloud Data Integration project where you want/need since it will impact performance.

Am I materialized or not?

As I generally do, I created a video to accompany this post. The entire video uses my voice, however, for the demo part I didn't actually record myself reading the script. But it's there. SERIOUSLY!!!!!

You see I had already recorded a completely video. It was awesome, but when I asked my friend David Freriks for some feedback, he thought I needed some images to help people understand which path I was talking about.

But I'm more a do a live demo from the seat of my pants with the cursor flying all over the screen kind of guy. I don't like just narrating images in Power Point.

After pondering for days, my buddy Clever Anjos offered a crazy suggestion. Utilize Play.HT to generate the narration using my voice. I know that seems preposterous, but that's because you are still reading instead of watching the video. Please be sure and leave your comments about the topic or the "voice" and the use of Generative #AI.

Non-materialized (Views)