Is there any penalty on creating a view from another view?

user_4999690 0
Viewed 177

I have tables that are historicized and then views are created from them to retain only the most recent and active data.

I wanted to make views that would aggregate some of these views together, where I would create my view as a SELECT * FROM {Other view(s)}. So a bit like this:

Table -> Intermediate View -> Aggregated View

I'm just wondering if I'll run into any performance hit by basing my view on other views. Should I just instead have my aggregated views be more complex code-wise, but based directly on the underlying tables?

Table -> Aggregated View

Or does it not make a difference at all?

Thanks a lot.

snowflake-cloud-data-platform
4 Answers

From a performance viewpoint, it doesn't make any difference - unless you are making your view out of a single table, in which case you would be able to Materialize your view - in fact, one of the biggest limitations of Materialized Views is that the FROM has to refer to a single table.

From a software engineering viewpoint, I see many advantages like more reusable work and more flexible and, potentially, faster development (while developer-A works on View-A, developer-B works on View-B, and developer-C could even work on View-C to combine View-A and View-C).

The downside is the increase in complexity of the lineage of the views which might require a graphical representation in some cases where objects are too many.

user_152877420

I have found myself doing this more and more in Snowflake, to the point where I'm writing blog and giving it a new acronym, ELVT. I've built a 3 layer stacking of VIEWs at one client. Lowest level is simple against a single table with presentation names for each column. Next layer is business logic for the underlying single table VIEW. 3rd level is joining VIEWs and more complex business logic (lot's of UDFs). I have a meta-data repository from which I generate all of the VIEWs (which also provides lineages). The final VIEWs have 35+ joins against 40+ physical tables. Salesforce, Marketo, Eloqua and others. SELECT * against multiple years of data using medium DW averaged 1min, 25s. These VIEW replaced thousands of lines of QLIK scripting with SELECT FROM VIEW.

user_123397390

One point to note, is if you are comparing writing one really large block of SQL to nested views, aka macro's.

Then they will perform the same.

The down side to nested views, is you are selecting a lot of columns (in the SQL that is getting compiled) so if at the top level you are not using most the columns, you SQL compile times will be marginally slower.

Also sometime if you put a filter for say a date range, over a large volume of SQL the optimizer can fail to push the filters down, and you can then pull/compute large amounts of data that are later thrown away.

We found this happened, and the optimizer behavior can change with releases. Sometime for the better sometimes for much worse.

We ended up using table functions for a number of parts of SQL to force the date range into the lower layer "views". But we also controlled the layer writing the dynamic SQL so this was an easy substitution.

user_439920

it depends upon what type of processing you are doing in the View, if it is a lot then you can create a Materialized view (this requires storage, and hence will incur some cost). 1 st option try creating a View and if it does not help then try MV.

user_112279190
Related