Author: David Badovinac, Senior BI Developer, Datatonic
Datatonic have recently partnered dbt labs, to enable seamless BI transformation for clients by efficiently connecting their data warehouse and enabling BI teams to transform their data into a format that serves them the best.
With market leaders such as Sky, Inmarsat, Pets at Home, and MoneySupermarket choosing BigQuery for their modern data warehouse, dbt + Looker are fast becoming the go-to data transformation tools to complete the modern-day data stack.
Clients often ask us: How do dbt and Looker work well together? What are the benefits of using the two tools together? When should you model data in Looker vs. dbt?
In this article, we dwell deeper into dbt and Looker, how they work together, and how they complement BigQuery.
dbt (see more here) is a data modelling tool combining software engineering best practices and analytics best practices to build modular models that can be easily reused across the business and used as the source for your BI needs.
dbt allows you to:
dbt is open-source, so engineers own their code and have complete control over it.
Looker is a leading enterprise data platform for business intelligence, enabling embedded analytics and driving and self-service analytics. It is completely cloud-native, extensible, and fully web-based with a full API First extensibility.
LookML, a powerful data modelling layer that sits behind Looker, enables you to:
Looker also has powerful security features that allow you to implement columnar and row-level security on top of limiting the data based on the end-user needs. Find out more on implementing security in Looker (see more here).
Now that we have covered a brief overview of the tools, you might have noticed that some of their functionalities overlap. Indeed, clients often ask us why and when it makes sense to use Looker and dbt together.
To answer “why”, we take a look at how Looker and dbt complement each other in the following ways:
To address when to use these tools together, it’s important to note that both Looker and dbt serve a specific purpose when building a modern BI stack.
Overall, Looker is a great tool to get started as quickly as possible. With its powerful modelling layer, you can connect all your source tables directly to Looker, potentially cutting back on your data warehouse design. When BI teams want to get up-to-speed quickly, modelling within the tool itself enables them to do this by reading directly from Looker’s staging or source tables. Many analysts opt to build all of their business logic in Looker, such as:
All of this can be done in Looker, however, it’s not a scalable solution. You will be required to decouple various layers of code and logic to apply changes each time the underlying data changes. This can lead to a significant overhead cost for your analyst, and a very large, complex codebase of LookML difficult to navigate and understand.
Let’s look at an example: A company just acquired Looker and they want to get up to speed as quickly as possible to be able to serve their end-user, building all the self-service layer, and ultimately, the content being consumed. This will result in a lot of business logic created in the LookML layers (see below).
This is a great way to get started and it works perfectly within the Looker application. The limitation arises if you wanted to use this query outside of Looker, for example:
You can prevent any of the above limitations created by Looker by shifting this logic to dbt. It works the same way as if you were to take your derived table queries, and break them up into dbt models, creating modular queries. Adding modularity allows you to be more flexible with your models, meaning they can be reused in various locations. They are easier to read, and you can see how everything is connected through DAGs.
Therefore all your transformations and logic is sitting in your warehouse, creating one single source of truth accessible to everyone.
Overall, combining the power of dbt and Looker allows you to build a robust, scalable warehouse layer, so your analysts can focus on delivering an amazing user experience in the front-end for data consumers.
One of the common questions that arise from integrating these two tools is: What gets modelled where?
dbt has written an insightful article around this going into more detail, but here is our recommendation:
BigQuery is a scalable modern data warehouse, with real-time capabilities. It is quick and easy to integrate and has unique activation and analytics capabilities via Looker and dbt (e.g. BigQuery ML). We cover more about harnessing the power of BigQuery with Looker in this free online workshop.
BigQuery ML (BQML) integrates seamlessly with Looker and dbt such that data from trained models never leaves BigQuery. This removes the need for additional data pipelines and management and ensures faster delivery of insights to the customers. One of our favourite features is the model descriptions in dbt that flow through to BigQuery and Looker, so once it’s defined, it’s accessible across all tools. To find out more about using BQML, have a look at our how-to blog.
As Looker’s go-to partner in EMEA, Datatonichave worked with industry-leading clients such as Pollen, Pets at Home, MandM Direct, and many more, helping them transform their data and BI capabilities and enabling self-serve analytics across the organisation. If you’re curious how we could do the same for you, request one of our BI Jumpstarts – our two-week programme to put your data into action with Looker.
Know exactly where and how to start your AI journey with Datatonic’s
three-week AI Innovation Jumpstart *.
* Duration dependent on data complexity and use case chosen for POC model
With your own data sets, convince your business of the value of migrating your data warehouse, data lake and/or streaming platform to the cloud in four weeks.
With your own data, see how Looker can modernise your BI needs
with Datatonic’s two-week Showcase.