Template: Stage (View)

What is the fastest way to ingest data? To persist or to stream? A view comes close to streaming data. Persisting is always kind of stop-and-go in my view.

I wanted to wait until the end of my little template story. But I couldn’t resist to talk about it now. I’m too excited about it!

When I started, I thought I could design either a table or a view alike. But all but one of those object types are fixed to TABLE and the view type is VIEW. I created a ticket to give choice for any object to be created as TABLE or VIEW. Maybe it is still on the development list.

What’s the story behind? I had a couple of stage tables for an export and I was building up a network of data snippets. Each of them persisted. What happens, when I need now to create a job? I must add all those little steps together and having to check for dependencies. Others would maybe build a custom stored procedure. But this kills parallelism. Why not creating little views?

Another one would be, why do I need to persist a staging table when it could be „in-transit“ to load a satellite? No need to wait for persistence.

Recently when I have been exploring the DDL function to create special purpose views. E.g. a „current“ view for satellites, it hit literally my face. Would it be possible to use the DDL function in any object to create views instead of tables?

I couldn’t wait. I copied an existing View DDL and added some code of my stage template. Done.

So easy and so obvious!

Now I can create stage tables and stage views with the same functionality.

Unfortunately, WhereScape has some safety checks around it (e.g. a view can’t be dropped when the object type expects a table). Hopefully they are going to fix that some when.

Download the template nu3_sqlserver_ddl_view_stage.peb and the utility templates from my GitHub repository at https://github.com/EckisWelt/WhereScape.