I’m working with a manufacturing client that’s trying to scale its data exploration, analytics, and semantic modeling capabilities.
They’re heavily invested in Looker, with multiple instances deployed across the organization, all sourcing data from core operational systems.
So far:
Exploration: users are very happy with it.
Analytics: generally positive feedback, although some teams have pointed out inconsistencies and a lack of structure in parts of the underlying data.
Semantic/business modeling: relatively new territory for the organization.
The question we’re wrestling with is whether LookML and the Looker semantic layer are the right foundation for building and maintaining a large catalog of governed business entities aligned with the company’s internal ontology.
The current direction under consideration is to model these entities through multiple layers of PDTs built on top of relatively raw source data (ingestion volumes are in the low-to-mid GiB/day range, with PiB of historical data).
My understanding is that Looker’s semantic layer is primarily intended to provide a governed layer of dimensions, measures, relationships, and business definitions, while PDTs are mainly a mechanism for materialization, performance optimization, and intermediate transformations. That makes me wonder whether a deep hierarchy of PDTs is the right long-term approach for enterprise-scale business modeling, particularly from a maintainability and governance perspective.
Am I misunderstanding the intended role of PDTs here? Has anyone successfully used LookML + PDTs as the primary mechanism for modeling enterprise business entities at scale? I’d be especially interested in hearing about where people draw the boundary between transformations upstream/in the warehouse and transformations inside Looker.
That is an accurate summary of the role of PDTs. They are primarily mechanisms for improving performance. We also use them for altering the ‘level of detail’ / ‘calculation context’ e.g. calculating total lifetime spend at the user_id level. And we use aggregate tables to improve query performance / reduce cost (aggregate tables = a type of PDT).
Looker’s own documentation cautions here against using PDTs unless you absolutely have to.
I may have misunderstood your post, but it sounds like the question is more whether to model data in the database vs Looker, rather than LookML vs PDTs.
Here is a very general rule of thumb I follow for deciding where to model data:
Use database for:
Scalability: when you are working with large volumes of data
Reproducibility: when you are using multiple downstream tools to visualise the data (i.e. not just Looker) / the business logic is set in stone
Performance: when you are doing complex transformations - let the database do the heavy lifting.
Use Looker:
Permissions: when you do not have database privileges / engineering resource to create views in the database
Agility: when you are going through the trial and error phase
Flexibility: when the business logic changes frequently
Optimisation: use aggregate tables to optimise frequently-run queries
This is exactly my views as an architect. The link you’re pointing is gold to defend this. I missed it when building my tech recommandation, this is exactly what I needed.
Any other insights ?
Another cons of doing heavy-lifting in Looker is that any change in the SQL defining a PDT will trigger a full rebuild of the whole PDTs cascade at next trigger+regenerator pass, opening door to uncontrolled full backfills. No partial backfills in Looker, and no schema drift support…