Dr. Mario's 2nd 🧠

How to Design Your Looker Explores

3 min read

rw-book-cover

Metadata

Highlights

building well-designed explores is the thing we see people get wrong most often when writing LookML (View Highlight)

Make multiple small explores instead of a single big one (View Highlight)

if you join across 20 tables to get an answer that you really only needed two tables for, your intermediate query results have the potential to be truly massive, sometimes exceeding the ability of your database to process (View Highlight)

Design each explore to answer a specific set of questions. Single-view explores are fine! One of my most commonly-used explores is explore: snowplow_sessions {}. That’s it. You can do so much with just that. (View Highlight)

2–3 views joined together is my sweet spot, and 5–6 is my absolute upper limit (View Highlight)

If you’re making an orders explore and just need to join to customers to grab a customer segment, only include that field. This will feel strange at first, but you’ll slowly start to realize how much better the feedback from your users is (View Highlight)

Instead, create two explores. One should use ad_spend as its base and the other should use trial_signups as its base. Both explores might join in the exact same views, but they should be left joined instead of full outer joined. With this strategy, each explore has a clear scope: one is to analyze everything related to the performance of an ad campaign (including how many trials it generated) and the other is to analyze everything related to trial signups (including attribution information). Because of the joins, sensible data will show in each (View Highlight)

Don’t use many-to-many joins (View Highlight)

If you were to write this query by hand, you’d pre-aggregate one of the sides of the join to eliminate the fanout like this: (View Highlight)

But Looker doesn’t write queries like that. It joins everything together first and then aggregates afterwards (View Highlight)

That works fine as long as one of the sides of the join is unique and you don’t get cartesian products, but not in many-to-many situations. That’s an easy way to get your query to hang forever. (View Highlight)

If you find yourself in this situation, you have to pre-aggregate one side of the join before joining it into your explore. Write a quick derived table. This is a bit annoying in practice because it forces you to pre-aggregate in ways that can be hard to anticipate, but it’s a necessary evil if you want your queries to actually return. (View Highlight)

Graph View