View the original community article here
Last tested: Jun 1, 2020
This article: How do you decide what to model in dbt vs LookML? gives an outline. Substitute dbt for any other ETL or database-layer transformation tool. The key points are that LookML is best for modeling query-time transformations in an iterative way.
- LookML is best for Query-time transformations.
- Query-time transformations are good for: giving maximum flexibility for the end users. The article section "Should I calculate metrics in dbt or Looker?" explains how there are infinite combinations of any data set: don't try to predict every combination and constrain your end-users with pre-built tables. Give them a GUI to combine pieces on their own. Before you model something in the database, ask yourself, "Will this limit the end users' freedom to get the details they need?"
- Query-time transformations are not ideal for: computationally heavy calculations that are frequently required. For example, maybe 2 tables are always joined together, and it would make sense to pre-join them into a wide table in the ETL, since running this join at query time happens often, and is expensive.
- LookML is best for iterative modeling.
- LookML is lightweight and agile. You can quickly develop and test data modeling changes, plus track version history with Git. LookML modeling is ideal if you aren't sure how the final logic should be and/or if logic frequently changes.
- Many leverage this quality to use LookML as a prototyping tool: Discover how to model data in LookML, then move these transformations into the database once they have been tried and tested (so long as they don't limit end-user flexibility, discussed above). Since you can also create derived tables (aka "materialized views" or subqueries) in Looker (and even write them to the database as physical tables), there is a discussion of when to create derived tables in Looker, and when to move those derived tables to the ETL. Check out this JOIN 2017 - To Use or Not Use PDT’s article and video on Youtube.
This content is subject to limited support.