Visualizing Data to Make Data-Driven Decisions with Looker Studio

Visualizing data helps business stakeholders concentrate on important KPIs and empowers them to make data-driven decisions. Data engineers need to analyze the underlying structure of the data and curate custom reporting layers on top to enable the development of dashboards and reports.

In this chapter, we will discuss the dashboarding product Looker Studio in detail, which can be leveraged to visualize data coming from different sources, including BigQuery, to build compelling reports. On top of that, we will learn what a data engineer should see from a data visualization point of view.

At a high level, here is a list of content that will be covered in this chapter:

• Unlocking the power of your data with Looker Studio
• From data to metrics in minutes with an illustrative use case
• Understanding how Looker Studio can impact the cost of BigQuery
• Creating Materialized Views and understanding how BI...

Technical requirements

Before we begin this chapter, make sure you have the following prerequisites ready. In this chapter’s exercises, we will use two Google Cloud Platform (GCP) services – Looker Studio and BigQuery.

We will use the data output from the exercises in Chapter 3, Building a Data Warehouse in BigQuery, or Chapter 4, Building Workflows for Batch Data Loading Using Cloud Composer. In both chapters, we produced a BigQuery dataset called dwh_bikesharing. Make sure you’ve finished the exercises in those chapters.

Unlocking the power of your data with Looker Studio

Looker Studio is a tool for you to visualize your data fully on the cloud. There are two main reasons why we need data visualization; the first is exploration, while the second is reporting:

• AqaaAAQ

  As a data engineer, even though visualizing data is not your main responsibility, there are times when visualizing data may help in your job. For example, at times when you need to optimize your data pipeline, you may need to analyze a query job’s performance. Visualizing the job’s data will help you get more information. If you have a data science background, you may be familiar with tools such as Jupyter Notebook. It is a fully-fledged tool for data analytics, including visualization for exploration. But in our case, we may only need to quickly visualize a bar chart from a BigQuery table, for example. In that case, Looker Studio is the best option due to its simplicity and seamless connectivity to BigQuery.

• Data...

From data to metrics in minutes with an Illustrative use case

Now let’s start our exercise – we will try to use data visualization for exploration and reporting. You will only need Looker Studio and BigQuery as the data sources for the exercise in this chapter.

We’ll perform two main tasks in this section:

• Exploring the BigQuery INFORMATION_SCHEMA table using Looker Studio
• Creating a Looker Studio report using data from a bike-sharing data warehouse

Before trying out Looker Studio, let’s become familiar with INFORMATION_SCHEMA in BigQuery.

Understanding what BigQuery INFORMATION_SCHEMA is

INFORMATION_SCHEMA is a collection of tables in BigQuery that stores your BigQuery metadata. For example, if you want to know how many tables exist in your project, you can use the table INFORMATION_SCHEMA view. The other common example is that you might be wondering how much a query costs per job, day, or month. For that, you can access the...

Understanding how Looker Studio can impact the cost of BigQuery

At a very high level, the total BigQuery cost is driven by how big your data is and the amount of usage. Both factors work as multipliers. For example, if you have a table that’s 1 TB in size and you access the table 10,000 times in a month, it means the BigQuery cost will be 1TB x 10,000 x $5 = $50,000 / month.

Whether $50,000 is expensive or not depends on your organization. But we will ignore the context and focus on the cost driver aspects, so let’s say $50,000 is expensive. Now, the questions are, what kind of table could be 1 TB in size? and how can a table be accessed 10,000 times in a month? We’ll discuss these questions in the following sections.

What kind of table could be 1 TB in size?

To answer this, let’s take a look at our data warehouse diagram from Chapter 3, Building a Data Warehouse in BigQuery:

Figure 7.24 – High-level data layers...

Creating Materialized Views and understanding how BI Engine works

BigQuery has a feature called Materialized Views. It’s not a table, nor a view; it’s a materialized view. To understand it, let’s go back to what a table is compared to a view. One of the reasons you create tables is that you want to store transformation results to be used for downstream usage. The reason you create a view instead of a table is that you need the data in real time, but with a view, you always pre-compute all the processes. A materialized view is somewhere in between. With Materialized Views, you can have real-time access, but the processes aren’t pre-computed.

It’s easier to understand by trying it in practice, so let’s set up a scenario:

1. Create an aggregation query in the BigQuery console.

   Let’s use our facts_trip_daily table and run this query from the BigQuery console:

   SELECT trip_date, sum(sum_duration_sec) AS sum_duration_sec

   FROM `packt-data...

Summary

In this chapter, we learned how to use Looker Studio using BigQuery as the data source. We learned how to connect the data and create charts and reports for sharing it with other users.

Throughout the exercises in this chapter, we learned not only about how to create charts but also the point of view of our end users. In the exercises, we realized how important it is to create a proper data model in our datasets. Imagine if our tables didn’t have proper naming conventions, weren’t aggregated properly, or followed any other bad practices that can happen in a data warehouse. Since we already learned all the good data engineering practices and code from the previous chapters, it’s now very easy for us to use our example tables to visualize things in Looker Studio.

And lastly, as data engineers, we need to be the ones who understand and are aware of the cost implications in our data ecosystem. In this chapter, we learned about BigQuery INFORMATION_SCHEMA...