Referencing Database Views vs. Tables

lookml
(C.J.) #1

I had the question of whether you can create a Looker view from a database view (vs. the database table, which is the default). From Looker support, it looks like this is natively possible in Snowflake and for other DB platforms, this is possible by using a derived table, simplified by executing a query against a database view in SQL Runner and then using the “Add to Project” feature. This will create the dimensions and measures dynamically.

My question, now knowing that it’s possible, is this a best practice? Is it better to build the core aggregate DB tables in the ETL and then do any “refined” modeling in Looker? Just struggling a bit on how to split the modeling work between the DB and Looker. We are using Aurora MySQL BTW.

Thanks!

0 Likes

(Ian) #2

If you model everything in Looker from actual db tables then you can mostly trust Looker to put together the most performant query for every analysis as it will not join in tables that are not needed, all out modelling is not always ideal though as it takes longer and this logic cannot be reused as easily as a db view outside of Looker.

Depending on the database this is not going to be the same for running a query which includes a view (the db view may join in tables which are not required for your analysis and are therefore wasted effort). The same can be said for derived tables (containing similar types of logic) which manifest themselves as CTE’s in your end query, some databases materialize the result of the CTE first and then the main query might only take a small segment of that result…wasted effort. (see templated filters)

If your derived table or db view contains aggregation then it may always need to be calculated first in its entirety (depending on the complexity and again the database).

In conclusion…in depends. Some databases execute certain scenarios in smarter ways than other databases. I have spent a lot of time in the past playing around and testing different options (derived tables with templated filters, database views, completely model in lookml) for a handful of different projects and I wouldn’t say there is not a single answer to this question but there will be a fastest and best way for your scenario and database.

4 Likes

(C.J.) #3

Thanks! Definitely appreciate your feedback and perspective on this. Trying not to get overwhelmed. Sounds like iteration is the best approach here. Fortunately, we have that luxury at the moment.

0 Likes