GCP Data Engineering · Chapter 07

Visualizing Data with Data Studio (now Looker Studio)

Dashboards are the end of the pipeline: the part everyone else sees. Data Studio (now called Looker Studio) makes them nearly effortless, which is exactly why they can quietly multiply your BigQuery bill.

0 of – checks answered

Two reasons to visualise

Visualisation is not usually your main job, but you will do it for two different reasons, and they need different amounts of care.

Find answers, ad hoc, for yourself

A quick look at your own data: a bar chart of pipeline job durations, or a time series of the bytes you have been billed for. Speed and low friction matter more than polish, and Data Studio’s seamless BigQuery connection is what makes it good at this.

Audience: You, or your team
Effort: A few clicks from a table or query result
Data engineer use: Analyse pipeline performance, cost, data quality

01 You want a quick bar chart of yesterday’s pipeline job durations to find the slow one.
02 Regional managers need a weekly dashboard with a clear layout, shared by link.
03 You want to see how much you were billed per day on BigQuery this month, once.

Data Studio vs Looker

Two Google products with similar names and very different weight. Choosing well saves effort.

Simple, free, cloud dashboards

A fully managed visualisation tool in the browser. Free to use, hundreds of connectors, drag-and-drop reports. Excellent for exploring BigQuery data quickly.

Strength: Simplicity and BigQuery connectivity
Limits: No permission hierarchy or version control like Looker
Cost: Free, but your BigQuery queries are not

How a dashboard multiplies your BigQuery bill

On-demand BigQuery cost is roughly bytes scanned times price. A shared dashboard multiplies it twice: by the number of users and by how often each one refreshes. Play with the numbers.

Table size (GB) Users Refreshes per user per day Price per TB scanned ($)

Queries per month
Data scanned per month
Estimated monthly cost
Verdict

The book's default is 1 TB, 10 users refreshing every half hour (48 a day): about 14,400 scans and a large bill. Prices change and vary by region (the book used $5 per TB; check current pricing). It also ignores the monthly free allowance and any caching.

Big multiplier check

01 A 1 TB table sits behind a dashboard that 10 people each refresh every half hour.
02 A large table is queried by only a handful of analysts, a few times a month.
03 Hundreds of people use dashboards built on several small, aggregated tables.
04 A dashboard for hundreds of people is connected straight to a raw transactional table.

The cure is architecture: raw, warehouse, then an access layer that is already filtered and aggregated. Point dashboards at the access layer, and you avoid having both big multipliers at once.

Measuring your own spend

BigQuery keeps its metadata in INFORMATION_SCHEMA views, including one for jobs. Multiply total_bytes_billed by the price per byte to get a query's cost, then group by day to see a trend. Charting that in Data Studio is a good first exercise.

Caching to cut the bill

Two BigQuery features reduce repeated processing. Both help most when the same dashboard queries run over and over.

	Materialized view	BI Engine
What it is	A precomputed, automatically maintained result of a query you define	An in-memory cache for BI tools
You do	Create it with SQL	Enable it and reserve memory capacity
Caches	Aggregations, filters and inner joins you define	Almost any query result, within capacity
Limits	Restrictions on which queries qualify (check the docs)	Bounded by the memory you reserve, which costs money
Viewer changes	None: BigQuery uses it automatically	None: dashboards just get faster

Table, view or materialized view?

01 Store a transformation’s output so downstream users read stored rows, refreshed by your own job.
02 Always-current results, but you accept the query being recomputed every time it is read.
03 Always-current aggregate results, with the heavy computation precomputed so far fewer bytes are processed.

In the book's experiment, an aggregate query that processed about 14.9 KB dropped to about 64 bytes once a materialized view existed. Tiny numbers here, but the proportion is the point at scale. In Data Studio, a lightning icon on a chart shows it is using BI Engine.

Hands-on: explore, report, then measure

You need the fact and dimension tables from Chapter 3 or 4, or any BigQuery tables. Looker Studio is free, but your queries are not.

Explore cost history with INFORMATION_SCHEMA

Run this in the BigQuery editor and scan the columns. Change the region to match yours. total_bytes_billed is the interesting one.


						SELECT EXTRACT(DATE FROM creation_time) AS creation_date,
       SUM(total_bytes_billed) AS sum_total_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
GROUP BY creation_date;

Click Explore Data and draw a time-series chart
Choose the date as the dimension and the summed bytes as the metric. Add a date filter. You will not get a smart guess; you must set the inputs.
Create a report from a BigQuery table
Create a report, pick the BigQuery connector, then your project, dataset and the fact table.
Add the dimension table and blend the two
Add the second data source, blend them on the station id, and choose which fields are dimensions and which are metrics (sums and averages) from each.
Build three charts
A bar chart of the top five stations by total duration, a tree map of the top ten by average duration, and a table with duration and capacity per station. Limit rows in the chart settings.
Lay it out for someone else, then view it
Add a title, group charts sensibly and check the view mode. You are designing for the reader, not for yourself.
Share it, and note the data credentials
Share by email. Recipients can see the report without BigQuery permission when it uses your credentials.
Measure a materialized view
Turn off "use cached results" in query settings, run an aggregate query and note the bytes processed. Create a materialized view of it, rerun the same query on the base table, and compare.
```
						CREATE MATERIALIZED VIEW `dwh_bikesharing.fact_trips_daily_sum`
AS
SELECT trip_date, SUM(sum_duration_sec) AS sum_duration_sec
FROM `dwh_bikesharing.fact_trips_daily`
GROUP BY trip_date;
					
```
Look at BI Engine, but do not create a reservation
Find it under BigQuery administration to see the configuration, then close the page without confirming. Check for the lightning icon on charts in a real deployment.

Key terms

Exploration: Ad hoc analysis for finding answers.
Report: A layout of charts curated for other people.
BI: Business intelligence: the reporting side of data, usually a separate team.
Blend: Join two data sources inside the visualisation tool.
INFORMATION_SCHEMA: BigQuery metadata views, such as jobs and their billed bytes.
Access layer: Filtered, aggregated tables built for reports.
Materialized view: A precomputed, auto-refreshing query result that BigQuery can reuse.
BI Engine: In-memory acceleration and caching for BI queries.

Check yourself

Pick an answer to see the reasoning straight away.

Why should a data engineer care about how dashboards are built?

BigQuery cost is driven by data size times what?

Where in the layered warehouse are tables least likely to be huge?

A report is shared with a colleague who has no BigQuery access. What decides whether they see data?

What is a materialized view for?

What must you remember about BI Engine while practising?

Study notes in my own words, following the structure of Adi Widjaja's Data Engineering with Google Cloud Platform (Packt, 2022). GCP changes quickly, so check each service's current documentation before relying on names, prices or console steps.