Insights

Looker recently introduced the experimental Labs feature importing files from other projects and it promises to be another game changer for your project development. However, innovation always brings intricacy and a heightened attention to detail. If you add in other DRY (Don’t Repeat Yourself) concepts like extending explores and native derived tables, things can get a little tricky.

Looker enables you to reuse explores, views, and LookML dashboards, greatly cutting down the development process. However, you should keep in mind everything in your base explore (the one to be extended) will be included in the extensions; particularly all the joins.

Like all derived tables, a native derived table (NDT) is defined in LookML at the view level and is essentially a query whose results are treated just like a physical table in your database. However, rather than using SQL to define the query, native derived tables utilize the dimension and measure definitions already created in your model. Among many other benefits, native derived tables enable you to utilize code and logic already built into your model. In order to accomplish this, you must identify the explore you want the NDT to be based on with the explore_source parameter.

Since NDTs are based on explores, you’ll need to be mindful of this fact when importing them from other Looker projects. Especially if you will be extending explores in your new project. You should join the NDT in the final explore, and the final explore must use the same name as the native derived table’s source. If you try to use the NDT’s source name for your base explore (the one you’ll be extending), and require extension, Looker will throw an error telling you it can’t find the source for the NDT.

That’s a lot of words, so a concrete example should help.

Troubleshooting NDT 'Source Not Found' Errors with Imported Views

Suppose you have a view named “ranked_sales” built in a project named “standard_data_project.” The view is based on a native derived table whose explore_source is “sales”. You want to create a project named “marketing” that imports the “ranked_sales” view from “standard_data_project” (note this project doesn’t necessarily have any models or explores). You create a model in the “marketing” project named “marketing_data” and an explore named “marketing_data_base” which you intend on extending to other explores. You then create another model named “marketing_reporting” with a “marketing_reports” explore which extends your “marketing_data_base” explore.

In short, we’re creating a project with a base explore we intend on extending, and we want to use a native derived table imported from another project.

So, we should end up with something like this:

Looker is going to throw an error that the “sales” explore, the explore_source for “ranked_sales” NDT does not exist. Indeed, it doesn’t. You must name one of your explores “sales”. The question is which explore?

It’s best practice to include only what is needed in your explore. Your base explore specifically should only include the fundamental views you’ll be needing for your entire project, and it should be appropriately named. While you can include the NDT in your base explore, if you join its view into your base explore, every explore that extends from that explore will include that join, and will therefore need to be named for the NDT’s source. In other words, you’ll only be able to extend the base explore to one other explore in each of your project’s models, and those explores will all need to be named for the NDT’s source, or “sales” in our case.

If you require more flexibility than that, you’ll want to keep your base explore streamlined and include and join only those views absolutely necessary for your entire project. You can then extend this base explore to other explores and include their NDTs in their respective models. Note that only one explore in each model of your project will be able to join in the imported NDT. In addition, you won’t be able to join in multiple NDTs into your explore unless they all use the same explore source.

In our example, we’ll have something like this:

Extending explores, native derived tables, and now importing files from other projects are very powerful Looker concepts, and logical progressions of the DRY philosophy embraced by Looker. However, keep in mind the magic going on under the hood when Looker converts your LookML into SQL. Remember to keep your base explores limited to only those views which are necessary for all intended extensions, and ensure any explores using NDTs are properly named.

Jeff Springer is an Analytics Consultant Team Lead at DAS42.