Chapter 7: Visualizing Data for Making Data-Driven Decisions with Data Studio

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

This chapter will discuss in detail the dashboarding product Google Data Studio, which can be leveraged to visualize data coming from different sources, including BigQuery, to build compelling reports. And 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 Data Studio
• From data to metrics in minutes with an illustrative use case
• Understanding how Data Studio can impact the cost of BigQuery
• How to create a materialized view and...

Technical requirements

Before we begin this chapter, make sure you have the following prerequisites ready. In this chapter's exercises, we will use these Google Cloud Services (GCP) services – Data Studio and BigQuery.

We will use the data output from exercises in Chapter 3, Building a Data Warehouse in BigQuery, or Chapter 4, Building Orchestration 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 from those chapters.

Unlocking the power of your data with Data Studio

Data 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 and the second is reporting: 

• Data visualization for exploration

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 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. And in that case, Data Studio is the best option due to its simplicity and seamless connectivity...

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 Data Studio and BigQuery for the exercise in this chapter. Note that using Data Studio is free. 

There will be two main things that we will be doing in this section:

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

Before trying Data Studio, let's get 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 you have 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. To do that, you...

Understanding how Data 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 1 TB 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? Let's discuss these questions in the following sections.

What kind of table could be 1 TB in size?

To answer that, let's take a look at our data warehouse diagram from Chapter 3, Building a Data Warehouse in BigQuery, in the following figure:

Figure 7.25 – High-level data...

How to create 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 is by trying it in practice, so let's set up a scenario. 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-eng-on-gcp.dwh_bikesharing.facts_trips_daily`...

Summary

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

Through the exercises in this chapter, you have learned about not only how to create charts but also the point of view of your end users. In the exercises, you realized how important it is to create a proper data model in your datasets. Imagine if your 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 Data 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...