GCP Data Engineering · Chapter 10

Cost Strategy in GCP

Stakeholders always ask what a solution will cost. This chapter gives you a way to answer: three pricing models, a method for estimating a whole architecture, and the two BigQuery features that most often shrink the bill.

0 of – checks answered

Three ways GCP charges you

Every service prices differently, but they fall into three families. Knowing which one a service belongs to tells you what to put in the calculator.

You pay for machines while they exist

Billed by how many machines, how big they are, and for how many hours, whether or not you use them. A Composer environment nobody touches still costs the same as a busy one.

Formula: Machines × size × hours
Data services: Cloud Composer, Dataflow, Dataproc
Lever: Turn it off, size it down, make it ephemeral

01 A Cloud Composer environment sat idle over the weekend, and the bill still counted every hour.
02 Cloud Storage is billed by the total gigabytes you keep.
03 You reserve a fixed amount of BI Engine capacity for a month.
04 Pub/Sub cost depends on how many bytes are published.

BigQuery: on-demand or flat-rate?

BigQuery is the one service where you choose the model for analysis. On-demand charges for bytes processed by each query. Flat-rate charges for slots (its smallest processing unit) whatever you run. Change the numbers to find where the lines cross.

Table size (GB) Number of such tables Users Full-table queries / user / day On-demand $ per TB Slots you would buy Flat $ per slot per month

On-demand, per month
Flat-rate, per month
Cheaper here
Break-even

The book's defaults reproduce its example: one 10 GB table, 100 users querying it once a day is about $150 on-demand against $4,000 for 200 slots. Set the number of such tables to 100 and on-demand becomes about $15,000 while flat-rate stays at $4,000. It assumes 30 days and ignores caching and free allowances.

General practice: start on-demand. Consider a commitment once monthly spend is already above the cost of the minimum slot purchase (the book gives $2,000 for 100 slots). Data engineers often help decide.

Estimating an end-to-end solution

You need three things: the list of services, each service's cost components, and how those components map to the calculator's inputs. Here is the book's worked example, turned into a translation table.

Requirement	Service	Calculator inputs
100 GB of CSV lands daily	Cloud Storage	Total stored: 100 GB × 30 days = 3,000 GB
Permanent Hadoop cluster: 3 masters, 10 workers, 4 vCPU / 15 GB, 200 GB SSD each	Dataproc (plus Compute Engine and disks)	High-availability masters, n1-standard-4, 13 nodes, hours per month. Use the total, not only the Dataproc line
Warehouse in BigQuery, 20 active users, 5 days a week	BigQuery	Storage: 100 GB raw + 100 GB warehouse + 100 GB mart, per day; queries: users × days × weeks × data size
Orchestrate Dataproc and BigQuery jobs	Cloud Composer	Three workers, default size, whole month
Stream 2 GB per hour, one topic, one subscription	Pub/Sub	Bytes published per day; one subscription; no snapshots or retention
Process the stream, max three workers, n1-standard-1	Dataflow	Streaming job, data processed, hours a month, three workers

In the book's 2021 run of the calculator, the lines came out roughly as follows. They are illustrative only.

Service	Monthly estimate (2021)
Pub/Sub	$112.11
Dataflow	$189.55
Cloud Storage	$60.00
Cloud Composer	$298.73
Dataproc (incl. Compute Engine and disks)	$2,062.63

The Dataproc line dwarfs the rest, which is the useful outcome: it starts a conversation about whether the permanent cluster is necessary. An estimate will differ from reality, and the assumptions are what let you explain the gap.

Partitioned and clustered tables

On-demand cost follows bytes processed. Selecting fewer columns helps; partitioning and clustering reduce the rows read. Below is a table of 30 daily partitions, each split into 26 storage blocks. Dark cells are what BigQuery must read.

Table layout Days in the filter Also filter title starts with Table size (GB)

Clustering sorts rows inside each partition by up to four columns, so BigQuery can skip blocks that cannot match. In reality you cannot see the blocks; you only choose the columns. Pick the column most queries filter on, and list columns from least to most granular.

Partition or cluster?

01 Almost every query filters to one day of a table holding months of events.
02 Within each day, queries also filter by title prefix or customer id, and you want to skip most of the day’s blocks.
03 Sort records by country, then region, then city, then postal code (least to most granular).
04 You want to divide a 1 TB, five-day table so that one day costs roughly a fifth to query.

Under flat-rate pricing these features do not change the bill directly. They reduce the slots your queries need, so you may buy fewer.

Hands-on: estimate, then prove

Part one is paper and a calculator. Part two is a BigQuery experiment that proves the partition and cluster claims with real bytes.

List the services in your design and each one’s pricing model
Machine-based, usage-based or flat rate, and what the cost inputs are (machines and hours; GB stored; bytes processed).
Enter each into the Google Cloud pricing calculator
Convert requirements into inputs. For Dataproc, add the Compute Engine and disk lines, not just the Dataproc line. Assume storage builds up over the month.
Sum the monthly cost and look for the surprise
One line usually dominates. Ask whether a different design (ephemeral clusters, aggregates, smaller tables) removes it.
Write your assumptions next to the number
Reality will differ, and the assumptions explain why. Revisit when real usage arrives.
Set a billing budget and alerts
My addition, not the book’s. An estimate is a guess; an alert is a safety net.

Experiment: build a plain, a partitioned and a partitioned-plus-clustered copy of one table

Use a large public table restricted to a couple of years. Create all three and compare.


						CREATE TABLE ds.posts_plain AS SELECT * FROM `bigquery-public-data.stackoverflow.stackoverflow_posts` WHERE EXTRACT(YEAR FROM creation_date) BETWEEN 2013 AND 2014;

CREATE TABLE ds.posts_part PARTITION BY DATE(creation_date) AS SELECT * FROM ds.posts_plain;

CREATE TABLE ds.posts_part_clust PARTITION BY DATE(creation_date) CLUSTER BY title AS SELECT * FROM ds.posts_plain;

Run the same query on all three and compare bytes billed

Filter on a date range and on a title prefix. Read the processed bytes under the results. Expect plain to be largest.


						SELECT DATE(creation_date) d, COUNT(*) n
FROM ds.TABLE_NAME
WHERE DATE(creation_date) BETWEEN '2013-01-01' AND '2013-06-01'
  AND title LIKE 'a%'
GROUP BY d;

Delete the experiment tables
Storage is cheap but not free.

Key terms

Machine-based pricing: Cost from machines, size and hours, used or not.
Usage-based pricing: Cost from what you use: storage, bytes, messages.
Flat rate: A commitment to fixed capacity for a fixed period.
On-demand (BigQuery): Billed per byte processed by each query.
Slot: BigQuery’s unit of processing: virtual CPU and memory.
Pricing calculator: Google’s tool for turning inputs into an estimate.
Partitioned table: A table divided into segments by date, timestamp or integer key.
Clustered table: A table sorted by up to four columns so blocks can be skipped.
Billed bytes: The bytes a query is charged for.

Check yourself

Pick an answer to see the reasoning straight away.

Which pricing model bills for an idle Cloud Composer environment?

When should a new BigQuery user usually start?

How do partitioned and clustered tables help under flat-rate pricing?

You cluster on country, region, city and postal code. Why that order?

What must you do when the estimate shows Dataproc as by far the largest line?

Why check the partition quota before choosing hourly partitions?

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.