Perfect complements: Using dbt with Looker for effective data governance

I want to talk about dbt and Looker today and make the case for why I think they work so well together, both in features and what they bring to your data team, with a focus on governance and, in particular, on data trust, which I believe will be a common theme. I'll return to this point without sacrificing agility. So, with that, I'm going to jump in and introduce myself a little. I'm Jonathan, or JB's fine. I lead solutions architecture at Four Mile.

We're a full-stack data consultancy. We partner with many individuals and organizations looking to deploy solutions on Looker and dbt, often in combination. Our focus is on building solutions within the modern data stack, which typically includes dbt and Looker. This is a topic I am really passionate about. We have collectively observed numerous patterns—some that work well and others that do not. There is a lot to discuss today, and I hope we can have many great conversations in the Slack channel as well.

Here's a revised version of your text with the correct capitalization and spelling: "A little bit more on the background context to this: I was thinking about it, and I've completed around 150 Looker launches or expansion projects at this point. I started to consider why this number was significant, and it occurred to me that 150 is also the number of Pokémon from Generation One. So, what would a Charizard Looker instance look like, or, you know, what would a Pikachu data stack look like? Right? I'll come back to that."

Actually, there's a connection to that, which I'll come to, but I'm also a bit of a utility person at Four Mile. Sometimes I'll be doing LookML development in Looker, sometimes it's dbt modeling, and other times it's data integrations or data warehouse design and solutions architecture. So, I get to wear a lot of hats, which is a fun place to be when talking about something like this, and generally just helping folks deploy these tools together.

And I think I have some hopefully good arguments, as well as a few things to say about why I believe that dbt and Looker work really well as perfect complements in the data stack. Here’s a loose agenda for the day. I'm going to go through a few common challenges that folks encounter, primarily from the Looker perspective, about why dbt can be effective. From there, I’ll dive into why dbt and Looker work so well together and how Looker can actually be a great complement to dbt for analytics engineers. Next, I’ll discuss the roles of dbt and Looker, particularly in the data stack, highlighting where they are similar and where they differ. I will talk about the symbiotic benefits of their integration. In particular, I want to focus on some frequently asked questions, which I’m especially excited about because there’s a lot of information there, and many folks have questions regarding this topic. Ultimately, I’ll wrap up by discussing how to build a strong data culture, which serves as the overarching connective tissue that brings it all together in the end.

So, I’m going to dive into a common challenge. We will look at a fairly common issue that folks using Looker may face, which makes adding dbt (data build tool) to your stack kind of compelling. As I mentioned, this will be focused from that perspective. Our case study will serve as a representative example. It's a global software development firm, and they started out with a smaller data team. Over time, they began to recognize the value of analytics. However, up to this point, they have mainly been generating reports manually, often relying heavily on Excel.

And then they started using Looker. It began a couple of years ago when their data team was fairly small, and they were integrating just a few data sources together, perhaps on their Snowflake-like data warehouse. This is a picture of what that stack might have looked like: They had an ingestion tool, similar to Fivetran, possibly Snowflake or maybe BigQuery as their data warehouse, and then Looker as their BI tool, working off the ingested data coming in from their data integration tool. If I jump ahead now a little bit, we can note that in the past two years, the company has grown very quickly, and now multiple departments rely on this data.

You know, Looker has been really effective for this. When the team started using Looker, they implemented a great approach: quickly adding value by integrating views into their explores. For those who aren't familiar with the terminology, I'll explain that a bit more in a minute, but essentially, they built dashboards very quickly by creating views from raw data. In a rapidly growing team, we often see new use cases, new features, and new tables in their database. With this pattern, they can just add a view, add a view, add a view, and integrate it into their explore. This is really cool; however, sometimes explores can become quite large.

You might have over 30 views in one Explore, with many fields having the same name. This can become quite unintuitive over time, especially for your business users, because there is just so much information available. In the past, the interface may have been clearer, allowing users to self-service and answer their own questions, which is a fantastic feature in Looker. However, they might now experience slowness on the dashboard due to all those joins, leading to a generally poor experience. No self-service means that everything becomes a manual request for the data team, and I was thinking, "Oh, this is definitely a scary, Hunter-esque Looker instance; this is not what we’re searching for." That’s my impression of that situation. To generalize, Looker allows for very quick time to value and fast iteration. What this sometimes leads teams to do is skip the warehouse design step, particularly newer and smaller teams with just a few use cases. They might start by simply adding views to Explorers whenever they need insights and new tables. Over time, they may find that Looker's PDTs (Persistent Derived Tables), a way to write data back to your warehouse as SQL, can be somewhat limited.

It does have its strengths; however, it also comes with some limitations. There are certain things you can't do with it, which we'll discuss shortly. To start, let's consider a representative set of views that were initially built to explore data. This set is designed to create a self-service interface that a user could likely understand through a simple Entity-Relationship Diagram (ERD). As time goes on and things expand, users begin to develop explorers and features in Looker based on something that resembles a more complex ERD. This may, in fact, just be a subset of a large ERD.

And some folks don't have an ERD at all, right? But they just have tons of tables they need to model. Again, this can happen because integrating data in Looker is so easy, allowing for great agility. However, it does have its problems, as I highlighted. By the way, if anyone has seen this sort of thing before, I'd love to hear about it in the Slack channel because it's interesting to see how similar patterns emerge and what works well and what doesn't.

So feel free to have that conversation there. And again, as people reach this point—I may have mentioned this earlier—sometimes business users just have to request data. Rather than being able to build things, they find themselves in a request-oriented process, which is definitely not ideal. So perhaps another tool can help us with our transformations. I want to make the case, as we compare and contrast the tools, that DBT is really great for this purpose.

And we'll kind of go into, again, that perspective of dbt to Looker too. But right now, I want to highlight the roles of dbt and Looker and what they're doing. First, we'll talk about where they differ. These are primarily core components of each tool and definitely not fully representative. dbt is great for complex transformation pipelines or DAGs and in-warehouse transformations in general, serving as your ELT or T&ELT tool. It allows you to do impressive things like document your database and your sources, surface visibility through database documentation via dbt's docs, define source freshness, and conduct data and schema tests. Additionally, it lets you materialize your transforms in interesting ways, like incremental builds and ephemeral builds, to modularize and ultimately serve as a fantastic tool for designing your data warehouse and building transformations on it.

And then Looker’s primary components are self-service BI and Explorers, which is, again, the self-service interface for diving into your data. Curated dashboards, your business data model, which is your LookML—it's kind of the language of Looker. Additionally, it supports features like embedded analytics, custom data experiences, alerts, and data deliveries, all of which help users scale effectively and leverage their data in meaningful ways. However, what’s particularly compelling is where these elements coalesce—yes, see what I did there—and to discuss some of those similarities. First of all, we have version-controlled business logic.

This is huge, right? And we'll come back to some of those similarities. First of all, we have version-controlled business logic. This is significant, right? We'll revisit some of those analytics engineering principles shortly, as well as those topics in that area. But for now, version-controlled business logic is fantastic.

We're able to be careful with what we release and all that good stuff. SQL and YAML, or YAML-like analytics data modeling—YAML-like in the case of LookML—are very similar. I will make the case for how they're essentially alike in a good way, not in a way that suggests they're slightly different and therefore confusing. They both have isolated development environments: dbt has your development schema, and Looker has development mode, which allows you to test dashboards and changes in your LookML quickly. They both give you the ability to write really DRY code.

So, you know, between things like dbt's ref statement and Looker's pointers to other dimensions and metrics, you're not redefining logic over and over. You can have a good source of truth. Generally, again, I'm hoping to build towards the idea that these both fuel good analytics, engineering principles, and philosophy, which really stems from dbt and the Fishtown community. I think a lot of those pieces fit into Looker very well as well. I hope to connect that tissue here shortly, and we'll do so by discussing their symbiotic benefits.

And, you know, in order to highlight their symbiotic benefits, I think it's helpful to dive into some values of analytics engineering or some of the philosophical components. These are based on a blog post by Tristan, which we’ve generalized a bit to fit into the BI space. It's kind of like a four-mile version of them, but you’ll definitely recognize these if you’ve seen them. 1. Code, not graphical user interfaces, is the best abstraction to express complex analytics logic. 2. The business data model should be adopted by data analysts who use similar practices, standards, and tools as software developers. 3. Critical analytics infrastructure should be controlled by its users and made available to analysts, rather than hidden behind complex pipelines. 4. Analytics teams should be able to quickly iterate and expand to new use cases without sacrificing data integrity and trustworthiness. Now, again, many of you may have seen these before, but if we reflect on the prior slide, a few things probably come to mind about how we’re fulfilling these philosophical components, as these tools work together. They both utilize code to represent potentially complex business logic rather than relying on graphical user interfaces.

Both allow you to do cool things that software developers might do when they're building projects, such as defining tests, having a separate development mode, and establishing a process to promote changes to production, among other tasks. Neither kind hides things behind complex pipelines, and I will showcase that in a moment. Generally speaking, they both provide the ability to see the journey from source to destination and how we reached that point, which is essential for data analysts, analytics engineers, and analytics teams to move quickly without sacrificing trustworthiness. That is incredibly important, and it's the central theme or motif here. I want to make the case and demonstrate how that's the situation shortly.

So, excuse me. Let's say I want to connect to those principles, particularly the connectivity between elements and not hiding things behind complex pipelines or isolated environments. DBT is excellent at showing you where your source data comes from and what is being done to transform it into your analytics-ready tables or data marts. I want to do something similar with Looker, but I actually want to move backward to showcase how we create that overall connective tissue in a compelling way. So, this is a Looker dashboard, and let's say we are looking at this here.

As a business user, I want to examine how this e-commerce funnel was built. In the top left image, I can click on the ellipsis in the top right corner of the tile and select "Explore from here." This action will take me to the "Look" or "Explore" interface, which is located in the bottom right. It will show me specifically which dimensions or metrics were used and the filters that contributed to creating that tile. This way, I can identify the components or ingredient parts that helped me build this information.

Now, let's say I want to look at one of these metrics in particular, like, what is this "All Sessions" thing? There's a little gear you can click on that says "Go to LookML." Now, on the left here, I can see LookML. For those who haven't seen it, it's not different from something like YAML. I've defined the measure called "All Sessions" and applied some semantic enhancements to it, like a nice label. You can also see specifically what formula I'm using to get there. Now, let’s connect it. I can take that measure, "All Sessions," and I can go into DBT to see the table that this view is built on top of. What is the description? What is the definition? What tests and metrics are associated with this? I can jump into my DBT documentation and see my "All Sessions" measure there.

I can see the lineage in dbt, of course, that got me there. I can ultimately open up the documentation on the bottom right of the source table, where it was initially defined, along with the ingredients that guided me there. With Looker, you have a really neat way of extending from the source data to the analytics output, all the way to your dashboard endpoints. There's a nice connection there that doesn't hide information behind complex pipelines and provides us with a sort of full-stack ability to use these modeling layers and tools to see what we're building from our source data. This results in great visibility. Now, connecting back a little bit to our case study, what we ultimately recommended for these folks was to start using dbt and Looker together. This involved their process as they transitioned their transformation logic, mainly in some of their dimensions and persisted derived tables, into dbt. They still use Looker for the semantics layer and for those self-service explorers for their business users. There was also a shift within the data team, which began to focus more on requests and building LookML each time.

Now they started adopting some of those software development practices through dbt and Looker, building components in dbt and considering testing upfront. They really embraced testing in particular, which we've seen as a good pattern. Along the way, they recognized, "Oh, we're building a warehouse here." So they also thought about dimensional modeling, and ultimately they saw huge performance improvements and actual warehouse cost reductions by avoiding massive join queries from those explorers. A significant impact of this change, which my FAQs will address, is that this analytics team was able to break the cycle of constant fixes and administration. They gained visibility into their transformations, which was somewhat difficult in Looker, and began to focus more on new use cases. Their former use cases weren't introducing unexpected issues as frequently, and their LookML was cleaner.

So they could actually build a cleaner semantic layer and a really nice self-service interface, allowing the whole organization to be more data-driven. You could say that they are now the trustworthy performance of self-service analytics. Now, regarding this question, since we're not in a live format, I wanted to simulate some of the most common questions that we have received in the past about combining these tools. Again, I am really excited about this session.

There are some good, maybe even controversial components here that we can keep the conversation going on. So, here's a big one: if my data team is using Looker today and we want to move our transforms to dbt because we're interested in using dbt in a complementary fashion, how much of a lift is this? Well, I'm definitely happy to say that, in general, the process is not bad at all. The initial lift and shift of SQL takes more like a count of days as opposed to weeks for most teams. From there, what folks usually do is think about an additional optimization phase to use the tools most effectively. What that usually looks like in dbt is you then start to do additional documentation and tagging. You might also introduce incremental and ephemeral models, allowing for more modularity. This way, you can test your individual components rather than having large, complex subqueries and CTE transforms that are hard to understand.

And then, of course, other things like snapshotting and gender usage can be great additions to your DBT. Where should I define my column descriptions? This seems like a small question, but it's a fair one. If both tools have similar features, what should I do? Right.

But it kind of depends a little bit. However, there is a good mental model to evaluate these things. Do I need to update my descriptions in two places for any change? No. With something like this, let's go upstream.

So let's use dbt to define our model, our column descriptions, and so on. Ultimately, we can pass that metadata to our Looker instance and supplement it in Looker as needed. Some people who want to do this will recommend features like Looker's refinements or extensions, which are more advanced topics; however, they essentially allow you to build on top of an existing model. This means you can pass through description changes while still enhancing those elements with additional semantic layer components you wish to implement on top of your dbt setup, and so forth. By the way, Looker's "Create View from Table" feature currently does not pass through descriptions, but I have an open-source option that is more generalized, which people can use to address this issue if it makes sense. So, keep an eye out for that.

And then, should any business logic or transforms live in Looker? This is a good question. In general, I would say transforms should live in dbt. You can imagine if we're trying to build a warehouse source of truth, it's really nice to have it all there, right? Especially to serve other potential data consumers, right?

If we have data scientists using Jupyter Notebooks for their outputs, it's great to have a source of truth there. However, it's important not to have to remember which tool we implemented that logic in. That said, there are a couple of exceptions to point out.

Potentially, if you have anything dynamic in Looker, this is a good case to consider. With Looker, you have features like parameters and templated filters, which are more advanced topics. If a user's specific choice in a dashboard should change the way we query a table, and it makes sense to implement that query change in Looker, then that's a good use case for including some of that logic in the drive table and SQL within Looker. Other use cases might include early testing for new scenarios where you want to quickly test against dashboards but then promote it back to dbt. Additionally, dashboards and data alerts based on source or staging data are other examples. I mention access grants for both of these cases because a valuable governance tool in Looker is the ability to establish access grants, which limit certain audiences' access to specific dimensions, metrics, explores, or joins.

You can basically say, "Oh, my data team can go in and do early testing, and it doesn't affect the user experience of business users." Or, you know, we have an alerting system that is close to our source data that our data team works with to ensure certain things are as expected. That's an additional aspect that you might use access grants for and have in Looker. It seems like we want our business logic in DBT.

However, oh man, testing and using development mode and writing PDTs in Looker was so quick. If I have a new analytics use case, do I start developing in dbt or do I start developing in Looker? This is another good question. It's sort of an "it depends" one. I think there is a good, simple process you can define as a team for this answer.

But generally, I say for you, use cases where you want to make a quick change and test right away against the dashboard, Looker is a solid option. Think about a basic transformation to a dimension you already have that you want to make available, and ensure your dashboard looks how it should. That's a good use case, right? Or, you know, potentially some kind of convenience metrics that you want people to be able to self-service with. But for new datasets and more complex tasks, I would say starting with DBT is a good option.

Again, it's just a great place to define your logic, and there are additional integrity checks. It's also great for your data team to become really familiar with and optimize the use of this. Here are additional points; I will paste them in. There are a bunch of good questions here, and the first one could likely be a discussion on its own.

I'd be happy to answer some of those questions, but I did want to bring up some other common points we've seen here and keep the conversation going. Finally, in the last couple of minutes, I want to discuss building a strong data culture and how these tools connect to that. You may have the wheels spinning on this, but concepts like data trust—so users can rely on what they see—are crucial. Additionally, user experience-focused data democratization, self-service analytics that allow people to find their own answers, and agility to respond to new challenges are all significant factors. I truly believe that Looker and DBT combine to serve as the fuel for this data-driven culture and the trust it fosters.

And they really have symbiotic components that we've seen work in real instances, which can help folks actually surface analytics, even external analytics, to customers. That scales and is truly trustworthy. In this space, it seems to be a really valuable set of tools that work together. From the dbt perspective, Looker adheres to many principles of analytics engineering, and learning LookML is natural for analytics engineers. On the other hand, from the Looker perspective, managing transforms in dbt adds a ton of visibility and governance benefits. Furthermore, SQL and YAML will be very familiar to LookML developers.

As a data team, we are expanding our tools, which makes things more interesting and provides more opportunities for learning for our team members. Additionally, from a combined perspective, we are enhancing governance and enabling self-service in a trustworthy manner while maintaining performance. We are building a strong data warehouse that allows business users to feel confident in making data-driven decisions using a stack that effectively combines the best features of both worlds. We are leveraging the strengths of each component in a way that complements one another. There are tools that work well together, and others that do not, but I firmly believe that these tools do complement each other in a highly effective manner.

And I’d love to keep that conversation going, but thank you all so much for your attention. I appreciate everything about the conference once again, and let's definitely continue the conversation in the Slack channel.